Oracle PLSQL Examples

Hello Friends ,

Below are Some PLSQL Sample Anonymous and named Block Program for your quick pick.

Please ask if you have any question Related to PLSQL.

*************Program to print the string on to the screen*****************************
DECLARE
v_name VARCHAR2(100):='&Name';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;
/
*************Program to print the string on to the screen*****************************
*************Program to find out the the odd/Even Number******************************
DECLARE
V_NUM NUMBER:=#
BEGIN
IF MOD(V_NUM,2)=0 THEN
DBMS_OUTPUT.PUT_LINE('THE NUMBER ENTERED'||V_NUM||' is an EVEN Number');
ELSE
DBMS_OUTPUT.PUT_LINE('THE NUMBER ENTERED'||V_NUM||' is an ODD Number');
END IF;
END;
/
*************Program to find out the the odd/Even Number******************************
*************Program to find out the the greater/lesses Number************************
DECLARE
V_NUM1 NUMBER:=&NUM1;
V_NUM2 NUMBER:=&NUM2;
BEGIN
IF V_NUM1>V_NUM2 THEN
DBMS_OUTPUT.PUT_LINE('THE NUMBER '||V_NUM1||'is greater than '||V_NUM2);
ELSE
      IF V_NUM2>V_NUM1 THEN
      DBMS_OUTPUT.PUT_LINE('THE NUMBER '||V_NUM2||'is greater than '||V_NUM1);
      ELSE
      DBMS_OUTPUT.PUT_LINE('THE NUMBER '||V_NUM1||'is equal to '||V_NUM2);
      END IF;
END IF;
END;
/
*************Program to find out the the greater/lesses Number************************
*************Program to Swap the Two numbers******************************************
DECLARE
V_NUM1 NUMBER:=&NUM1;
V_NUM2 NUMBER:=&NUM2;
V_TEMP NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('THE NUMBER ENTERED By YOU are FIRST Number'||V_NUM1||'and Second Number'||V_NUM2);
IF V_NUM1 IS NOT NULL AND V_NUM2 IS NOT NULL THEN
V_TEMP:=V_NUM1;
V_NUM1:=V_NUM2;
V_NUM2:=V_TEMP;
END IF;
DBMS_OUTPUT.PUT_LINE('THE Swapped NUMBER are'||V_NUM1||'and Second Number'||V_NUM2);
END;
/
*************Program to Swap the Two numbers******************************************
*************Program to find out the Leap Year****************************************
DECLARE
V_YEAR NUMBER:=&V_YEAR;
BEGIN
DBMS_OUTPUT.PUT_LINE('YOU HAVE ENTERED THE YEAR'||V_YEAR);
IF MOD(V_YEAR,4)=0 THEN
      IF MOD(V_YEAR,100)<>0 THEN
         DBMS_OUTPUT.PUT_LINE('The year is a Leap Year');
      ELSE
          IF  MOD(V_YEAR,400)=0 THEN
          DBMS_OUTPUT.PUT_LINE('The year is a Leap Year');
          ELSE
          DBMS_OUTPUT.PUT_LINE('The year is NOT a Leap Year');
          END IF;
         
      END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('The year is NOT a Leap Year');
