17,089
社区成员
发帖
与我相关
我的任务
分享
--------你定义的类型也不用了,直接使用游标就行了
------- 变量也不要了
-------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;
--------你定义的类型也不用了,直接使用游标就行了
------- 变量也不要了
-------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;