很简单的一个例子
declare
TYPE cur_typ IS REF CURSOR;
v_inv_cursor cur_typ;
v_tabName varchar2(20);
v_sql varchar2(2000);
v_count number(8, 2);
cursor c is
select table_name from user_tables;
begin
open c;
loop
fetch c
into v_tabName;
exit when c%notfound;
v_sql := 'select count(*) num from ' || v_tabName;
open v_inv_cursor for v_sql;
loop
fetch v_inv_cursor
into v_count;
exit when v_inv_cursor%notfound;
dbms_output.put_line(v_count);
end loop;
close v_inv_cursor;
end loop;
close c;
end;
------
select * from dept;
select * from emp;declare
sql_stmt varchar2(200);
type id_table is table of integer;
type name_tables is table of varchar2(8);
t_empno id_table:=id_table(9001,9002,9003,9004,9005);
t_empname name_tables:=name_tables('张三','李四','王五','赵六','何七');
v_deptno number(2):=30;
v_department_name varchar2(20):='南京';
emp_rec emp%rowtype;
begin
emp_rec.employee_id:=9001;
emp_rec.first_name:='simens';
emp_rec.last_name:='bai';
emp_rec.email:='baixyu';
emp_rec.phone_number:='123.123.1232';
emp_rec.hire_date:=trunc(sysdate);
emp_rec.job_id:='AD_VP';
emp_rec.salary:=90000;
emp_rec.commission_pct:=null;
emp_rec.manager_id:=100;
emp_rec.department_id:=90;
sql_stmt:='update dept set department_name=:1 where department_id=:2';
execute immediate sql_stmt using v_department_name,v_deptno;
sql_stmt:='create table emp_name_tab(empno number,empname varchar2(20))';
execute immediate sql_stmt;
sql_stmt:='insert into emp_name_tab values(:1,:2)';
for i in t_empno.first .. t_empno.last loop
execute immediate sql_stmt using t_empno(i),t_empname(i);
end loop;
-- sql_stmt:='insert into emp values :1';
--execute immediate sql_stmt using emp_rec;
end;
select * from dept;
select * from emp_name_tab;
动态sql---查询多行--没有使用批量,只能循环,下面有个使用批量的效率高
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
v_deptno number(4) := '&deptno';
v_empno number(4);
v_ename varchar2(25);
begin
open emp_cur for 'select employee_id,first_name from emp where department_id=:1'
using v_deptno;
loop
fetch emp_cur
into v_empno, v_ename;
exit when emp_cur%notfound;
dbms_output.put_line(v_empno);
dbms_output.put_line(v_ename);
end loop;
close emp_cur;
exception
when others then
if emp_cur%found then
close emp_cur;
end if;
dbms_output.put_line('error:'||substr(sqlerrm,1,200));
end;
--dml中使用bulk
declare
type ename_table_type is table of varchar2(20) index by binary_integer;
type sal_table_type is table of number(10,2) index by binary_integer;
ename_tab ename_table_type;
sal_tab sal_table_type;
v_deptno number(4):=20;
v_percent number(4,2):=0.12;
sql_stmt varchar2(500);
begin
sql_stmt:='update hr.employees set salary=salary*(1+:percent) where department_id=:deptno returning first_name,salary into :ename,:salary';
execute immediate sql_stmt using v_percent,v_deptno returning bulk collect into ename_tab,sal_tab;
for i in 1 .. ename_tab.count loop
dbms_output.put_line(ename_tab(i)||':'||sal_tab(i));
end loop;
end;
--select中使用bulk
declare
type ename_table_type is table of varchar2(20) index by binary_integer;
ename_tab ename_table_type;
v_deptno number(4):='&deptno';
sql_stmt varchar2(500);
begin
sql_stmt:='select first_name from hr.employees where department_id=:1';
execute immediate sql_stmt bulk collect into ename_tab using v_deptno;
for i in 1 .. ename_tab.count loop
dbms_output.put_line(ename_tab(i));
end loop;
end;
--游标fetch中使用bulk
declare
type ename_table_type is table of varchar2(20) index by binary_integer;
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
ename_tab ename_table_type;
v_deptno number(4) := '&deptno';
sql_stmt varchar2(500);
begin
open emp_cur for 'select first_name from hr.employees where department_id=:1' using v_deptno;
fetch emp_cur bulk collect into ename_tab;
close emp_cur;
for i in 1 .. ename_tab.count loop
dbms_output.put_line(ename_tab(i));
end loop;
end;
--dml使用forall
create or replace procedure tt
authid current_user --定义调用者权限
as
type ename_table_type is table of varchar2(20) index by binary_integer;
type sal_table_type is table of number(10, 2) index by binary_integer;
type empno_table_type is table of number(4);
ename_tab ename_table_type;
sal_tab sal_table_type;
empno_tab empno_table_type;
v_percent number(4, 2) := 0.12;
sql_stmt varchar2(500);
begin
empno_tab := empno_table_type(2134, 2214, 143);
sql_stmt := 'update hr.employees set salary=salary*(1+:percent) where employee_id=:empno returning first_name,salary into :ename,:salary';
forall i in 1 .. empno_tab.count
execute immediate sql_stmt using
v_percent, empno_tab(i) returning bulk collect into ename_tab, sal_tab;
exception
when others
then
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line(sql_stmt);
raise;
for i in 1 .. ename_tab.count loop
dbms_output.put_line(ename_tab(i) || ':' || sal_tab(i));
end loop;
end;
动态sql的绑定变量及返回值
declare
v_empno number(3) := 363;
v_percent number(4, 2) := 0.12;
v_salary number(10, 2);
sql_stmt varchar2(500);
begin
sql_stmt := 'update emp set salary=salary*(1+:percent)where employee_id=:empno returning salary into :salary';
executeimmediate sql_stmt using v_percent, v_empno returninginto v_salary;
dbms_output.put_line(v_salary);
end;
多行动态sql语句
declare
type emp_cur_type isrefcursor;
emp_cur emp_cur_type;
v_deptno number(4) := '&deptno';
v_empno number(4);
v_ename varchar2(25);
begin
open emp_cur for'select empno,ename from emp wheredeptno=:1'
using v_deptno;
loop
fetch emp_cur
into v_empno, v_ename;
exitwhen emp_cur%notfound;
dbms_output.put_line(v_empno);
dbms_output.put_line(v_ename);
endloop;
close emp_cur;
exception
whenothersthen
if emp_cur%foundthen
close emp_cur;
endif;
dbms_output.put_line(substr(sqlerrm, 1, 200));
end;
使用批量多行dml语句
declare
type ename_table_type istableofvarchar2(25) indexbybinary_integer;
type sal_table_type istableofnumber(10, 2) indexbybinary_integer;
ename_tab ename_table_type;
sal_tab sal_table_type;
v_deptno number(4) := 20;
v_percent number(4, 2) := 0.12;
sql_stmt varchar2(500);
begin
sql_stmt := 'update emp setsal=sal*(1+:percent) where deptno=:deptno returning ename,sal into:ename,:salary';
executeimmediate sql_stmt
using v_percent, v_deptno
returningbulkcollect
into ename_tab, sal_tab;
for i in1 .. ename_tab.count loop
dbms_output.put_line(ename_tab(i) || ',' || sal_tab(i));
endloop;
end;
使用批量fetch语句来获取多行查询结果
declare
type ename_table_type istableofvarchar2(20) indexbybinary_integer;
type empno_table_type istableofnumber(24) indexbybinary_integer;
type emp_cur_type isrefcursor;
ename_tab ename_table_type;
empno_tab empno_table_type;
emp_cur emp_cur_type;
v_deptno number(4) := '&deptno';
begin
open emp_cur for'select empno,ename from emp wheredeptno=:1'
using v_deptno;
fetch emp_cur bulkcollect
into empno_tab, ename_tab;
close emp_cur;
for i in1 .. ename_tab.count loop
dbms_output.put_line(empno_tab(i) || ename_tab(i));
endloop;
end;