CODEWITHSUNDEEP
oracle-databasefunctionplsqlpackageprocedure
user6611026
user6611026

Reputation:

Does the PLSQL documentation say anything about order of subprograms into a package?

I am coding a package with many functions and procedures to work with the HR table. But I am not sure if there is any consideration about the order of the subprograms. And what happen if you compile a package and then create a new procedure in the middle of the existing package? Thanks.

CREATE OR REPLACE PACKAGE BODY empinfo_pkg IS

  FUNCTION emp_sal_1(
    p_empid employees.employee_id%TYPE,
    p_sal   employees.salary%TYPE)
  RETURN NUMBER
  IS
    v_incre_sal NUMBER(8,2);
  BEGIN
    SELECT SALARY * p_sal
    INTO   v_incre_sal
    FROM   employees
    WHERE  employee_id = p_empid;
    RETURN v_incre_sal;
  END emp_sal_1;

  PROCEDURE emp_basicinfo_1(
    p_empid employees.employee_id%TYPE)
  IS
    v_info VARCHAR2(200);
  BEGIN
    SELECT employee_id || ' ' || first_name || ' ' || last_name
    INTO   v_info
    FROM   employees
    WHERE  employee_id = p_empid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('NO EXISTE EMPLEADO CON ID INGRESADO');
  END emp_basicinfo_1;

  FUNCTION emp_comm_1(
    p_empid employees.employee_id%TYPE)
  RETURN NUMBER
  IS
    v_comm NUMBER(8,2);
  BEGIN
    SELECT commission_pct
    INTO   v_comm
    FROM   employees
    WHERE  employee_id = p_empid;
    RETURN v_comm;
  END emp_comm_1;

  PROCEDURE emp_allinfo_1(
    p_empid  IN  employees.employee_id%TYPE,
    p_refcur OUT SYS_REFCURSOR)
  IS
  BEGIN
    OPEN p_refcur
    FOR SELECT *
    FROM       employees
    WHERE      employee_id = p_empid;
  END emp_allinfo_1;

END empinfo_pkg;
/
SHOW ERRORS;

Upvotes: 0

Views: 75

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Oracle doesn't care what order you declare your procedures other than that you provide a signature before you call another method. That can be done either by making the method public and declaring it in the package specification, creating a forward declaration in the package body before it is called, or defining the method before it is called.

Say I wanted to have a method that wrote to a log table from within empinfo_pkg. I could declare that method in the package specification but that probably wouldn't make sense. A caller outside the package would never want to call this logging method since it was specific to logging things related to employees. I could either define the procedure in the package before the first time it is used, i.e.

  PROCEDURE log_employee_action( p_empid IN  employees.employee_id%TYPE,
                                 p_action IN varchar2(10))
  AS
  BEGIN
    <<do some logging>>
  END;

  PROCEDURE emp_allinfo_1(
    p_empid  IN  employees.employee_id%TYPE,
    p_refcur OUT SYS_REFCURSOR)
  IS
  BEGIN
    log_employee_action( p_empid, 'SELECT' );

    OPEN p_refcur
    FOR SELECT *
    FROM       employees
    WHERE      employee_id = p_empid;
  END emp_allinfo_1;

Or I could create a forward declaration and define it later

  -- A forward declaration with no implementation
  PROCEDURE log_employee_action( p_empid IN  employees.employee_id%TYPE,
                                 p_action IN varchar2(10));

  PROCEDURE emp_allinfo_1(
    p_empid  IN  employees.employee_id%TYPE,
    p_refcur OUT SYS_REFCURSOR)
  IS
  BEGIN
    log_employee_action( p_empid, 'SELECT' );

    OPEN p_refcur
    FOR SELECT *
    FROM       employees
    WHERE      employee_id = p_empid;
  END emp_allinfo_1;

  -- And an implementation later
  PROCEDURE log_employee_action( p_empid IN  employees.employee_id%TYPE,
                                 p_action IN varchar2(10))
  AS
  BEGIN
    <<write to log table>>
  END;

While Oracle doesn't much care what order things are declared in, future developers are likely to appreciate it if things are declared in a reasonably logical order. If you have utility functions that are going to be called from many different procedures, for example, it probably makes sense to declare those first rather than creating forward declarations and implementing those utility methods haphazardly throughout the code. It probably makes sense to group methods together in some sort of logical fashion. If you have a bunch of set_<<some attribute>> procedures, for example, it probably makes sense to put them all together rather than having them all throughout the package. Oracle won't care but a developer that is trying to figure out where set_first_name and set_last_name are defined will appreciate it if they are reasonably close to each other.

Upvotes: 4

Related Questions