動態sql和利用動態sql解決資料字典的讀取

oracle中的動態sql的一些理解，動態sql通俗說也就是執行的時候才去檢查的sql。
dml語句可以在pl/sql程式中載入，但是ddl語句是無法在pl/sql中執行的，這個時候就可以使用動態sql來完成ddl語句和不確定的dml語句。
首先在oracle資料庫開發pl/sql程式中的sql分為：靜態sql語句和動態sql語句。靜態sql語句是在程式編譯期間就明確的，執行的是確定的物件，而動態sql在pl/sql程式編譯時sql語句是不確定的。
編譯程式對動態語句部分不進行處理，只在程式執行時動態建立語句並分析語法然後執行。

procedure中包含動態sql執行ddl語句
create or replace procedure pro_test
(
table_name in varchar2,
column_1 in varchar2,
column_2 in varchar2
)
as
sql_in varchar2(50);
begin
sql_in:='create table table_name ( column_1 varchar2,column_2 varchar2)';
execute immediate sql_in;
exeception when others then
null;
end pro_test;
由於procedure是可利用pl/sql程式塊，所以可以直接在pl/sql來利用動態sql執行ddl操作
declare
sql_in varchar2(100):='create table test_database as select * from v$database';
begin
execute immediate sql_in;
end;
利用動態sql執行不確定的dml操作
create or replace procedure dml_sql
(
v_ab in number,
v_ac in varchar2
)
as
sql_in varchar2(100);
begin
sql_in:='insert into dave values(:1,:2)';
execute immediate sql_in using v_ab,v_ac; --動態執行插入，上面的values的:1和:2其實相當於形式引數，而using後的id，v_ac才是實際的引數
exception when others then
null;
end dml_sql;
上面中本地動態sql執行dml語句時使用了using子句，按順序將輸入的值繫結到變數，如果要輸出引數，可以執行動態sql的時候，使用returning into子句
create or replace procedure using_sql(id in number) as
p_id number;
p_name varchar2(10);
begin
execute immediate 'select id,name from dave where id=8' into p_id,p_name;
dbms_output.put_line(p_id||'的名字:'||p_name);
end;
oracle中提供了execute immediate語句來執行動態sql
execute immediate 動態sql語句 using 繫結引數列表 returing into 輸出引數列表
1 動態sql是指ddl和不確定的dml
2 繫結引數列表為輸入引數列表，其型別為in型別，在執行時刻和動態sql語句中的形式引數進行繫結
3 輸出引數列表為動態sql語句執行後返回的引數列表
4 由於動態sql在執行時刻進行確定，相對與靜態sql而言會損失一些系統效能來換取靈活性
利用動態sql在procedure中讀取資料字典
在oracle中非sys使用者無法在procedure讀取資料字典例如
create table v_test as select * from v$logfile where 1<>1;
建立v$test表，只保留v$logfile的結構
create or replace procedure v_ab
as
begin
insert into v_test
select * from sys.v$logfile d'
commit;
end;
提示表或檢視不存在，非sys使用者無法讀取，即使grant dba to user也一樣，但是sys使用者還是可以的，這應該是oracle對資料字典的一種保護吧，
此時我們利用靜態sql是無法完成的，試試動態sql了
create or replace procedure v_ab
as
begin
execute immediate ('inser into v_test select * from sys.v$logfile d')
commit;
end;
下面會顯示procedure已經完成。至於這裡面內部的oracle對資料字典的安全機制資料中暫時找不到其中的緣由，不過對於在procedure中涉及到資料字典讀取的我們確定是可以利用動態sql來處理了。

execute immediate中有個引數returning into ，剛開始我建立一個動態sql的procedure

create or replace procedure test_into_procedure
(
v_ab in varchar2,
v_ac in varchar2
)
as
v_ad varchar2(10);
v_ae varchar2(10);
sql_in varchar2(100);
begin
sql_in:='select no_stu,name_stu from student where no_stu=:1 and name_stu=:2';
execute immediate sql_in using v_ab,v_ac returning into v_ad,v_ae ;
dbms_output.put_line(v_ad||' '||v_ae);

exception when others then

null;
end;

提示建立成功，但是在

sql>execute test_into_procedure('02','Sf');

報ORA-06547: INSERT, UPDATE 或 DELETE 語句必須使用 RETURNING 子句。

後來查詢資料，發現動態sql中是存在的問題的，正如oracle所說的insert update delete語句才使用returning子句，把上述的procedure中的execute immediate修改成

execute immediate sql_in into v_ad,v_ae using v_ab,v_ac ;

這樣procedure也提示已經建立成功，execute時也可以達到理想的效果。oracle在編譯上述中有動態sql的procedure時，個人覺得是不會理解其中的execute immediate後的內容的，因為動態sql嗎，oracle只是在執行期間才去編譯檢視語法等，所以當存在動態sql的程式塊中我們還是需要認真的去檢視，本來寫程式碼就是一個比誰細心的工作。

[@more@]