PL/SQL Exercises with Solution

Last update on March 06 2024 08:03:41 (UTC/GMT +8 hours)

Introduction

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL and Oracle. Exercises are designed to enhance your ability to write well-structured PL/SQL programs. Hope, these exercises help you to improve your PL/SQL query skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

PL/SQL Fundamentals:

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. It enables users to combine SQL statements with procedural constructs, providing capabilities such as variables, loops, conditional statements, and exception handling. PL/SQL facilitates the development of powerful and efficient database applications by allowing developers to encapsulate business logic within the database itself, enhancing performance, security, and maintainability.

Data Types:

Scalar Data Types:

INTEGER: Stores whole numbers within a specified range.
NUMBER: Represents numeric values with optional precision and scale.
VARCHAR2 / CHAR: Used for storing character strings of variable or fixed length, respectively.
BOOLEAN: Holds Boolean values (TRUE, FALSE, or NULL).
DATE / TIMESTAMP: Stores date and time values with optional time zone information.
BINARY_INTEGER: Similar to INTEGER but limited to smaller values.

Composite Data Types:

%TYPE: This pseudo-type is used to declare variables that have the same data type as a specified database column or variable.
%ROWTYPE: Represents a complete row of data in a table or cursor result set.
RECORD: Allows you to define a user-defined data structure with multiple fields of different data types.

Collection Data Types:

Nested Tables: Unordered collections of elements of the same data type.
VARRAY (Variable-size Array): Arrays of a fixed maximum size, storing elements of the same data type.
Associative Arrays (Index-by Tables): Collections of key-value pairs where the key is unique and can be of any scalar data type.

LOB Data Types:

CLOB (Character Large Object): Stores large blocks of character data, such as text documents.
BLOB (Binary Large Object): Holds large blocks of binary data, such as images or multimedia files.
BFILE: Represents binary files stored outside the database.

REF CURSOR: A special data type used to hold the result set returned by a query. It allows dynamic SQL queries to be executed and processed within PL/SQL programs.

Control Statement:

PL/SQL (Procedural Language/Structured Query Language) provides various control structures that allow developers to control the flow of execution within their code. These control statements help in making decisions, looping through code blocks, and handling exceptions. Here's a brief description of PL/SQL control statements:

IF-THEN-ELSE Statement:

The IF-THEN-ELSE statement allows developers to execute a block of code conditionally based on a specified condition.
If the condition evaluates to true, the code inside the THEN block is executed; otherwise, the code inside the ELSE block is executed.

CASE Statement:

The CASE statement evaluates a set of conditions and executes the corresponding code block based on the first condition that evaluates to true.
It is similar to a switch-case statement in other programming languages.

LOOP Statements:

PL/SQL provides various loop statements such as LOOP, WHILE, and FOR loops to iterate over a block of code until a certain condition is met.
The LOOP statement creates an infinite loop that continues until explicitly terminated using an EXIT statement.
The WHILE loop executes a block of code repeatedly as long as a specified condition remains true.
The FOR loop iterates over a range of values or a collection and executes the loop body for each iteration.

EXIT Statements:

EXIT statements are used to exit a loop prematurely based on a specified condition.
They are commonly used within loops to terminate the loop when a certain condition is met.

GOTO Statement:

The GOTO statement allows developers to transfer control to a specified label within the same block, subprogram, or package.
While it provides flexibility, its usage is generally discouraged due to the potential for creating unreadable and unmaintainable code.

Exception Handling:

PL/SQL includes exception handling mechanisms to gracefully handle errors and unexpected conditions that may arise during program execution.
Exception handling blocks such as BEGIN, EXCEPTION, and END allow developers to catch and handle exceptions, ensuring that programs can recover from errors gracefully.

String Functions:

PL/SQL provides a variety of built-in string functions that enable developers to manipulate strings efficiently within their code. These string functions offer capabilities such as concatenation, substring extraction, case conversion, searching, and replacing parts of strings. Here's a brief description of some commonly used PL/SQL string functions:

CONCAT: This function is used to concatenate two or more strings together, producing a single string as the output.
Example: CONCAT('Hello', ' ', 'World') returns 'Hello World'.

SUBSTR: SUBSTR function is used to extract a substring from a string. It takes three arguments: the source string, the starting position, and the length of the substring.

These are just a few examples of PL/SQL string functions. By utilizing these functions effectively, developers can perform various string operations efficiently within their PL/SQL code.

While loop:

In PL/SQL, the WHILE loop is a control structure used to repeatedly execute a block of code as long as a specified condition evaluates to true. It consists of the WHILE keyword followed by a condition. The code within the loop is executed repeatedly until the condition becomes false. The loop continues until the condition evaluates to false, at which point control exits the loop and moves to the next statement following the loop block.

Cursors:

Cursors are used in PL/SQL to retrieve and process multiple rows returned by a SQL query. PL/SQL supports both implicit and explicit cursors. Implicit cursors are automatically created for SQL statements like SELECT INTO, while explicit cursors are declared explicitly using the CURSOR keyword.

Triggers:

Triggers are special types of stored procedures that are automatically executed in response to specific events occurring in the database. These events may include INSERT, UPDATE, or DELETE operations on tables. Triggers are useful for enforcing data integrity rules and implementing complex business logic.

Exception Handling:

Exception handling in PL/SQL allows developers to handle runtime errors gracefully. PL/SQL provides built-in exceptions and allows users to define custom exceptions using the EXCEPTION keyword. Exception handling blocks consist of BEGIN, EXCEPTION, and END keywords.

Packages:

Packages are schema objects that group logically related PL/SQL types, variables, constants, cursors, exceptions, procedures, and functions into a single unit. They provide modularity, encapsulation, and namespace management, making code maintenance easier.

Object Oriented Programming:

PL/SQL (Procedural Language/Structured Query Language) is primarily a procedural language used for writing stored procedures, functions, and triggers within Oracle databases. However, it also supports some aspects of object-oriented programming (OOP) through its object-oriented features. Here's a description of PL/SQL's object-oriented programming capabilities:

User-Defined Types (UDTs):

PL/SQL allows users to define their own abstract data types using the CREATE TYPE statement. These user-defined types can encapsulate both data and methods, similar to classes in object-oriented programming languages.
UDTs can have attributes (data members) and methods (procedures and functions) associated with them.

Object Types:

Object types in PL/SQL are similar to classes in object-oriented programming languages. They encapsulate data and behavior, allowing users to model real-world entities as objects.
An object type consists of attributes (data members) and methods (procedures and functions) that operate on those attributes.
Objects of a particular type can be instantiated, allowing users to create instances (objects) based on the defined type.

Object Views:

Object views provide a way to present relational data as objects, allowing users to interact with relational data using object-oriented concepts.
They map relational tables to object types, enabling applications to access and manipulate relational data as objects.

Inheritance:

PL/SQL supports inheritance, allowing object types to inherit attributes and methods from parent types. This promotes code reuse and facilitates modeling of hierarchical relationships between objects.
Subtypes can extend or override the attributes and methods of their parent types.

Encapsulation:

Encapsulation is a key principle of object-oriented programming, and PL/SQL allows users to encapsulate data and behavior within object types.
By encapsulating data and methods, users can enforce data abstraction and control access to the internal state of objects.

Polymorphism:

Polymorphism refers to the ability of objects to exhibit different behaviors based on their data types or class hierarchy. While PL/SQL does not support dynamic polymorphism like languages such as Java or C++, it does support method overloading and overriding within object types.