Sunday 19 February 2012

pl/sql best examples session 3

click here for session1
click here for session 2


Type creation(this is similar to structure )
Type example in pl/sql
declare
type t_numbers is record
(
  a number(5),
  b number(5),
  c number(5)
); vnum t_numbers;
v_b number(5):=10;
begin
            vnum.a:=5;
            vnum.b:=5;
 dbms_output.put_line(vnum.a+v_b);
end;
/
 Boolean type example
Declare
            A Boolean:=TRUE;
            B Boolean:=FALSE;
            C Boolean:=NULL;
            AB VARCHAR2(4):=’TRUE’;
            BB VARCHAR2(5):=’FALSE’;
            CB VARCHAR2(4):=’NULL’;
Begin
            IF(A AND A) THEN
                        dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
            END IF;
            IF(A AND B) THEN
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS TRUE’);
            ELSE
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS FALSE’);
            END IF;
            IF(A AND C) THEN
                        dbms_output.put_line(AB||‘ AND’||CB||’  IS TRUE’);
            ELSE 
IF((A AND C) IS NULL) THEN
                                    dbms_output.put_line(AB||‘ AND’||BB||’  IS null’);
                        END IF;
            END IF;
IF(A AND A) THEN
                        dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
            END IF;
            IF(A AND B) THEN
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS TRUE’);
            ELSE
                        dbms_output.put_line(AB||‘ AND’||BB||’  IS FALSE’);
            END IF;
            IF(A AND C) THEN
                        dbms_output.put_line(AB||‘ AND’||CB||’  IS TRUE’);
            ELSE 
IF((A AND C) IS NULL) THEN
                                    dbms_output.put_line(AB||‘ AND’||BB||’  IS null’);
                        END IF;
            END IF;
end ;

Declare
            A Boolean:=TRUE;
            B Boolean:=FALSE;
            C Boolean:=NULL;
            AB VARCHAR2(4):=’TRUE’;
            BB VARCHAR2(5):=’FALSE’;
            CB VARCHAR2(4):=’NULL’;
Begin
            Case
                        While (a and a) then dbms_output.put_line(AB||‘ AND’||AB||’  IS TRUE’);
                        While ((a and b)=false) then dbms_output.put_line(AB||‘ AND’||BB||’  IS  False’);
                        While ((a and c) is NULL) then dbms_output.put_line(AB||‘ AND’||CB||’  IS  NULL’);
                        While ((b and c) is NULL) then dbms_output.put_line(BB||‘ AND’||CB||’  IS  NULL’);
                        While ((b and b )=false) then dbms_output.put_line(BB||‘ AND’||BB||’  IS  FALSE’);
                        While ((c and c) is NULL) then dbms_output.put_line(CB||‘ AND’||CB||’  IS  NULL’);

                        While (a or a)
                        While (a or b)
                        While ((a or c) is NULL)
                        While ((b or c) is NULL)
                        While ((b or b )=false)
                        While ((c or c) is NULL)
--u can use as many condition as u want--

Create,drop,truncate in plsql / ddl in plsql /execute immediate
in pl/sql
Using execute immediate
BEGIN
     EXECUTE IMMEDIATE
    'CREATE TABLE ' || 'emp1' ||
    '(
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2)
    )';
EXECUTE IMMEDIATE
    'drop ’ || ‘ table' || ' emp1';
EXECUTE IMMEDIATE
    'truncate ’ || ‘table’||' emp1';
END;
/
Drop table in pl/sql
BEGIN
     EXECUTE IMMEDIATE
    'drop ’ || ‘ table' || ' emp1';
END;
/
Insert in pl/sql
Begin
            Insert into emp1 values(‘&empno’,’&ename’,’&job’,’&sal’,’&deptno’);
End ;
/





Cursor with for and %rowtype example in pl/sql
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            For I in 1..cnt loop
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            end loop;
            close empd;
end;
/          









Cursor using loop and %rowtype
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            Loop
                        Exit when i>cnt ;
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            i:=i+1;
            end loop;
            close empd;
end;
/          




Cursor Using while loop and row type

Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            While(I < cnt) Loop
                        Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
            i:=i+1;
            end loop;
            close empd;
end;
/          





Cursor Using %rowcount,%isopen,%notfound,%found
Declare
            Cursor empcursor is select * from emp;
            Edetail emp%rowtype;
Cnt number;


Begin
            Select count(*) into cnt from emp;
            If(empcursor%isopen) then
                        Dbms_output.put_line(‘Cursor is already opened’);
            Else
                        Open empcursor;
Dbms_output.put_line(‘Cursor Opened succesfully’);
            End if;
Dbms_output.put_line(‘Total No. of records before fetch are:’||empcursor%rowcount);
For  I in 1..cnt loop
If(empcursor%found)
            Dbms_output.put_line(‘Record available’);
Else
            Dbms_output.put_line(‘Record not available’);
