SQL> conn scott/system
SQL>
1 create or replace procedure p_emp(salary number)
2 as
3 cursor_name integer;
4 rows_processes integer;
5 begin
6 cursor_name:=dbms_sql.open_cursor; --開啟遊標
7 dbms_sql.parse(cursor_name,'delete from emp where sal>:x',dbms_sql.native);--解析遊標,dbms_sql.parse三個引數:遊標名稱,要解析的sql或ddl或dml,第三個引數
8 dbms_sql.bind_variable(cursor_name,':x',salary);--因為解析中用到了繫結變數,所以此處採用dbms_sql.bind_variable過程,把儲存過程的輸入引數salary傳遞給:x繫結

--變數
9 rows_processes:=dbms_sql.execute(cursor_name);--執行遊標(也就是執行解析過的sql或dml或ddl)
10 dbms_sql.close_cursor(cursor_name);--關閉遊標,用完了嗎;不關就會佔用記憶體
11 exception --exception關鍵字
12 when others then
13 dbms_sql.close_cursor(cursor_name); --採用異常關閉遊標
14* end;

Procedure created.

SQL> exec p_emp(1100); --呼叫儲存過程,從emp表中刪除工資小於1100的記錄

PL/SQL procedure successfully completed.

SQL> commit;---最好把commit寫進dbms.sql的儲存過程中,dbms_sql不會提交解析過的dml

Commit complete.

SQL> r
1 create or replace procedure p_dynamic(string in varchar2)
2 as
3 cursor_name integer;
4 result integer;
5 begin
6 cursor_name:=dbms_sql.open_cursor;
7 dbms_sql.parse(cursor_name,string,dbms_sql.native);
8 result:=dbms_sql.execute(cursor_name);
9 dbms_sql.close_cursor(cursor_name);
10* end;

Procedure created.

SQL> exec p_dynamic('create table pp(a int)');---看到沒,報許可權不足啊,怪了嗎??
BEGIN p_dynamic('create table pp(a int)'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.P_DYNAMIC", line 7
ORA-06512: at line 1

SQL> r
1 create or replace procedure p_dynamic(string in varchar2)
2 as
3 cursor_name integer;
4 result integer;
5 begin
6 cursor_name:=dbms_sql.open_cursor;
7 dbms_sql.parse(cursor_name,string,dbms_sql.native);
8 result:=dbms_sql.execute(cursor_name);
9 dbms_sql.close_cursor(cursor_name);
10* end;

Procedure created.

1 create or replace procedure p_dynamic(string in varchar2) --這個儲存過程應用dbms_sql執行一個ddl建表語句
2 authid current_user --為以上報許可權不足的儲存過程新增此行,再次呼叫儲存過程就不會報錯了
3 as
4 cursor_name integer;
5 result integer;
6 begin
7 cursor_name:=dbms_sql.open_cursor;--開一個遊標
8 dbms_sql.parse(cursor_name,string,dbms_sql.native);--解析遊標
9 result:=dbms_sql.execute(cursor_name);--執行遊標
10 dbms_sql.close_cursor(cursor_name);--關閉遊標
11* end;

Procedure created.

SQL> exec p_dynamic('create table pp(a int)');--呼叫以上儲存過程建表,ok了

PL/SQL procedure successfully completed.

SQL> desc pp;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)

