Unit 5: Pl-SQL - Subjective Questions

PL/SQL stands for Procedural Language extensions to SQL. It is Oracle's procedural programming language designed to combine SQL statements with programming constructs.

Its relationship with SQL can be explained as follows:

• SQL is used to define, retrieve, and manipulate data in a relational database.
• PL/SQL extends SQL by adding procedural features such as variables, conditions, loops, exceptions, procedures, and functions.
• SQL statements such as SELECT, INSERT, UPDATE, and DELETE can be embedded directly inside a PL/SQL block.
• PL/SQL allows several SQL statements to be grouped and executed as a single logical unit.
• The SQL engine processes SQL statements, while the PL/SQL engine processes procedural statements.

Thus, PL/SQL does not replace SQL; it supplements SQL with procedural capabilities required for implementing complex database applications.

SQL is a declarative language that specifies what data is required, but it provides limited support for describing complex procedural logic. PL/SQL addresses this limitation.

PL/SQL is needed because it provides:

• Conditional execution through IF, ELSIF, ELSE, and CASE statements.
• Repetitive execution through LOOP, WHILE, and FOR loops.
• Variables and constants for temporarily storing values.
• Exception handling for detecting and managing runtime errors.
• Modularity through procedures, functions, packages, and triggers.
• Integration with SQL, allowing data manipulation and procedural logic in the same program.
• Reduced network traffic, because an entire block can be sent to the database server at once.

Therefore, PL/SQL enables developers to implement business rules, validate data, automate operations, and build secure, maintainable database applications.

The major differences between SQL and PL/SQL are:

In summary, SQL performs data-oriented operations, whereas PL/SQL coordinates those operations using procedural programming logic.

A PL/SQL block can contain three sections: declaration, execution, and exception handling.

DECLARE
    -- Variable, constant, cursor, and exception declarations
BEGIN
    -- Executable SQL and PL/SQL statements
EXCEPTION
    -- Exception-handling statements
END;
/

Sections

• DECLARE section: Optional. It contains declarations of variables, constants, cursors, records, and user-defined exceptions.
• BEGIN section: Mandatory. It contains executable SQL and procedural statements. It must contain at least one statement; NULL; can be used when no action is required.
• EXCEPTION section: Optional. It contains handlers for errors raised during execution.
• END;: Mandatory. It marks the end of the block.
• /: In tools such as SQL*Plus, it submits the completed block for execution; it is not part of the PL/SQL language itself.

This structure makes PL/SQL programs organized and supports reliable error management.

A standard PL/SQL block consists of the following sections:

1. Declaration section — optional

   • Begins with DECLARE.
   • Defines local variables, constants, cursors, types, and exceptions.
   • The declared items are generally accessible within that block and its nested sub-blocks.

2. Executable section — mandatory

   • Begins with BEGIN.
   • Contains SQL statements and procedural statements.
   • Statements are executed in sequence unless control structures alter the flow.
   • A block with no real operation may contain NULL; as its executable statement.

3. Exception section — optional

   • Begins with EXCEPTION.
   • Handles errors raised in the executable section.
   • It can include handlers such as WHEN NO_DATA_FOUND THEN and WHEN OTHERS THEN.

4. Block termination — mandatory

   • END; terminates the block.

Therefore, only the executable section and block termination are mandatory; the declaration and exception sections are optional.

A suitable PL/SQL block is:

DECLARE
    v_num1 NUMBER := 10;
    v_num2 NUMBER := 20;
    v_sum  NUMBER;
BEGIN
    v_sum := v_num1 + v_num2;
    DBMS_OUTPUT.PUT_LINE('Sum = ' || v_sum);
END;
/

Explanation

• v_num1 and v_num2 are initialized in the declaration section.
• v_sum is declared without an initial value and is therefore initially NULL.
• The assignment operator := stores the expression v_num1 + v_num2 in v_sum.
• DBMS_OUTPUT.PUT_LINE displays the result.
• The concatenation operator || joins the text with the numeric result.
• END; closes the PL/SQL block.
• The output is Sum = 30, provided server output is enabled in the client tool.

PL/SQL is described as block-structured because a program is divided into logical blocks. Each block may contain declarations, executable statements, and exception handlers.

A block can be placed inside another block, creating a nested block:

DECLARE
    v_outer NUMBER := 10;
BEGIN
    DECLARE
        v_inner NUMBER := 20;
    BEGIN
        DBMS_OUTPUT.PUT_LINE(v_outer + v_inner);
    END;
END;
/

Important characteristics include:

• The outside block is the outer block, and the enclosed block is the inner block.
• An inner block can normally access identifiers declared in the outer block.
• An outer block cannot access identifiers declared only in an inner block.
• Each nested block can have its own exception-handling section.
• Nested blocks help divide complex tasks into smaller logical units.
• They also limit the scope and lifetime of local variables.

Block structure therefore improves organization, readability, modularity, and error isolation.

The major benefits of PL/SQL include:

• Tight SQL integration: SQL statements can be used directly with procedural code.
• Improved performance: Several statements can be sent to the server and executed as one block, reducing communication overhead.
• Reduced network traffic: A complete block generally requires fewer client-server exchanges than many separate SQL statements.
• Modularity: Code can be organized into blocks, procedures, functions, packages, and triggers.
• Error handling: The exception mechanism allows errors to be detected and handled systematically.
• Portability within Oracle environments: PL/SQL programs can operate on Oracle platforms without major platform-specific changes.
• Security: Users can be granted permission to execute stored programs without being given direct access to underlying tables.
• Maintainability: Centralized stored code can be modified without changing every client application.
• Productivity: Variables, loops, conditions, and reusable components simplify business-rule implementation.

These benefits make PL/SQL suitable for transaction processing and enterprise database applications.

When separate SQL statements are issued from a client, each statement may require a request to the database server and a response to the client. These repeated exchanges add network and processing overhead.

PL/SQL improves this process by allowing multiple SQL and procedural statements to be grouped into one block:

• The client sends the complete PL/SQL block in a single request.
• The PL/SQL engine executes procedural instructions on the server.
• Embedded SQL statements are passed to the SQL engine.
• Intermediate processing can remain on the server.
• Only the required final results or status information need to be returned.

Additional performance benefits may arise from:

• Reusing stored procedures and packages.
• Avoiding repeated transfer of business logic.
• Processing data close to where it is stored.
• Supporting bulk-processing facilities in more advanced PL/SQL programs.

The exact performance gain depends on the application, but reducing client-server round trips is a major advantage.

Oracle uses cooperating engines to execute a PL/SQL block:

• The PL/SQL engine processes procedural statements such as assignments, conditions, loops, and exception-handling constructs.
• When it encounters an embedded SQL statement, it passes that statement to the SQL engine.
• The SQL engine parses, optimizes, and executes data definition, retrieval, or manipulation operations.
• The SQL engine returns the result or status to the PL/SQL engine.
• The PL/SQL engine then continues with the remaining procedural statements.

For example, in a block containing an IF statement and an UPDATE, the PL/SQL engine evaluates the condition, while the SQL engine performs the update.

This cooperation provides:

• Direct integration of database operations with programming logic.
• Efficient server-side processing.
• The ability to treat a group of statements as one logical program unit.

Thus, procedural processing and data-oriented processing remain specialized but closely integrated.

PL/SQL blocks can broadly be classified as anonymous blocks and named blocks.

Anonymous blocks

• Have no stored program name.
• Are usually created and executed interactively or from an application.
• Are not normally stored as independent schema objects.
• Must be sent again when they need to be executed again.
• Are useful for testing, one-time processing, and administrative tasks.

Named blocks

Named blocks include procedures, functions, packages, and triggers.

• They have a name or are defined as part of a named database object.
• They are stored in the database in compiled form.
• They can be invoked or activated repeatedly, depending on their type.
• They support modularity, reuse, centralized maintenance, and security.

For example, a procedure is explicitly called, whereas a database trigger is automatically activated by a specified event. Both categories use the basic PL/SQL block structure, although their headers and invocation mechanisms differ.

Variables and constants are normally declared in the DECLARE section before they are used.

Variable declaration

v_salary NUMBER(10, 2) := 25000;
v_name   VARCHAR2(50);

The general form is:

identifier datatype [NOT NULL] [:= initial_value];

A variable can be changed during execution using := or by receiving a value from a query.

Constant declaration

c_tax_rate CONSTANT NUMBER := 0.10;

A constant:

• Must include the CONSTANT keyword.
• Is assigned an initial value.
• Cannot be assigned another value later in the block.

Usage

v_salary := v_salary + (v_salary * c_tax_rate);

Variables hold changing program data, while constants represent fixed values. Meaningful names and suitable datatypes improve readability and reduce conversion errors.

%TYPE and %ROWTYPE allow declarations to be based on database definitions or existing PL/SQL items.

%TYPE

%TYPE declares an item with the datatype of a table column or another variable:

v_salary employees.salary%TYPE;

Here, v_salary receives a datatype compatible with the salary column.

%ROWTYPE

%ROWTYPE declares a record capable of holding an entire row:

v_employee employees%ROWTYPE;

Individual fields can be accessed using dot notation, such as v_employee.employee_id.

Advantages

• Avoids hard-coding column datatypes.
• Reduces the risk of datatype mismatches.
• Makes code easier to maintain when column definitions change.
• %ROWTYPE provides a convenient record for handling a complete row.
• Improves consistency between program variables and database columns.

However, constraints and default values associated with a column are not necessarily inherited in the same way as its datatype, so developers must still apply required validations.

The executable section starts with BEGIN and contains statements that perform actions. PL/SQL executes them sequentially unless a control structure changes the flow.

Examples of executable statements include:

• Variable assignments.
• SQL statements such as SELECT INTO, INSERT, UPDATE, and DELETE.
• Procedure calls.
• Conditional statements.
• Loop statements.
• The NULL statement.

An assignment uses the := operator:

v_total := v_price * v_quantity;

This differs from the equality comparison operator = used in conditions and SQL predicates.

A simple example is:

DECLARE
    v_price    NUMBER := 50;
    v_quantity NUMBER := 4;
    v_total    NUMBER;