End if;
Fetch empcursor into edetail;
Dbms_output.put_line(‘Total No. of records are:’||empcursor%rowcount);
            end loop;
If(empcursor%found)
            Dbms_output.put_line(‘Record available’);
Else
            Dbms_output.put_line(‘Record not available’);
End if;
Close empcursor;
End;
/


Cursor with parameter
Declare
            Cursor empcursor(eno number) is select * from emp where empno=eno ;
            Edetail emp%rowtype;
Cnt number;
Begin
            Open empcursor(102);
            Select count(*) into cnt from emp where empno=102;
            For I in 1..cnt loop
                        Fetch Empcursor into edetail;
                        dbms_output.put_line(‘Employee number:  ’||edetail.EMPNO||’  Employee name ’||edetail. ENAME||’  Employee Salary :  ’||edetail. SAL||’  Employee works for : ’ ||edetail. DEPTNO||’  Employee Date of joining ’||edetail.DOJ||’  Manager ID ’||edetail. MGR);
            end loop;
            end;
/
Bind variable in plsql
Sql>variable c number; //this is a bind variable
begin
:c:=:c+20;
 dbms_output.put_line(:c);
end;
/


Exception handling in plsql
declare
 sal number(6);
begin
 select emp.sal into sal from emp;
dbms_output.put_line (SAL/0);
exception
 when TOO_MANY_ROWS then
dbms_output.put_line ('A SELECT...INTO wrote many rows');
            when  ZERO_DIVIDE then
dbms_output.put_line (‘DIVIDE BY ZERO ERROR’);
            WHEN OTHERS THEN
dbms_output.put_line (‘Some other Exception raised’);

end;
/


User defined exceptions raising user exception in plsql
Declare
            Abcd exception;
Begin
            Delete emp where empno=110;
            If(sql%notfound) then
                        Raise abcd;
            End if;
            EXCEPTION
                        When abcd then
                                    dbms_output.put_line (‘No row deleted’);
End;
/

Using  RAISE_APPLICATION_ERROR ( ) in plsql

Begin
            Delete emp where empno=110;
            If(sql%notfound) then

                        RAISE_APPLICATION_ERROR ( ‘-20999’,’No rows deleted’);

            End if;
End;
/

RAISE_APPLICATION_ERROR and handling it
declare
 SALY  NUMBER;
BEGIN
 SELECT SAL INTO SALY FROM EMP WHERE EMPNO=102;
 IF(SALY>=20000) THEN
  RAISE_APPLICATION_ERROR('-20988','YOU ARE GETTING MORE THAN WHAT U SHOULD GET');
 END IF;
EXCEPTION
            WHEN OTHERS THEN
dbms_output.put_line (‘GET PERMISSION TO GET MORE THAN 19999’);
END;
/

Scope example in plsql
Declare
            I number;
Begin
            I:=10;
            Dbms_output.put_line(i);
            Declare
                        j number:=20;
            Begin
                        Dbms_output.put_line(j);
            End;
            Dbms_output.put_line(j);
End;
/




declare
            var number(10):=5;
            i number:=0;
begin
            for i in 1..10 loop
                        I:=I+1;
                        dbms_output.put_line(i);
            end loop;
end;                
/

Function example in plsql
Declare
            Arg1 number(10):=5;
            Arg2 number(10):=5;
            Arg3 number(10);
Begin
            Arg3:=addd(arg1,arg2);
            Dbms_output.put_line(arg3);
End;
/
Create OR REPLACE function addd(a number,b number) RETURN NUMBER IS
BEGIN

                                                    RETURN A+B;
END;
/
SHOW ERROR;                         

Create OR REPLACE function addd(a number,b number) RETURN NUMBER IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘inside function add’);
                                                    RETURN A+B;
END;
/
This is wrong since using out in function parameter a function can have only in not out or in out
Create OR REPLACE function DISP1( var out  varchar2(30))  RETURN VARCHAR2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘HI I GOT OUT PUT’);
Var:=’jayachandran’;
                                                    RETURN var;
END;
/
Show error;

Cursor with for to display all data of the table
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp;
            Cnt number;
Begin
           
            Open empd;

                        Select count(*) into cnt from emp;
            For I in 1..cnt loop
                                    Fetch empd into edetail;
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
           
end loop;
close empd;

            end;
/                       

Procedure with in and out
CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT varchar2)
IS
BEGIN
  SELECT ename INTO emp_name
  FROM emp WHERE empno = id;
END;                                    
/


declare
            Empname varchar2(50);
Begin
            Emp_name(102,empname);
            Dbms_output.put_line(empname);
End;
/

Function with out return in plsql (this function will raise application error but will do the work inside it)
Create OR REPLACE function sayhi(nam varchar) return varchar  IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘hjhkjhkjhk’);
END;
/

Create or replace procedure displa(c in number) is
begin
                                                    dbms_output.put_line(c);
End;
Create or replace procedure dees(c in out number) is
Begin
                                                    C:=24;
