oracle 函數中調用預存程序

  項目中開發了一個預存程序來給報表提供資料來源，而程式商務邏輯中需要有一個對該資料來源進行判斷的功能，開始是使用拼接sql的方式在預存程序中對應sql的外麵包裝一層來進行處理，但是覺得這種方式拼接的sql太長，而且以後商務邏輯改變要同時修改預存程序和代碼，容易發生遺漏，故產生了在函數中處理預存程序的結果（遊標）的想法。但是在網上搜尋發現對預存程序返回的遊標進行處理的例子很少，自己弄了半天終於成功了，在此記錄一下。

預存程序plan_station_contrast

create or replace procedure plan_station_contrast( groupid in varchar2 ,--到站計劃主表id，非空 refCursor out sys_refcursor)isplanDate  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;

函數fun_plan_station_contrast

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;

結果如下：