ORACLE SQL PROCEDURE WITH FOR LOOP AND IF ELSE ERROR

Question

I need to replace employee id in shift table for a particular day with another employee id who hasn't any shift for a particular day. This my tables,

CREATE TABLE EMPLOYEE
        (EMPLOYEE_ID NUMBER(4) NOT NULL,
        NAME VARCHAR(50),
        GENDER VARCHAR(10), 
        BIRTH_DAY DATE,
        PRIMARY KEY(EMPLOYEE_ID)        
        );
CREATE TABLE SHIFT
        (SHIFT_ID NUMBER(4),
        SHIFT_DATE DATE,
        CUSTOMER_ID NUMBER(4),
        SERVICE_ID NUMBER(4),
        EMPLOYEE_ID  NUMBER(4),
        SHIFT_CHARGE FLOAT(10), 
        PRIMARY KEY(SHIFT_ID),
        FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)  
        );

For the above purpose, I want to create a procedure. I created the procedure is below. But there is a compilation error.

CREATE OR REPLACE PROCEDURE CHANGE_SHIFT_SHEDULED
 (
    EMPLOYEE_ID IN SHIFT.EMPLOYEE_ID%TYPE,
    SHIFT_DATE IN SHIFT.SHIFT_DATE%TYPE
 )
 AS
    CHECK_CON BOOLEAN:=TRUE;
 BEGIN
    FOR SICK_EMP_SHIFT_REC IN (SELECT * FROM SHIFT WHERE SHIFT.EMPLOYEE_ID=EMPLOYEE_ID AND SHIFT.SHIFT_DATE=SHIFT_DATE)
    LOOP
        FOR EMP_REC IN (
                SELECT e.EMPLOYEE_ID,COUNT(e.EMPLOYEE_ID)AS SHIFT_COUNT,
                CASE
                    WHEN SHIFT_COUNT>0 THEN FALSE
                    ELSE TRUE
                END AS SHIFT_COUNT
                FROM EMPLOYEE e LEFT OUTER JOIN SHIFT s ON e.EMPLOYEE_ID=s.EMPLOYEE_ID 
                WHERE s.SHIFT_DATE=SHIFT_DATE 
                GROUP BY e.EMPLOYEE_ID)
        LOOP
            CHECK_CON:=EMP_REC.SHIFT_COUNT;
            IF CHECK_CON THEN
                UPDATE SHIFT s SET s.EMPLOYEE_ID=EMP_REC.EMPLOYEE_ID WHERE s.SHIFT_ID=SICK_EMP_SHIFT_REC.SHIFT_ID;          
                EXIT;
            END IF;
        END LOOP;
    END LOOP;
 END;
 /

Please anyone help me to solve this issue.

Littlefoot · Accepted Answer · 2020-05-30 15:31:59Z

1

I'm not looking at logic you applied; I hope you did it right. I just fixed errors which prevented code you wrote to compile; for example:

don't name parameters as column names; use some prefix, e.g. par_ or p_
in cursor FOR loop, you can't have two columns with same name (shift_count); one has to be changed
also, you can't use an alias in the same statement's CASE (which is what you did with shift_count)
Boolean support is somewhat strange in Oracle. For example, there's no such datatype at SQL level. Therefore, you'll have to work with strings or numbers, and then "convert" them to Boolean (e.g. line #26)

The rest is OK, I suppose.

SQL> CREATE OR REPLACE PROCEDURE change_shift_sheduled (
  2     par_employee_id  IN shift.employee_id%TYPE,
  3     par_shift_date   IN shift.shift_date%TYPE)
  4  AS
  5     check_con  BOOLEAN := TRUE;
  6  BEGIN
  7     FOR sick_emp_shift_rec IN (SELECT *
  8                                  FROM shift
  9                                 WHERE     shift.employee_id = par_employee_id
 10                                       AND shift.shift_date = par_shift_date)
 11     LOOP
 12        FOR emp_rec
 13           IN (  SELECT e.employee_id,
 14                        COUNT (e.employee_id) AS shift_count,
 15                        CASE
 16                           WHEN COUNT (e.employee_id) > 0 THEN 'FALSE'
 17                           ELSE 'TRUE'
 18                        END
 19                           AS shift_count_case
 20                   FROM employee e
 21                        LEFT OUTER JOIN shift s ON e.employee_id = s.employee_id
 22                  WHERE s.shift_date = par_shift_date
 23               GROUP BY e.employee_id)
 24        LOOP
 25           check_con :=
 26              CASE
 27                 WHEN emp_rec.shift_count_case = 'FALSE' THEN FALSE
 28                 ELSE TRUE
 29              END;
 30
 31           IF check_con
 32           THEN
 33              UPDATE shift s
 34                 SET s.employee_id = emp_rec.employee_id
 35               WHERE s.shift_id = sick_emp_shift_rec.shift_id;
 36
 37              EXIT;
 38           END IF;
 39        END LOOP;
 40     END LOOP;
 41  END;
 42  /

Procedure created.

SQL>

answered May 30, 2020 at 15:31

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madhushani Hewagama Over a year ago

thank you soo much, now compiled it. you saved my time lot. thank u again.

Littlefoot Over a year ago

You're welcome. As you were already told: on Stack Overflow, "thank you" is best expressed by upvoting and/or accepting answers that help you.

Collectives™ on Stack Overflow

ORACLE SQL PROCEDURE WITH FOR LOOP AND IF ELSE ERROR

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related