oracle 函数调用存储过程操作

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;