Store ProcedureFunctions PLSQL Lecture 07 https docs oracle

Store Procedure/Functions PL/SQL Lecture 07 https: //docs. oracle. com/cd/B 28359_01/appdev. 111/b 28843/tdddg_procedures. htm https: //docs. oracle. com/database/121/LNPLS/controlstatements. htm#LNPLS 411

Agenda • PL/SQL Overview • Creating Standalone Procedures and Functions • Variable and Constraints • General Comparison Functions

PL/SQL Overview • PL/SQL is a language with a procedural construct integrated with SQL that can be used to build complex application. • PL/SQL is executed in the database. • PL/SQL can be used to create the following program units: Procedures Functions Packages

Creating Procedures/Functions • In Oracle, a program can be written and stored in the database once and be accessed from any application program. • There are two schema level standalone programs: Procedures are programs with no returning value Functions Are programs with a returning value When these programs are written and complied in a database becomes the schema objects called stored procedures stored functions.

Procedure/Function building Block Header AS [declaration statements . . . ] BEGIN . . . [EXCEPTION . . . ] END; • Procedures/Functions consists of the following basic PL/SQL block structures: Declarative (optional) Variables and constants are identifies by keyword DECLARE. Executable (mandatory) Contains the application logic. It starts with keyword BEGIN and ends with keyword END. Exception handling (optional) Starts with keyword EXCEPTION and handles error conditions that may occur in the executable part.

Create Procedures/Functions • The following is the syntax to create a stored procedure or function: The keyword OR REPLACE recreates a function or a procedure if it already exists. Schema is the name of schema that contains the procedure/function. CREATE OR REPLACE PROCEDURE schema. procedure_name(arg 1 data_type, . . . ) AS BEGIN . . END procedure_name; CREATE OR REPLACE FUNCTION schema. function_name(arg 1 data_type, . . . ) AS BEGIN . . RETURN END function_name;

Arguments in a Procedure/Function • A procedure/function may receive arguments. • An argument has the following elements: Datatype Can be any datatype supported by PL/SQL. IN/OUT/IN OUT IN indicates that the procedure has to receive a value for the argument. OUT indicate that the procedure/function passes a value for the argument back to the calling program. IN OUT indicates that procedure must receive a value for the argument and passes a value back to the calling program. Default Using DEFAULT keyword, you can define a value for an argument.

PL/SQL Anonymous Blocks • Consider the following simple PL/SQL code: Function DBMS_OUTPUT. PUT_LINE() outputs the given message or values as arguments. BEGIN DBMS_OUTPUT. PUT_LINE (‘Welcome to DBS 311!'); END; • To see the output: Execute the following statement first: SET SERVEROUTPUT ON; • To execute the block, in your SQL developer worksheet, select the block and press the run bottom: BEGIN DBMS_OUTPUT. PUT_LINE (‘Welcome to DBS 311!'); END; • Output: anonymous block completed Hello World!. . .

