PL-SQL WITH EXAMPLE

SOME OTHER IMPORTENT

PL-SQL NOTES WITH EXAMPLE:-

FIRST STEP OF PL-SQL IS:

DECLARE

<VARIABLE NAME>;

BEGIN

SELECT * INTO <VARIABLE NAME> FROM <TABLE NAME> <CONDITION>;

END;

CONDITIONAL BRANCHING:-

CONDITIONAL BRANCHING HAS 3 TYPES. THEY ARE FOLLOWING. WE CAN UNDERSTAND EASILY WITH THE HELP OF FOLLOWING DISCRIPTION:-

1. SIMPLE IF:

SYNTAX: 

IF <COND> THEN

<STMT>

END IF;

FOR EXAMPLE:

WRITE A PROGRAMM TO DISPLAY EVEN,ODD NUMBERS AND INSERT INTO ONE TABLE OF YOUR OWN CHOICE.

ANSWER:

DECLARE

N NUMBER;

BEGIN

N:='&ENTER_NUMBER';

IF MOD(N,2)=0 THEN

DBMS_OUTPUT.PUT_LINE('NO IS EVEN:=>'||N);

INSERT INTO EVEN(COL) VALUES(N);

ELSE

DBMS_OUTPUT.PUT_LINE('NO IS ODD:=>'||N);

INSERT INTO ODD(COL) VALUES(N);

END IF;

END;

2. NESTED IF:

SYNTAX: 

IF <COND> THEN

<STMT>

ELSIF <COND>THEN

<STMT>

ELSIF <COND>THEN

<STMT>

ELSIF <COND>THEN

<STMT>

ELSE

<STMT>

END IF;

FOR EXAMPLE:

DECLARE

V_SAL NUMBER(20);

BEGIN

V_SAL:='&SALARY';

IF

V_SAL>=10000

THEN

DBMS_OUTPUT.PUT_LINE('A');

ELSIF

V_SAL<10000 AND V_SAL>=5000

THEN

DBMS_OUTPUT.PUT_LINE('B');

ELSE

DBMS_OUTPUT.PUT_LINE('C');

END IF;

END;

3. COMPLEX IF :

SYNTAX:-  IF <COND>THEN

<STMT>

IF <COND>THEN

<STMT>

IF <COND>THEN

<STMT>

END IF;

<STMT>

END IF;

END IF;

FOR EXAMPLE:-

DECLARE

  V_SAL NUMBER(20);

BEGIN

  V_SAL:='&SALARY';

 IF

   V_SAL>=10000

      THEN

      DBMS_OUTPUT.PUT_LINE('A');

 END IF;

 IF

   V_SAL<10000 AND V_SAL>=5000

      THEN

      DBMS_OUTPUT.PUT_LINE('B');

      END IF;

 IF

   V_SAL<5000

     THEN

     DBMS_OUTPUT.PUT_LINE('C');

 END IF;

END;

LOOP:-

LOOP HAS THREE TYPES:-

1. FOR:-

SYNTAX:

FOR <VAR> IN <FIRST VAR>..<LAST VAR>

LOOP

<STMT>

END LOOP;

FOR EXAMPLE:-

BEGIN

FOR

I IN 1..5

LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

END;

2. EXAMPLE:-

DECLARE

TEMP NUMBER:=0;

BEGIN

FOR I IN 1..10

LOOP

TEMP:=TEMP+2;

DBMS_OUTPUT.PUT_LINE(I||'   '||TEMP);

END LOOP;

END;

2. WHILE:-

WE CAN UNDERSTAND WHILE LOOP WITH THE FOLLOWING EXAMPLE.

FOR EXAMPLE:-

DECLARE

N NUMBER:=1;

BEGIN

WHILE (N<=10)

LOOP

N:=N+1;

DBMS_OUTPUT.PUT_LINE(N);

END LOOP;

END;

3. LOOP:-

WE CAN UNDERSTAND WHILE LOOP WITH THE FOLLOWING EXAMPLE.

FOR EXAMPLE:-

