程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 動態SQL 學習筆記

Oracle 動態SQL 學習筆記

編輯：Oracle數據庫基礎

還記得那幾天沒日沒夜的配報表的時候，總是參考著以前的人寫的存儲過程來寫自己的存儲過程，大體形式都差不多，一個很長的由SQL語句組成的字符串，然後調用EXECUTE IMMEDIATE語句來執行這個字符串，最後COMMIT就完事了。都這樣的形式，但是自己很好奇，EXECUTE IMMEDIATE是個什麼用法，最後Google了一下，終於明白了還有動態SQL這麼概念的存在。好了，這篇文章就帶領大家去學習動態SQL。

一個“血淋淋”的例子

不知道大家有沒有寫過如下這樣的代碼：

create or replace procedure create_tmp_tb
as
    strSQL varchar2(200);begin
    create table tp(id int, name varchar2(20));end;

運行上面的代碼，存儲過程創建會失敗，錯誤信息如下：

Error(5,3): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

很抱歉，根據這個錯誤信息，我可以確定是由於使用了create而引起的錯誤，但是我無法知道為什麼在存儲過程中使用了create就會出錯。後來，我使用了另一種方法來完成了這個任務，修改後的代碼如下：

create or replace procedure create_tmp_tb
as
    rowCount number(10);begin
    select count(1) into rowCount from all_tables where 
        TABLE_NAME = 'TP_201508' and OWNER='JELLY'; 
    if rowCount=1 then
        dbms_output.put_line('drop table tp_201508');
        execute immediate 'drop table TP_201508';
    end if; 
    execute immediate 'create table TP_201508(id int, name varchar2(20))';end;

為什麼在存儲過程中直接使用create就不行，而使用execute immediate來直接就OK了呢？這就關系到這裡總結的動態SQL的問題。說到動態SQL，就需要先說說靜態SQL。

靜態SQL

學過高級語言（C++、Java等）的都知道靜態編譯和動態這麼回事，靜態SQL就如靜態編譯一樣，在編譯時，靜態SQL語句已經被解析和驗證過。像我們平時寫的DML、TCL等語句都是靜態SQL；但是悲劇的是，在PL/SQL中不支持靜態SQL的DDL語句，現在你就應該知道上面的代碼中使用create為什麼不行了吧。既然靜態SQL中不能使用DDL語句，那麼現在有這個需求怎麼辦？好了，這個時候就需要說到今天的主角——動態SQL了。

動態SQL

動態SQL語句在編譯時，並不知道SQL語句的內容，SQL語句的內容“不確定”，只有在運行時，才建立、解析並執行SQL語句。利用動態SQL，在存儲過程中，可以動態創建表、視圖、觸發器等。

動態SQL主要用在以下兩種場景：

編譯時，無法確定SQL語句的內容
靜態SQL不支持的SQL語句，就比如上面代碼中的create

我們可以看到，靜態SQL在編譯時就已經提前檢查了SQL正確性，以及涉及的數據庫對象和對應的權限關系，而動態SQL則需要在運行的時候才能判斷，所以，靜態SQL的效率高於動態SQL。說了這麼多概念的東西，我們現在就來實際看看如何編寫動態SQL，以及如何運行動態SQL。

編寫本地動態SQL

編寫動態SQL有兩種方式方法：

本地動態SQL，用於建立和執行SQL語句；本地動態SQL使用EXECUTE IMMEDIATE命令來執行動態SQL語句
使用包DBMS_SQL中的方法來執行動態SQL語句（沒有精力總結，請自行Google）

下面我們就先來說說本地動態SQL的編寫和執行。首先來一段最簡單，也沒有任何實際作用的SQL代碼：

create or replace procedure insert_data(id varchar2, name varchar2, sex varchar2, age number)as
    strSQL varchar2(32766);begin
    strSQL := 'insert into jelly.tb_student values(:id, :name, :sex, :age)';
    execute immediate strSQL using id, name, sex, age;
    commit;end;

