需要这么做:
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
is right!
顺便问一句,那里有帮助或者技术文章,教程什么的?
create or replace package pkg_test
as
type cur_test is ref cursor;
end pkg_test;
/create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100);
begin
v_sql := 'select NE_INFO.ID,Count(NPC.Type)
From NE_INFO,NPC
Where NPC.Type <>"-----"
And NE_INFO>ID = NPC.ID
Group By NE_INFO.ID
Order By NE_INFO.ID';
OPEN v_cur FOR v_sql;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error--------'||sqlcode||':'||
sqlerrm);
end p_test;
/
09:49:32 SQL> CREATE OR REPLACE PROCEDURE Procdemo AS
09:49:32 2 t_out varchar2(40);
09:49:32 3 col1 number;
09:49:32 4 col2 number;
09:49:32 5 cursor c_1 is
09:49:32 6 select max(a_id1),max(a_id2) from a group by a_id1,a_id2;
09:49:32 7 BEGIN
09:49:32 8 OPEN c_1;
09:49:32 9 LOOP
09:49:32 10 FETCH c_1 INTO col1,col2 ;
09:49:32 11 t_out := ' max_a_id1:'||to_char(col1)||' max_a_id2:'||to_char(col2);
09:49:32 12 DBMS_OUTPUT.PUT_LINE(t_out);
09:49:32 13 EXIT WHEN c_1%NOTFOUND;
09:49:32 14 END LOOP;
09:49:32 15 CLOSE c_1;
09:49:32 16
09:49:32 17 END Procdemo;
09:49:32 18 /过程已创建。实际:90
09:49:33 SQL> exec procdemo;
max_a_id1:1 max_a_id2:1
max_a_id1:2 max_a_id2:1
max_a_id1:3 max_a_id2:1
max_a_id1:3 max_a_id2:1PL/SQL 过程已成功完成。实际:80
09:49:34 SQL>
又试了以下方法:
SET SERVEROUTPUT ON;
Create OR REPLACE PROCEDURE p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100);
begin
v_sql := 'select * from NE_INFO';
open v_cur for v_sql;
exception
When others then
DBMS_OUTPUT.PUT_LINE('Error!');
end p_test;
/
结果还是编译错误
我感觉不是SQL语句的问题.是不是游标和存储过程语法的问题?
使用show error命令查看错误详细信息