Total Pageviews

May 1, 2016

5/01/2016 11:07:00 PM
  1. 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;

  1. 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;
                   Cursor with subqueries

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;