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:
- In the Data Project Explorer, right-click
the PL/SQL Packages folder in a project, and
click . The New PL/SQL Package wizard
opens.
- 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.
- 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;
- 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;
- 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)