Dozens of practical pl/sql (3)

Phase III

Q. Use a REF CURSOR to display the values in the EMP table.

A.

DECLARE

TYPE Emprectyp is record

(

EMPNO Emp.empno%type,

Ename Emp.ename%type,

JOB Emp.job%type,

MGR Emp.mgr%type,

HireDate Emp.hiredate%type,

SAL Emp.sal%type,

COMM Emp.comm%type,

DEPTNO Emp.deptno%type

);

TYPE Emp_cursor is REF cursor return emp%rowtype;

Vemp_cur Emp_cursor;

Vemp_rec Emprectyp;

BEGIN

OPEN vemp_cur for SELECT * from EMP;

LOOP

FETCH vemp_cur into Vemp_rec;

EXIT when Vemp_cur%notfound;

Dbms_output.  Put (vemp_rec.empno| | ' '|| vemp_rec.ename| | ' '|| Vemp_rec.job);

Dbms_output.  Put (vemp_rec.mgr| | ' '|| vemp_rec.hiredate| | ' '|| Vemp_rec.sal);

Dbms_output.  Put_Line (vemp_rec.comm| | ' '|| VEMP_REC.DEPTNO);

End LOOP;

Close vemp_cur;

End;

/

Q. Get the value from "EMP" to the Pl/sql table, increase the salary value in the Pl/sql table by 500 and show the user the increased salary and other details.

A.

DECLARE

TYPE Emprec is record

(

EMPNO Emp.empno%type,

Ename Emp.ename%type,

JOB Emp.job%type,

MGR Emp.mgr%type,

HireDate Emp.hiredate%type,

SAL Emp.sal%type,

COMM Emp.comm%type,

DEPTNO Emp.deptno%type

);



I binary_integer:=1;



TYPE Emp_tab is TABLE of Emprec INDEX by Binary_integer;

Vemp Emp_tab;



CURSOR C1 is a SELECT * from EMP;

BEGIN

For X in C1

LOOP

Vemp (i). Empno:=x.empno;

Vemp (i). Ename:=x.ename;

Vemp (i). Job:=x.job;

Vemp (i). Mgr:=x.mgr;

Vemp (i). Hiredate:=x.hiredate;

Vemp (i). sal:=x.sal+500;

Vemp (i). Comm:=x.comm;

Vemp (i). Deptno:=x.deptno;

i:=i+1;

End LOOP;



For J in 1..i-1

LOOP

Dbms_output.  Put (Vemp (j). empno| | ' '|| Vemp (j). ename| | ' '|| Vemp (j). Job);

Dbms_output.  Put (Vemp (j). mgr| | ' '|| Vemp (j). hiredate| | ' '|| Vemp (j). Sal);

Dbms_output.  Put_Line (Vemp (j). comm| | ' '|| Vemp (j). Deptno);



End LOOP;

End;

/

Q. Once the value is sent to the Pl/sql table, try inserting a new record in the Pl/sql table and deleting some existing records.

A.

DECLARE

TYPE Emprec is record

(

EMPNO Emp.empno%type,

Ename Emp.ename%type,

JOB Emp.job%type,

MGR Emp.mgr%type,

HireDate Emp.hiredate%type,

SAL Emp.sal%type,

COMM Emp.comm%type,

DEPTNO Emp.deptno%type

);



I binary_integer:=1;



TYPE Emp_tab is TABLE of Emprec INDEX by Binary_integer;

Vemp Emp_tab;



CURSOR C1 is a SELECT * from EMP;

BEGIN

For X in C1

LOOP

Vemp (i). Empno:=x.empno;

Vemp (i). Ename:=x.ename;

Vemp (i). Job:=x.job;

Vemp (i). Mgr:=x.mgr;

Vemp (i). Hiredate:=x.hiredate;

Vemp (i). Sal:=x.sal;

Vemp (i). Comm:=x.comm;

Vemp (i). Deptno:=x.deptno;

i:=i+1;

End LOOP;

--for J-in 1..i-1

--LOOP

--Dbms_output.  Put (Vemp (j). empno| | ' '|| Vemp (j). ename| | ' '|| Vemp (j). Job);

--Dbms_output.  Put (Vemp (j). mgr| | ' '|| Vemp (j). hiredate| | ' '|| Vemp (j). Sal);

--Dbms_output.  Put_Line (Vemp (j). comm| | ' '|| Vemp (j). Deptno);



--End LOOP;



--Inserting records

Dbms_output. Put_Line (' Insert record: ');

Vemp (i). empno:=1000;

Vemp (i) ename:= ' Goldens ';

Vemp (i) job:= ' Software ';

Vemp (i). Mgr:=null;

Vemp (i) hiredate:= ' 2003-01-04 ';

Vemp (i). sal:=8888;

Vemp (i). comm:=10;

Vemp (i). deptno:=10;



For J in 1..i

LOOP

Dbms_output.  Put (Vemp (j). empno| | ' '|| Vemp (j). ename| | ' '|| Vemp (j). Job);

Dbms_output.  Put (Vemp (j). mgr| | ' '|| Vemp (j). hiredate| | ' '|| Vemp (j). Sal);

Dbms_output.  Put_Line (Vemp (j). comm| | ' '|| Vemp (j). Deptno);



End LOOP;

--Delete 5th, 6 records

Dbms_output. Put_Line (' Delete 5th, 6 records: ');

For J in 5..i-2

LOOP

Vemp (j). Empno:=vemp (j+2). Empno;

Vemp (j). Ename:=vemp (j+2). ename;

Vemp (j). Job:=vemp (j+2). Job;

Vemp (j). Mgr:=vemp (j+2). Mgr;

Vemp (j). Hiredate:=vemp (j+1). HireDate;

Vemp (j). Sal:=vemp (j+2). Sal;

Vemp (j). Comm:=vemp (j+2). Comm;

Vemp (j). Deptno:=vemp (j+2). Deptno;

End LOOP;

Vemp (i-1). Empno:=null;

Vemp (i-1). Ename:=null;

Vemp (i-1). Job:=null;

Vemp (i-1). Mgr:=null;

Vemp (i-1). Hiredate:=null;

Vemp (i-1). Sal:=null;

Vemp (i-1). Comm:=null;

Vemp (i-1). Deptno:=null;

Vemp (i). Empno:=null;

Vemp (i). Ename:=null;

Vemp (i). Job:=null;

Vemp (i). Mgr:=null;

Vemp (i). Hiredate:=null;

Vemp (i). Sal:=null;

Vemp (i). Comm:=null;

Vemp (i). Deptno:=null;



For J in 1..i-2

LOOP

Dbms_output.  Put (Vemp (j). empno| | ' '|| Vemp (j). ename| | ' '|| Vemp (j). Job);

Dbms_output.  Put (Vemp (j). mgr| | ' '|| Vemp (j). hiredate| | ' '|| Vemp (j). Sal);

Dbms_output.  Put_Line (Vemp (j). comm| | ' '|| Vemp (j). Deptno);



End LOOP;





End;

/