PL/SQL: A Comprehensive Guide to Procedural Extensions in SQL

This guide explores PL/SQL (Procedural Language/SQL), an extension of SQL that adds procedural programming capabilities. Learn about its purpose, key characteristics, and understand how PL/SQL enhances SQL's functionality for building complex and robust Oracle database applications. The advantages of combining SQL and procedural programming are highlighted.

PL/SQL Interview Questions and Answers

What is PL/SQL?

Question 1: What is PL/SQL?

PL/SQL (Procedural Language/SQL) is an extension of SQL that adds procedural programming capabilities to the SQL language. It combines the power of SQL for data manipulation with the structure of a procedural language (like C or Java) for creating more complex database applications.

Purpose of PL/SQL

Question 2: Purpose of PL/SQL

PL/SQL addresses the limitations of standard SQL by providing procedural features (loops, conditional statements, exception handling). This makes it possible to write more complex database applications and procedures. It's tightly integrated with the Oracle database.

Characteristics of PL/SQL

Question 3: Characteristics of PL/SQL

Key characteristics:

• Block structure.
• Portability (runs on any system supporting Oracle).
• Integration with the Oracle data dictionary.
• Stored procedures for code reusability.

PL/SQL Tables

Question 4: PL/SQL Tables

PL/SQL tables are array-like structures in memory that are useful for temporarily storing and manipulating collections of data within a PL/SQL block. They're not persistent; the data is lost when the PL/SQL block finishes execution.

Data Types in PL/SQL

Question 5: Data Types in PL/SQL

PL/SQL supports various data types:

• Scalar: NUMBER, VARCHAR2, DATE, BOOLEAN, etc.
• Composite: RECORD, TABLE, etc.

PL/SQL Block Structure

Question 6: Basic Structure of PL/SQL

A PL/SQL block has three sections:

1. Declaration Section (Optional): Declares variables, constants, cursors, etc.
2. Executable Section (Mandatory): Contains PL/SQL and SQL statements.
3. Exception Handling Section (Optional): Handles exceptions that might occur during execution.

Functions, Procedures, and Packages

Question 7: Function, Procedure, and Package

Differences:

Exceptions in PL/SQL

Question 8: Exceptions in PL/SQL

Exceptions handle errors. Types:

• Predefined exceptions (e.g., NO_DATA_FOUND, DUP_VAL_ON_INDEX).
• User-defined exceptions.

String Concatenation

Question 9: String Concatenation in PL/SQL

DECLARE
  Greeting VARCHAR2(20);
BEGIN
  Greeting := 'Hello' || ' World';
  DBMS_OUTPUT.PUT_LINE(Greeting); -- Output: Hello World
END;
/

`CREATE` Command in PL/SQL

Question 10: `CREATE` Command in PL/SQL

PL/SQL does not support the `CREATE` command (for creating database objects). `CREATE` is a DDL (Data Definition Language) command that is handled directly by the SQL engine.

Function vs. Stored Procedure

Question 11: Function vs. Stored Procedure

A function returns a value; a stored procedure does not.

Exception vs. Error

Question 12: Exception vs. Error

An exception is a runtime error condition that can be handled; an error is a more severe problem that often terminates the program.

Indexes

Question 13: Purpose of Indexes

Indexes speed up data retrieval in databases by creating sorted data structures.

Predefined PL/SQL Exceptions

Question 14: Predefined PL/SQL Exceptions

Examples: `TOO_MANY_ROWS`, `NO_DATA_FOUND`, `ZERO_DIVIDE`, `INVALID_NUMBER`.

User-Defined Exceptions

Question 15: Declaring User-Defined Exceptions

DECLARE
  my_exception EXCEPTION;
BEGIN
  -- Code that might raise my_exception
EXCEPTION
  WHEN my_exception THEN
    -- Handle my_exception
END;
/

Triggers in PL/SQL

Question 17: Triggers in PL/SQL