END IF;
END;
/
*************Program to find out the Leap Year****************************************
*************Program to Print out the No and sample example for Basic Loop ***********
DECLARE
V_NUM NUMBER :=1;
BEGIN
LOOP
V_NUM:=V_NUM+1;
IF  V_NUM>10 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The output is'||V_NUM);
END;
/
*************Program to Print out the No and sample example for Basic Loop ***********
*************Program to Print out the No and sample example for WHILE Loop ***********
DECLARE
V_NUM NUMBER :=1;
V_OUT VARCHAR2(100);
BEGIN
WHILE V_NUM<10
LOOP
V_OUT:=V_OUT||V_NUM;
V_NUM:=V_NUM+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The output is'||V_OUT);
END;
/
*************Program to Print out the No and sample example for WHILE Loop ***********
*************Program to Print out the No and sample example for FOR Loop *************
DECLARE
V_NUM NUMBER :=1;
BEGIN
FOR I IN REVERSE 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
/
*************Program to Print out the No and sample example for FOR Loop *************
*************Program to find out the Factorial****************************************
DECLARE
V_NUM NUMBER :=&NUM;
V_FACT NUMBER:=1;
BEGIN
FOR I IN REVERSE 1..V_NUM
LOOP
V_FACT:=V_FACT*I;
DBMS_OUTPUT.PUT_LINE('THE Index is '||I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE output is '||V_FACT);
END;
/
*************Program to find out the Factorial****************************************
Declare
v_empno EMP.EMPNO%Type:=&EMPLOYEE_NUMBER;
v_Ename EMP.ENAME%TYPE;
v_SAL   EMP.SAL%TYPE;
v_Job   EMP.JOB%TYPE;
BEGIN
SELECT ename,sal,job into v_Ename,v_SAL,v_Job  from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||v_empno||' '||'is'|| v_Ename);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| v_sal);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| v_job);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
-----------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
Declare
v_empno EMP.EMPNO%Type:=&EMPLOYEE_NUMBER;
v_Ename EMP.ENAME%TYPE;
v_SAL
EMP.SAL%TYPE;
v_Job
EMP.JOB%TYPE;
BEGIN
SELECT ename,sal,job into v_Ename,v_SAL,v_Job from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||v_empno||' '||'is'||
v_Ename);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| v_sal);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| v_job);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||
' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
-----------------------------------------------------------------------------
------
Declare
v_empno EMP.EMPNO%Type:=&EMPLOYEE_NUMBER;
v_emp_rec EMP%ROWTYPE;
BEGIN
SELECT * into v_emp_rec from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||v_empno||' '||'is'||
v_emp_rec.Ename);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| v_emp_rec.sal);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| v_emp_rec.job);
DBMS_OUTPUT.PUT_LINE('His Deptno is'||' '|| v_emp_rec.deptno);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||
' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
-------------------------------Records Example----------------------------------------------

DECLARE
v_empno NUMBER:=&EMPLOYEE_NUMBER;
TYPE employee_type IS RECORD
(employee_last_name emp.ename%type,
 employee_dept dept.deptno%type,
 employee_salary emp.sal%type
 );
employee_rec employee_type;
BEGIN
SELECT ename,deptno,sal into employee_rec from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||' '||'is'||
employee_rec.employee_last_name);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| employee_rec.employee_salary);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| employee_rec.employee_dept);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||
' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
----------------------------
DECLARE
V_ROW_COUNT NUMBER;
BEGIN
Update emp set sal=sal+100;
IF SQL%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('THE CURSOR IS OPEN');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no Record got updated');
ELSIF SQL%FOUND THEN
V_ROW_COUNT:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(V_ROW_COUNT||' '||'No of Record got updated');
END IF;
END;

select * from emp

DECLARE
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
SELECT ENAME,SAL into V_Ename,V_SAL FROM EMP Where EMPNO=10001;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);

END;
/
----------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP WHERE empno=10001;
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
OPEN C1;
FETCH C1 INTO V_ENAME,V_SAL ;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
CLOSE C1;
END;
-----------------------------------------------------
DECLARE
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
SELECT ENAME,SAL into V_Ename,V_SAL FROM SCOTT.EMP Where SAL>800;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
END;
/
----------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL>800;
V_SAL SCOTT.EMP.SAL%TYPE;
V_Ename SCOTT.EMP.Ename%Type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_ENAME,V_SAL ;
EXIT WHEN C1%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
END LOOP;
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
END;
/
-----------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL>800;
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
OPEN C1;
FETCH C1 INTO V_ENAME,V_SAL ;
WHILE C1%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
FETCH C1 INTO V_ENAME,V_SAL ;
END LOOP;
CLOSE C1;
END;
/
--------------------------------------------------------------
DECLARE

