1 / 22

Advanced SQL And PL/SQL Topics

Advanced SQL And PL/SQL Topics. Overview of PL/SQL Stored Program Units. Self-contained group of program statements that can be used within a larger program. Easier to conceptualize, design, and debug

stamos
Download Presentation

Advanced SQL And PL/SQL Topics

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. Advanced SQL And PL/SQL Topics Eyad Alshareef

  2. Overview of PL/SQL Stored Program Units • Self-contained group of program statements that can be used within a larger program. • Easier to conceptualize, design, and debug • Save valuable programming time because you can reuse them in multiple database applications • Other PL/SQL programs can reference them Eyad Alshareef

  3. Functions and Procedures Up until now, our code was in an anonymous block It was run immediately It is useful to put code in a function or procedure so it can be called several times Once we create a procedure or function in a Database, it will remain until deleted (like a table). Eyad Alshareef

  4. Types of Program Units Eyad Alshareef

  5. Creating Stored Program Units • Procedure: a program unit that can receive multiple input parameters and return multiple output values or return no output values • Function: a program unit that can receive multiple input parameters, and always returns a single output value. Eyad Alshareef

  6. Creating Procedures Modes: IN: procedure must be called with a value for the parameter. Value cannot be changed OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference) IN OUT: value can be sent, and changes to the parameter are seen by the user Default Mode is: IN CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS PL/SQL Block; Eyad Alshareef

  7. Parameter Declarations List • Defines the parameters and declares their associated data types • Enclosed in parentheses • Separated by commas Eyad Alshareef

  8. Parameter Declarations List • Parameter mode describes how the program unit can change the parameter value: • Modes: • IN: procedure must be called with a value for the parameter. Value cannot be changed • OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference) • IN OUT: value can be sent, and changes to the parameter are seen by the user • Default Mode is: IN Eyad Alshareef

  9. Creating a Stored Procedure in SQL*Plus Eyad Alshareef

  10. Debugging Stored Program Units in SQL*Plus Eyad Alshareef

  11. Errors in a Procedure When creating the procedure, if there are errors in its definition, they will not be shown To see the errors of a procedure called myProcedure, type SHOW ERRORS PROCEDURE myProcedure in the SQLPLUS prompt For functions, type SHOW ERRORS FUNCTION myFunction Eyad Alshareef

  12. Debugging Stored Program Units in SQL*Plus Eyad Alshareef

  13. Calling a Stored Procedure • From SQL*Plus command line: • EXECUTE procedure_name (parameter1_value, parameter2_value, ...); • From PL/SQL program: • Omit execute command Eyad Alshareef

  14. Creating a Function Almost exactly like creating a procedure, but you supply a return type CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] RETURN datatype IS|AS PL/SQL Block; Eyad Alshareef

  15. Creating a Stored Program Unit Function Eyad Alshareef

  16. Stored Function: Example Create the function: Invoke the function as an expression or as a parameter value: CREATE OR REPLACE FUNCTION get_sal (id employees.employee_id%TYPE) RETURN NUMBER IS sal employees.salary%TYPE := 0; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = id; RETURN sal; END get_sal; / EXECUTE dbms_output.put_line(get_sal(100)) Eyad Alshareef

  17. Ways to Execute Functions Invoke as part of a PL/SQL expression Using a local variable to obtain the result Use as a parameter to another subprogram Use in a SQL statement (subject to restrictions) DECLARE sal employees.salary%type; BEGIN sal := get_sal(100); ... END; EXECUTE dbms_output.put_line(get_sal(100)) SELECT job_id, get_sal(employee_id) FROM employees; Eyad Alshareef

  18. Function in SQL Expressions: Example CREATE OR REPLACE FUNCTION tax(value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (value * 0.08); END tax; / SELECT employee_id, last_name, salary, tax(salary) FROM employees WHERE department_id = 100; Function created. 6 rows selected. Eyad Alshareef

  19. create or replace function squareFunc(num in number) return number is BEGIN return num*num; End; / Creating a function: Using the function: BEGIN dbms_output.put_line(squareFunc(3.5)); END; / Eyad Alshareef

  20. Creating a Stored Program Unit Function • Last command in function must be RETURN Eyad Alshareef

  21. Calling a Function • variable_name := function_name(parameter1, parameter2, ...); Eyad Alshareef

  22. Removing Functions Removing a stored function: You can drop a stored function by using the following syntax: Example: All the privileges that are granted on a function are revoked when the function is dropped. The CREATE OR REPLACE syntax is equivalent to dropping a function and re-creating it. Privileges granted on the function remain the same when this syntax is used. DROP FUNCTION function_name DROP FUNCTION get_sal; Eyad Alshareef

More Related