Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Friday, 14 July 2017

Oracle PL/SQL Cursor Attributes




Cursor Attributes



Explicit Cursors Attributes
Explicit Cursor: Example -1

DECLARE
   -- Step 1: Cursor declaration
   CURSOR empcur
   IS
      SELECT empno, ename
        FROM emp;
   v_empno   emp.empno%TYPE;
   v_name    emp.ename%TYPE;
BEGIN
   OPEN empcur;                         -- Step2: Open the Cursor for a query
   LOOP
      -- Step 3: Fetch the values from table results into PL/SQL variables.
      FETCH empcur
       INTO v_empno, v_name;
      EXIT WHEN empcur%NOTFOUND;
      DBMS_OUTPUT.put_line(v_empno || ' ' || v_name);
   END LOOP;
   CLOSE empcur;                                   -- Step 4: Close the cursor
END;

Explicit Cursor: using %TYPE and RECORD

DECLARE
   TYPE t_emprec IS RECORD (
      ename   emp.ename%TYPE,
      sal     emp.sal%TYPE,
      job     emp.job%TYPE
   );
   r_emp   t_emprec;
   CURSOR c_emp
   IS
      SELECT ename, sal, job
        FROM emp;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp
       INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.put_line(r_emp.ename || ', ' || r_emp.sal || ', '
                            || r_emp.job
                           );
   END LOOP;
   CLOSE c_emp;
END;
Share this article :

0 comments:

Post a Comment