动态SQL

动态SQL简介：   1、静态SQL      静态SQL是指直接嵌入在PL/SQL块中的SQL语句。在编写PL/SQL时。静态SQL用于完成特定或固定的任务：   2、动态SQL      动态SQL是指在运行PL/SQL块时动态输入的SQL语句。如果在PL/SQL中需要执行DDL语句,DCL语句（GRANT,REVOKE），或者在PL/SQL中需要执行更加灵活的SQL语句（例如在SELECT语句中使用不同的WHERE条件），那么就必须使用动态SQL。      在PL/SQL块中编写动态语句时，需要将SQL语句存放在字符创变量中，而且SQL语句可以包含占位符（以冒号开始）。       如：CREATE TABLE temp(cola INT,colb VARCHAR2(10))           GRANT SELECT ON temp TO smith           DELETE FROM emp WHERE sal>:a           SELECT ename,sal FROM emp WHERE empno=:l   3、动态SQL的处理方法      （1）、使用EXECUTE IMMEDIATE              不能用于处理多行查询语句。      （2）、使用OPEN-FOR FETCH 和CLOSE语句             为了处理动态的多行查询操作，必须使用OPEN-FOR语句打开游标，使用FETCH 语句提取循环数据，最终使用CLOSE语句关闭游标.      （3）、使用批量动态SQL

