BASICS OF PL

IN THIS LESSON YOU WILL JUST GONA LEARN OR UNDERSTAND THE SCALAR VARIABLES
BELOW ARE NOT THE PROGRAMMED THEY ARE JUST THE SMALL ANONYMOUS BLOCK OR SMALL PROGRAME JUST TO MAKE A SENSE TO BEGINNERS THAT WHAT IS VARIABLES AND HOW THEY BEHAVE IN PL .

CREATING VARIABLES---
THERE ARE 4 TYPES OF VARIABLES IN PL----
USING 1ST TYPE SCALR VARIABLE-----
-------------------------------------------------------------------------------------
MAKE A ANONYMOUS BLOCK WHICH TAKE INPUT NAME , AND SHOW THE RESULT ..DEPT NO. SAL , AND JOb


DECLARE
MN VARCHAR2(50):='&NAME';
MDEPT NUMBER;
MSAL NUMBER;
MJOB VARCHAR2(50);
BEGIN
SELECT DEPTNO,SAL,JOB INTO MDEPT,MSAL,MJOB FROM EMP WHERE ENAME=MN;
&D(MN||' '||MDEPT||' '||MSAL||' '||MJOB);
END;
-------------------------------------------------------------------------------------
--FIND DATA FROM MULTIPLE TABLE BUT RESULT SHULD B IN SINGLE ROW ---

DECLARE
MN VARCHAR2(50):=UPPER('&NAME');
MJ VARCHAR2(50);
MSAL NUMBER;
MDN VARCHAR2(50);
MLOC VARCHAR2(50);
BEGIN
SELECT JOB,SAL,DNAME,LOC INTO MJ,MSAL,MDN,MLOC FROM EMP NATURAL JOIN DEPT WHERE ENAME=MN;
&D(MN||' '||MJ||' '||MSAL||' '||MDN||' '||MLOC);
END;

-------------------------------------------------------------------------------------
NOW MAKING A PROGRAME USING SCALAR VARIABLE WHICH INSERT RECORDS IN TABLE---
NOW IN SCLAR VARALBE WE GONA USE %TYPE IN THIS PROGRAME---
-------------------------------------------------------------------------------------
%TYPE TAKE THE SAME DATA TYPE WHICH USE IN THAT PARTICULAR CLOUMN IN TABLE---
USING %TYPE(PERCENTTYPE) VARIABLE--

DECLARE
EMID EMP.EMPNO%TYPE:='&EMPLOYEE_ID';
MEN EMP.ENAME%TYPE:=('&NAME');
MJ EMP.JOB%TYPE:='&JOB';
MSAL EMP.SAL%TYPE:=&SALERY;
BEGIN
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)VALUES(EMID,MEN,MJ,MSAL);
&D('YOUR RECORD INSERTS SUCESSFULLY');
END;
-------------------------------------------------------------------------------------
--NOW MAKE A PROGRAME IN WHICH YOU GONA UPDATE RECORDS IN TABLE USING %TYPE---


DECLARE
MEN EMP.ENAME%TYPE:=UPPER('&NAME');
MSAL EMP.SAL%TYPE:=&SALERY;
BEGIN
UPDATE EMP SET SAL=MSAL WHERE ENAME=MEN;
&D('YOUR RECORD UPDATED SUCESSFULLY');
END;
------------------------------------------------------------------------------------
-MAKE A PROGRAME TO DELETE RECORDS FROM TABLE USING %TYPE

DECLARE
MN EMP.ENAME%TYPE:=UPPER('&NAME');
BEGIN
DELETE FROM EMP WHERE ENAME=MN;
&D('YOUR RECORD HAS BEEN DELETED ');
END;



---SO WE WAS USING SCALAR VARIABLE WHICH HOLDS SINGLE VALUE AND DEPEND ON VARIABLE DATATYPE AS U SEE IN PREVIOUS PROGRAMME----

oracle programming

SALAM N HELLO TO ALL
THIS IS MY BLOG FOR ALL PROGRAMMERS ...
IN THIS U WILL FIND EVERY SOLUTION AND LEARN NEW THINGS AS WELL



REGARDS,

MUHAMMAD ARSALAN HASHMI

SOFTWARE DEVELOPER
(ACTION SCRIPT,HTML,XMLSCRIPT,PL/SQL,SQL,ORACLE FORMS 10G, ORACLE REPORTS 10G)

ORACLE UNIVERSITY