懵懂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 2.1 2017-06-15 HE.LILI 游标介绍错误修复，commit的添加 118 2.2 2017-06-16 HE.LILI 'case'语句使用'else'的缺失补充 119 3.0 2017-06-17 HE.LILI 存储过程的调用和调试，补充至另一篇《懵懂oracle之存储过程2》 120 3.1 2017-09-16 HE.LILI 读TOM书有感，加些警示，学习是漫漫长路 121 3.2 2018-05-10 HE.LILI 修正FORALL与bulk collect into配合处bug 122 ****************************************************************************** 123 ******************************************************************************/ 124 /* 125 下面这块属变量定义区域，类型除了常用的一些字符串类型、数字类型、日期类型、LOB类型等，还可以自主搭配定义类型。 126 同时不同于参数，变量类型如果有长度区分则可自定义长度，具体oracle含哪些基本类型和各个类型的一些默认长度范围和设定情况 127 可网络搜索了解。 128 变量基本格式：var1 type1 [(default | :=) value1]; 129 变量1 类型1 [默认 值1]; 130 ps：厨房里也有基本佐料如盐、糖、油、醋等等，也可以自由搭配出你的独门秘方yo。 131 */ 132 133 v_salt char := 'Y'; -- 默认长度为1 134 v_sugar varchar2(50) default 'sugar is sweet,and so are you.'; -- varchar2需指定长度 135 v_oil date; 136 v_vinegar clob; 137 v_chili number; -- 默认[10e-130,10e126) 138 v_ginger user_tables.table_name%type; -- 和参数一样，我们也可这样根据对应表对应字段设置类型 139 140 v_sql varchar2(4000);-- varchar2最大字节长度 141 v_table_batch number := 1000; 142 143 v_sqlcode number; -- 用来获取异常code 144 v_sqlerrm varchar2(1024); -- 用来获取异常errm信息 145 146 subtype number1 is number(8, 2); -- 定义子类型number1。 注意：number(8,2)表示整数位最多6位小数位最多2位的数值 147 v_paprika number1; -- 变量 number(8,2) 148 v_capsicum number1; -- 变量 number(8,2) 149 v_pepper number1; -- 变量 number(8,2) 150 /* 151 subtype又是我们用于偷懒的方法，基本格式： 152 subtype subtype_name is based_type [not null] 153 subtype 子类型名称 is 基类型 [非空] 154 然后我们就得到个“新”的类型，也就相当于is后边的类型的一个别名，因为有些时候我们有一批变量都用到这个类型，一旦长度或类型有变化，一一都去改很麻烦， 155 所以就可定义这个子类型，然后那一批变量都用这个子类型，这样就只要改这个子类型，然后所有用它的变量类型就都改变了。 156 ps：子类型长度限定不一定要有，可以在用这个子类型的时候再加。 157 子类型也可以是根据下面会表述的record类型、table类型等创建的子类型，但是这样的子类型只继承大小精度等约束，并不能继承其他约束，如not null。 158 */ 159 160 v_user_tables user_tables%rowtype; 161 /* 162 单行多列（一个表字段量也就1~1000个）数据。 163 类似于%type，%rowtype表示行类型，%号前面是表则对应这个表的行类型，如果是游标则是游标的行类型。 164 */ 165 166 type tr_redburnedlionhead is record( -- 定义一个叫tr_redburnedlionhead的record类型。 167 v_choppedpork varchar2(100) := 'good marbled meat', 168 v_eggs user_tables.iot_name%type default 'one egg or two', 169 v_scallion number1, 170 v_ginger char(2), 171 v_cookingwine user_tab_columns.data_length%type); 172 r_redburnedlionhead tr_redburnedlionhead; -- 实例化使用叫tr_redburnedlionhead的record类型，定义变量r_redburnedlionhead。 173 /* 174 单行多列数据，当需要用的类型是一个或多个表的某些字段和某些其它类型的集合，我们就能使用record了 175 record基本格式： 176 type record_name is record( -- 定义一个record类型 177 var1 type1 [not null][(default | :=) value1], 178 var2 type2 [not null][(default | :=) value2], 179 var3 type3 [not null][(default | :=) value3]); 180 record_instance record_name; -- 实例化使用这个record类型，这是最基本的使用方法，还可以用于其它需要类型的地方，如后边会讲述的varray或table中，可互相嵌套。 181 */ 182 183 type tv_rice is varray(3) of varchar2(50); -- 定义一个varray类型 184 a_rice tv_rice; -- 实例化使用叫tv_rice的varray类型，定义变量a_rice。 185 /* 186 多行单列数据，varray基本格式：type varray_name is varray(size) of type1 [not null]; -- 定义一个varray类型 187 varray_instance varray_name; -- 实例化使用这个varray_name类型，亦可用于table of的类型。 188 varray里面的元素是有序排列的，通过size设定固定正整数位长度，可通过extend(k)方法增加k长度，a_rice.extend 与a_rice.extend(1)同样增加一个长度。 189 */ 190 191 type tt_rooms is table of number index by varchar2(20); 192 v_rooms tt_rooms; 193 type tt_tables is table of varchar2(200); 194 v_tables tt_tables := tt_tables('101号桌', '102号桌', '103号桌'); -- 基本类型且未设置动态自增的table可如此初始化 195 -- 或存过主体中使用如：v_tables.extend; v_tables(1) := '101号桌'; 来初始化值。 196 type tt_members is table of user_tables%rowtype index by binary_integer; 197 v_members tt_members; 198 /* 199 多行多列数据，table基本格式： 200 type table_name is table of type1 [not null] [index by binary_integer|pls_integer|varchar2(size)]; -- 定义一个table类型 201 table_instance table_name; -- 实例化使用这个table_name类型。 202 ps：type1-可为基本类型，可为record、varray(它们三者之间的互相嵌套可各自摸索使用)，可为游标%rowtype、表%rowtype。 203 index by binary_integer|pls_integer|varchar2(size) : 使用该项配置，则table会动态自增，无需利用extend方法申请扩展长度同时也不可在声明时初始化值。 204 binary_integer：整型下标，值计算由oracle模拟执行，不会溢出，但执行速度较慢，最为常用； 205 pls_integer：整型下标，值计算由CPU执行，会出现溢出，比oracle模拟快； 206 varchar2(size)：字符串下标，size范围为1~32767。 207 dbms_sql系统包中提供了一些常用的table，如：type number_table is table of number index by binary_integer; 等，在存储过程中 208 可直接使用dbms_sql.number_table。（更多内容请进入此包查看） 209 */ 210 211 type tr_tables_comp is record( 212 tb_name user_tables.table_name%type, 213 tbsp_name user_tables.tablespace_name%type); 214 cursor c_tables return tr_tables_comp is 215 select table_name, tablespace_name 216 from user_tables 217 where status = i_status; 218 219 type trc_tables is ref cursor; 220 vc_tables trc_tables; 221 /* 222 显示游标基本格式： 223 cursor cursor_name -- 定义一个叫cursor_name的显示游标，可以用括号设定入参，类似于开头介绍过的存过入参，但是此处只能为in类型的入参。 224 [(parameter[, parameter]…)] --定义若干参数，基本格式param1 [in] type1 [(default | :=) value1] 225 [return return_type] -- 比较少添加，return_type定义返回一个记录类型，指定了游标变量最终返回的查询结果集类型，需和select_statement返回的类型和列数目一致， 226 -- 可以是自定义的记录类型(最终获取游标行数据时列名采用记录内部字段名称)或%rowtype定义的记录类型。 227 is select_statement -- 定义游标对应的select语句，可用上所有cursor定义的参数和存储过程的in类型参数。 228 [for update -- 定义for update后，则可在循环游标时，通过where current of cursor_name来更新或删除当前游标所在行数据，会默认给select语句中所有表加共享锁。 229 [of [table1.]column1[, [table2.]column2]…] -- 定义不同的列，则for update后只给对应的表加共享锁 230 [nowait] -- nowait用于指定不等待其它会话的锁，当遇此情况，会抛出ORA-0054异常并退出当前块，默认当前会话要一直等待释放。 231 ]; 232 隐示游标基本格式：sql%isopen | sql%%found | sql%notfound | sql%rowcount -- 这不是定义，而是在存储过程主体中使用这四个属性的方式。 233 基本上是这四种用法，“PL/SQL为所有SQL数据操作语句（包括返回一行的SELECT）隐式声明游标，称为隐式声明游标的原因是用户不能直接命名和控制此类游标。 234 当用户在PL/SQL中使用数据库操作语言（DML）时，Oracle预定义一个名为SQL的隐式游标，通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。"--摘自百度百科 235 236 游标变量基本格式： 237 type ref_cursor_name is ref cursor -- 定义一个游标变量类型 238 [ return return_type]; -- 类似显示游标处作用，有指定此处return则是强类型定义（限制了后面调用时select需要返回的类型次序），否则是弱类型定义（未限制）。 239 ref_cursor_instance ref_cursor_name; -- 实例化使用这个ref_cursor_name类型 240 -- ps：此处实例化游标变量时还可用sys_refcursor这个类型，它是oracle9i以后系统定义的一个ref cursor， 241 -- 主要用在过程中返回结果集（作为出差）。 242 ps：当sql语句是动态生成的时候，我们就没办法按显示游标基本格式定义游标，所以可以用游标变量，在存过主体中它俩的基本用法也差不多，见后面关于循环的知识点。 243 游标基本属性： 244 isopen：【显】当游标已打开时返回 true。【隐】因为隐示游标在执行DML语句前自动隐含式的打开，并在DML执行完后关闭，所以隐示游标的该值总为false。 245 found：【显】当最新fetch提取游标操作成功则为true，否则为false。【隐】当insert、update、delete语句处理一行或多行，或者是执行select into 语句返回一行（多行或0行都会 246 直接异常报错）时，该属性为true，否则为false。 247 notfound：【显】【隐】与found属性相反。 248 rowcount：【显】返回当前已从游标中读取到的记录数。【隐】执行insert、update、delete语句返回的行数（0~n），或执行select into语句时查询出的行数（0或1， 249 多行就异常不会再判断rowcount）。 250 */ 251 252 bulk_error exception;-- 定义一个异常 253 pragma exception_init (bulk_error, -24381);-- 将此定义的异常与oracle的一个错误联系起来 254 continue_error exception;-- 定义一个异常，用于下文实现continue 255 256 -- pragma autonomous_transaction; 257 /* 258 [ pragma autonomous_transaction; ] 259 自治事务，可选的一项配置，相当于一段独立出来的子事务，设置后在此存储过程中可以自由的commit或者rollback，却不会影响到调用这个存储过程的主存过主事务中去， 260 当然主事务未commit的数据对我们这个存储过程来说也是不知道的，主事务的rollback也回滚不了我们子事务已commit的东西。 261 一般专用于用来记录日志的存储过程。 262 ps: （2017-9-16）此项还是注释掉为好！请合理考虑使用逻辑，基本用到的地方只有用于错误日志记录，如果有其它场景需要使用， 263 请考虑是否场景有误。终归跳出主事务的东西还是得谨慎使用。 264 */ 265 begin 266 -- 存储过程主体内容的开始 ps：菜谱的材料都准备好了，该来做菜步骤了。 267 select count(1) 268 into v_chili 269 from user_tables 270 where table_name = 'USER_TABLES_TMP' 271 and tablespace_name <> r_redburnedlionhead.v_choppedpork; -- 在增、删、改、查等语句中，都是可以这样直接使用变量的 272 /* 273 一种可在存储过程中给变量（包括type类的记录类型）赋值的语句： 274 select value1,value2,value3… into val1,var2,var3…|record_instance|table_instance from table1 where condition1; 275 或者 execute immediate 查询语句字符串或查询语句字符串变量 into val1,var2,var3…|record_instance|table_instance; 276 277 备注： 278 1.在存储过程中select时，不存在没有意义的select，平常单条执行select我们可以主动看到查询的结果， 279 而在存储过程中，这个结果得拿出来才能使用，所以在存储过程中，我们的select语句要么跟随游标一起 280 把结果存入变量，要么则用into存入变量中。 281 282 2.除了 select语句 的赋值，:= 的赋值，还有update、insert、delete语句也能赋值。 283 当然这个赋值不是必要的，这三个语句与select不同，它们对存过来说本身就是有意义的。 284 所以returning into语句对它们来说是可有可无选项，加上这个后会在这些DML语句成功执行后 285 影响到的行数据的值保存到变量中。 286 returning into子句的用法： 287 insert into 表1(字段1[,字段2,字段3,……]) values (值1[,值2,值3,……]) 288 returning 字段1[,字段2,字段3,……] into 变量1[,变量2,变量3,……] 或者 记录1; 289 update 表1 set 字段1=值1[ ,……] where 条件 290 returning 字段1[,字段2,字段3,……] into 变量1[,变量2,变量3,……] 或者 记录1; 291 delete 表1 where 条件 292 returning 字段1[,字段2,字段3,……] into 变量1[,变量2,变量3,……] 或者 记录1; 293 ps：由于returning into是执行DML后才将影响到的行数据的对于列的值保存到对应变量中。 294 所以1）语句执行异常，到最近的捕获异常处被处理（没有则整个存储过程异常退出）。 295 2）语句更新、删除成功，影响行数=0，那么相当于没有returning into选项，不对变量值造成影响。 296 3）语句执行成功，影响行数>0，那么等于执行了insert/update/delete后再select对应的值into变量。 297 298 3.对应insert、update、delete这样的DML语句，在存储过程中需配合使用rollback(回滚)和commit(提交)来结束 299 它们的事务。如果整个存储过程是一个整体，需各个插入删除等语句都成功后才提交，往往在存储过程末尾才 300 加commit进行提交。 301 302 4.DML(数据操作语言)：select/insert/update/delete/merge/…… --select在某些数据库中当成DQL 303 DDL(数据定义语言)：create/alert/drop --注意：DDL会隐示的进行事务提交 304 DCL(数据控制语言)：grant、revoke --授予权限与回收权限语句 305 */ 306 307 if v_chili = 0 then 308 execute immediate 'create table USER_TABLES_TMP as select * from user_tables'; 309 end if; 310 for i in 1..v_tables.count loop 311 v_sql := 'declare 312 flag number; 313 begin 314 select count(1) into flag from user_tables 315 where table_name = :1 316 and tablespace_name <> '''||v_sugar || '''; 317 if flag =0 then 318 delete from user_tables_tmp where table_name = :2; 319 end if; 320 end;'; 321 execute immediate v_sql using v_tables(i),v_tables(i); 322 end loop; 323 /* 324 1.DDL在存储过程中的执行： 325 上面已经提到了DML语句的使用，而DDL语句在存储过程中是不鼓励使用的，如果直接用这些语句， 326 那么存储过程无法编译通过，所以得用到execute immedate来将DDL“屏蔽”。注意：在存储过程中这样执行 327 DDL语句也不是直接就能执行的，需要用户有执行存储过程中DDL的权限（如创建表等权限，通过语句给这个 328 用户授权：grant create table to 用户名），或者使用之前提过的AUTHID CURRENT_USER项将存储过程改为 329 调用者权限机制也行，因为之所以没有权限是因为用户的角色权限在进入存过里面会被剥离掉，所以要么显示 330 的授权，这样系统权限就会带入进来，要么则改为调用者权限机制。 331 332 警告：DDL语句在存储过程中的使用需谨慎处理，因为DDL语句是隐示提交的，所以看的一个DDL语句就得 333 想到一个commit语句，这个会对存储过程中的事务控制造成影响！如果在存储过程中无法避免使用到影响事务 334 控制的DDL语句，那么怎么办呢？没错，我们可以使用之前提到的pragma autonomous_transaction;项自治事务。 335 336 2. EXECUTE IMMEDIATE(后续简称为EI)神器： 337 ps: （2017-9-16）此项用起来是爽，还是强调下别滥用了，能用正常SQL的地方请勿用动态拼装的SQL， 338 数据库它更喜欢明确的东西，从执行效率上来看这个玩意是肯定比不上正常SQL的。 339 上面第1点的接受我们已经知道execute immedate可以用来执行DDL语句，其实，这个神器可以做的还有很多。 340 基本格式： 341 execute immediate dynamic_sql_string|plsql block|var1(字符串变量或者clob变量) 342 [into {var1[,var2]…| record}] 343 [using [in | out | in out] arg1[,[in | out | in out] arg1]…] 344 [{returning | return} into r_arg1[,r_arg2]…]; 345 346 2.1 dynamic_sql_string|plsql block 指的是动态拼接的SQL语句或者一个PL/SQL块。 347 因此，EI不止可执行DDL，之前讲的DML、DCL甚至一个完整的PL/SQL块等都可以执行，它替代了以前ORACLE8I中 348 DBMS_SQL包（当然两者还是有区别差异，但DBMS_SQL用到的情况现在已经很少见了，有兴趣的可搜索了解）。 349 后边接动态拼接（可以利用一些循环）的语句或者块的字符串，可以用变量--字符串变量或者clob变量(oracle 11g 350 才开始支持clob)存储拼接， 351 352 PL/SQL块简单介绍：可以看成是一种匿名的存储过程，可以看成执行时立即调用自己，不会像存储过程一样生成对象， 353 待后续的调用使用。 354 基本格式declare …… begin …… end; （如果不需要定义变量，直接使用begin …… end; 块亦是可行的） 355 类比存储过程就相当于create or replace procedure sp_hll_test_20170415(参数) as用declare代替。当然一些属于 356 存储过程特有的参数、事务自治、定义者或调用者权限等无法使用，其它内部格式和使用与存储过程基本一致。 357 358 2.2 into一般用来保存单个select语句查询返回的结果，类似于select……into……，后边可接多个单值变量，或者一个记录类型。 359 360 2.3 using 需在动态语句中配合 :(冒号)+绑定变量名 来使用，笔者一般使用:1,:2,:3……等方便快捷，或者可以:table_name等 361 用字段具体名称来标识，都无关紧要，对于using来说，它就从using后接的参数值按从前至后的顺序把值自前往后地 362 替换到动态语句中对应的绑定变量中（该变量不可绑定表名、字段名等，只可绑定字段值或者块里面调用存过函数等 363 参数值，对于表名、字段名等就需直接拼接，而字段值可直接拼接，亦可用using方式来绑定变量） 364 365 何时需要使用using呢？1)不涉及where的语句；2)OLTP环境；--OLTP与OLAP的含义和区别请搜索了解 366 执行下面代码可对绑定变量使硬解析转变为软解析的情况加以了解（硬软解析请搜索了解）： 367 select * from all_objects where object_id=20; 368 select * from all_objects where object_id=30; 369 select * from all_objects where object_id=40; 370 select * from all_objects where object_id=50; 371 begin 372 for i in 1 .. 4 loop 373 execute immediate 'select * from all_objects where object_id=:i' 374 using i; 375 end loop; 376 end; 377 / 378 select sql_text, s.parse_calls, loads, executions 379 from v$sql s 380 where sql_text like 'select * from all_objects where object_id%' 381 order by 1, 2, 3, 4; 382 383 2.4 {returning | return} into 对应的是EI执行之前介绍的returning into子句，同时与using一样需在动态语句中 384 配合 :(冒号)+绑定变量名 来使用，即returning into子句into后的变量需加:(冒号)，同样与using一样，内部变量的名称 385 亦无关紧要，统一都按变量顺序一一对应。最后，它的执行赋值结果也同returning into子句一致。 386 示例： 387 execute immediate 'update TEMP_WC_1 t 388 set t.接口表名 = ''INTF_LTE_EUTRANCELLTDD'' 389 where t.接口表字段 = ''related_me'' and rownum = 1 390 returning 接口表名 into :a' 391 returning into b; 392 393 备注：EI执行动态语句的优势在于其灵活性，根据条件不同可拼接成不同的语句，大至整个内容块，小至表名、字段名、 394 参数值等，使代码更具适用性更简洁，利用好也能减少耗费提高性能。脑洞大开的你还能EI嵌套EI嵌套EI…… 395 所以滥用EI也会使得代码更加脆弱，调整优化更加困难。笔者建议在使用EI时，定义一个变量存储值，如v_sql，然后 396 可以通过dbms_output.put_line(v_sql)或者调试取v_sql的值来了解最终动态拼接成的语句是什么样的，可以把这个语句 397 放到新的SQL窗口执行来解决错误。 398 399 3. CLOB的使用：由于存储过程中varchar2最大长度是4000，所以在字段长度会超过varchar2的限制时，我们就会用clob类型， 400 特别在使用EI时，由于是动态拼接的语句，很多情况下都会导致拼接的语句过长。而在使用中，clob类型又是能方便的与 401 varchar2类型进行拼接，如：v_clob := v_clob || '我是一个普通字符串'; 所以滥用clob引发性能问题也是不少的。 402 因为在对clob字段进行操作时，会导致大量的逻辑读和临时段（在会话退出才清除），影响性能，怎么解决呢？ 403 在必须用到clob时，我们需要根据情况，先将短的字符串进行拼接，确保小于4000，然后再拼接到clob变量上去， 404 以尽量减少对clob字段的操作，来降低对性能的影响。 405 406 4. (单引号--> ' )的使用： 407 1）字符串的标识（由两个单引号包裹组成）； 408 2）转义（在字符串内部相邻两个单引号，前者为转义符号）。--特别在EI嵌套中需注意使用！ 409 示例1： 410 select '''',' '' ','''''',''' '' ''' from dual; 411 示例2（EI嵌套时，每多一层嵌套，代表单引号的引号数目需乘以2）： 412 select 'declare begin execute immediate ''declare begin execute immediate ''''select sysdate from dual''''; end;''; end;' 413 from dual; 414 */ 415 416 /* 417 if判断基本格式： 418 if condition1 then 419 content1 -- 当满足条件1的时候，做内容1的事情 420 [elsif condition2 then content2] -- 当满足条件2的时候，做内容2的事情 421 [elsif condition3 then content3] -- 当满足条件3的时候，做内容3的事情 422 … 423 end if; -- 结束if判断，注意加分号 424 425 case判断基本格式： 426 case [var1] when value1|condition1 then -- 有var1时，when后面跟value1~n(对应具体值，进行判断var1是否等于value1~n)；无var1时，when后跟condition1~n，类似于if的用法。 427 content1 -- 当满足条件1的时候，做内容1的事情 428 [when value2|condition2 then content2] -- 当满足条件2的时候，做内容2的事情 429 [when value3|condition3 then content3] -- 当满足条件3的时候，做内容3的事情 430 [else contentn] 431 end case; -- 结束case判断，注意加分号 432 ps：使用"case …end case;"则在"then"后接具体的执行语句； 433 使用"case …end"则在"then"后接具体的值，整个case结构代表一个具体固定类型的值，所有then与else后返回的值类型必须统一（或number或varchar2等等，由第一个then后 434 的值类型确定）。 435 ps：判断格式condition1~n未要求但可使用小括号包裹，也可利用小括号包裹部分形成一个整体用于条件组合。 436 */ 437 if (case i_a 438 when 1 then 439 true 440 when 2 then 441 false 442 when 3 then 443 true 444 else 445 false 446 end) or (i_b > sysdate and i_c = 'default c value') then 447 -- a := 2; 这个语句是错误的，作为一个in的入参，不可以在存储过程中用“:=”去重新赋值，只能拿本身的值去使用 448 o_d := 1; -- 而作为out的出参，则当然可以改动值。 449 io_e := 'changed e value'; -- 既入又出的参，也能改动值，同时还可以接收外界传的值。 450 end if; 451 452 case 453 when i_status = '' then -- null = '' --> false 454 /* 455 =''用法是不对的，或者说它总是false，在存储过程编译的时候就会有提示信息，告诉你“怀疑有 NULL 进行比较的情况”。 456 在oracle中，'' 就被当做为null了，而mysql则两者是不同的东西，这个务必要注意， 457 暂发现在oracle中concat、count函数，把null能看成是''，一个空字符串，其它函数中，基本结果都是null， 458 当然如果是一些逻辑运算的话，例如：null = null; null > '1' 等等，结果都是false。 459 */ 460 v_sugar := 'I need more sugar!'; 461 dbms_output.put_line('sugar:' || v_sugar); 462 /* 463 dbms_output是oracle的一个系统包，可用于输出一些调试信息，常用方法： 464 put：把内容写到内存，无换行符在末尾，遇到put_line或new_line后才会把内容输出； 465 put_line：输出内容并换行（输出一行内容包括前面所有put里的内容）； 466 new_line：相当于输出换行符，另起一行，亦即结束当前行（把所有put里的内容也输出）。 467 在plsql中的“SQL窗口-输出”、“测试窗口-DBMS输出”处都有设置“缓冲区大小”，用dbms_output输出的内容在此处区域显示， 468 且put的内存内容字节长或put_line的内容的字节长或所有本次输出的内容字节总长都不能大于“缓冲区大小”，同时每行最多32767字节长， 469 否则会报错（在手动执行测试时），在调试存过的时候也会因此异常而中断存过，无法继续执行，需要注意使用。 470 ps：其它方法或其它常用系统包根据情况网络搜索了解，不再细表。 471 */ 472 473 when length(i_status) = 0 then -- null = 0 --> false 474 v_sugar := 'more and more sugar!'; 475 dbms_output.put_line('sugar:' || v_sugar); 476 when i_status is null then 477 v_sugar := 'just "sugar_maroon 5"!'; 478 dbms_output.put_line('sugar:' || v_sugar); 479 /*else -- 务必加else项，否则在上面都不满足后，找不到该执行的项而报错：ORA-06592: 执行 CASE 语句时未找到 CASE 480 v_sugar:='no more sugar!'; 481 dbms_output.put_line('sugar:' || v_sugar);*/--注释后用于调试测试 482 end case; 483 484 /* 485 因游标与循环关联比较多，故将游标定义后在存储过程主体中的用法也汇总在循环格式里。 486 loop循环基本格式： 487 [open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：打开一个游标，若为带参数的游标可在此处传入参数。 488 [for select_statement | select_sql_variable;] -- 当open的是一个游标变量时，我们可以在此处for后添加select语句或者一个字符串变量（拼接的一串select语句）。 489 -- ps：用法多多，可以根据条件判断open不同的语句，也可重复利用同一个游标变量open不同的语句。 490 ]; 491 [<<label_name>>] --设定一个标签名称，关联这个loop，可选 492 loop -- 开始循环 493 [ 494 fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：loop开始时每fetch取一行或一个集合数据，然后游标自动指向下一行或下个集合数据 495 [into var1 [, var2]…| record_instance;]-- fetch获取一行数据后，还得扔到我们定义的一些变量中保存， 496 -- 也可放到record中作为一个整体（使用时“record_instance.具体的属性名称”）。 497 [bulk collect into varray_instance|table_instance [limit rows];] -- into 和 bulk collect into 只能有一种，要么一次取一行，要么一次取一批 498 -- bulk collect into提取一批数据放入varray/table的一些实例变量内，limit项可选， 499 -- 限制每批提取数据的最大的行数，rows为正整数（常量or变量）。 500 -- ps：limit一般用于分批提取处理，将“for循环limit分批值”放入上面的loop循环游标内部， 501 -- 来循环中分批提取数据，既保证能提取游标中所有数据，又可通过分批提高效率。 502 exit when cursor_name%notfound|ref_cursor_instance%notfound; -- 游标的话就可在此处exit，判断fetch是否有取到值。 503 ] 504 content1; -- 循环的内容1 -- 可嵌套一个循环处理bulk collect获取的集合的内容 505 if condition1 then [content2;] exit [label_name]; | exit [label_name] when condition1; -- 无论是用if…exit还是exit…when都行或者上述游标的exit，请有能退出循环的条件。 506 content2; -- 循环的内容2 507 end loop [label_name]; -- 结束循环 508 [close cursor_name;] --汇总游标的用法：关闭游标，必须在使用完后及时关闭以释放该游标所占用的系统资源，并使该游标的工作区变成无效。 509 510 for循环基本格式： 511 [open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：同上。 512 [for select_statement | select_sql_variable;] -- 同上。 513 ]; 514 [ 515 fetch cursor_name|ref_cursor_instance 516 bulk collect into varray_instance|table_instance; -- 将游标中数据一次性全部取出来，然后在下面的for循环中可做相应业务操作。 517 ] -- 初始化fetch值 518 [<<label_name>>] --设定一个标签名称，关联这个for…loop，可选 519 for var1 in -- 设定一个变量，且这个变量无需在前面有定义过 520 [reverse] value1 .. value2|cursor_name|(select_statement) 521 -- 前者设置循环[floor(value1),floor(value2)]，此时变量var1为从floor(value1)至floor(value2)（设置了reverse则从floor(value2)至floor(value1))的整数值。 522 -- ps：汇总游标使用：可用1 .. table_instance.count或者table_instance.first .. table_instance.last来指定遍历集合数据类型变量里存的数据 523 -- 的下标范围（从1开始），可通过“table_instance(var1).具体字段名”来使用每行数据内的每列值，var1相当于循环计数器。 524 -- 中者设置循环游标查询出来的结果集，for开始时隐含地打开游标，在循环开始隐含地执行了fetch， 525 -- 在继续循环前隐含地检查了notfound值，在循环结束隐含地执行了close，较便利。 526 -- ps：在for内部var1相当于一个赋了值的record，可通过“var1.具体字段名”来使用每行数据内的每列值 527 -- 后者设置循环select语句查询出来的结果集，此时变量var1为循环这个结果集中的每一行数据， 528 -- 在for内部var1相当于一个赋了值的record，可通过“var1.具体字段名”来使用每行数据内的每列值。 529 -- ps：这种for循环游标简洁安全，省却了定义游标，open游标，fetch值，循环，判断退出，close游标等等操作，可用的时候推荐使用它。 530 loop -- 开始循环 531 content1; -- 循环的内容 532 end loop [label_name]; -- 结束循环 533 [close cursor_name;] --汇总游标的用法：同上。 534 535 while循环基本格式： 536 [open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：同上。 537 [for select_statement | select_sql_variable;] -- 同上。 538 ]; 539 [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：同上 540 into var1 [, var2]…| record_instance; -- 同上。 541 ] -- 初始化fetch值 542 [<<label_name>>] --设定一个标签名称，关联这个while…loop，可选 543 while condition1 loop -- 先判断是否满足condition1条件，然后开始循环 544 -- 汇总游标的用法：condition1则为cursor_name%found或ref_cursor_instance%found，看是否有数据。 545 content1; -- 循环的内容 546 [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：同上 547 into var1 [, var2]…| record_instance; -- 同上。 548 ] -- 循环fetch值，如果游标的用法，必须在循环最后都重新fetch值。while前的fetch只是一个初始化，只会执行一次。 549 end loop [label_name]; -- 结束循环 550 [close cursor_name;] --汇总游标的用法：同上。 551 552 */ 553 io_e := 'select * from user_tables'; 554 open vc_tables for io_e; -- 可使用open … for … using 的用法，类似于之前介绍的EI 555 loop 556 fetch vc_tables bulk collect -- bulk collect增强SQL引擎到PL/SQL引擎的交换 557 into v_members limit v_table_batch; 558 exit when v_members.count = 0; -- 退出fetch前的loop，否则fetch空不会报错，导致会无限循环下去。 559 for i in 1 .. v_members.count loop 560 for j in 1 .. 1 loop -- 假循环实现contine 561 if i=5 then 562 exit; -- 起continue的作用，退出假循环 563 end if; 564 dbms_output.put_line(v_members(i).table_name); 565 end loop; 566 567 begin -- 抛异常方式实现continue 568 if i = 5 then 569 raise continue_error; -- 起continue的作用，不再继续执行后续循环内容，到异常处理 570 end if; 571 dbms_output.put_line(v_members(i).table_name); 572 exception 573 when continue_error then -- 捕获此异常，并不做任何处理，以进行后边的循环 574 null; 575 end; 576 /* 577 oracle中continue的实现(由于oracle中没有continue)： 578 1. 如上例，使用一个for j in 1 .. 1 loop <content> end loop;这样的假循环将循环体包裹， 579 这样在内部exit只会退出当前的假循环。 580 2. 如上例，begin end、raise exception联合处理来实现异常，比较繁琐。 581 3. 通过goto和<<标签>>的配合也可实现continue，但是由于goto会打乱我们的程序逻辑，一般不使用，因此不介绍。 582 */ 583 end loop; 584 end loop; 585 close vc_tables; --关闭游标 586 587 /* 588 BULK COLLECT批量绑定：一次性提取所有行并绑定到记录变量里面，可增强SQL引擎到PL/SQL引擎的交换。 589 使用：配合into 记录类型： 590 1)select … bulk collect into … 591 2)fetch … bulk collect into … [limit row_number] 592 3)returning … bulk collect into … 593 */ 594 forall j in v_members.first .. v_members.last -- forall增强PL/SQL引擎到SQL引擎的交换 595 save exceptions -- 可选项配置，将产生的异常保存并继续批处理 596 execute immediate 'delete from user_tables_tmp where table_name = :1' using v_members(j).table_name; 597 /* 598 FORALL批联编基本格式： 599 forall index_name in { min_index .. max_index 600 | indices of collection_instance [ between min_index and max_index ] 601 | values of index_collection 602 } 603 [ save exceptions ] 604 DML_SQL|DML_EI; 605 1. index_name：无需事先申明的变量标识符，在循环中作为集合的下标来使用，常命名为i,j,k…等。 606 2. min_index、max_index：数值限定index_name的最大最小值。 607 3. indices of record_instance：当记录中第i行未初始化赋值，或者被删除导致没有值，我们需要在循环中调过这 608 些行的数据，则需使用indices of，以此使下标调过这些无效项，同时也可配合min_index、max_index使用。 609 4. values of index_collection：上述3是自动跳过无效项，而此项values of配置则是手动设置“有效项”，index_collection 610 需为pls_integer/binary_integer组成的集合来配合。 611 示例：1）定义变量：type t_index_collection is table of pls_integer; 612 index_collection t_index_collection:=t_index_collection(2,4,6,8,10); --在定义时可初始化 613 2）内部赋值：index_collection:=t_index_collection(1,3,5,7,9); 614 5. save exceptions：配置此项则表明当批量执行中遇到异常仍执行到最后。此时会抛出ORA-24381： error(s) in array DML。 615 可将此异常在变量定义区定义异常来关联，如本存储过程定义的bulk_error变量。 616 同时所有异常信息会保存在sql%bulk_exceptions，可在捕获异常处处理，见本存储过程最后异常处理部分。 617 6. DML_SQL|DML_EI：此项是批处理的单条DML语句，也可以用EI语句。如果单条DML语句需循环引用集合数据执行， 618 那么采用FORALL会显著地提供处理性能，它会通过只调用一次SQL，然后将整个循环处理的内容提交上去。 619 7. FORALL一般配合select … bulk collect into … 使用。 620 但当数据量比较大的时候，往往配合fetch … bulk collect into … limit row_number来分批处理可使效率更快。 621 下面提供一个简单实用的海量数据分批处理模板： 622 declare -- 在处理量多的时候，最好转成存储过程然后通过JOB调用来执行，否则手动在plsql执行很容易卡死。 623 maxrows number default 5000; -- 可根据情况适当调整每批处理的量 624 row_id_table dbms_sql.urowid_table; 625 cursor c_cursor is 626 select n.rowid from 表 n where 一些限制条件; 627 begin 628 open c_cursor; 629 loop 630 fetch c_cursor bulk collect 631 into row_id_table limit maxrows; 632 -- exit when c_cursor%notfound; --2018年5月10日22:24:20 发现有误，需要调整为下面语句，或把此行移至commit后，否则丢数据 633 exit when row_id_table.count = 0; 634 forall i in 1 .. row_id_table.count 635 update 表 a 636 set a.字段1 = 值1 637 where rowid = row_id_table(i); 638 commit; 639 end loop; 640 close c_cursor; 641 end; 642 643 */ 644 645 commit; 646 /* 647 COMMIT： 648 ps: （2017-9-16）要不是读了TOM的书，还不知道commit的使用情况，很多错误的理解错误的攒了下来，如果不去实践， 649 或者从其它途径了解到真实情况，可能永远都得不到更正了，还是要多读书多实践啊，使用commit必须要谨慎，TOM告诉了我， 650 "COMMIT和ROLLBACK一般情况下不应该在PL/SQL中使用，只有PL/SQL存储过程的调用者才知道事务何时完成。 651 在你开发的PL/SQL过程中执行COMMIT和ROLLBACK是一个不好的编程实践" 652 653 如果此存储过程是自己完成任务而不是给别人调用，那么一般也就在最后显示的加一个commit来进行提交工作， 654 对于不同的使用工具、环境来说，有可能在正常退出会话时会进行commit操作， 655 也有可能进行rollback操作，这些隐式的行为是不可控的，所以对于整体的事务来看，显示提交和回滚是正常有效的。 656 657 如果此存储过程是提供给别的程序、存储过程等调用的，那么还是让别人来操心提交的问题吧，因为这种情况下你只是做了 658 一部分的活，你（不是自治事务的存过）不是一个主要的整体，你会把别人主事务在调用你之前的未提交的内容一起提交！ 659 可能需要使用多个提交的情况是单个海量数据操作事务整体时根据操作量来提交，例如每100000条数据提交一次之类等， 660 反正还是得谨慎使用，如果频繁地提交，通常并不会更快，一般在一个SQL语句中完成工作几乎总是更快一些。 661 而且用到频繁提交一般也得配合批处理来进行，总之都会使得一条SQL语句的活变成了一堆复杂的代码，而且这样提交，不可避免的， 662 需要考虑到中途异常失败的处理，异常前用commit提交的内容已经提交了无法回滚了，那么使用到这种提交的必定要是能 663 从失败的地方解决问题如何接着继续完成未了的事情，也就是你能够清楚的知道哪些是处理掉的（有可能是处理表中有字段做标识， 664 有可能是从一个表到另一个表等） 665 666 因此，取一个网友的话来总结下： 667 "从事务上来说，如果是单独的存储过程调用，一个commit,如果是前台语言调用存储过程，调用的语言commit.. 668 从性能上说，如果做很多大事务，不commit,必然对undo产生冲击，可能会干爆undo...但是太频繁也不行，太频繁commit,redo受不了 669 从锁上说，高并发的，你多条语句如果commit一次，语句慢的话，产生锁的时间必然长，会造成其他session等待的问题。。。" 670 671 and TOM:"数据库的这些组件（临时段、undo和redo等）不是开销，而是系统的关键组件。必须适当地设置大小（不要太大，也不要太小）" 672 （如果你使用了pragma autonomous_transaction，则务必得记住commit语句的添加，否则没有在DML操作后没有提交或会滚回导致报错 673 “ORA-06519: 检测到活动的独立的事务处理, 已经回退”） 674 */ 675 exception 676 -- 处理异常 677 when no_data_found then 678 rollback; --回退，其实一般捕获这种特定的异常基本表示是需要特殊处理这些异常，而不是直接回滚 679 --说明是一个预料中的异常，用对应的措施去处理。 680 -- 一种oracle的预定义异常，表示select into没有找到数据 681 dbms_output.put_line('我也不晓得你的哪条select into没有找到数据'); 682 when bulk_error then 683 rollback; --回退 684 for i in 1 .. sql%bulk_exceptions.count loop 685 dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code)); 686 dbms_output.put_line('index='||sql%bulk_exceptions(i).error_index); 687 dbms_output.put_line('table_name='||v_members(sql%bulk_exceptions(i).error_index).table_name); 688 end loop; 689 when others then 690 /* 691 ps: （2017-9-16）TOM:"实际上，我认为所有只包含WHEN OTHERS异常处理程序，而不包括RAISE或RAISE_APPLICATION_ERROR 692 来重新抛出这个异常的代码都是bug。它只是悄无声息地忽略这个错误，这会改变事务语义。" 693 */ 694 rollback; --回退 695 v_sqlcode := sqlcode; 696 v_sqlerrm := sqlerrm; 697 dbms_output.put_line('本次的异常code：' || v_sqlcode || '\n本次的异常信息：' || 698 v_sqlerrm); 699 /* 700 异常处理基本格式： 701 begin 702 … 703 exception -- 跟begin对应的，一个begin…end块里面只能有一处这个异常处理，所以如果想要详细确定哪一步出的错， 704 -- 将每个步骤单独用begin…end(可嵌套使用)包裹起来，然后细致明确的抛出对应的异常情况。 705 [when 某种预定义或用户自定义的异常1 then 706 content1; 707 [when 某种预定义或用户自定义的异常2 then 708 content2;] 709 … 710 ] 711 when others then 712 content_others; 713 end; -- 和begin对应 714 ps：异常的更多信息暂不讲述，待后续添加，想了解的可网络查询。 715 ps: （2017-9-16）在使用when others then时务必要注意，你可能因此写了个bug，如果是你打算把所有异常都记录至日志表中，以此反馈告知也还可行，如果没有任何记录此异常或抛出此异常的行为，那么你很可能把所有未知的异常都吞掉了，从表面上来看，你这块代码总是没问题的（就算它没正常完成自己的任务）。 716 */ 717 718 /* 719 存过的调试、存过的调用见另一篇文章《懵懂oracle之存储过程2》：http://www.cnblogs.com/snowballed/p/7028912.html 720 存过相关oracle JOB处理见另一篇文章《懵懂oracle之存储过程3--JOB详解》：http://www.cnblogs.com/snowballed/p/7245739.html 721 */ 722 end sp_hll_test_20170415; -- 笔者的习惯，最外层的end总是“end 本个存储过程或本个函数等”，有始有终，当然不加后面的存过或函数名称也能正常使用。 723 / 724 --如果存储过程或者函数或者PL/SQL块后边还有其它的内容如普通语句或其它存储过程、函数等的创建，需用"/"作为分割符， 725 --在sqlplus中则需加此来表示内容写完了需要执行了~~
作者：滚雪球俱乐部-何理利
出处： http://www.cnblogs.com/snowballed/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接。
如有疑问， 可邮件（he.lili1@ztesoft.com）咨询。
