- Can we create a cursor without declaring it?
Yes – by using cursor for loop
using subqueries.
BEGIN
FOR emp_record IN ( SELECT empno, ename
FROM emp) LOOP
-- implicit open and implicit fetch
occur
IF emp_record.empno = 7839 THEN
...
END LOOP; -- implicit close occurs
END;
- Can we pass Parameters in cursor ? if yes then how do we populate them?
What is cursor for loop? Why is
WHERE CURRENT OF clause used in cursors?
Yes we can pass as shown below:
CURSOR emp_cursor
(v_dept
NUMBER, v_job VARCHAR2) IS
SELECT last_name, salary,
start_date
FROM s_emp
WHERE dept_id = v_dept
AND title = v_job;
Begin
Open emp_cursor (10,’FACULTY’)
---parameters are passed in open cursor statements
Fetch cursor into v1,v2
Close emp_cursor;
cursor for loop (shortcut for explicit cursors,no need to open,fetch
and close)
CURSOR emp_cursor
(v_dept NUMBER, v_job VARCHAR2) IS
SELECT last_name, salary,
start_date
FROM s_emp
WHERE dept_id = v_dept
AND title = v_job;
Begin
For emp_data in emp_cursor loop –no
need to declare variable emp_data.
--------emp_data.last_name :=
‘sharma’;
End loop;
WHERE
CURRENT OF Clause
•Update or delete the current row
using cursors.
•Lock the rows first by including
the FOR UPDATE clause in the cursor query.
Syntax:
CURSOR emp_cursor IS
SELECT ...
FOR
UPDATE;
BEGIN
...
FOR emp_record IN emp_cursor LOOP
UPDATE ...
WHERE CURRENT OF emp_cursor;
...
END LOOP;
COMMIT;
END;
Retrieve department number,dname and count
of staff from dept and emp table where Count >=5
DECLARE
CURSOR my_cursor IS
SELECT t1.deptno, dname,STAFF
FROM
dept t1, ( SELECT deptno,
count(*) STAFF
FROM emp
GROUP BY deptno)
t2 –retrieves count of staff in each
dept.This query is used in place of a table
WHERE
t1.deptno = t2.deptno
AND
STAFF >= 5;
