最近使用存储过程和游标的联合使用,由于有些生疏所以记录一下
存储过程游标书写如下:
CREATE OR REPLACE procedure SGKQ.SCHEDULE_RECORD_PRO is
/**
*
* 描述:查询员工时间段内连续五天及以上未打卡的员工信息,
查找出员工号,员工user_name,连续未打卡的最后日期,连续未打卡的天数
**/
--定义变量
v_numA number;--声明变量用于接收连续未打开天数
v_numB number;
v_record_count number;--声明变量用于接收查询的考勤条数
v_code varchar2(100);
v_username varchar2(200);
v_workdate varchar2(37);
v_workdate_temp varchar2(37);
--查询员工游标
CURSOR HAO_STAFF_CURSOR IS SELECT CODE,USER_NAME FROM HAO_STAFF WHERE EMPLOYEE_STATUS = '2'AND DEL_FLAG = '0' AND ORG_NAME LIKE '%商管集团-各区域公司%' and code NOT IN(10086);
--查询排班游标
CURSOR SCHEDULE_MANAGER_CURSOR IS SELECT STAFF_CODE ,WORK_DATE FROM SCHEDULE_MANAGER_DETAIL WHERE WORK_DATE<'20180320' AND WORK_DATE>'20180120' AND DEL_FLAG='0'and WORK_STATION NOT IN ('009') AND STAFF_CODE=v_code ORDER BY STAFF_CODE ,WORK_DATE ASC;
BEGIN
--解决控制台输出长度限制报错
--循环员工
FOR V_HAOSTAFF IN HAO_STAFF_CURSOR LOOP
v_numA := 0;
v_numB := 0;
v_code := V_HAOSTAFF."CODE";--把code赋值给v_code
v_username := V_HAOSTAFF."USER_NAME";
--循环排班
FOR V_SCHEDULE_MANAGER IN SCHEDULE_MANAGER_CURSOR LOOP
v_record_count := 0;
v_workdate := V_SCHEDULE_MANAGER.WORK_DATE;
--查询考勤(查询出来的条数通过into赋值给v_record_count)
SELECT COUNT(*) INTO v_record_count FROM DEV_RECORD WHERE TS_TIME <= to_date((select to_char(concat(v_workdate,' 23:59:59')) from DUAL), 'yyyy-MM-dd HH24:mi:ss') AND TS_TIME >= to_date((select to_char(concat(v_workdate,' 00:00:00')) from DUAL), 'yyyy-MM-dd HH24:mi:ss') AND SZ_UID=v_username;
IF v_rocord_count = 0 THEN
v_numA := v_numA+1;
IF v_numA >= 5 THEN
v_workdate_temp := v_workdate;
v_numB := v_numA;
END IF;
ELSE
v_numA := 0;
END IF;
END LOOP;
--保存五天缺勤人员信息
IF v_numB >= 5 THEN
--保存五天未打开的员工信息
COMMIT;
END IF;
END LOOP;
end SCHEDULE_RECORD_PRO;
通过实践,明白了存储过程和游标的联合使用,以后使用的会更加的得心应手!