reference : http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg09dyn.htm
create or replace procedure salart_raise ( raise_percent number, job varchar2)
is
type loc_array_type is table of varchar2(40)
index by binary_integer;
dml_str varchar2(200);
loc_array loc_array_type;
begin
-- bulk fetch the list of office locations
select location bulk collect into loc_array
from offices;
-- for each location, give a raise to employees with the given 'job'
for i in loc_array.first..loc_array.last loop
dml_str := 'update emp_'||loc_array(i)
|| ' set sal = sal * ( 1 + (:raise_percent/100))'
|| ' where job = :job_title';
execute immediate dml_str using raise_percent, job;
end loop;
end;
create or replace procedure add_location ( loc varchar2) is
begin
-- insert new location in master table
insert into offices values(loc);
-- create an employee information table
execute immediate
'create table '|| ' emp_'||loc||
'(empno number(4) not null,
ename varchar2(10),
job varchar2(9),
sal number(7,2),
deptno number(2)
)';
end;
create or replace procedure drop_location (loc varchar2)
is
begin
-- delete the employee table for location 'loc'
execute immediate 'drop table '||'emp_'||loc;
end;
create or replace function get_num_of_employees ( loc varchar2, job varchar2)
return number
is
query_str varchar2(1000);
num_of_employees number;
begin
query_str := 'select count(*) from '
|| ' emp_'||loc
|| ' where job = :job_title';
execute immediatequery_str
into num_of_employees
using job;
return num_of_employees;
end;
create or replace procedure lost_employees(loc varchar2, job varchar2)
is
type cur_typ is ref cursor;
c cur_typ;
query_str varchar2(1000);
emp_name varchar2(20);
emp_num number;
begin
query_str := 'select ename, empno, from emp_'||loc
|| ' where job = :job_title';
-- find employees who perform the specified job
open c for query_str using job;
loop
fetch c into emp_name, emp_num;
exit when c%NOTFOUND;
-- process row here;
end loop;
close c;
end;
-- DBMS_SQL Package
create procedure insert_into_table (
table_name varchar2,
deptnumber number,
deptname varchar2,
location varchar2)
is
cur_hdl integer;
stmt_str varchar2(200);
rows_processed binary_integer;
begin
stmt_str := 'insert into '||
table_name|| ' values
(:deptno, :dname, :loc)';
--open cursor
cur_hdl : = dbms_sql.open_cursor;
--parse cursor
dbms_sql.parse(cur_hdl, stmt_str,
dbms_sql.native);
-- supply binds
dbms_sql.bind_variable
(cur_hdl, ':deptno', deptnumber);
dbms_sql.bind_variable
(cur_hdl, ':dname', deptname);
dbms_sql.bind_variable
(cur_hdl, ':loc', location);
-- execute cursor
rows_processed :=
dbms_sql.execute(cur_hdl);
-- close cursor
dbms_sql.close_cursor(cur_hdl);
end;
create procedure insert_into_table(
table_name varchar3,
deptnumber number,
deptname varchar2,
location varchar2)
is
stmt_str varchar2(200);
begin
stmt_str := 'insert into '||
table_name || ' values
(:deptno, :dname, :loc)';
execute immediate stmt_str
using deptnumber, deptname, location;
end;
create or replace procedure del_dept (
my_deptno dept.deptno%type
)
is
begin
execute immediate 'delete from dept where deptno = ' ||to_char(my_deptno);
end;
create or replace procedure del_dept (
my_deptno dept_deptno%type
)
is
begin
execute immediate 'delete from dept where deptno = :1' using my_deptno;
end;
declare
type empcurtyp is ref cursor;
c empcurtyp;
emp_rec emp%rowtype;
stmt_str varchar2(200);
e_job emp.job%type;
begin
stmt_str := 'select * from emp where job = :1';
-- in a multi-row query
open c for stmt_str using 'manager';
loop
fetch c into emp_rec;
exit when c%notfound;
end loop;
close c;
-- in a single-row query
execute immediate stmt_str into emp_rec using 'president';
end;