Oracle預存程序使用總結

標籤：des   style   blog   io   color   ar   os   使用   sp   

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)ISSQLTEXT 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 <> ‘‘ THENSQLTEXT := SQLTEXT || ‘ and s.OrderId like ‘‘%‘||ORDER_ID||‘%‘‘‘;END IF;IF CUSTOMER <> ‘‘ THENSQLTEXT := SQLTEXT || ‘ and s.CustomerId = ‘||to_number(CUSTOMER);END IF;IF STATUS <> ‘‘ and STATUS <> ‘0‘ THENSQLTEXT := 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_BASEIScursor c_base ISSELECT 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;BEGINFOR 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;

 

Oracle預存程序使用總結