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. 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 procedure or function in
a PL/SQL package, has the number of its parameters in parentheses:
For procedure, the engine just validate the parameter number and procedure name, does not care parameter data type whatever it is IN, IN OUT and OUT.
For function, the engine validate both the parameters data type, number and function name.
Function(1)
Function(2)
After you have created
the PL/SQL package, you can deploy and run the package.