Oracle Stored Procedure with Cursor

Here’s a stored procedure example in Oracle database, using Cursor to manipulate or navigate the records.

1. Table SQL Script

DBUSER table creation script.

CREATE TABLE DBUSER ( 

  USER_ID       NUMBER (5)    NOT NULL, 

  USERNAME      VARCHAR2 (20)  NOT NULL, 

  CREATED_BY    VARCHAR2 (20)  NOT NULL, 

  CREATED_DATE  DATE          NOT NULL, 

  PRIMARY KEY ( USER_ID ) 

 )

 

2. Stored Procedure

A stored procedure, return the record as cursor type (SYS_REFCURSOR) base on the matched username.

CREATE OR REPLACE PROCEDURE getDBUSERCursor

(p_username IN DBUSER.USERNAME%TYPE, c_dbuser OUT SYS_REFCURSOR)

IS

BEGIN

   OPEN c_dbuser FOR

  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';

END;

/

3. Calls from PL/SQL

Get the returned ref cursor and navigate the records like this :

DECLARE 

  c_dbuser SYS_REFCURSOR;

  temp_dbuser DBUSER%ROWTYPE;

BEGIN

   --records are assign to cursor 'c_dbuser'

  getDBUSERCursor('mkyong',c_dbuser);

   LOOP

         --fetch cursor 'c_dbuser' into dbuser table type 'temp_dbuser'

               FETCH c_dbuser INTO temp_dbuser;

         --exit if no more records

        EXIT WHEN c_dbuser%NOTFOUND;

         --print the matched username

        dbms_output.put_line(temp_dbuser.username);

   END LOOP;

   CLOSE c_dbuser;

END;

/

Result
The records of username like ‘mkyong%’ are returned as cursor via getDBUSERCursor store procedure.

How to make procedure fetch and cursor for this ?

SELECT aktualne_notowania.aspolka_id,

    aktualne_notowania.notowanie * spolki.ilosc_akcji as total

  FROM aktualne_notowania, spolki

  WHERE aktualne_notowania.aspolka_id = spolki.spolka_id;

something like

create or replcae procedure test_proc() is 

   v_es_id polski_table.id%type; 

   v_mult_val number; 

   cursor c1 is 

    select select q.id, (q.quantity*p.price) as total_amount 

      from quantity q

         , price p 

     where q.itemId = p.itemId 

begin 

   open c1 

   loop 

      fetch c1 into v_es_id, v_mult_val; 

      exit when c1%notfound; 

      update table_target 

         set total := v_mult_val 

       where id:=v_es_id; 

end

No need to go to procedure. You can do it with UPDATE statement:

UPDATE table_target t

SET total = ( select sum(q.quantity*p.price)

  from quantity q

     , price p 

 where q.itemId = p.itemId 

  and q.id = t.id );

It will be faster than PL/SQL code. NOTE: I added sum() in case you have multiple line to sum - if not the case, you can put it out.

I hope you all have enjoyed reading this article. Comments are welcome....