Unit 6: PL/SQL

Comprehensive Summary of Chapter 6: PL/SQL

This chapter provides an in-depth study of PL/SQL (Procedural Language/Structured Query Language), Oracle's procedural extension to standard SQL [7, p. 227].

1. Introduction and Architecture of PL/SQL

• What is PL/SQL? PL/SQL is a block-structured language that enables developers to combine the data manipulation power of SQL with standard procedural statements (like loops, conditions, and variables) [7, p. 227, 247]. It is a superset of SQL [7, p. 227, 247].

• PL/SQL Architecture: The architecture consists of three core components [7, p. 227]:

  1. PL/SQL Block: The basic structural coding unit containing the actual program logic [7, p. 227, 229].

  2. PL/SQL Engine: The component that processes the procedural statements and sends the SQL statements to the SQL statement executor [7, p. 227].

  3. Database Server: The database engine that executes the SQL queries [7, p. 227].

• Performance Advantage: Instead of passing SQL statements to the Oracle database one at a time, PL/SQL sends an entire block of statements at once, significantly increasing processing speeds and slashing network traffic [7, p. 227, 230].

2. SQL vs. PL/SQL and Core Benefits

• Core Benefits of PL/SQL:

  • Better Performance: Executes statements in bulk rather than one-by-line [7, p. 228].

  • High Productivity & Portability: Programs can run on any hardware platform or operating system where Oracle is operational [7, p. 228, 247].

  • Tight Security & Error Checking: Provides highly secure execution and extensive routines to handle runtime exceptions [7, p. 228].

• Key Differences:

  • Standard SQL has no procedural capabilities (no conditional branching, loops, or variables) [7, p. 230].

  • PL/SQL provides complete error-handling routines (exceptions) that SQL lacks [7, p. 230].

  • SQL statements are executed individually, consuming more database engine resources than bulk PL/SQL blocks [7, p. 230].

3. PL/SQL Block Structure

A PL/SQL program is made up of logical blocks, which can be nested within one another [7, p. 229]. A standard block contains four sections [7, p. 229, 247]:

1. DECLARE (Optional): Used to declare variables, constants, records, and cursors, which temporarily store data [7, p. 229].

2. BEGIN (Mandatory): Marks the start of the executable section where program logic (loops, conditions, and DML/DDL queries) is written [7, p. 229].

3. EXCEPTION (Optional): Contains statements executed to handle specific errors that occur during execution [7, p. 229, 230].

4. END (Mandatory): Marks the end of the block [7, p. 229]. A slash (/) is placed after the END; statement to execute the code in SQL*Plus [7, p. 231, 232].

4. Variable Declarations and Identifiers

• Variables must be declared in the DECLARE section before they are used, specifying their name and data type (such as INTEGER, REAL, or VARCHAR2) [7, p. 231].

• The standard syntax is: variable_name datatype [NOT NULL := value]; [7, p. 231].

• PL/SQL is case-insensitive, meaning lowercase and uppercase letters are treated identically outside of string and character literals [7, p. 229].

• Administrators run the command SET SERVEROUTPUT ON; to display screen outputs generated by dbms_output [7, p. 231].

5. Control Flow & Selection Structures

PL/SQL supports selection control structures using IF Statements [7, p. 232]. There are three forms [7, p. 232, 233]:

• IF-THEN: The simplest form; executes statements enclosed by THEN and END IF only if the condition evaluates to TRUE [7, p. 233].

• IF-THEN-ELSE: Chooses between two mutually exclusive actions based on whether the condition evaluates to TRUE or FALSE/NULL [7, p. 234].

• IF-THEN-ELSIF: Handles multiple alternative conditions sequentially, executing the first block whose condition is TRUE [7, p. 235, 236].

6. Iterative Control (Loop Structures)

To repeat a sequence of statements, PL/SQL provides three loop options [7, p. 237]:

1. Basic Loop: An infinite loop structure starting with LOOP and ending with END LOOP; [7, p. 237]. It requires an internal conditional statement containing EXIT or EXIT WHEN to stop execution [7, p. 238].

2. FOR Loop: Iterates over a specified range of integers [7, p. 239]. The loop variable is implicitly declared and automatically incremented, and can run in descending order using the REVERSE keyword [7, p. 239, 240].

3. WHILE Loop: An entry-conditioned loop that evaluates its conditional criteria before executing the loop body [7, p. 241]. If the condition is false initially, the loop will not execute even once [7, p. 241].

7. Database Triggers

• Definition: A trigger is a named PL/SQL block stored in the database that is automatically "fired" (executed) when a database event occurs [7, p. 243, 246].

• Triggering Events: Triggers can be executed in response to [7, p. 243]:

  • DML Actions: INSERT, UPDATE, or DELETE.

  • DDL Actions: CREATE, ALTER, or DROP.

  • Database Operations: SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN.

• Execution Rules: Triggers can be row-level (fires once for each affected row) or statement-level (fires once per statement, regardless of rows affected) [7, p. 244]. They can run BEFORE, AFTER, or INSTEAD OF the triggering statement [7, p. 244].

• Benefits: Useful for generating derived columns, enforcing referential integrity, auditing, event logging, and security authorizations [7, p. 243].

8. Cursors for Multi-Row Processing

• Definition: A cursor is a name assigned to a private work area (context area) allocated by Oracle to process SQL query results row-by-row [7, p. 244].

• Implicit Cursors: Automatically created by PL/SQL for single-row queries and DML operations [7, p. 245]. Programmers monitor implicit cursors using attributes like %FOUND (returns TRUE if rows are affected), %NOTFOUND, %ISOPEN, and %ROWCOUNT (returns the number of rows affected) [7, p. 245].

• Explicit Cursors: Manually defined by developers for multi-row queries [7, p. 245]. They require a four-step cycle [7, p. 245, 246]:

  1. DECLARE: Names the cursor and binds it to a SELECT query [7, p. 245, 246].

  2. OPEN: Allocates memory and identifies the active row set [7, p. 245, 246].

  3. FETCH: Retrieves the data row-by-row into variables [7, p. 245, 246].

  4. CLOSE: Releases the context area memory [7, p. 245, 246].

9. Triggers vs. Cursors Comparison

• Triggers: Stored blocks in the database that cannot take parameters, are invoked automatically by database events, and are used to enforce security and data integrity [7, p. 246].

• Cursors: Temporary workspace areas that can take parameters, are invoked explicitly or implicitly, and are used to sequentially process query results [7, p. 246].

💻 Would you like me to generate a tailored report summarizing the complete code examples and program explanations for the basic, FOR, and WHILE loops shown in this chapter?