Triggers are stored PL/SQL programs that automatically execute in response to specific events (like `INSERT`, `UPDATE`, `DELETE`) on a table or view.

Maximum Number of Triggers

Question 18: Maximum Number of Triggers per Table

A maximum of 12 triggers per table can be created in PL/SQL.

Types of Triggers

Question 19: Types of Triggers

Triggers can be `BEFORE` or `AFTER` events, at the `ROW` or `STATEMENT` level, and for `INSERT`, `UPDATE`, `DELETE`, or any combination (`INSTEAD OF` triggers are also supported).

Trigger vs. Stored Procedure Execution

Question 20: Trigger vs. Stored Procedure Execution

Triggers execute automatically; stored procedures are explicitly called.

Triggers on Views

Question 21: Triggers on Views

Triggers on views typically fire against the underlying base table(s).

`WHEN` Clause in Triggers

Question 22: `WHEN` Clause in Triggers

The `WHEN` clause in a trigger specifies a condition that must be met for the trigger to execute.

Disabling and Dropping Triggers

Question 23 & 24: Disabling and Dropping Triggers

ALTER TRIGGER update_salary DISABLE;

DROP TRIGGER update_salary;

`NEW` and `OLD` in Triggers

Question 25: Virtual Tables in Triggers

In PL/SQL triggers, `NEW` (holds new values for `INSERT` and `UPDATE`) and `OLD` (holds old values for `UPDATE` and `DELETE`) are pseudo-tables used to access the data that changed.

Stored Procedures

Question 26: Stored Procedures

Stored procedures are pre-compiled SQL code stored in the database. They improve performance and can encapsulate complex database logic.

Schema Objects

Question 27: Schema Objects Created Using PL/SQL

Schema objects you can create: stored procedures, functions, packages, triggers, cursors.

PL/SQL Cursors

Question 28: PL/SQL Cursors

Cursors in PL/SQL manage the result sets of SQL queries. They provide a mechanism for processing multiple rows returned by a query one row at a time.

Implicit vs. Explicit Cursors

Question 29: Implicit vs. Explicit Cursors

Implicit cursors are automatically created and managed by Oracle for single-row DML operations. Explicit cursors are declared and managed explicitly by the programmer for multiple-row queries.

Cursor Attributes

Question 30-32: Cursor Attributes

Cursor attributes (`SQL%ROWCOUNT`, `SQL%FOUND`, `SQL%NOTFOUND`) provide information about the execution of a query.

PL/SQL Packages

Question 33: PL/SQL Packages

Packages group related PL/SQL types, variables, constants, cursors, procedures, and functions into a single unit. This promotes modularity, reusability, and data hiding. Packages consist of a specification (public interface) and a body (implementation).

Deleting Packages

Question 35: Deleting Packages

Use the `DROP PACKAGE` command to delete a package.

Executing Stored Procedures

Question 36: Executing Stored Procedures

Ways to execute a stored procedure:

• EXECUTE procedure_name;
• CALL procedure_name;

Advantages of Stored Procedures

Question 37: Advantages of Stored Procedures

Advantages:

• Modularity
• Improved performance (pre-compilation)
• Increased security
• Code reusability

Syntax Error vs. Runtime Error

Question 39: Syntax Error vs. Runtime Error

In PL/SQL:

• Syntax errors: Detected by the compiler (e.g., incorrect keywords, missing semicolons).
• Runtime errors: Occur during program execution; handled using exception handling (e.g., `NO_DATA_FOUND`, `TOO_MANY_ROWS`).

`COMMIT` Statement

Question 40: `COMMIT` Statement

The `COMMIT` statement in PL/SQL permanently saves changes made during a transaction. After a `COMMIT`, other users see those changes, locks are released, and the changes become permanent in the database.

`ROLLBACK` Statement

Question 41: `ROLLBACK` Statement

The `ROLLBACK` statement undoes all changes made during a transaction. Locks are released, and the database is reverted to its previous state before the transaction began.

`SAVEPOINT` Statement

