Geek asks and answers

How to create an Oracle stored procedure that can return certain objects, as well as the whole entity

I am starting to store a stored procedure.

We need to create a stored procedure that should return all records or return only those passed as an IN parameter, the procedure returns a cursor.

If the procedure is called by the transfer list accountId, it should return only those accounts, otherwise all accounts.

If you can post an example, that would be great.

+1
source share
4 answers

Here is a simple example:

Consider the table: PERSONS (person_id, name)

This function will return a cursor that returns either a single record or all records if no argument is specified:

CREATE FUNCTION get_person
   (person_id IN persons.person_id%TYPE := NULL)
   RETURN SYS_REFCURSOR IS
   rc SYS_REFCURSOR;
BEGIN
   OPEN rc FOR
     SELECT *
     FROM   persons p
     WHERE  p.person_id = get_person.person_id
     OR     get_person.person_id IS NULL;
   RETURN rc;
END;
+4
source

, . SQL, select.

create or replace type numbers_nt as table of number
/

, . , EMP, . SQL ref.

create or replace function qry_emps
        (p_nt in numbers_nt)
        return sys_refcursor
as
    rv sys_refcursor;
    stmt varchar2(32767) := 'select * from emp';
begin
    if p_nt.count() > 0
    then
        stmt := stmt || ' where empno in ( select * from table(:1) )';
        open rv for stmt
            using p_nt;
    else
        open rv for stmt;
    end if;
    return rv;
end qry_emps;
/

, :

SQL> set serveroutput on
SQL>
SQL> declare
  2      empty_nt numbers_nt := new numbers_nt();
  3      pop_nt numbers_nt := new numbers_nt(7876,8083,7788);
  4      rc sys_refcursor;
  5      lrec emp%rowtype;
  6  begin
  7      rc := qry_emps(pop_nt);
  8      dbms_output.put_line ( 'Three rows');
  9
 10      loop
 11          fetch rc into lrec;
 12          exit when rc%notfound;
 13          dbms_output.put_line('empno = '||lrec.empno);
 14      end loop;
 15
 16      dbms_output.put_line ( 'Done');
 17  end;
 18  /
Three rows
empno = 7876
empno = 8083
empno = 7788
Done

PL/SQL procedure successfully completed.

SQL>

, :

SQL> declare
  2      empty_nt numbers_nt := new numbers_nt();
  3      rc sys_refcursor;
  4      lrec emp%rowtype;
  5  begin
  6      rc := qry_emps(empty_nt);
  7      dbms_output.put_line ( 'all rows');
  8
  9      loop
 10          fetch rc into lrec;
 11          exit when rc%notfound;
 12          dbms_output.put_line('empno = '||lrec.empno);
 13      end loop;
 14
 15      dbms_output.put_line ( 'Done');
 16  end;
 17  /
all rows
empno = 8083
empno = 8084
empno = 8085
empno = 7369
empno = 7499
empno = 7521
empno = 7566
empno = 7654
empno = 7698
empno = 7782
empno = 7788
empno = 7839
empno = 7844
empno = 7876
empno = 7900
empno = 7902
empno = 7934
empno = 8060
empno = 8061
empno = 8100
empno = 8101
Done

PL/SQL procedure successfully completed.

SQL>
+2

PIPELINED . , ( , ). . .

, ( , sys_refcursor, ..).

set echo on
set serveroutput on

drop table people;
create table people
(
pid number primary key,
name varchar2(100),
address varchar2(100),
city varchar2(100),
state varchar2(2)
);

insert into people values (1, 'John Smith', '123 Main St', 'Denver', 'CO');
insert into people values (2, 'Jane Doe', '456 West St', 'Ft Lauderdale', 'FL');
insert into people values (3, 'Pete Rose', '789 North Ave', 'Philadelphia', 'PA');
commit;

:

create or replace package refcur_pkg is
    type people_tab is table of people%rowtype;
end refcur_pkg;

