SQL 如何调用包中的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4614475/
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 call a function in a package
提问by code511788465541441
I'm doing the following but it doesnt work
我正在执行以下操作，但它不起作用
select package_name.function_name(param,param) from dual
I'm calling a function that returns a cursor so im guessing "from dual"is the problem
我正在调用一个返回游标的函数，所以我猜"from dual"是问题所在
is there another way of doing it?
还有另一种方法吗？
采纳答案by APC
I presume you mean a Ref Cursor. This is a PL/SQL construct which acts as a pointer to a set of records returned by a query. This means it has to be interpreted by the client which runs the query. For instance, we can map a Ref Cursor to a JDBC or ODBC ResultSet.
我想你的意思是参考光标。这是一个 PL/SQL 构造，它充当指向查询返回的一组记录的指针。这意味着它必须由运行查询的客户端解释。例如，我们可以将 Ref Cursor 映射到 JDBC 或 ODBC ResultSet。
There is certainly nothing wrong with your basic statement. Here is a function similar to your own:
你的基本陈述当然没有错。这是一个类似于您自己的功能：
SQL> desc get_emps
FUNCTION get_emps RETURNS REF CURSOR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DNO NUMBER(2) IN
P_SORT_COL VARCHAR2 IN DEFAULT
P_ASC_DESC VARCHAR2 IN DEFAULT
SQL>
I can easily call this in a wider PL/SQL block:
我可以在更广泛的 PL/SQL 块中轻松调用它：
SQL> declare
2 rc sys_refcursor;
3 begin
4 rc := get_emps(50);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
However, SQL*PLus can handle CURSOR constructs natively:
但是，SQL*PLus 可以本机处理 CURSOR 构造：
SQL> select get_emps(50) from dual
2 /
GET_EMPS(50)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8060 VERREYNNE PLUMBER 8061 08-APR-08 4000 50
8061 FEUERSTEIN PLUMBER 7839 27-FEB-10 4500 50
8085 TRICHLER PLUMBER 8061 08-APR-10 3500 50
8100 PODER PLUMBER 8061 3750 50
SQL>
This statement also runs in SQL Developer, although the result set is laid out in an uglyfashion.
该语句也在 SQL Developer 中运行，尽管结果集的布局很丑陋。
So, if you are having problems with your function, the questions are:
所以，如果你的函数有问题，问题是：
- What client environment are you using?
- In what precise fashion does it "not work"? Please describe the observed behaviour, including any error messages?
- Also give us environment details such as the version of the database, the OS, etc.
- 您使用的是什么客户端环境？
- 它以什么精确的方式“不起作用”？请描述观察到的行为，包括任何错误消息？
- 还给我们提供环境详细信息，例如数据库的版本、操作系统等。
Having read your other question on this topic I thought the problem might be due to the use of a User-Defined Ref Cursor (rather than the built-in). However, that doesn't make any difference. This packaged function:
阅读您关于此主题的其他问题后，我认为问题可能是由于使用了用户定义的参考光标（而不是内置的）。但是，这没有任何区别。这个打包的功能：
SQL> create or replace package emp_rc_utils as
2
3 type emp_rc is ref cursor return emp%rowtype;
4
5 function get_emps
6 ( p_dno in emp.deptno%type
7 )
8 return emp_rc;
9 end;
10 /
Package created.
SQL> create or replace package body emp_rc_utils as
2
3 function get_emps
4 ( p_dno in emp.deptno%type
5 )
6 return emp_rc
7 is
8 return_value emp_rc_utils.emp_rc;
9 begin
10
11 open return_value for select * from emp where deptno = p_dno;
12
13 return return_value;
14 end get_emps;
15
16 end emp_rc_utils;
17 /
Package body created.
SQL>
Still works...
仍然有效...
SQL> declare
2 rc sys_refcursor;
3 begin
4 rc := emp_rc_utils.get_emps(50);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select emp_rc_utils.get_emps(50) from dual
2 /
EMP_RC_UTILS.GET_EMP
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8085 TRICHLER PLUMBER 8061 08-APR-10 3500 50
8060 VERREYNNE PLUMBER 8061 08-APR-08 4000 50
8061 FEUERSTEIN PLUMBER 7839 27-FEB-10 4500 50
8100 PODER PLUMBER 8061 3750 50
SQL>
回答by Harrison
You can do this via a refcursor call or populate a user defined table and return it as follows:
您可以通过 refcursor 调用或填充用户定义的表并按如下方式返回它：
create or replace
function getRef return sys_refcursor
is
l_ref sys_refcursor;
begin
open l_ref for
select 1 a, 'a' c from dual
union all
select 2 a, 'b' c from dual
union all
select 3 a, 'c' c from dual
union all
select 4 a, 'd' c from dual;
return l_ref;
end getRef;
/
select getref() from dual;
GETREF()
--------
A C
---------------------- -
1 a
2 b
3 c
4 d
--you'll notice this isn't the most user-friendly result set if you look at it in SQL Developer or whatno
--drop function getRef;
you can also use the 'table' if you are passing back a table collection as such
如果您要传回表格集合，您也可以使用“表格”
create or replace type lookup_row as
object ( a number, c varchar2(20) );
/
create or replace type lookups_tab as
table of lookup_row;
/
create or replace
function getUserDefinedTableType return lookups_tab
is
lTestTypeTable lookups_tab;
begin
SELECT lookup_row(a,c)
bulk collect INTO lTestTypeTable
from
(select 1 a, 'a' c from dual
union all
select 2 a, 'b' c from dual
union all
select 3 a, 'c' c from dual
union all
select 4 a, 'd' c from dual);
return lTestTypeTable;
end getUserDefinedTableType;
/
select * from table(getUserDefinedTableType());
--this returns it in a more user friendly manner
--http://www.oreillynet.com/pub/a/network/2003/01/22/feuerstein.html?page=2
--http://stackoverflow.com/questions/3150137/converting-oracle-query-into-user-defined-types-in-pl-sql/3152885#3152885
A C
---------------------- --------------------
1 a
2 b
3 c
4 d
回答by akf
have you tried:
你有没有尝试过：
myCursor := package_name.function_name(param,param);
this would have to be from within a test block or a stored procedure.
这必须来自测试块或存储过程。
