Scribd
Upload
3 views2 pages

PL/SQL Cursor Examples and Usage

The document contains PL/SQL cursor programs for various tasks. It includes a program to display details of students in the Computer department, print even-numbered records from the student table, and count items with a price greater than 10,000 in a store table. Each program utilizes a cursor to fetch and process data accordingly.

Uploaded by

beladarsonal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

PL/SQL Cursor Examples and Usage

The document contains PL/SQL cursor programs for various tasks. It includes a program to display details of students in the Computer department, print even-numbered records from the student table, and count items with a price greater than 10,000 in a store table. Each program utilizes a cursor to fetch and process data accordingly.

Uploaded by

beladarsonal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

PL/SQL Cursor Programs

a) Display details of students studying in Computer department using cursor


DECLARE
CURSOR comp_cur IS
SELECT roll_no, name, dept
FROM student
WHERE dept = 'Computer';

v_roll student.roll_no%TYPE;
v_name [Link]%TYPE;
v_dept [Link]%TYPE;
BEGIN
OPEN comp_cur;
LOOP
FETCH comp_cur INTO v_roll, v_name, v_dept;
EXIT WHEN comp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Roll No: ' || v_roll ||
', Name: ' || v_name ||
', Dept: ' || v_dept);
END LOOP;
CLOSE comp_cur;
END;
/

b) Print even number of records stored in the student table


DECLARE
CURSOR stu_cur IS
SELECT roll_no, name FROM student;

v_roll student.roll_no%TYPE;
v_name [Link]%TYPE;
rec_count NUMBER := 0;
BEGIN
OPEN stu_cur;
LOOP
FETCH stu_cur INTO v_roll, v_name;
EXIT WHEN stu_cur%NOTFOUND;
rec_count := rec_count + 1;
IF MOD(rec_count, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Roll No: ' || v_roll || ', Name: ' || v_name);
END IF;
END LOOP;
CLOSE stu_cur;
END;
/

c) Display number of items with price > 10000 in store table using cursor
DECLARE
CURSOR store_cur IS
SELECT item_id, item_name, price
FROM store;

v_id store.item_id%TYPE;
v_name store.item_name%TYPE;
v_price [Link]%TYPE;
cnt NUMBER := 0;
BEGIN
OPEN store_cur;
LOOP
FETCH store_cur INTO v_id, v_name, v_price;
EXIT WHEN store_cur%NOTFOUND;
IF v_price > 10000 THEN
cnt := cnt + 1;
END IF;
END LOOP;
CLOSE store_cur;
DBMS_OUTPUT.PUT_LINE('Number of items with price > 10000 = ' || cnt);
END;
/

You might also like