PL/SQL --> 动态SQL

  1. 云栖社区>
  2. 博客>
  3. 正文

PL/SQL --> 动态SQL

leshami 2011-01-05 15:06:00 浏览709
展开阅读全文

--====================

-- PL/SQL --> 动态SQL

--====================

   

    使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行

SQL查询语句,对于这种情况需要使用动态SQL来完成。再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只

能针对某几个特定的表来形成分页。而使用动态的SQL,则可以对不同的表,不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL

完成。本文讲述了动态SQL的日常用法。

 

一、动态SQL和静态SQL

    1.静态SQL

       静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程,返回该部门的所有雇员及薪水信息,则该语句为

           SELECT enamesal INTO lv_ename,lv_sal FROM scott.emp WHERE deptno=&dno;

       对于上述类似的DML语句在第一次运行时进行编译,而后续再次调用,则不再编译该过程。即一次编译,多次调用,使用的相同的执行

        计划。此种方式被称之为使用的是静态的SQL

      

    2.动态SQL

       动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询,对于表emp分页,需要使用字段雇员姓名,薪水,雇用日期,且按

       薪水降序生成报表,每页显示行数据。而对于表sales,需要使用字段雇员名称,客户名称,销售数量,销售日期,且按销售日期升序

       排列。以上两种情况,可以创建存储过程来对其进行分页,通过定义变量,根据输入不同的表名,字段名,排序方法来生成不同的SQL

       语句。对于输入不同的参数,SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译,此称之为动态SQL

       动态SQL语句通常存放在字符串变量中,且SQL语句可以包含占位符(使用冒号开头)

       也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后,如EXECUTE IMMEDIATE 'alter table emp enable row movement'

      

    3.两者的异同

       静态SQL为直接嵌入到PL/SQL中的代码,而动态SQL在运行时,根据不同的情况产生不同的SQL语句。

       静态SQL为在执行前编译,一次编译,多次运行。动态SQL同样在执行前编译,但每次执行需要重新编译。

       静态SQL可以使用相同的执行计划,对于确定的任务而言,静态SQL更具有高效性。但缺乏灵活性

       动态SQL使用了不同的执行计划,效率不如静态SQL,但能够解决复杂的问题。

       动态SQL容易产生SQL注入,为数据库安全带来隐患。

      

    4.动态SQL语句的几种方法

       a.使用EXECUTE IMMEDIATE语句

           包括DDL语句,DCL语句,DML语句以及单行的SELECT 语句。该方法不能用于处理多行查询语句。

       b.使用OPEN-FORFETCHCLOSE语句

           对于处理动态多行的查询操作,可以使用OPEN-FOR语句打开游标,使用FETCH语句循环提取数据,最终使用CLOSE语句关闭游标。

       c.使用批量动态SQL

           即在动态SQL中使用BULK子句,或使用游标变量时在fetch中使用BULK ,或在FORALL语句中使用BULK子句来实现。

       d.使用系统提供的PL/SQLDBMS_SQL来实现动态SQL,关于该方式请参考后续博文。   

 

二、动态SQL的语法

    下面是动态SQL常用的语法之一

   

       EXECUTE IMMEDIATE dynamic_SQL_string

       [INTO defined_variable1, defined_variable2, ...]

       [USING [IN | OUT | IN OUT] bind_argument1, bind_argument2,

       ...][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1,

       bind_argument2, ...]

 

    1.语法描述

       dynamic_SQL_string:存放指定的SQL语句或PL/SQL块的字符串变量

       defined_variable1:用于存放单行查询结果,使用时必须使用INTO关键字,类似于使用SELECT ename INTO v_name FROM scott.emp

           只不过在动态SQL时,将INTO defined_variable1移出到dynamic_SQL_string语句之外。

       bind_argument1:用于给动态SQL语句传入或传出参数,使用时必须使用USING关键字,IN表示传入的参数,OUT表示传出的参数,

           IN OUT则既可以传入,也可传出。

       RETURNING | RETURN 子句也是存放SQL动态返回值的变量。

 

    2.使用要点

        a.EXECUTE IMMEDIATE执行DML时,不会提交该DML事务,需要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。

       b.EXECUTE IMMEDIATE执行DDL,DCL时会自动提交其执行的事务。

       c.对于多行结果集的查询,需要使用游标变量或批量动态SQL,或者使用临时表来实现。

       d.当执行SQL时,其尾部不需要使用分号,当执行PL/SQL 代码时,其尾部需要使用分号。

       f.动态SQL中的占位符以冒号开头,紧跟任意字母或数字表示。

      

