Creating stored procedures in a PL/SQL package

You can create PL/SQL stored procedures in a PL/SQL package using the New PL/SQL Package wizard.

Before you begin

Ensure the following requirements are met:

About this task

With the New PL/SQL Package wizard, you specify PL/SQL as the stored procedure language and select a template. Then you can edit the package specification and body in the routine editor.

Procedure

To create a stored procedure in a PL/SQL package:

  1. In the Data Project Explorer, right-click the PL/SQL Packages folder in a project, and click New > PL/SQL Package. The New PL/SQL Package wizard opens.
  2. Complete the steps of the wizard.

    The wizard creates the PL/SQL package and adds it to the PL/SQL Packages folder; and the PL/SQL package specification opens in the Routine Editor.

  3. In the specification, add the stored procedure name and variables. For example:
    CREATE OR REPLACE PACKAGE emp_admin
    IS
    
       ...
       PROCEDURE hire_emp (
          p_empno         NUMBER,
          p_ename         VARCHAR2,
          p_job           VARCHAR2,
          p_sal           NUMBER,
          p_hiredate      DATE DEFAULT sysdate,
          p_comm          NUMBER DEFAULT 0,
          p_mgr           NUMBER,
          p_deptno        NUMBER DEFAULT 10
       );
       ...
    END emp_admin;
  4. Click the Body tab and edit the PL/SQL package body, to add the stored procedure. For example
    --
    --  Package body for the 'emp_admin' package.
    --
    CREATE OR REPLACE PACKAGE BODY emp_admin
    IS
       --
       ...
       --  Procedure that inserts a new employee record into the 'emp' table.
       --
       PROCEDURE hire_emp (
          p_empno         NUMBER,
          p_ename         VARCHAR2,
          p_job           VARCHAR2,
          p_sal           NUMBER,
          p_hiredate      DATE    DEFAULT sysdate,
          p_comm          NUMBER  DEFAULT 0,
          p_mgr           NUMBER,
          p_deptno        NUMBER  DEFAULT 10
       )
       AS
       BEGIN
          INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno)
             VALUES(p_empno, p_ename, p_job, p_sal,
                    p_hiredate, p_comm, p_mgr, p_deptno);
       END;
    	 ...
       --
       END;
  5. Save the package.
    A PL/SQL stored procedure can be overloaded only if all routines with the same name and type are in the same PL/SQL package. In a PL/SQL package, a procedure or function can be overloaded by another procedure or function of the same type and name, but with a different number of parameters. A function in a package can be overloaded by another function with same number of parameters if the data type of one of the parameters is different. An overloaded package or function in a PL/SQL package, shows the number of its parameters in parentheses:
    Procedure02(1)
    Procedure02(2)

Feedback