45动态SQL.doc

l 动态SQL简介n 静态SQL直接嵌入在PL/SQL的语句，并且静态SQL用于完成特定的或固定的任务。Select ename into v_ename from where empno=v_empno;Insert into emp (empno,ename) values(v_empno,v_ename);Update emp set sal=v_sal where ename=v_ename;Delete from emp where empno=v_empno;n 动态SQL动态SQL是指执行PL/SQL块动态输入的SQL语句。当在PL/SQL中嵌入DDL或DCL语句时，必须使用动态SQL。执行更灵活的SELECT 或DML语句，也使用动态SQLCreate table temp (cola int,colb varchar2(10)Grant select on emp to smithDelete from emp where sal:a 带有占位符的SELECT 语句Select ename,sal from emp where empno=:1 带有占位符的SELECT 语句n 比较静态SQL和动态SQL如果功能完全确定，则使用静态SQL,当不能确定执行SQL语句，则使用动态SQLn 动态SQL处理方法u 使用EXECUTE IMMEDIATE语句：用于处理除多行查询之外任何的动态SQL.u 使用OPEN-FOR,FETCH,CLOSE语句：用于处理多行查询的操作。u 使用批量动态SQL：用于加快SQL语句处理，提高PL/SQL性能l 使用EXECUTE IMMEDIATE语句为了在动态SQL中处理DDL,DCL,DML,以及单行SELECT INTO 语句。需要使用这个n 处理DDL 和 DCL语句SQL declare 2 ddl_string varchar2(100):=&string; 3 begin 4 execute immediate ddl_string; 5 end; 6 / 输入string的值：create table te2(cola int)PL/SQL procedure successfully completedSQL declare 2 dcl_string varchar2(100):=&string; 3 begin 4 execute immediate dcl_string; 5 end; 6 /输入string的值：grant select on dept to hrPL/SQL procedure successfully completedn 处理DML语句示例一：处理占位符和RETURNING子句的DML语句Execute immediate dyn_string;SQL declare 2 dcl_stat varchar2(100):=&string; 3 begin 4 execute immediate dcl_stat; 5 end; 6 /输入string的值：update emp set sal=1800 where empno=7799PL/SQL procedure successfully completed示例二 处理包含占位符的DML语句Execute immediate dyn_string USING bind_argument, bind_argument;bind_argument:用于为占位符提供数据。占位符必须以冒号开始。SQL declare2 dml_stat varchar2(100):=insert into emp |(empno,ename,sal) values (:no,:name,:sal); 3 begin 4 execute immediate dml_stat using &1,&2,&3; 5 end; 6 /示例三 处理包含RETURNING子句的DML语句Execute immediate dyn_stringReturning into variale1,variale2,;SQL declare 2 dms_stat varchar2(100):=delete emp where | -这没有fromempno=7934 returning ename into :name; 3 v_name emp.ename%type; 4 begin 5 execute immediate dms_stat returning into v_name; 6 dbms_output.put_line(v_name|被开除); 7 end; 8 / MILLER被开除 PL/SQL procedure successfully completed示例四 处理包含占位符和RETURNING INTO 子句的DML语句当处理这类语句的时候，需要使用USING子句和RETURNING INTO子句。declare dml_stat varchar2(100):=update emp set sal=:salary |where empno=:eno returning sal into :new_sal;begin execute immediate dml_stat using &sal,&eno returning into v_sal ; dbms_output.put_line(新工资:|v_sal);end;/n 处理单行查询当使用execute immediate语句处理单行查询语句时，需使用INTO子句接收返回数据Execute immediate dyn_string into variale1,variale2,;示例一 处理不包含占位符的单行查询当但行查询不包含占位符，则可在execute immediate语句之后直接使用INTO子句接收数据。SQL declare 2 query_stat varchar2(100):=select * from emp| where lower(ename)=lower(&name); 3 emp_record emp%rowtype; 4 begin 5 execute immediate query_stat into emp_record; 6 dbms_output.put_line(岗位 :|emp_record.job|工资 :|emp_record.sal); 7 end; 8 / 岗位 :PRESIDENT工资 :5000 PL/SQL procedure successfully completed示例二 处理包含占位符的单行查询如果单行查询包含占位符，则需要带有USING 子句和 INTO子句SQL declare 2 query_stat varchar2(100):=select * from emp| where empno=:eno; 3 emp_record emp%rowtype; 4 begin 5 execute immediate query_stat into emp_record using 7369; 6 dbms_output.put_line(岗位 :|emp_record.job|工资 :|emp_record.sal); 7 end ; 8 / 岗位 :CLERK工资 :880 PL/SQL procedure successfully completedl 处理多行查询定义游标变量：Type cursor is ref cursor;Cursor_variable cursorttype;打开游标变量：执行游标变量所对应的动态SELECT语句，并将查询结果存放到游标结果集。 OPEN cursor_variable for dynamic_stringusing bind_argument,bind_argument提取数据：用于将结果集中的行数据存放到PL/SQL变量。FETCH CURSOR_VARIALE INTO VAR1,VAR2|RECORD_VAR;关闭游标变量： close cursor_variableSQL declaretype empcurtyp is ref cursor;emp_cv empcurtyp;emp_record emp%rowtype; sql_stat varchar2(100);beginsql_stat:=select * from emp where deptno=:dno;open emp_cv for sql_stat using &dno;loop fetch emp_cv into emp_record; exit when emp_cv%notfound; dbms_output.put_line(雇员名 ：|emp_record.ename|，工资： |emp_record.sal); end loop; close emp_cv; end; /雇员名 ：SMITH，工资： 880雇员名 ：JONES，工资： 3272.5雇员名 ：ADAMS，工资： 1210雇员名 ：FORD，工资： 3300 PL/SQL procedure successfully completedl 在动态SQL中使用批量绑定通过在动态SQL中使用批量绑定，可加快批量数据的处理速度，从而提高应用性能。集合元素需要使用SQL固有数据类型，而不能使用PL/SQL特有的数据类型示例一：在动态DML上使用批量变量Forall index in lower bound.upper boundExecute immediate dynamic_string ;SQL declare 2 type ename_table_type is varray(3) of emp.ename%type; 3 ename_table ename_table_type:=ename_table_type(&name1,&name2,&name3); 4 sql_stat varchar2(100):=update emp set sal=sal*1.1 |where lower(ename)=lower(:1); 5 begin 6 forall i in 1.ename_table.count 7 execute immediate sql_stat using ename_table(i); 8 end; 9 / PL/SQL procedure successfully completed示例二：在DML返回子句中使用批量变量Execute immediate dynamic_stringReturning bulk collect into collect_var,;declare type ename_table_type is table of emp.ename%type; type sal_table_type is table of emp.sal%type; ename_table ename_table_type; sal_table sal_table_type; sql_stat varchar2(100):=update emp set sal=sal+200 |where deptno=：dno |returning ename,sal into :1,:2;begin execute immediate sql_stat using &dno returning bulk collect into ename_table,sal_table; for i in 1.ename_table.count loop dbms_output.put_line(姓名：|ename_table(i)|,新工资 ：|sal_table(i); end loop;end;姓名：SMITH,新工资 ：1080姓名：JONES,新工资 ：3472.5姓名：ADAMS,新工资 ：1410姓名：FORD,新工资 ：3500 PL/SQL procedure successfully completed示例三 使用批量绑定处理多行查询SQL declare 2 type ename_table_type is varray(100) of emp.ename%type; 3 type hiredate_table_type is varray(100) of emp.hiredate%type; 4 ename_table ename_table_type; 5 hiredate_table hiredate_table_type; 6 sql_stat varchar2(100):=select ename,hiredate from emp |where deptno=:dno; 7 begin 8 execute immediate sql_stat 9 bulk collect into ename_table,hiredate_table using &dno; 10 for i in 1.ename_table.count loop 11 dbms_output.put_line(姓名：|ename_table(i)|,工作日期 ：|hiredate_table(i); 12 end loop; 13 end; 14 / 姓名：SMITH,工作日期 ：17-12月-80姓名：JONES,工作日期 ：02-4月 -81姓名：ADAMS,工作日期 ：23-5月 -87姓名：FORD,工作日期 ：03-12月-81 PL/SQL procedure successfully completed示例四 在FETCH语句中使用批量提取当使用游标变量处理动态查询时，FETCH .INTO语句每次只能提取单行数据，为一次提取所有数据，可以使用FETCH.BULK COLLECT INTO语句。FETCH CURSOR_VARIABLE BULK COLLECT INTO COLLECT_