1 / 20

Creating Functions

Creating Functions. 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.

ova
Download Presentation

Creating Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

  5. Oracle Procedure Builder Creating a Function Edit Textfile Systemeditor 1 Store in database Sourcecode 2 Compile p-code Execute

  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. 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.

  10. SQL> START get_salary.sql Procedure 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

  11. Advantages of User-Defined Functions in SQL Expressions • Extend SQL where activities are too complex, too awkward, or unavailable with SQL • Query efficiency: functions used in the WHERE clause can filter data • Manipulate character strings • Provide parallel query execution

  12. 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

  13. 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 CHAR, DATE, or NUMBER, not PL/SQL types such as BOOLEAN, RECORD, or TABLE. • Return type must be an Oracle Server internal type.

  14. 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.

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

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

  17. 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

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

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

  20. 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.

More Related