本篇文章主要彙總分享oracle中存儲過程的基礎用法、整體的書寫規範、格式要求，儘可能多的細的講述和存儲過程有關的知識點。 ...
作為一個oracle界和廚師界的生手，筆者想給大家分享討論下存儲過程的知識，因為在我接觸的通信行業中，存儲過程的使用還是占據了一小塊的地位。
存儲過程是什麼？不得不拿下百度詞條的解釋來：“存儲過程（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，存儲在資料庫中， 經過第一次編譯後再次調用不需要再次編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要對象。” 其實就似我們經過一系列的材料準備和烹飪過程準備的一道菜，這樣的菜內部消化是存儲過程，而端出來給別人吃就是函數了（比喻好像不恰當……），後邊也不提函數， 因為懂得了存儲過程，函數也就會了，只要稍微瞭解下結構就行。
雖然說用上了存儲過程，也喜歡上存儲過程，但是還是得在分享時提提它的利和弊，因為筆者也被它的弊坑得很慘（要遷移近百個存儲過程？表模型也要改造有大變動？ 還以前基本用dblink（這個坑貨更不想提，咱還是忘記它吧！）傳輸？），然後苦逼的“搬磚” 生涯開始了（如果是自己做的菜，怎麼著也吃得下，不好吃也很快就能改好菜譜， 但別人的菜別人的菜譜……一言難盡！）。你看，存儲過程用起來是爽，很多時候也提高效率，筆者也是算java開發的，比起用java一次次的從廚房拿材料出來到java代碼裡面來搞事情， 在方便的時候還是傾向於在廚房裡把菜做好，這樣也減少了網路傳輸，因為很多時候調來調去處理好數據最終還是寫到表裡面去，所以才把整個邏輯直接包在存儲過程中，而且有plsql， 也還算方便調試，特特特便利的是，如果這盤菜按照菜譜做出來不好吃，誒，我們改改菜譜在廚房裡立馬就能做新的出來，維護性也很強，如果邏輯在java裡面就得部署升級重啟項目。 但是爽歸爽了，在遷移的時候就懵逼了，存儲過程和資料庫綁在一起，庫一旦變了，那得重新把它們綁起來，而且前期開發的時候如果未把這些存儲過程功用記錄好，遷移就沒人知道 它是拿來幹嘛的，是不是必須的（不可否認，總會有些髒東西在廚房裡面，或多或少會把這些東西也帶到新廚房）。
閑談這麼多，該到一些基礎的語法了（由於筆者看到大寫容易文盲，所以多數字母都是小寫）。
1 create or replace procedure sp_hll_test_20170415 2 /* 3 創建[或者替換] 存儲過程 存儲過程的名字 4 所有對象的名稱都一樣限製為最多30個位元組……筆者一般習慣以sp_起頭，而且不喜歡把名稱包裹在英文雙引號裡面 5 因此得提下雙引號一些常見的用法： 6 1.當為oracle特殊關鍵字時（不建議存儲過程啊表啊欄位啊等名稱使用oracle關鍵字（可搜索瞭解）命名） 7 2.用於一些格式字元串中包裹非法格式 8 （例如像用於select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh24"小時"mi"分"ss"秒"') from dual; 9 但是在select 'yyyy"年"mm"月"dd"日" hh24"小時"mi"分"ss"秒"' from dual;中則雙引號正常輸出。 10 ps：dual表的用途如果不清楚，還是去網上搜索下吧。 11 ） 12 3.用於嚴格區分大小寫（在sql語句中大小寫不敏感，但在資料庫中數據值大小寫敏感） 13 （一般而言我們創建表、欄位、存儲過程、等對象，oracle是預設大寫名稱的，所以要是亂加雙引號會導致你找不到你認為建好的表， 14 用不了你認為你建好的存儲過程，因為你使用它們的時候忘了雙引號區分大小寫。 15 ） 16 當然使用了英文雙引號包裹存儲過程名稱的話，這兩個引號是不計入長度的。 17 而且得註意，在oracle中，單個漢字的長度，根據設置不同，占2(ZHS16GBK字元集)~3(AL32UTF8字元集)個位元組， 18 同時也可提下在oracle中，下麵這條語句兩個值可是不同的： 19 select length('abcd啊啵唓嘚'),lengthb('abcd啊啵唓嘚') from dual; 20 前者把單個漢字算一個長度，後者根據字元集不同算2~3個長度。 21 (安裝oracle預設是AL32UTF8字元集，字元集修改為ZHS16GBK字元集的方法： 22 CONNECT SYS_NAME/SYS_PASSWORD AS SYSDBA --根據自己的實際情況登入 23 SHUTDOWN IMMEDIATE; 24 STARTUP MOUNT; 25 ALTER SYSTEM ENABLE RESTRICTED SESSION; 26 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 27 ALTER SYSTEM SET AQ_TM_PROCESSES=0; 28 ALTER DATABASE OPEN; 29 ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; 30 SHUTDOWN IMMEDIATE; 31 STARTUP; 32 ps：不是自己的廚房（資料庫）可別亂動哦！ 33 )。 34 ps：創建一個存儲過程也就是準備一道菜譜，菜譜的具體內容都在下麵一一列出。可千萬要明確好是準備新的菜譜， 35 不要擅自把別人的菜譜替換為你的菜譜了，這樣做出來的菜可不一定是顧客要吃的。 36 也就是說不要隨便使用"or replace" ，在新建每個存儲過程的時候，務必去除這兩個詞（這樣的話在新建如果重名就會有提示）， 37 或者確保自己的存儲過程不和已有的存儲過程重名，這邊就可提下如何查看已有存儲過程的方法： 38 1.在plsql中一個SQL視窗內輸入存儲過程的名稱，通過按住ctrl鍵，然後左鍵單擊存儲過程名字，就可進入存儲過程的內容程式視窗， 39 要是進不了，恭喜您，該名稱可用於新建新的存儲過程。也可通過右擊存儲過程名稱，通過彈出的右鍵菜單中“查看”、“編輯”進入內容視窗， 40 在編輯模式下才可實時修改存儲過程內容，然後點擊運行或者按F8進行編譯，如果有問題有錯誤都會及時反饋在視窗下部的矩形區域內。 41 2.在plsql左側對象資源欄中展開Procedures，然後在搜索框中輸入存儲過程名稱，通過回車進行搜索，找到對應的存儲過程後也可在其名稱上右擊 42 打開右鍵菜單。 43 3.最靠譜的語句查詢來了，上面第2點也就相當於查詢all_objects這個表： 44 select * from all_objects a where a.object_type = 'PROCEDURE' 45 and a.object_name = '你的存儲過程名稱，記得大寫(當然你建存過時定製化了有小寫的請忽略，不想和你說話……)，用plsql可雙擊選中一串信息，然後右擊-選擇-大寫'; 46 ps：正如表名含義所示，該表記錄了所有對象的信息，所以存儲過程、表、視圖、序列、包、函數……一系列的對象你都可以查得到。 47 */ 48 ( 49 /* 50 當要創建的存儲過程需要一些外部參數時，就用括弧括起來，沒有參數的時候就不用加這對括弧了 51 參數基本格式：param1 [ in | out [nocopy] | in out [nocopy] ] type1 [(default | :=) value1] 52 參數名 [ 入參(預設，可省略) | 出參 | 既入又出的參] 參數對應的類型 [預設 值] 53 ps：參數的類型不能指定長度 54 註意：in {只能夠將實參傳遞給形參，在存儲過程內部只能讀取使用該值，無法修改。可為變數或常量} 55 out {不管外部實參是否有值，進入存儲過程內部初始值總是null，在內部可以任意使用修改該參數的值，存儲過程執行完畢後，形參的值會傳遞給實參。必須為變數} 56 in out {既可正常接收實參值，又可在結束時傳遞形參值給實參。必須為變數} 57 out和in out都可加nocopy，加上nocopy就可能是按引用傳遞（nocopy是一個提示而不是指令，編譯器可以決定是否執行該項），沒加則是按值傳遞。 58 某些情況下會有效率提升，在遇到異常時候兩者有不同之處要註意，筆者未使用涉及nocopy，詳情略過不表。 59 ps：菜譜要是沒有其它特殊進口材料要用，就不需要進口的包裝袋了 60 */ 61 62 /* 63 Warning(警告)：請不要學習筆者下麵的用法，參數、變數等命名為a、b、c、d……務必發揮你英文特長或網路漢英詞典的優勢，將參數、變數命名的有含義，最好加上註釋註明含義。 64 同時也不可直接使用對應表的對應欄位名稱作為參數名稱或變數名稱，例如： 65 參數名或變數名為table_name，user_tables表的table_name欄位，那麼往往會遇到在存儲過程主體中使用如下模式的語句： 66 select ut.status into v_status from user_tables ut where ut.table_name = table_name; 67 當然，你的本意可能是想讓ut.table_name = 入參table_name，可是對於sql語句來說，這個table_name就是user_tables裡面的table_name列，而對錶來說， 68 它每行的ut.table_name總是等於它的table_name，所以select ut.status 返回的就是整個表的status列的值，你還可以把這個列的值存到你定義的單值變數v_status中嗎？當然不行啦。 69 因此，參數或變數要有含義，而有欄位名稱含義的參數也得加些東西改動。 70 筆者習慣的參數或變數模式為：i_(入參)、o_(出參)、io_(入出參)、v_(變數)、c_(游標)…… 71 72 同時在oracle中，使用" := "進行初始化或賦值的操作，而" = " 則是比較左右兩邊的值是否相等返回true或false的boolean值。 73 */ 74 75 i_a in number, -- 入參a，in可省略 76 i_b date default sysdate, -- 入參b，用default設定預設值 77 i_c varchar2 := 'default c value', -- 入參c，用:=設定預設值 ps：預設值就相當於大眾口味，當然你也可以改動它，作為湖(fu)南(lan)人(yin)我表示要特辣 78 o_d out number, -- 出參d 79 i_status user_tables.status%type, 80 /* 81 i_status user_tables.status%type 82 一種實用的設定參數類型的方式，它的類型會隨著user_tables這個表的status的這個欄位的類型一起變動保持一致， 83 當該個參數實質數據來源於某個表某個欄位，可仿照上例設置，就不用擔心該個欄位的類型有變動，長度有變化之類的， 84 導致後邊又得來改動這個參數的類型。 85 */ 86 io_e in out varchar2 -- 既入又出的參f 87 ) 88 89 AUTHID DEFINER 90 /* 91 AUTHID DEFINER(可省略，預設為這個) | AUTHID CURRENT_USER 92 前者表示這個存儲過程使用創建這個存儲過程的用戶的許可權來運行--定義者許可權。 93 後者表示這個存儲過程使用當前調用它的用戶的許可權來運行--調用者許可權。 94 這樣有什麼用呢？一個用戶一般對應一個schema（不知可否對應多個，暫不考慮），該用戶的schema名等於用戶名，並作為該用戶預設schema。 95 而這個schema就對應了這個用戶下麵的所有數據對象的集合，用戶如果使用自己的東西，當然可以不用打招呼：select * from 我自己的表; 96 而要是其它用戶的表你也有查詢許可權，那用之前還是需要打下招呼：select * from 其它用戶schema名.其它用戶schema下的表; 97 同理，其它對象如視圖、函數、存儲過程等等都需要這麼做。我們總是喜歡偷懶的，所以調用自己的東西總是不會打招呼，因此存儲過程裡面查詢 98 的表總是會沒帶schema。 99 可要是有這種情況：我們大家都有a、b、c三張表，都想要彙總a、b表信息放到c表中（就是想做的活是一模一樣的），哥們1號就創建了存儲過程來做這個活， 100 但是悲劇的事情發生了，其他哥們包括我，都想用這個存儲過程，誒，都去幫哥們1號幹活去了，都去操作彙總哥們1號的表了。想各自都乾各自的活怎麼辦？ 101 難道只能像哥們1號一樣，都各自建各自的存儲過程嗎？因此有AUTHID CURRENT_USER這個設置了，這樣這個存儲過程，誰調用它，它就用誰的schema去查對應 102 的表（不僅僅是表，不帶schema的其它所有對象都一樣）。哈哈，“王美”。 103 ps：用了AUTHID CURRENT_USER的菜譜，那就每個廚師用自己的廚具來幹活了。 104 */ 105 is 106 -- is | as 這個地方is或者as都可，看喜好。 107 /****************************************************************************** 108 NAME: HLL_TEST_20170415 109 PURPOSE: 懵懂oracle之存儲過程 110 111 112 REVISIONS: 113 Ver Date Author Description 114 --------- -------------- --------------- ------------------------------------ 115 1.0 2017-04-15 HE.LILI 創建存儲過程 116 2.0 2017-05-24 HE.LILI DDL/EXECUTE IMMEDIATE/CLOB/BULK COLLECT/FORALL/CONTINUE/單引號/EXCEPTION_INIT等的添加 117 ****************************************************************************** 118 ******************************************************************************/ 119 /* 120 下麵這塊屬變數定義區域，類型除了常用的一些字元串類型、數字類型、日期類型、LOB類型等，還可以自主搭配定義類型。 121 同時不同於參數，變數類型如果有長度區分則可自定義長度，具體oracle含哪些基本類型和各個類型的一些預設長度範圍和設定情況 122 可網路搜索瞭解。 123 變數基本格式：var1 type1 [(default | :=) value1]; 124 變數1 類型1 [預設 值1]; 125 ps：廚房裡也有基本佐料如鹽、糖、油、醋等等，也可以自由搭配出你的獨門秘方yo。 126 */ 127 128 v_salt char := 'Y'; -- 預設長度為1 129 v_sugar varchar2(50) default 'sugar is sweet,and so are you.'; -- varchar2需指定長度 130 v_oil date; 131 v_vinegar clob; 132 v_chili number; -- 預設[10e-130,10e126) 133 v_ginger user_tables.table_name%type; -- 和參數一樣，我們也可這樣根據對應表對應欄位設置類型 134 135 v_sql varchar2(4000);-- varchar2最大位元組長度 136 v_table_batch number := 1000; 137 138 v_sqlcode number; -- 用來獲取異常code 139 v_sqlerrm varchar2(1024); -- 用來獲取異常errm信息 140 141 subtype number1 is number(8, 2); -- 定義子類型number1。 註意：number(8,2)表示整數位最多6位小數位最多2位的數值 142 v_paprika number1; -- 變數 number(8,2) 143 v_capsicum number1; -- 變數 number(8,2) 144 v_pepper number1; -- 變數 number(8,2) 145 /* 146 subtype又是我們用於偷懶的方法，基本格式： 147 subtype subtype_name is based_type [not null] 148 subtype 子類型名稱 is 基類型 [非空] 149 然後我們就得到個“新”的類型，也就相當於is後邊的類型的一個別名，因為有些時候我們有一批變數都用到這個類型，一旦長度或類型有變化，一一都去改很麻煩， 150 所以就可定義這個子類型，然後那一批變數都用這個子類型，這樣就只要改這個子類型，然後所有用它的變數類型就都改變了。 151 ps：子類型長度限定不一定要有，可以在用這個子類型的時候再加。 152 子類型也可以是根據下麵會表述的record類型、table類型等創建的子類型，但是這樣的子類型只繼承大小精度等約束，並不能繼承其他約束，如not null。 153 */ 154 155 v_user_tables user_tables%rowtype; 156 /* 157 單行多列（一個表欄位量也就1~1000個）數據。 158 類似於%type，%rowtype表示行類型，%號前面是表則對應這個表的行類型，如果是游標則是游標的行類型。 159 */ 160 161 type tr_redburnedlionhead is record( -- 定義一個叫tr_redburnedlionhead的record類型。 162 v_choppedpork varchar2(100) := 'good marbled meat', 163 v_eggs user_tables.iot_name%type default 'one egg or two', 164 v_scallion number1, 165 v_ginger char(2), 166 v_cookingwine user_tab_columns.data_length%type); 167 r_redburnedlionhead tr_redburnedlionhead; -- 實例化使用叫tr_redburnedlionhead的record類型，定義變數r_redburnedlionhead。 168 /* 169 單行多列數據，當需要用的類型是一個或多個表的某些欄位和某些其它類型的集合，我們就能使用record了 170 record基本格式： 171 type record_name is record( -- 定義一個record類型 172 var1 type1 [not null][(default | :=) value1], 173 var2 type2 [not null][(default | :=) value2], 174 var3 type3 [not null][(default | :=) value3]); 175 record_instance record_name; -- 實例化使用這個record類型，這是最基本的使用方法，還可以用於其它需要類型的地方，如後邊會講述的varray或table中，可互相嵌套。 176 */ 177 178 type tv_rice is varray(3) of varchar2(50); -- 定義一個varray類型 179 a_rice tv_rice; -- 實例化使用叫tv_rice的varray類型，定義變數a_rice。 180 /* 181 多行單列數據，varray基本格式：type varray_name is varray(size) of type1 [not null]; -- 定義一個varray類型 182 varray_instance varray_name; -- 實例化使用這個varray_name類型，亦可用於table of的類型。 183 varray裡面的元素是有序排列的，通過size設定固定正整數位長度，可通過extend(k)方法增加k長度，a_rice.extend 與a_rice.extend(1)同樣增加一個長度。 184 */ 185 186 type tt_rooms is table of number index by varchar2(20); 187 v_rooms tt_rooms; 188 type tt_tables is table of varchar2(200); 189 v_tables tt_tables := tt_tables('101號桌', '102號桌', '103號桌'); -- 基本類型且未設置動態自增的table可如此初始化 190 -- 或存過主體中使用如：v_tables.extend; v_tables(1) := '101號桌'; 來初始化值。 191 type tt_members is table of user_tables%rowtype index by binary_integer; 192 v_members tt_members; 193 /* 194 多行多列數據，table基本格式： 195 type table_name is table of type1 [not null] [index by binary_integer|pls_integer|varchar2(size)]; -- 定義一個table類型 196 table_instance table_name; -- 實例化使用這個table_name類型。 197 ps：type1-可為基本類型，可為record、varray(它們三者之間的互相嵌套可各自摸索使用)，可為游標%rowtype、表%rowtype。 198 index by binary_integer|pls_integer|varchar2(size) : 使用該項配置，則table會動態自增，無需利用extend方法申請擴展長度同時也不可在聲明時初始化值。 199 binary_integer：整型下標，值計算由oracle模擬執行，不會溢出，但執行速度較慢，最為常用； 200 pls_integer：整型下標，值計算由CPU執行，會出現溢出，比oracle模擬快； 201 varchar2(size)：字元串下標，size範圍為1~32767。 202 dbms_sql系統包中提供了一些常用的table，如：type number_table is table of number index by binary_integer; 等，在存儲過程中 203 可直接使用dbms_sql.number_table。（更多內容請進入此包查看） 204 */ 205 206 type tr_tables_comp is record( 207 tb_name user_tables.table_name%type, 208 tbsp_name user_tables.tablespace_name%type); 209 cursor c_tables return tr_tables_comp is 210 select table_name, tablespace_name 211 from user_tables 212 where status = i_status; 213 214 type trc_tables is ref cursor; 215 vc_tables trc_tables; 216 /* 217 顯示游標基本格式： 218 cursor cursor_name -- 定義一個叫cursor_name的顯示游標，可以用括弧設定入參，類似於開頭介紹過的存過入參，但是此處只能為in類型的入參。 219 [(parameter[, parameter]…)] --定義若幹參數，基本格式param1 [in] type1 [(default | :=) value1] 220 [return return_type] -- 比較少添加，return_type定義返回一個記錄類型，指定了游標變數最終返回的查詢結果集類型，需和select_statement返回的類型和列數目一致， 221 -- 可以是自定義的記錄類型(最終獲取游標行數據時列名採用記錄內部欄位名稱)或%rowtype定義的記錄類型。 222 is select_statement -- 定義游標對應的select語句，可用上所有cursor定義的參數和存儲過程的in類型參數。 223 [for update -- 定義for update後，則可在迴圈游標時，通過where current of cursor_name來更新或刪除當前游標所在行數據，會預設給select語句中所有表加共用鎖。 224 [of [table1.]column1[, [table2.]column2]…] -- 定義不同的列，則for update後只給對應的表加共用鎖 225 [nowait] -- nowait用於指定不等待其它會話的鎖，當遇此情況，會拋出ORA-0054異常並退出當前塊，預設當前會話要一直等待釋放。 226 ]; 227 隱示游標基本格式：sql%isopen | sql%%found | sql%notfound | sql%rowcount -- 這不是定義，而是在存儲過程主體中使用這四個屬性的方式。 228 基本上是這四種用法，“PL/SQL為所有SQL數據操作語句（包括返回一行的SELECT）隱式聲明游標，稱為隱式聲明游標的原因是用戶不能直接命名和控制此類游標。 229 當用戶在PL/SQL中使用資料庫操作語言（DML）時，Oracle預定義一個名為SQL的隱式游標，通過檢查隱式游標的屬性可以獲取與最近執行的SQL語句相關的信息。"--摘自百度百科 230 231 游標變數基本格式： 232 type ref_cursor_name is ref cursor -- 定義一個游標變數類型 233 [ return return_type]; -- 類似顯示游標處作用，有指定此處return則是強類型定義（限制了後面調用時select需要返回的類型次序），否則是弱類型定義（未限制）。 234 ref_cursor_instance ref_cursor_name; -- 實例化使用這個ref_cursor_name類型 235 -- ps：此處實例化游標變數時還可用sys_refcursor這個類型，它是oracle9i以後系統定義的一個ref cursor， 236 -- 主要用在過程中返回結果集（作為出差）。 237 ps：當sql語句是動態生成的時候，我們就沒辦法按顯示游標基本格式定義游標，所以可以用游標變數，在存過主體中它倆的基本用法也差不多，見後面關於迴圈的知識點。 238 游標基本屬性： 239 isopen：【顯】當游標已打開時返回 true。【隱】因為隱示游標在執行DML語句前自動隱含式的打開，併在DML執行完後關閉，所以隱示游標的該值總為false。 240 found：【顯】當最新fetch提取游標操作成功則為true，否則為false。【隱】當insert、update、delete語句處理一行或多行，或者是執行select into 語句返回一行（多行或0行都會 241 直接異常報錯）時，該屬性為true，否則為false。 242 notfound：【顯】【隱】與found屬性相反。 243 rowcount：【顯】返回當前已從游標中讀取到的記錄數。【隱】執行insert、update、delete語句返回的行數（0~n），或執行select into語句時查詢出的行數（0或1， 244 多行就異常不會再判斷rowcount）。 245 */ 246 247 bulk_error exception;-- 定義一個異常 248 pragma exception_init (bulk_error, -24381);-- 將此定義的異常與oracle的一個錯誤聯繫起來 249 continue_error exception;-- 定義一個異常，用於下文實現continue 250 251 pragma autonomous_transaction; 252 /* 253 [ pragma autonomous_transaction; ] 254 自治事務，可選的一項配置，相當於一段獨立出來的子事務，設置後在此存儲過程中可以自由的commit或者rollback，卻不會影響到調用這個存儲過程的主存過主事務中去， 255 當然主事務未commit的數據對我們這個存儲過程來說也是不知道的，主事務的rollback也回滾不了我們子事務已commit的東西。 256 一般專用於用來記錄日誌的存儲過程。 257 */ 258 begin 259 -- 存儲過程主體內容的開始 ps：菜譜的材料都準備好了，該來做菜步驟了。 260 select count(1) 261 into v_chili 262 from user_tables 263 where table_name = 'USER_TABLES_TMP' 264 and tablespace_name <> r_redburnedlionhead.v_choppedpork; -- 在增、刪、改、查等語句中，都是可以這樣直接使用變數的 265 /* 266 一種可在存儲過程中給變數（包括type類的記錄類型）賦值的語句： 267 select value1,value2,value3… into val1,var2,var3…|record_instance|table_instance from table1 where condition1; 268 或者 execute immediate 查詢語句字元串或查詢語句字元串變數 into val1,var2,var3…|record_instance|table_instance; 269 270 備註： 271 1.在存儲過程中select時，不存在沒有意義的select，平常單條執行select我們可以主動看到查詢的結果， 272 而在存儲過程中，這個結果得拿出來才能使用，所以在存儲過程中，我們的select語句要麼跟隨游標一起 273 把結果存入變數，要麼則用into存入變數中。 274 275 2.除了 select語句 的賦值，:= 的賦值，還有update、insert、delete語句也能賦值。 276 當然這個賦值不是必要的，這三個語句與select不同，它們對存過來說本身就是有意義的。 277 所以returning into語句對它們來說是可有可無選項，加上這個後會在這些DML語句成功執行後 278 影響到的行數據的值保存到變數中。 279 returning into子句的用法： 280 insert into 表1(欄位1[,欄位2,欄位3,……]) values (值1[,值2,值3,……]) 281 returning 欄位1[,欄位2,欄位3,……] into 變數1[,變數2,變數3,……] 或者 記錄1; 282 update 表1 set 欄位1=值1[ ,……] where 條件 283 returning 欄位1[,欄位2,欄位3,……] into 變數1[,變數2,變數3,……] 或者 記錄1; 284 delete 表1 where 條件 285 returning 欄位1[,欄位2,欄位3,……] into 變數1[,變數2,變數3,……] 或者 記錄1; 286 ps：由於returning into是執行DML後才將影響到的行數據的對於列的值保存到對應變數中。 287 所以1）語句執行異常，到最近的捕獲異常處被處理（沒有則整個存儲過程異常退出）。 288 2）語句更新、刪除成功，影響行數=0，那麼相當於沒有returning into選項，不對變數值造成影響。 289 3）語句執行成功，影響行數>0，那麼等於執行了insert/update/delete後再select對應的值into變數。 290 291 3.對應insert、update、delete這樣的DML語句，在存儲過程中需配合使用rollback(回滾)和commit(提交)來結束 292 它們的事務。如果整個存儲過程是一個整體，需各個插入刪除等語句都成功後才提交，往往在存儲過程末尾才 293 加commit進行提交。 294 295 4.DML(數據操作語言)：select/insert/update/delete/merge/…… --select在某些資料庫中當成DQL 296 DDL(數據定義語言)：create/alert/drop --註意：DDL會隱示的進行事務提交 297 DCL(數據控制語言)：grant、revoke --授予許可權與回收許可權語句 298 */ 299 300 if v_chili = 0 then 301 execute immediate 'create table USER_TABLES_TMP as select * from user_tables'; 302 end if; 303 for i in 1..v_tables.count loop 304 v_sql := 'declare 305 flag number; 306 begin 307 select count(1) into flag from user_tables 308 where table_name = :1 309 and tablespace_name <> '''||v_sugar || '''; 310 if flag =0 then 311 delete from user_tables_tmp where table_name = :2; 312 end if; 313 end;'; 314 execute immediate v_sql using v_tables(i),v_tables(i); 315 end loop; 316 /* 317 1.DDL在存儲過程中的執行： 318 上面已經提到了DML語句的使用，而DDL語句在存儲過程中是不鼓勵使用的，如果直接用這些語句， 319 那麼存儲過程無法編譯通過，所以得用到execute immedate來將DDL“屏蔽”。註意：在存儲過程中這樣執行 320 DDL語句也不是直接就能執行的，需要用戶有執行存儲過程中DDL的許可權（如創建表等許可權，通過語句給這個 321 用戶授權：grant create table to 用戶名），或者使用之前提過的AUTHID CURRENT_USER項將存儲過程改為 322 調用者許可權機制也行，因為之所以沒有許可權是因為用戶的角色許可權在進入存過裡面會被剝離掉，所以要麼顯示 323 的授權，這樣系統許可權就會帶入進來，要麼則改為調用者許可權機制。 324 325 警告：DDL語句在存儲過程中的使用需謹慎處理，因為DDL語句是隱示提交的，所以看的一個DDL語句就得 326 想到一個commit語句，這個會對存儲過程中的事務控製造成影響！如果在存儲過程中無法避免使用到影響事務 327 控制的DDL語句，那麼怎麼辦呢？沒錯，我們可以使用之前提到的pragma autonomous_transaction;項自治事務。 328 329 2. EXECUTE IMMEDIATE(後續簡稱為EI)神器： 330 上面第1點的接受我們已經知道execute immedate可以用來執行DDL語句，其實，這個神器可以做的還有很多。 331 基本格式： 332 execute immediate dynamic_sql_string|plsql block|var1(字元串變數或者clob變數) 333 [into {var1[,var2]…| record}] 334 [using [in | out | in out] arg1[,[in | out | in out] arg1]…] 335 [{returning | return} into r_arg1[,r_arg2]…]; 336 337 2.1 dynamic_sql_string|plsql block 指的是動態拼接的SQL語句或者一個PL/SQL塊。 338 因此，EI不止可執行DDL，之前講的DML、DCL甚至一個完整的PL/SQL塊等都可以執行，它替代了以前ORACLE8I中 339 DBMS_SQL包（當然兩者還是有區別差異，但DBMS_SQL用到的情況現在已經很少見了，有興趣的可搜索瞭解）。 340 後邊接動態拼接（可以利用一些迴圈）的語句或者塊的字元串，可以用變數--字元串變數或者clob變數(oracle 11g 341 才開始支持clob)存儲拼接， 342 343 PL/SQL塊簡單介紹：可以看成是一種匿名的存儲過程，可以看成執行時立即調用自己，不會像存儲過程一樣生成對象， 344 待後續的調用使用。 345 基本格式declare …… begin …… end; （如果不需要定義變數，直接使用begin …… end; 塊亦是可行的） 346 類比存儲過程就相當於create or replace procedure sp_hll_test_20170415(參數) as用declare代替。當然一些屬於 347 存儲過程特有的參數、事務自治、定義者或調用者許可權等無法使用，其它內部格式和使用與存儲過程基本一致。 348 349 2.2 into一般用來保存單個select語句查詢返回的結果，類似於select……into……，後邊可接多個單值變數，或者一個記錄類型。 350 351 2.3 using 需在動態語句中配合 :(冒號)+綁定變數名 來使用，筆者一般使用:1,:2,:3……等方便快捷，或者可以:table_name等 352 用欄位具體名稱來標識，都無關緊要，對於using來說，它就從using後接的參數值按從前至後的順序把值自前往後地 353 替換到動態語句中對應的綁定變數中（該變數不可綁定表名、欄位名等，只可綁定欄位值或者塊裡面調用存過函數等 354 參數值，對於表名、欄位名等就需直接拼接，而欄位值可直接拼接，亦可用using方式來綁定變數） 355 356 何時需要使用using呢？1)不涉及where的語句；2)OLTP環境；--OLTP與OLAP的含義和區別請搜索瞭解 357 執行下麵代碼可對綁定變數使硬解析轉變為軟解析的情況加以瞭解（硬軟解析請搜索瞭解）： 358 select * from all_objects where object_id=20; 359 select * from all_objects where object_id=30; 360 select * from all_objects where object_id=40; 361 select * from all_objects where object_id=50; 362 begin 363 for i in 1 .. 4 loop 364
