Oracle儲存過程例子
Dal.cs
OracleCommand myCmd = new OracleCommand(); --例項化
myCmd.CommandType = CommandType.StoredProcedure; ---儲存過程名稱
myCmd.CommandText = "PK_APAR.GET_ORG_TRADING_RECORD"; ---儲存過程名稱
myCmd.Parameters.Add("V_ORG_ID", OracleDbType.Long).Value = orgId; --引數
myCmd.Parameters.Add("V_START_TIME", OracleDbType.Date).Value = StartTime; --引數
myCmd.Parameters.Add("V_END_TIME", OracleDbType.Date).Value = EndTime; --引數
myCmd.Parameters.Add("V_DIRECTION", OracleDbType.Varchar2).Value = direction; --引數
myCmd.Parameters.Add("L_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; --輸出引數
myCmd.Parameters.Add("RETURN_CODE", OracleDbType.Int64).Direction = ParameterDirection.Output; --輸出引數
myCmd.Parameters.Add("RETURN_TEXT", OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output; --輸出引數
DataSet ds = Utilities.FillData(myCmd);
return ds;
Utilities.cs
public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD)
{
return FillData(myCMD,ConfigurationSettings.AppSettings["CenterDBConnStr"] --資料庫連線符);
}
public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD,string connectString)
{
Oracle.DataAccess.Client.OracleConnection myConn = new Oracle.DataAccess.Client.OracleConnection(connectString);
myConn.Open();
Oracle.DataAccess.Client.OracleTransaction trans = myConn.BeginTransaction();
myCMD.Connection = myConn;
Oracle.DataAccess.Client.OracleDataAdapter myAdapter = new Oracle.DataAccess.Client.OracleDataAdapter(myCMD);
DataSet myData = new DataSet();
try
{
myAdapter.Fill(myData);
trans.Commit();
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if( ConnectionState.Open == myConn.State )
{
myConn.Close();
}
}
return myData;
}
WebCofing
<!--使用者資料庫連線-->
PROCEDURE GET_ORG_TRADING_RECORD(V_ORG_ID IN TP_ORG.ORG_ID%TYPE,
V_START_TIME IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
V_END_TIME IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
V_DIRECTION IN varchar2,
L_RESULT OUT REFCURSOR,
RETURN_CODE OUT NUMBER,
RETURN_TEXT OUT VARCHAR2) IS
BEGIN
OPEN L_RESULT FOR
SELECT b.account_name,
a.trade_time,
d.base_code_name,
a.trade_direction,
a.trade_fee,
a.account_balance,
a.work_number,
a.trade_remark
from TA_AABANK_TRADE a, ta_aabank b, tp_org c, ta_base_code d
where a.aabank_id = b.aabank_id
and b.aabank_id = c.aabank_id
and a.trade_type_id = d.base_code_id
and c.org_id = V_ORG_ID
and a.trade_direction like V_DIRECTION
and a.trade_time between V_START_TIME and V_END_TIME;
RETURN_CODE := 0;
RETURN_TEXT := '成功!';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN_CODE := 1;
RETURN_TEXT := SQLERRM || '失敗!';
END GET_ORG_TRADING_RECORD;
OracleCommand myCmd = new OracleCommand(); --例項化
myCmd.CommandType = CommandType.StoredProcedure; ---儲存過程名稱
myCmd.CommandText = "PK_APAR.GET_ORG_TRADING_RECORD"; ---儲存過程名稱
myCmd.Parameters.Add("V_ORG_ID", OracleDbType.Long).Value = orgId; --引數
myCmd.Parameters.Add("V_START_TIME", OracleDbType.Date).Value = StartTime; --引數
myCmd.Parameters.Add("V_END_TIME", OracleDbType.Date).Value = EndTime; --引數
myCmd.Parameters.Add("V_DIRECTION", OracleDbType.Varchar2).Value = direction; --引數
myCmd.Parameters.Add("L_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; --輸出引數
myCmd.Parameters.Add("RETURN_CODE", OracleDbType.Int64).Direction = ParameterDirection.Output; --輸出引數
myCmd.Parameters.Add("RETURN_TEXT", OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output; --輸出引數
DataSet ds = Utilities.FillData(myCmd);
return ds;
Utilities.cs
public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD)
{
return FillData(myCMD,ConfigurationSettings.AppSettings["CenterDBConnStr"] --資料庫連線符);
}
public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD,string connectString)
{
Oracle.DataAccess.Client.OracleConnection myConn = new Oracle.DataAccess.Client.OracleConnection(connectString);
myConn.Open();
Oracle.DataAccess.Client.OracleTransaction trans = myConn.BeginTransaction();
myCMD.Connection = myConn;
Oracle.DataAccess.Client.OracleDataAdapter myAdapter = new Oracle.DataAccess.Client.OracleDataAdapter(myCMD);
DataSet myData = new DataSet();
try
{
myAdapter.Fill(myData);
trans.Commit();
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if( ConnectionState.Open == myConn.State )
{
myConn.Close();
}
}
return myData;
}
WebCofing
<!--使用者資料庫連線-->
PROCEDURE GET_ORG_TRADING_RECORD(V_ORG_ID IN TP_ORG.ORG_ID%TYPE,
V_START_TIME IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
V_END_TIME IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
V_DIRECTION IN varchar2,
L_RESULT OUT REFCURSOR,
RETURN_CODE OUT NUMBER,
RETURN_TEXT OUT VARCHAR2) IS
BEGIN
OPEN L_RESULT FOR
SELECT b.account_name,
a.trade_time,
d.base_code_name,
a.trade_direction,
a.trade_fee,
a.account_balance,
a.work_number,
a.trade_remark
from TA_AABANK_TRADE a, ta_aabank b, tp_org c, ta_base_code d
where a.aabank_id = b.aabank_id
and b.aabank_id = c.aabank_id
and a.trade_type_id = d.base_code_id
and c.org_id = V_ORG_ID
and a.trade_direction like V_DIRECTION
and a.trade_time between V_START_TIME and V_END_TIME;
RETURN_CODE := 0;
RETURN_TEXT := '成功!';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN_CODE := 1;
RETURN_TEXT := SQLERRM || '失敗!';
END GET_ORG_TRADING_RECORD;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29406868/viewspace-1064265/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql儲存過程例子MySql儲存過程
- Oracle儲存過程Oracle儲存過程
- Oracle儲存過程乾貨(一):儲存過程基礎Oracle儲存過程
- Oracle儲存過程-1Oracle儲存過程
- oracle的儲存過程Oracle儲存過程
- Oracle建立儲存過程Oracle儲存過程
- oracle plsql儲存過程OracleSQL儲存過程
- ORACLE 儲存過程示例Oracle儲存過程
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle
- 學習儲存過程時寫的個例子儲存過程
- 原創:oracle 儲存過程Oracle儲存過程
- oracle 儲存過程批次提交Oracle儲存過程
- oracle 儲存過程學習Oracle儲存過程
- [Flashback]Oracle flashback儲存過程Oracle儲存過程
- Oracle儲存過程學習Oracle儲存過程
- oracle的儲存過程格式Oracle儲存過程
- java 呼叫oracle 儲存過程JavaOracle儲存過程
- oracle--08儲存過程Oracle儲存過程
- oracle儲存過程書寫格式Oracle儲存過程
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- C#呼叫 oracle儲存過程C#Oracle儲存過程
- Oracle儲存過程基本語法Oracle儲存過程
- [Procedure]Oracle之分頁儲存過程Oracle儲存過程
- oracle 儲存過程遊標的使用Oracle儲存過程
- C#呼叫Oracle儲存過程C#Oracle儲存過程
- java中呼叫ORACLE儲存過程JavaOracle儲存過程
- 用java呼叫oracle儲存過程JavaOracle儲存過程
- Oracle 基礎 ----procedure(儲存過程)Oracle儲存過程
- oracle父儲存過程呼叫子儲存過程procedure與輸出引數Oracle儲存過程
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 儲存過程儲存過程
- ORACLE儲存過程中建立子過程的測試!Oracle儲存過程
- oracle儲存過程和觸發器Oracle儲存過程觸發器
- LightDB/PostgreSQL 相容Oracle儲存過程SQLOracle儲存過程
- Oracle SQL Developer Debug儲存過程OracleSQLDeveloper儲存過程
- Oracle 儲存過程加密之wrap工具Oracle儲存過程加密
- oracle 儲存過程重新編譯方法Oracle儲存過程編譯
- oracle儲存過程分頁程式碼Oracle儲存過程