三、动态SQL的使用(DDLDCLDML以及单行结果集)     

      

    1.使用EXECUTE IMMEDIATE处理DDL操作

       下面是一个简单的DDL操作,将其封装在存储过程之中,通过传入表名来进行调用。

   

       CREATE OR REPLACE PROCEDURE trunc_table(table_name VARCHAR2)  --创建存储过程trunc_table

       IS

         sql_statement VARCHAR2(100);

       BEGIN

         sql_statement := 'TRUNCATE TABLE ' || table_name;           --为变量进行赋值,用于生成动态SQL语句

         EXECUTE IMMEDIATE sql_statement;                            --使用EXECUTE IMMEDIATE执行动态SQL语句

       END;

       /

 

       flasher@ORCL> create table tb2                                --scott.emp生产表tb2

         2  as select empno,ename,sal,deptno from scott.emp;

 

       flasher@ORCL> select count(1) from tb2;

 

         COUNT(1)

       ----------

              14

             

       flasher@ORCL> exec trunc_table('tb2');                        --调用存储过程来对表tb2进行truncate

 

       flasher@ORCL> select count(1) from tb2;                       --tb2被清空

 

         COUNT(1)

       ----------

               0    

 

       flasher@ORCL> insert into tb2                                 --重新为表tb2生成记录

         2  select empno,ename,sal,deptno from scott.emp;

 

       flasher@ORCL> commit;

 

    2.使用EXECUTE IMMEDIATE处理DCL操作  

       下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限

      

       sys@ORCL> conn sys/redhat@orcl as sysdba

 

       CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2, username VARCHAR2)

       IS

           sql_stat VARCHAR2(100);

       BEGIN

           sql_stat := 'GRANT ' || priv || ' TO ' || username; 

           EXECUTE IMMEDIATE sql_stat;

       END;

       /  

 

       sys@ORCL> exec grant_sys_priv('connect','usr1');

 

    3.使用EXECUTE IMMEDIATE处理DML操作

       在使用EXECUTE IMMEDIATE处理DML操作时,分为几种情况,即不带输入参数,带输入参数,既有输入也有输出参数或返回参数等不同情

       况,下面分别对其描述。  

 

       a.没有参数传入传出的DML语句

           下面的示例中,使用动态SQL删除一条记录,且未使用参数传入。

          

           flasher@ORCL> select * from tb2 where empno=7900;          --删除前

 

               EMPNO ENAME             SAL     DEPTNO

           ---------- ---------- ---------- ----------

                7900 JAMES             950         30

 

           flasher@ORCL> DECLARE sql_stat VARCHAR2(100);

             2  BEGIN

             3    sql_stat:='DELETE FROM flasher.tb2 WHERE empno=7900';   --使用动态SQL来删除记录

             4    EXECUTE IMMEDIATE sql_stat;

             5  END;

             6  /

 

           flasher@ORCL> SELECT * FROM tb2 where empno=7900;              --验证删除情况

 

           no rows selected

 

       b.有参数传入的DML语句(使用USING子句)

           对于使用了参数传入的动态SQL,需要使用USING子句来指明传入的参数。在下面的示例中,为表tb2插入一条记录,在DML语句中使

           用了四个占位符(占位符用以冒号开头,紧跟任意字母或数字表示)。因此在使用EXECUTE IMMEDIATE使用USING子句为其指定其参数。

          

           DECLARE                  --声明变量

             sql_stat VARCHAR2(100);

             lv_empno tb2.empno%TYPE := 7900;

             lv_ename tb2.ename%TYPE := 'JAMES';

             lv_sal   tb2.sal%TYPE := 950;

 

           BEGIN

             sql_stat := 'INSERT INTO tb2 VALUES(:1,:2,:3,:4)';                --DML语句中使用了占位符

             EXECUTE IMMEDIATE sql_stat USING lv_empno, lv_ename,lv_sal,30;    --为占位符指定参数或值

             COMMIT;

           END;

           /

 

           flasher@ORCL> select * from tb2 where empno=7900;                   --验证插入后的结果

 

               EMPNO ENAME             SAL     DEPTNO

           ---------- ---------- ---------- ----------

                7900 JAMES             950         30

     

       c.处理包含returning子句的DML语句

           下面的示例中,对表tb2进行更新,使用了两个占位符,一个是:percent,一个是:eno,因此在使用EXECUTE IMMEDIATE执行动态

           DML时,需要使用USING子句且带两个输入参数。其次,动态DML中使用了RETURNING sal INTO :salary,因此EXECUTE IMMEDIATE

           也必须使用RETURNING INTO varialbe_name

          

           DECLARE

             salary NUMBER(6, 2);

             sql_stat VARCHAR2(100);

           BEGIN

             sql_stat := 'UPDATE tb2 SET sal = sal * (1 + :percent / 100)'   --更新sal列,使用占位符:percent

                || ' WHERE empno = :eno RETURNING sal INTO :salary';      --使用了占位符:eno:salary,以及RETURNING子句

             EXECUTE IMMEDIATE sql_stat USING &1, &2 RETURNING INTO salary;  --必须使用USINGRETURNING子句

             COMMIT;

             dbms_output.put_line('New salary: ' || salary);

           END;

           /

 

           Enter value for 1: 10

           Enter value for 2: 7900

           old   7:   EXECUTE IMMEDIATE sql_stat USING &1, &2 RETURNING INTO salary;

           new   7:   EXECUTE IMMEDIATE sql_stat USING 10, 7900 RETURNING INTO salary;

           New salary: 1045

   

       d.处理包含检索值的单行查询

           下面的示例中,使用SELECT 查询获得单行结果集,使用了占位符:name,因此也需要使用USING子句为其传递参数

          

           DECLARE

             sql_stat VARCHAR2(100);

             emp_record tb2%ROWTYPE;

           BEGIN

             sql_stat := 'SELECT * FROM tb2 WHERE ename = UPPER(:name)';     --动态SQL语句为单行DQL语句

             EXECUTE IMMEDIATE sql_stat INTO emp_record USING '&name';       --使用USING子句为其传递参数

             DBMS_OUTPUT.PUT_LINE('The salary is ' || emp_record.sal || ' for '||emp_record.ename);

           END;

           /

 

           Enter value for 1: james

           old   6:   EXECUTE IMMEDIATE sql_stat INTO emp_record USING '&1';

           new   6:   EXECUTE IMMEDIATE sql_stat INTO emp_record USING 'james';

           The salary is 1045 for JAMES

 

