SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Creating Functions

ova avatar
ova
20 diapositives · 286 Views · Oct 22, 2014

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.

Creating Functions - slide 1
01 / 20Creating Functions
1 / 17

Télécharger la présentation

Creating Functions
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  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.