Unit 5: PL/SQL Programming

Why This Chapter Pays Your Salary

SQL is declarative — you tell the database what you want. PL/SQL is procedural — you tell the database how to do it. Every bank, insurance company, telecom, and government system in India runs on PL/SQL stored procedures and triggers. SBI alone has 50,000+ stored procedures managing ₹45 lakh crore in assets. If you work in enterprise IT in India, PL/SQL is not optional.

Learning Outcomes — Bloom's Taxonomy

Concept Explanations

3.1 PL/SQL Block Structure

PL/SQL — Block Structure
-- ═══════════════════════════════════════
-- Anonymous Block: Hello World
-- ═══════════════════════════════════════

DECLARE                          -- (Optional) Variable declarations
    v_student_name  VARCHAR2(50);
    v_total_marks   NUMBER(5,2);
    v_pass          BOOLEAN := FALSE;
BEGIN                             -- (Required) Executable statements
    v_student_name := 'Rahul Sharma';
    v_total_marks  := 87.5;

    IF v_total_marks >= 40 THEN
        v_pass := TRUE;
        DBMS_OUTPUT.PUT_LINE(v_student_name || ' PASSED with ' || v_total_marks || '%');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_student_name || ' FAILED');
    END IF;
EXCEPTION                        -- (Optional) Error handling
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Output: Rahul Sharma PASSED with 87.5%

3.2 Variables, Constants & Data Types

PL/SQL — Variables & Types
DECLARE
    -- Scalar variables
    v_emp_id        NUMBER(10)       := 1001;
    v_emp_name      VARCHAR2(100)   := 'Priya Patel';
    v_salary        NUMBER(10,2)    := 75000.00;
    v_join_date     DATE            := SYSDATE;
    v_is_active     BOOLEAN         := TRUE;

    -- Constants (cannot be changed after declaration)
    c_tax_rate      CONSTANT NUMBER := 0.30;
    c_company       CONSTANT VARCHAR2(20) := 'TCS';

    -- %TYPE: anchored to a table column's data type (RECOMMENDED)
    v_cust_name     customers.customer_name%TYPE;    -- Same type as the column
    v_price         products.price%TYPE;

    -- %ROWTYPE: entire row structure of a table
    v_emp_record    employees%ROWTYPE;    -- Has .emp_id, .name, .salary, etc.

    -- User-defined record type
    TYPE t_address IS RECORD (
        street   VARCHAR2(100),
        city     VARCHAR2(50),
        pincode  VARCHAR2(6)
    );
    v_addr  t_address;
BEGIN
    -- Using %TYPE: if column type changes, your code adapts automatically
    SELECT customer_name INTO v_cust_name FROM customers WHERE customer_id = 1;

    -- Using %ROWTYPE: fetch entire row
    SELECT * INTO v_emp_record FROM employees WHERE emp_id = 1001;
    DBMS_OUTPUT.PUT_LINE(v_emp_record.emp_name || ' earns ' || v_emp_record.salary);

    -- Record type usage
    v_addr.street  := 'MG Road';
    v_addr.city    := 'Bangalore';
    v_addr.pincode := '560001';
END;
/

3.3 Control Structures — IF, CASE, Loops

PL/SQL — Control Structures
DECLARE
    v_marks   NUMBER := 72;
    v_grade   VARCHAR2(2);
    v_counter NUMBER;