四、动态SQL的使用(处理多行结果集的查询语句)  

    1.使用游标变量来循环提取数据,其主要流程为

       定义游标变量

           TYPE cursortype IS REF CURSOR;

           cursor_variable cursortype;

       打开游标变量

           OPEN cursor_variable FOR dynamic_string

           [USING bind_argument[,bind_argument]...]

       循环提取数据

           FETCH cursor_variable INTO {var1[,var2]...| record_variable};

           EXIT WHEN cursor_variable%NOTFOUND

       关闭游标变量

           CLOSE cursor_variable;

   

    2.使用游标变量处理查询多行结果集

       下面的示例中,首先定义了一个游标类型,接下来定义游标变量,以及存放结果集的变量,动态查询语句将获得多个结果集。

       OPEN cursorname FOR SELECT ... 时,其SELECT 语句使用了字符串变量(动态SQL),其后紧跟USING子句。

   

       DECLARE                             --游标,变量的声明

         TYPE emp_cur_type IS REF CURSOR;

           emp_cv       emp_cur_type;

           emp_record   tb2%ROWTYPE;

           sql_stat     VARCHAR2(100);

           v_dno        NUMBER := &dno;

        

       BEGIN

         sql_stat := 'SELECT * FROM tb2 WHERE deptno = :dno';   --动态多行结果集查询语句

         OPEN emp_cv FOR sql_stat USING v_dno;                   --OPEN 时使用动态查询语句以及USING子句来传递参数

         LOOP

           FETCH emp_cv INTO emp_record;                        --从结果集中提取记录

           EXIT WHEN emp_cv%NOTFOUND;

           dbms_output.put_line('Employee name' || emp_record.ename || ',  Salary' || emp_record.sal);

         END LOOP;

         CLOSE emp_cv;

       END;

       /

 

       Employee nameHenry,  Salary

       Employee nameJONES,  Salary

       Employee nameADAMS,  Salary

       Employee nameFORD,  Salary

 

