Use Oracle stored procedure notes in C #
1. Call the stored procedure containing the OUT / IN OUT type parameters
Store procedure:
Create or Replace Procedure "Site_EDitsitedataDataExist"
(id_ number,
Name_ varchar2,
HTTPROOT_ VARCHAR2,
Flag out integer // OUT only has output function in OUT as input / output type
AS
Tempnum integer;
Begin
Flag: = 0;
Select Count (ID) INTO TEMPNUM from Website_Info WHERE Name = Name_ and ID <> id_;
IF Tempnum> 0 THEN
FLAG: = 3;
END IF;
SELECT Count (ID) INTO TEMPNUM from Website_info where httproot = httproot_ and id <> id_;
IF Tempnum> 0 THEN
Flag: = 4;
END IF;
COMMIT;
END;
/
Call method:
OracleParameter Retpar = New OracleParameter ("CHANNELID", ORACLETYPE.NUMBER);
RETPAR.DIRECTION = parameterDirection.output; // The type matches the type during the stored procedure
// If you are in OUT type, you should // 明 uTPUT
OracleParameter [] Param = New OracleParater [2]
{
New OracleParameter ("SubjectID", Oracletype.varchar, 60)
}
Param [0] .value = 0;
OracleHelper.executeReader (OracleHelper.conn_String_Base, CommandType.StoredProcedure,
"site_editsitedataDataexist", param);
/ / Must use the ExecuteReader method when there is a return value
Object val = param [3] .value;
Return int.parse (Val.toString ());
2. Store procedure return record set
The stored procedure must be written in the package, then call.
Writing:
Create or replace package pkg_cms
AS
TYPE MyRCTYPE IS REF CURSOR;
Procedure site_getsitedata (id_ number, p_rc out myrctype);
END PKG_CMS;
/
Create Or Replace Package Body PKG_CMS
AS
Procedure Site_getsitedata (ID_ number, p_rc out myrctype)
IS
Begin
Open p_rc for
SELECT ID, NAME, URL, FOLDER_NAME, DESCCMS, CHAR_NAME,
DB_ADDRESS, DB_USER, DB_PASSWORD, DB_NAME, DB_CONNSTRING, HTTPROOT
From Website_Info
Where id = id_;
End site_getsitedata;
END PKG_CMS;
/
transfer:
OracleParameter [] Param = New OracleParater [2]
{
New OracleParameter (PARM_ID_, ORACLETY.NUMBER, 8), New OracleParameter ("p_rc", oracletype.cursor, 2000, parameterdirection.output, true, 0, 0, "", datarowversion.default, convert.dbnull) // Type of cursor declared in the body
}
Param [0] .value = siteId;
Return OracleHelper.executeReader (OracleHelper.conn_String_Base, CommandType.StoredProcedure, "PKG_CMS.SITE_GETSITEDATA", PARAM
// Write the package before calling
3. Other methods during Oracle stored procedures
String operation
INSTR (STR, Maker) // Take the character of characters in strings
Substr (str, beginnum, len) // tap
TO_CHAR () // Turn the number to the string
|| // String is equivalent to number
Length (Oldword) // Take a string length
Time class
TO_DATE ('DateStr', 'YYYY-MI-DD') // string into Date type "'YYYY-MM-DD'"
Date1-Date2 = Title Community Communication Welcome to MSN: Yutao728@hotmail.com
