C # using Oracle Stored Procedure notes

1. Call includes Out/in outStored Procedure of type parameters
Stored Procedure:
Create or replace procedure "SITE_EDITSITEDATAEXIST"
(Id _ number,
Name _ varchar2,
HttpRoot _ varchar2,
Flag out integer) // out only provides the output function. in out is the input/output type.
As
TempNum integer;
Begin
If tempNum> 0 then
End if;

If tempNum> 0 then
End if;
Commit;
End;
/

Call method:

// If it is an in out type, the response must be: // Ming InputOutput
{
New OracleParameter ("subjectId", OracleType. VarChar, 60)
};

OracleHelper. ExecuteReader (OracleHelper. CONN_STRING_BASE, CommandType. StoredProcedure,

"Site_EditSiteDataExist", param );

// The ExecuteReader method must be used when return values exist.

Return int. Parse (val. ToString ());

 
2. Stored Procedure return record set

The stored procedure must be written in the package and then called.

Package Syntax:

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

Select Id, Name, Url, Folder_Name, Desccms, Char_Name,

DB_Address, DB_User, DB_Password, DB_Name, DB_ConnString, HttpRoot

From WebSite_Info

END site_GetSiteData;

END pkg_cms;

/

Call:

{

New OracleParameter (PARM_ID _, OracleType. Number, 8 ),

New OracleParameter ("p_rc", OracleType. cursor, 2000, ParameterDirection. output, true, 0, 0, "", DataRowVersion. default, Convert. DBNull) // The cursor type declared in the package body.

};

Return OracleHelper. ExecuteReader (OracleHelper. CONN_STRING_BASE, CommandType. StoredProcedure, "pkg_cms.site_GetSiteData", param );

// Write the package name before calling

3.OracleOther methods in the Stored Procedure

String operation
INSTR (STR, Maker) // obtain the character position in the string

SUBStr (str, beginnum, len) // obtain the substring

To_char () // convert a number to a string

| // Equals to +

LENGTH (Oldword) // obtain the string LENGTH

Time

To_date ('datestr', 'yyyy-Mi-dd') // convert string to date type "'yyyy-MM-DD '"