BEGIN
    v_total := v_price * v_quantity;
    DBMS_OUTPUT.PUT_LINE('Total = ' || v_total);
END;
/

The expression is evaluated first, and its result is then assigned to v_total.

SQL statements can be written directly in the executable section of a PL/SQL block. A query returning a single row usually places its values into variables through SELECT INTO.

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = 101;

    UPDATE employees
    SET salary = v_salary * 1.10
    WHERE employee_id = 101;
END;
/

Explanation

• SELECT INTO retrieves the salary and stores it in v_salary.
• A single-row SELECT INTO should return exactly one row.
• No matching row raises NO_DATA_FOUND.
• More than one matching row raises TOO_MANY_ROWS.
• The UPDATE statement uses the PL/SQL variable in its expression.
• SQL statements end with semicolons like other PL/SQL statements.

This example demonstrates how SQL performs data operations while PL/SQL variables and logic coordinate those operations.

An exception is a runtime error or abnormal condition that interrupts normal execution. PL/SQL provides an optional EXCEPTION section for responding to such conditions.

Basic structure:

BEGIN
    -- Executable statements
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Corrective action
    WHEN OTHERS THEN
        -- General error handling
END;
/

Important points are:

• An exception raised in the executable section transfers control to the exception section.
• Each handler starts with WHEN and specifies an exception name.
• Predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.
• WHEN OTHERS catches exceptions not handled by earlier clauses and should normally appear last.
• After a local exception handler finishes, execution does not resume at the failed statement; the block terminates and control returns to its caller or enclosing context.

Exception handling improves reliability by allowing applications to report errors, perform corrective actions, or preserve consistent behavior instead of terminating without explanation.

A block that handles division by zero is:

DECLARE
    v_numerator   NUMBER := 100;
    v_denominator NUMBER := 0;
    v_result      NUMBER;
BEGIN
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE('Result = ' || v_result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: division by zero is not allowed.');
END;
/

Flow of execution

1. The variables are declared and initialized.
2. Execution enters the BEGIN section.
3. The division operation attempts to divide by zero.
4. Oracle raises the predefined ZERO_DIVIDE exception.
5. Normal execution of the remaining statements in the executable section stops.
6. Control transfers to the matching WHEN ZERO_DIVIDE handler.
7. The handler displays an explanatory message.
8. The block ends after the handler completes.

The block therefore manages the error in a controlled manner rather than allowing an unhandled exception to propagate immediately.

PL/SQL supports software engineering principles through its block-based and stored-program architecture.

Modularity

• A large application can be divided into smaller blocks, procedures, functions, and packages.
• Each module can perform a specific task.
• Modules can be tested and understood independently.

Maintainability

• Common database logic can be centralized on the server.
• A change to a stored program may benefit all applications that invoke it.
• Structured declarations, executable logic, and exception handling make responsibilities clearer.
• Packages can organize related declarations and implementations.

Reusability

• Procedures and functions can be called multiple times.
• Shared business rules do not need to be rewritten in each application.
• Packages provide reusable interfaces and related utilities.
• Reuse can reduce duplication and inconsistencies.

Together, these features lower development effort, promote uniform business rules, simplify testing, and improve the long-term quality of database applications.

PL/SQL can strengthen security and integrity by placing controlled program logic between users and database tables.

Security

• Users may receive EXECUTE privilege on a stored procedure without receiving broad direct privileges on its underlying tables, subject to Oracle's privilege model.
• Stored programs expose only approved operations through defined parameters and interfaces.
• Sensitive implementation details can be centralized rather than duplicated in client applications.
• Packages can provide a public interface while keeping implementation details in the package body.

Data integrity

• Procedures and triggers can enforce business rules consistently.
• Input values can be validated before data manipulation occurs.
• Exception handlers can respond to invalid conditions and database errors.
• Related statements can be coordinated as part of a transaction.
• Centralized logic reduces the possibility that different applications will apply conflicting rules.

PL/SQL complements, rather than replaces, declarative controls such as primary keys, foreign keys, CHECK constraints, and database privileges.

The following block demonstrates declarations, SQL integration, calculation, output, and exception handling:

DECLARE
    v_employee_id employees.employee_id%TYPE := 101;
    v_salary      employees.salary%TYPE;
    v_bonus       employees.salary%TYPE;
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = v_employee_id;

    v_bonus := v_salary * 0.10;

    UPDATE employees
    SET salary = salary + v_bonus
    WHERE employee_id = v_employee_id;

    DBMS_OUTPUT.PUT_LINE(
        'Updated salary = ' || (v_salary + v_bonus)
    );
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee was found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('The query returned multiple employees.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation

• %TYPE keeps variable datatypes compatible with table columns.
• SELECT INTO retrieves one employee's salary.
• The bonus is calculated as ten percent of the original salary.
• UPDATE modifies the database row.
• DBMS_OUTPUT.PUT_LINE displays the calculated result.
• Specific exceptions are handled before WHEN OTHERS.
• SQLERRM provides the message associated with an unexpected Oracle error.
• Transaction completion should be controlled deliberately with COMMIT or ROLLBACK according to application requirements; neither is performed automatically by this block.