Oracle本地動態 SQL

標籤：sdn   col   procedure   declare   一個   nat   varchar   欄位   bsp   

本地動態 SQL

首先我們應該瞭解什麼是動態 SQL，在 Oracle資料庫開發 PL/SQL塊中我們使用的 SQL

分為：靜態 SQL語句和動態 SQL語句。所謂靜態 SQL指在 PL/SQL塊中使用的 SQL語句在編

譯時是明確的，執行的是確定對象。而動態 SQL是指在 PL/SQL塊編譯時間 SQL語句是不確定

的，如根據使用者輸入的參數的不同而執行不同的操作。編譯器對動態語句部分不進行處理，

只是在程式運行時動態地建立語句、對語句進行文法分析並執行該語句。

Oracle中動態 SQL可以通過本地動態 SQL來執行，也可以通過 DBMS_SQL包來執行。

下面就這兩種情況分別進行說明：

一、本地動態 SQL

本地動態 SQL是使用 EXECUTE IMMEDIATE語句來實現的。

1、本地動態 SQL執行 DDL語句：

需求：根據使用者輸入的表名及欄位名等參數動態建表。

create or replace procedure proc_test

(

table_name in varchar2， --表名

field1 in varchar2， --欄位名

datatype1 in varchar2， --欄位類型

field2 in varchar2， --欄位名

datatype2 in varchar2 --欄位類型

) as

str_sql varchar2(500);

begin

str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’，’

||field2||’ ’||datatype2||’)’;

execute immediate str_sql; --動態執行 DDL語句

exception

when others then

null;

end ;

以上是編譯通過的預存程序代碼。下面執行預存程序動態建表。

SQL> execute proc_test(’dinya_test’，’id’，’number(8) not null’，’name’，’

varchar2(100)’);

PL/SQL procedure successfully completed

SQL> desc dinya_test;

Name Type Nullable Default Comments

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

ID NUMBER(8)

NAME VARCHAR2(100) Y

 

SQL>

到這裡，就實現了我們的需求，使用本地動態 SQL根據使用者輸入的表名及欄位名、

欄位類型等參數來實現動態執行 DDL語句。

2、本地動態 SQL執行 DML語句。

需求：將使用者輸入的值插入到上例中建好的 dinya_test表中。

create or replace procedure proc_insert

(

id in number， --輸入序號

name in varchar2 --輸入姓名

) as

str_sql varchar2(500);

begin

str_sql:=’insert into dinya_test values(:1，:2)’;

execute immediate str_sqlusing id，name; --動態執行插入操作

exception

when others then

null;

end ;

執行預存程序，插入資料到測試表中。

SQL> execute proc_insert(1，’dinya’);

PL/SQL procedure successfully completed

SQL> select * from dinya_test;

ID NAME

1 dinya

在上例中，本地動態 SQL執行 DML語句時使用了 using子句，按順序將輸入的值綁

定到變數，如果需要輸出參數，可以在執行動態 SQL的時候，使用 RETURNING INTO 子句，

如：

declare

p_id number:=1;

v_count number;

begin

v_string:=’select count(*) from table_name a where a.id=:id’;

execute immediate v_stringinto v_count using p_id;

end ;

更多的關於動態 SQL中關於傳回值及為輸出輸入綁定變數執行參數模式的問題，請

讀者自行做測試。

 

二、使用 DBMS_SQL包

使用 DBMS_SQL包實現動態 SQL的步驟如下：A、先將要執行的 SQL語句或一個語句

塊放到一個字串變數中。B、使用 DBMS_SQL包的 parse過程來分析該字串。C、使用

DBMS_SQL包的 bind_variable過程來綁定變數。D、使用 DBMS_SQL包的 execute函數來執

行語句。

1、使用 DBMS_SQL包執行 DDL語句

需求：使用 DBMS_SQL包根據使用者輸入的表名、欄位名及欄位類型建表。

create or replace procedure proc_dbms_sql

(

table_name in varchar2， --表名

field_name1 in varchar2， --欄位名

datatype1 in varchar2， --欄位類型

field_name2 in varchar2， --欄位名

datatype2 in varchar2 --欄位類型

)as

v_cursor number; --定義游標

v_string varchar2(200); --定義字串變數

v_row number; --行數

begin

v_cursor:=dbms_sql.open_cursor; --為處理開啟游標

v_string:= ’ create table ’ ||table_name|| ’ ( ’ ||field_name1|| ’ ’

||datatype1||’，’||field_name2||’ ’||datatype2||’)’;

dbms_sql.parse(v_cursor，v_string，dbms_sql.native); --分析語句

v_row:=dbms_sql.execute(v_cursor); --執行語句

dbms_sql.close_cursor(v_cursor); --關閉游標

exception

when others then

dbms_sql.close_cursor(v_cursor); --關閉游標

raise;

end;

以上過程編譯通過後，執行過程建立表結構：

SQL> execute proc_dbms_sql(’dinya_test2’，’id’，’number(8) not null’，’

name’，’varchar2(100)’);

PL/SQL procedure successfully completed

SQL> desc dinya_test2;

Name Type Nullable DefaultComments

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

ID NUMBER(8)

NAME VARCHAR2(100) Y

 

SQL>

2、使用 DBMS_SQL包執行 DML語句

需求：使用 DBMS_SQL包根據使用者輸入的值更新表中相對應的記錄。

查看錶中已有記錄：

SQL> select * from dinya_test2;

ID NAME

1 Oracle

2 CSDN

3 ERP

SQL>

建預存程序，並編譯通過：

create or replace procedure proc_dbms_sql_update

(

id number，

name varchar2

)as

v_cursor number; --定義游標

v_string varchar2(200); --字串變數

v_row number; --行數

begin

v_cursor:=dbms_sql.open_cursor; --為處理開啟游標

v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’;

dbms_sql.parse(v_cursor，v_string，dbms_sql.native); --分析語句

dbms_sql.bind_variable(v_cursor，’:p_name’，name); --綁定變數

dbms_sql.bind_variable(v_cursor，’:p_id’，id); --綁定變數

v_row:=dbms_sql.execute(v_cursor); --執行動態ＳＱＬ

dbms_sql.close_cursor(v_cursor); --關閉游標

exception

when others then

dbms_sql.close_cursor(v_cursor); --關閉游標

raise;

end;

執行過程，根據使用者輸入的參數更新表中的資料：

SQL> execute proc_dbms_sql_update(2，’csdn_dinya’);

PL/SQL procedure successfully completed

SQL> select * from dinya_test2;

ID NAME

 

1 Oracle

2 csdn_dinya

3 ERP

SQL>

執行過程後將第二條的 name欄位的資料更新為新值 csdn_dinya。這樣就完成了使用

dbms_sql包來執行 DML語句的功能。

使用 DBMS_SQL中，如果要執行的動態語句不是查詢語句，使用 DBMS_SQL.Execute或

DBMS_SQL.Variable_Value來執行，如果要執行動態語句是查詢語句，則要使用

DBMS_SQL.define_column定義輸出變數，然後使用 DBMS_SQL.Execute，

DBMS_SQL.Fetch_Rows， DBMS_SQL.Column_Value及 DBMS_SQL.Variable_Value來執行查詢

並得到結果。

Oracle本地動態 SQL