220 likes | 377 Views
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
E N D
Advanced SQL And PL/SQL Topics Eyad Alshareef
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
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
Types of Program Units Eyad Alshareef
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
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
Parameter Declarations List • Defines the parameters and declares their associated data types • Enclosed in parentheses • Separated by commas Eyad Alshareef
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
Creating a Stored Procedure in SQL*Plus Eyad Alshareef
Debugging Stored Program Units in SQL*Plus Eyad Alshareef
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
Debugging Stored Program Units in SQL*Plus Eyad Alshareef
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
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
Creating a Stored Program Unit Function Eyad Alshareef
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
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
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
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
Creating a Stored Program Unit Function • Last command in function must be RETURN Eyad Alshareef
Calling a Function • variable_name := function_name(parameter1, parameter2, ...); Eyad Alshareef
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