Introduction to Oracle PLSQL CIA 4203 LO 2

Introduction to Oracle PL/SQL CIA 4203 LO 2

Lesson Objectives • Describe the fundamentals of the PL/SQL programming language • Explain the benefits of using PL/SQL blocks versus several SQL statements • Identify the sections of a PL/SQL block and describe their contents • Write and execute PL/SQL programs in SQL Developer or APEX • Display output through PL/SQL programs • Debug PL/SQL programs

PL/SQL • • Procedure Language SQL Interpreted Language Native to Oracle Strongly typed language – Explicitly declare each variable including data type before using variable Introduction to Oracle PL/SQL 3

Advantages of PL/SQL • Can include error handling and control structures • Can be stored and used by various application programs or users • Allows for tighter security by granting privileges for executing stored procedures rather than directly on database object Introduction to Oracle PL/SQL 4

Types of Blocks • • Function Procedure Triggers Anonymous block Introduction to Oracle PL/SQL 5

Function • Named block that is stored on Oracle server • Accepts zero or more input parameters • Returns one value Introduction to Oracle PL/SQL 6

Procedure • • Named block Can process several variables Returns no values Interacts with application program using IN, OUT, or INOUT parameters Introduction to Oracle PL/SQL 7

Triggers • A program unit that runs in response to an event. • The even could be deleting a record, inserting a new record, an update or click of a button or a system action such as startup of the database. For instance you can create a trigger that inserts the next sequence value into the EMPLOYEE_ID field, in the EMPLOYEE form, whenever the user creates a new employee. Introduction to Oracle PL/SQL 8

Anonymous Block • Not stored since it cannot be referenced by a name • Usually embedded in an application program, stored in a script file, or manually entered when needed Introduction to Oracle PL/SQL 9

Basic Structure of a Block Has 3 sections: • Declaration section (Optional) • Execution section (Required) • Exception section (Optional) • Comment statements – Enclosed within /* and */ Introduction to Oracle PL/SQL 10

Declarative Section • Identified by the DECLARE keyword • Used to define variables and constants referenced in the block • Forward execution – variable and constants must be declared before they can be referenced • Variable Declaration syntax: variable_name v_salary v_lastname data_type; NUMBER(7, 2); VARCHAR 2(20); • DEFAULT value always NULL Introduction to Oracle PL/SQL 11

Executable Section • Identified by the BEGIN keyword • Mandatory • Can consist of several SQL and/or PL/SQL statements Introduction to Oracle PL/SQL 12

Exception-handling Section • Identified by the EXCEPTION keyword • Used to display messages or identify other actions to be taken when an error occurs • Addresses errors that occur during a statement’s execution Introduction to Oracle PL/SQL 13

END Keyword • Used to close a PL/SQL block • Always followed by a semicolon Introduction to Oracle PL/SQL 14

Example PL/SQL Block (1) Introduction to Oracle PL/SQL 15

Example PL/SQL Block (2) Introduction to Oracle PL/SQL 16

Example PL/SQL Block (3) Introduction to Oracle PL/SQL 17

Declaring a Variable • Reserves a temporary storage area in the computer’s memory • Every variable must have: – A name – A datatype • Variables can be initialized • Variables that have a value that does not change during the execution of the block can be designated as constant using the optional keyword CONSTANT Introduction to Oracle PL/SQL 18

Some PL/SQL Datatypes • Scalar – holds a single value – – – – CHAR(2) NUMBER(5, 2) BOOLEAN -- true/false VARCHAR 2(30) %TYPE -- v_empid HR_EMPLOYEES. EMPLOYEE_ID%TYPE BINARY_INTEGER DATE • Other Data Types: – Composite – collection of grouped data treated as one unit (RECORD, TABLE, VARRAY) – Reference – holds pointers to other program items – Large Object (LOB) – holds location of large objects Introduction to Oracle PL/SQL 19

SELECT Statement Requires use of INTO clause to identify variable assigned to each data element Introduction to Oracle PL/SQL 20

Example PL/SQL Block (4) » SELECT statement with an INTO clause Introduction to Oracle PL/SQL 21

Example PL/SQL Block (5) Introduction to Oracle PL/SQL 22

Example PL/SQL Block (6) Introduction to Oracle PL/SQL 23

Example PL/SQL Block (7) Introduction to Oracle PL/SQL 24

Cursors • Implicit cursor – created for DML operations or a SELECT statement that retrieves only one row of results • Explicit cursor – required for SELECT statements retrieving more than one row of results Introduction to Oracle PL/SQL 25

Debugging PL/SQL Programs • Syntax error – Occurs when command does not follow guidelines of programming language – Generate compiler or interpreter error messages • Logical error – Does not stop program from running – Results in incorrect result Introduction to Oracle PL/SQL 30

Syntax Errors • Often involve: – Misspelling reserved word – Omitting required character in command – Using built-in function improperly • Interpreter – Flags line number and character location of syntax errors • May actually be on preceding line – Displays error code and message Introduction to Oracle PL/SQL 27

