在项目里写了个小的存数过程,功能是给一些特定人员类型补录对应档案流水号的。总结下:
开头:create or replace procedure insert_serial as
as后边接申请的变量:
perNum_str person_info.person_no%type; 表示声明的变量 perNum_str 的类型 和 person_info.person_no一样
cursor cur_perInfo is 表示定义一个游标cursor
select person_no, person_type,hj_area_id 表示游标 对应这个查询结果集
from person_info;
create or replace procedure insert_serial as perNum_str person_info.person_no%type; perType_str person_info.person_type%type; perOrgan_str person_info.hj_area_id%type; licNum_num licence_serial_no.cur_num%type; licType_str licence_serial_no.licence_type%type; cursor cur_perInfo is select person_no, person_type,hj_area_id from person_info;
开始遍历结果集合:
首先要声明打开游标,读数据要用fetch语句完成,依次把每行结果集放入到已经声明的变量里。
如果显式的打开游标,一定要加上 %found 属性判断,否则当游标到最后一行时候,就会无限循环取出最后一行结果集,不会自动推出loop循环
最后end loop 循环,提交事务commit,关闭游标
也可以用 for..in..:
用for....in... 语句遍历结果集 则不需要声明打开游标。(变量v后边直接接字段名,就可以取出结果)
/*begin for v in cur_perInfo loop dbms_output.put_line(v.person_no); end loop;*/
对游标取出的结果集合进行处理:
如果游标没有有效数据了,跳转到 exit 推出循环。
select .... into ... from 隐式游标,表示把查询出来的结果放入到对应的变量里。这种写法每次只能查出一行数据,如果结果有多行(too_many_rows)或者没有数据查出(no_data_found),程序会抛出异常,中断循环。
所以添加了异常处理 begin....exception (when then end;):
使用了 goto 跳转 ,当出现异常了,跳到<<ponit1>>,然后继续循环。
在oracle中 “ :=“ 表示赋值,“=”表示判断等价。 “||” 表示字符串拼接
使用to_char() 对数字格式化的时候,会自动把转换格式后的字符最高位,来保存数字符号,所以当正数格式化的时候,最高位会多出个空格。可以“fm”来消除空格
if cur_perInfo%found then begin select nvl(cur_num, -1), licence_type into licNum_num, licType_str from licence_serial_no where area_id = perType_str and cur_area_id = perOrgan_str; EXCEPTION WHEN NO_DATA_FOUND then goto point1; WHEN TOO_MANY_ROWS then goto point1; end; if licNum_num > -1 then licNum_num := licNum_num + 1; update licence_serial_no set cur_num = licNum_num where area_id = perType_str and cur_area_id = perOrgan_str; update person_info set DOCUMENT_NO =(licType_str||to_char(licNum_num,'fm0999')) where person_no = perNum_str; end if; <<point1>> null; else exit; end if;
完整代码:
create or replace procedure insert_serial as perNum_str person_info.person_no%type; perType_str person_info.person_type%type; perOrgan_str person_info.hj_area_id%type; licNum_num licence_serial_no.cur_num%type; licType_str licence_serial_no.licence_type%type; cursor cur_perInfo is select person_no, person_type,hj_area_id from person_info; /*begin for v in cur_perInfo loop dbms_output.put_line(v.person_no); end loop;*/ begin open cur_perInfo; loop fetch cur_perInfo into perNum_str, perType_str, perOrgan_str; if cur_perInfo%found then begin select nvl(cur_num, -1), licence_type into licNum_num, licType_str from licence_serial_no where area_id = perType_str and cur_area_id = perOrgan_str; EXCEPTION WHEN NO_DATA_FOUND then goto point1; WHEN TOO_MANY_ROWS then goto point1; end; if licNum_num > -1 then licNum_num := licNum_num + 1; update licence_serial_no set cur_num = licNum_num where area_id = perType_str and cur_area_id = perOrgan_str; update person_info set DOCUMENT_NO =(licType_str||to_char(licNum_num,'fm0999')) where person_no = perNum_str; end if; <<point1>> null; else exit; end if; end loop; commit; close cur_perInfo; end insert_serial;