1.使用Oracle存储过程查询结果集：
网上写的都是他妈的扯淡！其实一句话就行了，你只要返回一个游标就OK了。具体代码如下:
CREATE OR REPLACE PROCEDURE PR_ORDER_LIST( ORDER_ID VARCHAR2, BEGINDATE VARCHAR2, ENDDATE VARCHAR2, CUSTOMER VARCHAR2, STATUS VARCHAR2, V_TEMP out sys_refcursor ) IS SQLTEXT VARCHAR2(4000); BEGIN SQLTEXT := 'select s.OrderId,c.CustomerName,s.SendAddress,p.TextValue as BussinessStatus, NVL(s.UpdateT,s.InsertT) as OrderDate from SaleOrder s LEFT JOIN CustomerInfo c on s.CustomerId = c.CustomerId and c.CustomerType = ''C'' LEFT JOIN DataParameter p on s.BussinessStatus = p.Code and p.ParameterType = ''BussinessStatus'' where to_char(NVL(s.UpdateT,s.InsertT),''yyyyMMdd'') between '''||BEGINDATE||''' and '''||ENDDATE||''' '; IF ORDER_ID <> '' THEN SQLTEXT := SQLTEXT || ' and s.OrderId like ''%'||ORDER_ID||'%'''; END IF; IF CUSTOMER <> '' THEN SQLTEXT := SQLTEXT || ' and s.CustomerId = '||to_number(CUSTOMER); END IF; IF STATUS <> '' and STATUS <> '0' THEN SQLTEXT := SQLTEXT || ' and s.BussinessStatus in ('||STATUS||')'; END IF; SQLTEXT := SQLTEXT || ' order by NVL(s.UpdateT,s.InsertT) desc'; dbms_output.put_line(SQLTEXT); --这个是打印 --execute immediate SQLTEXT; 这个是动态执行sql,写游标可以不用这个，用下面那个 --如果要往变量里面注入值，需要使用上面这个 open V_TEMP FOR SQLTEXT; --彺游标里面注入值 ，这里是注入了一个sql字符串 END;
2.然后就是包的定义，包就相当于C#里面的类，可以封装类型、方法等等。
3.C#程序段使用方法：
/// <summary> /// 订单列表 /// </summary> /// <param name="begin"></param> /// <param name="end"></param> /// <param name="orderid"></param> /// <param name="customer"></param> /// <param name="status"></param> /// <returns></returns> public DataTable SaleOrderList(string begin, string end, string orderid, string customer, string status) { DBManager vDBManager = new DBManager(); vDBManager.ConnectionOpen(); vDBManager.AddParameter(":begin", begin); vDBManager.AddParameter(":end", end); vDBManager.AddParameter(":orderid", orderid); vDBManager.AddParameter(":customer", customer); vDBManager.AddParameter(":status", status); vDBManager.AddOutParameter("V_TEMP", null); //这里注意，一定要是out类型的参数 DataTable dt = vDBManager.RunProcedure("PR_ORDER_LIST", "table1"); vDBManager.ConnectionClose(); return dt; }
/// <summary> /// 添加输出参数 /// </summary> /// <param name="key"></param> /// <param name="value"></param> public void AddOutParameter(string key, object value) { command.Parameters.Add(new OracleParameter(key, OracleType.Cursor)).Direction = ParameterDirection.Output; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">过程名</param> /// <param name="parameters">参数</param> /// <param name="tableName">表名</param> /// <returns></returns> public DataSet ExecuteProcName(string storedProcName, string tableName) { using (OracleDataAdapter adapter = new OracleDataAdapter()) { DataSet ds = new DataSet(); adapter.SelectCommand = BuildQueryCommand(storedProcName); adapter.Fill(ds, tableName); command.Parameters.Clear(); return ds; } }
另外，这个是游标的使用方法：
CREATE OR REPLACE PROCEDURE PR_MOD_BASE IS cursor c_base IS SELECT MIN(INVENTORY_DATE) IDATE,KUNNR,MATNR FROM STOCK_BASE_DATA GROUP BY TO_CHAR(INVENTORY_DATE,'yyyy-MM-dd'), KUNNR,MATNR HAVING count(*) > 1; c_row c_base%rowtype; BEGIN FOR c_row in c_base loop DELETE FROM STOCK_BASE_DATA WHERE INVENTORY_DATE = c_row.IDATE and KUNNR = c_row.KUNNR and MATNR = c_row.MATNR; end loop; COMMIT; END;
CREATE OR REPLACE PROCEDURE "KX_LIST_FROMSTORE_ADD_ITEM"(IN_ORDER_ID NVARCHAR2, OU_TEMP OUT SYS_REFCURSOR) IS /*订单转交货的时候用来获取订单行项目带到交货单编辑界面的存储过程*/ BEGIN OPEN OU_TEMP FOR SELECT DISTINCT A.DETAILID, A.ORDERID, A.PRODUCTID, PI2.BARCODE, PI1.PRODUCTNAME, PI1.PRODUCTCODE, PI1.SPEC, A.UNITID, UI.UNITNAME, SNUM(A.DETAILID, A.SEQ) AS QUANTITY, A.DISCOUNT, KX_GET_FORMATNUMBER(A.UNITPRICE) UNITPRICE, A.TAXRATE, CASE ORDERTYPE WHEN 'SH01' THEN DECODE(A.PRODUCTTYPE, 'TA01', KX_GET_FORMATNUMBER(A.PRODUCTMONEY), 0) ELSE KX_GET_FORMATNUMBER(A.PRODUCTMONEY) END AS PRODUCTMONEY, A.TAXMONEY, KX_GET_FORMATNUMBER(A.PAYABLEMONEY) PAYABLEMONEY, A.ISGIVEAWAY, A.DETAILID AS COLGUID, A.PARENTGUID, A.ORDERID AS VGBEL, A.SEQ AS VGPOS, A.ORDERID AS RELEVANCEORDER, A.MEMO, A.CHARG, O.STOREID, O.SERVICEP, O.MEMO AS ORDERMEMO, O.PAYTYPE, O.TOTALMONEY, KX_GET_FORMATNUMBER(PI2.SALEPRICE) AS INITUNITPRICE, A.SEQ, A.QUANTITY AS ORDERQUANTITY, (SELECT P.TEXTVALUE FROM DATAPARAMETER P WHERE P.CODE = A.PRODUCTTYPE AND P.PARAMETERTYPE = 'ProductType') AS PRODUCTTYPE, --KX_GET_DO_COUNT(a.PRODUCTID, a.UNITID, a.SURPLUSQUANTITY) as OutQuantity1, FUN_HASORDER(A.PRODUCTID, A.ORDERID, 'XXX', A.UNITID, A.SEQ) AS OUTQUANTITY FROM SALEORDERDETAIL A LEFT JOIN SALEORDER O ON A.ORDERID = O.ORDERID LEFT JOIN CUSTOMERINFO C ON C.CUSTOMERID = O.CUSTOMERID LEFT JOIN PRODUCTINFO PI1 ON A.PRODUCTID = PI1.PRODUCTID LEFT JOIN PRODUCTPRICE PI2 ON A.PRODUCTID = PI2.PRODUCTID AND PI2.UNITID = A.UNITID AND PI2.CUSTOMERTYPE2 = C.CUSTOMERTYPE2 LEFT JOIN UNITINFO UI ON A.UNITID = UI.UNITID WHERE A.ORDERID = IN_ORDER_ID AND A.CLOSEDESC = '00' AND A.STATUS <> 'C' ORDER BY A.SEQ ASC; KX_HISTORY_LOG(IN_ORDER_ID, '1', '订单'||IN_ORDER_ID||'转交货', 'M'); END;