create or replace type pid_tab as table of number;

( - )

-- pipelined function to return people based on list of people ids
create or replace function get_people(pids in pid_tab)
return refcur_pkg.people_tab pipelined
IS
    v_people_row people%rowtype;
begin
    --
    -- Note: business rule is no input ids returns ALL rows:
    --
    if (pids is null or pids.count = 0) then
        -- return all rows
        for rec in (select * from people)
        loop
            pipe row(rec);
        end loop;
    else
        -- return rows based on ids
        for rec in (select * from people where pid in (select * from table(pids)))
        loop
            pipe row(rec); 
        end loop;
    end if;
end;


-- EXAMPLES
-- get any/all people with any of these ids
select * from table(get_people(new pid_tab(1,3,4,5)));

-- gets nobody (nobody with this pid)
select * from table(get_people(new pid_tab(-1)));

-- get ALL people
select * from table(get_people(new pid_tab()));

-- also gets ALL people
select * from table(get_people(NULL));
+2

, .

:

CREATE TABLE accounts
    ( account_id NUMBER
    , NAME      VARCHAR2(100)
    );

INSERT INTO accounts values ( 1, 'Tom Selleck');
INSERT INTO accounts VALUES ( 2, 'Elvis Presley');
INSERT INTO accounts VALUES ( 3, 'Morgan Freeman');
INSERT INTO accounts values ( 4, 'Harry Morgan');

commit;

:

CREATE TYPE accountId_rec AS OBJECT ( account_id NUMBER );
CREATE TYPE accountid_tbl AS TABLE OF accountid_rec;

:

CREATE OR REPLACE
FUNCTION get_accounts ( p_accounts IN accountId_tbl ) 
    RETURN sys_refcursor 
IS
    retcur sys_refcursor;
BEGIN
    IF ( p_accounts IS NULL OR p_accounts.count < 1 ) THEN
        OPEN retcur FOR SELECT * FROM accounts;
    ELSE
        OPEN retcur FOR SELECT * FROM accounts WHERE account_id IN ( SELECT account_id FROM TABLE( p_accounts ));
    END IF;
    RETURN retcur;
EXCEPTION
    WHEN OTHERS THEN dbms_output.put_line('get_accounts error: '||sqlerrm);
END;

Now, to check the function with the pl / sql block:

DECLARE
    p_accounts  accountId_tbl := accountId_tbl();
    account_rec accounts%rowtype;
    ref_cur     sys_refcursor;
BEGIN
    dbms_output.put_line('Test with no Account ID''s.');
    ref_cur := get_accounts( p_accounts );
    LOOP
        FETCH ref_cur INTO account_rec;
            EXIT WHEN ref_cur%NOTFOUND;
        dbms_output.put_line('Account ID: '||account_rec.account_id||', Name: '||account_rec.name);
    END LOOP;

    dbms_output.put_line('');

    -- now let test with account ids provided.
    dbms_output.put_line('Test with Account ID''s.');
    p_accounts.EXTEND;
    p_accounts( p_accounts.count ) := accountId_rec(2);
    p_accounts.EXTEND;
    p_accounts( p_accounts.count ) := accountId_rec(4);
    -- get the new ref_cur
    ref_cur := get_accounts( p_accounts );
    LOOP
        FETCH ref_cur INTO account_rec;
            EXIT WHEN ref_cur%NOTFOUND;
        dbms_output.put_line('Account ID: '||account_rec.account_id||', Name: '||account_rec.name);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN dbms_output.put_line('whoops: '||sqlerrm);
END;

And the test results:

Test with no Account ID's.
Account ID: 1, Name: Tom Selleck
Account ID: 2, Name: Elvis Presley
Account ID: 3, Name: Morgan Freeman
Account ID: 4, Name: Harry Morgan

Test with Account ID's.
Account ID: 2, Name: Elvis Presley
Account ID: 4, Name: Harry Morgan

Hope this helps.

+1
source

More articles:


All Articles