Lesson 5

Overview of PL/SQL
 Topic Coved

• DBMS and RDBMS

• Installation of Oracle Database 21c XE and Oracle SQL Developer
• Oracle SQL (Querying data, Sorting data, Filtering data)
• Grouping and Joining tables and Subqueries
• Views, Indexes and Sequences
• Regular Expressions
• PL/SQL Blokes
• Control Structure, Iterative processing with loops, Select Into in PL/SQL
• Exception handlers
• Cursors, Records and Stored procedures and Functions
• Program Units
• Database Triggers
• PL/SQL Collections
• Dynamic SQL

2-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Objectives

• Determine the benefits of accessing the Oracle

database with Procedural Language/SQL
(PL/SQL).
• Describe basic PL/SQL program constructs.
• Describe the Oracle Procedure Builder tool.

3-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Overview

What is PL/SQL?
• PL/SQL is an extension to SQL with design
features of programming languages.
• Data manipulation and query statements are
included within procedural units of code.

• PL/SQL adds many procedural constructs to SQL

language to overcome some limitations of SQL.
Besides, PL/SQL provides a more comprehensive
programming language solution for building
mission-critical applications on Oracle Databases
4-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 What is PL/SQL?

• PL/SQL stands for “Procedural Language

extensions to the Structured Query Language”.
SQL is a popular language for both querying and
updating data in the relational database
management systems (RDBMS).

• PL/SQL is a highly structured and readable

language. Its constructs express the intent of the
code clearly. Also, PL/SQL is a straightforward
language to learn

5-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL architecture

6-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 • PL/SQL engine is in charge of compiling PL/SQL
code into byte-code and executes the executable
code. The PL/SQL engine can only be installed in
an Oracle Database server or an application
development tool such as Oracle Forms.

• Once you submit a PL/SQL block to the Oracle

Database server, the PL/SQL engine collaborates
with the SQL engine to compile and execute the
code. PL/SQL engine runs the procedural
elements while the SQL engine processes the SQL
statements.
7-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Benefits of PL/SQL

• Modularize program development

• Declare identifiers
• Program with procedural language control
structures
• Handle errors
• Portability
• Integration
• Improve performance

8-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

9-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL anonymous block overview

• PL/SQL is a block-structured language whose

code is organized into blocks.

• A PL/SQL block consists of three sections:

declaration, executable, and exception-handling
sections.

• In a block, the executable section is mandatory

while the declaration and exception-handling
sections are optional.

10-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 The following picture illustrates the
structure of a PL/SQL block:

11-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 • 1) Declaration section
• A PL/SQL block has a declaration section where
you declare variables, allocate memory for
cursors, and define data types.

• 2) Executable section
• A PL/SQL block has an executable section. An
executable section starts with the keyword BEGIN
and ends with the keyword END.

• 3)Exception-handling section
•
12-18
A PL/SQL block has an exception-handling section
that starts with the keyword EXCEPTION
.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright
 PL/SQL anonymous

• A block without a name is an anonymous block.

An anonymous block is not saved in the Oracle
Database server, so it is just for one-time use.
However, PL/SQL anonymous blocks can be
useful for testing purposes.

• A PL/SQL block has a name. Functions or

Procedures is an example of a named block. A
named block is stored into the Oracle Database
server and can be reused later.

13-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL anonymous block example

• 1)
• BEGIN
• DBMS_OUTPUT.put_line ('Hello World!');
• END;

• 2)
• DECLARE
• l_message VARCHAR2( 255 ) := 'Hello World!';
• BEGIN
• DBMS_OUTPUT.PUT_LINE( l_message );
14-18
• END;
.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright
 PL/SQL Boosts Performance

SQL
SQL
Application Other DBMSs
SQL
SQL

SQL
IF...THEN
SQL
Application ELSE Oracle with
SQL PL/SQL
;END IF
SQL

15-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL Block Structure
DECLARE
DECLARE –– Optional
Optional
–– Variables,
Variables, constants,
constants, cursors,
cursors,
user-defined
user-defined exceptions
exceptions
BEGIN
BEGIN –– Mandatory
Mandatory
–– SQL
SQL statements
statements
–– PL/SQL
PL/SQL control
control statements
statements
EXCEPTION
EXCEPTION –– Optional
Optional
–– Actions
Actions to
to perform
perform when
when errors
errors
occur
occur
END;
END; –– Mandatory
Mandatory

16-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 SQL Statements in PL/SQL Block

• DML IN PL/SQL
• DRL IN PL/SQL

17-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 DML IN PL/SQL

• BEGIN
• INSERT Statements;
• UPDATE statements;
• DELETE statements;
• END;
• /
• EXAMPLE:
• BEGIN
• INSERT INTO TABLE1 VALUES ('C14');
• UPDATE TABLE1 SET NO='C12' WHERE NO ='C13';
•
18-18 DELETE TABLE1 WHERE NO ='C14';
• END;
.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright
 DRL IN PL/SQL

• - It does not fetch the values from table in the

table structure, it fetch indiviual value from each
cell in the table into variable ,therefore we must
declare variable
• DECLARE
• Variable _name DATA_TYPE;
• BEGIN
• SELECT COLUMN_NAME INTO varible_name
from table_name where column=search_value;
statements_to_process_the record
END;
/
•
19-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 EXAMPLE

• DECLARE
• NAME VARCHAR2(20);
• BEGIN
• SELECT first_name INTO NAME
• FROM employees WHERE EMPLOYEE_ID=101
• DBMS_OUTPUT.PUT_LINE(NAME);
• END;