Question 42: `SAVEPOINT` Statement

A `SAVEPOINT` marks a point within a transaction. You can roll back to a savepoint to undo only part of the transaction.

Mutating Table Error

Question 43: Mutating Table Error

A mutating table error occurs when a trigger tries to modify a table it's currently accessing. This can lead to unpredictable behavior. You can fix this by using views or temporary tables.

Data Consistency

Question 44: Data Consistency

Data consistency means that all users see the same, correct data. Transactions help maintain consistency in database systems.

PL/SQL Cursors

Question 45: Cursors in PL/SQL

A cursor is a temporary work area in memory that holds the results of a SQL query. Cursors are needed to process multiple rows returned by a query, one row at a time.

Types of Cursors

Question 46: Types of Cursors

Two types:

• Implicit Cursors: Automatically managed by Oracle for single-row operations.
• Explicit Cursors: Declared and managed by the programmer for multiple-row operations.

Cursor Attributes

Question 30-32: Cursor Attributes

Cursor attributes provide information about cursor operations:

• %ROWCOUNT: Number of rows affected.
• %FOUND: True if at least one row was fetched.
• %NOTFOUND: True if no rows were fetched.
• %ISOPEN: True if the cursor is open.

PL/SQL Packages

Question 33: PL/SQL Packages

Packages group logically related PL/SQL elements (types, variables, subprograms) into a single unit. This promotes modularity, reusability, and information hiding. Packages have a specification (public interface) and a body (implementation).

Deleting Packages

Question 35: Deleting Packages

Use the DROP PACKAGE statement to remove a package from the database.

Executing Stored Procedures

Question 36: Executing Stored Procedures

You execute a stored procedure using:

• EXECUTE procedure_name;
• CALL procedure_name;

Advantages of Stored Procedures

Question 37: Advantages of Stored Procedures

Advantages include:

• Modularity
• Reusability
• Improved performance (pre-compilation)
• Enhanced security

Predefined Exceptions

Question 14: Predefined Exceptions in PL/SQL

PL/SQL provides predefined exceptions (e.g., `NO_DATA_FOUND`, `TOO_MANY_ROWS`, `ZERO_DIVIDE`). These handle common error conditions.

What is PL/SQL?

Question 1: What is PL/SQL?

PL/SQL (Procedural Language/SQL) extends SQL with procedural programming capabilities. It lets you create more complex database applications by combining the data manipulation power of SQL with the control structures of a procedural language.

Purpose of PL/SQL

Question 2: Purpose of PL/SQL

PL/SQL enhances SQL's capabilities by allowing for complex logic, error handling, and procedural programming structures within the database environment.

PL/SQL Block Structure

Question 6: Basic PL/SQL Block Structure

A PL/SQL block has three parts:

1. Declaration section (optional): Declares variables, constants, cursors, exceptions.
2. Executable section (required): Contains PL/SQL and SQL statements.
3. Exception handling section (optional): Handles exceptions.

Data Types

Question 5: Data Types in PL/SQL

PL/SQL supports scalar (NUMBER, VARCHAR2, DATE, BOOLEAN, etc.) and composite (RECORD, TABLE, etc.) data types.

Mutating Table Error

Question 43: Mutating Table Error

A mutating table error occurs when a trigger tries to modify the same table it's currently accessing. This is usually resolved by using views or temporary tables.

Data Consistency

Question 44: Data Consistency

Data consistency ensures that all users see the same, valid data. Transactions and locking mechanisms are important for maintaining data consistency.

Cursors

Question 45: Cursors

Cursors are used to retrieve and process data from SQL queries that return multiple rows. They provide a mechanism for working with result sets one row at a time.

Cursor Types

Question 46: Types of Cursors

Types of cursors: implicit (automatically handled by Oracle) and explicit (declared and managed by the programmer).

`COPY` Command

Example: Copying Files

The command `cp chap?? progs` copies files named "chap" followed by two characters into the "progs" directory.