在某些特殊的情況下,在 PL/SQL 中使用標準的 SQL 語法或 DML 語法不能符合自己的需求,比如需要動態 建表或某個不確定的操作需要動態執行。這就需要使用動態 SQL 來呈現。一般的 PL/SQL設計中,在 DML 和Transaction控制的語法中可以直接使用 SQL,但是 DDL 語法及DCL系統控制語法卻不能在 PL/SQL 中直接使用,要想在 PL/SQL 中使用 DDL 語法及系統控制語法, 可以透過使用動態 SQL。
首先我們應該了解什麼是動態 SQL,在 Oracle 資料庫 PL/SQL 中我們使用的 SQL 分為:靜態 SQL 語法和動態 SQL 語法。所謂靜態 SQL 指在 PL/SQL 中使用的 SQL 語法在編譯時是明確的,執行 的是確定對象。而動態 SQL 是指在 PL/SQL 編譯時 SQL 語法是不確定的,如根據使用者輸入的參數 的不同而執行不同的操作。Store Procedure 對動態語法部分不進行處理,只是在 Procedure 運作時 動態地建置語法、對語法進行語法分析並執行該語法。
Oracle 中動態 SQL 可以透過本地動態 SQL 來執行,也可以透過 DBMS_SQL 包來執行。下面就 這兩種情況分別進行說明:
一、本地動態 SQL (Local Dynamic)
本地動態 SQL 是使用 EXECUTE IMMEDIATE 語法來執行的。
1、本地動態 SQL 執行 DDL 語法: 需求:根據使用者輸入的表名及 段名等參數動態建表。
CREATE OR REPLACE PROCEDURE proc_test
( table_name IN VARCHAR2,
field1 datatype1 field2 datatype2
IN VARCHAR2, IN VARCHAR2,
IN VARCHAR2, IN VARCHAR2)
AS
str_sql VARCHAR2(500);
BEGIN
str_sql:='create Table'||table_name||'('||field1||' '||datatype1||','||field2||' '|| datatype2||')';
EXECUTE immediate str_sql; EXCEPTION
WHEN OTHERS THEN NULL; END;
/
|
下面執行 Store procedure 動態建製表格
SQL> execute proc_test('fb_test','id','number(8) not null','name','varchar2(100)');
PL/SQL procedure successfully completed
SQL> desc fb_test;
Name Type Nullable Default Comments ---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y
SQL>
|
到這裏,就實現了我們的需求,使用本地動態 SQL 根據使用者輸入的 table nalme 及欄位名 稱、欄位型態等參數來執行動態執行 DDL 語法。
2、本地動態 SQL 執行 DML 語法。
需求:將使用者輸入的值插入到上例中建好的 fb_test 表中。 create or replace procedure
proc_insert
(
id in number,
name in varchar2 ) as
str_sql varchar2(500); begin
str_sql:=’insert into fb_test values(:1,:2)’; execute immediate str_sql using id,name; exception
when others then null;
end ;
/
|
執行 儲過程,插入資料到測試 table 中。
SQL> execute proc_insert(1,’fb’); PL/SQL procedure successfully completed SQL> select * from fb_test;
|
在上面範例中,本地動態 SQL 執行 DML 語法時使用了 using 子句,按順序將輸入的值綁定到變 量,如果需要輸出參數,可以在執行動態 SQL 的時候,使用 RETURNING INTO 子句,如: declar
p_id number:=1;
v_count number; begin
v_string:=’select count(*) from table_name a where a.id=:id’;
execute immediate v_string into v_count using p_id;
end ;
|
二、使用 DBMS_SQL Package
使用 DBMS_SQL實現動態 SQL 的步驟如下:
A、先將要執行的 SQL 語法或一個語法放到一個 符串變量中。
B、使用 DBMS_SQL 包的 parse 過程來分析該 符串。C、使用 DBMS_SQL 包 的 bind_variable 過程來綁定變量。D、使用 DBMS_SQL 包的 execute 函數來執行語法。
1、使用 DBMS_SQL執行 DDL 語法
需求:使用 DBMS_SQL 包根據使用者輸入的 table name、欄位名稱及欄位資料型態。
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;
/
|
以上編譯透過後,執行 Procedure 建置表格:
SQL> execute proc_dbms_sql(’fb_test2’,’id’,’number(8) not null’,’name’,’varchar2(100)’); PL/SQL procedure successfully completed
SQL> desc fb_test2;
Name Type Nullable Default Comments
---- ------------- -------- --------------- --------
ID NUMBER(8) NAME VARCHAR2(100) Y
|
2、使用 DBMS_SQL 包執行 DML 語法
需求:使用 DBMS_SQL 根據使用者輸入的值更新 table 中相對應的記錄。 查看表中已有記錄:
SQL> select * from fb_test2; ID NAME
1 Oracle 2 CSDN 3 ERP
SQL>
|
建製Procedure:
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 fb_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_fb’); PL/SQL procedure successfully completed
SQL> select * from fb_test2;
ID NAME
1 Oracle
2 csdn_fb 3 ERP
SQL>
|
執行過程後將第二條的 name 的資料更新為新值 csdn_fb。這樣就完成了使用 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 來執行查詢並得到結果。
沒有留言:
張貼留言