CLO1: INTRODUCTION TO PL/SQL

CLO1: Implement stored procedures, functions, and

triggers for backend processing.
Document Revision Version Author Effect Change DR
CONTROL (DRC) ive Description C
Date No
1.0 SCTL Sept Defined the 1.0
2020 first version
2.0 Nourchene Dec Restructured 2.0
Benayed 2022 the slides and
added new
X: MAJOR CHANGE
content
Y: MINOR CHANGE
2.1 Ghazala Dec Updated Slide 2.0
Bilquise 2022 18, Added
slides 44--
>47

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 2

 PL/SQL LANGUAGE

TYPES OF PL/SQL BLOCKS

VARIABLES

CONTROL STATEMENTS LECTURE

OUTLINE
CURSORS

EXCEPTIONS

DEBUGGING PL/SQL BLOCKS

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS

Objectives

 After completng this lesson, you should be able to do the following:

 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

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 4

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 5

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 6

PL/SQL Environment

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 7

docs.oracle.com
Types of Blocks

Anonymous
Function Procedure Trigger
block
• Named block • Named block • A trigger is a • Not stored
that is stored • Can process PL/SQL block since it cannot
on Oracle several associated be referenced
server variables with a specific by a name
• Accepts zero • Returns no table, view, • Usually
or more input values schema, or a embedded in
parameters • Interacts with database an application
• Returns one application • Executes program,
value program using implicitly stored in a
IN, OUT, or whenever a script file, or
INOUT particular manually
parameter event occurs entered when
INTRODUCTION TO ORACLE PL/SQL needed 8
PL/SQL Block Structure

Has 3 sections:
 Declaration section (Optional)
 Variables, cursors, user-defined exceptions
 Execution section (Required)
 SQL statements
 PL/SQL statements
 Exception section (Optional)
 Actions to perform when errors occur
 Comment statements
 Enclosed within /* and */

INTRODUCTION TO ORACLE PL/SQL 9

Example PL/SQL Block

INTRODUCTION TO ORACLE PL/SQL 10

Program Constructs

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 11

docs.oracle.com
  Reserves a temporary storage area
in the computer’s memory
 Every variable must have:
 A name
 A datatype
Declaring
 Variables can be initialized
Variables
 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

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 12

PL/SQL Datatypes
 Scalar – holds a single value
 CHAR(2)
 NUMBER(5,2)
 BOOLEAN -- true/false
The %TYPE attribute lets you declare
 VARCHAR2(30) a constant, variable, field, or
 %TYPE -- v_empid HR_EMPLOYEES.EMPLOYEE_ID%TYPEparameter to be of the same data
type a previously declared variable,
 BINARY_INTEGER
field, record, nested table, or
 DATE database column. If the referenced
item changes, your declaration is
 Other Data Types:
automatically updated.
 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 13
PL/SQL Block Example …

INTRODUCTION TO ORACLE PL/SQL 14

PL/SQL Block Example…

INTRODUCTION TO ORACLE PL/SQL 15

PL/SQL Block Example

INTRODUCTION TO ORACLE PL/SQL 16

Writing Control
Statements

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS

Source: 17
docs.oracle.com
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 18

IF statements…

Syntax Simple IF Statement Example

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 19

IF statements…

Syntax IF THEN ELSE Statement Example

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 20

Example IF THEN ELSE Statement

INTRODUCTION TO ORACLE PL/SQL 21

IF statements

Syntax IF ELSIF ELSE Statement Example

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 22

Example IF ELSIF ELSE Statement

INTRODUCTION TO ORACLE PL/SQL 23

Iterative Control: Loop Statements

 Loops repeat a statement or sequence of statements multiple times

 There are 3 loop types
 Basic loop
 FOR loop
 WHILE loop

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 24

BASIC and WHILE Loops

Syntax of Basic Loops Syntax of WHILE Loops

Loop repeats statements multiple times until WHILE Loop repeats statements while
it reaches exit condition condition is TRUE

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 25

Basic Loop Example

INTRODUCTION TO ORACLE PL/SQL 26

WHILE Loop Example

INTRODUCTION TO ORACLE PL/SQL 27

FOR Loops

 Do not decalre the counter: it is decalred

implicitly
 Lower_bound upper_bound is required
syntax

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 28

FOR Loop Example

INTRODUCTION TO ORACLE PL/SQL 29

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 30

 Every SQL statement executed by
the Oracle server has an associated
individual cursor
 Implicit cursor – declared and
managed by PL/SQL for all DML
operations and SELECT statement that
retrieves only one row of results Cursors
 Explicit cursor – Declared and
