create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;create or replace package body pkg_test
as
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;--调用
set serverout on
declare
w_rc pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;create or replace package body pkg_test
as
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;--调用
set serverout on
declare
w_rc pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
Compilation errors for PACKAGE BODY SHUIBJ.PKG_TESTError: PLS-00103: 出现符号 ""在需要下列之一时:
begin end function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "" 被忽略。
Line: 3
Text: function get(p_id number) return myCursor isError: Hint: Variable ' function' is declared but never used in 'pkg_test'
Line: 3
Text: function get(p_id number) return myCursor is
能不能搞测试好的例子啊!!
PROCEDURE procedure1 (global_variable1 datatype, ...);
PROCEDURE procedure2 (global_variable1 datatype, ...);
END package_name;CREATE PACKAGE BODY package_name AS
PROCEDURE procedure1 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure1;PROCEDURE procedure2 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure2;END package_name;
create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;
--包体
create or replace package body pkg_test
as function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;--调用
set serverout on
declare
w_rc pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
SQL> set serverout on
SQL> declare
2 w_rc pkg_test.myCursor;
3 w_name varchar2(100);
4 begin
5 w_rc:=pkg_test.get(11111110);
6 loop
7 fetch w_rc into w_name;
8 exit when w_rc%notfound;
9 dbms_output.put_line(w_name);
10 end loop;
11 end;
12 /PL/SQL procedure successfully completedSQL>
SQL> set serverout on
SQL> declare
2 w_rc pkg_test.myCursor;
3 w_name varchar2(100);
4 begin
5 w_rc:=pkg_test.get(1055);
6 loop
7 fetch w_rc into w_name;
8 exit when w_rc%notfound;
9 dbms_output.put_line(w_name);
10 end loop;
11 end;
12 /DINYA <---返回了记录PL/SQL procedure successfully completedSQL>
但是,取了一部分数据
错误如下:........
........
三环路桥下游31号
三环路桥下游32号
三环路桥下游33号
三环路桥下游3号
三环路桥下游4号
三环路桥下游5号
三环路桥下游26号
三环路桥下游27号
...........
...........
declare
w_rc TEST_PAG.myCursor;
w_name varchar2(100);
begin
w_rc:=TEST_PAG.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: 在"SYS.DBMS_OUTPUT", line 35
ORA-06512: 在"SYS.DBMS_OUTPUT", line 198
ORA-06512: 在"SYS.DBMS_OUTPUT", line 139
ORA-06512: 在line 9
这个是什么错误啊??
要定多少啊??varchar2(2000);
都没有用!
就是你的错误提示~~这个
buffer overflow, limit of 2000 bytes
要把它扩大~~~
如果是要显示的话就要设个值~~我是用
set serveroutput on size = 10000来设定的~~~~不知道你是用什么来执行过程的?
SQL>
SQL> declare
2 w_rc TEST_PAG.myCursor;
3 w_name varchar2(100);
4 begin
5 w_rc:=TEST_PAG.get(0);
6 loop
7 fetch w_rc into w_name;
8 exit when w_rc%notfound;
9 dbms_output.put_line(w_name);
10 end loop;
11 end;
12 /
w_rc TEST_PAG.myCursor;
w_name varchar2(100);
begin
w_rc:=TEST_PAG.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.enable(2000000);
--在这里设置一下大小.就可以解决了,是dbms_output.put_line 的限制
dbms_output.put_line(w_name);
end loop;
end;
dbms_output.enable(2000000);
和和~~~~
zoulipeng(向Oracle前进,再前进...) :))))