当前位置: 代码迷 >> SQL >> (转)PL/SQL -> 动态SQL的常见异常
  详细解决方案

(转)PL/SQL -> 动态SQL的常见异常

热度:76   发布时间:2016-05-05 14:06:01.0
(转)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_enamev_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

??????

?????? 处理办法一

?????????? 执行时输入带引号的字串

?????????? [email protected]> /

?????????? 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.动态SQLRETURNING INTO返回DML操作的结果,对于SELECT查询返回的结果,在执行EXECUTE IMMEDIATE时,直接使用INTO子句来传递。

  相关解决方案