1 / 21

Procedures & Functions Oracle Database PL/SQL 10g Programming

Procedures & Functions Oracle Database PL/SQL 10g Programming. Chapter 8. Procedures & Functions. Functions Procedures Forward Referencing Parameter Mode Positional versus Named Notation Call Statement Package Specifications Package Bodies. Procedures & Functions Function: Definition.

madonna-holman
Download Presentation

Procedures & Functions Oracle Database PL/SQL 10g Programming

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. Procedures & FunctionsOracle Database PL/SQL 10g Programming Chapter 8

  2. Procedures & Functions • Functions • Procedures • Forward Referencing • Parameter Mode • Positional versus Named Notation • Call Statement • Package Specifications • Package Bodies Oracle Database PL/SQL 10g Programming (Chapter 8)

  3. Procedures & FunctionsFunction: Definition • Functions are black boxes: • Taking values in to process them. • Returning a single value as a scalar or compound variable to the left operand of an assignment, or as a column value in a SQL statement (single value is misleading because a compound variable can be a collection of a structure). • Function parameters: • Are copies of variables, known as pass-by-value parameters. • Are internally managed as a unidirectional reference, which means a reference is passed that disallows changes to the referenced value. • Can not use the NOCOPY hint without raising a compilation error. Oracle Database PL/SQL 10g Programming (Chapter 8)

  4. Procedures & FunctionsFunction: Prototype CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [IN] data_type [:= null_default1] [,parameter2 [IN] data_type [:= null_default2] [,parameter(n+1) [IN] data_type [:= null_default(n+1)])]]] RETURN return_data_type [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} declaration_statements; BEGIN execution_statements; RETURN return_data_variable; END function_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  5. Procedures & FunctionsProcedure: Definition • Procedures are black boxes: • Taking values in to process them. • Returning any parameter as a scalar or compound variable to the calling scope of the procedure. • Cannot be used as the right operand of an assignment because they return no variable as an output, which means a void type in many other programming languages. • Procedure parameters: • Are references to variables, known as pass-by-reference parameters. • Are internally managed as a unidirectional or bidirectional references; the former means a reference is passed that disallows changes to the referenced value and the latter allows changes to the value. • Can use the NOCOPY hint without raising a compilation error provided the variables are designated as bidirectional references. Oracle Database PL/SQL 10g Programming (Chapter 8)

  6. Procedures & FunctionsProcedure: Prototype CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [IN [OUT]] data_type [:= null_default1] [,parameter2 [IN [OUT]] data_type [:= null_default2] [,parameter(n+1) [IN [OUT]] data_type [:= null_default(n+1)])]]] [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} declaration_statements; BEGIN execution_statements; END procedure_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  7. Procedures & FunctionsForward Referencing: Definition • Forward Referencing is how compilers parse programs: • A single pass compiler only reads the file once, which means all items must appear in logical sequence. • A double pass compiler reads the file twice, which means a forward reference can be managed without providing a header definition or declared but undefined variable. • The PL/SQL parse is a single pass compiler: • Types, variables, local functions, local procedures, and cursors must be declared before referencing them in the program. • Local functions and procedures are declared by providing a header signature as a stub before they are called in another subroutine. Oracle Database PL/SQL 10g Programming (Chapter 8)

  8. Procedures & FunctionsForward Referencing: Prototype DECLARE -- A forward referencing stub for a local procedure. PROCEDURE b; -- A local procedure calling a forward referenced procedure. PROCEDURE a IS BEGIN b; dbms_output.put_line('This is procedure A.'); END a; -- A local procedure implementation for the forward referencing stub. PROCEDURE b IS BEGIN dbms_output.put_line('This is procedure B.'); END b; BEGIN a; END; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  9. Procedures & FunctionsParameter Mode: Definition • Parameter mode is either IN, IN OUT, or OUT: • The mode determines whether or not a formal parameter can be updated after it is passed to a function or procedure. • The IN mode disallows update to the referenced variable in all cases. • The IN OUT or OUT mode allows update to the referenced variable in all cases. • The IN OUT or OUT mode do not support parameter default values. • Functions support: • Only the IN mode because they return formal data type other than a void. • Procedures support: • All combinations because they return a void data type that is inaccessible in the PL/SQL environment. Oracle Database PL/SQL 10g Programming (Chapter 8)

  10. Procedures & FunctionsPositional versus Named Notation • Formal parameters in functions and procedures: • Are positional specific by data type as a default. • Can have default values, which makes them optional when called by another program unit. • Can be referenced by the formal parameter name using the named notation assignment operator: =>. • Formal parameter design considerations: • Organize all optional parameters at the end of the list of parameters. • When using positional calls to two or more optional parameters, a NULL value must be passed to any optional parameter skipped, or the value will be sent as the wrong actual parameter: • This raises an exception if the types differ. • This raises no advice if the types agree. • When two or more optional parameters are in a parameter list, the function or procedure should be called using named notation. Oracle Database PL/SQL 10g Programming (Chapter 8)

  11. Procedures & FunctionsCall Statement: Definition • The CALL statement lets the developer test calling PL/SQL programs from the SQL prompt. • The CALL statement can run stored procedures with formal parameters using any mode: • The IN only mode variables can be called by passing values. • The IN OUT and OUT mode variable must be called by passing a bind variable. Oracle Database PL/SQL 10g Programming (Chapter 8)

  12. Procedures & FunctionsCall Statement: Prototype CREATE OR REPLACE PROCEDURE show_call (whom_in IN OUT VARCHAR2) IS hello VARCHAR2(10) := 'Hello '; whom VARCHAR2(10) := 'World'; BEGIN whom_in := hello || NVL(whom_in,whom) ||'!'; END show_call; / VARIABLE chosen VARCHAR2(20) CALL show_call(:chosen); SELECT :chosen FROM dual; Oracle Database PL/SQL 10g Programming (Chapter 8)

  13. Procedures & FunctionsPackage Specification: Definition • Package Specifications: • Define the database catalog for the package. • Can define types, global variables, system reference cursors, functions and procedures. Oracle Database PL/SQL 10g Programming (Chapter 8)

  14. Procedures & FunctionsPackage Specification: Definition CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [TYPE type_name IS {RECORD | REF CURSOR | TABLE} OF data_type [%ROWTYPE] [(parameter 1 data_type [:= null_default1] [,parameter2 data_type [:= null_default2] [,parameter(n+1) data_type [:= null_default(n+1)]]])] [INDEX BY BINARY_INTEGER]; … more_in_later_slide … END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  15. Procedures & FunctionsPackage Specification: Definition CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} … covered_in_early_slide … FUNCTION function_name [(parameter1 [IN] data_type [:= null_default1] [,parameter2 [IN] data_type [:= null_default2] [,parameter(n+1) [IN] data_type [:= null_default(n+1)]]])] RETURN return_data_type; … more_in_later_slide … END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  16. Procedures & FunctionsPackage Specification: Definition CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} … covered_in_early_slide … PROCEDURE procedure_name [(parameter1 [IN [OUT]] data_type [:= null_default1] [,parameter2 [IN [OUT]] data_type [:= null_default2] [,parameter(n+1) [IN [OUT]] data_type [:= null_default(n+1)]]])]; END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  17. Procedures & FunctionsPackage Body: Definition • Package Bodies: • Implement the definitions provided to the database catalog by the package specification. • Can define local types, global variables, system reference cursors, functions and procedures, which are not visible externally to the package. Oracle Database PL/SQL 10g Programming (Chapter 8)

  18. Procedures & FunctionsPackage Body: Definition CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} -- Define package body only types. [TYPE type_name IS {RECORD | REF CURSOR | TABLE} OF data_type [%ROWTYPE] [(parameter 1 data_type [:= null_default1] [,parameter2 data_type [:= null_default2] [,parameter(n+1) data_type [:= null_default(n+1)]]])] [INDEX BY BINARY_INTEGER]; … more_in_later_slide … END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  19. Procedures & FunctionsPackage Body: Definition CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} … covered_in_early_slide … FUNCTION function_name [(parameter1 [IN] data_type [:= null_default1] [,parameter2 [IN] data_type [:= null_default2] [,parameter(n+1) [IN] data_type [:= null_default(n+1)])] RETURN return_data_type {IS | AS} declaration_statements; BEGIN execution_statements; RETURN return_data_variable; END function_name; … more_in_later_slide … END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  20. Procedures & FunctionsPackage Body: Definition CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} … covered_in_early_slide … PROCEDURE procedure_name [(parameter1 [IN [OUT]] data_type [:= null_default1] [,parameter2 [IN [OUT]] data_type [:= null_default2] [,parameter(n+1) [IN [OUT]] data_type [:= null_default(n+1)]]])]; declaration_statements; BEGIN execution_statements; END procedure_name; … more_in_later_slide … END package_name; / Oracle Database PL/SQL 10g Programming (Chapter 8)

  21. Summary • Functions • Procedures • Forward Referencing • Parameter Mode • Positional versus Named Notation • Call Statement • Package Specifications • Package Bodies Oracle Database PL/SQL 10g Programming (Chapter 8)

More Related