oracle 存储过程、游标参考实例

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

create or replace procedure INIT_DICT_QUEUECODE(p_queueId int,p_paramType in varchar2,p_queenName in varchar2,p_ofDepart in varchar2,p_result out int) is loopNum number;--循环次数 codeNum number;--号源编号 codeFirstAm number; codeFirstPm number; cursor paramDetails is select queuename,paramtype,begintimepart,endtimepart,ofdepart,calltype,codecount from qs_param a where a.queueid=p_queueId and a.paramtype=p_paramType order by to_number(endtimepart);  c_row paramDetails%rowtype;  begin  loopNum:=1;  codeNum:=1;  codeFirstAm:=0;--未进行初始化  codeFirstPm:=0;--未进行初始化  --删除号源字典  delete from dict_queuecode b where b.queuename=p_queenName and b.codeparamtype=p_paramType and b.ofdepart=p_ofDepart;  FOR c_row IN paramDetails LOOP  --循环获取qs_param表中维护的某个时间段 loopNum:=1;  while loopNum <= c_row.codecount loop   if codeFirstAm=0 and c_row.calltype='上午' then      codeNum:=1;      codeFirstAm:=1;   end if;   if codeFirstPm=0 and c_row.calltype='下午' then      codeNum:=1;      codeFirstPm:=1;   end if;         insert into DICT_QUEUECODE t (t.queuename,t.codeparamtype,t.codevalue,t.timepart,t.hintinfo,t.OFDEPART,t.calltype,t.queueid)         values (c_row.queuename,c_row.paramtype,codeNum,c_row.begintimepart||'~'||c_row.endtimepart,'',c_row.ofdepart,c_row.calltype,p_queueId);         loopNum:=loopNum+1;         codeNum:=codeNum+1;       end loop;      END LOOP;     p_result:=1;    commit;   EXCEPTION     when others then       p_result:=-1;       rollback;  end;