首页 > 代码库 > 懵懂oracle之存储过程
懵懂oracle之存储过程
/* 作为一个oracle界和厨师界的生手，笔者想给大家分享讨论下存储过程的知识，因为在我接触的通信行业中，存储过程的使用还是占据了一小块的地位。 存储过程是什么？不得不拿下百度词条的解释来：“存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中， 经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。” 其实就似我们经过一系列的材料准备和烹饪过程准备的一道菜，这样的菜内部消化是存储过程，而端出来给别人吃就是函数了（比喻好像不恰当……），后边也不提函数， 因为懂得了存储过程，函数也就会了，只要稍微了解下结构就行。 虽然说用上了存储过程，也喜欢上存储过程，但是还是得在分享时提提它的利和弊，因为笔者也被它的弊坑得很惨（要迁移近百个存储过程？表模型也要改造有大变动？ 还以前基本用dblink（这个坑货更不想提，咱还是忘记它吧！）传输？），然后苦逼的“搬砖” 生涯开始了（如果是自己做的菜，怎么着也吃得下，不好吃也很快就能改好菜谱， 但别人的菜别人的菜谱……一言难尽！）。你看，存储过程用起来是爽，很多时候也提高效率，笔者也是算java开发的，比起用java一次次的从厨房拿材料出来到java代码里面来搞事情， 在方便的时候还是倾向于在厨房里把菜做好，这样也减少了网络传输，因为很多时候调来调去处理好数据最终还是写到表里面去，所以才把整个逻辑直接包在存储过程中，而且有plsql， 也还算方便调试，特特特便利的是，如果这盘菜按照菜谱做出来不好吃，诶，我们改改菜谱在厨房里立马就能做新的出来，维护性也很强，如果逻辑在java里面就得部署升级重启项目。 但是爽归爽了，在迁移的时候就懵逼了，存储过程和数据库绑在一起，库一旦变了，那得重新把它们绑起来，而且前期开发的时候如果未把这些存储过程功用记录好，迁移就没人知道它是拿来干嘛的， 是不是必须的（不可否认，总会有些脏东西在厨房里面，或多或少会把这些东西也带到新厨房）。 闲谈这么多，该到一些基础的语法了（由于笔者看到大写容易文盲，所以多数字母都是小写） */ create or replace procedure sp_hll_test_20170415 /* 创建[或者替换] 存储过程 存储过程的名字 所有对象的名称都一样限制为最多30个字节……笔者一般习惯以sp_起头，而且不喜欢把名称包裹在英文双引号里面 因此得提下双引号一些常见的用法： 1.当为oracle特殊关键字时（不建议存储过程啊表啊字段啊等名称使用oracle关键字（可搜索了解）命名） 2.用于一些格式字符串中包裹非法格式 （例如像用于select to_char(sysdate, ‘yyyy"年"mm"月"dd"日" hh24"小时"mi"分"ss"秒"‘) from dual; 但是在select ‘yyyy"年"mm"月"dd"日" hh24"小时"mi"分"ss"秒"‘ from dual;中则双引号正常输出。 ps：dual表的用途如果不清楚，还是去网上搜索下吧。 ） 3.用于严格区分大小写（在sql语句中大小写不敏感，但在数据库中数据值大小写敏感） （一般而言我们创建表、字段、存储过程、等对象，oracle是默认大写名称的，所以要是乱加双引号会导致你找不到你认为建好的表， 用不了你认为你建好的存储过程，因为你使用它们的时候忘了双引号区分大小写。） 当然使用了英文双引号包裹存储过程名称的话，这两个引号是不计入长度的。 而且得注意，在oracle中，单个汉字的长度，根据设置不同，占2(ZHS16GBK字符集)~3(AL32UTF8字符集)个字节， 同时也可提下在oracle中，下面这条语句两个值可是不同的： select length(‘abcd啊啵唓嘚‘),lengthb(‘abcd啊啵唓嘚‘) from dual; 前者把单个汉字算一个长度，后者根据字符集不同算2~3个长度。 (安装oracle默认是AL32UTF8字符集，字符集修改为ZHS16GBK字符集的方法： CONNECT SYS_NAME/SYS_PASSWORD AS SYSDBA --根据自己的实际情况登入 SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; SHUTDOWN IMMEDIATE; STARTUP; ps：不是自己的厨房（数据库）可别乱动哦！ )。 ps：创建一个存储过程也就是准备一道菜谱，菜谱的具体内容都在下面一一列出。可千万要明确好是准备新的菜谱， 不要擅自把别人的菜谱替换为你的菜谱了，这样做出来的菜可不一定是顾客要吃的。 也就是说不要随便使用"or replace" ，在新建每个存储过程的时候，务必去除这两个词（这样的话在新建如果重名就会有提示）， 或者确保自己的存储过程不和已有的存储过程重名，这边就可提下如何查看已有存储过程的方法： 1.在plsql中一个SQL窗口内输入存储过程的名称，通过按住ctrl键，然后左键单击存储过程名字，就可进入存储过程的内容程序窗口， 要是进不了，恭喜您，该名称可用于新建新的存储过程。也可通过右击存储过程名称，通过弹出的右键菜单中“查看”、“编辑”进入内容窗口， 在编辑模式下才可实时修改存储过程内容，然后点击运行或者按F8进行编译，如果有问题有错误都会及时反馈在窗口下部的矩形区域内。 2.在plsql左侧对象资源栏中展开Procedures，然后在搜索框中输入存储过程名称，通过回车进行搜索，找到对应的存储过程后也可在其名称上右击 打开右键菜单。 3.最靠谱的语句查询来了，上面第2点也就相当于查询all_objects这个表： select * from all_objects a where a.object_type = ‘PROCEDURE‘ and a.object_name = ‘你的存储过程名称，记得大写(当然你建存过时定制化了有小写的请忽略，不想和你说话……)，用plsql可双击选中一串信息，然后右击-选择-大写‘; ps：正如表名含义所示，该表记录了所有对象的信息，所以存储过程、表、视图、序列、包、函数……一系列的对象你都可以查得到。 */ ( /* 当要创建的存储过程需要一些外部参数时，就用括号括起来，没有参数的时候就不用加这对括号了 参数基本格式：param1 [ in | out [nocopy] | in out [nocopy] ] type1 [(default | :=) value1] 参数名 [ 入参(默认，可省略) | 出参 | 既入又出的参] 参数对应的类型 [默认 值] ps：参数的类型不能指定长度 注意：in {只能够将实参传递给形参，在存储过程内部只能读取使用该值，无法修改。可为变量或常量} out {不管外部实参是否有值，进入存储过程内部初始值总是null，在内部可以任意使用修改该参数的值，存储过程执行完毕后，形参的值会传递给实参。必须为变量} in out {既可正常接收实参值，又可在结束时传递形参值给实参。必须为变量} out和in out都可加nocopy，有需求的人员可了解下，加上nocopy就可能是按引用传递（nocopy是一个提示而不是指令，编译器可以决定是否执行该项），没加则是按值传递。某些情况下会有效率提升， 在遇到异常时候两者有不同之处要注意，笔者未使用涉及nocopy，详情略过不表。 ps：菜谱要是没有其它特殊进口材料要用，就不需要进口的包装袋了 */ /* Warning(警告)：请不要学习笔者下面的用法，参数、变量等命名为a、b、c、d……务必发挥你英文特长或网络汉英词典的优势，将参数、变量命名的有含义，最好加上注释注明含义。 同时也不可直接使用对应表的对应字段名称作为参数名称或变量名称，例如： 参数名或变量名为table_name，user_tables表的table_name字段，那么往往会遇到在存储过程主体中使用如下模式的语句： select ut.status into v_status from user_tables ut where ut.table_name = table_name; 当然，你的本意可能是想让ut.table_name = 入参table_name，可是对于sql语句来说，这个table_name就是user_tables里面的table_name列，而对表来说， 它每行的ut.table_name总是等于它的table_name，所以select ut.status 返回的就是整个表的status列的值，你还可以把这个列的值存到你定义的单值变量v_status中吗？当然不行啦。 因此，参数或变量要有含义，而有字段名称含义的参数也得加些东西改动。 笔者习惯的参数或变量模式为：i_(入参)、o_(出参)、io_(入出参)、v_(变量)、c_(游标)…… 同时在oracle中，使用" := "进行初始化或赋值的操作，而" = " 则是比较左右两边的值是否相等返回true或false的boolean值。 */ i_a in number, -- 入参a，in可省略 i_b date default sysdate, -- 入参b，用default设定默认值 i_c varchar2 := ‘default c value‘, -- 入参c，用:=设定默认值 ps：默认值就相当于大众口味，当然你也可以改动它，作为湖(fu)南(lan)人(yin)我表示要特辣 o_d out number, -- 出参d i_status user_tables.status%type, /* i_status user_tables.status%type 一种实用的设定参数类型的方式，它的类型会随着user_tables这个表的status的这个字段的类型一起变动保持一致， 当该个参数实质数据来源于某个表某个字段，可仿照上例设置，就不用担心该个字段的类型有变动，长度有变化之类的， 导致后边又得来改动这个参数的类型。 */ io_e in out varchar2 -- 既入又出的参f ) AUTHID DEFINER /* AUTHID DEFINER(可省略，默认为这个) | AUTHID CURRENT_USER 前者表示这个存储过程使用创建这个存储过程的用户的权限来运行--定义者权限。 后者表示这个存储过程使用当前调用它的用户的权限来运行--调用者权限。 这样有什么用呢？一个用户一般对应一个schema（不知可否对应多个，暂不考虑），该用户的schema名等于用户名，并作为该用户缺省schema。 而这个schema就对应了这个用户下面的所有数据对象的集合，用户如果使用自己的东西，当然可以不用打招呼：select * from 我自己的表; 而要是其它用户的表你也有查询权限，那用之前还是需要打下招呼：select * from 其它用户schema名.其它用户schema下的表; 同理，其它对象如视图、函数、存储过程等等都需要这么做。我们总是喜欢偷懒的，所以调用自己的东西总是不会打招呼，因此存储过程里面查询 的表总是会没带schema。 可要是有这种情况：我们大家都有a、b、c三张表，都想要汇总a、b表信息放到c表中（就是想做的活是一模一样的），哥们1号就创建了存储过程来做这个活， 但是悲剧的事情发生了，其他哥们包括我，都想用这个存储过程，诶，都去帮哥们1号干活去了，都去操作汇总哥们1号的表了。想各自都干各自的活怎么办？ 难道只能像哥们1号一样，都各自建各自的存储过程吗？因此有AUTHID CURRENT_USER这个设置了，这样这个存储过程，谁调用它，它就用谁的schema去查对应 的表（不仅仅是表，不带schema的其它所有对象都一样）。哈哈，“王美”。 ps：用了AUTHID CURRENT_USER的菜谱，那就每个厨师用自己的厨具来干活了。 */ is -- is | as 这个地方is或者as都可，看喜好。 /****************************************************************************** NAME: HLL_TEST_20170415 PURPOSE: 懵懂oracle之存储过程 REVISIONS: Ver Date Author Description --------- -------------- --------------- ------------------------------------ 1.0 2017-04-15 HE.LILI 创建存储过程 ****************************************************************************** ******************************************************************************/ /* 下面这块属变量定义区域，类型除了常用的一些字符串类型、数字类型、日期类型、LOB类型等，还可以自主搭配定义类型。 同时不同于参数，变量类型如果有长度区分则可自定义长度，具体oracle含哪些基本类型和各个类型的一些默认长度范围和设定情况 可网络搜索了解。 变量基本格式：var1 type1 [(default | :=) value1]; 变量1 类型1 [默认 值1]; ps：厨房里也有基本佐料如盐、糖、油、醋等等，也可以自由搭配出你的独门秘方yo。 */ v_salt char := ‘Y‘; -- 默认长度为1 v_sugar varchar2(50) default ‘sugar is sweet,and so are you.‘; -- varchar2需指定长度 v_oil date; v_vinegar clob; v_chili number; -- 默认[10e-130,10e126) v_ginger user_tables.table_name%type; -- 和参数一样，我们也可这样根据对应表对应字段设置类型 v_table_batch number := 1000; v_sqlcode number; -- 用来获取异常code v_sqlerrm varchar2(1024); -- 用来获取异常errm信息 subtype number1 is number(8, 2); -- 定义子类型number1。 注意：number(8,2)表示整数位最多6位小数位最多2位的数值 v_paprika number1; -- 变量 number(8,2) v_capsicum number1; -- 变量 number(8,2) v_pepper number1; -- 变量 number(8,2) /* subtype又是我们用于偷懒的方法，基本格式：subtype subtype_name is based_type [not null] subtype 子类型名称 is 基类型 [非空] 然后我们就得到个“新”的类型，也就相当于is后边的类型的一个别名，因为有些时候我们有一批变量都用到这个类型，一旦长度或类型有变化，一一都去改很麻烦， 所以就可定义这个子类型，然后那一批变量都用这个子类型，这样就只要改这个子类型，然后所有用它的变量类型就都改变了。 ps：子类型长度限定不一定要有，可以在用这个子类型的时候再加。 子类型也可以是根据下面会表述的record类型、table类型等创建的子类型，但是这样的子类型只继承大小精度等约束，并不能继承其他约束，如not null。 */ v_user_tables user_tables%rowtype; /* 单行多列（一个表字段量也就1~1000个）数据。 类似于%type，%rowtype表示行类型，%号前面是表则对应这个表的行类型，如果是游标则是游标的行类型。 */ type tr_redburnedlionhead is record( -- 定义一个叫tr_redburnedlionhead的record类型。 v_choppedpork varchar2(100) := ‘good marbled meat‘, v_eggs user_tables.iot_name%type default ‘one egg or two‘, v_scallion number1, v_ginger char(2), v_cookingwine user_tab_columns.data_length%type); r_redburnedlionhead tr_redburnedlionhead; -- 实例化使用叫tr_redburnedlionhead的record类型，定义变量r_redburnedlionhead。 /* 单行多列数据，当需要用的类型是一个或多个表的某些字段和某些其它类型的集合，我们就能使用record了 record基本格式：type record_name is record( -- 定义一个record类型 var1 type1 [not null][(default | :=) value1], var2 type2 [not null][(default | :=) value2], var3 type3 [not null][(default | :=) value3]); record_instance record_name; -- 实例化使用这个record类型，这是最基本的使用方法，还可以用于其它需要类型的地方，如后边会讲述的varray或table中，可互相嵌套。 */ type tv_rice is varray(3) of varchar2(50); -- 定义一个varray类型 a_rice tv_rice; -- 实例化使用叫tv_rice的varray类型，定义变量a_rice。 /* 多行单列数据，varray基本格式：type varray_name is varray(size) of type1 [not null]; -- 定义一个varray类型 varray_instance varray_name; -- 实例化使用这个varray_name类型，亦可用于table of的类型。 varray里面的元素是有序排列的，通过size设定固定正整数位长度，可通过extend(k)方法增加k长度，a_rice.extend 与a_rice.extend(1)同样增加一个长度。 */ type tt_rooms is table of number index by varchar2(20); v_rooms tt_rooms; type tt_tables is table of varchar2(200); v_tables tt_tables := tt_tables(‘101号桌‘, ‘102号桌‘, ‘103号桌‘); -- 基本类型且未设置动态自增的table可如此初始化，或存过主体中使用如：v_tables.extend; v_tables(1) := ‘101号桌‘; 来初始化值。 type tt_members is table of user_tables%rowtype index by binary_integer; v_members tt_members; /* 多行多列数据，table基本格式：type table_name is table of type1 [not null] [index by binary_integer|pls_integer|varchar2(size)]; -- 定义一个table类型 table_instance table_name; -- 实例化使用这个table_name类型。 ps：type1-可为基本类型，可为record、varray(它们三者之间的互相嵌套可各自摸索使用)，可为游标%rowtype、表%rowtype。 index by binary_integer|pls_integer|varchar2(size) : 使用该项配置，则table会动态自增，无需利用extend方法申请扩展长度同时也不可在声明时初始化值。 binary_integer：整型下标，值计算由oracle模拟执行，不会溢出，但执行速度较慢，最为常用； pls_integer：整型下标，值计算由CPU执行，会出现溢出，比oracle模拟快； varchar2(size)：字符串下标，size范围为1~32767。 */ type tr_tables_comp is record( tb_name user_tables.table_name%type, tbsp_name user_tables.tablespace_name%type); cursor c_tables return tr_tables_comp is select table_name, tablespace_name from user_tables where status = i_status; type trc_tables is ref cursor; vc_tables trc_tables; /* 显示游标基本格式：cursor cursor_name -- 定义一个叫cursor_name的显示游标，可以用括号设定入参，类似于开头介绍过的存过入参，但是此处只能为in类型的入参。 [(parameter[, parameter]…)] --定义若干参数，基本格式param1 [in] type1 [(default | :=) value1] [return return_type] -- 比较少添加，return_type定义返回一个记录类型，指定了游标变量最终返回的查询结果集类型，需和select_statement返回的类型和列数目一致， -- 可以是自定义的记录类型(最终获取游标行数据时列名采用记录内部字段名称)或%rowtype定义的记录类型。 is select_statement -- 定义游标对应的select语句，可用上所有cursor定义的参数和存储过程的in类型参数。 [for update -- 定义for update后，则可在循环游标时，通过where current of cursor_name来更新或删除当前游标所在行数据，会默认给select语句中所有表加共享锁。 [of [table1.]column1[, [table2.]column2]…] -- 定义不同的列，则for update后只给对应的表加共享锁 [nowait] -- nowait用于指定不等待其它会话的锁，当遇此情况，会抛出ORA-0054异常并退出当前块，默认当前会话要一直等待释放。 ]; 隐示游标基本格式：sql%isopen | sql%%found | sql%notfound | sql%rowcount -- 这不是定义，而是在存储过程主体中使用这四个属性的方式。 基本上是这四种用法，“PL/SQL为所有SQL数据操作语句（包括返回一行的SELECT）隐式声明游标，称为隐式声明游标的原因是用户不能直接命名和控制此类游标。 当用户在PL/SQL中使用数据库操作语言（DML）时，Oracle预定义一个名为SQL的隐式游标，通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。"--摘自百度百科 游标变量基本格式：type ref_cursor_name is ref cursor -- 定义一个游标变量类型 [ return return_type]; -- 类似显示游标处作用，有指定此处return则是强类型定义（限制了后面调用时select需要返回的类型次序），否则是弱类型定义（未限制）。 ref_cursor_instance ref_cursor_name; -- 实例化使用这个ref_cursor_name类型 -- ps：此处实例化游标变量时还可用sys_refcursor这个类型，它是oracle9i以后系统定义的一个ref cursor，主要用在过程中返回结果集（作为出差）。 ps：当sql语句是动态生成的时候，我们就没办法按显示游标基本格式定义游标，所以可以用游标变量，在存过主体中它俩的基本用法也差不多，见后面关于循环的知识点。 游标基本属性： isopen：【显】当游标已打开时返回 true。【隐】因为隐示游标在执行DML语句前自动隐含式的打开，并在DML执行完后关闭，所以隐示游标的该值总为false。 found：【显】当最新fetch提取游标操作成功则为true，否则为false。【隐】当insert、update、delete语句处理一行或多行，或者是执行select into 语句返回一行（多行或0行都会直接异常报错）时，该属性为true，否则为false。 notfound：【显】【隐】与found属性相反。 rowcount：【显】返回当前已从游标中读取到的记录数。【隐】执行insert、update、delete语句返回的行数（0~n），或执行select into语句时查询出的行数（0或1，多行就异常不会再判断rowcount）。 */ pragma autonomous_transaction; /* [ pragma autonomous_transaction; ] 自治事务，可选的一项配置，相当于一段独立出来的子事务，设置后在此存储过程中可以自由的commit或者rollback，却不会影响到调用这个存储过程的主存过主事务中去， 当然主事务未commit的数据对我们这个存储过程来说也是不知道的，主事务的rollback也回滚不了我们子事务已commit的东西。 一般专用于用来记录日志的存储过程。 */ begin -- 存储过程主体内容的开始 ps：菜谱的材料都准备好了，该来做菜步骤了。 /* if判断基本格式：if condition1 then content1 -- 当满足条件1的时候，做内容1的事情 [elsif condition2 then content2] -- 当满足条件2的时候，做内容2的事情 [elsif condition3 then content3] -- 当满足条件3的时候，做内容3的事情 … end if; -- 结束if判断，注意加分号 case判断基本格式：case [var1] when value1|condition1 then -- 有var1时，when后面跟value1~n(对应具体值，进行判断var1是否等于value1~n)；无var1时，when后跟condition1~n，类似于if的用法。 content1 -- 当满足条件1的时候，做内容1的事情 [when value2|condition2 then content2] -- 当满足条件2的时候，做内容2的事情 [when value3|condition3 then content3] -- 当满足条件3的时候，做内容3的事情 [else contentn] end case; -- 结束case判断，注意加分号 ps：使用"case …end case;"则在"then"后接具体的执行语句； 使用"case …end"则在"then"后接具体的值，整个case结构代表一个具体固定类型的值，所有then与else后返回的值类型必须统一（或number或varchar2等等，由第一个then后的值类型确定）。 ps：判断格式condition1~n未要求但可使用小括号包裹，也可利用小括号包裹部分形成一个整体用于条件组合。 */ if (case i_a when 1 then true when 2 then false when 3 then true else false end) or (i_b > sysdate and i_c = ‘default c value‘) then -- a := 2; 这个语句是错误的，作为一个in的入参，不可以在存储过程中用“:=”去重新赋值，只能拿本身的值去使用 o_d := 1; -- 而作为out的出参，则当然可以改动值。 io_e := ‘changed e value‘; -- 既入又出的参，也能改动值，同时还可以接收外界传的值。 end if; case when i_status = ‘‘ then -- null = ‘‘ --> false /* =‘‘用法是不对的，或者说它总是false，在存储过程编译的时候就会有提示信息，告诉你“怀疑有 NULL 进行比较的情况”。 在oracle中，‘‘ 就被当做为null了，而mysql则两者是不同的东西，这个务必要注意， 暂发现在oracle中concat、count函数，把null能看成是‘‘，一个空字符串，其它函数中，基本结果都是null， 当然如果是一些逻辑运算的话，例如：null = null; null > ‘1‘ 等等，结果都是false。 */ v_sugar := ‘I need more sugar!‘; dbms_output.put_line(‘sugar:‘ || v_sugar); /* dbms_output是oracle的一个系统包，可用于输出一些调试信息，常用方法： put：把内容写到内存，无换行符在末尾，遇到put_line或new_line后才会把内容输出； put_line：输出内容并换行（输出一行内容包括前面所有put里的内容）； new_line：相当于输出换行符，另起一行，亦即结束当前行（把所有put里的内容也输出）。 在plsql中的“SQL窗口-输出”、“测试窗口-DBMS输出”处都有设置“缓冲区大小”，用dbms_output输出的内容在此处区域显示， 且put的内存内容字节长或put_line的内容的字节长或所有本次输出的内容字节总长都不能大于“缓冲区大小”，同时每行最多32767字节长， 否则会报错（在手动执行测试时），在调试存过的时候也会因此异常而中断存过，无法继续执行，需要注意使用。 ps：其它方法或其它常用系统包根据情况网络搜索了解，不再细表。 */ when length(i_status) = 0 then -- null = 0 --> false v_sugar := ‘more and more sugar!‘; dbms_output.put_line(‘sugar:‘ || v_sugar); when i_status is null then v_sugar := ‘just "sugar_maroon 5"!‘; dbms_output.put_line(‘sugar:‘ || v_sugar); end case; /* 因游标与循环关联比较多，故将游标定义后在存储过程主体中的用法也汇总在循环格式里。 loop循环基本格式：[open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：打开一个游标，若为带参数的游标可在此处传入参数。 [for select_statement | select_sql_variable;] -- 当open的是一个游标变量时，我们可以在此处for后添加select语句或者一个字符串变量（拼接的一串select语句）。 -- ps：用法多多，可以根据条件判断open不同的语句，也可重复利用同一个游标变量open不同的语句。 ]; [<<label_name>>] --设定一个标签名称，关联这个loop，可选 loop -- 开始循环 [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：loop开始时每fetch取一行数据，然后游标自动指向下一行数据 into var1 [, var2]…| record_instance; -- fetch获取一行数据后，还得仍到我们定义的一些变量中保存，也可放到record中作为一个整体（使用时“record_instance.具体的属性名称”）。 exit when cursor_name%notfound|ref_cursor_instance%notfound -- 游标的话就在此处exit，判断是否已经没有数据了。 ] content1; -- 循环的内容1 if condition1 then [content2;] exit [label_name]; | exit [label_name] when condition1; -- 无论是用if…exit还是exit…when都行，请有能退出循环的条件。 content2; -- 循环的内容2 end loop [label_name]; -- 结束循环 for循环基本格式：[open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：同上。 [for select_statement | select_sql_variable;] -- 同上。 ]; [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：同上 bulk collect into varray_instance|table_instance [limit rows]; -- bulk collect into提取一批数据放入varray/table的一些实例变量内，limit项可选，限制每批提取数据的最大的行数，rows为正整数（常量or变量）。 -- ps：limit一般用于分批提取处理，将“for循环limit分批值”放入上面的loop循环游标内部，来循环中分批提取数据，既保证能提取游标中所有数据， -- 又可通过分批提高效率。 ] [<<label_name>>] --设定一个标签名称，关联这个for…loop，可选 for var1 in -- 设定一个变量，且这个变量无需在前面有定义过 [reverse] value1 .. value2|cursor_name|(select_statement) -- 前者设置循环[floor(value1),floor(value2)]，此时变量var1为从floor(value1)至floor(value2)（设置了reverse则从floor(value2)至floor(value1))的整数值。 -- ps：汇总游标使用：可用1 .. table_instance.count或者table_instance.first .. table_instance.last来指定遍历集合数据类型变量里存的数据的下标范围（从1开始），可通过“table_instance(var1).具体字段名”来使用每行数据内的每列值， -- var1相当于循环计数器。 -- 中者设置循环游标查询出来的结果集，for开始时隐含地打开游标，在循环开始隐含地执行了fetch，在继续循环前隐含地检查了notfound值，在循环结束隐含地执行了close，较便利。 -- ps：在for内部var1相当于一个赋了值的record，可通过“var1.具体字段名”来使用每行数据内的每列值 -- 后者设置循环select语句查询出来的结果集，此时变量var1为循环这个结果集中的每一行数据，在for内部var1相当于一个赋了值的record，可通过“var1.具体字段名”来使用每行数据内的每列值。 -- ps：这种for循环游标简洁安全，省却了定义游标，open游标，fetch值，循环，判断退出，close游标等等操作，可用的时候推荐使用它。 loop -- 开始循环 content1; -- 循环的内容 end loop [label_name]; -- 结束循环 while循环基本格式：[open cursor_name|ref_cursor_instance [([param1 =>] value1[, [param2 =>] value2]…)] -- 汇总游标的用法：同上。 [for select_statement | select_sql_variable;] -- 同上。 ]; [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：同上 into var1 [, var2]…| record_instance; -- 同上。 ] -- 初始化fetch值 [<<label_name>>] --设定一个标签名称，关联这个while…loop，可选 while condition1 loop -- 先判断是否满足condition1条件，然后开始循环 -- 汇总游标的用法：condition1则为cursor_name%found或ref_cursor_instance%found，看是否有数据。 content1; -- 循环的内容 [fetch cursor_name|ref_cursor_instance -- 汇总游标的用法：同上 into var1 [, var2]…| record_instance; -- 同上。 ] -- 循环fetch值，如果游标的用法，必须在循环最后都重新fetch值。while前的fetch只是一个初始化，只会执行一次。 end loop [label_name]; -- 结束循环 */ io_e := ‘select * from user_tables‘; open vc_tables for io_e; loop fetch vc_tables bulk collect into v_members limit v_table_batch; for i in 1 .. v_members.count loop dbms_output.put_line(v_members(i).table_name); end loop; end loop; exception -- 处理异常 when no_data_found then -- 一种oracle的预定义异常，表示select into没有找到数据 dbms_output.put_line(‘我也不晓得你的哪条select into没有找到数据‘); when others then v_sqlcode := sqlcode; v_sqlerrm := sqlerrm; dbms_output.put_line(‘本次的异常code：‘ || v_sqlcode || ‘\n本次的异常信息：‘ || v_sqlerrm); /* 异常处理基本格式：begin … exception -- 跟begin对应的，一个begin…end块里面只能有一处这个异常处理，所以如果想要详细确定哪一步出的错，将每个步骤单独用begin…end包裹起来，可嵌套使用。 [when 某种预定义或用户自定义的异常1 then content1; [when 某种预定义或用户自定义的异常2 then content2;] … ] when others then -- 所有上面没处理到的异常，一般是有这处的，保证完美处理所有异常 content_others; end; -- 和begin对应 ps：异常的更多信息暂不讲述，待后续添加，想了解的可网络查询。 */ /* //TODO 未完待续： 1.execute immediate大杀器 2.实现continue 3.clob的使用警示 4.海量数据删除修改与forall 5.存过的调试 6.job实现存储过程定时执行，及job的异常处理 ……
n.格式调整 ヾ(￣▽￣)Bye~Bye~
*/ end sp_hll_test_20170415; -- 笔者的习惯，最外层的end总是“end 本个存储过程或本个函数等”，有始有终，当然不加后面的存过或函数名称也能正常使用。
懵懂oracle之存储过程
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。