(转）PL/SQL --> 动态SQL的常见错误

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

-- PL/SQL --> 动态SQL的常见错误

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

 

    动态SQL在使用时，有很多需要注意的地方，如动态SQL语句结尾处不能使用分号(;)，而动态PL/SQL结尾处需要使用分号(;)，但不能使用正

斜杠结尾(/)，以及shcema对象不能直接作为变量绑定。本文介绍了动态SQL的常见问题。

 

一、演示动态SQL的使用

    下面的示例中，首先使用动态SQL基于scott.emp创建表tb2，然后里直接使用动态SQL从新表中获取记录数并输出。再接下来是定义了一个动

    态PL/SQL代码并执行以获取当前的系统时间，最后使用动态SQL对新表进行更新。

   

       DECLARE                               --定义变量以及给变量设定初始值

         sql_stmt         VARCHAR2(100);

         plsql_block      VARCHAR2(300);

         v_deptno         NUMBER := 30;

         v_count          NUMBER;

         v_new_sal        VARCHAR2(5);

         v_empno          NUMBER := 7900;

       BEGIN

         sql_stmt := 'CREATE TABLE tb_emp ' ||        --为变量赋值，生成动态SQL语句

                    'AS SELECT * FROM scott.emp WHERE deptno = ' || v_deptno;

         EXECUTE IMMEDIATE sql_stmt;                  --执行动态SQL语句

        

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp'　--直接使用EXECUTE IMMEDIATE后跟动态SQL串获得新表的记录数

           INTO v_count;

         DBMS_OUTPUT.PUT_LINE('The employee count is : ' || v_count);

        

         plsql_block := 'DECLARE ' ||             --声明一个PL/SQL块，存放到变量plsql_block中

                      ' v_date DATE; ' ||

                      'BEGIN ' ||

                      ' SELECT SYSDATE INTO v_date FROM DUAL; ' ||

                      ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,''DD-MON-YYYY''));' ||

                      'END;';

         EXECUTE IMMEDIATE plsql_block;           --执行动态的PL/SQL块

        

         sql_stmt := 'UPDATE tb_emp SET sal = sal + 100 WHERE empno =:eno ' ||  --更新新表的一条记录

                    'RETURNING sal INTO :sal';                         --动态SQL语句中包含RETURNING子句返回更新后的结果

         EXECUTE IMMEDIATE sql_stmt               --执行动态SQL块

           USING v_empno

           RETURNING INTO v_new_sal;              --使用RETURNING子句将结果存放到变量v_new_sal中

         DBMS_OUTPUT.PUT_LINE('New salary is: ' || v_new_sal);

       END;

 

       The employee count is : 6

       04-JAN-2011

       New salary is: 1050

 

