如何在包中调用函数 [英] How to call a function in a package
问题描述
我正在执行以下操作，但不起作用
I'm doing the following but it doesnt work
select package_name.function_name(param,param) from dual
我正在调用一个返回游标的函数，所以我猜是"from dual"是问题
I'm calling a function that returns a cursor so im guessing "from dual" is the problem
还有另一种方法吗?
推荐答案
我认为您的意思是参考光标.这是一个PL/SQL构造，用作指向查询返回的一组记录的指针.这意味着它必须由运行查询的客户端解释.例如，我们可以将Ref Cursor映射到JDBC或ODBC ResultSet.
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.
您的基本声明当然没有错.这是一个类似于您自己的函数:
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>
我可以在更广泛的PL/SQL块中轻松地调用它:
I can easily call this in a wider PL/SQL block:
SQL> declare
2 rc sys_refcursor;
3 begin
4 rc := get_emps(50);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
但是，SQL * PLus可以本地处理CURSOR构造:
However, SQL*PLus can handle CURSOR constructs natively:
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>
此语句也可以在SQL Developer中运行，尽管结果集以 ugly 的方式布置.
This statement also runs in SQL Developer, although the result set is laid out in an ugly fashion.
因此，如果您在功能方面遇到问题，则问题是:
So, if you are having problems with your function, the questions are:
- 您使用的是哪种客户端环境?
- 以什么精确的方式无效"?请描述观察到的行为，包括任何错误消息?
- 还向我们提供环境详细信息，例如数据库版本，操作系统等.
已经阅读了有关此主题的其他问题，我认为问题可能是由于使用了用户定义的Ref Cursor(而不是内置的).但是，这没有任何区别.打包的功能:
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>
仍然有效...
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>
这篇关于如何在包中调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