五、动态SQL的使用(FORALLBULK子句的使用)        

    1.动态SQL中使用BULK子句的语法

   

       EXECUTE IMMEDIATE dynamic_string                          --dynamic_string用于存放动态SQL字符串

       [BULK COLLECT INTO define_variable[,define_variable...]]  --存放查询结果的集合变量

       [USING bind_argument[,argument...]]                       --使用参数传递给动态SQL

       [{RETURNING | RETURN}                                     --返回子句

       BULK COLLECT INTO return_variable[,return_variable...]];  --存放返回结果的集合变量

                 

       使用bulk collect into子句处理动态SQL中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可

       以是PL/SQL所支持的索引表、嵌套表和VARRY,但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句,分别为EXECUTE

       IMMEDIATE   FETCH FORALL

      

    2.使用EXECUTE IMMEDIATE 结合BULK子句处理DML语句返回子句

       下面的例子,首先定义了两个索引表类型以及其变量,接下来使用动态SQL语句来更新tb2的薪水,使用EXECUTE IMMEDIATE配合BULK

       COLLECT INTO 来处理结果集。

      

       DECLARE

         TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER;  --定义类型用于存放结果集

         TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;

           ename_table ename_table_type;

           sal_table sal_table_type;

           sql_stat VARCHAR2(120);

           v_percent NUMBER :=&percent;

           v_dno     NUMBER :=&dno;

        

       BEGIN

         sql_stat := 'UPDATE tb2 SET sal = sal * (1 + :percent / 100)'              --动态DML语句

             || ' WHERE deptno = :dno'

             || ' RETURNING ename, sal INTO :name, :salary';                        --使用了RETURNING子句,有返回值

         EXECUTE IMMEDIATE sql_stat USING v_percent, v_dno                          --执行动态SQL语句

           RETURNING BULK COLLECT INTO ename_table, sal_table;                      --使用BULK COLLECT INTO到集合变量

         FOR i IN 1..ename_table.COUNT                                              --使用FOR循环读取集合变量的结果

         LOOP

           DBMS_OUTPUT.PUT_LINE('Employee ' || ename_table(i) || ' Salary is: ' || sal_table(i));

         END LOOP;

       END;

       /

 

       Employee Henry Salary is: 1694

       Employee JONES Salary is: 3841.75

       Employee ADAMS Salary is: 1573

       Employee FORD Salary is: 3872

 

    3.使用EXECUTE IMMEDIATE 结合BULK子句处理多行查询

       下面示例中,与前一个示例相同,只不过其动态SQL有查询语句组成,且返回多个结果集,同样使用了BULK COLLECT INTO来传递结果。

      

       DECLARE

         TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集

         TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;

           ename_table ename_table_type;

           sal_table sal_table_type;

           sql_stat VARCHAR2(100);

       BEGIN

         sql_stat := 'SELECT ename,sal FROM tb2 WHERE deptno = :dno';              --动态DQL语句,未使用RETURNING子句

         EXECUTE IMMEDIATE sql_stat BULK COLLECT INTO ename_table,sal_table USING &dno;  --使用BULK COLLECT INTO

         FOR i IN 1..ename_table.COUNT

         LOOP

           DBMS_OUTPUT.PUT_LINE('Employee ' || ename_table(i) || ' Salary is: ' || sal_table(i));

         END LOOP;

       END;

       /

 

       Employee Henry Salary is: 1694

       Employee JONES Salary is: 3841.75

       Employee ADAMS Salary is: 1573

       Employee FORD Salary is: 4259.2

 

    4.使用FETCH子句结合BULK子句处理多行结果集

       下面的示例中首先定义了游标类型,游标变量以及复合类型,复合变量,接下来从动态SQLOPEN游标,然后使用FETCH将结果存放到复

       合变量中。即使用OPENFETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。

      

       DECLARE

         TYPE empcurtype IS REF CURSOR;         --定义游标类型及游标变量

         emp_cv empcurtype;

         TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER;  --定义结果集类型及变量

         ename_table ename_table_type;

         sql_stat    VARCHAR2(120);

       BEGIN

         sql_stat := 'SELECT ename FROM tb2 WHERE deptno = :dno';      --动态SQL字符串

         OPEN emp_cv FOR sql_stat                                      --从动态SQL中打开游标

           USING &dno;

         FETCH emp_cv BULK COLLECT                                     --使用BULK COLLECT INTO提取结果集

           INTO ename_table;

         FOR i IN 1 .. ename_table.COUNT LOOP

           DBMS_OUTPUT.PUT_LINE('Employee Name is ' || ename_table(i));

         END LOOP;

         CLOSE emp_cv;

       END;

       /

 

       Employee Name is Henry

       Employee Name is JONES

       Employee Name is ADAMS

       Employee Name is FORD

 

    5.使用FORALL语句中使用BULK子句

       下面是FORALL子句的语法

      

       FORALL index IN lower bound..upper bound           --FORALL循环计数

           EXECUTE IMMEDIATE dynamic_string               --结合EXECUTE IMMEDIATE来执行动态SQL语句

           USING bind_argument | bind_argument(index)     --绑定输入参数

              [bind_argument | bind_argument(index)]...

           [{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument...]];  --绑定返回结果集

      

       FORALL子句允许为动态SQL输入变量,但FORALL子句仅支持DML(INSERT,DELETE,UPDATE)语句,不支持动态的SELECT语句。

       下面的示例中,首先声明了两个复合类型以及复合变量,接下来为复合变量ename_table赋值,以形成动态SQL语句。紧接着使用FORALL

       子句结合EXECUTE IMMEDIATE 来提取结果集。

      

       DECLARE                                                 --定义复合类型及变量

         TYPE ename_table_type IS TABLE OF tb2.ename%TYPE;    

         TYPE sal_table_type IS TABLE OF tb2.sal%TYPE;

           ename_table ename_table_type;

           sal_table sal_table_type;

           sql_stat VARCHAR2(100);

       BEGIN

         ename_table := ename_table_type('BLAKE', 'FORD', 'MILLER');   --为复合类型赋值

         sql_stat := 'UPDATE tb2 SET sal = sal * 1.1 WHERE ename = :1' --定义动态SQL语句

             || ' RETURNING sal INTO :2';

         FORALL i IN 1..ename_table.COUNT                              --FORALL 设定起始值

           EXECUTE IMMEDIATE sql_stat USING ename_table(i) --使用EXECUTE IMMEDIATE 结合RETURNING BULK COLLECT INTO获取结果集

             RETURNING BULK COLLECT INTO sal_table;

         FOR j IN 1..ename_table.COUNT

         LOOP

           DBMS_OUTPUT.PUT_LINE('The new salary is ' || sal_table(j) || ' for ' ||ename_table(j)) ;

         END LOOP;

       END;

       /

          

       The new salary is 3135 for BLAKE

       The new salary is 4259.2 for FORD

       The new salary is 1760 for MILLER 

   

    6.动态SQL使用的常见错误,请参考:PL/SQL --> 动态SQL的常见错误 

 

六、更多参考

有关SQL请参考

        SQL 基础--> 子查询

        SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

 

    有关PL/SQL请参考

        PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

   

 

网友评论

登录后评论
0/500
评论