Understanding and Creating Functions in PL/SQL

1. Creating Functions

2. Objectives • After completing this lesson, you should be able to do the following: • Describe the uses of functions • Create client-side and server-side functions • Invoke a function • Remove a function • Differentiate between a procedure and a function

3. Overview of Stored Functions • A function is a named PL/SQL block that returns a value. • A function can be stored in the database, as a database object, for repeated execution. • A function can be called as part of an expression.

4. Syntax for Creating Functions CREATE [OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .) RETURN datatype IS|AS PL/SQL Block; The PL/SQL block must have at least one RETURN statement.

5. Oracle Procedure Builder Editor Code SQL*Plus Code 1 Save SQL> START file.sql 2 Source code Oracle Compile p-code Execute Creating a Function

6. Creating a Stored Function Using SQL*Plus • 1. Enter the text of the CREATE FUNCTION statement in a system editor or word processor and save it as a script file (.sql extension). • 2. From SQL*Plus, run the script file to compile the source code into p-code and store both in the database. 3. Invoke the function from an Oracle Server environment to determine whether it executes without error.

7. Creating a Stored Function Using SQL*Plus: Example SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /

8. Creating a Function Using Procedure Builder Procedure Builder allows you to: • Create a client-side function • Create a server-side function • Drag and drop functions between client and server

9. Creating Functions Using Procedure Builder: Example • Return the tax based on a specified value. FUNCTION tax (v_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (v_value * .08); END tax;

10. Executing Functions • Invoke a function as part of a PL/SQL expression. • Create a host variable to hold the returned value. • Execute the function. The host variable will be populated by the RETURN value.

11. SQL> START get_salary.sql Function created. SQL> VARIABLE g_salary number SQL> EXECUTE :g_salary := get_sal(7934) PL/SQL procedure successfully completed. SQL> PRINT g_salary G_SALARY ------------------ 1300 Executing Functions in SQL*Plus: Example Calling environment GET_SAL function v_id 7934 RETURN v_salary

12. Executing Functions in Procedure Builder: Example Calling environment TAX function • Display the tax based on a specified value. v_value 1000 RETURN (computed value) PL/SQL> .CREATE NUMBER x PRECISION 4 PL/SQL> :x := tax(1000); PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x)); 80

13. Advantages of User-Defined Functions in SQL Expressions • Extend SQL where activities are too complex, too awkward, or unavailable with SQL • Can increase efficiency, by using them in the WHERE clause to filter data, as opposed to filtering the data in the application • Can manipulate character strings

14. Locations to Call User-Defined Functions • Select list of a SELECT command • Condition of the WHERE and HAVING clauses • CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses • VALUES clauses of the INSERT command • SET clause of the UPDATE command

15. Calling Functions from SQL Expressions: Restrictions • A user-defined function must be a stored function. • A user-defined function must be a ROW function, not a GROUP function. • A user-defined function only takes IN parameters, not OUT, or IN OUT. • Datatypes must be valid SQL datatypes, CHAR, DATE, or NUMBER. • Datatypes cannot be PL/SQL types such as BOOLEAN, RECORD, or TABLE.

16. Calling Functions from SQL Expressions: Restrictions • INSERT, UPDATE, or DELETE commands are not allowed. • Calls to subprograms that break the above restriction are not allowed.

17. Removing Functions • Using SQL*Plus: Drop a server-side function. • Using Procedure Builder: • Drop a server-side function. • Delete a client-side function.

18. Removing a Server-SideFunction • Using SQL*Plus • Syntax • Example DROP FUNCTION function_name SQL> DROP FUNCTION get_sal; Function dropped.

19. Removing Server-Side Functions • Using Procedure Builder • 1. Connect to the database. • 2. Expand the Database Objects node. • 3. Expand the schema of the owner of the function. • 4. Expand the Stored Program Units node. • 5. Click the function you want to drop. • 6. Click Delete in the Object Navigator. • 7. Click Yes to confirm.

20. Removing a Client-Side Function • Using Procedure Builder • 1. Expand the Program Units node. • 2. Click the function you want to remove. • 3. Click Delete in the Object Navigator. • 4. Click Yes to confirm.

21. Function Procedure IN argument IN argument OUT argument IN OUT argument (DECLARE) BEGIN EXCEPTION END; (DECLARE) BEGIN EXCEPTION END; Procedure or Function? Calling Environment Calling Environment

22. Comparing Procedures and Functions Procedure Execute as a PL/SQL statement No RETURN datatype Can return none, one or many values Function Invoke as part of an expression Must contain a RETURN datatype Must return a single value

23. Benefits of Stored Procedures and Functions • Improved performance • Improved maintenance • Improved data security and integrity

24. Summary • A function is a named PL/SQL block that must return a value. • A function is invoked as part of an expression. • A stored function can be called in SQL statements.

25. Practice Overview • Creating stored functions • Invoking a stored function from a SQL statement • Invoking a stored function from a stored procedure