CODEWITHSUNDEEP
sqloracle-database
Nati
Nati

Reputation: 1

How to manipulate data according to cursor values which are also the return value of a stored procedure in Oracle sql?

I have an oracle sql stored procedure that returns a cursor. This cursor gets in the stored procedure body the value of a complex select statement (in the example below I made the select statement simple).

Then, I want to use the cursor for two things: 1. Use it as return value of the stored procedure 2. Use its data to update some values in another table within the stored procedure body

I couldn't find how to do it, so in the meanwhile I had to replicate the (complex) select statement and let another cursor have its value for updating the other table.

create or replace procedure sp_GetBuildings(returned_cursor OUT SYS_REFCURSOR,
                                                 timeFrameHrsParam number) is
  v_buildingID Buildings.buildingId%type;

        cursor t_result is
            select customerId
            from (select buildingId from Buildings) b
            inner join Customers c on c.building_id = b.building_id; 
    begin 
        open returned_cursor for
            select customerId
              from (select buildingId from Buildings) b
              inner join Customers c on c.building_id = b.building_id; 
        for t in t_result
            loop
                v_buildingID := t.building_id;                 
                update Buildings set already = 1 where building_id = v_buildingID ;
            end loop;          
        commit;
    end sp_GetBuildings;

Can you help me with a solution that will save me the select statement replication?

Upvotes: 0

Views: 1028

Answers (1)

Allan
Allan

Reputation: 17429

You can't copy or clone a cursor in Oracle. The cursor is just a pointer to the result set and reading the cursor moves along the result list in a single direction. However, you can achieve something very similar using arrays:

CREATE TYPE nt_number AS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE sp_getbuildings(returned_table OUT nt_number,
                                            timeframehrsparam NUMBER) IS
   CURSOR t_result IS
      SELECT   customerid
        FROM        buildings b
               JOIN customers c
                 ON c.building_id = b.building_id;
   i   NUMBER;
BEGIN
   OPEN t_result;

   FETCH t_result
   BULK COLLECT INTO   returned_table;

   CLOSE t_result;

   FORALL i IN returned_table.FIRST .. returned_table.LAST
      UPDATE   buildings
         SET   already = 1
       WHERE   building_id = v_buildingid;

   COMMIT;
END sp_getbuildings;

Upvotes: 1

Related Questions