PL/SQL Fundamentals and Query Structures

Introduction to PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. While SQL is a non-procedural relational database language, PL/SQL introduces procedural capabilities that allow for more complex data processing.

  • Integration: PL/SQL enables the use of SQL statements to manipulate Oracle data alongside flow-of-control statements to process that data.
  • Features: It allows for the declaration of constants and variables, the definition of subprograms (procedures and functions), and the trapping of runtime errors.
  • Synergy: It combines the data manipulating power of SQL with the data processing power of procedural languages.

PL/SQL Architecture and Client/Server Dynamics

Many Oracle applications utilize a client/server architecture where the Oracle database resides on the server and the program making requests resides on the client machine. Programs interacting with the database can be written in C, Java, or PL/SQL.

  • Environment: PL/SQL is not a standalone language; it is a part of the Oracle RDBMS. It can reside in two environments: the client and the server. This makes it easy to move PL/SQL modules between server-side and client-side applications.
  • Server-Side Execution: When the PL/SQL engine is on the server, the entire PL/SQL block is passed to the engine on the Oracle server for processing.
  • Client-Side Execution: When the engine is located on the client (e.g., Oracle Developer Tools), processing occurs on the client. SQL statements embedded within the PL/SQL block are sent to the Oracle server, but if a block contains no SQL, it is executed entirely on the client.

Network Efficiency: PL/SQL vs. SQL

PL/SQL offers significant performance advantages regarding network traffic.

  • Standard SQL Traffic: When a standard SQL statement is issued from a client, it requires a request to the server and a result sent back, totaling two trips on the network.
  • Scaling Issues: Multiples of these statements increase traffic linearly. For instance, four SELECT statements cause eight network trips.
  • PL/SQL Efficiency: If those same four statements are part of a PL/SQL block, the entire block is sent to the server as a single unit. The statements execute at the server, and the result set is returned as a single unit, resulting in only one network trip.

PL/SQL Block Structures

PL/SQL code is organized into blocks, which fall into two primary categories:

  • Anonymous Blocks: These do not have names and cannot be stored in the database for later reference. They are often used for scripts or one-time executions.
  • Named Blocks: These are used for creating subroutines such as procedures, functions, and packages. They can be stored in the database, referenced by name, and can also be defined within anonymous blocks.

The Three Sections of a Block

A PL/SQL block consists of three specific sections, with only one being mandatory:

  1. Declare Section (Optional): Used for defining identifiers such as variables, constants, and cursors.
  2. Executable Section (Mandatory): Contains the statements used to manipulate data and variables. It is bounded by BEGIN and END;.
  3. Exception-Handling Section (Optional): Contains statements executed when a runtime error occurs.
Block Schema
DECLARE 
    Declaration statements
BEGIN
    Executable statements
EXCEPTION
    Exception handling statements
END;

Detailed Block Components and Examples

Declaration Section

This is the first section of a block. It defines variables and constants.

  • Example:sql DECLARE v_first_name VARCHAR2(35); v_last_name VARCHAR2(35); v_counter NUMBER := 0;     
Executable Section

This section follows the declaration and contains statements to manipulate variables and data.

  • Example:sql BEGIN SELECT first_name, last_name INTO v_first_name, v_last_name FROM student WHERE student_id = 123; DBMS_OUTPUT.PUT_LINE ('Student name :' || v_first_name || ' ' || v_last_name); END;     
Exception-Handling Section

This section manages runtime errors that the compiler cannot detect.

  • Example:sql EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (' There is no student with student id 123 '); END;     

Compilation and Execution Process

Every time an anonymous block is executed, it is sent to the PL/SQL engine on the server for compilation. Named blocks are compiled only upon creation or modification.

  • Compilation Steps:
    1. Syntax Checking: Verifying the code for syntax or compilation errors.
    2. Binding: Assigning storage addresses to program variables used to hold data for Oracle.
    3. P-code Generation: Creating a list of instructions for the PL/SQL engine.
  • Status: Successful compilation sets a named block's status to VALID. Failure sets it to INVALID. Both the p-code and status are stored in the database for named blocks.

PL/SQL in SQL*Plus

SQL*Plus is an interactive tool for typing SQL and PL/SQL statements. There are distinct differences in how it handles each:

  • SQL Termination: A semicolon (;) terminates a SQL statement and triggers immediate execution (e.g., SELECT * FROM student;).
  • PL/SQL Termination: In a PL/SQL block, semicolons terminate individual statements within the block but do not terminate the block itself.
  • Block Execution: To signal the end of a PL/SQL block in SQL*Plus, use a period (.) (optional) followed by a forward slash (/) on a new line (required) to execute it.
  • Direct Execution: Scripts saved with a .sql extension can be executed using the @ command (e.g., SQL> @DisplayAge).

Directing and Generating Output

PL/SQL uses the PUT_LINE procedure within the DBMS_OUTPUT package to display results. To see output in SQL*Plus, two steps are necessary:

  1. Enable Output: Run SET SERVEROUTPUT ON; once per session.
  2. Invoke Procedure: Use DBMS_OUTPUT.PUT_LINE(<string>);.
    • Concatenation Example: DBMS_OUTPUT.PUT_LINE('My age is ' || num_age); where || is the concatenation operator.

Substitution Variables

