Tutorial: Creating a Standalone Stored Function

To create a standalone stored function, use either the SQL Developer tool Create PL/SQL Function or the DDL statement CREATE FUNCTION.

This tutorial shows how to use the Create PL/SQL Function tool to create a standalone stored function named calculate_score that has three parameters and returns a value of type NUMBER.

To create a standalone stored function using Create PL/SQL Function tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Functions.

   A list of choices appears.

3. Click New Function.

   The Create PL/SQL Function window opens. It looks like the Create PL/SQL Procedure window (see "Tutorial: Creating a Standalone Stored Procedure") except that its Parameters pane has a row for the value that the function returns. In that row, the value of Name is <Return> and the default value of Type is VARCHAR2.

4. For Schema, accept the default value, HR.

5. For Name, change FUNCTION1 to calculate_score.

6. In the Parameters pane, in the Type field of the only row, select NUMBER from the drop-down list.

7. Click the icon Add Column.

   A row appears under the column headings. Its fields have these default values: Name, param1; Type, VARCHAR2; Mode, IN; Default Value, empty.

8. For Name, change param1 to cat.

9. For Type, accept the default, VARCHAR2.

10. For Mode, accept the default value, IN.

11. Leave Default Value empty.

12. Add a second parameter by repeating steps 7 through 11 with the Name score and the Type NUMBER.

13. Add a third parameter by repeating steps 7 through 11 with the Name weight and the Type NUMBER.

14. Click OK.

    The calculate_score pane opens, showing the CREATE FUNCTION statement that created the function.

    Because the only statement in the execution part of the function is RETURN NULL, the function does nothing.

15. Replace NULL with score * weight.

    The title of the calculate_score pane is in italic font, indicating that the function is not yet saved in the database.

16. Select Save from the File menu.

    Oracle Database compiles the function and saves it. The title of the calculate_score pane is no longer in italic font.