8.0 Features
Returning into clause:
Used to return the values thru " DML" stmts.
Used with update and delete stmts.
Ex:
>var a varchar2(20)
>var b number
>update emp set sal = sal + 3000 where empno = 7900
returning ename,sal into :a,:b;
>print a b
>delete from emp where empno = 7902
returning ename,sal into :a,:b;
>print a b
----------------------------------------------------------------------------
* Bulk Collect:
Used to return bulk data into pl/sql variables.
Variables must be of pl/sql table type only.
Improves performance while retrieving data.
Used with select, update, delete, Fetch stmts.
select ename,sal into a,b from emp where empno = &ecode;
ecode : 101
>declare
type names is table of emp.ename%type index by binary_integer;
type pays is table of emp.sal%type index by binary_integer;
n names; p pays;
begin
-- retrieving all employees in 1 transaction
select ename,sal bulk collect into n,p from emp;
-- printing table contents
dbms_output.put_line('EMPLOY DETAILS ARE :');
for i in 1 .. n.count loop
dbms_output.put_line(n(i)||' '||p(i));
end loop;
end;
* update emp set sal = sal + 3000 where deptno = 30
returning ename,sal bulk collect into n,p;
* delete from emp where job = 'CLERK'
returning ename,sal bulk collect into n,p;
----------------------------------------------------------------------------
Using in Fetch stmt :
declare
type names is table of emp.ename%type index by binary_integer;
type pays is table of emp.sal%type index by binary_integer;
n names; p pays;
cursor c1 is select ename,sal from emp;
begin
open c1;
fetch c1 bulk collect into n,p;
-- printing table contents
for i in 1 .. n.count loop
dbms_output.put_line(n(i)||' '||p(i));
end loop;
end;
----------------------------------------------------------------------------
Dynamic SQL:
Supports to execute " DDL" stmts in Pl/sql block.
syntax: execute immediate(' DDL stmt ');
>begin
execute immediate(' create table employ1
(ecode number(4), ename varchar2(20),sal number(10))');
end;
Note: Table cannot be manipulated in same pl/sql block
begin
execute immediate('drop table employ1');
end;
----------------------------------------------------------------------------
