网上搜到说用游标实现,我写了如下存储过程,请大侠看看我怎么得不到结果呢?
初学者,不知存储过程写的是否正确,请大家说的详细点。CREATE OR REPLACE PACKAGE P_SEARCH_ERROR_PACKAGE AS
TYPE OUT_CUR IS REF CURSOR;
end P_SEARCH_ERROR_PACKAGE;
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out P_SEARCH_ERROR_PACKAGE.OUT_CUR)
AS
begin
declare
v_cfs_co_nbr varchar2(20);
v_cfs_co_id varchar2(16);
oe_hand_flag char(1);
oe_err_info varchar2(2000);
oe_order_error_id number;
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'; open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'; open v_outmsg for select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';exception
WHEN OTHERS THEN
ROLLBACK;
end;
end;
初学者,不知存储过程写的是否正确,请大家说的详细点。CREATE OR REPLACE PACKAGE P_SEARCH_ERROR_PACKAGE AS
TYPE OUT_CUR IS REF CURSOR;
end P_SEARCH_ERROR_PACKAGE;
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out P_SEARCH_ERROR_PACKAGE.OUT_CUR)
AS
begin
declare
v_cfs_co_nbr varchar2(20);
v_cfs_co_id varchar2(16);
oe_hand_flag char(1);
oe_err_info varchar2(2000);
oe_order_error_id number;
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'; open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'; open v_outmsg for select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';exception
WHEN OTHERS THEN
ROLLBACK;
end;
end;
2.你打开游标的into是什么呢?
3.及时into给了几个局部变量,你的局部变量也没起作用呢。
4.你这个存储过程的最终目的是什么?就返回游标结果集,还是要做其他的操作呢?
这个你必须明确啊。。
open v_outmsg for
(
selectfrom ...
union all
select .....
union all
select ....
)还有你定义的变量没用,而且地方也错了
(2)从Table2和Table3中通过Select语句选择得到record2;
(3)record1和record2中可能有多条结果,也可能为空,我想把这些结果全部输出;
--------你定义的类型也不用了,直接使用游标就行了
------- 变量也不要了
-------into语句有错
-------应用程序操作游标
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out sys_refcuror)
AS
begin
declare
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
-------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'
union all
select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
----------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'
union all
select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
--------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';exception
WHEN OTHERS THEN
ROLLBACK;
end;
end;
--------你定义的类型也不用了,直接使用游标就行了
------- 变量也不要了
-------into语句有错
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out sys_refcuror)
AS
v_cfs_co_nbr varchar2(20);
v_cfs_co_id varchar2(16);
oe_hand_flag char(1);
oe_err_info varchar2(2000);
oe_order_error_id number
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
-------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'
union all
select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
----------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'
union all
select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
--------into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';loop
fetch v_outmsg into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id;
exit when v_outmsg notfound;
dbms_output.put_line('v_cfs_co_nbr:'||v_cfs_co_nbr) ;
dbms_output.put_line('v_cfs_co_id:'||v_cfs_co_id) ;
dbms_output.put_line('oe_hand_flag:'||oe_hand_flag) ;
dbms_output.put_line('oe_err_info:'||oe_err_info) ;
dbms_output.put_line('oe_order_error_id:'||oe_order_error_id) ;
end loop
close v_outmsg;
exception
WHEN OTHERS THEN
end;