处理非查询语句   为了动态的处理非查询语句（DML DDL DCL）或者单行查询语句，可以在PL/SQL快中使用EXEXUTE IMMEDIATE 语句，                                     EXECUTE IMMEDIATE dynamic_string     [INTO |define_variable|,define_variable...|record]     [USING [IN|OUT|INOUT] bind_argument...]     [{RETURNING|RETURN} INTO bind_argument...];   dynamic_string 用于指定存放SQL语句或者PL/SQL块的字符串变量；   define_variable用于指定存放单行查询结婚的变量   输入bind_argument(IN)用于指定存放被传递给动态SQL值的变量   输出bind_argument(OUT)用于指定存放动态SQL返回值的变量   1、使用EXECUTE IMMEDIATE 处理DDL操作      EXECUTE IMMEDIATE后面只需要带有DDL语句文本即可，而不需要INTO和USING    字句。      CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)      IS        sql_statement VARCHAR2(100);      BEGIN        sql_statement:='DROP TABLE '||table_name;         EXECUTE IMMEDIATE sql_statement;      END;      2、使用EXECUTE IMMEDIATE 处理DCL操作        和处理DDL语句差不多    3、使用EXECUTE IMMEDIATE 处理DML 语句       如果DML语句既没有占位符，也咩有RETURNING 字句，那么在EXECUTE IMMEDIATE 语句之后不需要带有USING和RETURNING INTO子句；       如果DML语句含有占位符，那么在   EXECUTE IMMEDIATE 语句之后要带有USING子句；       如果DML语句之后带有RETURNING子句，那么在EXECUTE IMMEDIATE 语句之后带有RETURNING INTO子句。      （1）、处理无占位符和RETURNING子句的DML语句         DECLARE          sql_statement VARCHAR2(100);         BEGIN          sql_statement:='UPDATE emp SET sal=sal*1.1 WHERE depno=30';          EXECUTE IMMEDIATE sql_statement;         END;       (2)、处理包含占位符的DML语句          要使用USING 自己为占位符提供输入数据。          DECLARE             sql_state VARCHAR2(100);          BEGIN             sql_state:='UPDATE emp SET sal=sal*(1+:percent/100) WHRE deptno=:dno';             EXECUTE IMMEDIATE sql_state USING &1,&2;          END;       (3)、处理包含RETURNING 子句的DML语句          必要要使用RETURNING INTO 子句接受返回数据。          注意：直接使用EXECUTE IMMEDIATE 语句处理带有RETURNING 子句的DML语句时，只能处理作用在单行的DML语句。               如果DML语句作用在多行伤，则必须要使用BULK子句。           DECLARE              salary NUMBER(6,2);             sql_state VARCHAR2(100);          BEGIN             sql_state:='UPDATE emp SET sal=(sal*:present/100) WHERE empno=:eno RETURNING sal INTO :salary';             EXECUTE IMMEDIATE sql_state USING &1,&2 RETURNING INTO salary;             dbms_output.put_line('新工资:'||salary);          END;         (4)、使用EXECUTE IMMEDIATE 处理单行查询          要使用INTO 子句接受返回数据          DECLARE            sql_state VARCHAR2(100);            emp_record emp%ROWTYPE;          BEGIN             sql_state:='SELECT * FROM emp WHERE empno=:eno';             EXECUTE IMMEDIATE  sql_state INTO emp_record USING &1;                 dbms_output.put_line('雇员:'||emp_record.ename||'的工资为:'||emp_record.sal);           END;处理多行查询语句：  使用EXECUTE IMMEDIATE只能处理单行查询语句，为了动态的处理SELECT语句所返回的多行数据，需要使用OPEN-FOR,FETCH,CLOSE语句。  步骤如下    定义游标变量-->打开游标变量-->循环提取数据-->关闭游标变量。    1、定义游标变量：       TYPE cursortype IS REF CURSOR;       cursor_variable cursortype;    2、打来游标变量       OPEN   cursor_variable FOR dynamic_string [USING bind_argument,...];        bind_argument 用于存放传递给动态SELECT语句值的变量。    3、循环提取数据       FETCH cursor_variable INTO {var1[,var2]...|record_var};    4、关闭游标变量       close cursor_variable；    5、多行查询实例：        DECLARE          TYPE cursor_type IS REF CURSOR;          sql_state VARCHAR2(100);          emp_record emp%ROWTYPE;          v_cursor vursor_type;        BEGIN          sql_state:='SELECT * FROM emp WHERE deptno=:dno';          OPEN  v_cursor FOR sql_state USING &deptno;          LOOP            FETCH   v_cursor INTO emp_record;            EXIT WHEN v_cursor%NOTFOUND;            dbms_output.put_line('雇员名：'||emp_record.ename||'的工资是：'||emp_record.salary);          END LOOP;          CLOSE v_cursor;        END;在动态SQL中使用BULK子句  Oracle9i新增加的特性。可以加快批量数据的处理速度。  有三种语句支持BULK子句：EXECUTE IMMEDIATE，FETCH和FORALL  1、在EXECUTE IMMEDIATE语句中使用动态BULK子句       EXECUTE IMMEDIATE dynamic_string     [BULK COLLECT INTO |define_variable|,define_variable...|record]     [USING [IN|OUT|INOUT] bind_argument...]     [{RETURNING|RETURN} BULK COLLECT INTO bind_argument...];     (1)、使用BULK子句处理DML语句返回子句        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;          sal_table sal_table_type;          sql_state VARCHAR2(150);        BEGIN           sql_state:='UPDATE emp SET sal=sal*(1+:precent/100) WHERE deptno=:dno'             ||'RETURNING ename,sal INTO :name,:salary';          EXECUTE IMMEDIATE sql_state USING &precent,&depno 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;                                           (2)、使用BULK子句处理多行查询                                         DECLARE            sql_state VARCHAR2(100);            TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;            ename_table ename_table_type;          BEGIN             sql_state:='SELECT ename FROM emp WHERE empno=:eno';             EXECUTE IMMEDIATE  sql_state BULK COLLECT INTO emp_record USING &1;              FOR i IN 1..ename_table.COUNT LOOP                  dbms_output.put_line('雇员:'||ename_table(i));               END LOOP;            END;  2、在FETCH 语句中使用BULK子句     OPEN-FOR ,FETCH,CLOSE .....BULK     DECLARE        TYPE cursor_type IS REF CURSOR;        TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;        sql_state VARCHAR2(200);        v_vursor cursor_type;        ename_table ename_table_type;     BEGIN         sql_state:='SELECT ename FROM emp WHERE deptno=:dno';        OPEN v_corsor FOR sql_state USING &deptno;        FETCH v_cursor BULK COLLECT INTO ename_table;        FOR i IN i..ename_table.COUNT LOOP             dbms_output.put_line('雇员:'||ename_table(i));          END LOOP;        CLOSE   v_cursor;     END;    FORALL       

本文转自：http://blog.csdn.net/memoordit/article/details/2385600?locationNum=5&fps=1