BEGIN
    -- ═══ IF-ELSIF-ELSE ═══
    IF v_marks >= 90 THEN
        v_grade := 'A+';
    ELSIF v_marks >= 80 THEN     -- Note: ELSIF not ELSEIF
        v_grade := 'A';
    ELSIF v_marks >= 70 THEN
        v_grade := 'B+';
    ELSIF v_marks >= 60 THEN
        v_grade := 'B';
    ELSE
        v_grade := 'F';
    END IF;

    -- ═══ CASE Expression ═══
    v_grade := CASE
        WHEN v_marks >= 90 THEN 'A+'
        WHEN v_marks >= 80 THEN 'A'
        WHEN v_marks >= 70 THEN 'B+'
        ELSE 'F'
    END;

    -- ═══ BASIC LOOP (loop...exit when) ═══
    v_counter := 1;
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;   -- Exit condition (mandatory!)
    END LOOP;

    -- ═══ WHILE LOOP ═══
    v_counter := 1;
    WHILE v_counter <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('While: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;

    -- ═══ FOR LOOP (most common) ═══
    FOR i IN 1..5 LOOP            -- i is auto-declared, 1 to 5 inclusive
        DBMS_OUTPUT.PUT_LINE('For: ' || i);
    END LOOP;

    -- ═══ FOR LOOP (reverse) ═══
    FOR i IN REVERSE 1..5 LOOP    -- 5 down to 1
        DBMS_OUTPUT.PUT_LINE('Reverse: ' || i);
    END LOOP;
END;
/

3.4 Cursors — Row-by-Row Processing

Implicit Cursor

PL/SQL — Implicit Cursor
-- Oracle automatically creates an implicit cursor for every DML/SELECT INTO
DECLARE
    v_name  customers.customer_name%TYPE;
    v_rows  NUMBER;
BEGIN
    -- SELECT INTO creates an implicit cursor
    SELECT customer_name INTO v_name
    FROM customers WHERE customer_id = 1;
    DBMS_OUTPUT.PUT_LINE('Customer: ' || v_name);

    -- UPDATE creates an implicit cursor too
    UPDATE products SET price = price * 1.10 WHERE category_id = 1;

    -- Implicit cursor attributes (use SQL% prefix)
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' products updated');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No products found in category 1');
    END IF;
END;
/

Explicit Cursor

PL/SQL — Explicit Cursor (Full Lifecycle)
-- ═══════════════════════════════════════
-- Business Context: Generate invoice for all delivered orders
-- ═══════════════════════════════════════
DECLARE
    -- Step 1: DECLARE the cursor
    CURSOR cur_delivered_orders IS
        SELECT o.order_id, c.customer_name,
               SUM(oi.quantity * oi.unit_price) AS total
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status = 'DELIVERED'
        GROUP BY o.order_id, c.customer_name;

    v_order_id      orders.order_id%TYPE;
    v_cust_name     customers.customer_name%TYPE;
    v_total         NUMBER(12,2);
    v_invoice_count NUMBER := 0;
BEGIN
    -- Step 2: OPEN the cursor (executes the query)
    OPEN cur_delivered_orders;

    -- Step 3: FETCH rows one by one
    LOOP
        FETCH cur_delivered_orders INTO v_order_id, v_cust_name, v_total;
        EXIT WHEN cur_delivered_orders%NOTFOUND;   -- Exit when no more rows

        v_invoice_count := v_invoice_count + 1;
        DBMS_OUTPUT.PUT_LINE(
            'Invoice #' || v_invoice_count ||
            ' | Order: ' || v_order_id ||
            ' | Customer: ' || v_cust_name ||
            ' | Total: ₹' || TO_CHAR(v_total, '99,99,999.00')
        );
    END LOOP;

    -- Step 4: CLOSE the cursor (release memory)
    CLOSE cur_delivered_orders;

    DBMS_OUTPUT.PUT_LINE('Total invoices generated: ' || v_invoice_count);
END;
/

Cursor FOR Loop (RECOMMENDED)

PL/SQL — Cursor FOR Loop
-- The cursor FOR loop auto-handles OPEN, FETCH, CLOSE, and %NOTFOUND
-- MUCH cleaner than explicit OPEN/FETCH/CLOSE

BEGIN
    FOR rec IN (
        SELECT o.order_id, c.customer_name,
               SUM(oi.quantity * oi.unit_price) AS total
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status = 'DELIVERED'
        GROUP BY o.order_id, c.customer_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Order ' || rec.order_id || ' — ' || rec.customer_name ||
            ' — ₹' || rec.total
        );
    END LOOP;  -- Auto-closes cursor, handles empty result set gracefully
END;
/

Cursor Attributes

3.5 Exception Handling

PL/SQL — Exception Handling
-- ═══════════════════════════════════════
-- Business Context: Safe customer lookup with error handling
-- ═══════════════════════════════════════
DECLARE
    v_name  customers.customer_name%TYPE;
    v_bal   NUMBER;
