Oracle動態執行語句（Execute Immediate）

-------&gt>轉載於;http://sillyge.blog.163.com/blog/static/1769642082011043547773/

一。為什麼要使用動態執行語句？

       由於在PL/SQL 塊或者儲存過程中只支援DML語句及控制流語句，並不支援DDL語句，所以Oracle動態執行語句便應允而生了。關於DDL與DML的區別，請參見：DDL語句與DML語句及DCL和TCL。

二。動態執行語句怎麼用？     

     動態執行語句代替了Oracle 8i中的DBMS_SQL Package包。

     1）在PL/SQL中執行SQL語句，例如：

       示例一：

        BEGIN 
              EXECUTE IMMEDIATE 'select count(username) from user_users';     --DML（每條語句必須以分號結尾）
       END;

       示例二：

       BEGIN 
               EXECUTE IMMEDIATE  'ALTER TABLE a RENAME TO EXAMPLE';    --DDL
        END;

        你可能會問不是隻DDL語句需要用動態語句執行嗎？是的，你說的完全正確。但是DML語句用動態語句執行也可以。

        即：DDL語句只能用動態執行語句來執行，DML語句亦可用動態語句來執行。

    2）使用using給動態語句傳值，例如：

         DECLARE
                e_name VARCHAR2(10); --宣告變數e_name
                e_age INT;                        --宣告變數e_age
         BEGIN
                e_name :=  'sillylaura';      --給變數e_name賦值
                e_age := 21;                     --給變數e_age 賦值
                EXECUTE IMMEDIATE 'insert into Example values(seq_add_by_one.nextval,:2,:3)' usinge_name,e_age;  --DML             END;

    3）使用動態語句賦值（select 列名 into 變數 from ……）

         DECLARE 
                        temp INT; 
         BEGIN
                        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual'  INTO temp; --DML

                        dbms_output.put_line(temp);
         END;

     4）傳遞並檢索值：into用在using之前。

         DECLARE
                   temp INT;
                   test VARCHAR2(10);
         BEGIN
                 test := 'ok';
                 EXECUTE immediate 'SELECT COUNT(*) FROM dual where dummy = :1 GROUP BY dummy' INTO temp USING test;
                 dbms_output.put_line(temp ||'  '|| test); 
         EXCEPTION WHEN OTHERS  THEN 
                 dbms_output.put_line('It has no data!'); 
         END;

三。動態語句小結

1. DDL語句只能用動態執行語句來執行，DML語句亦可用動態語句來執行。
2. 在使用select……into子句為變數賦值時，into字句必須寫在單引號外面。
3. 在同時使用select……into子句和using時，注意二者的順序：into用在using之前，且都在單引號外面。
4. 注意寫上必要的異常錯誤處理。