managed by the programmer. It is
required for SELECT statements
retrieving more than one row of results

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 31

Implicit Cursors – DML Statement

 When a DML statement (INSERT, UPDATE or DELETE) is issued an

implicit cursor is created.
 The cursor identifies the rows that are affected by the operation
 The most recent implicit cursor can be accessed as follows:
 SQL%ROWCOUTNT (returns the number of rows affected by the DML
statement)
 SQL%FOUND (returns true if the DML statement affected one or more rows)
 SQL%NOTFOUND (returns true of the DML statement affected no rows)

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 32

PL/SQL Block Example

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 33

Implicit Cursor - SELECT Statement

 Simplest way of fetching a single row using a select statement

Restrictions on using an implicit SELECT statement within a PLSQL block:

• The SELECT statement must return exactly one row
• Requires use of INTO clause to identify variable assigned to each data element
• The number and type of variables in the INTO clause list must match the number and type
of columns in the SELECT clause list
INTRODUCTION TO ORACLE PL/SQL 34
PL/SQL Block Example …

SELECT statement
with an INTO
clause

INTRODUCTION TO ORACLE PL/SQL 35

PL/SQL Block Example …

INTRODUCTION TO ORACLE PL/SQL 36

PL/SQL Block Example …

INTRODUCTION TO ORACLE PL/SQL 37

PL/SQL Block Example

INTRODUCTION TO ORACLE PL/SQL 38

Explicit Cursors

 Explicit cursors are programmer-defined cursors for gaining more

control over the context area.
 An explicit cursor should be defined in the declaration section of the
PL/SQL Block.
 It is created on a SELECT Statement which returns more than one
row.
 The syntax
CURSOR for creatingIS
cursor_name anselect_statement;
explicit cursor is -

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 39

Controlling Explicit Cursors…

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 40

docs.oracle.com
Declaring and opening a cursor

Declare a cursor open a cursor

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 41

docs.oracle.com
Fetching data from a Cursor

ow e
t r cti v
n a
rre h e
e cu set oft
th ti ve ws t
nly ac
l l ro se
d o the d a
a in a
Lo Lo

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS

Source: 42
docs.oracle.com
Closing a cursor

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 43

docs.oracle.com
Cursors and records

 Process the rows of the active set by fetching values into a PL/SQL
record

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 44

docs.oracle.com
Use a FOR LOOP to process an explicit cursor

 FOR LOOP statement allows you to fetch every row from a cursor
without manually managing the execution cycle i.e., OPEN, FETCH,
and CLOSE.
FOR record IN cursor_name
LOOP
process_record_statements;
END LOOP;

 Record is a pointer to the row of data that is fetched by the cursor

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 46

PL/SQL Example

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 47

Explicit Cursor Attributes

 Obtain status information about a cursor

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 48

docs.oracle.com
  An exception is a PL/SQL error that
is raised during program exceution
 An exception can be raised :
 Implicity by the Oracle server
Handling  Explicitly by the program
Exceptions  An exception can be handled
 By trapping it with a handler
 By propagating it to the calling
environment

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS

Source: 49
docs.oracle.com
Handling Exceptions

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS Source: 50

docs.oracle.com
Trapping Exceptions

 EXCEPTION keyord starts the exception

handling section
 Several exception handlers are allowed
 Only one handler is processed before
laving the block
 WHEN OTEHRS is the last clause

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS

Source: 51
docs.oracle.com
EXCEPTION TYPES

Predefined Exceptions User-defined Exceptions

• Implicitly raised • Explicitly raised
• Examples:
• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX
• OTHERS

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 52

Trapping exceptions

Trapping predefined exceptions Trapping user-defined exceptions

 Reference the predefined name in the  Declare, raise, and reference the user-
exception handling routine defined exception in the exception
handling routine

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 53

Trapping User-defined Exceptions

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 54

RAISE_APPLICATION_ERROR Proedure

 Use this procedure to issue user-defined error messages from stored

subprograms

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 55

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

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 56

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 57

Syntax Error Example

Missing
SEMI_COLON (;)

INTRODUCTION TO ORACLE PL/SQL 58

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 59

Logical Error Examples

INTRODUCTION TO ORACLE PL/SQL 60

Key Terms

Anonymous Control SELECT… Implicit

Variables
Block Statements INTO Cursors

Explicit Logical Syntax

Cursors
Records Exceptions Errors Errors

CIA 4203 - ENTERPRISE DATABASE APPLICATIONS 61

800 MyHCT (800 www.hct.ac.ae
69428)