Sunday 28 October 2012

pl sql record and pl sql table simple example



pl sql table and record example withe clear explanation:
=======================================

create or replace procedure plsql_table_ex_proc is
/*type empno is table of emp.empno%type index by binary_integer;
type ename is table of emp.ename%type index by binary_integer;
type job is table of emp.job%type index by binary_integer;
type sal is table of emp.sal%type index by binary_integer;
type deptno is table of dept.deptno%type index by binary_integer;
type grade is table of salgrade.grade%type index by binary_integer;
*/
type sample_rec is record
(
v_empno emp.empno%type,
v_ename emp.ename%type,
v_job emp.job%type ,
v_sal emp.sal%type,
v_deptno dept.deptno%type,
v_grade salgrade.grade%type
);
type sample_tab is table of sample_rec index by binary_integer;
data_table sample_tab;
cursor data_cur is select empno,ename,job,sal,d.deptno,grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno
and (e.sal between s.losal and s.hisal);
begin
open data_cur;
fetch data_cur bulk collect into data_table;
forall i in 1..data_table.count
insert into (select * from emp_dept_salgrade) values data_table(i);
dbms_output.put_line('no of records inserted : '||data_table.count);
close data_cur;
select empno,ename,job,sal,d.deptno,grade
bulk collect into data_table
from emp e,dept d,salgrade s
where e.deptno=d.deptno
and (e.sal between s.losal and s.hisal);
for i in 1..data_table.count
loop
insert into emp_dept_salgrade(empno,ename,sal,job,deptno,grade)
values ( data_table(i).v_empno,data_table(i).v_ename,data_table(i).v_sal,data_table(i).v_job,
data_table(i).v_deptno,data_table(i).v_grade);
end loop;
dbms_output.put_line('no of records inserted : '||data_table.count);
end;
/

No comments:

Post a Comment