c 获取oracle输出,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();

}