PL/SQL -- 动态SQL的常见错误_plsql中单引号中冒号':-优快云博客

本文链接：https://blog.youkuaiyun.com/fsfsdfsdw/article/details/87873314

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

-- 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再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.youkuaiyun.com/jiangjunshow