--============================
-- 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
v_deptno VARCHAR2(5) := '30';
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'
DBMS_OUTPUT.PUT_LINE('The temp table count is ' || v_count);
*
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語句查詢需要傳遞表名，是以收到了錯誤提示。
sql_stmt VARCHAR2(100);
v_count NUMBER;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :tb_name'
DBMS_OUTPUT.PUT_LINE('The table record is ' || v_count);
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語句使用了分号來結束，收到錯誤提示。
--v_tablename VARCHAR2(30) :='scott.emp';
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp;' --此處多出了分号，應該去掉
ORA-00911: invalid character
ORA-06512: at line 6
去掉動态SQL語句末尾的分号
4.動态PL/SQL塊不能使用正斜杠來結束塊，但是塊結尾處必須要使用分号(;)
plsql_block VARCHAR2(300);
plsql_block := 'DECLARE ' ||
' BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,''YYYY-MM-DD''));' ||
'END;
/'; --此處多出了/，應該将其去掉
EXECUTE IMMEDIATE plsql_block;
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收到錯誤提示。
v_empno NUMBER := 7900;
sql_stmt := 'UPDATE tb_emp SET sal = :new_sal WHERE empno=:eno';
USING NULL,v_empno; --此處不能直接使用NULL
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
正确的處理辦法
v_empno NUMBER := 7900;
v_sal NUMBER; --聲明一個新變量，但不指派
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');
6.傳遞參數時順序不正确的問題
使用USING傳遞參數到動态SQL或使用INTO子句傳遞結果集到變量應注意按正确的順序排列處理
下面的示例中由于v_ename與v_sal為不同的資料類型，在使用INTO時不小心将順序颠倒，導緻錯誤産生。當然，如果資料類型相同，
且不會存在溢出的情況下将沒有錯誤提示。
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;
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;
Enter value for dno: 20
old 5: v_dno NUMBER := &dno;
new 5: v_dno NUMBER := 20;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 14
更正參數變量的順序
7.日期和字元型必須要使用引号來處理
下面的示例中，使用了日期型變量，未使用引号标注，且使用了變量綁定，但直接輸入日期型資料，而不加引号，則收到錯誤提示。
sql_stat VARCHAR2(100);
v_date DATE :=&dt; --定義日期型變量，未使用引号
v_empno NUMBER :=7900;
v_ename tb_emp.ename%TYPE;
v_sal tb_emp.sal%TYPE;
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);
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:
處理辦法一
執行時輸入帶引号的字串
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語句不使用綁定日期變量，而是将其連接配接成字元串，則可以使用下面的方式來實作
v_date DATE :='&dt';
sql_stat := 'SELECT ename,sal FROM tb_emp WHERE hiredate=' || chr(39) ||v_date|| chr(39);--chr(39)代表單引号
INTO v_ename,v_sal;
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結果集的傳回，是以，收到了錯誤提示。
sql_stat VARCHAR2(200);
v_empno tb2.empno%TYPE := &eno;
v_ename tb2.ename%TYPE;
sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno
RETURNING ename INTO :v_ename ';
RETURNING INTO v_ename;
DBMS_OUTPUT.PUT_LINE('The employee name is ' || v_ename);
去掉動态SQL語句中的RETURNING coloumn_name INTO子句，在執行EXECUTE IMMEDIATE時，直接使用INTO子句來傳遞值。
sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno';
-- RETURNING ename INTO :v_ename '; --去掉RETURNING子句
INTO v_ename --增加INTO子句來傳回變量值
USING v_empno;
--RETURNING INTO v_ename; --去掉RETURNING子句
三、總結
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子句來傳遞。
四、更多參考
有關SQL請參考
<a href="http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx">SQL基礎-->分組與分組函數</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx">SQL 基礎-->常用函數</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx">SQL 基礎--> ROLLUP與CUBE運算符實作資料彙總</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx">SQL基礎-->階層化查詢(START BY ... CONNECT BY PRIOR)</a>
有關PL/SQL請參考
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx">PL/SQL --> 流程控制</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx">PL/SQL --> 存儲過程</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx">PL/SQL --> 函數</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx">PL/SQL --> 遊标</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx">PL/SQL -->隐式遊标(SQL%FOUND)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx">PL/SQL --> 異常處理(Exception)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx">PL/SQL --> PL/SQL記錄</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx">PL/SQL --> 包的建立與管理</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx">PL/SQL --> 包重載、初始化</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx">PL/SQL --> DBMS_DDL包的使用</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx">PL/SQL --> DML 觸發器</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx">PL/SQL --> INSTEAD OF 觸發器</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2011/01/05/6118010.aspx">PL/SQL --> 動态SQL</a>
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)