PL/SQL Fundamentals: Blocks, Identifiers & Constants

TAKEAWAYS:

• PL/SQL blocks are the smallest executable units, structured as DECLARE, BEGIN, and EXCEPTION sections.
• Variables store dynamic data (e.g., salary := 50000), while constants are fixed values (e.g., PI CONSTANT NUMBER := 3.14159).
• Identifiers follow strict naming rules (no spaces, max 30 chars) and must start with a letter or underscore.
• Anonymous blocks execute once (e.g., ad-hoc salary updates), while named blocks (procedures/functions) are reusable.
• Constants improve code reliability by preventing accidental changes, but variables enable dynamic calculations.
• PL/SQL integrates with SQL but extends it with procedural logic (loops, conditionals, error handling).

1. PL/SQL Block Structure

PL/SQL organizes code into blocks, which are self-contained units with three optional sections:

flowchart TD
    A["DECLARE (Optional)"] -->|"Optional"| B["BEGIN (Mandatory)"]
    B --> C["EXCEPTION (Optional)"]
    C -->|"Ends block"| D["END"]

• DECLARE: Defines variables, constants, cursors, and exceptions.
• BEGIN: Contains executable PL/SQL or SQL statements.
• EXCEPTION: Handles runtime errors (e.g., division by zero).

Example: Anonymous Block

DECLARE
    v_emp_name VARCHAR2(50) := 'Ramesh';
    v_salary NUMBER := 30000;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name || ', Salary: ' || v_salary);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Why anonymous? Used for one-time tasks like updating a single record or generating reports.

2. Variables vs. Constants

Worked Example: Dynamic vs. Fixed Discount

-- Variable discount (changes per customer)
DECLARE
    v_discount NUMBER := 0.1; -- 10% off
    v_total NUMBER := 1000;
BEGIN
    v_total := v_total * (1 - v_discount); -- Total after discount
    DBMS_OUTPUT.PUT_LINE('Final Price: ' || v_total);
END;

-- Constant tax rate (fixed by law)
DECLARE
    v_tax_rate CONSTANT NUMBER := 0.13; -- Nepal VAT
    v_amount NUMBER := 500;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Tax: ' || v_amount * v_tax_rate);
END;

3. Identifiers in PL/SQL

Rules for Naming:

1. Must start with a letter or underscore (_).
2. Max 30 characters; no spaces or special chars (except _, $, #).
3. Case-insensitive but conventionally uppercase (e.g., V_SALARY).
4. Cannot be a reserved keyword (e.g., SELECT, WHERE).

Valid vs. Invalid Examples:

Real-World Tie: In eSewa’s transaction system, constants like v_MAX_TRANSACTION := 500000 (₨500,000 limit) prevent fraud by enforcing fixed rules, while variables like v_current_balance update dynamically per user.

4. Anonymous vs. Named Blocks

Mermaid Diagram: Block Types

Example: Named Block (Procedure)

CREATE OR REPLACE PROCEDURE calc_bonus(
    p_emp_id IN NUMBER,
    p_bonus_rate IN NUMBER,
    p_bonus OUT NUMBER
) AS
BEGIN
    SELECT salary * p_bonus_rate INTO p_bonus
    FROM employees
    WHERE emp_id = p_emp_id;
END;

Call the Procedure:

DECLARE
    v_bonus NUMBER;
BEGIN
    calc_bonus(101, 0.2, v_bonus);
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;

5. Constants in PL/SQL

Syntax:

v_constant_name CONSTANT datatype [NOT NULL] := value;

Example: Tax Constants

DECLARE
    v_VAT_RATE CONSTANT NUMBER := 0.13; -- Nepal VAT
    v_SERVICE_TAX CONSTANT NUMBER := 0.05;
    v_MAX_DEDUCTION CONSTANT NUMBER := 50000;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Total Tax: ' ||
        (v_VAT_RATE + v_SERVICE_TAX) * 1000);
END;

Why Use Constants?

• Prevents errors: Accidental changes to tax rates are caught at compile time.
• Improves readability: v_VAT_RATE is clearer than hardcoding 0.13.
• Real-world use: NEPSE uses constants for fixed fees (e.g., v_BROKERAGE_FEE CONSTANT NUMBER := 0.0025).

6. Exam Tip: Common Pitfalls

1. Variables vs. Constants:

   • Mixing them up in exams often leads to losing 1 mark. Always check if the value should change (variable) or stay fixed (constant).
   • Example: v_tax_rate is a constant; v_discount is a variable.

2. Block Structure:

   • Forgetting the DECLARE section or misplacing BEGIN/END costs marks. Always write:

     DECLARE
         -- Variables/constants
     BEGIN
         -- Logic
     EXCEPTION
         -- Error handling
     END;
     

3. Identifier Rules:

   • Naming a variable 1st_quarter (starts with a number) or SELECT (reserved keyword) will fail. Use v_1st_qtr or qtr_select.

4. Anonymous vs. Named:

   • Anonymous blocks are for one-time use; named blocks (procedures/functions) are for reusable logic. Exams often ask to differentiate them.

5. Constants Syntax:

   • Forgetting the CONSTANT keyword or omitting the := for initialization will deduct marks. Example:

     -- Correct:
     v_PI CONSTANT NUMBER := 3.14159;
     -- Incorrect (missing CONSTANT):
     v_PI NUMBER := 3.14159;
     

6. Worked Example Practice:

   • Exams may ask to write a block that calculates Pathao’s surge pricing (e.g., v_base_fare := 100; v_surge_multiplier := 1.5;). Always include:
     • Variable for dynamic data (e.g., v_surge_multiplier).
     • Constant for fixed rules (e.g., v_MIN_FARE CONSTANT NUMBER := 50;).

In the Real World

1. Khalti’s Payment Limits:

   • Uses constants like v_MAX_PAYOUT := 1000000 (₨1M/day limit per user) to enforce fraud prevention rules. Variables like v_current_balance update in real time per transaction.

2. Daraz’s Order Queue:

   • Anonymous blocks process each order dynamically (e.g., checking stock in DECLARE and updating inventory in BEGIN). Named procedures handle bulk discounts (e.g., apply_sale(v_order_id, v_discount_rate)).

3. Ncell’s Call Charges:

   • Constants define fixed rates (e.g., v_ROAMING_CHARGE CONSTANT NUMBER := 5.5; per minute), while variables track dynamic usage (e.g., v_used_minutes).

Exam Tip: Scoring Full Marks

• Definitions: Always include syntax and a brief example (e.g., "A constant is defined as v_const CONSTANT datatype := value;").
• Comparisons: Use tables (like the one above) to show differences between variables/blocks.
• Worked Examples: Show both declaration and usage (e.g., declare v_tax, then use it in a calculation).
• Real-World Tie: Link to apps like eSewa or NEPSE to explain why constants/variables are used (e.g., "eSewa uses constants for transaction limits to prevent fraud").