存储过程游标声明问题？

[TCat]

CREATE OR REPLACE Procedure ListCustomer_proc
( P_CURSOR OUT NILE_TYPES.NILE_CURSOR
)
AS
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;
错误信息：identifier 'nile_types.nile_sursor' must be declared
在前面加上 declare还是不行，请教怎样声明？

[TCat]

感谢大家建议，小弟第一次使用Oracle，多谢指导！

to：beckhambobo(beckham)
怎么还是不行啊，在第二个Create的地方错误信息为：
“Encountered the symbol"CREATE"”

to：ATCG(ATCG) 你的怎么也不能执行啊错误信息为
"Encountered the symbol"TYPE" when expecting one of the following ;is with as"

[ATCG]

应该这样，刚才我没看清楚
CREATE OR REPLACE Procedure ListCustomer_proc
type ref_cursor is ref cursor;
P_CURSOR ref_cursor;
AS
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;

[beckhambobo]

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE v_CURSOR IS REF CURSOR;

Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR );
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR )
is
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;
END pkg_test;
/

让大家提担忧，试试看!

[ATCG]

不好意思，又写错了，这样才对
应该这样，刚才我没看清楚
CREATE OR REPLACE Procedure ListCustomer_proc
as
type ref_cursor is ref cursor;
P_CURSOR ref_cursor;
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;

[beckhambobo]

ATCG(ATCG) 兄，这样就不能返回一个记录集了（没有参数)

[TCat]

to： beckhambobo(beckham)
感谢你的再次回答，可是把NILE换成v有什么区别吗？大哥帮忙请敬业一点
小弟这里就是执行不通过的，菜鸟认了！

[ATCG]

To: beckhambobo(beckham) 兄
我不知道楼主到底要实现什么功能，只是给他一个正确的例子。
至于记录集，游标本身返回的就是记录集。并不存在不返回记录集的问题。

[TCat]

感谢：beckhambobo(beckham) 真是不看书也是不行的，你的函数要分开
在Packages和Packages bodies中执行才行，一身大汉啊，谢谢：－）
感谢：ATCG(ATCG) 虽然你的函数还是没有找到可以运行通过的地方，我想
是我太菜了吧，肯定会找得的，还是谢谢。

[beckhambobo]

SQL> CREATE OR REPLACE PACKAGE pkg_test
2 AS
3 TYPE v_CURSOR IS REF CURSOR;
4
5 Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR );
6 END pkg_test;
7 /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg_test
2 AS
3 Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR )
4 is
5 BEGIN
6 OPEN P_CURSOR FOR
7 'SELECT cusid,passtyp,idno,name,sex
8 FROM customer
9 WHERE ROWNUM <= 10';
10 END ListCustomer_proc;
11 END pkg_test;
12 /

Package body created

太笨了，忘记在语句之间加分号!

[beckhambobo]

SQL> CREATE OR REPLACE PACKAGE pkg_test
2 AS
3 TYPE v_CURSOR IS REF CURSOR;
4
5 Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR );
6 END pkg_test;
7 /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg_test
2 AS
3 Procedure ListCustomer_proc(P_CURSOR OUT v_CURSOR )
4 is
5 BEGIN
6 OPEN P_CURSOR FOR
7 'SELECT cusid,passtyp,idno,name,sex
8 FROM customer
9 WHERE ROWNUM <= 10';
10 END ListCustomer_proc;
11 END pkg_test;
12 /

Package body created

太笨了，忘记在语句之间加分号!

[beckhambobo]

先向楼主提示一个问题，游标不是一个oracle内部对象，不能被存取，只能以变量形式出现.

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE NILE_CURSOR IS REF CURSOR;

Procedure ListCustomer_proc(P_CURSOR OUT NILE_CURSOR );
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
CREATE OR REPLACE Procedure ListCustomer_proc
( P_CURSOR OUT NILE_TYPES.NILE_CURSOR
)
AS
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;
END pkg_test;
/

用包就可实现你的意思.

[ATCG]

CREATE OR REPLACE Procedure ListCustomer_proc
(P_CURSOR OUT NILE_TYPES.NILE_CURSOR%type)
type ref_cursor is ref cursor;
P_CURSOR ref_cursor;
AS
BEGIN
OPEN P_CURSOR FOR
SELECT cusid,passtyp,idno,name,sex
FROM customer
WHERE ROWNUM <= 10;
END ListCustomer_proc;

[jiezhi]

建议写代码之前先看看书，不然你写的代码质量令人担忧。

[jlandzpa]

同意jiezhi(西域浪子)

[beckhambobo]

sorry! 参数部分修改为

CREATE OR REPLACE Procedure ListCustomer_proc
( P_CURSOR OUT NILE_CURSOR）
........