這段代碼和開始那段建立表的SQL代碼又有點不一樣，這裡在execute immediate語句中多了個using關鍵字。這裡使用的using關鍵字就是所謂的“占位符”，顧名思義，就是占住位置的符號。一般我們在使用動態SQL時，都需要拼接一個字符串，在拼接字符串的過程中，會將很多的變量拼接進來，而這些變量是一般都是外部傳遞進來的；如果將這些變量都使用字符串拼接符號||連接在一起，則顯的代碼比較亂，同時也不利於變量的統一管理，以及代碼的後期維護，所以就有了使用占位符這種方式來解決這個問題。

在上面的代碼中，:id、:name、:sex和:age都是占位符，占位符必須以冒號開始，名字倒是無所謂。使用了占位符以後，就需要在execute immediate語句後面使用using將參數傳遞進去，參數將與占位符一一對應。但是有一點我們需要謹記，綁定參數不能是表名、列名、數據類型等，綁定參數只能是值、變量或者表達式。用DDL語句動態創建對象時，應該使用連接運算符||，最好不要使用綁定參數。

有的時候，我們執行的動態SQL語句有返回值，那麼如何將這個返回值賦值給變量呢？看下面這兩段代碼就可以搞定這個問題。

代碼片段一：

create or replace procedure get_data(stuid varchar2)as
    strSQL varchar2(32767);
    strID varchar2(50);
    strName varchar2(50);
    strSex varchar2(10);
    iAge number(3);begin
    strSQL := 'select id, name, sex, age from tb_student where id=:a';
    execute immediate strSQL into strID, strName, strSex, iAge using stuid;
    dbms_output.put_line('ID:' || strID || ' ;Name:' || strName || ' ;Sex:' || strSex || ' ;Age:' || iAge);end;

這裡我將select得到的值保存在變量中，但是這裡只能返回一行記錄，如果有多行數據返回，則會出現異常。

代碼片段二：

create or replace procedure update_data(stuid varchar2, age number)as
    strSQL varchar2(32767);
    strID varchar2(50);
    strName varchar2(50);
    strSex varchar2(50);begin
    strSQL := 'update tb_student set age=:a where id=:b returning id, name, sex into :c, :d, :e';
    execute immediate strSQL using age, stuid returning into strID, strName, strSex;
    execute immediate 'commit'; -- 這樣也是可以的
    dbms_output.put_line('ID:' || strID || ' ;Name:' || strName || ' ;Sex:' || strSex);end;

這樣的話，我們就可以將更新之後的值，以及更新對應記錄的值返回到變量中；在上面的代碼中，我使用了一個returning into的關鍵語句，這個用起來比較簡單。returning into語句的主要作用是：

delete操作：returning返回的是delete之前的結果
insert操作：returning返回的是insert之後的結果
update操作：returning語句是返回update之後的結果

但是問題又來了，我們假定的是更新一條數據，如果更新的數據有兩條，甚至多條時，這個時候上面的存儲過程就會運行出錯。這個時候怎麼辦？這又是一個難點。

如果動態SQL語句是一個查詢語句，並且返回多行記錄，可以使用帶有子句bulk collect into的execute immediate語句。采用bulk collect into可以將查詢結果一次性地加載到集合中，我們可以在select into、fetch into、returning into語句中使用bulk collect into；但是需要特別注意的是，在使用bulk collect into時，所有的into變量都必須是集合類型。廢話少說，直接通過代碼來說明怎麼使用就好了。

create or replace procedure get_multi_data(stuid varchar2)is
    strSQL varchar2(32767);
    type tb_student_type is table of tb_student%rowtype;
    student_array tb_student_type;begin
    strSQL := 'select id, name, sex, age from tb_student where id=:a';
    execute immediate strSQL bulk collect into student_array using stuid;

    for i in student_array.first .. student_array.last loop
        dbms_output.put_line('ID:' || student_array(i).id 
                            || ' ;Name:' || student_array(i).name 
                            || ' ;Sex:' || student_array(i).sex 
                            || ' ;Age:' || student_array(i).age);
    end loop;end;

除了使用bulk collect into這種方法，我們還可以使用游標的方式，具體的代碼示例請參考《Oracle學習筆記——批處理利器游標》文章中的最後一段代碼。