Example Syntax Error » Missing SEMI_COLON (; ) Introduction to Oracle PL/SQL 28

Logical Errors • Caused by: – Not using proper order of operations in arithmetic functions – Passing incorrect parameter values to built-in functions – Creating loops that do not terminate properly – Using data values that are out of range or not of right data type Introduction to Oracle PL/SQL 29

Example Logical Error Introduction to Oracle PL/SQL 30

PL/SQL Execution Control • Sequential processing – Processes statements one after another • Decision control structures – Alter order in which statements execute – Based on values of certain variables Introduction to Oracle PL/SQL 31

Execution (Decision) Control Structures • IF/THEN statement – executes statements based on a condition • Basic loop – executes statements until condition in EXIT clause is TRUE • FOR loop – uses counter • WHILE loop – executes statements until condition is FALSE Introduction to Oracle PL/SQL 32

IF/THEN Statement Syntax IF condition THEN commands that execute if condition is TRUE; END IF; • Condition – Expression evaluates to TRUE or FALSE – If TRUE commands execute Introduction to Oracle PL/SQL 33

IF/THEN/ELSE Statement Syntax IF condition THEN commands that execute if condition is TRUE; ELSE commands that execute if condition is FALSE; END IF; • Evaluates ELSE command if condition FALSE Introduction to Oracle PL/SQL 34

Nested IF/ELSIF IF condition 1 THEN commands that execute if condition 1 is TRUE; ELSIF condition 2 THEN commands that execute if condition 2 is TRUE; . . . ELSE commands that execute if no conditions TRUE; END IF; Introduction to Oracle PL/SQL 35

Example PL/SQL Block (8) Introduction to Oracle PL/SQL 36

Example IF Statement (9) Introduction to Oracle PL/SQL 37

Example IF Statement (10) Introduction to Oracle PL/SQL 38

Loops • Loop repeats action multiple times until it reaches exit condition. Basic Loop LOOP statements; EXIT WHEN (condition); END LOOP; Introduction to Oracle PL/SQL 39

Basic Loop Example (11) Introduction to Oracle PL/SQL 40

FOR Loop Example (12) Introduction to Oracle PL/SQL 41

WHILE Loop Example (13) Introduction to Oracle PL/SQL 42

Nested Loops • Any type of loop can be nested inside another loop • Execution of the inner loop must be completed before control is returned to the outer loop Introduction to Oracle PL/SQL 43

STORED PROCEDURES/FUNCTIONS Example (13): • Create a PL/SQL Block that can be used to retrieve the gross salary of an employee based on the Employee ID. Let’s attempt to write the code for the above example: Introduction to Oracle PL/SQL 44

Example (13) – Anonymous Block DECLARE v_gross_salary NUMBER(9, 2); BEGIN select salary + (salary * nvl(commission_pct, 0)) into v_gross_salary from hr_employees where employee_id = 101; dbms_output. put_line(v_gross_salary); END; Introduction to Oracle PL/SQL 45

Example (13) – STORED PROCEDURE CREATE OR REPLACE PROCEDURE CALC_GROSS_SALARY (empid NUMBER) -- This is a comment -- EMPID is passed as parameter to the procedure AS v_gross_salary NUMBER(9, 2); BEGIN select salary + (salary * nvl(commission_pct, 0)) into v_gross_salary from hr_employees where employee_id = empid; dbms_output. put_line(v_gross_salary); END; Introduction to Oracle PL/SQL 46

Execute a stored procedure in APEX » An exception handler is required when the SQL statement returns no rows or more than one row Introduction to Oracle PL/SQL 47

Example (13) – STORED PROCEDURE with EXCEPTION handler CREATE OR REPLACE PROCEDURE CALC_GROSS_SALARY (empid NUMBER) AS v_gross_salary NUMBER(9, 2); BEGIN select salary + (salary * nvl(commission_pct, 0)) into v_gross_salary from hr_employees where employee_id = empid; dbms_output. put_line('Employee Gross Salary: $' || v_gross_salary); EXCEPTION -- This code is the error handler when no_data_found then dbms_output. put_line('Employee ID is not valid !!!!'); END; Introduction to Oracle PL/SQL 48

Example (13) – FUNCTION CREATE OR REPLACE FUNCTION FCALC_GROSS_SALARY (empid NUMBER) RETURN NUMBER IS -- This is a comment -- EMPID is passed as parameter to the procedure v_gross_salary NUMBER(9, 2); BEGIN select salary + (salary * nvl(commission_pct, 0)) into v_gross_salary from hr_employees where employee_id = empid; return(v_gross_salary); EXCEPTION when no_data_found then dbms_output. put_line('Employee ID is not valid !!!!'); END; Introduction to Oracle PL/SQL 49

Execute a Function Introduction to Oracle PL/SQL 50

Self Test • Write a function that calculates the returns the number of employees based on a given department ID. • Write a function that returns the duration of the project based on the Project ID. Introduction to Oracle PL/SQL 51