Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> beginner: cursors

beginner: cursors

From: matt <reflection77_at_gmail.com>
Date: 10 Apr 2007 01:24:03 -0700
Message-ID: <1176193443.750143.325050@d57g2000hsg.googlegroups.com>


I have put together the following plsql and can verify that it does return the expected result..."the closest existing salary to a user entered salary of 8000"
The problem i face with this having to make this a stored procedure or function without whole-sale changes. Any ideas? The required use of a cursor makes this especially challenging for me.

DECLARE
  name empbb02.ename%TYPE;
  salary empbb02.sal%TYPE;
  TYPE cursor_var IS REF CURSOR;
  myCursorVar cursor_var;
  TargetSalary empbb02.sal%TYPE;
  rk number(5);

BEGIN   OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS RNK, E.ename
, E.sal
  FROM empbb02 E;

  LOOP
    FETCH myCursorVar INTO rk, name,salary;     EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';     DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);   END LOOP;
  CLOSE myCursorVar;
END;
/ Received on Tue Apr 10 2007 - 03:24:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US