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;