CURSOR C1 IS SELECT * FROM SCOTT.EMP WHERE SAL>800;
BEGIN
FOR I IN C1
Loop
DBMS_OUTPUT.PUT_LINE(I.ENAME||' '||'has sal '||I.SAL||'of Department '||
I.deptno);
END LOOP;
END;
/
-----------------------------------------------------------------------------
---------
DECLARE
CURSOR C1 IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM SCOTT.EMP;
EMP_REC C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EMP_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.Empno||' ,'||EMP_REC.Ename||', '||EMP_REC.SAL||
','||EMP_REC.DEPTNO);
END LOOP;
Close C1;
END;
/
---------------
DECLARE
CURSOR C1 IS SELECT * FROM SCOTT.EMP;
--EMP_REC C1%ROWTYPE;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.Empno||' ,'||I.Ename||', '||I.SAL||','||I.DEPTNO);
END LOOP;
END;
-------------------------------------------------------------------------
DECLARE
CURSOR C1(p_deptno Number) IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM SCOTT.EMP where deptno=p_deptno;
EMP_REC C1%ROWTYPE;
BEGIN
OPEN C1(&deptno);
LOOP
FETCH C1 INTO EMP_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.Empno||' ,'||EMP_REC.Ename||', '||EMP_REC.SAL||
','||EMP_REC.DEPTNO);
END LOOP;
Close C1;
END;
/
---------------------------------------------------------------------------------
DECLARE
CURSOR C1(p_deptno Number) IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM SCOTT.EMP where deptno=p_deptno FOR UPDATE OF SAL;
EMP_REC C1%ROWTYPE;
BEGIN
OPEN C1(&deptno);
LOOP
FETCH C1 INTO EMP_REC;
Update SCOTT.EMP SET SAL=EMP_REC.SAL+EMP_REC.SAL*1.10 where Current of C1;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.Empno||' ,'||EMP_REC.Ename||', '||EMP_REC.SAL||
','||EMP_REC.DEPTNO);
END LOOP;
COmmit;
Close C1;
END;
/
--------------------------------------------------------------------------------------------------------
Declare
v_empno EMP.EMPNO%Type:=&EMPLOYEE_NUMBER;
v_Ename EMP.ENAME%TYPE;
v_SAL   EMP.SAL%TYPE;
v_Job   EMP.JOB%TYPE;
BEGIN
SELECT ename,sal,job into v_Ename,v_SAL,v_Job  from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||v_empno||' '||'is'|| v_Ename);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| v_sal);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| v_job);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
-----------------------------------------------------------------------------------
Declare
v_empno EMP.EMPNO%Type:=&EMPLOYEE_NUMBER;
v_emp_rec EMP%ROWTYPE;
BEGIN
SELECT * into v_emp_rec  from emp where empno=v_empno;
DBMS_OUTPUT.PUT_LINE('The details of Employee'||v_empno||' '||'is'|| v_emp_rec.Ename);
DBMS_OUTPUT.PUT_LINE('His Salary is'||' '|| v_emp_rec.sal);
DBMS_OUTPUT.PUT_LINE('His Job is'||' '|| v_emp_rec.job);
DBMS_OUTPUT.PUT_LINE('His Deptno is'||' '|| v_emp_rec.deptno);
Exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||' '||'PLZ TRY AGAIN WITH VALID EMPLOYEE NO');
END;
---------------------------------------------------------------------------------------------------------
DECLARE
V_ROW_COUNT NUMBER;
BEGIN
Update emp set sal=sal+100;
IF SQL%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('THE CURSOR IS OPEN');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no Record got updated');
ELSIF SQL%FOUND THEN
V_ROW_COUNT:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(V_ROW_COUNT||' '||'No of Record got updated');
END IF;
END;

select * from emp

