oracle 函数调用存储过程操作

utznplujsc

create or replace function fun_plan_station_contrast(groupid varchar2)
return number is
tmp varchar2(200);
mycur sys_refcursor;
res number :=0;--不符合条件的记录条数
type station_record is record(
strCode varchar2(200),
strName varchar2(200),
planQuantity number(19,2),
configStock number(19,2),
configQuantity number(19,2),
stockQuantity number(19,2)
);--定义新类型来存放存储过程返回的结果
tmp_record station_record;
begin
tmp := 'call plan_station_contrast(:param1,:param2) ';
execute immediate tmp using groupid,out mycur ;--调用存储过程
--游标默认已打开,因为存储过程中是open refcursor for
if(mycur%isopen) then
dbms_output.put_line('打开');
else
dbms_output.put_line('关闭');
end if;
loop
fetch mycur into tmp_record;
exit when mycur%notfound;
if tmp_record.planQuantity<>tmp_record.configQuantity or tmp_record.stockQuantity<>tmp_record.configStock then
res:=res+1;
end if;
end loop;
close mycur;--关闭游标
return res;
end fun_plan_station_contrast;
　　

create or replace procedure plan_station_contrast(
groupid in varchar2 ,--到站计划主表id，非空
refCursor out sys_refcursor)
is
planDate  varchar2(6);--计划年月
deptCode  varchar2(10);--上报单位编码
begin
select p.plan_date,p.dept_code into planDate,deptCode from p_station_group p where id=to_number(groupid);
open refcursor for 'select nvl(station.oil_code,config.oil_code) oilCode,nvl(station.oil_name,config.oil_name) oilName
,nvl(station.planQuantity,0) planQuantity  --到站上报
,nvl(station.configStock,0)  configStock   --外采上报
,nvl(config.configQuantity,0) configQuantity --直炼配置
,nvl(config.stockQuantity,0) stockQuantity --外采配置
from
--到站计划
(select p.oil_code,p.oil_name
,sum(plan_Quantity)-sum(special_Quantity) planQuantity
,sum(stock_Quantity) configStock
from
(select oil_code,oil_name
,decode(special,''0'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0)plan_Quantity
,decode(special,''1'',nvl(train_quantity, 0),0) special_Quantity
,decode(special,''2'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0) stock_Quantity
from p_station where sid = '||groupid||'
)p
group by p.oil_code,p.oil_name) station
full join
--配置计划
(
select p.oil_code,p.oil_name
,sum(decode(a.dtype,''factory'',decode(a.f_type,1,p.plan_quantity,0),''stock'',0,p.plan_quantity)) configQuantity --配置量
,sum(decode(a.dtype,''factory'',decode(a.f_type,1,0,p.plan_quantity),''stock'',p.plan_quantity,0)) stockQuantity --外采量
from p_disbtn p
left outer join P_CONFIG_PLAN_FLOW f on p.flow_code=f.flow_code --关联配置计划流向
left outer join acc_domain a on p.company_code=a.code
where bill_month='''||planDate||'''
and (select status from p_disbtn_group where bill_month='''||planDate||''')=''2''
and f.company_code='''||deptCode||'''
and p.plan_quantity>0
group by p.oil_code,p.oil_name
) config
on station.oil_code=config.oil_code ';
end plan_station_contrast;