-bash-3.2$ sqlplus scott/system

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 2 02:02:38 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure p_copy(source in varchar2,destination in varchar2) --利用dbms_sql複製一個表資料到另一個表(二表結構相同)
--儲存過程2個輸入引數各為:源與目標表的名字
2 is
3 id_var number; --源與目標表的列1
4 name_var varchar2(30);--同上,列2
5 birthdate_var date;--同上,列3
6 source_cursor integer;--提取源表資料的遊標
7 destination_cursor integer;--同上,插入到目標表的遊標
8 ignore integer;--執行遊標
9 begin
10 source_cursor:=dbms_sql.open_cursor;--開源表遊標
11 dbms_sql.parse(source_cursor,'select id,name,birthdate from '||source,dbms_sql.native);--解析源表遊標,此處第二個引數用了||連線符,傳入引數source(儲存過程

--的輸入引數)
12 dbms_sql.define_column(source_cursor,1,id_var);--定義接收源表遊標資料的列,此處為列1,對應儲存過程內部定義引數 id_var
13 dbms_sql.define_column(source_cursor,2,name_var,30);--同上,列2
14 dbms_sql.define_column(source_cursor,3,birthdate_var);--同上,列3
15 ignore:=dbms_sql.execute(source_cursor);--執行源表遊標
16 destination_cursor:=dbms_sql.open_cursor;--開目標表遊標
17 dbms_sql.parse(destination_cursor,'insert into '||destination ||' values (:id_bind,:name_bind,:birthdate_bind)',dbms_sql.native);
--解析目標表遊標,以繫結變數方式插入資料到目標表

18 loop --用一個loop迴圈處理，因為源遊標會提取多行記錄啊
19 if dbms_sql.fetch_rows(source_cursor)>0 then --使用dbms_sql.fetch_rows判斷源遊標提取資料是否還有記錄；此處dbms_sql.fetch_rows用於從源遊--標提取記錄
20 dbms_sql.column_value(source_cursor,1,id_var);--dbms_sql.column_value返回特定遊標具體位置元素的值；它用於訪問dbms_sql.fetch_rows提取的--記錄
21 dbms_sql.column_value(source_cursor,2,name_var);--column_name有三個引數：遊標名稱，遊標中特定位置的元素，要返回的值
22 dbms_sql.column_value(source_cursor,3,birthdate_var);--此處三個column_value用於返回源表遊標三個引數（對應select三列）給儲存過程內部定義的變數
23
24
25 dbms_sql.bind_variable(destination_cursor,':id_bind',id_var);---牛吧，以上三個column_name接收了源遊標的引數值，這裡用bind_variable把以上引數的值傳遞給目

--標表遊標，這不就實現把源表的資料複製到了目標表了嗎，牛
26 dbms_sql.bind_variable(destination_cursor,':name_bind',name_var);--同上
27 dbms_sql.bind_variable(destination_cursor,':birthdate_bind',birthdate_var);--同上;bind_variable三個引數：遊標名稱，遊標中對應要解析sql或dml或ddl的繫結變

                                                                               ---量，要傳遞給繫結變數的引數
28
29 ignore:=dbms_sql.execute(destination_cursor);--執行目標表遊標，說明在儲存過程中定義一個ignore可以在儲存過程中多處執行遊標處使用,指的是：execute過程
30 else --否則如果從源遊標提取不到記錄了
31 exit; --牛了，就退出源遊標了
32 end if;
33 end loop; ---對應上面的loop
34 commit; ---處理了sql,dml,ddl就提交sql了，儲存過程內部不會自動提交
35 dbms_sql.close_cursor(source_cursor);--處理完了工作，也提交了工作，就關閉遊標吧，別浪費記憶體喲
36 dbms_sql.close_cursor(destination_cursor);
37 exception --為了健壯性，加上異常處理
38 when others then
39   if dbms_sql.is_open(source_cursor) then --用dbms_sql.is_open(遊標名稱）判斷遊標是否開啟
40     dbms_sql.close_cursor(source_cursor); --用close_cursor關遊標
41   end if;
42   if dbms_sql.is_open(destination_cursor) then
43     dbms_sql.close_cursor(destination_cursor);
44 end if;
45 raise;
46 end;
47 /

Procedure created.

SQL> exec p_copy('source','destination'); --呼叫以上儲存過程，複製源表資料到目標表

PL/SQL procedure successfully completed.

oracle dbms_sql執行查詢select_dml_ddl(一）

相關文章