Oracle 儲存過程中的DDL語句

realkid4發表於2011-10-10

 

Oracle的儲存過程,是我們使用資料庫應用開發的重要工具手段。在儲存過程中,我們大部分應用場景都是使用DML語句進行資料增刪改操作。本篇中,我們一起探討一下資料定義語句DDL在儲存過程中使用的細節和要點。

 

1、“借道而行”的DDL

 

Oracle PL/SQL和儲存過程程式開發原則上,應該是不鼓勵在SP中使用DDL語句的。首先一個表現,就是Oracle在編譯時就不允許直接在SP中使用DDL語句。下面我們使用Oracle 10gR2作為實驗環境。

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE     10.2.0.1.0       Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

 

建立儲存過程p_test_nc,進行簡單的資料表建立。

 

 

SQL> create or replace procedure P_TEST_NC

  2  is

  3  begin

  4    create table t (id number);

  5  end P_TEST_NC;

  6  /

 

Warning: Procedure created with compilation errors

 

SQL> select name, text from user_errors;

 

NAME       TEXT

---------- --------------------------------------------------------------------------------

P_TEST_NC  PLS-00103: 出現符號 "CREATE"在需要下列之一時:

            begin case declare exit

              for goto if loop mod null pragma raise return select update

              while with

              <<

              close current delete fetch lock insert open rollback

              savepoint set sql execute commit forall merge pipe

 

 

顯然,在編譯時Oracle就報錯不允許儲存過程建立。之後的實驗droptruncate table操作,也都是不允許直接在儲存過程中書寫DDL語句。說明起碼使用直接的DDL語句,儲存過程是不能編譯透過的。

 

那麼,有沒有什麼折中的方法呢?我們說是有的,就是藉助“execute immediate”方法,“繞過”編譯過程中對DDL的遮蔽。我們使用truncate table DDL語句實驗。

 

 

SQL> create or replace procedure P_TEST_NC

  2  is

  3  begin

  4    execute immediate 'truncate table t';

  5  end P_TEST_NC;

  6  /

Procedure created

 

 

編譯透過了,DDL語句以一個字串的形式避開了編譯時Oracle的語法檢查,編譯成功。那麼,執行起來會不會報執行時錯誤呢?

 

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

 

執行成功,說明:Oracle儲存過程中,可以使用exectue immediate語句繞開編譯時對DDL語句的檢查,生成執行程式碼。

 

 

2SPDDL許可權

 

任何程式編譯執行都會伴隨著語法語義的一系列檢查。使用execute immediate雖然可以迴避編譯時檢查,但是SQL語句還是面臨著執行時檢查的問題。下面看實驗的例子。

 

--scott使用者下進行試驗;

SQL> create or replace procedure P_TEST_NC

  2  is

  3  begin

  4    execute immediate 'create table t(id number)';

  5  end P_TEST_NC;

  6  /

Procedure created 編譯時透過;

 

SQL> exec p_test_nc;

 

begin p_test_nc; end;

 

ORA-01031: 許可權不足

ORA-06512: "SCOTT.P_TEST_NC", line 4

ORA-06512: line 1

 

 

在使用者自己的schema下建立資料表,難道是不允許的嗎?顯然不是。

 

 

SQL> create table m (id number);

Table created

 

 

單獨建立是允許的,說明是由於許可權機制導致的問題。我們切換到sys使用者上,提高scott使用者許可權。

 

 

Connected as SYS

--賦予最高建立資料表的系統許可權;

SQL> grant create any table to scott;

Grant succeeded

 

 

切換回scott使用者,繼續實驗。

 

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

SQL> select * from t;

        ID

----------

 

執行成功!這個原因是什麼呢?還是由於儲存過程許可權體系特點和DDL語句特點共同造成的。

 

