C#执行oraclec存储过程获取返回值的一个问题（使用到临时表）

至于ORACLE有几种临时表，这里就不在叙述了，下面就使用中遇到的一个问题简单的描述一下；

1，新建临时表，脚本如下：

-- Create table
create global temporary table TMP_DATAORDER
(
  order_no VARCHAR2(256),
  qty      NUMBER(8)
)
on commit delete rows;

 

2，创建一个存储过程，该存储过程返回一个游标

create or replace procedure sp_get_material_requirment(LINENO      varchar2,
                                                         RETURN_LIST OUT SYS_REFCURSOR) as
  vErrormsg  varchar2(2000);
  vErrorCode varchar2(20);
  testCount  number;
  des        varchar(2000);
begin

  begin
  
 --获取需要计算需求的制造命令以及数量
 INSERT INTO TMP_DATAORDER    SELECT UG.ORDER_NO, COUNT(UG.UWIP_NO) AS LOT_UWIP_COUNT
  FROM
  ( 
    SELECT UWIP.* FROM (   
       SELECT ROWNUM,  KU.* FROM KIT_UWIP KU
      LEFT JOIN
      DATA_SCHEDULE  DS ON DS.ORDER_NO = KU.ORDER_NO
      WHERE DS.Line_No = LINENO AND  DS.STATUS < 2  AND KU.UWIP_STATUS < 2  ORDER BY DS.DATETIME_CREATED ASC, DS.ROUND_SEQ ASC, KU.UWIP_SEQ ASC )  UWIP  WHERE  ROWNUM < 500
      ) UG  GROUP BY UG.ORDER_NO ;
       

 

open RETURN_LIST for  SELECT BP.PART_NO, BP.PART_NAME, BP.PART_SPECIFICATION, BP.PART_DESCRIPTION,EB.NEED_PART_QTY,EB.CUR_PART_QTY FROM ( select PART_NO, SUM(NEED_PART_QTY) NEED_PART_QTY , SUM(CUR_PART_QTY) CUR_PART_QTY  FROM
  (
    SELECT   UGA.ORDER_NO, (DO.ORDER_QTY * UNIT_QTY) NEED_PART_QTY,
   DL.WORK_ORDER_NAME,DL.PART_NO, DL.UNIT_QTY, DL.TOTAL_QTY,  EP.CUR_PART_QTY   FROM TMP_DATAORDER  UGA
       LEFT JOIN DATA_ORDER  DO ON DO.ORDER_NO = UGA.ORDER_NO
       LEFT JOIN DATA_MATERIAL_LIST DL ON DL.WORK_ORDER_ID = DO.WORK_ORDER_ID
       LEFT JOIN (SELECT ORDER_NO, PART_NO, SUM(PART_QTY) AS CUR_PART_QTY FROM KIT_CALL_ORDER_REQ WHERE ORDER_NO IN (SELECT ORDER_NO FROM TMP_DATAORDER ) GROUP BY ORDER_NO, PART_NO  ) EP ON EP.PART_NO = DL.PART_NO
       ) GROUP BY PART_NO) EB
        LEFT JOIN BD_PART BP ON BP.PART_NO = EB.PART_NO;
  end;

 --COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    vErrorCode := Sqlcode;
    vErrormsg  := Sqlerrm;
    INSERT INTO KIT_QUEUE_LOG
      (ID,
       LOG_TYPE,
       DES,
       error_code,
       error_message,
       action_executed_object)
    VALUES
      (QUEUE_LOG_SEQUENCE.NEXTVAL,
       'Procedure Error',
       '指导仓库备料需求',
       vErrorCode,
       vErrormsg,
       'sp_get_material_requirment');
    COMMIT;
 
end sp_get_material_requirment;

3，注意上面的红色字体，不能要COMMIT，否则返回值是空，因为COMMIT已经提交了值，该临时表是事务性临时表；

4，c#该如何取值呢，OracleDataReader dr = cmd.ExecuteReader(); 一般这样会默认会执行COMMIT语句；所以

  while (dr.Read())     ------这样取值会错误
                {
                   ........

                }

 

5，正确的使用方式如下（注意红色字体）：

 using (OracleConnection conn = new OracleConnection(base.ConnStrGet()))
            {
           

                conn.Open();
                OracleTransaction trans = conn.BeginTransaction();
                OracleCommand cmd = BuildQueryCommand(conn, "sp_get_material_requirment", parameters);
                cmd.CommandType = CommandType.StoredProcedure;
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    MaterialRequirmentInfo mrInfo = new MaterialRequirmentInfo();
                    mrInfo.PartNo = dr["PART_NO"] == DBNull.Value ? "" : dr["PART_NO"].ToString();
                        }
                dr.Close();
                cmd.Dispose();
                trans.Commit();
                conn.Close();
            }

 

转载于:https://www.cnblogs.com/xiaxc889/archive/2012/11/30/2796882.html