PL/SQL Introduction and Basics

Overview

• Discussing PL/SQL (Procedural Language extensions to SQL).
• Importance and applications in 2020 and beyond.
• Key features: control statements, loops, exception handling, etc.

What is PL/SQL?

• PL/SQL stands for Procedural Language extensions to SQL.
• Integrates SQL with procedural features of programming languages.
• Allows defining variables, control structures (loops, conditional statements).

Advantages

• Portable across different operating systems.
• High-performance and secure for transaction processing.
• Main uses: large data handling, exception handling.
• Versatile in enterprise business applications.

Core Concepts Covered

• Data Types: Numeric, Character, Date, etc.
• Structures: Variables, Constants, Conditional Statements, and Loops.

Data Types

• Simple: Represents single values (e.g., NUMBER, VARCHAR2).
• Composite: Multiple values (e.g., RECORD, COLLECTIONS).
• Large Object (LOB): Stores large data such as images, texts.
• Reference: Pointers to other data items.

Basic Structure

• DECLARE: Declare variables/constants.
• BEGIN: Start execution block.
• EXCEPTION: Handle errors (optional).
• END: End of execution block.

Example Structure

DECLARE
  message VARCHAR2(20);
BEGIN
  message := 'Hello, World!';
  DBMS_OUTPUT.PUT_LINE(message);
END;

String Functions

• Concatenation, Substring, Length, etc.
• Handling upper and lower case conversions.

Example

DECLARE
  greetings VARCHAR2(50) := 'Hello World';
BEGIN
  DBMS_OUTPUT.PUT_LINE(UPPER(greetings)); -- Output: HELLO WORLD
END;

Conditional Statements

• IF-THEN: Basic conditional logic.
• IF-THEN-ELSE: Provides alternative path.
• CASE: Similar to switch-case statements in other languages.

Example

DECLARE
  color VARCHAR2(10) := 'Red';
BEGIN
  IF color = 'Red' THEN
    DBMS_OUTPUT.PUT_LINE('Color is Red');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Color is not Red');
  END IF;
END;

Loops

• Basic Loop: Repeat until condition met.
• WHILE Loop: Repeat as long as condition is true.
• FOR Loop: Iterate over range of values.

Example

DECLARE
  counter NUMBER := 0;
BEGIN
  FOR i IN 1..5 LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
  END LOOP;
END;

PL/SQL Procedures

• Procedures: Block of code performing an action, can be called with parameters.
• Functions: Similar to procedures but return a value.

Procedure Example

CREATE OR REPLACE PROCEDURE add_record (p_id IN NUMBER, p_name IN VARCHAR2) IS
BEGIN
  INSERT INTO users (id, name) VALUES (p_id, p_name);
END;

Function Example

CREATE OR REPLACE FUNCTION calculate_bonus (salary NUMBER) RETURN NUMBER IS
  bonus NUMBER;
BEGIN
  bonus := salary * 0.10;
  RETURN bonus;
END;

Cursors

• Cursors: Handle data retrievals in PL/SQL.
• Types: Implicit (automatically handled by PL/SQL), Explicit (defined by the programmer).

Cursor Lifecycle

1. Declare: Define the cursor.
2. Open: Open the cursor to establish context.
3. Fetch: Retrieve data from the cursor into variables.
4. Close: Close the cursor when done.

Example

DECLARE
  CURSOR cur IS SELECT id, name FROM users;
  v_id users.id%TYPE;
  v_name users.name%TYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_id, v_name;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
  END LOOP;
  CLOSE cur;
END;

Summary

• PL/SQL enhances SQL by adding procedural constructs.
• Useful for complex database applications and business logic.
• Understanding data types, control structures, and key concepts such as procedures, functions, and cursors are essential.