DECLARE
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
SELECT ENAME,SAL into V_Ename,V_SAL  FROM EMP  Where EMPNO=10001;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
END;
/
----------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP WHERE empno=10001;
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
OPEN C1;
FETCH C1 INTO V_ENAME,V_SAL ;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
CLOSE C1;
END;
-----------------------------------------------------
DECLARE
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
SELECT ENAME,SAL into V_Ename,V_SAL  FROM EMP  Where SAL>800;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
END;
/
----------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP WHERE SAL>800;
V_SAL EMP.SAL%TYPE;
V_Ename EMP.Ename%Type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_ENAME,V_SAL ;
EXIT WHEN C1%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
END LOOP;
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
END;
/
-----------------------------------------------
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP WHERE SAL>800;
V_SAL NUMBER;
V_Ename Varchar(50);
BEGIN
OPEN C1;
FETCH C1 INTO V_ENAME,V_SAL ;
WHILE C1%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||'has sal '||V_SAL);
FETCH C1 INTO V_ENAME,V_SAL ;
END LOOP;
CLOSE C1;
END;
/
--------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT * FROM EMP WHERE SAL>800;
BEGIN
FOR I IN C1
Loop
DBMS_OUTPUT.PUT_LINE(I.ENAME||' '||'has sal '||I.SAL||'of Department '||I.deptno);
END LOOP;
END;
/
--------------------------------------------------------------------------------------
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
EMP_REC C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EMP_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.Empno||' ,'||EMP_REC.Ename||', '||EMP_REC.SAL||','||EMP_REC.DEPTNO);
END LOOP;
Close C1;
END;
/---------------
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
--EMP_REC C1%ROWTYPE;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.Empno||' ,'||I.Ename||', '||I.SAL||','||I.DEPTNO);
END LOOP;
END;
--------------------Ref Cursor Example---------------------------------
Declare
Type empdept_ref is Ref Cursor;
v_empdept_ref empdept_ref;
Type e_rec is Record
( r_empno Number
 ,r_ename Varchar2(30)
 ,r_sal Number
);
v_erec e_rec;
Type d_rec is Record
( r_empno Number
 ,r_ename Varchar2(30)
 ,r_sal Number
 ,r_deptno number
 ,r_dname varchar2(30)
 ,r_loc  varchar2(50)
);
v_drec d_rec;
v_dept dept%rowtype;
Begin
        Begin
        Open v_empdept_ref for select empno,ename,sal from emp;
        Loop
        fetch v_empdept_ref into v_erec;
        exit when v_empdept_ref%notfound;
        dbms_output.Put_line(v_erec.r_empno||' ' ||v_erec.r_ename||' '||v_erec.r_sal);
        End loop;
        End;
       
        Begin
        Open v_empdept_ref for select * from dept;
        Loop
        fetch v_empdept_ref into v_dept;
        exit when v_empdept_ref%notfound;
        dbms_output.Put_line(v_dept.deptno||' ' ||v_dept.dname||' '||v_dept.loc);
        End loop;
        End;
       
        Begin
        Open v_empdept_ref for select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e,dept d
        where e.deptno(+)=d.deptno;
        Loop
        fetch v_empdept_ref into v_drec;
        exit when v_empdept_ref%notfound;
        dbms_output.Put_line(v_drec.r_empno||' ' ||v_drec.r_ename||' '||v_drec.r_sal
        ||' '||v_drec.r_deptno||' '|| v_drec.r_dname||v_drec.r_loc);
        End loop;
        End;
       
 close v_empdept_ref;
 END;



----------------------------------userdefined Exception--------------------------------------------------------
 DECLARE
 SAL_COMM EXCEPTION;
 V_SAL NUMBER;
V_EMPNO NUMBER:=&EMPLOYEE_NUMBER;
 BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO=V_EMPNO;
dbms_output.put_line('THE SAL is '||V_SAL);
   if v_sal<1000 then
    raise SAL_COMM ;
   end if;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('The Employee'||' '||v_empno||' '||'does not exist'||' '||'PLZ TRY AGAIN');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
 WHEN  SAL_COMM THEN
 DBMS_OUTPUT.PUT_LINE('He is eligable for commission');
update emp set comm=500 where EMPNO=V_EMPNO;
 commit;
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
 END;