在之前筆者的系列文章《所有者許可權和呼叫者許可權》(http://space.itpub.net/17203031/viewspace-692161)中,介紹了Oracle儲存過程採用的兩種許可權體系方式和role許可權在儲存過程執行中的特殊性。

 

預設情況下,Oracle對儲存過程是使用所有者許可權,也就是說:如果使用者B呼叫了使用者A schema下的一個儲存過程,其中使用的物件許可權和系統許可權,全部都是使用者A的。如果使用者A沒有許可權,使用者B執行要報錯。

 

同時,使用者的角色許可權在進入儲存過程後,會被剝離掉,是不其效果的。

 

結合上面的實驗,就好解釋了:scott自身只擁有一個resource的角色許可權,單獨在SQL中使用沒有問題。進入到SP之後,這個create table的許可權就被剝離掉了。而該SP存在被其他使用者呼叫生成資料表的可能。所以會在執行時報錯許可權不足。

 

當我們顯示的賦予scott使用者create any table/create table之後,系統許可權就可以滲透到SP中起效果了。

 

這並不是解決該問題的唯一方法。此處我們可以使用呼叫者許可權機制,改寫SP程式碼。首先我們剔除掉scottcreate any table 許可權。

 

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

 

SQL> revoke create any table from scott;

Revoke succeeded

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> exec p_test_nc;

begin p_test_nc; end;

 

ORA-01031: 許可權不足

ORA-06512: "SCOTT.P_TEST_NC", line 4

ORA-06512: line 1

 

 

我們改寫程式碼為:

 

 

SQL> create or replace procedure P_TEST_NC

  2  Authid Current_User

  3  is

  4  begin

  5    execute immediate 'create table t (id number)';

  6  end P_TEST_NC;

  7  /

Procedure created

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

 

執行成功,這裡使用“authid Current_user”將儲存過程轉化為呼叫者許可權。每次呼叫儲存過程,都是動態根據呼叫者的許可權構成去判定是否有許可權,這樣就回避了該問題的出現。

 

總之:在使用DDL在儲存過程中時,許可權管理和使用的複雜度是在增加。

 

 

4DDL對事務的提交影響

 

DDL語句放置在儲存過程中,潛在最大風險就是對事務管理的破壞。在Oracle中,如果呼叫一個DDL語句,潛藏效果就是將當前會話的未提交事務進行提交。這個過程顯然是對原有的事務邏輯破壞。

 

 

SQL> create table m (id number);

Table created

 

SQL> select * from m;

        ID

----------

 

SQL> create or replace procedure P_TEST_NC

  2  is

  3  begin

  4    insert into m values (3);

  5    execute immediate 'truncate table t';

  6 

  7    rollback;

  8  end P_TEST_NC;

  9  /

 

Procedure created

 

--執行程式碼

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

--事務提交

SQL> select * from m;

        ID

----------

         3

 

 

從上面的例子上,我們可以清楚的看到現象。由於中間的truncate table操作,引起資料表m的插入操作被提交commit。而真正的事務邏輯可能是一個rollback

 

所以,在SP中使用DDL命令,可能引起業務邏輯的不可控提交和資料不一致,這個風險在任何應用中是不可以允許的。

 

那麼,有沒有方法迴避這個過程呢?經一個同事提醒,的確可以使用手段迴避。

 

 

5DDL與自治事務

 

自治事務(AUTONOMOUS_TRANSACTION)是保證在事務進行過程中一段獨立的事務過程。如果在DDL操作外套入一個自治事務過程,是否就可以迴避問題了。

 

 

SQL> select * from m;

 

        ID

----------

 

SQL> create or replace procedure P_TEST_NC is

  2    procedure p_inner_test

  3    is

  4    PRAGMA AUTONOMOUS_TRANSACTION;

  5    begin

  6      --呼叫ddl

  7      execute immediate 'truncate table t';

  8    end;

  9  begin

 10    insert into m values (3);

 11    p_inner_test;

 12 

 13    rollback;

 14  end P_TEST_NC;

 15  /

Procedure created

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

\

SQL> select * from m;

        ID

----------

 

 

實驗成功,透過自治事務的確可以迴避DDL的事務問題。

 

6、結論

 

DDLSP中,與常規的DML操作差異很大。這種差異不僅僅是語法上,更多的是許可權、事務等更深層次複雜的差異。所以,從Oracle的角度看,儘量少在SP中使用DDL語句,避免出現不可控的問題。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17203031/viewspace-708923/,如需轉載,請註明出處,否則將追究法律責任。

相關文章