oracle存储过程、游标参考实例
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
博客内容主要用于日常学习记录内容比较随意如有问题还需谅解
oracle存 储 过 程 、 游 标 参 考 实 例
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
END LOOP; p_result:=1; commit; EXCEPTION when others then
p_result:=-1; rollback; 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;
oracle存 储 过 程 、 游 标 参 考 实 例
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
END LOOP; p_result:=1; commit; EXCEPTION when others then
p_result:=-1; rollback; 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;