8.0 Features
Returning into clause:Used to return the values thru " DML" stmts.Used with update and delete stmts.>var a varchar2(20)>var b number>update emp set sal = sal + 3000 where empno = 7900returning ename,sal into :a,:b;>print a b>delete from emp where empno = 7902returning 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>declaretype 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 transactionselect ename,sal bulk collect into n,p from emp;-- printing table contentsdbms_output.put_line('EMPLOY DETAILS ARE :');for i in 1 .. n.count loopdbms_output.put_line(n(i)||' '||p(i));end loop;end;* update emp set sal = sal + 3000 where deptno = 30returning ename,sal bulk collect into n,p;* delete from emp where job = 'CLERK'returning ename,sal bulk collect into n,p;----------------------------------------------------------------------------
declaretype 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;beginopen c1;fetch c1 bulk collect into n,p;-- printing table contentsfor i in 1 .. n.count loopdbms_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 ');>beginexecute immediate(' create table employ1(ecode number(4), ename varchar2(20),sal number(10))');end;Note: Table cannot be manipulated in same pl/sql blockbeginexecute immediate('drop table employ1');end;----------------------------------------------------------------------------
