关于if语句嵌套的问题,到底应该怎么写呢。
CREATE OR REPLACE PROCEDURE PRO_CHECK(
V_ID IN NUMBER,
V_DATE IN NUMBER)
IS
CURSOR CHECK_CURSOR IS
SELECT CHECK_IN,CHECK_OUT FROM T_CHECK
WHERE EMPLOYEE_ID=V_ID AND TO_CHAR(CHECK_IN,'MM')=V_DATE;
V_CHECKIN T_CHECK.CHECK_IN%TYPE;
V_CHECKOUT T_CHECK.CHECK_OUT%TYPE;
V_CHIDAO NUMBER:=0;
V_ZAOTUI NUMBER:=0;
V_KUANGBAN NUMBER:=0;
V_KUANGYI NUMBER:=0;
V_ZHUNSHI NUMBER:=0;
V_WEEKDAY NUMBER;
V_INT NUMBER:=1;
BEGIN
OPEN CHECK_CURSOR;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
WHILE V_INT<TO_NUMBER(TO_CHAR(LAST_DAY(V_CHECKIN),'DD')) LOOP
IF V_INT=TO_NUMBER(TO_CHAR(V_CHECKIN,'DD')) THEN
IF TO_NUMBER(TO_CHAR(to_date(TO_CHAR(V_CHECKIN,'YYYY')||' '||TO_CHAR(V_CHECKIN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=1 OR
TO_NUMBER(TO_CHAR(to_date(TO_CHAR(V_CHECKIN,'YYYY')||' '||TO_CHAR(V_CHECKIN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=7
THEN V_INT:=V_INT+1;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
CONTINUE;
ELSE
IF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <=0 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>=0
THEN V_ZHUNSHI:=V_ZHUNSHI+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS')>0 AND
TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <0.0208333333333333 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>=0
THEN V_CHIDAO:=V_CHIDAO+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <=0 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>-0.0208333333333333 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS') <0
THEN V_ZAOTUI:=V_ZAOTUI+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') >=0.0208333333333333
OR TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')<=-0.0208333333333333
THEN V_KUANGBAN:=V_KUANGBAN+1;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
ELSE V_KUANGYI:=V_KUANGYI+1;
END IF;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
END IF;
ELSE
IF TO_NUMBER(TO_CHAR(to_date(TO_CHAR(CHECK_IN,'YYYY')||' '||TO_CHAR(CHECK_IN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=1 OR
TO_NUMBER(TO_CHAR(to_date(TO_CHAR(CHECK_IN,'YYYY')||' '||TO_CHAR(CHECK_IN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=7
THEN
V_INT:=V_INT+1;
CONTINUE;
ELSE V_INT:=V_INT+1;
V_KUANGYI:=V_KUANGYI+1;
CONTINUE;
END IF;
END IF;
END LOOP;
CLOSE CHECK_CURSOR;
DBMS_OUTPUT.PUT_LINE('月份'||' '||V_DATE);
DBMS_OUTPUT.PUT_LINE('迟到'||''||V_CHIDAO);
DBMS_OUTPUT.PUT_LINE('早退'||' '||V_ZAOTUI);
DBMS_OUTPUT.PUT_LINE('旷工半天'||' '||V_KUANGBAN);
DBMS_OUTPUT.PUT_LINE('旷工一天'||' '||V_KUANGYI);
DBMS_OUTPUT.PUT_LINE('员工ID'||' '||V_ID);
END;
创建进程一直显示红叉却不报错哪行写错了=自己找了半天也不知道错在哪。
CREATE OR REPLACE PROCEDURE PRO_CHECK(
V_ID IN NUMBER,
V_DATE IN NUMBER)
IS
CURSOR CHECK_CURSOR IS
SELECT CHECK_IN,CHECK_OUT FROM T_CHECK
WHERE EMPLOYEE_ID=V_ID AND TO_CHAR(CHECK_IN,'MM')=V_DATE;
V_CHECKIN T_CHECK.CHECK_IN%TYPE;
V_CHECKOUT T_CHECK.CHECK_OUT%TYPE;
V_CHIDAO NUMBER:=0;
V_ZAOTUI NUMBER:=0;
V_KUANGBAN NUMBER:=0;
V_KUANGYI NUMBER:=0;
V_ZHUNSHI NUMBER:=0;
V_WEEKDAY NUMBER;
V_INT NUMBER:=1;
BEGIN
OPEN CHECK_CURSOR;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
WHILE V_INT<TO_NUMBER(TO_CHAR(LAST_DAY(V_CHECKIN),'DD')) LOOP
IF V_INT=TO_NUMBER(TO_CHAR(V_CHECKIN,'DD')) THEN
IF TO_NUMBER(TO_CHAR(to_date(TO_CHAR(V_CHECKIN,'YYYY')||' '||TO_CHAR(V_CHECKIN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=1 OR
TO_NUMBER(TO_CHAR(to_date(TO_CHAR(V_CHECKIN,'YYYY')||' '||TO_CHAR(V_CHECKIN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=7
THEN V_INT:=V_INT+1;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
CONTINUE;
ELSE
IF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <=0 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>=0
THEN V_ZHUNSHI:=V_ZHUNSHI+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS')>0 AND
TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <0.0208333333333333 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>=0
THEN V_CHIDAO:=V_CHIDAO+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') <=0 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')>-0.0208333333333333 AND
TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS') <0
THEN V_ZAOTUI:=V_ZAOTUI+1;
ELSIF TO_DATE(TO_CHAR(V_CHECKIN,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('9:00:00','HH24:MI:SS') >=0.0208333333333333
OR TO_DATE(TO_CHAR(V_CHECKOUT,'HH24:MI:SS'),'HH24:MI:SS')-TO_DATE('17:00:00','HH24:MI:SS')<=-0.0208333333333333
THEN V_KUANGBAN:=V_KUANGBAN+1;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
ELSE V_KUANGYI:=V_KUANGYI+1;
END IF;
FETCH CHECK_CURSOR INTO V_CHECKIN,V_CHECKOUT;
END IF;
ELSE
IF TO_NUMBER(TO_CHAR(to_date(TO_CHAR(CHECK_IN,'YYYY')||' '||TO_CHAR(CHECK_IN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=1 OR
TO_NUMBER(TO_CHAR(to_date(TO_CHAR(CHECK_IN,'YYYY')||' '||TO_CHAR(CHECK_IN,'MM')||' '||V_INT,'YYYY MM DD'),'D'))=7
THEN
V_INT:=V_INT+1;
CONTINUE;
ELSE V_INT:=V_INT+1;
V_KUANGYI:=V_KUANGYI+1;
CONTINUE;
END IF;
END IF;
END LOOP;
CLOSE CHECK_CURSOR;
DBMS_OUTPUT.PUT_LINE('月份'||' '||V_DATE);
DBMS_OUTPUT.PUT_LINE('迟到'||''||V_CHIDAO);
DBMS_OUTPUT.PUT_LINE('早退'||' '||V_ZAOTUI);
DBMS_OUTPUT.PUT_LINE('旷工半天'||' '||V_KUANGBAN);
DBMS_OUTPUT.PUT_LINE('旷工一天'||' '||V_KUANGYI);
DBMS_OUTPUT.PUT_LINE('员工ID'||' '||V_ID);
END;
创建进程一直显示红叉却不报错哪行写错了=自己找了半天也不知道错在哪。