Announcement of the variable in the procedure



  • Hello! Please tell me 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;
    


  • Apparently, you want count_orders to be a local variable, not a parameter of procedure. It should then be announced as follows:

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

    Local variables are announced 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; 
    

    The dates I prefer to check always in the way indicated above, because the use of functions (i.e. and extract) to the column slows the sample.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2