SQLPlus allows input via substitution variables, which are prefixed by ampersands. Note that these are substituted by SQLPlus before the block reaches the database; no memory is allocated for them in the database.

  • Single Ampersand (&): Prompt the user for a value at every occurrence of the variable.
  • Double Ampersand (&&): Prompt the user once and use that same value for all subsequent occurrences in the block.
  • Verification: SET VERIFY OFF; hides the "old" and "new" lines showing the substitution details.
  • Customization: The SET DEFINE command can change the prefix character:
    • SET DEFINE <character> (Cannot be alphanumeric or whitespace).
    • SET DEFINE ON (Resets to ampersand).
    • SET DEFINE OFF (Disables substitution).

Fundamentals of PL/SQL Programming

Capitalization Styles
Item TypeCapitalizationExample
Reserved wordUppercaseBEGIN, DECLARE
Built-in functionUppercaseCOUNT, TO_DATE
Predefined data typeUppercaseVARCHAR2, NUMBER
SQL commandUppercaseSELECT, INSERT
Database objectLowercasestudent, f_id
Variable nameLowercasecurrent_s_id, current_f_last
Variables and Data Types

Variables must be declared before use using the syntax: variable_name data_type_declaration;.

Scalar Data Types (Single Value)
  • VARCHAR2: Variable-length character string (e.g., current_s_last VARCHAR2(30);).
  • CHAR: Fixed-length character string (e.g., student_gender CHAR(1);).
  • DATE: Date and time (e.g., todays_date DATE;).
  • INTERVAL: Periods of time (Year to Month or Day to Second).
  • NUMBER: Numeric values with precision and scale (e.g., current_price NUMBER(5, 2);).
  • BINARY_INTEGER / INTEGER / INT / SMALLINT: Integer subtypes.
  • DECIMAL / NUMERIC / REAL: Decimal subtypes.
  • BOOLEAN: True/False values.
Composite and Reference Variables
  • RECORD: Contains multiple scalar values, similar to a database row.
  • TABLE: Tabular structure with columns and rows.
  • VARRAY: Variable-sized array.
  • %TYPE: Sets a variable to the same data type as a specific database field.
  • %ROWTYPE: Sets a variable to the same data type as an entire database record (row).
Operators and Precedence

Arithmetic operators in order of precedence:

  1. Exponentiation: ** (e.g., 23=82^{3} = 8)
  2. Multiplication and Division: *, / (e.g., 2×3=62 \times 3 = 6, 9/2=4.59 / 2 = 4.5)
  3. Addition and Subtraction: +, - (e.g., 3+2=53 + 2 = 5)
  4. Negation: - (e.g., −5-5)
Assignment

Values are assigned using the := operator.

  • variable_name := value; (e.g., current_s_first_name := 'John'; or current_s_first_name := s_first_name;).

Character String Manipulation and Functions

  • Concatenation: Use || (e.g., new_string := string1 || string2;).
  • Trimming: LTRIM removes leading spaces; RTRIM removes trailing spaces.
  • Length: LENGTH(string_variable_name) returns the number of characters.
  • Case Change: UPPER, LOWER, and INITCAP.
  • Search: INSTR(original_string, substring) returns the start position of a substring.
  • Extraction: SUBSTR(string_variable, starting_point, number_of_characters) extracts a portion of a string.

Data Conversion Functions

FunctionDescriptionExample
TO_CHARConverts number/date to stringTO_CHAR(2.98, '$999.99')
TO_DATEConverts string to dateTO_DATE('07/14/2003', 'MM/DD/YYYY')
TO_NUMBERConverts string to numberTO_NUMBER('2')

Decision Control Structures

PL/SQL uses IF structures to execute conditional logic. If a condition evaluates to NULL, it is treated as FALSE.

  • IF/THEN: Executes code only if true.
  • IF/THEN/ELSE: Executes one block if true, another if false.
  • IF/ELSIF: Evaluates multiple conditions sequentially.
  • Complex Conditions: Uses logical operators AND, OR, and NOT. AND is evaluated before OR unless parentheses are used.

Loop Structures

Loops repeat statements until an exit condition is met.

  • Pretest Loop: Evaluates the exit condition before any commands inside the loop are executed.
  • Posttest Loop: Executes loop commands at least once before evaluating the exit condition.
Standard Loop Types
  1. Simple Loop:sql LOOP statements; EXIT WHEN condition; END LOOP; &nbsp;&nbsp;&nbsp;&nbsp;
  2. WHILE Loop (Pretest):sql WHILE condition LOOP statements; END LOOP; &nbsp;&nbsp;&nbsp;&nbsp;
  3. Numeric FOR Loop:sql FOR counter_variable IN start_value .. end_value LOOP statements; END LOOP; &nbsp;&nbsp;&nbsp;&nbsp;
Loop Labels

Loops can be labeled to improve readability, especially when nesting. Labels are enclosed in double angle brackets (<<label_name>>).

  • Example:sql <<outer_loop>> FOR i IN 1..3 LOOP <<inner_loop>> FOR j IN 1..2 LOOP DBMS_OUTPUT.PUT_LINE('i=' || i || ' j=' || j); END LOOP inner_loop; END LOOP outer_loop; &nbsp;&nbsp;&nbsp;&nbsp;

Commands and Comments

  • Comments:
    • Single line: --
    • Multi-line: /* comment */
  • SQL in PL/SQL: Action queries (INSERT, UPDATE, DELETE) and SELECT statements (specifically SELECT ... INTO) function normally. However, Data Definition Language (DDL) commands (like CREATE or DROP) may not be used directly in PL/SQL.