当前位置:我的异常网» Oracle认证考试 » oracle存储里提交更新然后读取更新的结果
oracle存储里提交更新然后读取更新的结果
www.myexceptions.net 网友分享于:2013-12-18 浏览:2次
oracle储存里提交更新然后读取更新的结果
CREATE OR REPLACE PROCEDURE serv_busi_automatic (
v_flag OUT NUMBER ) IS
v_irows2 NUMBER;
v_irows3 NUMBER;
v_irows4 NUMBER;
v_irows5 NUMBER;
v_user_id VARCHAR2(30);
v_user_name VARCHAR2(30);
v_dept_id VARCHAR2(30);
v_dept_name VARCHAR2(30);
v_deal_user VARCHAR(30);
v_threshold NUMBER;
CURSOR v_ordersub_cursor IS
SELECT busiid,
busitypeid,
proc_user,
status,
demand_time,
orderid,
begin_time,
companyid
FROM serv_busi
WHERE deal_status = '0'
AND (if_order = 0 OR
(if_order = 1 AND
TO_CHAR(order_time, 'yyyymmdd') = TO_CHAR(SYSDATE, 'yyyymmdd'))) FOR UPDATE;
BEGIN
v_flag := -1;
FOR v_cur IN v_ordersub_cursor LOOP
v_flag := 0;
v_threshold :=0;
if v_cur.status = 1 then
v_cur.status:=2 ;
end if ;
BEGIN --获取在线、当前状态为1、且具备相应技能的坐席、待办工单数未超过阀值
SELECT u.user_id, u.user_name,d.deptid,d.dept_name
INTO v_user_id, v_user_name,v_dept_id,v_dept_name
FROM base_user_ext u, pub_online o, pub_users p,base_dept d
WHERE u.user_id = o.user_id
AND u.user_id = p.user_id
AND p.e_mail = d.deptid
AND u.companyid = v_cur.companyid
AND (p.e_mail = 'CU' OR p.e_mail='CM')
AND u.cur_status = 1
AND INSTR(busi_type, SUBSTR(v_cur.busitypeid, 1, 6)) <> 0
AND ((SELECT COUNT(*)
FROM serv_busi b
WHERE b.proc_user = u.user_id
文章评论