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 it 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.