Declaration • Using DECLARE keyword, we can define variables and constants. See the following code: DECLARE value_1 NUMBER : = 20; value_2 NUMBER : = 5; addition NUMBER; subtraction NUMBER; multiplication NUMBER; division FLOAT; BEGIN addition : = value_1 + value_2; subtraction : = value_1 - value_2; multiplication : = value_1 * value_2; division : = value_1 / value_2; DBMS_OUTPUT. PUT_LINE ('addition: ' || addition); DBMS_OUTPUT. PUT_LINE ('subtraction: ' || subtraction); DBMS_OUTPUT. PUT_LINE (‘multiplication: ' || multiplication); DBMS_OUTPUT. PUT_LINE ('division: ' || division); END; • output: anonymous block completed addition: 25 subtraction: 15 multiplication: 100 division: 4

Exception • Exception part handles the errors occurred during the execution of a PL/SQL block. See the following PL/SQL block: DECLARE value_1 NUMBER : = 20; value_2 NUMBER : = 0; division NUMBER; BEGIN division : = value_1 / value_2; DBMS_OUTPUT. PUT_LINE ('division: ' || division); END; • The execution of the above code stops with an error. See the following output: Error report: ORA-01476: divisor is equal to zero ORA-06512: at line 8 01476. 00000 - "divisor is equal to zero" *Cause: *Action:

Exception Handling • To handle the exception errors, we use EXCEPTION section. WE add this section to handle the error in the code from previous slide: DECLARE value_1 NUMBER : = 20; value_2 NUMBER : = 0; division NUMBER; BEGIN division : = value_1 / value_2; DBMS_OUTPUT. PUT_LINE ('division: ' || division); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Error!'); END; • See the output with exception handling: anonymous block completed Error! • You can also check the following exception: WHEN ZERO_DIVIDE THEN DBMS_OUTPUT. PUT_LINE ('Divider is zero!');

SELECT INTO • IN PL/SQL, you can use SELECT INTO statement to store data from a single row fetch by a SELECT statement. SELECT column_list INTO variable_list FROM table_name WHERE condition(s); • TOO_MANY_ROWS exception An exception will occur if the SELECT statement returns more than one row. • NO_DATA_FOUND exception An exception will occur if the SELECT statement returns no data.

SELECT INTO (Example) • The following PL/SQL code searches for a specific product by its product ID and displays the product ID and the product name for that product. DECLARE product. Id NUMBER : = 2; product. Name VARCHAR 2(255 BYTE); price NUMBER(9, 2); BEGIN SELECT product_name, List_price INTO product. Name, price FROM products WHERE product_id = product. ID; DBMS_OUTPUT. PUT_LINE ('Product Name: ' || product. Name); DBMS_OUTPUT. PUT_LINE ('Product Price: ' || price); END; • Output: anonymous block completed Product Name: Intel Xeon E 5 -2697 V 4 Product Price: 2554. 99

TOO_MANY_ROWS Exception • In SELECT INTO statement, an exception occurs if the result of the fetched data includes more than one row. • We change the condition to search for products with category 2. Since, we have many products in this category, the SELECT INTO statement fetches more than one row and raised an exception. See the following code DECLARE category. ID NUMBER : = 2; product. Name VARCHAR 2(255 BYTE); price NUMBER(9, 2); BEGIN SELECT product_name, List_price INTO product. Name, price FROM products WHERE category_id = category. Id; DBMS_OUTPUT. PUT_LINE ('Product Name: ' || product. Name); DBMS_OUTPUT. PUT_LINE ('Product Price: ' || price); END; • Output: Error report: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested

TOO_MANY_ROWS Exception Handling • We use the EXCEPTION section to handle the error of the PL/SQL block from the previous slide. DECLARE category. Id NUMBER : = 2; product. Name VARCHAR 2(255 BYTE); price NUMBER(9, 2); BEGIN SELECT product_name, List_price INTO product. Name, price FROM products WHERE category_id = category. Id; DBMS_OUTPUT. PUT_LINE ('Product Name: ' || product. Name); DBMS_OUTPUT. PUT_LINE ('Product Price: ' || price); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT. PUT_LINE ('Too Many Rows Returned!'); END; • Output: anonymous block completed Too Many Rows Returned!

NO_DATA_FOUND Exception • IF the SELECT INTO does not fetch any data, the NO_DATA_FOUND exception is raised. The following PL/SQL block handles the exception. DECLARE product. Id NUMBER : = 300; product. Name VARCHAR 2(255 BYTE); price NUMBER(9, 2); BEGIN SELECT product_name, List_price INTO product. Name, price FROM products WHERE product_id = product. Id; DBMS_OUTPUT. PUT_LINE ('Product Name: ' || product. Name); DBMS_OUTPUT. PUT_LINE ('Product Price: ' || price); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE ('No Data Found!'); END; • Output: anonymous block completed No Data Found!

Your First Procedure • So far, we create and execute anonymous PL/SQL blocks. If a code is used from multiple programs or applications, you need to store them in your database. Stored PL/SQL blocks are known as stored procedures or stored functions. • The following simple procedure displays a text after execution. This stored procedure CREATE OR REPLACE PROCEDURE my_procedure AS BEGIN DBMS_OUTPUT. PUT_LINE ('Hello World!. . . '); END; • To call the procedure you can execute the following code: BEGIN my_procedure(); END; • A stored procedure/function can be called in an application or in another PL/SQL procedure/function.

CREATE PROCEDURE Example 1 • Create a new table named new_employee from the employees table. Execute the following procedure: CREATE OR REPLACE PROCEDURE remove_employee AS employee. Id NUMBER; BEGIN employee. Id : = 1080; DELETE FROM new_employee WHERE employee_id = employee. Id; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Error!'); END; • To execute: • Output: BEGIN remove_employee(); END; anonymous block completed

Stored Procedures with Parameters • In PL/SQL, we can pass parameters to stored procedures and functions. • See the following syntax CREATE OR REPLACE procedure_name(arg 1 data_type, . . . ) AS BEGIN . . END procedure_name; • The following stored procedure deletes the employee with Id given in the stored procedure parameter: CREATE PROCEDURE remove_emp (employee_id NUMBER) AS EGIN DELETE FROM employees WHERE employees. employee_id = remove_emp. employee_id; END;

Parameters in Stored Prcedures • There are three types of parameters: IN parameter to send values to a stored procedures OUT parameter to get values from stored procedures IN OUT parameter to send and get values from stored procedures. • By default, a parameter is an IN parameter in stored procedures.

IN Parameters • See the following syntax: CREATE OR REPLACE procedure_name(arg 1 IN data_type, . . . ) AS BEGIN . . END procedure_name; • The following stored procedure deletes the employee with Id given in the stored procedure parameter: CREATE PROCEDURE remove_emp (employee_id IN NUMBER) AS EGIN DELETE FROM employees WHERE employees. employee_id = remove_emp. employee_id; END;

OUT Parameters • See the following syntax: CREATE OR REPLACE procedure_name(arg 1 OUT data_type, . . . ) AS BEGIN . . END procedure_name; • The following stored procedure find the number of employees and stores it in the output parameter employee_count: CREATE PROCEDURE count_employees (employee_count OUT NUMBER) AS employee_count NUMBER; BEGIN select count(*) INTO employee_count from employees; END; DECLARE employee_count NUMBER : = 0; BEGIN count_employees(employee_count); DBMS_OUTPUT. PUT_LINE (employee_count); END;

IN OUT Parameters • See the following syntax: CREATE OR REPLACE procedure_name(arg 1 IN OUT data_type, . . . ) AS BEGIN . . END procedure_name; • The following procedure gets a salary and increases the salary by 2%. CREATE OR REPLACE PROCEDURE employee_new_salary (salary IN OUT FLOAT) AS BEGIN salary : = salary * 1. 2; END; DECLARE salary FLOAT : = 1503; BEGIN employee_new_salary (salary); DBMS_OUTPUT. PUT_LINE (salary); END;

UPDATE/DELETE in Procedures • When deleting or updating data in database tables, if no error occurs, the output of the procedure/function will be: block complete • To check if any rows updated or deleted, we need conditional statements to see how many rows are affected. • Control statements in PL/SQL Conditional selection statements Loop statements Sequential control statements

Conditional Selection Statements • The conditional statement runs a sequence of statements if the condition is true. IF statement IF THEN ELSE IF THEN ELSIF ELSE CASE statement Simple Compare a given expression to different values Search Evaluates multiple conditions and choose the first condition which is true.

IF THEN Statement • IF condition THEN statements END IF; See the following procedure. IF THEN statement in this procedures checks if any row is deleted by evaluating the number of the affected rows. CREATE OR REPLACE PROCEDURE remove_employee AS employee. Id NUMBER; BEGIN employee. Id : = 1080; DELETE FROM new_employee WHERE employee_id = employee. Id; IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT. PUT_LINE ('Employee with ID ' || employee. Id || ' does not exists'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Error!'); END; • Output: BEGIN remove_employee(); END; anonymous block completed Employee with ID 1080 does not exists

IF THEN ELSE • IF condition THEN statements ELSE statements END IF; See the procedure below. If the employee with ID 12 is deleted it shows that the employee is deleted. If the employee does not exists, it shows the message that the employee does not exists. CREATE OR REPLACE PROCEDURE remove_employee AS employee. Id NUMBER; BEGIN employee. Id : = 12; DELETE FROM new_employee WHERE employee_id = employee. Id; IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT. PUT_LINE ('Employee with ID ' || employee. Id || ' does not exists'); ELSE DBMS_OUTPUT. PUT_LINE ('Employee with ID ' || employee. Id || ' DELETED!'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Error!'); END; • Output: anonymous block completed Employee with ID 12 DELETED!

IF THEN ELSIF • See the following procedure: IF condition THEN statements ELSIF statements ELSE statements END IF; CREATE OR REPLACE PROCEDURE remove_employee AS manager. Id NUMBER; BEGIN manager. Id : = 2; DELETE FROM new_employee WHERE manager_id = manager. Id; IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT. PUT_LINE ('No employee is deleted'); ELSIF SQL%ROWCOUNT = 1 THEN DBMS_OUTPUT. PUT_LINE ('One employee is deleted. '); ELSE DBMS_OUTPUT. PUT_LINE ('More than one employee is deleted!'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Error!'); END; • Output: There is more than one employee with manager Id 2. Hence, more that one employee is deleted as a result of the code execution. anonymous block completed More than one employee is deleted!

Nested IF THEN ELSE • See the following syntax for nested conditional statements. IF condition THEN statements ELSE condition statements END IF; ELSIF IF condition THEN statements END IF; ELSE statements END IF;

Simple CASE Statement • In the simple CASE statement, the value of the selector is compared with the values after the WHEN clause. The statement of the first matching value is then executed. • If non of the values matches the value of the selector, the ELSE statement is executed if it exits. CASE selector WHEN value_1 THEN statements WHEN value_2 THEN statements. . . WHEN value_n THEN statements [ ELSE statements ] END CASE;

Simple CASE Example • The following PL/SQL block prints the proper message based on the given value for the semester variable. DECLARE semester CHAR(1); BEGIN semester : = 'S'; CASE semester WHEN 'F' THEN DBMS_OUTPUT. PUT_LINE('Fall Term'); WHEN 'W' THEN DBMS_OUTPUT. PUT_LINE('Winter Term'); WHEN 'S' THEN DBMS_OUTPUT. PUT_LINE('Summer Term'); ELSE DBMS_OUTPUT. PUT_LINE('Wrong Value'); END CASE; END; • Output: anonymous block completed Summer Term

IF ELSIF and CASE • The given codes below do the same task: CASE semester WHEN 'F' THEN DBMS_OUTPUT. PUT_LINE('Fall Term'); WHEN 'W' THEN DBMS_OUTPUT. PUT_LINE('Winter Term'); WHEN 'S' THEN DBMS_OUTPUT. PUT_LINE('Summer Term'); ELSE DBMS_OUTPUT. PUT_LINE('Wrong Value'); END CASE; IF semester = 'F' THEN DBMS_OUTPUT. PUT_LINE('Fall Term'); ELSIF semester = 'W' THEN DBMS_OUTPUT. PUT_LINE('Winter Term'); ELSIF semester = 'S' THEN DBMS_OUTPUT. PUT_LINE('Summer Term'); ELSE DBMS_OUTPUT. PUT_LINE('Wrong Value'); END IF;