1 What is PL/SQL?
PL/SQL, which stands for Procedural Language extension to SQL, is a powerful extension to the
standard SQL language developed by Oracle. It allows you to combine SQL statements with
procedural programming constructs like loops, conditional statements, and variables, enabling more
complex logic and functionality within your database interactions.
2 Benefits of Using PL/SQL:
• Incorporates programming logic such as conditional statements (IF), loops, and branching.
• Enables custom error handling through exception management.
• Supports automated responses to database events via triggers.
• Web Application Development: PL/SQL can be used to develop web applications and server
pages, providing a powerful way to interact with databases and build dynamic web content
• Object-Oriented Programming Support: PL/SQL supports object-oriented programming
concepts, allowing you to create modular and reusable code, which can improve maintainability and
reduce development time
• Security: PL/SQL offers various security features, including the ability to control access to
data and prevent unauthorized modifications
• Error Handling: PL/SQL provides robust error handling mechanisms that allow you to trap
and manage runtime errors, preventing unexpected program termination and providing user-friendly
error messages
3. What is the basic structure of a PL/SQL block?
DECLARE
-- Variable and cursor declarations
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling code
END;
A PL/SQL block is composed of three main parts:
1. Declaration Section: Where variables, constants, and cursors are defined.
2. Executable Section: Contains the code that performs operations.
3. Exception Section: Handles runtime errors and exceptions gracefully.
PL/SQL Block Structure Explained
1. Declaration Section:
• This part is where you define local variables, cursors, exceptions, and other elements needed
for your program.
• Everything written between the DECLARE and BEGIN keywords forms the declaration section.
• Note that this section is optional and only included when necessary.
2. Executable Section:
• This is the core part of the block where the actual operations and logic are implemented.
• All code between the BEGIN and EXCEPTION keywords belongs here.
• This section is mandatory for every PL/SQL block.
3. Exception Section:
• If any errors or exceptions occur during execution, control is passed to this section.
• It includes all statements between the EXCEPTION and END keywords.
• This section is optional but useful for handling runtime errors gracefully.
5. What are the basic control structures in PL/SQL?
PL/SQL includes several control structures that help manage the flow of a program:
Loops: These include LOOP, FOR LOOP, and WHILE LOOP, allowing repetitive execution of
statements.
Conditional Statements: These include IF and CASE statements, which execute different blocks of
code based on conditions. The DECODE() function is another good example of a conditional that is
worth studying.
6. what is function ?
function is a named stored subprogram that performs a specific task and always returns a value.
Key Features of a Function in PL/SQL
Returns a value using the RETURN clause.
Can accept parameters (IN, OUT, IN OUT – but usually IN).
Can be called:
In SQL statements (if it does not modify database state).
In PL/SQL blocks, procedures, or other functions.
Useful for reusable logic (e.g., salary calculation, tax computation).
7. what is procedure ?
a procedure is a named stored subprogram that performs a specific task but does not have to return
a value (unlike a function).
Key Features of a Procedure
May accept parameters:
IN → pass values to the procedure.
OUT → return values to the caller.
IN OUT → both pass and return.
Can perform DML operations (INSERT, UPDATE, DELETE).
Cannot be directly used in SQL statements (unlike functions).
Used for business logic, data manipulation, and reusable code blocks.
8. what is package ?
package is a schema object that groups logically related procedures, functions, variables, cursors, and
types into a single unit.
Think of it like a "container" or "module" that organizes PL/SQL code for better reusability, security,
and performance.
A package has two parts:
Package Specification (PACKAGE)
Declares public items (procedures, functions, variables, cursors).
Acts as the interface for users.
Package Body (PACKAGE BODY)
Contains the actual implementation of the procedures and functions.
Can have private declarations (not visible outside).
9. what is trigger ?
trigger is a stored program unit that is automatically executed (fired) in response to a specific event
on a table, view, schema, or database.
Key Points About Triggers
Event-driven → They run automatically when an event occurs (e.g., INSERT, UPDATE, DELETE, DDL, or
system events).
Types of triggers → Based on event and timing.
Used for auditing, enforcing business rules, maintaining logs, and automatic actions.
Types of Triggers:
1. Based on Timing
BEFORE Trigger → Fires before the triggering statement executes.
AFTER Trigger → Fires after the triggering statement executes.
INSTEAD OF Trigger → Used with views (executes instead of DML on a view).
2. Based on Level
Row-level Trigger → Fires once for each row affected.
Statement-level Trigger → Fires once for the whole SQL statement.
3. Based on Event
DML Triggers → On INSERT, UPDATE, DELETE.
DDL Triggers → On CREATE, ALTER, DROP.
System Triggers → On database/system events (e.g., LOGON, LOGOFF, STARTUP).
10. what is cursor ?
cursor is a pointer to the result set of a SQL query.
It allows you to process query results row by row, especially when a query returns multiple rows
(since PL/SQL variables can only hold one row at a time).
Implicit Cursor
Automatically created by Oracle when you execute a single-row SQL statement (like INSERT,
UPDATE, DELETE, or SELECT INTO).
You don’t declare it manually.
Status can be checked using attributes:
%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
Explicit Cursor
Declared by the programmer to handle queries that return multiple rows.
You must open, fetch, and close it.