当存储过程参数中有游标时，调用方法如下

variable tempCur refcursor;//必须声明全局绑定变量

declear

month varchar2(10) := '2009-09';

begin

PKG_SUBSTATION_LINE.GET_SUBSTATION_VOLTAGE_TABLE(month,:tempCur);

end;

 

程序包如下：

 

CREATE OR REPLACE PACKAGE PKG_SUBSTATION_LINE

AS

  type lineTable is ref cursor;

  procedure GET_SUBSTATION_VOLTAGE_TABLE(vMonth in varchar2, cur_lineTable out lineTable);

END;

CREATE OR REPLACE PACKAGE BODY PKG_SUBSTATION_LINE

AS

  procedure GET_SUBSTATION_VOLTAGE_TABLE(vMonth in varchar2, cur_lineTable out lineTable)

  is

  nCount number;

  strTempTable varchar2(1000);

  strInsertTable varchar2(500);

  strDeleteTable varchar2(200);

  strSeleteTable varchar2(200);

  cursor station_cursor is SElECT X_XLMC, X_BDZ FROM XSFX WHERE X_XSLB = '分线线损';

  cursorrecord_station station_cursor%rowtype;

  nInMonth number := 0;

  nOutMonth number := 0;

  nLossValueMonth number := 0;

  nLossRateMonth number := 0;

  nInYear number := 0;

  nOutYear number := 0;

  nLossValueYear number := 0;

  nLossRateYear number := 0;

  begin

    strTempTable := 'CREATE GLOBAL TEMPORARY TABLE GLOBAL_SUBSTATION_LINE(SUBSTATIONNAME VARCHAR2(40 BYTE), LINENAME VARCHAR2(40 BYTE), PROVIDEVALUE_MONTH NUMBER(15,2), USEVALUE_MONTH NUMBER(15,2), LOSSVALUE_MONTH NUMBER(15,2), LOSSRATE_MONTH NUMBER(10,2), PROVIDEVALUE_YEAR NUMBER(15,2), USEVALUE_YEAR NUMBER(15,2), LOSSVALUE_YEAR NUMBER(15,2), LOSSRATE_YEAR NUMBER(10,2)) ON COMMIT PRESERVE ROWS';

    strInsertTable := 'INSERT INTO GLOBAL_SUBSTATION_LINE VALUES(:stationName, :lineName, :provideMonth, :useMonth, :lossMonth, :rateMonth, :provideYear, :useYear, :lossYear, :rateYear)';

    strDeleteTable := 'DELETE FROM GLOBAL_SUBSTATION_LINE';

    strSeleteTable := 'SELECT * FROM GLOBAL_SUBSTATION_LINE';

    SELECT COUNT(*) INTO nCount FROM USER_TABLES WHERE TABLE_NAME = UPPER('GLOBAL_SUBSTATION_LINE');

    if nCount <= 0 then

      execute immediate strTempTable;

    else

      execute immediate strDeleteTable;

    end if;

 

    open station_cursor;

    loop

      fetch station_cursor into cursorrecord_station;

      exit when station_cursor%notfound;

 

      SELECT NVL(SUM(DLZ*HJFS), 0) into nInMonth FROM XSFXBG_MONTH WHERE RQ = vMonth AND XSLB = '分线线损' AND XLMC = cursorrecord_station.X_XLMC AND JCGX = '进';

      SELECT NVL(SUM(DLZ*HJFS), 0) into nOutMonth FROM XSFXBG_MONTH WHERE RQ = vMonth AND XSLB = '分线线损' AND XLMC = cursorrecord_station.X_XLMC AND JCGX = '出';

      nLossValueMonth := nInMonth - nOutMonth;

      if nInMonth <> 0 then

        nLossRateMonth := nLossValueMonth/nInMonth*100;

      end if;

 

      SELECT NVL(SUM(DLZ*HJFS), 0) into nInYear FROM XSFXBG_MONTH WHERE RQ >= substr(vMonth, 1, 4)||'-01' AND RQ <= vMonth AND XSLB = '分线线损' AND XLMC = cursorrecord_station.X_XLMC AND JCGX = '进';

      SELECT NVL(SUM(DLZ*HJFS), 0) into nOutYear FROM XSFXBG_MONTH WHERE RQ >= substr(vMonth, 1, 4)||'-01' AND RQ <= vMonth AND XSLB = '分线线损' AND XLMC = cursorrecord_station.X_XLMC AND JCGX = '出';

      nLossValueYear := nInYear - nOutYear;

      if nInYear <> 0 then

        nLossRateYear := nLossValueYear/nInYear*100;

      end if;

      execute immediate strInsertTable using cursorrecord_station.X_BDZ, cursorrecord_station.X_XLMC, nInMonth/1000, nOutMonth/1000, nLossValueMonth/1000, nLossRateMonth, nInYear/1000, nOutYear/1000, nLossValueYear/1000, nLossRateYear;

 

      nInMonth := 0;

      nOutMonth := 0;

      nLossValueMonth := 0;

      nLossRateMonth := 0;

      nInYear := 0;

      nOutYear := 0;

      nLossValueYear := 0;

      nLossRateYear := 0;

    end loop;

    close station_cursor;

    open cur_lineTable for strSeleteTable;

    commit;

  end GET_SUBSTATION_VOLTAGE_TABLE;

END;