CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Nickknack
Nickknack

Reputation: 857

Are cursors necessary for queries in a procedure?

I'm rather new to Oracle and I was asked to write a procedure to query some data from a table. I built it with 2 arguments, a cursor and a number. Essentially I have:

PROCEDURE PROC_NAME (
    cursor_name     IN OUT NOCOPY MY_DEFINED_CURSOR_TYPE,
    a_number       IN NUMBER);
AS
BEGIN

OPEN CURSOR_NAME FOR
    SELECT
        column
    FROM
        table
    WHERE
        table.dat_value > (SYSDATE - a_number);
END PROC_NAME;

It works like a charm, and I'm able to fetch the column from the cursor. My problem is that the requester doesn't want to pass in a cursor, they just want to pass in the number. I've never created a procedure that doesn't use a cursor to return the values of a query and the examples I have seen only ever do it that way. Is this possible?

Upvotes: 2

Views: 111

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21063

If you want to apply some PL/SQL logic, but remain using select for querying the data (i.e not pass in a cursor - use pipelined functions.

You need to define the TYPEs of the result row and table; FETCH the cursor and PIPE the results in the function. 

 CREATE or replace type MY_DEFINED_ROW_TYPE as object
 (
 txt             VARCHAR2(30)
 );
 /

 create or replace type MY_DEFINED_TABLE_TYPE as table of MY_DEFINED_ROW_TYPE
 /


 create or replace function FUN_NAME( a_number  IN NUMBER) return 
 MY_DEFINED_TABLE_TYPE
 PIPELINED
 as
     cur  MY_DEFINED_CURSOR_TYPE;
     v_txt   varchar2(30);
 begin
   OPEN cur
       FOR
       SELECT
        column
       FROM table
       WHERE table.dat_value > (SYSDATE - a_number);
    LOOP
     FETCH cur INTO v_txt;
     EXIT WHEN cur%NOTFOUND; 
     pipe row(v_txt);
    END LOOP;      
    return;
 end;
 /

The usage:

select * from table (FUN_NAME(2));

Upvotes: 1

user272735
user272735

Reputation: 10648

Use a function instead ? But it's just a "stylistic" difference compared to procedure out parameter. Anyway the returned value have to be implicitly passed (unlike in SQL Server as noted by @ShannonSeverance).

function f(
  p_days in number
) return my_defined_cursor_type is
  v_cur my_defined_cursor_type;
begin
  open v_cur for
    select
        column
    from
        table
    where
        table.dat_value > (sysdate - p_days);

  return v_cur;
end;
/

Usage

declare
  v_cur my_defined_cursor_type := f(42);
begin
  -- use v_cur as you like
end;

Upvotes: 1

MT0
MT0

Reputation: 167972

You can use a collection:

CREATE PROCEDURE PROC_NAME (
  a_number  IN  NUMBER,
  numbers   OUT SYS.ODCINUMBERLIST
)
AS
BEGIN
  SELECT number_value
  BULK COLLECT INTO numbers
  FROM   table_name
  WHERE  date_value > (SYSDATE - a_number);
END PROC_NAME;

Also, if you don't want to pass in a cursor then you can just pass one out:

CREATE OR REPLACE PROCEDURE PROC_NAME (
  a_number  IN  NUMBER,
  numbers   OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN numbers FOR
  SELECT number_value
  FROM   table_name
  WHERE  date_value > (SYSDATE - a_number);
END PROC_NAME;

Upvotes: 1

Related Questions