BEGIN
    SELECT customer_name INTO v_name
    FROM customers WHERE customer_id = 999;   -- Doesn't exist!

    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Customer 999 not found!');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query returned multiple rows — use a cursor!');

    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division by zero error');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLCODE || ' — ' || SQLERRM);
        RAISE;  -- Re-raise the exception to the calling environment
END;
/

Predefined Oracle Exceptions

User-Defined Exceptions

PL/SQL — Custom Exceptions
DECLARE
    e_insufficient_balance  EXCEPTION;             -- Declare custom exception
    e_daily_limit_exceeded  EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_daily_limit_exceeded, -20001);  -- Map to error code

    v_balance  NUMBER := 5000;
    v_withdraw NUMBER := 8000;
BEGIN
    IF v_withdraw > v_balance THEN
        RAISE e_insufficient_balance;              -- Raise custom exception
    END IF;

    -- Alternative: RAISE_APPLICATION_ERROR (sends error to client)
    -- RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance: ₹' || v_balance);

EXCEPTION
    WHEN e_insufficient_balance THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Balance ₹' || v_balance ||
            ' insufficient for withdrawal ₹' || v_withdraw);
END;
/

3.6 Stored Procedures & Functions

PL/SQL — Stored Procedure
-- ═══════════════════════════════════════
-- Business Context: Bank fund transfer procedure
-- Used by: SBI ATM network, mobile banking apps
-- ═══════════════════════════════════════
CREATE OR REPLACE PROCEDURE sp_transfer_funds (
    p_from_acc   IN  NUMBER,             -- IN: input parameter
    p_to_acc     IN  NUMBER,
    p_amount     IN  NUMBER,
    p_status     OUT VARCHAR2,           -- OUT: output parameter
    p_new_bal    OUT NUMBER
) AS
    v_from_balance  NUMBER;
    e_insufficient  EXCEPTION;
BEGIN
    -- Check source balance
    SELECT balance INTO v_from_balance
    FROM accounts WHERE account_id = p_from_acc
    FOR UPDATE;  -- Lock the row to prevent concurrent modification

    IF v_from_balance < p_amount THEN
        RAISE e_insufficient;
    END IF;

    -- Debit source
    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_from_acc;

    -- Credit destination
    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_to_acc;

    -- Log transaction
    INSERT INTO transactions (from_acc, to_acc, amount, txn_date, txn_type)
    VALUES (p_from_acc, p_to_acc, p_amount, SYSDATE, 'TRANSFER');

    COMMIT;
    p_status  := 'SUCCESS';
    p_new_bal := v_from_balance - p_amount;

EXCEPTION
    WHEN e_insufficient THEN
        ROLLBACK;
        p_status := 'FAILED: Insufficient balance';
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        p_status := 'FAILED: Account not found';
    WHEN OTHERS THEN
        ROLLBACK;
        p_status := 'FAILED: ' || SQLERRM;
END sp_transfer_funds;
/

-- Execute the procedure:
DECLARE
    v_status  VARCHAR2(200);
    v_bal     NUMBER;
BEGIN
    sp_transfer_funds(1001, 1002, 5000, v_status, v_bal);
    DBMS_OUTPUT.PUT_LINE(v_status || ' | New Balance: ₹' || v_bal);
END;
/

Functions (Return a Single Value)

PL/SQL — Function
-- ═══════════════════════════════════════
-- Business Context: Calculate GST for an invoice
-- ═══════════════════════════════════════
CREATE OR REPLACE FUNCTION fn_calc_gst (
    p_amount     IN NUMBER,
    p_gst_slab   IN VARCHAR2 DEFAULT '18'  -- Default 18%
) RETURN NUMBER
AS
    v_rate  NUMBER;
    v_gst   NUMBER;
BEGIN
    v_rate := CASE p_gst_slab
        WHEN '0'  THEN 0      -- Essential goods
        WHEN '5'  THEN 0.05   -- Food, transport
        WHEN '12' THEN 0.12   -- Business class
        WHEN '18' THEN 0.18   -- Standard (most services)
        WHEN '28' THEN 0.28   -- Luxury (cars, tobacco)
        ELSE 0.18
    END;

    v_gst := ROUND(p_amount * v_rate, 2);
    RETURN v_gst;
