CODEWITHSUNDEEP
sqloracle-database
Chris
Chris

Reputation: 5605

Stored Procedure Update a Cursor then Return the Result

I am trying to create an ORACLE stored procedure that selects some info into a cursor, run some updates on that cursor. Then return the contents of the cursor, this is to move away from the current setup that involves creating a temp table and running queries on that.

Everything that I have tried has resulted in the contents of the cursor being out of scope of any updates etc that I want to run..

Essentially I'm trying to:

  1. Select some data
  2. Do some processing on it
  3. Return some of the processed data (based on a select)

Please help!

My current code is along the lines of:

create or replace
PROCEDURE TEMP_REPORT
(
  returnTable OUT SYS_REFCURSOR
)
IS
  CURSOR resultTable
  IS
  SELECT FNAME,SALARY FROM STAFF;

   NAME     VARCHAR2 (10);
   SALARY   VARCHAR2 (10);

BEGIN


    Update resultTable set Salary = (salary * 1.1);
    --- some more processing here
    ---- now return the datarows
    Cursor returnTable from select name, salary from resultTable where salary > 1000;


END TEMP_REPORT;

Upvotes: 1

Views: 1000

Answers (1)

Jestem_z_Kozanowa
Jestem_z_Kozanowa

Reputation: 637

what not try something simpler like :

declare

   NAME     VARCHAR2 (10);
   SALARY   VARCHAR2 (10);

begin

FOR cid IN ( select FNAME,SALARY FROM STAFF) LOOP

      NAME := cid.NAME;
      SALARY := cid.SALARY;

      DBMS_OUTPUT.
       put_line (
            NAME
         || ' | '
         || SALARY);
END LOOP;

END;

Upvotes: 1

Related Questions