Follow us: Connect on YouTube 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 :

1 comments:

  1. Selamat Petang,

    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    I noticed that after my SQL instance crashed and came back online, One of the huge databases showed as recovering 10%..20% even though it was accessible and I was able to browse tables and query. I am surprised as to why it showed as recovering when it was all up and running.


    Super likes !!! for this amazing post
    . I thinks everyone should bookmark this.

    Thanks and Regards

    ReplyDelete