1 动态sql 简介
2
1 使用execute immediate 处理ddl 操作
create or replacee procedure drop_table(table_name varchar2)
is
sql_statement varchar2(100);
begin
sql_statement:='drop table'||table_name;
execute immediate sql_statement;
调用
exec drop_table('worker');
end;
2) 使用 execute immediate 处理dcl 操作
create or replace procedure grant_sys_priv
(priv varchar2,username varchar2)
is
begin
sql_stat:='gruant'||priv||'to'||username;
execute immediate sql_stat;
end;
exec grant_sys_priv('create session','scott');
3 使用execute immediate 处理dml 操作
1) 处理无占位符和returning 子句的dml 语句
delcare
sql_stat varchar2(100);
begin
sql_stat:='update emp set sal=sal*1.1 where deptno=44';
execute immediate sql_stat;
end;
2) 处理包含占位符的dml语句
delare
sql_stat varchar2(100);
begin
sql_stat:='update emp set sql=sql*(1+:percent/100)'
||'where deptno=:dno';
execute immediate sql_stat using &1,&2;
end;
3) 处理包含returning 子句的dml语句
declare
salary number(6,2);
sql_stat varchar2(200);
begin
sql_stat:='update emp set sal=sal*(1:percent/100)'
||'where empno=:eno returning sal into :salary';
execute immediate sql_stat using &1,&2;
returning into salary;
end;
输入1的值 15
输入2的值 2222
新工资；2223
4) 使用execute immediate 处理单行查询
declare
sql_stat varcchar2(100);
emp_record emp%rowtype;
begin
sql_stat:='select * from emp where empno=:eno';
execute immediate sql_stat into emp_record using &1;
end;
3 处理多行查询语句
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
emp record emp%rowtype;
sql_stat varchar2(100);
begin
sql_stat:='select * from em where deptno=:dno';
open emp_cv for sql_stat using &dno;
loop
fetch emp_cu into emp_record;
exit when emp_cv%notfound;
end loop;
close emp_cv;
end;
4 在动态sql 中使用bulk语句
1) 在 execute immediate 语句中使用动态bulk 语句
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
type sal_table_type is table of emp.sal%type
index by binary_integer;
ename_table ename_table_type;
sa_table sal_table_type;
sal_stat varchar2(100);
begin
sql_stat:='update emp set sal=sal*(1+:percent/100)'
|| 'where deptno=:dno'
||'returning ename,sal into :name,:salary';
execut immediate sql_stat using &percent,&dno
returning bulk collect into ename_table,sal_table;
for i in 1..ename_table.count loop
....
end loop;
end;
2) 使用bulk 子句处理多行查询
sql_stat:='select ename from emp where deptno=:dno';
execute immediate sql_stat bulk collect into ename_table using &dno;
3) 在fetch 语句中使用bulk 子句
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
type ename_table_type is table of emp.ename%type;
index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(100);
begin
sql_stat:='select ename from emp where job:=title';
open emp_cv for sql_stat using '&job';
fetch emp_cv bulk collect into ename_table;
4) 在forall 语句中使用bulk 子句
declare
type ename_table_type is table of emp.ename%type;
type sla_table_type is table of emp.sal%type;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
begin
ename_table:=ename_table_type('scott','smith','clark');
sql_stat:='update emp set sal=sal*1.1 where ename=:1'
||'returning sal into :2';
forall i in 1..ename_table.count
execite immediate sql_stat using ename_table(i)
returning bulk collect into sal_table;
end;