/
----------------------------------
Collection
SQL> Declare
2  typearrtype is table of dept.dname%type INDEX BY BINARY_INTEGER;
  3   p arrtype;
  4   Begin
  5   for i in 1 .. 4
  6   loop
  7   select dname into p(i) from dept where deptno=i*10;
  8   end loop;
  9   For i IN p.First .. P.LAST
 10   Loop
 11   dbms_output.put_line(P(i));
 12   End Loop;
 13   END;
 14   /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------
SQL>  declare
  2   type arrtype is table of dept.dname%type INDEX BY BINARY_INTEGER;
  3   p arrtype;
  4   BEGIN
5  SELECTdname  bulk collect   into P FROM dept;
6  Fori IN p.First .. P.LAST
7  LOOP
8  dbms_output.put_line(P(i));
9  END LOOP;
10  END;
11  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS
HR
MARKETING
bis

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------
SQL> Declare
  2   type arrtype is table of dept.dname%type INDEX BY BINARY_INTEGER;
  3   p arrtype;
  4   a number;
5  Begin
6  SELECTdname  bulk collect   into P FROM dept;
7  a:=P.FIRST;
  8   dbms_output.put_line(a);
9  while (a<=p.last)
10  loop
11  dbms_output.put_line(p(a));
12  a:=p.next(a);
13  end loop;
14  end;
15  /
1
ACCOUNTING
RESEARCH
SALES
OPERATIONS
HR
MARKETING
bis

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------
SQL> Declare
  2   type arrtype is table of dept.dname%type INDEX BY BINARY_INTEGER;
  3   p arrtype;
  4   a number;
5  Begin
6  SELECTdname  bulk collect   into P FROM dept;
7  a:=P.FIRST;
8  while (a<=p.last)
9  loop
10  dbms_output.put_line(a);
 11   dbms_output.put_line(p(a));
 12   a:=p.next(a);
 13   end loop;
 14   end;
 15   /
1
ACCOUNTING
2
RESEARCH
3
SALES
4
OPERATIONS
5
HR
6
MARKETING
7
8
bis

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------
SQL>  Declare
  2   type arrtype is table of emp%ROWTYPE INDEX BY BINARY_INTEGER;
  3   p arrtype;
  4   BEGIN
  5   Select * bulk collect into p from emp;
  6   FOR I IN P.FIRST .. P.LAST
  7   LOOP
  8  dbms_output.put_line(P(i).EMPNO||' '||P(i).ENAME||' '||P(i).SAL||' '||P(i).JOB||' '||P(i).DEPTNO);
  9   END LOOP;
 10   END;
 11   /
7369 SMITH 1000 CLERK 20
7499 ALLEN 1800 SALESMAN 30
7521 WARD 1450 SALESMAN 30
7566 JONES 3175 MANAGER 20
7654 MARTIN 1450 SALESMAN 30
7698 BLAKE 3050 MANAGER 30
7782 CLARK 2650 MANAGER 10
7788 SCOTT 3200 ANALYST 20
7839 KING 5200 PRESIDENT 10
7844 TURNER 1700 SALESMAN 30
7876 ADAMS 1300 CLERK 20
7900 JAMES 1150 CLERK 30
7902 FORD 3200 ANALYST 20
7934 MILLER 1500 CLERK 10
1234 Aditi
4321 UTKARSH

PL/SQL procedure successfully completed.

-----------------------------------------------------------------------------------------------------------------------------------------
SQL>  Declare
2  typedept_rec is record
3  (deparmentno DEPT.DEPTNO%TYPE,
4  DEPTNAME varchar2
5  (20),lOCATION VARCHAR2(30));
6  TYPEarrtype is table of dept_rec INDEX BY BINARY_INTEGER;
7  parrtype;
8  BEGIN
9  Select * bulk collect into p from DEPT;
10  FOR I IN P.FIRST .. P.LAST
11  LOOP
12  dbms_output.put_line(P(i).deparmentno||' '||P(i).DEPTNAME||' '||P(i).lOCATION);
13  end loop;
14  end;
15  /
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 HR NOIDA
60 MARKETING Bangalore
70  Kolkata
80 bis

PL/SQL procedure successfully completed.