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

Declare: Define the cursor.
Open: Open the cursor to establish context.
Fetch: Retrieve data from the cursor into variables.
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.