Procedural SQL

PRINCIPLES OF DATABASE
DESIGN AND DATA
MANAGEMENT

Presented By:
Likeleli Letsie
 Learning Objectives


Procedural SQL

Date: 2025-04-22
 Introduction

You have learned to use SQL to read, write, and delete data in the database.

Most database access in practical applications is accomplished through software
programs that implement database applications.

Unfortunately, SQL does not support the conditional execution of procedures
typically supported by a programming language using the general format:

IF <condition>
THEN <perform procedure>
ELSE <perform alternate procedure>
END IF

Date: 2025-04-22
 Introduction
●
SQL also fails to support looping operations in programming languages that permit
the execution of repetitive actions typically encountered in a programming
environment.
●
The typical format is:
DO WHILE
<perform procedure>
END DO

●
Traditionally, if you wanted to perform a conditional or looping type of operation (that
is, a procedural type of programming using an IF-THEN-ELSE or DO-WHILE
statement)you would use a programming language such as Visual Basic .NET, C#, or
Java.
–
Although that approach is still common, it usually involves the duplication of
application code in many programs.
–
Therefore, when procedural changes are required, modifications must be made
in many different programs.
–
An environment characterized by such redundancies often creates data-
management problems.

Date: 2025-04-22
 Procedural SQL
●
The rise of distributed databases and object-oriented databases required that
more application code be stored and executed within the database.
●
To meet that requirement, most RDBMS vendors created numerous
programming language extensions.
●
Those extensions include:
– Flow-control procedural programming structures (IF-THEN-ELSE, DO-WHILE)
for logic representation
– Variable declaration and designation within the procedures
– Error management
●
To remedy the lack of procedural functionality in SQL and to provide some
standardization within the many vendor offerings, the SQL-99 standard defined
the use of persistent stored modules.

Date: 2025-04-22
 Procedural SQL
●
A persistent stored module (PSM) is a block of code containing standard
SQL statements and procedural extensions that is stored and executed at the
DBMS server.
– PSM represents business logic that can be encapsulated, stored, and shared
among multiple database users.
– A PSM lets an administrator assign specific access rights to a stored module
to ensure that only authorized users can use it.

●
The most useful feature of PL/SQL blocks is that they let you create code that
can be named, stored, and executed—either implicitly or explicitly—by the
DBMS. That capability is especially desirable when you need to use triggers and
stored procedures.
●
PL/SQL blocks can contain only standard SQL data manipulation language (DML)
commands such as SELECT, INSERT, UPDATE, and DELETE. The use of data
definition language (DDL) commands is not directly supported in a PL/SQL block.

Date: 2025-04-22
 Procedural SQL
●
Procedural Language SQL (PL/SQL) is a language that makes it possible to use
and store procedural code and SQL statements within the database and to
merge SQL and traditional programming constructs, such as variables,
conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops), and
error trapping.
●
The procedural code is executed as a unit by the DBMS when it is invoked
(directly or indirectly) by the end user.
●
Exact syntax of the language is vendor-dependent; in fact, many vendors
enhance their products with proprietary features
●
End users can use PL/SQL to create:
– Anonymous PL/SQL blocks
– Triggers
– Stored procedures
– PL/SQL functions

Date: 2025-04-22
 Procedural SQL
●
You can write a PL/SQL code block by enclosing the commands inside BEGIN and
END clauses.

BEGIN

INSERT INTO VENDOR

VALUES (25772, 'Clue Store', 'I ssac H ayes', '456','323-2009', 'VA', 'N ');

DBM S_OUTPUT.PUT_LINE('N ew Vendor Added!');

END;

NB: The following anonymous PL/SQL block inserts a row in the VENDOR table and

displays the message “New Vendor Added!”

Date: 2025-04-22
 Procedural SQL
DE CL ARE ●
The PL/SQL block starts with the DECLARE
W_P1 N U M BE R(3) := 0;
section, in which you declare the variable
names, the data types, and, if desired, an
W_P2 N U M BE R(3) := 10; initial value.
W_N U M N U M BE R(2) := 0;
●
Supported data types are; CHAR,
VARCHAR2, NUMBER, DATE and %TYPE.
BE GIN ●
A WHILE loop is used. Note the following
WH I L E W_P2 < 300 L OOP
syntax:
WHILE condition LOOP
SE L E CT COU N T(P_CODE) I N TO W_N U M F ROM PL/SQL statements;
PRODU CT
END LOOP
WH E RE P_PRI CE BETWE EN W_P1 AN D W_P2;
●
The SELECT statement uses the INTO
keyword to assign the output of the query
DBM S_OU TPU T .PU T_L I N E('There are ' || W_N U M || '
Products with
to a PL/SQL variable. You can use the INTO
keyword only inside a PL/SQL block of code.
price between ' || W_P1 || ' and ' || W_P2); If the SELECT statement returns more than
W_P1 := W_P2 + 1;
one value, you will get an error.
●
Note the use of the string concatenation
W_P2 := W_P2 + 50; symbol ( || ) to display the output.
E N D L OOP;
●
Each statement inside the PL/SQL code
must end with a semicolon ( ; ).
E N D;

Date: 2025-04-22
 Examples
Refer to the following link on more PL/SQL Anonymous
Blocks;

[Link]

Date: 2025-04-22
 Summary (cont’d.)

• PL/SQL can be used to create triggers, stored

11
© 2013 Cengage Learning. All Rights Reserved. This edition is intended for use outside of the U.S. only, with content that may be different from the U.S.
Edition. May not be scanned, copied, duplicated, or posted to a publicly accessible website, in whole or in part.