Hello! Please tell count_orders how to correctly declare the variable count_orders in the procedure below:

  CREATE OR REPLACE PROCEDURE INCREASE_EMPLOYEE_SALARY ( EMP_ID IN NUMBER, count_orders int := 0 ) AS BEGIN SELECT COUNT(order_id) INTO count_orders FROM orders WHERE sales_rep_id = emp_id AND EXTRACT(YEAR FROM order_date) = 2000; IF count_orders > 0 THEN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id; END IF; END INCREASE_EMPLOYEE_SALARY;

    1 answer 1

    Apparently you want count_orders to be a local variable, not a procedure parameter. Then it should be declared like this:

     CREATE OR REPLACE PROCEDURE INCREASE_EMPLOYEE_SALARY ( EMP_ID IN NUMBER ) AS count_orders int := 0; BEGIN ...

    Those. local variables are declared after the word AS .

    But in this particular example, you don’t need a variable at all. For the request could be formulated as follows:

     UPDATE employees SET salary = salary * 1.1 WHERE exists(SELECT 1 FROM orders WHERE sales_rep_id = emp_id AND order_date between TO_DATE('01012000','DDMMYYYY') and TO_DATE('31122000','DDMMYYYY') ) AND employee_id = emp_id;

    I prefer to check the dates always in the manner indicated above, because the use of functions (including extract) to the column slows down the selection.