二、动态SQL的常见错误  

    1.使用动态DDL时，不能使用绑定变量

       下面的示例中，在创建表示，使用了绑定变量:dno，在执行的时候收到了错误信息。

      

       DECLARE

         sql_stmt         VARCHAR2(100);

         v_deptno         VARCHAR2(5) := '30';

         v_count          NUMBER;

       BEGIN

         sql_stmt := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' ||

                    'WHERE deptno = :dno';

         EXECUTE IMMEDIATE sql_stmt

           USING v_deptno;

 

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_tmp'

           INTO v_count;

         DBMS_OUTPUT.PUT_LINE('The temp table count is  ' || v_count);

       END;

 

       DECLARE

       *

       ERROR at line 1:

       ORA-01027: bind variables not allowed for data definition operations

       ORA-06512: at line 8

 

       解决办法，将绑定变量直接拼接，如下：

           sql_stmt := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' || 'WHERE deptno = ' || v_deptno;

 

    2.不能使用schema对象作为绑定参数

       下面的示例中，动态SQL语句查询需要传递表名，因此收到了错误提示。

      

       DECLARE

         sql_stmt VARCHAR2(100);

         v_count  NUMBER;

       BEGIN

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :tb_name'

           INTO v_count;

         DBMS_OUTPUT.PUT_LINE('The table record  is  ' || v_count);

       END;

 

       DECLARE

       *

       ERROR at line 1:

       ORA-00903: invalid table name

       ORA-06512: at line 5

 

       处理办法

           DECLARE

             sql_stmt VARCHAR2(100);

             v_tablename VARCHAR2(30) :='scott.emp';   --增加一个变量并赋值

             v_count  NUMBER;

           BEGIN

             EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_tablename   --使用|| 连接变量

              INTO v_count;

             DBMS_OUTPUT.PUT_LINE('The table record is  ' || v_count);

           END;

          

           The temp table count is  14

 

    3.动态SQL块不能使用分号结束(;)

       下面的示例中，动态SQL语句使用了分号来结束，收到错误提示。

      

       DECLARE

         sql_stmt VARCHAR2(100);

         --v_tablename VARCHAR2(30) :='scott.emp';

         v_count  NUMBER;

       BEGIN

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp;'   --此处多出了分号，应该去掉

           INTO v_count;

         DBMS_OUTPUT.PUT_LINE('The temp table count is  ' || v_count);

       END;

 

       DECLARE

       *

       ERROR at line 1:

       ORA-00911: invalid character

       ORA-06512: at line 6

      

       处理办法

           去掉动态SQL语句末尾的分号

 

    4.动态PL/SQL块不能使用正斜杠来结束块，但是块结尾处必须要使用分号(;)

   

       DECLARE

         plsql_block VARCHAR2(300);

       BEGIN

         plsql_block := 'DECLARE ' ||

                      ' v_date DATE; ' ||

                      ' BEGIN ' ||

                        ' SELECT SYSDATE INTO v_date FROM DUAL; ' ||

                        ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,''YYYY-MM-DD''));' ||

                      'END;

                        /';   --此处多出了/，应该将其去掉

         EXECUTE IMMEDIATE plsql_block;

       END;

          

       DECLARE

       *

       ERROR at line 1:

       ORA-06550: line 3, column 2:

       PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.

       ORA-06512: at line 13

 

       处理办法

           去掉动态PL/SQL 块尾部的斜杠

 

    5.空值传递的问题

       下面的示例中对表tb_emp更新，并将空值更新到sal列，直接使用USING NULL收到错误提示。

      

       DECLARE

         sql_stmt VARCHAR2(100);

         v_empno  NUMBER := 7900;

       BEGIN

         sql_stmt := 'UPDATE tb_emp SET sal = :new_sal WHERE empno=:eno';

         EXECUTE IMMEDIATE sql_stmt

           USING NULL,v_empno;     --此处不能直接使用NULL

       END;

 

           USING NULL,v_empno;

                *

       ERROR at line 7:

       ORA-06550: line 7, column 11:

       PLS-00457: expressions have to be of SQL types

       ORA-06550: line 6, column 3:

       PL/SQL: Statement ignored

 

       正确的处理办法

           DECLARE

             sql_stmt VARCHAR2(100);

             v_empno  NUMBER := 7900;

             v_sal    NUMBER;    --声明一个新变量，但不赋值

           BEGIN

             sql_stmt := 'UPDATE tb_emp SET sal = :new_sal WHERE empno=:eno';

             EXECUTE IMMEDIATE sql_stmt

              USING v_sal, v_empno;

             COMMIT;

             DBMS_OUTPUT.PUT_LINE('The new sal is NULL');

           END;

 

    6.传递参数时顺序不正确的问题

       使用USING传递参数到动态SQL或使用INTO子句传递结果集到变量应注意按正确的顺序排列处理

       下面的示例中由于v_ename与v_sal为不同的数据类型，在使用INTO时不小心将顺序颠倒，导致错误产生。当然，如果数据类型相同，

       且不会存在溢出的情况下将没有错误提示。

      

           DECLARE

             TYPE emp_cur_type IS REF CURSOR;

             emp_cv   emp_cur_type;

             sql_stat VARCHAR2(100);

             v_dno    NUMBER := &dno;

             v_ename  VARCHAR2(25);

             v_sal    NUMBER;

 

           BEGIN

             sql_stat := 'SELECT ename,sal FROM scott.emp WHERE deptno = :dno';

             OPEN emp_cv FOR sql_stat     --使用游标来处理动态SQL

              USING v_dno;

             LOOP

              FETCH emp_cv

                INTO v_sal, v_ename;     --从结果集中提取记录时，顺序发生颠倒

              EXIT WHEN emp_cv%NOTFOUND;

              dbms_output.put_line('Employee name is ：' || v_ename ||

                                 ',  The sal is ：' || v_sal);

             END LOOP;

             CLOSE emp_cv;

           END;

 

           Enter value for dno: 20

           old   5:   v_dno    NUMBER := &dno;

           new   5:   v_dno    NUMBER := 20;

           DECLARE

           *

           ERROR at line 1:

           ORA-01722: invalid number

           ORA-06512: at line 14

      

       处理办法

           更正参数变量的顺序

 

    7.日期和字符型必须要使用引号来处理

       下面的示例中，使用了日期型变量，未使用引号标注，且使用了变量绑定，但直接输入日期型数据，而不加引号，则收到错误提示。

      

       DECLARE

         sql_stat  VARCHAR2(100);

         v_date    DATE :=&dt;      --定义日期型变量，未使用引号

         v_empno   NUMBER :=7900;

         v_ename   tb_emp.ename%TYPE;

         v_sal     tb_emp.sal%TYPE;

          

       BEGIN

         sql_stat := 'SELECT ename,sal FROM tb_emp WHERE hiredate=:v_date';    --使用了占位符:v_date进行变量绑定

                   

         EXECUTE IMMEDIATE sql_stat

         INTO v_ename,v_sal

         USING v_date;

         DBMS_OUTPUT.PUT_LINE('Employee Name '||v_ename||', sal is '||v_sal);

       END;

 

       Enter value for dt: 1981-05-01              --执行时，输入的字串中也未使用引号，此时收到错误提示

       old   3:   v_date    DATE :=&dt;

       new   3:   v_date    DATE :=1981-05-01;

         v_date    DATE :=1981-05-01;

                        *

       ERROR at line 3:

       ORA-06550: line 3, column 20:

       PLS-00382: expression is of wrong type

       ORA-06550: line 3, column 13:

       PL/SQL: Item ignored

       ORA-06550: line 13, column 9:

       PLS-00320: the declaration of the type of this expression is incomplete or malformed

       ORA-06550: line 11, column 3:

       PL/SQL: Statement ignored

      

       处理办法一

           执行时输入带引号的字串

           flasher@ORCL> /

           Enter value for dt: '1981-05-01'

           old   3:   v_date    DATE :=&dt;

           new   3:   v_date    DATE :='1981-05-01';

           Employee Name BLAKE, sal is 2850

 

           PL/SQL procedure successfully completed.

 

       处理办法二

           在声明变量时赋值用引号，如下

           v_date    DATE :='&dt';

          

           如存在字符格式转换，可以直接使用转换函数，如

           v_date    DATE :=TO_DATE('&dt','DD-MON-RR');

 

       如果上面的例子中，动态SQL语句不使用绑定日期变量，而是将其连接成字符串，则可以使用下面的方式来实现

      

       DECLARE

         sql_stat  VARCHAR2(100);

         v_date    DATE :='&dt';

         v_empno   NUMBER :=7900;

         v_ename   tb_emp.ename%TYPE;

         v_sal     tb_emp.sal%TYPE;

          

       BEGIN

         sql_stat := 'SELECT ename,sal FROM tb_emp WHERE hiredate=' || chr(39) ||v_date|| chr(39);--chr(39)代表单引号

         EXECUTE IMMEDIATE sql_stat

         INTO v_ename,v_sal;

         DBMS_OUTPUT.PUT_LINE('Employee Name '||v_ename||', sal is '||v_sal);

       END;

 

       Enter value for dt: 1981-05-01

       old   3:   v_date    DATE :='&dt';

       new   3:   v_date    DATE :='1981-05-01';

       SELECT ename,sal FROM tb_emp WHERE hiredate='1981-05-01'

       Employee Name BLAKE, sal is 2850

 

       PL/SQL procedure successfully completed.

 

    8.单行SELECT 查询不能使用RETURNING INTO返回

       下面的示例中，使用了动态的单行SELECT查询，并且使用了RETURNING子句来返回值。事实上，RETURNING coloumn_name INTO 子句仅

       仅支持对DML结果集的返回，因此，收到了错误提示。

      

       DECLARE

         sql_stat VARCHAR2(200);

         v_empno  tb2.empno%TYPE := &eno;

         v_ename  tb2.ename%TYPE;

      

       BEGIN

         sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno

                      RETURNING ename INTO :v_ename ';

         EXECUTE IMMEDIATE sql_stat

           USING v_empno

           RETURNING INTO v_ename;

         DBMS_OUTPUT.PUT_LINE('The employee name is ' || v_ename);

       END;

      

       处理办法

           去掉动态SQL语句中的RETURNING coloumn_name INTO子句，在执行EXECUTE IMMEDIATE时，直接使用INTO子句来传递值。

       DECLARE

         sql_stat VARCHAR2(200);

         v_empno  tb2.empno%TYPE := &eno;

         v_ename  tb2.ename%TYPE;

      

       BEGIN

         sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno';

                --       RETURNING ename INTO :v_ename ';     --去掉RETURNING子句

         EXECUTE IMMEDIATE sql_stat

           INTO v_ename                                        --增加INTO子句来返回变量值

           USING v_empno;

           --RETURNING INTO v_ename;                           --去掉RETURNING子句

         DBMS_OUTPUT.PUT_LINE('The employee name is ' || v_ename);

       END;

 

三、总结

    1.使用动态DDL时，不能使用绑定变量。应该将绑定变量与原动态SQL使用连接符进行连接。

    2.不能使用schema对象作为绑定参数，将schema对象与原动态SQL使用连接符进行连接。

    3.动态SQL块不能使用分号结束(;)。

    4.动态PL/SQL块不能使用正斜杠来结束块，但是块结尾处必须要使用分号(;)。

    5.空值传递的时候，不能直接使用USING NULL子句，应当声明变量，使用变量传递，当未给变量赋值时，即为空值。

    6.参数的传入传出应保证顺序的正确，以及防止数据溢出的问题。

    7.日期型或字符型在动态SQL中处理时，需要注意单引号个数的问题，特殊情况下可以使用chr(39)作为单引号使用。

    8.动态SQL中RETURNING INTO返回DML操作的结果，对于SELECT查询返回的结果，在执行EXECUTE IMMEDIATE时，直接使用INTO子句来传递。