PL / SQL Coding
1
Amirtharaj.K
CURSORS Explicit Cursor Normal - Select declare dno emp.deptno%type:=&D emp.deptno%type:=&Dept_No; ept_No; eno emp.empno%type; ename1 emp.ename%type; cursor c1 is select empno,ename from emp where deptno=dno; begin open c1; loop fetch c1 into eno,ename1; exit when c1%notfound; dbms_output.put_line(eno); dbms_output.put_line(ename1); end loop; dbms_output.put_line(c1%rowcount); close c1; end;
Explicit Cursor Normal -Update declare dno emp.deptno%type:=&D emp.deptno%type:=&Dept_No; ept_No; eno emp.empno%type; ename1 emp.ename%type; esal emp.sal%type; cursor c1 is select empno,ename,sal from emp where deptno=dno; begin open c1; loop fetch c1 into eno,ename1,esal; exit when c1%notfound; if esal<1200 then update emp set comm=sal*.6 where deptno=dno and (sal<1200); elsif esal>1200 and esal<=1500 then update emp set comm=sal*.4 where deptno=dno and (sal>1200 and sal<=1500); else update emp set comm=sal*.5 where deptno=dno and (sal>1500); end if; dbms_output.put_line(eno); dbms_output.put_line(ename1); end loop; commit; dbms_output.put_line(c1%rowcount); close c1; end;
Explicit Cursor Normal -Update - Cursor for Loop set server output on; declare dno emp.deptno%type:=&D emp.deptno%type:=&Dept_No; ept_No; cursor c1 is select empno,ename,sal from emp where deptno=dno; r1 c1%rowtype; begin for r1 in c1 loop if r1.sal<1200 then update emp set comm=sal*.06 where deptno=dno and (sal<1200); elsif r1.sal>1200 and r1.sal<=1500 then update emp set comm=sal*.04 where deptno=dno and (sal>1200 and sal<=1500); else update emp set comm=sal*.05 where deptno=dno and (sal>1500); end if; end loop; commit; exception when no_data_found then dbms_output.put_line('Nodata');
end;
PL / SQL Coding
2
Amirtharaj.K
Explicit Cursor Cursor Normal -Update - Cursor for Loop - Row Count declare dno emp.deptno%type:=&D emp.deptno%type:=&Dept_No; ept_No; cursor c1 is select empno,ename,sal from emp where deptno=dno; r1 c1%rowtype; begin for r1 in c1 loop exit when c1%rowcount>1 if r1.sal<1200 then update emp set comm=sal*.08 where deptno=dno and (sal<1200); elsif r1.sal>1200 and r1.sal<=1500 then update emp set comm=sal*.09 where deptno=dno and (sal>1200 and sal<=1500); else update emp set comm=sal*.1 where deptno=dno and (sal>1500); end if; end loop; commit; exception when no_data_found then dbms_output.put_line('Nodata'); when invalid_number then dbms_output.put_line('Nodata'); end;
Implicit Cursor - sql%rowcount , sql%notfound sql%notfound declare dno emp.deptno%type:=&D emp.deptno%type:=&Dept_No; ept_No; esal emp.sal%type:=&Esal; emp.sal%type:=&Esal; begin update emp set sal=sal+esal where deptno=dno; dbms_output.put_line(sql%rowcount); if sql%notfound then dbms_output.put_line('No Data'); end if; end;
Cursor – Parameterized set serveroutput on; declare a dept.deptno%type:=&Deptno; b dept.dname%type; cursor c1(d1 c1(d1 dept.deptno%type) is select dname from dept where deptno=d1; deptno=d1; begin open c1(a); fetch c1 into b; if c1%notfound then dbms_output.put_line('No'); else dbms_output.put_line(b); end if; close c1; end;
PL / SQL Coding
3
EXCEPTION – ERROR HANDLING Exception - Predefined declare a dept.deptno%type:=&DEPT_N dept.deptno%type:=&DEPT_NO; O; b emp.empno%type; begin select empno into b from emp where deptno=a; dbms_output.put_line(b); exception when no_data_found then dbms_output.put_line('No Depts' ); when too_many_rows then dbms_output.put_line('More Employees' ); end;
Exception - when dup_val_on_index and when others begin insert into dept(deptno,dname,loc) values(&a,'&b','&c'); dbms_output.put_line(SQL%rowcount); exception when dup_val_on_index then dbms_output.put_line('Duplicate Depts' ); when others then dbms_output.put_line('Invalid Depts Insers' Insers' ); end;
Exception - User Defined declare a dept.deptno%type:=&Deptno; b dept.dname%type:='&Dept_N dept.dname%type:='&Dept_Name'; ame'; c exception; begin update dept set dname=b where deptno=a; if sql%notfound then raise c; end if; exception when c then dbms_output.put_line(' Depts should be exists' ); when others then dbms_output.put_line(' Depts should be numeric' ); end;
Exception - SQL Code , SQL Errm begin insert into dept(deptno,dname,loc) values(&a,'&b','&c'); dbms_output.put_line(SQL%rowcount); exception when dup_val_on_index then dbms_output.put_line('Duplicate Depts' ); dbms_output.put_line(SQLerrm dbms_output.put_line(SQLer rm ); dbms_output.put_line(SQLcode); when others then dbms_output.put_line('Invalid Depts Inserts' Inserts' ); end;
Amirtharaj.K
PL / SQL Coding
4
Exception - INIT Pragma - Non - Predefined declare a dept.deptno%type:=&Deptno; b dept.dname%type:='&Dept_N dept.dname%type:='&Dept_Name'; ame'; c exception; PRAGMA EXCEPTION_INIT(c,-23); begin update dept set dname=b where deptno=a; if sql%notfound then raise c; end if; exception when c then dbms_output.put_line('dept no is missing'); when others then dbms_output.put_line(' Depts should be numeric' ); end;
STORED PROCEDURES Procedure - IN create or replace procedure p1000(a1 in dept.deptno%type,b1 in dept.dname%type,c1 in dept.loc%type) is begin insert into dept values(a1,b1,c1); exception when dup_val_on_index then dbms_output.put_line('Duplicate Depts' ); end; Procedure - IN IN OUT OUT - Specification create or replace procedure p1002(a1 in emp.empno%type,b1 in out emp.sal%type) is c1 number; begin select sal into b1 from emp where empno=a1; if b1<2000 then b1:=b1+111; else b1:=b1-100; end if; exception when no_data_found then dbms_output.put_line('No Emps' ); end; Procedure - IN OUT OUT - Body - Implementation declare b number; a number:=&AA; begin p1002(a,b); dbms_output.put_line(b); end; Procedure - IN IN - Exception – Raise Raise Application Application Error create or replace procedure p1004(a1 in dept.deptno%type,b1 in dept.dname%type) is begin update dept set dname=b1 where deptno=a1; dbms_output.put_line(sql%rowcount); if sql%rowcount=0 then raise_application_error(-20204,'Invalid Updates, Try Again'); end if; end;
Amirtharaj.K
PL / SQL Coding 5 Procedure - Implicit Cursor - Explicit Explicit Cursor for loop -Update
Amirtharaj.K
create or replace procedure p1007(dno1 dept.deptno%type) is dno emp.deptno%type:=dno1; cursor c1 is select empno,ename,sal from emp where deptno=dno; r1 c1%rowtype; begin for r1 in c1 loop exit when c1%rowcount>1; update emp set comm=sal*.075 where deptno=dno and (sal>2000 and sal<=3000); end loop; commit; dbms_output.put_line(sql%rowcount); end; Procedure Implicit Cursor - Explicit Cursor Normal -Update create or replace procedure p1005(dno1 dept.deptno%type) is dno emp.deptno%type:=dno1; eno emp.empno%type; ename1 emp.ename%type; esal emp.sal%type; cursor c1 is select empno,ename,sal from emp where deptno=dno; begin open c1; loop fetch c1 into eno,ename1,esal; exit when c1%notfound; if esal<1200 then update emp set comm=sal*.1 where deptno=dno and (sal<1200); elsif esal>1200 and esal<=1500 then update emp set comm=sal*.15 where deptno=dno and (sal>1200 and sal<=1500); else update emp set comm=sal*.2 where deptno=dno and (sal>1500); end if; dbms_output.put_line(eno); dbms_output.put_line(ename1); end loop; commit; dbms_output.put_line(c1%rowcount); close c1; dbms_output.put_line(sql%rowcount); end; Procedure - Implicit Cursor - Explicit Explicit Cursor Normal Normal -Update [ Single Condition ] create or replace procedure p1006(dno1 dept.deptno%type) is dno emp.deptno%type:=dno1; eno emp.empno%type; ename1 emp.ename%type; esal emp.sal%type; cursor c1 is select empno,ename,sal from emp where deptno=dno; begin open c1; loop fetch c1 into eno,ename1,esal; exit when c1%notfound; if esal>2000 and esal<=3000 then update emp set comm=sal*.15 where deptno=dno and (sal>2000 and sal<=3000); end if; dbms_output.put_line(eno); dbms_output.put_line(ename1); end loop; commit; dbms_output.put_line(c1%rowcount); close c1; dbms_output.put_line(sql%rowcount); end;
PL / SQL Coding
6
STORED FUNCTION Function – Normal - Specification create or replace function f1000(eno number) return number is esal emp.sal%type; begin select sal into esal from emp where empno=eno; RETURN esal; exception when no_data_found then raise_application_error(-20345,'This Employee id not exitst'); end; Function – Body declare a number; begin a:=f1000(&Empno); dbms_output.put_line(a); end;
PACKAGE Package - Normal - Package Specification Specification - Procedure create or replace package pc1000 is procedure p1100(a1 in number); end pc1000;
Package - Normal - Package Body - Procedure create or replace package body pc1000 is procedure p1100(a1 in number) is ename1 emp.ename%type; begin select ename into ename1 from emp where empno=a1; dbms_output.put_line(ename1); end p1100; end pc1000;
Execute Package / Procedure exec pc1000.p1100(7900); pc1000.p1100(7900); -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Package - Normal - Package Specification Specification - Function create or replace package pc1001 is function f1100(z1 f1100(z1 in number) return number; end pc1001;
Package - Normal - Package Body - Function create or replace package body pc1001 is function f1100(z1 in number) return number is esal emp.sal%type; begin select sal+nvl(comm,0) into esal esal from emp where empno=z1; return esal; end f1100; end pc1001;
Amirtharaj.K
PL / SQL Coding
7
Execute Function declare a1 number; begin a1:=pc1001.f1100(&empno); dbms_output.put_line(a1); exception when no_data_found then dbms_output.put_line('No Emp'); end; -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Package - Normal - Package Specification Specification - Procedure - Function create or replace package pc1002 is procedure p1101(a1 in number); function f1101(z1 f1101(z1 in number) return number; end pc1002;
Package - Normal - Package Body - Procedure - Function create or replace package body pc1002 is procedure p1101(a1 in number) is ename1 emp.ename%type; begin select ename into ename1 from emp where empno=a1; dbms_output.put_line(ename1); exception when no_data_found then dbms_output.put_line('No Emp Nos'); end p1101; function f1101(z1 in number) return number is esal emp.sal%type; begin select sal+nvl(comm,0) sal+nvl(comm,0) into esal from emp where empno=z1; return esal; end f1101; end pc1002;
Execute Package / Procedure exec pc1002.p1101(7900); pc1002.p1101(7900);
Execute Function declare a2 number; begin a2:=pc1002.f1101(&empno); dbms_output.put_line(a2); exception when no_data_found then dbms_output.put_line('Emp Nos None'); end;
Execute Package / Function - Procedure declare a3 number:=&empNo; a2 number; begin a2:=pc1002.f1101(a3); pc1002.p1101(a3); dbms_output.put_line(a2); exception when no_data_found then dbms_output.put_line('Emp Nos None'); end; -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Amirtharaj.K
PL / SQL Coding
8
Package - Normal - Package Package Specification Specification - Function create or replace package pc1003 is function f1103(z1 f1103(z1 in number) return number; end pc1003;
Package - Normal - Package Body - Function create or replace package body pc1003 is function f1103(z1 in number) return number is exp1 emp.sal%type; begin select (sysdate-hiredate)/365 into exp1 from emp where empno=z1; return exp1; end f1103; end pc1003;
Execute Package / Function declare a2 number; begin a2:=pc1003.f1103(&empno); dbms_output.put_line(a2); exception when no_data_found then dbms_output.put_line('Emp Nos None'); end; -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Package - Normal - Package Specification Specification - - Function - Cursor create or replace package pc1004 is function f1104 f1104 return number; end pc1004;
Package - Normal - Package Body- - Function - Cursor create or replace package body pc1004 is function f1104 return number is cursor c1 is select (sysdate-hiredate)/365 service from emp ; exp1 emp.sal%type; begin for r1 in c1 loop exp1:=r1.service; exit when c1%notfound; end loop; return exp1; end f1104; end pc1004;
Execute Package / Function - Cursor declare a2 number; begin a2:=pc1004.f1104; dbms_output.put_line(a2); end; -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Package - Normal - Package Specification Specification - -Procedure - Procedure - Cursor create or replace package pc1105 pc1105 is procedure p1105; end pc1105;
Amirtharaj.K
PL / SQL Coding
9
Package - Normal - Package Body- - Procedure - Cursor create or replace package body pc1105 is procedure p1105 is cursor c1 is select empno,ename,(sysdateempno,ename,(sysdate-hiredate)/365 hiredate)/365 service from emp ; eno1 number; ename1 varchar2(25); exp1 number; begin open c1; loop fetch c1 into eno1,ename1,exp1; exit when c1%notfound; dbms_output.put_line(eno1||' '||ename1||' '||exp1 ); end loop; close c1; end p1105; end pc1105;
Execute Package / Procedure - Cursor exec pc1105.p1105; -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRIGGER Trigger – Replication –Row Level Create or replace trigger tr01 after delete on dept for each row Begin Insert into dept01 values (:old.deptno,:old.dname,:old.loc); End; Trigger -Days – Statement Level create or replace trigger tr02 before update or delete on dept declare d1 varchar(3); begin d1:=to_char(sysdate,'dy'); if d1 in('mon','tue') then raise_application_error(-20025,'try on another day'); end if; end; Trigger -Cross Updataion - Row Level create or replace trigger tr03 after update on dept for each row begin update emp set sal=sal+777 where deptno=:old.deptno; end;
Trigger - Auditing – Statement Level create or replace trigger tr05 after update on dept50 declare u1 varchar2(50); begin select user into u1 from dual; insert into user50 values(u1,sysdate,'Update'); values(u1,sysdate,'Update'); end;
Amirtharaj.K
PL / SQL Coding
10
create or replace trigger tr06 after insert on dept50 declare u1 varchar2(50); begin select user into u1 from dual; insert into user50 values(u1,sysdate,'inser values(u1,sysdate,'insert'); t'); end;
Trigger - Inserting Insertin g - Updating Updatin g - Deleting Deleti ng - CONDITIONAL TRIGGER – Statement Statemen t Level
create or replace trigger tr07 before insert or update or delete on dept50 begin if inserting then raise_application_error(-20001,' You You unable to insert '); elsif updating then raise_application_error(-20002,' You You unable to update '); elsif deleting then raise_application_error(-20003,' You You unable to delete '); end if; end; Trigger -Update Cascade – Row Level create or replace trigger tr08 after update of of deptno on dept50 for each row begin update emp50 set deptno=:ne w.deptno w.deptno where deptno=:old.deptno; end; Trigger -Delete Cascade – Row Level create or replace trigger tr09 before delete on dept50 for each row begin delete from emp where deptno=:old.deptno; end; Trigger –Computation – Statement Level create or replace trigger tr10 after insert or update or delete on emp50 begin update dept50 set totsal=(select sum(sal) from emp50 where emp50.deptno=dept50.deptno); end;
Amirtharaj.K