END fn_calc_gst;
/

-- Use function in SQL (unlike procedures):
SELECT product_name, price,
       fn_calc_gst(price, '18') AS gst_amount,
       price + fn_calc_gst(price, '18') AS total_with_gst
FROM products;

Procedure vs Function

3.7 Triggers — Automatic Actions on Data Changes

PL/SQL — Trigger Types
-- ═══════════════════════════════════════
-- BEFORE ROW trigger: Audit trail for salary changes
-- Business Context: HR audit — log every salary modification
-- ═══════════════════════════════════════
CREATE OR REPLACE TRIGGER trg_salary_audit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary != NEW.salary)   -- Only when salary actually changes
BEGIN
    INSERT INTO salary_audit_log (
        emp_id, old_salary, new_salary,
        changed_by, changed_at, change_pct
    ) VALUES (
        :OLD.emp_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE,
        ROUND((:NEW.salary - :OLD.salary) * 100 / :OLD.salary, 2)
    );

    -- Business rule: prevent salary decrease > 20%
    IF :NEW.salary < :OLD.salary * 0.80 THEN
        RAISE_APPLICATION_ERROR(-20002,
            'Salary decrease cannot exceed 20%. Old: ₹' || :OLD.salary ||
            ', New: ₹' || :NEW.salary);
    END IF;
END;
/

-- ═══════════════════════════════════════
-- AFTER INSERT trigger: Auto-generate order confirmation
-- ═══════════════════════════════════════
CREATE OR REPLACE TRIGGER trg_order_confirm
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO notifications (customer_id, message, created_at)
    VALUES (
        :NEW.customer_id,
        'Order #' || :NEW.order_id || ' placed successfully!',
        SYSDATE
    );
END;
/

-- ═══════════════════════════════════════
-- INSTEAD OF trigger: Enable INSERT on a complex view
-- ═══════════════════════════════════════
CREATE OR REPLACE TRIGGER trg_order_view_insert
INSTEAD OF INSERT ON v_order_summary
FOR EACH ROW
BEGIN
    INSERT INTO orders (order_id, customer_id, order_date, status)
    VALUES (:NEW.order_id, :NEW.customer_id, SYSDATE, 'PENDING');
END;
/

Trigger Timing & Types

3.8 Packages — Organizing PL/SQL Code

PL/SQL — Package Specification
-- ═══════════════════════════════════════
-- Package: Banking operations module
-- Specification = public interface (what others can see)
-- ═══════════════════════════════════════
CREATE OR REPLACE PACKAGE pkg_banking AS
    -- Public constants
    c_min_balance  CONSTANT NUMBER := 1000;
    c_max_withdraw CONSTANT NUMBER := 200000;

    -- Public procedures
    PROCEDURE deposit    (p_acc_id IN NUMBER, p_amount IN NUMBER);
    PROCEDURE withdraw   (p_acc_id IN NUMBER, p_amount IN NUMBER);
    PROCEDURE transfer   (p_from IN NUMBER, p_to IN NUMBER, p_amt IN NUMBER);

    -- Public function
    FUNCTION  get_balance (p_acc_id IN NUMBER) RETURN NUMBER;
END pkg_banking;
/