End;
/

Create or replace procedure dees is
Begin
                                                    Dbms_output.put_line(‘This is in procedure’);
End;
/

Procedure with %type and %rowtype:
CREATE OR REPLACE PROCEDURE TAB_DIS(EMPNUM EMP.EMPNO%TYPE) IS
RECRD EMP%ROWTYPE;
BEGIN
SELECT * INTO RECRD FROM EMP WHERE EMPNO = EMPNUM;     
DBMS_OUTPUT.PUT_LINE(RECRD.EMPNO||RECRD.ENAME||RECRD.SAL||RECRD.DEPTNO||RECRD.DOJ);
DELETE FROM EMP WHERE EMPNO=EMPNUM;
DBMS_OUTPUT.PUT_LINE(‘THE ABOVE RECORD DELETED’);
END;
/
DECLARE
                                             EMPNUMBER NUMBER;
BEGIN
                                             TAB_DIS(&EMPNUMBER);
END;
/


Trigger

CREATE OR REPLACE TRIGGER releaseemp
BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO oldemp(EMPNO, ENAME, SAL, DEPTNO, DOJ, MGR)
VALUES(:old.EMPNO,:old.ENAME,:old.sal,:old.deptno,:old.doj,:old.mgr);
END;


CREATE OR REPLACE TRIGGER emp_insert
after insert ON emp
FOR EACH ROW
Declare
            Dptno dept.deptno%type;
            Cunt number;
BEGIN
            Dptno:=:new.deptno;
            Comit();
            Select noemp into cunt from dept where deptno=dptno;
            If(cunt is null) then
                        Cunt:=1;
            Else
                        Cunt:=cunt+1;
            End if;
            Update dept set noemp=cunt where deptno=dptno;
END;
AUTONOMOUS_TRANSACTION in plsql
Create or replace procedure comit(CUNT IN OUT NUMBER) as PRAGMA AUTONOMOUS_TRANSACTION;
CNT NUMBER;
Begin
                                                                   CNT:=CUNT;
                                                                   Commit;
                                                                   select count(DEPTNO) INTO CUNT from emp where deptno=CNT;
                                                                  
End;
/

CREATE OR REPLACE TRIGGER emp_insert1
after insert ON emp
FOR EACH ROW
Declare
 Dptno dept.deptno%type;
 Cunt number;
BEGIN
 Dptno:=:new.deptno;
 CUNT:=DPTNO;
Comit(CUNT); --uses the AUTONOMOUS_TRANSACTION u cant commit in trigger--

  update dept set noemp= CUNT where deptno=dptno;
END;
/
Insert into emp values(&EMPNO,’&ENAME’,SAL,& DEPTNO,’& DOJ’,& MGR);

Insert into emp values(111,'chandran',null,30,'19-jan-12',101);


To display 5th max value in plsql using cursor
Declare
            Edetail emp%rowtype;
            Cursor empd  is select * from emp ORDER BY SAL DESC;
            Cnt number;
I number;
Begin
            Open empd;
            Select count(*) into cnt from emp;
            I:=1;
            Loop
                        Exit when i>cnt ;
                        Fetch empd into edetail;
                        IF(I=5) THEN
dbms_output.put_line(‘Employee number: ’||edetail.EMPNO||’ Employee name’||edetail. ENAME||’ Employee Salary :’||edetail. SAL||’ Employee works for :’||edetail. DEPTNO||’ Employee Date of joining’||edetail.DOJ||’ Manager ID’||edetail. MGR);
                        END IF;
            i:=i+1;
            end loop;
            close empd;
end;

To display 5th max value in plsql using sql alone
SELECT DISTINCT ename,SAL FROM emp A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
FROM emp B WHERE A.SAL<=B.SAL);
Package example in sql

CREATE OR REPLACE PACKAGE bank_account
IS
                                                                   function addd(n number,n1 number) return number;
                                                                   function addd(CH VARCHAR2,CH1 VARCHAR2) RETURN VARCHAR2
END;
CREATE OR REPLACE PACKAGE BODY bank_account
IS
function addd(n number,n1 number) return number is
                                                                   n3 number;
                                                                   begin
                                                                    N3:=N+N1;
                                                                   RETURN N3;
                END;
function addd(CH VARCHAR2,CH1 VARCHAR2) RETURN VARCHAR2 IS
                                                                    CH2 VARCHAR2(30);
                                                                    BEGIN
                                                                     CH2:=CH||CH1;
                                                                    RETURN CH2;
                END;
END;


Go to session-1
Posted by at 05:14

2 comments:

  1. Anonymous27 February 2012 at 10:25

    Keep Rocking with some more clear picture jaya...

    ReplyDelete
  2. Anonymous18 May 2012 at 05:04

    hi...
    I request you to elaborate your Entertainment link based on some social awareness esp for youths to bring much more brighter country...
    Jai Hind....

    ReplyDelete

Subscribe to: Post Comments (Atom)