动态SQL的常见错误

作者：网络转载发布时间：[ 2013/4/17 9:57:26 ] 推荐标签：

　　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