PL/SQL — Package Body
CREATE OR REPLACE PACKAGE BODY pkg_banking AS

    -- Private helper (not in specification — hidden from callers)
    PROCEDURE log_transaction(p_acc NUMBER, p_type VARCHAR2, p_amt NUMBER) IS
    BEGIN
        INSERT INTO txn_log (account_id, txn_type, amount, txn_date)
        VALUES (p_acc, p_type, p_amt, SYSDATE);
    END;

    PROCEDURE deposit(p_acc_id IN NUMBER, p_amount IN NUMBER) IS
    BEGIN
        UPDATE accounts SET balance = balance + p_amount
        WHERE account_id = p_acc_id;
        log_transaction(p_acc_id, 'DEPOSIT', p_amount);
        COMMIT;
    END;

    PROCEDURE withdraw(p_acc_id IN NUMBER, p_amount IN NUMBER) IS
        v_bal NUMBER;
    BEGIN
        SELECT balance INTO v_bal FROM accounts
        WHERE account_id = p_acc_id FOR UPDATE;

        IF p_amount > c_max_withdraw THEN
            RAISE_APPLICATION_ERROR(-20003, 'Exceeds daily withdrawal limit');
        ELSIF (v_bal - p_amount) < c_min_balance THEN
            RAISE_APPLICATION_ERROR(-20004, 'Minimum balance ₹' || c_min_balance || ' required');
        END IF;

        UPDATE accounts SET balance = balance - p_amount
        WHERE account_id = p_acc_id;
        log_transaction(p_acc_id, 'WITHDRAW', p_amount);
        COMMIT;
    END;

    PROCEDURE transfer(p_from IN NUMBER, p_to IN NUMBER, p_amt IN NUMBER) IS
    BEGIN
        withdraw(p_from, p_amt);
        deposit(p_to, p_amt);
    END;

    FUNCTION get_balance(p_acc_id IN NUMBER) RETURN NUMBER IS
        v_bal NUMBER;
    BEGIN
        SELECT balance INTO v_bal FROM accounts WHERE account_id = p_acc_id;
        RETURN v_bal;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN -1;
    END;

END pkg_banking;
/

-- Usage:
BEGIN
    pkg_banking.deposit(1001, 25000);
    pkg_banking.withdraw(1001, 10000);
    DBMS_OUTPUT.PUT_LINE('Balance: ₹' || pkg_banking.get_balance(1001));
END;
/

Industry Problems

PL/SQL
CREATE OR REPLACE PROCEDURE sp_atm_withdraw (
    p_card_no    IN  VARCHAR2,
    p_pin        IN  VARCHAR2,
    p_amount     IN  NUMBER,
    p_status     OUT VARCHAR2,
    p_balance    OUT NUMBER
) AS
    v_acc_id          NUMBER;
    v_stored_pin      VARCHAR2(64);
    v_balance         NUMBER;
    v_today_withdrawn NUMBER;
    c_daily_limit     CONSTANT NUMBER := 200000;
    c_min_balance     CONSTANT NUMBER := 1000;
BEGIN
    -- Step 1: Validate card and PIN
    SELECT account_id, pin_hash INTO v_acc_id, v_stored_pin
    FROM atm_cards WHERE card_number = p_card_no AND is_active = 'Y';

    IF v_stored_pin != DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(p_pin), 2) THEN
        p_status := 'FAILED: Invalid PIN';
        RETURN;
    END IF;

    -- Step 2: Check balance
    SELECT balance INTO v_balance FROM accounts
    WHERE account_id = v_acc_id FOR UPDATE;

    IF (v_balance - p_amount) < c_min_balance THEN
        p_status := 'FAILED: Insufficient balance';
        RETURN;
    END IF;

    -- Step 3: Check daily limit
    SELECT NVL(SUM(amount), 0) INTO v_today_withdrawn
    FROM transactions
    WHERE account_id = v_acc_id
      AND txn_type = 'ATM_WITHDRAW'
      AND TRUNC(txn_date) = TRUNC(SYSDATE);

    IF (v_today_withdrawn + p_amount) > c_daily_limit THEN
        p_status := 'FAILED: Daily limit ₹' || c_daily_limit || ' exceeded';
        RETURN;
    END IF;

    -- Step 4: Debit and log
    UPDATE accounts SET balance = balance - p_amount WHERE account_id = v_acc_id;

    INSERT INTO transactions (account_id, txn_type, amount, txn_date, channel)
    VALUES (v_acc_id, 'ATM_WITHDRAW', p_amount, SYSDATE, 'ATM');

    COMMIT;
    p_status  := 'SUCCESS';
    p_balance := v_balance - p_amount;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_status := 'FAILED: Invalid card';
    WHEN OTHERS THEN
        ROLLBACK;
        p_status := 'FAILED: System error — ' || SQLERRM;
END;
/