20-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Program Constructs
Stored
Anonymous
Procedure/
Block
Function
DECLARE
Application
Application
BEGIN Procedure/
Trigger
Function
EXCEPTION

Database ;END Packaged

Trigger Procedure

21-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Block Types
Anonymous Procedure
Function
[DECLARE] PROCEDURE
[DECLARE] PROCEDURE name
name FUNCTION
FUNCTION name
name
IS
IS RETURN
RETURN datatype
datatype
IS
IS
BEGIN
BEGIN BEGIN
BEGIN BEGIN
BEGIN
--statements
--statements --statements
--statements --statements
--statements
RETURN
RETURN value;
value;
[EXCEPTION]
[EXCEPTION] [EXCEPTION]
[EXCEPTION] [EXCEPTION]
[EXCEPTION]

END;
END; END;
END; END;
END;

22-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL Environment
PL/SQL Engine
non-SQL Procedural
PL/SQL PL/SQL
Block SQL Statement
Block Executor

SQL Statement Executor

Oracle Server

23-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Procedure Builder

• GUI development environment for PL/SQL code

• Built-in editors
• Compile, test, and debug code
• Application partitioning allows drag-and-drop of
program units between client and server

24-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Example PL/SQL Block
DECLARE
DECLARE
v_product_id
v_product_id s_product.id%TYPE;
s_product.id%TYPE;
BEGIN
BEGIN
SELECT
SELECT id
id
INTO v_product_id
INTO v_product_id
FROM
FROM s_product
s_product
WHERE
WHERE id
id == &p_product_id;
&p_product_id;
DELETE
DELETE FROM
FROM s_inventory
s_inventory
WHERE
WHERE product_id == v_product_id;
product_id v_product_id;
COMMIT;
COMMIT;
EXCEPTION
EXCEPTION
WHEN
WHEN OTHERS
OTHERS THEN
THEN
ROLLBACK;
ROLLBACK;
INSERT
INSERT INTO
INTO exception_table
exception_table (message)
(message)
VALUES
VALUES ('Some error occurred in
('Some error occurred in the
the
database.');
database.');
COMMIT;
COMMIT;
END;
END;
25-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL Data Types

• Types of Variables :
• a) Scalar – this data types hold a single value.
(data types that corresponds with column types.

• b) Composite – they allow groups of fields to be

defined and manipulated in blocks.

• c) References – they hold values, called pointers, but

designate other program items.

26-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 • PL/SQL divides the scalar data types into four
families:
• Number
• Boolean
• Character
• Datetime

27-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL Variables

• introduce you to PL/SQL variables and shows you

how to manipulate variables in programs efficiently

DECLARE
l_total_sales NUMBER(15,2);
l_credit_limit NUMBER (10,0);
;l_contact_name VARCHAR2(255)
BEGIN
;NULL
;END

28-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Default values

• PL/SQL allows you to set a default value for a

variable at the declaration time. To assign a
default value to a variable, you use the assignment
operator (:=) or the DEFAULT keyword.
• The following example declares a variable named
l_product_name with an initial value 'Laptop':
• DECLARE
• l_product_name VARCHAR2( 100 ) := 'Laptop';
• BEGIN
• NULL;
• END;
29-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 NOT NULL constraint

• DECLARE
• l_shipping_status VARCHAR2( 25 ) NOT
NULL := 'Shipped';
• BEGIN
• l_shipping_status := '';
END;

30-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Variable assignments

• To assign a value to a variable, you use the assignment

operator (:=), for example:
• DECLARE
• l_customer_group VARCHAR2(100) :='Silver';
• BEGIN
• l_customer_group := 'Gold';
• DBMS_OUTPUT.PUT_LINE(l_customer_group);
END;

31-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 The %TYPE Attribute

• Declare a variable according to

– Another previously declared variable.
– A database column definition.
• Prefix %TYPE with
– The database table and column.
– The previously declared variable name.
• PL/SQL determines the datatype and size of the
variable

32-18

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 The %TYPE Attribute: Examples
...
...
v_last_name
v_last_name s_emp.last_name%TYPE;
s_emp.last_name%TYPE;
v_first_name
v_first_name s_emp.first_name%TYPE;
s_emp.first_name%TYPE;
v_balance
v_balance NUMBER(7,2);
NUMBER(7,2);
v_minimum_balance
v_minimum_balance v_balance%TYPE
v_balance%TYPE :=
:= 10;
10;
...
...

Advantages of using the %TYPE attribute

• The datatype of the underlying database column
may be unknown.
• The datatype of the underlying database column
may change at runtime.

33-21

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 Consider the following example:

• DECLARE
• l_customer_name customers.name%TYPE;
• l_credit_limit customers.credit_limit%TYPE;
• BEGIN
• SELECT name, credit_limit INTO l_customer_name,
l_credit_limit FROM customers WHERE customer_id = 38;

• DBMS_OUTPUT.PUT_LINE(l_customer_name || ':' ||
l_credit_limit );

• END; /

34-21

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright

 PL/SQL Comments

• Single-line comments
• A single-line comment starts with a double hyphen ( --)
that can appear anywhere on a line and extends to the
end of the line.
• -- valued added tax 10%
• DECLARE co_vat_rate CONSTANT NUMBER := 0.1;
• Multi-line comments
• A multi-line comment starts with a slash-asterisk ( /* )
and ends with an asterisk-slash ( */ ), and can span
multiple lines:

35-21

.Oracle Corporation, 1996. All rights reserved ‫ س‬Copyright