Explicit Cursor and Stored Procedure for Employee Management

Assume you have a table named Employees (emp_id, emp_name, salary, department_id).

Write a PL/SQL block that:

  • Declares an explicit cursor to select all employees where department_id = 10.
  • Increases the salary of each selected employee by 1000.
  • For each employee:
    • If the updated salary is greater than 60000, display:
      "High Salary: <employee_name>"
    • Otherwise, display:
      "Salary Updated for: <employee_name>"
  • Uses proper cursor operations: OPEN, FETCH, and CLOSE.

Now consider another table named Orders (order_id, customer_id, order_date, total_amount).

Write a stored procedure named GetCustomerOrders that:

  • Accepts customer_id as an input parameter.
  • Uses an explicit cursor to retrieve all orders for that customer.
  • Displays order_id, order_date, and total_amount for each order.
  • If the customer has no orders, display:
    "No Orders Found for This Customer".


PL/SQL Explicit Cursor and Stored Procedure – Detailed Answer

Part 1: PL/SQL Block Using Explicit Cursor (Employees Table)

Table: Employees (emp_id, emp_name, salary, department_id)

Requirements:

  • Select all employees where department_id = 10
  • Increase salary by 1000
  • If updated salary exceeds 60000 → Display: "High Salary: <employee_name>"
  • Otherwise → Display: "Salary Updated for: <employee_name>"
  • Use proper cursor operations: OPEN, FETCH, CLOSE

PL/SQL Block

DECLARE
    -- Declare variables
    v_emp_id        Employees.emp_id%TYPE;
    v_emp_name      Employees.emp_name%TYPE;
    v_salary        Employees.salary%TYPE;

    -- Declare explicit cursor
    CURSOR emp_cursor IS
        SELECT emp_id, emp_name, salary
        FROM Employees
        WHERE department_id = 10;

BEGIN
    -- Open cursor
    OPEN emp_cursor;

    LOOP
        -- Fetch data
        FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;

        EXIT WHEN emp_cursor%NOTFOUND;

        -- Increase salary
        v_salary := v_salary + 1000;

        -- Update table
        UPDATE Employees
        SET salary = v_salary
        WHERE emp_id = v_emp_id;

        -- Display message
        IF v_salary > 60000 THEN
            DBMS_OUTPUT.PUT_LINE('High Salary: ' || v_emp_name);
        ELSE
            DBMS_OUTPUT.PUT_LINE('Salary Updated for: ' || v_emp_name);
        END IF;

    END LOOP;

    -- Close cursor
    CLOSE emp_cursor;

    COMMIT;
END;
/

Explanation

  • Cursor Declaration: Selects employees from department 10.
  • OPEN: Starts the cursor.
  • FETCH: Retrieves one record at a time.
  • EXIT WHEN: Stops the loop when no more rows exist.
  • UPDATE: Updates salary in the table.
  • CLOSE: Releases cursor memory.
  • COMMIT: Saves changes permanently.

Part 2: Stored Procedure with Explicit Cursor (Orders Table)

Table: Orders (order_id, customer_id, order_date, total_amount)

Requirements:

  • Procedure Name: GetCustomerOrders
  • Input Parameter: customer_id
  • Retrieve all orders of that customer
  • Display order_id, order_date, total_amount
  • If no orders exist → Display: "No Orders Found for This Customer"

Stored Procedure Code

CREATE OR REPLACE PROCEDURE GetCustomerOrders (
    p_customer_id IN Orders.customer_id%TYPE
)
IS
    v_order_id     Orders.order_id%TYPE;
    v_order_date   Orders.order_date%TYPE;
    v_total_amount Orders.total_amount%TYPE;

    CURSOR order_cursor IS
        SELECT order_id, order_date, total_amount
        FROM Orders
        WHERE customer_id = p_customer_id;

    v_found BOOLEAN := FALSE;

BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO v_order_id, v_order_date, v_total_amount;

        EXIT WHEN order_cursor%NOTFOUND;

        v_found := TRUE;

        DBMS_OUTPUT.PUT_LINE('Order ID: ' || v_order_id ||
                             ' | Order Date: ' || v_order_date ||
                             ' | Total Amount: ' || v_total_amount);
    END LOOP;

    IF NOT v_found THEN
        DBMS_OUTPUT.PUT_LINE('No Orders Found for This Customer');
    END IF;

    CLOSE order_cursor;

END;
/

How to Execute the Procedure

BEGIN
    GetCustomerOrders(101);
END;
/

Key Concepts Used

Concept Purpose
Explicit Cursor Process multiple rows one by one
OPEN Initialize the cursor
FETCH Retrieve row data
CLOSE Release cursor resources
DBMS_OUTPUT Display output message
COMMIT Save changes permanently

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)