DECLARE

N NUMBER:=0;

BEGIN

LOOP

N:=N+3 ;

EXIT WHEN N>30;

DBMS_OUTPUT.PUT_LINE(N);

END LOOP;

END;

RECORD ATTRIBUTES IN PL/SQL:

RECORD ATTRIBUTES ARE TWO TYPES. THEY ARE FOLLOWING:-

1. TYPE:-

TYPE IS BASICALLY USED TO CALL A COLUMN ATTRIBUTE.

FOR EXAMPLE:-

DECLARE

V_SAL EMP.SAL%TYPE;

BEGIN

SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO=&EMPNO;

DBMS_OUTPUT.PUT_LINE(V_SAL);

END;

2. ROWRTYPE:-

DECLARE

V_SAL EMP%ROWTYPE;

BEGIN

SELECT * INTO V_SAL FROM EMP WHERE EMPNO=&EMPNO;

DBMS_OUTPUT.PUT_LINE(V_SAL.SAL||' '||V_SAL.ENAME);

END;

EXCEPTION HANDLING:-

EXCEPTION HANDLING IS BACICALLY USED FOR DISPLAY OUTPUT WHEN THERE MIGHT BE SOME ERROR OR ANY OTHER EXECPTION IN PROGRAM.EXCEPTION HANDLING ARE RUN TIME ERROR WHICH ARE EXECUTED RUN TIME INTERNALLY IN ORACLE.

THERE ARE 2 TYPES EXCEPTION:-

1. SYSTEM DEFINED EXCEPTION:-

SYSTEM DEFINED EXCEPTION ARE EXECUTED RUN TIME IN ORACLE,WHEN EVER THE RUN TIME ERROR ARE COMES INTO ORACLE.

IT GETS HANDLE INTERNALLY BY SYSTEM DEFINED ERROR.

NOTE:=>IT WILL NOT GIVE ERROR GROUP FUNCTION

EX: SELECT MAX(SAL) FROM EMP WHERE DEPTNO=70;

SOME OF THE SYSTEM DEFINED ERROR ARE:-

1.NO_DATA_FOUND:-

DECLARE

V_ENAME VARCHAR2(20);

BEGIN

SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=&EMPNO;

DBMS_OUTPUT.PUT_LINE(V_ENAME);

EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND');

END;

2.DUP_VAL_ON_INDEX:-

Begin

insert into PATM (A_NO,A_H_NAME,BAL)values(&A_NO,'&A_H_NAME','&BAL');

DBMS_output.put_line('Values Inserted on PATM');

Exception when dup_val_on_index then

Dbms_output.put_line('DUPLICATE ACCOUNT: YOU CAN NOT INSERT DUPLICATE VALUE.');

END;

3.TOO_MANY_ROWS:-

DECLARE

V_EMPNO EMP%ROWTYPE;

BEGIN

SELECT * INTO V_EMPNO FROM EMPCOPY WHERE EMPNO=&EMPNO;

DBMS_OUTPUT.PUT_LINE(V_EMPNO.EMPNO||' '||V_EMPNO.ENAME);

EXCEPTION when too_many_rows then

dbms_output.put_line('there is two many rows'||CHR(10)||

V_EMPNO.ENAME||' '||V_EMPNO.EMPNO||' ' ||V_EMPNO.SAL);

END;

4.VALUE_ERROR:-

5.ZERO_DIVIDE.

6.OTHERS

2. USER DEFINED EXCEPTION:-

THERE ARE TWO STEPS TO ASSIGN USER DEFINE ERRORS.

THEY ARE FOLLOWING:-

1. <execp_var> exception;

2. Raise the exception where there is need.

FOR EXAMPLE:-

declare

e_sal exception;

v_empno number;

v_ename varchar2(30);

v_sal number;

begin

select empno,ename,sal into v_empno,v_ename,v_sal from EMPCOPY where empno=&num ;

if v_sal<4000 then

update EMPCOPY set sal=v_sal+700 where empno=v_empno;

else

raise e_sal;

end if;

dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal||'Records updated');

insert into audit_det values(v_empno,v_sal,v_sal+700,'record updated',sysdate);

commit;

exception when e_sal then

dbms_output.put_line(v_empno||'already updated so u cannot updated');

when no_data_found then

dbms_output.put_line(v_empno||'no employees for these empno');

when others then

dbms_output.put_line(sqlerrm);

end;

PRAGMA:

PRAGMA HAS FOUR TYPES. THEY ARE FOLLOWING:-

1. Exception_init:-

declare

e_child_record_found exception;

pragma exception_init(e_child_record_found,-02292);

v_empno number;

v_dno number;

begin

delete from dept where deptno=&v_dno;

dbms_output.put_line('Records deleted');

insert into audit_det(empno,operation,odate) values(v_dno,'record deleted',sysdate);

commit;

exception when e_child_record_found then

dbms_output.put_line(v_dno||'already child for these department so u cannot delete');

when no_data_found then

dbms_output.put_line(v_empno||'no employees for these empno');

when others then

dbms_output.put_line(sqlerrm);

end;

2. Autonomous_transaction:-

Declare

pragma autonomous_transaction;

begin

for i in 30..40

loop

dbms_output.put_line(i);

insert into abc values(i);

dbms_output.put_line('record inserted==>');

end loop;

commit;

end;

3. Restrict_references:-

4. Serially_reusable:-

COLLECTION:-

WE CAN EASILY UNDERSTAND COLLECTION WITH THE HELP OF FOLLOWING DISCRIPTION:-

PL-SQL RECORD:-

RECORD ARE USED TO FETCH SAME RECORD FROM ONE OR MORE THEN ONE TABLE. IN THIS METHOD WE DECLARE RECORD

AND ASSGN AS A DATA TYPE OF AN INSTANCE.

FOR EXAMPLE:-

declare

type t_rec is record

(v_empno emp.empno%type,

v_ename emp.ename%type

);

v_rec t_rec;

begin

select empno,ename into v_rec from emp where empno=&num;

dbms_output.put_line(v_rec.v_empno||' '||v_rec.v_ename);

end;

Insertion WITH PL-SQL ON RUN TIME:-

declare

dept_info dept%rowtype;

begin

dept_info.deptno:=70;

dept_info.dname:='david';

dept_info.loc:='faridabad';

insert into dept values dept_info;

end;

PL-SQL TABLE:-

IN THIS METHOD WE DECLARE A VARIABLE AND STORE TABLE IN THAT VARIABLE. AFTER THAT WE DECLARE A INSTANCE AND DECLARE TABLE  AS A DATA TYPE OF THAT INSTANCE.

FOR EXAMPLE:-

DECLARE

TYPE EMP_TAB IS TABLE OF EMP%ROWTYPE;

INST_EMP EMP_TAB;

V_DNO NUMBER;

BEGIN

V_DNO:=&DEPTNO_NUM;

SELECT * BULK COLLECT INTO INST_EMP FROM EMP WHERE DEPTNO=V_DNO;

FOR I IN INST_EMP.FIRST..INST_EMP.LAST

LOOP

DBMS_OUTPUT.PUT_LINE

(INST_EMP(I).EMPNO||' '||INST_EMP(I).ENAME||' '||INST_EMP(I).SAL||' '||INST_EMP(I).DEPTNO

);

END LOOP;

END;

ASSOCIATIVE ARRAY:-

CURSOR:-

CURSOR IS JUST LIKE VIEW. IT DOES NOT EFFECT IN DATA BASE BUT IT FETCH RECORD FROM DATA BASE EASILY.

FOR EXAMPLE:-

DECLARE

CURSOR C1 IS SELECT ENAME FROM EMP;

V_NAME VARCHAR2(20);

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO V_NAME;

DBMS_OUTPUT.PUT_LINE(V_NAME);

EXIT WHEN C1%NOTFOUND;

END LOOP;

CLOSE C1;

END;

__________________________________________________________

TO_BE_CONTINUED........

**********************************************************