You can create PL/SQL user-defined functions in a PL/SQL
package using the New PL/SQL Package wizard.
Procedure
To create a PL/SQL user-defined function from a data
development project:
- In the Data Project Explorer, right-click the PL/SQL
Packages folder schema, 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 function name and variables.
For example:
CREATE OR REPLACE PACKAGE emp_admin
IS
...
FUNCTION SampleFunction RETURN integer;
...
END emp_admin;
- Click the Body tab and edit the PL/SQL package body, adding
the function. For example
--
-- Package body for the 'emp_admin' package.
--
CREATE OR REPLACE PACKAGE BODY emp_admin AS
FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
deptno NUMBER) RETURN NUMBER IS
END;
- Save the package.
Results
By default, creating the package does not register it on
the database. To register the package on the database, you must deploy
it.
A PL/SQL UDF 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. An overloaded procedure or function in a PL/SQL package,
has the number of its parameters in parentheses:
Function(1)
Function(2)
After you have created the PL/SQL package,
you can deploy and run the package.