oracle 如何使用oracle从存储的函数中检索多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13844898/
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 retrieve multiple rows from a stored function with oracle
提问by Alfwed
I'm trying to create a stored function in oracle that returns multiple rows.
我正在尝试在 oracle 中创建一个返回多行的存储函数。
My question is very similar to this oneexcept that I want to fetch a select *query
我的问题与这个问题非常相似，只是我想获取一个select *查询
In a nutshell, I want to create a function which returns the result of this query
简而言之，我想创建一个返回此查询结果的函数
select * from t_email_queue
What I've tried is this :
我试过的是这样的：
create or replace
PACKAGE email_queue AS
type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE;
FUNCTION lock_and_get return t_email_queue_type;
END email_queue;
create or replace
PACKAGE BODY email_queue AS
FUNCTION lock_and_get RETURN t_email_queue_type AS
queue_obj t_email_queue_type;
cursor c (lockid in varchar2) is select * from t_email_queue where lockedby = lockid;
lockid varchar2(100) := 'alf';
BEGIN
OPEN c(lockid);
FETCH c bulk collect INTO queue_obj;
return queue_obj;
END lock_and_get;
END email_queue;
The package compiles just fine but when I try to call it with this query
该包编译得很好但是当我尝试使用此查询调用它时
select * from table(email_queue.lock_and_get);
Oracle throws the following error
oracle抛出以下错误
ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error at Line: 1 Column: 20
I think Oracle want me to create my return type at the schema level but when I try to do
我认为 Oracle 希望我在架构级别创建我的返回类型，但是当我尝试这样做时
create type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE;
Oracle complains
甲骨文抱怨
Type IMAIL.T_EMAIL_QUEUE_TYPE@imail dev
Error(1): PL/SQL: Compilation unit analysis terminated
Error(2,37): PLS-00329: schema-level type has illegal reference to IMAIL.T_EMAIL_QUEUE
Can someone point me to the right direction? What am I missing here?
有人可以指出我正确的方向吗？我在这里缺少什么？
Thanks for reading!
谢谢阅读！
采纳答案by DazzaL
with SQL types you cant do %ROWTYPE, you'd have to type each column to match the table*.
对于 SQL 类型，您不能执行 %ROWTYPE，您必须键入每一列以匹配表 *。
*sys.anydataset aside. but going down that route is a lot more complex coding.
*sys.anydataset 放在一边。但沿着这条路走下去，编码要复杂得多。
e.g. if your table was
例如，如果你的桌子是
create table foo (id number, cola varchar2(1));
then
然后
create type email_queue_type is object (id number, cola varchar2(1));
/
create type t_email_queue_type as table of email_queue_type;
/
and use that table email_queue_type_tab as the output from your function.
并使用该表 email_queue_type_tab 作为函数的输出。
but i'd recommend a pipelined function, as your current code isn't scalable.
但我建议使用流水线函数，因为您当前的代码不可扩展。
eg:
例如：
SQL> create table foo (id number, cola varchar2(1));
Table created.
SQL>
SQL> create type email_queue_type is object (id number, cola varchar2(1));
2 /
Type created.
SQL> create type t_email_queue_type as table of email_queue_type;
2 /
Type created.
SQL> insert into foo select rownum, 'a' from dual connect by level <= 10;
10 rows created.
SQL>
SQL> create or replace PACKAGE email_queue AS
2
3
4 FUNCTION lock_and_get return t_email_queue_type pipelined;
5
6 END email_queue;
7 /
Package created.
SQL> create or replace PACKAGE BODY email_queue AS
2
3 FUNCTION lock_and_get RETURN t_email_queue_type pipelined AS
4 queue_obj t_email_queue_type;
5
6 BEGIN
7
8 for r_row in (select * from foo)
9 loop
10 pipe row(email_queue_type(r_row.id, r_row.cola));
11 end loop;
12
13 END lock_and_get;
14
15 END email_queue;
16 /
Package body created.
SQL> select * from table(email_queue.lock_and_get());
ID C
---------- -
1 a
2 a
3 a
4 a
5 a
6 a
7 a
8 a
9 a
10 a
10 rows selected.
SQL>
回答by Alex Poole
If you aren't particularly keen on having the SQL type, you could do this with a sys_refcursorinstead:
如果您不是特别热衷于拥有 SQL 类型，则可以sys_refcursor改为使用：
create or replace package email_queue as
function lock_and_get return sys_refcursor;
end email_queue;
/
create or replace package body email_queue as
function lock_and_get return sys_refcursor AS
c sys_refcursor;
lockid varchar2(100) := 'alf';
begin
open c for
select * from t_email_queue
where lockedby = lockid;
return c;
end lock_and_get;
end email_queue;
/
From SQL*Plus you could call it something like:
在 SQL*Plus 中，您可以这样称呼它：
var cur refcursor;
exec :cur := email_queue.lock_and_get;
print cur
and as execis shorthand for a simple anonymous block you can call if from other PL/SQL objects too. What you can't do, though, is:
并且作为exec简单匿名块的简写，您也可以从其他 PL/SQL 对象调用 if。但是，您不能做的是：
select * from table(email_queue.lock_and_get);
I'm not familiar with calling functions from PHP, but from Java you can use this directly as the return from a callable statement, so you don't need the select * from table()construct at all. I have no idea if you can execute an anonymous block in a PHP call, something like begin $cur = email_queue.lock_and_get; end;, and have $curas your result set that you can then iterate through?
我不熟悉从 PHP 调用函数，但是在 Java 中，您可以直接将其用作可调用语句的返回值，因此您根本不需要该select * from table()构造。我不知道您是否可以在 PHP 调用中执行匿名块，例如begin $cur = email_queue.lock_and_get; end;，然后$cur将结果集作为您可以迭代的结果集？
I realise this isn't a complete answer as the PHP side is way too vague, but might give you some ideas.
我意识到这不是一个完整的答案，因为 PHP 方面太模糊了，但可能会给你一些想法。
回答by Deolu Philip
If you are using PHP and you want to access a oracle stored function. You can make use of something like this
如果您正在使用 PHP 并且想要访问 oracle 存储的函数。你可以使用这样的东西
//Your connection details
$conn = oci_connect($username, $password, '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))' );
/* Your query string; you can use oci_bind_by_name to bind parameters or just pass the variable in it*/
$query = "begin :cur := functionName('".$param1."','".$param2."','".$param3."'); end;";
$stid = oci_parse($conn, $query);
$OUTPUT_CUR = oci_new_cursor($conn);
oci_bind_by_name($stid, ':cur', $OUTPUT_CUR, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_execute($OUTPUT_CUR);
oci_fetch_all($OUTPUT_CUR, $res);
// To get your result
var_dump($res);
I hope this helps.
我希望这有帮助。
