如何创建可以返回特定实体以及所有实体的 Oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5983821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create Oracle stored procedure which can return specific entities as well all entity
提问by Mat
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.
我们需要创建一个存储过程，它应该返回所有记录或仅作为 IN 参数返回，过程返回游标。
If procedure is called by passing list of accountId, it should return only those accounts, else all accounts.
如果通过传递列表调用过程accountId，则应仅返回那些帐户，否则返回所有帐户。
If you can place some example it would be great.
如果你能举一些例子，那就太好了。
回答by Jeffrey Kemp
Here's a simple example:
这是一个简单的例子：
Consider the table: PERSONS (person_id, name)
考虑下表： 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;
回答by APC
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.
我们从一个嵌套表类型开始，我们可以用它来传递一个数字列表。这需要是 SQL 类型，因为我们将在 select 语句中使用它。
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.
这是一个将数字集合作为参数的函数。如果集合被填充，它使用这些数字来限制对 EMP 表的查询，否则它选择所有记录。这使用动态 SQL 返回引用游标结果集。
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>
回答by tbone
The following uses a PIPELINEDfunction 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.
下面使用PIPELINED函数返回行。流水线函数的好处在于它们在函数终止时异步返回行（您立即开始获取行而不是最后获取所有行）。它们也可以针对并行查询进行优化。如此确定的性能优势。
Also, the return cursor is strongly typed (not weak as in sys_refcursor, which can see runtime exceptions when the underlying table changes, etc).
此外，返回游标是强类型的（不像在 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 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));
回答by Michael Rickman
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:
现在，使用 pl/sql 块测试该函数：
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.
我希望这有帮助。
