Scribd
Upload
7 views3 pages

SQL Cursor Examples and Exception Handling

The document contains four PL/SQL blocks demonstrating different cursor operations. The first block uses an explicit cursor to fetch and display employee details based on salary and hire date. The second block shows a parameterized cursor for fetching employees with salaries above a specified amount, while the third block updates employee salaries based on conditions using a cursor with a FOR loop, and the fourth block handles exceptions when querying employee details based on a substitution variable.

Uploaded by

Oğuzhan Elmas
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views3 pages

SQL Cursor Examples and Exception Handling

The document contains four PL/SQL blocks demonstrating different cursor operations. The first block uses an explicit cursor to fetch and display employee details based on salary and hire date. The second block shows a parameterized cursor for fetching employees with salaries above a specified amount, while the third block updates employee salaries based on conditions using a cursor with a FOR loop, and the fourth block handles exceptions when querying employee details based on a substitution variable.

Uploaded by

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

1.

Explicit Cursor Block


DECLARE
CURSOR c_emp IS
SELECT last_name, first_name, salary, hire_date
FROM employees;

v_last employees.last_name%TYPE;
v_first employees.first_name%TYPE;
v_sal [Link]%TYPE;
v_hdate employees.hire_date%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_last, v_first, v_sal, v_hdate;
EXIT WHEN c_emp%NOTFOUND;

IF v_sal > 50000


AND v_hdate < TO_DATE('31-DEC-2012','DD-MON-YYYY') THEN
DBMS_OUTPUT.PUT_LINE(
v_last || ', ' || v_first ||
' ' || v_sal || ' ' || v_hdate
);
END IF;
END LOOP;
CLOSE c_emp;
END;
/

2. Cursor with Parameter Block


DECLARE
CURSOR c_emp(p_salary [Link]%TYPE) IS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > p_salary;

v_id employees.employee_id%TYPE;
v_fn employees.first_name%TYPE;
v_ln employees.last_name%TYPE;
v_sal [Link]%TYPE;
BEGIN
OPEN c_emp(60000);
LOOP
FETCH c_emp INTO v_id, v_fn, v_ln, v_sal;
EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(
v_id || ' ' || v_fn || ' ' || v_ln || ' ' || v_sal
);
END LOOP;
CLOSE c_emp;
END;
/

3. Salary Update Cursor Block (FOR LOOP


+ FOR UPDATE)
DECLARE
CURSOR c_sal IS
SELECT employee_id, salary
FROM employees
WHERE department_id = 10
FOR UPDATE OF salary;

v_new_salary [Link]%TYPE;
BEGIN
FOR r IN c_sal LOOP
IF [Link] < 100000 THEN
v_new_salary := [Link] * 1.15;
ELSE
v_new_salary := [Link] * 1.10;
END IF;

UPDATE employees
SET salary = v_new_salary
WHERE CURRENT OF c_sal;

DBMS_OUTPUT.PUT_LINE(
r.employee_id || ' ' || [Link] || ' ' || v_new_salary
);
END LOOP;
END;
/

4. Substitution Variable + Exception


Handling Block
DECLARE
v_qid employees.qualification_id%TYPE := &qualification_id;

v_fn employees.first_name%TYPE;
v_ln employees.last_name%TYPE;
v_sal [Link]%TYPE;
BEGIN
SELECT first_name, last_name, salary
INTO v_fn, v_ln, v_sal
FROM employees
WHERE qualification_id = v_qid;

DBMS_OUTPUT.PUT_LINE(
v_fn || ' ' || v_ln || ' ' || v_sal || ' ' || v_qid
);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many rows.');
END;
/

You might also like