Experiment No 21: Create Implicit and Explicit Cursors.

 Experiment No 21: Create Implicit and Explicit Cursors.

Result:

The PL/SQL programs using implicit and explicit cursors were executed successfully, demonstrating their role in handling and processing query results.

Q1. Distinguish between Implicit and Explicit Cursors in Oracle.

Aspect	Implicit Cursor	Explicit Cursor
Declaration	Created automatically by Oracle for every DML statement (INSERT, UPDATE, DELETE, SELECT INTO).	Must be declared explicitly by the programmer in the PL/SQL block.
Control	Oracle controls opening, fetching, and closing automatically.	Programmer must explicitly OPEN, FETCH, and CLOSE the cursor.
Result Set	Used when SQL statements return only one row.	Used when SQL statements return multiple rows.
Attributes	Can use attributes like %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.	Can also use the same attributes, but only after explicit declaration.
Use Case	Suitable for simple queries and single-row DML operations.	Suitable for complex queries requiring row-by-row processing.

---

Q2. List advantages of using cursors in SQL programming.

1. Row-by-row processing – Allows handling of multiple rows returned by a query one at a time.

2. Fine-grained control – Enables programmers to apply conditions, calculations, or validations on each row.

3. Improved readability – Makes PL/SQL code structured and easier to understand.

4. Reusability – Explicit cursors can be declared and reused within the program.

5. Error handling – Cursor attributes help in tracking the success or failure of SQL statements.

---

Exercise

-- Enable DBMS_OUTPUT to view results
SET SERVEROUTPUT ON;

-- ============================================
-- 1. Table: STUDENT_DEPT (for Exercise a)
-- ============================================
DROP TABLE STUDENT_DEPT PURGE;

CREATE TABLE STUDENT_DEPT (
    ROLL_NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(50),
    DEPT VARCHAR2(20),
    YEAR NUMBER
);

-- Sample Data
INSERT INTO STUDENT_DEPT VALUES (1, 'Rohan Patil', 'Computer', 2);
INSERT INTO STUDENT_DEPT VALUES (2, 'Sonal Verma', 'Electronics', 3);
INSERT INTO STUDENT_DEPT VALUES (3, 'Amit Sharma', 'Computer', 1);
INSERT INTO STUDENT_DEPT VALUES (4, 'Pooja Gupta', 'Mechanical', 2);
INSERT INTO STUDENT_DEPT VALUES (5, 'Neha Joshi', 'Computer', 3);

COMMIT;

-- ============================================
-- Exercise a: Display Computer Department Students
-- ============================================
DECLARE
    CURSOR comp_cursor IS
        SELECT roll_no, name, dept, year
        FROM STUDENT_DEPT
        WHERE dept = 'Computer';
    
    v_roll_no STUDENT_DEPT.roll_no%TYPE;
    v_name STUDENT_DEPT.name%TYPE;
    v_dept STUDENT_DEPT.dept%TYPE;
    v_year STUDENT_DEPT.year%TYPE;
BEGIN
    OPEN comp_cursor;
    LOOP
        FETCH comp_cursor INTO v_roll_no, v_name, v_dept, v_year;
        EXIT WHEN comp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Roll No: ' || v_roll_no || 
                             ', Name: ' || v_name || 
                             ', Dept: ' || v_dept || 
                             ', Year: ' || v_year);
    END LOOP;
    CLOSE comp_cursor;
END;
/

-- ============================================
-- 2. Table: STUDENT (for Exercise b)
-- ============================================
DROP TABLE STUDENT PURGE;

CREATE TABLE STUDENT (
    ROLL_NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(50)
);

-- Sample Data
INSERT INTO STUDENT VALUES (1, 'Rohit Deshmukh');
INSERT INTO STUDENT VALUES (2, 'Sneha Patil');
INSERT INTO STUDENT VALUES (3, 'Aditya Kulkarni');
INSERT INTO STUDENT VALUES (4, 'Anjali Raut');
INSERT INTO STUDENT VALUES (5, 'Kiran More');
INSERT INTO STUDENT VALUES (6, 'Pratiksha Yadav');

COMMIT;

-- ============================================
-- Exercise b: Print Even Roll Number Students
-- ============================================
DECLARE
    CURSOR stud_cursor IS
        SELECT roll_no, name FROM STUDENT;
    
    v_roll_no STUDENT.roll_no%TYPE;
    v_name STUDENT.name%TYPE;
BEGIN
    OPEN stud_cursor;
    LOOP
        FETCH stud_cursor INTO v_roll_no, v_name;
        EXIT WHEN stud_cursor%NOTFOUND;
        IF MOD(v_roll_no, 2) = 0 THEN
            DBMS_OUTPUT.PUT_LINE('Roll No: ' || v_roll_no || ', Name: ' || v_name);
        END IF;
    END LOOP;
    CLOSE stud_cursor;
END;
/

-- ============================================
-- 3. Table: STORE (for Exercise c)
-- ============================================
DROP TABLE STORE PURGE;

CREATE TABLE STORE (
    ITEM_ID NUMBER PRIMARY KEY,
    ITEM_NAME VARCHAR2(50),
    PRICE NUMBER
);

-- Sample Data
INSERT INTO STORE VALUES (1, 'Laptop', 45000);
INSERT INTO STORE VALUES (2, 'Smartphone', 25000);
INSERT INTO STORE VALUES (3, 'Printer', 8000);
INSERT INTO STORE VALUES (4, 'Desk Chair', 12000);
INSERT INTO STORE VALUES (5, 'Headphones', 5000);
INSERT INTO STORE VALUES (6, 'Tablet', 15000);

COMMIT;

-- ============================================
-- Exercise c: Count Items with Price > 10000
-- ============================================
DECLARE
    CURSOR store_cursor IS
        SELECT item_id, item_name, price
        FROM STORE
        WHERE price > 10000;

    v_item_id STORE.item_id%TYPE;
    v_item_name STORE.item_name%TYPE;
    v_price STORE.price%TYPE;

    count_items NUMBER := 0;
BEGIN
    OPEN store_cursor;
    LOOP
        FETCH store_cursor INTO v_item_id, v_item_name, v_price;
        EXIT WHEN store_cursor%NOTFOUND;
        count_items := count_items + 1;
    END LOOP;
    CLOSE store_cursor;

    DBMS_OUTPUT.PUT_LINE('Number of items with price > 10000: ' || count_items);
END;
/

---