Database 2
PL / SQL
LAB 4
Outline
Lab 4 PL/SQL
1 Building PL/SQL Blocks
2 Types of Blocks
3 Variables Data Types
4 Nested Blocks
5 IF / ELSE Keyword
6 Loops
PL/ SQL
PL/SQL is a procedural language designed specifically to embrace
SQL statements within its syntax.
PL/SQL program units are compiled by the Oracle Database server
and stored inside the database.
At run-time, both PL/SQL and SQL run within the same server
process, bringing optimal efficiency.
PL/SQL automatically inherits the robustness, security, and
portability of the Oracle Database.
Building and Managing PL/SQL Program
Units
•Building with Blocks: PL/SQL is a block-structured language; familiarity with
blocks is critical to writing good code.
•Controlling the Flow of Execution: Conditional branching and iterative
processing in PL/SQL.
•Wrap Your Code in a Neat Package: Packages are the fundamental building
blocks of any high quality PL/SQL-based application.
•Picking Your Packages: Concepts and benefits of PL/SQL packages.
•Error Management: An exploration of error management features in PL/SQL.
•The Data Dictionary: Make Views Work for You: Use several key data dictionary
views to analyze and manage your code.
Building blocks of PL/SQL programs
PL/SQL is a block-structured language. A PL/SQL block is defined by the
keywords DECLARE, BEGIN, EXCEPTION, and END, which break up the block into
three sections:
1. Declarative: Statements that declare variables, constants, and other code
elements, which can then be used within that block
2. Executable: Statements that are run when the block is executed
3. Exception handling: A specially structured section you can use to “catch,” or
trap, any exceptions that are raised when the executable section runs.
A block itself is an executable statement, so you can nest blocks within other
blocks.
Types of Blocks
PL/SQL blocks are units of code that can include declarations, executable
statements, exception handlers, and other PL/SQL constructs.
There are several types of PL/SQL blocks, each serving a specific purpose.
1. Anonymous Block:
◦ Not stored in the database and has no name.
◦ Used for one-time execution, such as ad-hoc SQL scripts or quick
procedural tasks.
2. Named Blocks:
◦ Stored in the database and can be executed many times.
Anonymous Block:
Example
set serveroutput on: enable the display of the output generated by
DBMS_OUTPUT.PUT_LINE or other output commands within PL/SQL blocks.
Named Blocks:
Block Name Description
Stored A stored procedure is a named PL/SQL block that is stored in the database
Procedure: for reuse. It can be called explicitly by name.
Function: A function is similar to a stored procedure but returns a value.
It is called as part of an expression.
Trigger: A trigger is a type of PL/SQL block associated with a table or view.
It is automatically executed in response to specific events, such as INSERT,
UPDATE, or DELETE.
Package: A package is a collection of related PL/SQL objects (procedures, functions,
variables, etc.) grouped together.
It provides a way to organize and encapsulate code for modularity and
reusability.
PL/SQL Variables Datatypes
•Working with Strings in PL/SQL: PL/SQL offers several different string datatypes for use in your
applications
•Working with Numbers in PL/SQL: Learn about and how to use the different numeric types in
PL/SQL.
•Working with Dates in PL/SQL Dates are a relatively complex scalar datatype, in both SQL and
PL/SQL
•Working with Records: A very common and useful composite type, PL/SQL’s analogue to a
table’s row
•Error Management: PL/SQL’s implementation of arrays plays a role in almost every key
performance feature of PL/SQL
•Working with Collections: Use several key data dictionary views to analyze and manage your
code
Numbers Data Type Declaration in PL/SQL
PL/SQL features various numeric types for different purposes, including:
NUMBER: A true decimal data type that is ideal for working with monetary
amounts.
PLS_INTEGER: An integer type aligned with hardware's native representation,
utilizing machine instructions for arithmetic. Not suitable for table storage;
exclusive to PL/SQL.
SIMPLE_INTEGER.
BINARY_FLOAT and BINARY_DOUBLE.
Other numeric types like FLOAT, INTEGER, and DECIMAL are encountered, serving
as subtypes of the core numeric types listed above.
Numbers Data Type
Numbers Calculations
Nested Blocks / Global and Local Variable
Nested Blocks / Global and Local Variable
Employee table contain following Data
It will be used in the next examples
Retrieving Data from Table
SELECT INTO statement to retrieve data from a table and assign it to a variable.
SELECT COL_NAME
INTO VARIABLE
FROM TABLE_NAME;
PL / SQL Extension to SQL provide
procedural commands like
Variables
Arrays
If Else Conditions
Loops
Functions
Procedures
Triggers
IF / ELSE Keyword Procedure
IF [(condition)]
THEN
< statement>
ELSIF [(condition)]
THEN
< statement>
ELSE [(condition)]
< statement>
END IF;
Employee table contain following Data
It will be used in the next examples
IF / ELSE Keyword
Update Salary from Department 1 by increasing the rate by 10 %
Executing Procedure
Types of loops:
1. Basic Loop.
2. For Loop.
3. While Loop.
Basic Loop
1-Basic Loop: With each iteration of the basic LOOP statement, its
statements run and control returns to the top of the loop. The LOOP
statement ends when a statement inside the loop transfers control
outside the loop or raises an exception.
Basic Loop Syntax
Begin
Loop
-- Code to execute in each iteration
-- increment statements
EXIT WHEN condition
END LOOP;
END;
Example
For Loop:
2. For Loop: With each iteration of the FOR LOOP statement, its
statements run, its index is either incremented or decremented, and
control returns to the top of the loop.
The FOR LOOP statement ends when its index reaches a specified
value, or when a statement inside the loop transfers control outside
the loop or raises an exception. An index is also called an iterand.
Statements outside the loop cannot reference the iterand. After the
FOR LOOP statement runs, the iterand is undefined.
For Loop:
FOR LOOP
Begin
FOR counter_variable IN start_value .. end_value
Loop
-- Code to execute in each iteration
END LOOP;
END;
FOR LOOP (Increment)
FOR LOOP (REVERSE)
While Loop:
The WHILE LOOP statement runs one or more statements while a
condition is TRUE.
The WHILE LOOP statement ends when the condition becomes
FALSE or NULL, or when a statement inside the loop transfers
control outside the loop or raises an exception.
WHILE LOOP Syntax
Begin
WHILE condition
LOOP
-- Code to be executed
END LOOP;
END;
WHILE LOOP EXAMPLE
Thank you!