PL/SQL
CREATE OR REPLACE PROCEDURE sp_process_waitlist (
    p_train_no    IN VARCHAR2,
    p_journey_dt  IN DATE,
    p_class       IN VARCHAR2,
    p_seats_freed IN NUMBER
) AS
    CURSOR cur_waitlist IS
        SELECT pnr, passenger_name, phone
        FROM bookings
        WHERE train_no = p_train_no
          AND journey_date = p_journey_dt
          AND class = p_class
          AND status = 'WL'
        ORDER BY wl_number ASC   -- First waitlisted first
        FOR UPDATE OF status;    -- Lock rows for update
    v_confirmed NUMBER := 0;
BEGIN
    FOR rec IN cur_waitlist LOOP
        EXIT WHEN v_confirmed >= p_seats_freed;

        UPDATE bookings SET status = 'CNF'
        WHERE CURRENT OF cur_waitlist;

        -- Queue SMS notification
        INSERT INTO sms_queue (phone, message, status)
        VALUES (rec.phone,
            'PNR ' || rec.pnr || ' confirmed! Train ' || p_train_no,
            'PENDING');

        v_confirmed := v_confirmed + 1;
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE(v_confirmed || ' passengers confirmed from waitlist');
END;
/

PL/SQL
-- Function: Generate financial year invoice number
CREATE OR REPLACE FUNCTION fn_next_invoice_no RETURN VARCHAR2 AS
    v_fy     VARCHAR2(7);
    v_seq    NUMBER;
BEGIN
    -- Determine financial year (April-March in India)
    IF EXTRACT(MONTH FROM SYSDATE) >= 4 THEN
        v_fy := TO_CHAR(SYSDATE,'YYYY') || '-' || SUBSTR(TO_CHAR(SYSDATE,'YYYY')+1,3);
    ELSE
        v_fy := TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1) || '-' || TO_CHAR(SYSDATE,'YY');
    END IF;

    SELECT NVL(MAX(seq_no),0) + 1 INTO v_seq
    FROM invoices WHERE fy = v_fy;

    RETURN 'INV/' || v_fy || '/' || LPAD(v_seq, 5, '0');
END;
/

-- Trigger: Auto-calculate GST on invoice item insert
CREATE OR REPLACE TRIGGER trg_calc_gst
BEFORE INSERT ON invoice_items
FOR EACH ROW
BEGIN
    :NEW.taxable_amount := :NEW.quantity * :NEW.unit_price;
    :NEW.gst_amount     := ROUND(:NEW.taxable_amount * :NEW.gst_rate / 100, 2);
    :NEW.cgst           := ROUND(:NEW.gst_amount / 2, 2);  -- Intra-state
    :NEW.sgst           := :NEW.cgst;
    :NEW.total_amount   := :NEW.taxable_amount + :NEW.gst_amount;
END;
/

Lab Exercises

MCQ Assessment Bank — 15 Questions

Hover to reveal answer and explanation.

Chapter Summary

BLOCK:    DECLARE ... BEGIN ... EXCEPTION ... END; /

VARIABLE: v_name datatype [:= value];
ANCHORED: v_name table.column%TYPE;
ROW:      v_rec  table%ROWTYPE;

IF:       IF cond THEN ... ELSIF cond THEN ... ELSE ... END IF;
FOR:      FOR i IN 1..n LOOP ... END LOOP;
WHILE:    WHILE cond LOOP ... END LOOP;
CURSOR:   FOR rec IN (SELECT ...) LOOP ... END LOOP;

PROCEDURE: CREATE OR REPLACE PROCEDURE name(p IN type, p OUT type)
           AS ... BEGIN ... EXCEPTION ... END;
FUNCTION:  CREATE OR REPLACE FUNCTION name(p IN type) RETURN type
           AS ... BEGIN ... RETURN val; END;

TRIGGER:   CREATE TRIGGER name BEFORE|AFTER INSERT|UPDATE|DELETE
           ON table FOR EACH ROW BEGIN ... END;
           Access: :OLD.col, :NEW.col
           Detect: IF INSERTING / UPDATING / DELETING

PACKAGE:   CREATE PACKAGE name AS ... END;
           CREATE PACKAGE BODY name AS ... END;

EXCEPTIONS: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX
            RAISE_APPLICATION_ERROR(-20001, 'message');

Interview & Career Preparation