oracle存储过程

create or replace PROCEDURE PRO_SEARCH_NOT_CHECKIN
(
  P_USER_ROLE IN VARCHAR2, -- 用户角色
 P_CHECKIN_DATE IN NUMBER, -- 考勤日期，格式yyyy-mm-dd
  P_OP_TIME in number
) AS
CURSOR cur_not_checkin_all_day is -- 查询全天都没有签到签退的员工信息列表
 select t1.user_id,t1.user_name,t1.USER_DEPT_ID
 from bst_user t1,bst_user_rule t2
 where t1.user_id = t2.user_id and t2.rule_id = P_USER_ROLE
 and not EXISTS(select 1 from SLT_CHECKIN t3 where t1.user_id = t3.user_id and t3.FIELD2 = P_CHECKIN_DATE)
 and rownum <5;
 
 CURSOR cur_not_checkin_afternoon is -- 下午没有签退的员工信息列表
 select t1.check_id,t2.user_id,t2.user_name,t2.USER_DEPT_ID
 from SLT_CHECKIN t1,bst_user t2 where t1.user_id = t2.user_id
 and t1.is_checkout = 0 and t1.field2 = P_CHECKIN_DATE and rownum < 5;
 
 cur_not_checkin_all_day_type cur_not_checkin_all_day%ROWTYPE;
 cur_not_checkin_afternoon_type cur_not_checkin_afternoon%ROWTYPE;
 v_user_id varchar2(50);
 v_dept_id varchar2(50);
 v_user_name varchar2(50);
 v_config_id varchar2(50);
 v_check_id varchar2(50);
 v_check_excep_id1 varchar2(50);
 v_check_excep_id2 varchar2(50);
 v_confirm_user_id varchar2(50);
 
BEGIN
  open cur_not_checkin_all_day;
  loop
  fetch cur_not_checkin_all_day into cur_not_checkin_all_day_type;
  exit when cur_not_checkin_all_day%NOTFOUND;
  v_check_id :=  SYS_GUID();
  v_user_id := cur_not_checkin_all_day_type.user_id;
  v_dept_id := cur_not_checkin_all_day_type.user_dept_id;
 
  /**config_id */

<!--            为了保险起见，可以先查询是否存在              -->
  select cfg.CONFIG_ID into v_config_id  from cit_pro_user cpu
  left join cit_project cp on cpu.pro_id = cp.pro_id
  left join slt_check_config cfg on cp.check_config_id = cfg.config_id
  where cpu.user_id = v_user_id;
 
 /**插入考勤数据*/
 insert into slt_checkin(CHECK_ID,USER_ID,CONFIG_ID,IS_CHECKIN,CHECKIN_TYPE,IS_ASKOFF,IS_CHECKOUT,CHECKOUT_TYPE,CHECKIN_TIME,OP_TIME)
 values
 (v_check_id,v_user_id,v_config_id,0,2,0,0,2,P_CHECKIN_DATE,P_OP_TIME);
 
 select t1.user_id into v_confirm_user_id  from bst_user t1,bst_organ t2,bst_user_rule t3
 where t1.user_dept_id = t2.org_id and t1.user_id = t3.user_id
 and t2.org_id = v_dept_id  and t3.rule_id = 'KQ_BMZR';
 
 /**插入上午考勤异常数据*/
 v_check_excep_id1 := SYS_GUID();
 insert into SLT_CHECK_EXCEPTION(CHECK_EXCEP_ID,USER_ID,CHECK_ID,org_Id,confirm_State,
 confirm_User_Id,time_Excep,time_Excep_Type,create_Time,field1,field2,field3)
 values(v_check_excep_id1,v_user_id,v_check_id,v_dept_id,'0',v_confirm_user_id,'0','9',p_op_time,'checkin',to_char(P_CHECKIN_DATE),0);
 
 /**插入下午考勤异常数据*/
  v_check_excep_id2 := SYS_GUID();
  v_check_excep_id1 := SYS_GUID();
 insert into SLT_CHECK_EXCEPTION(CHECK_EXCEP_ID,USER_ID,CHECK_ID,org_Id,confirm_State,
 confirm_User_Id,time_Excep,time_Excep_Type,create_Time,field1,field2,field3)
 values(v_check_excep_id2,v_user_id,v_check_id,v_dept_id,'0',v_confirm_user_id,'0','9',p_op_time,'checkout',to_char(P_CHECKIN_DATE),0);
 end loop;
 close cur_not_checkin_all_day;
 
 open  cur_not_checkin_afternoon;
 loop
   fetch cur_not_checkin_afternoon into cur_not_checkin_afternoon_type;
   exit when cur_not_checkin_afternoon%NOTFOUND;
   v_check_id :=  cur_not_checkin_afternoon_type.check_id;
   v_user_id := cur_not_checkin_afternoon_type.user_id;
   v_dept_id := cur_not_checkin_afternoon_type.user_dept_id;
   v_check_excep_id1 := SYS_GUID();
  
  /**config_id */
  select cfg.CONFIG_ID into v_config_id  from cit_pro_user cpu
  left join cit_project cp on cpu.pro_id = cp.pro_id
  left join slt_check_config cfg on cp.check_config_id = cfg.config_id
  where cpu.user_id = v_user_id;
 
   select t1.user_id into v_confirm_user_id  from bst_user t1,bst_organ t2,bst_user_rule t3
   where t1.user_dept_id = t2.org_id and t1.user_id = t3.user_id
   and t2.org_id = v_dept_id  and t3.rule_id = 'KQ_BMZR';
 
  /**插入下午考勤异常记录*/
   insert into SLT_CHECK_EXCEPTION(CHECK_EXCEP_ID,USER_ID,CHECK_ID,org_Id,confirm_State,
   confirm_User_Id,time_Excep,time_Excep_Type,create_Time,field1,field2,field3)
   values(v_check_excep_id1,v_user_id,v_check_id,v_dept_id,'0',v_confirm_user_id,'0','9',p_op_time,'checkout',to_char(P_CHECKIN_DATE),0);
 
 end loop;
 close cur_not_checkin_afternoon;
END;