How to create Oracle stored procedure which can return specific entities as well all entity

I am beginner to stored procedure.

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

If procedure is called by passing list of accountId, it should return only those accounts, else all accounts.

If you can place some example 开发者_StackOverflowit would be great.

Here's a simple example:

Consider the table: PERSONS (person_id, name)

This function will return a cursor that returns either one record, or all records if no argument is supplied:

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;

We start with a nested table type which we can use to pass a list of numbers. This needs to be a SQL type, because we will be using it in a select statement.

create or replace type numbers_nt as table of number
/

Here is a function which takes a number collection as a parameter. If the collection is populated it uses those numbers to restrict a query on the EMP table, otherwise it selects all records. This uses dynamic SQL to return a ref cursor result set.

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;
/

So, first we pass a populated collection:

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>

Now we edit the anonymous block to pass an empty collection:

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>

The following uses a PIPELINED function to return the rows. The nice part about pipelined functions is that they return rows asynchronous to the termination of the function (you start getting rows immediately rather than all at the end). They can also be optimized for parallel queries as well. So definite performance benefits.

Also, the return cursor is strongly typed (not weak as in sys_refcursor, which can see runtime exceptions when the underlying table changes, etc).

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 types:

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;

And the main function (put whatever business logic here)

-- 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;

Some usage examples

-- 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));

You can do this with passing a declared table type into the procedure.

Here is my test table and data:

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;

Now I create the declared object type and table type:

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

Next is the function:

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 test the function with a 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's 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

I hope this helps.