To invoke the oracle9i stored procedure experience summary in. Net

oracle| stored procedures that call oracle9i stored procedures in. NET can use multiple data providers, such as Oralceclient,oledb. This article will use Oralceclient as an example. NET language in C #.



First, call a stored procedure with input and output parameters

First, the stored procedure is created in Oralce as follows:

Create or Replace procedure Getrecords (name_out out varchar2,age_in in varchar2) as

Begin

Select NAME into Name_out from test where age = age_in;

End



Then, make the following call in the C # project:

String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Getrecords";

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("Name_out", oracletype.varchar,20);

Cmd. parameters["Name_out"]. Direction = ParameterDirection.Output;

Cmd. Parameters.Add ("age_in", 21);



Try

{

cn. Open ();

Cmd. ExecuteNonQuery ();

Console.WriteLine ("Name is:{0}", cmd. parameters["Name_out"]. Value.tostring ());

cn. Close ();

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}

Summary:

The above is a very common calling method, but I am not so smooth when I do this sample program. One of the OracleClient to point out here is that for parameter names, you must have the same name as the parameters in the stored procedure definition, or you will report an error. For example, if you change the "Name_out" in your code to another name, you will report an exception. But I tried to replace oralceclient with OLE DB, but I didn't have the problem. I wonder if this will be improved in the new version of the data provider?

Second, call stored procedures that do not return data

First, the stored procedure is created in Oralce as follows:

Create or Replace procedure Insertrecord (UserID in Varchar2,

UserName in Varchar2,
Userage in VARCHAR2) is
Begin
INSERT into test values (UserID, UserName, userage);
End



Then, make the following call in the C # project:



String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Insertrecord";

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("UserID", "007");

Cmd. Parameters.Add ("UserName", "Dell");

Cmd. Parameters.Add ("Userage", "40");



Try

{

cn. Open ();

Cmd. ExecuteNonQuery ();

Console.WriteLine ("Record inserted!");

cn. Close ();

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}





Summary:

Stored procedures that do not return data are generally delete, Insert, update, and so on. Although their SQL statements are different, the invocation aspect is the same. As long as the correct parameters are passed in, there is generally no problem. However, it is important to note that when using Oralceclient as a data provider, the parameter name must be consistent with the stored procedure definition! The order in which the parameters are added is irrelevant because there are parameter names as differences.



Iii. IDENTITY and SEQUENCE

In SQL Server, it's easy to define an incrementing column, but I can't find a way to set it up in Oracle. But after checking some information, I know that there is an object named sequence in Oracle that produces a unique serial number, similar to identity in SQL Server. So, I did the following experiment:



First, a sequence object named Test_seq is created in Oracle, and the SQL statement is as follows:

Create sequence Test_seq

MinValue 100

MaxValue 999

Start with 102

Increment by 1

NoCache;



Syntax should be easy to understand, the minimum maximum value of the Minvalue,maxvalue, the initial value is 102 (this number is dynamic, I created the time set is 100, after inserting 2 data automatically increased by 2), increment of course is step. You can use Test_seq.nextval to access the next serial number in Pl/sql, and use Test_seq.currval to access the current serial number.



With sequence defined, the next step is to create a stored procedure insertrecordwithsequence:

-This time I modified the definition of the test table, unlike the previous example. Among them, UserID is PK.

Create or Replace procedure insertrecordwithsequence (UserID out number,

UserName in Varchar2,

Userage in number)

Is

Begin

INSERT into Test (ID, name, age)--Inserts a record, the PK value gets from the Sequece

VALUES (Test_seq.nextval, UserName, userage);

/* Returns the PK value. Pay attention to the usage of the dual table *

Select Test_seq.currval into UserID from dual;

End Insertrecordwithsequence;



Next, it's called in C #:

String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Insertrecordwithsequence";

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("UserID", Oracletype.number);

Cmd. parameters["UserID"]. Direction = ParameterDirection.Output;

Cmd. Parameters.Add ("UserName", "Jack");

Cmd. Parameters.Add ("Userage", 40);



Try

{

cn. Open ();

int rowaffected = cmd. ExecuteNonQuery ();

Console.WriteLine (' {0} row was inserted. ", rowaffected);

Console.WriteLine ("Insert Row ID: {0}", cmd.) parameters["UserID"]. Value.tostring ());

cn. Close ();

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}

Summary:

Using the Sequece object makes it easy to create unique sequences, and calls in stored procedures are also handy, as long as Sequence_name.nextval and Sequence.currval get the next and current sequence values. The dual watch is worth noting.



Iv. read the returned result set using DataReader

In order for a stored procedure to return a result set, a cursor variable must be defined as an output parameter. This is a big difference from SQL Server! and also use Oracle "package" (Package) concept, it seems a bit cumbersome, but it will feel very convenient after the familiar.

About the concept of "package", there are a lot of content can be referred to, this will not repeat. First, I created a package called testpackage, which is defined by the header:

Create or Replace package testpackage is

Type mycursor is REF CURSOR; --Defining a cursor variable

Procedure Getrecords (ret_cursor out mycursor); --Define the procedure, using the cursor variable as the return parameter

End Testpackage;



Inclusion is defined in this way:

Create or Replace package body Testpackage is

/* Process Body * *

Procedure Getrecords (Ret_cursor out MyCursor) as

Begin

Open Ret_cursor for SELECT * from test;

End Getrecords;

End Testpackage;



Have everything, let's call the front desk to try:

String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Testpackage.getrecords"; Note Call method

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("Ret_cursor", oracletype.cursor); Note the type here

Cmd. parameters["Ret_cursor"]. Direction = ParameterDirection.Output;



Try

{

cn. Open ();

OracleDataReader dr = cmd. ExecuteReader ();

int i = 1;

while (Dr. Read ())

{

Console.WriteLine ("Record {0}:", i++);

Console.WriteLine ("Id:{0} Name:{1} age:{2}",

Dr. GetOracleNumber (0),

Dr. Getoraclestring (1),

Dr. GetOracleNumber (2));

Console.WriteLine ();

}

Dr. Close (); Use up the DataReader object to remember to close in time

cn. Close (); The connection cannot be closed until the DataReader object is closed

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}



Please see the results:

Record 1:

id:100 Name:tony age:23



Record 2:

id:101 Name:jack age:40



------------------End-------------------



Summary:

Packages are Oracle-specific concepts that cannot be matched in SQL Server. In my opinion, the package is a bit like VC + + class, Baotou is the. h file, the package body is. cpp file. Baotou is only responsible for the definition, the package is responsible for specific implementation. If the package returns more than one cursor, DataReader accesses the cursors in the order that you add them to the Parameters collection, rather than in the order in which they appear in the procedure. You can use the DataReader NextResult () method to advance to the next cursor.



Fill the dataset with the returned result set

Ado. NET discards the concept of the recordset in the original ADO and replaces it with a new dataset. The dataset can provide more powerful features! With the previous foundation, it is also very easy to use Ado.net to return a dataset from Oralce. The main idea is to use DataAdapter to receive the returned cursor and then fill the dataset with the DataAdapter Fill () method. In the following example, I still use the Testpackage package that was built in the previous example.



The following is the main code for the foreground call:

String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Testpackage.getrecords";

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("Ret_cursor", oracletype.cursor);

Cmd. parameters["Ret_cursor"]. Direction = ParameterDirection.Output;



Try

{

cn. Open ();

OracleDataAdapter da = new OracleDataAdapter (cmd);

DataSet ds = new DataSet ();

Da. Fill (ds, "TestTable");

cn. Close ();

for (int i = 0;i <= ds. tables["TestTable"]. rows.count-1;i++)

{

String id = ds. tables["TestTable"]. rows[i]["ID"]. ToString ();

String name = ds. tables["TestTable"]. rows[i]["NAME"]. ToString ();

String age = ds. tables["TestTable"]. Rows[i]["Age"]. ToString ();

Console.WriteLine ("Record {0}:", i+1);

Console.WriteLine ("Id:{0}\tname:{1}\tage:{2}\n", id,name,age);

}

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}

Summary:

The result of the program call is the same as the result of the DataReader call just now. This only shows how to use Ado.net to invoke an Oracle stored procedure and how to populate the dataset. As to how to manipulate a dataset, this is not the scope of this article. Interested readers can refer to MSDN and related books.



Vi. updating the database with DataAdapter

Usually retrieving the dataset with DataAdapter will make some modifications to the dataset and then update the database (Microsoft recommends using DataReader instead of a dataset if only to get the data). However, updating the database through stored procedures is not so simple as to simply update through the DataAdapter Update () method. You must manually add InsertCommand, DeleteCommand, and UpdateCommand to DataAdapter, because the stored procedure is uninformed about the details of these operations and must be artificially given.

In order to achieve this goal, I perfected the previous testpackage package, Baotou as follows:

Create or Replace package testpackage is

Type mycursor is REF CURSOR;

Procedure Updaterecords (id_in in Number,newname into varchar2,newage in number);

Procedure Selectrecords (ret_cursor out mycursor);

Procedure Deleterecords (id_in in number);

Procedure Insertrecords (name_in in varchar2, age_in in number);

End Testpackage;



The package body is as follows:

Create or Replace package body Testpackage is
Procedure Updaterecords (id_in in number, newName in varchar2, newage in number) as
Begin
Update Test Set age = newage, name = newName where id = id_in;
End Updaterecords;

Procedure Selectrecords (Ret_cursor out MyCursor) as
Begin
Open Ret_cursor for SELECT * from test;
End Selectrecords;

Procedure Deleterecords (id_in in number) as
Begin
Delete from Test where id = id_in;
End Deleterecords;

Procedure Insertrecords (name_in in varchar2, age_in in number) as
Begin
INSERT into test values (Test_seq.nextval, name_in, age_in);

--test_seq is a built sequence object, please refer to the previous example

End Insertrecords;
End Testpackage;



The front call code is as follows, a little cumbersome, please read patiently:

String connectionString = "Data Source=yxzhang;" User Id=yxzhang; Password=yxzhang ";

String querystring = "Testpackage.selectrecords";

OracleConnection cn = new OracleConnection (connectionString);

OracleCommand cmd = new OracleCommand (QUERYSTRING,CN);

Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("Ret_cursor", oracletype.cursor);

Cmd. parameters["Ret_cursor"]. Direction = ParameterDirection.Output;



Try

{

cn. Open ();

OracleDataAdapter da = new OracleDataAdapter (cmd);

DataSet ds = new DataSet ();

Da. Fill (ds, "TestTable");

cn. Close ();

int count = ds. tables["TestTable"]. Rows.Count;



/* Print Original record * *

Console.WriteLine ("Old Records is:");

for (int i = 0;i <= count-1;i++)

{

String id = ds. tables["TestTable"]. rows[i]["ID"]. ToString ();

String name = ds. tables["TestTable"]. rows[i]["NAME"]. ToString ();

String age = ds. tables["TestTable"]. Rows[i]["Age"]. ToString ();



Console.WriteLine ("Record {0}:", i+1);

Console.WriteLine ("Id:{0}\tname:{1}\tage:{2}\n", id,name,age);

}



Da. SelectCommand = cmd; Specify SelectCommand for DataAdapter



OracleCommand updatecmd = new OracleCommand ("Testpackage.updaterecords", CN);

Updatecmd.commandtype = CommandType.StoredProcedure;

UPDATECMD.PARAMETERS.ADD ("id_in", oracletype.number,3, "id");

UPDATECMD.PARAMETERS.ADD ("NewName", oracletype.varchar,20, "NAME");

UPDATECMD.PARAMETERS.ADD ("NewAge", oracletype.number,3, "age");

Da. UpdateCommand = Updatecmd; Specify UpdateCommand for DataAdapter



OracleCommand deletecmd = new OracleCommand ("Testpackage.deleterecords", CN);

Deletecmd.commandtype = CommandType.StoredProcedure;

DELETECMD.PARAMETERS.ADD ("id_in", oracletype.number,3, "id");

Da. DeleteCommand = Deletecmd; Specify DeleteCommand for DataAdapter



OracleCommand insertcmd = new OracleCommand ("Testpackage.insertrecords", CN);

Insertcmd.commandtype = CommandType.StoredProcedure;

INSERTCMD.PARAMETERS.ADD ("name_in", oracletype.varchar,20, "name");

INSERTCMD.PARAMETERS.ADD ("age_in", oracletype.number,3, "age");

Da. InsertCommand = Insertcmd; Specify InsertCommand for DataAdapter



DataTable newtable = ds. tables["TestTable"];



/* Modify the first record * *

Newtable.rows[0]["age"] = 22;

newtable.rows[0]["NAME" = "John";



/* Delete a record * *

NEWTABLE.ROWS[2]. Delete ();



/* Insert a record/*

DataRow NewRow = Newtable.newrow ();

newrow["NAME" = "Bob";

Newrow["Age"] = 99;

NEWTABLE.ROWS.ADD (NewRow);



cn. Open ();

Da. Update (newtable); Update changes to the database

Newtable.clear (); Empty DataTable

Da. Fill (newtable); Get the changed data

cn. Close ();



/* Print New record * *

Console.WriteLine ("New Records is:");

for (int i = 0;i <= newtable.rows.count-1;i++)

{

String id = newtable.rows[i]["id"]. ToString ();

String name = newtable.rows[i]["Name"]. ToString ();

String age = newtable.rows[i][' age ']. ToString ();



Console.WriteLine ("Record {0}:", i+1);

Console.WriteLine ("Id:{0}\tname:{1}\tage:{2}\n", id,name,age);

}

}

catch (OracleException ex)

{

Console.WriteLine ("Exception occurred!");

Console.WriteLine ("The Exception Message is:{0}", ex. Message.tostring ());

}

Finally

{

Console.WriteLine ("------------------End-------------------");

}



The results of the operation are as follows:

Old Records are:

Record 1:

id:100 Name:tony age:23



Record 2:

id:101 Name:jack age:34



Record 3:

id:103 Name:newadd age:100



New Records is:

Record 1:

id:100 Name:john age:22



Record 2:

id:101 Name:jack age:34



Record 3:

id:104 Name:bob age:99



------------------End-------------------



Summary:

It is useful to update the database using the update () method, but it is only limited to a single table, or it is convenient to perform the update operation directly with the OracleCommand Excutenonquery () method for table joins.



Vii. Summary of this article:

Using ado.net in. NET to invoke stored procedures, functions, packages, etc. in oralce9i can improve efficiency and complete some complex business logic. However, there is a big difference between invoking Oracle stored procedures and SQL Server, and one of the most obvious ideas is that all return values must appear as output parameters during the Oracle process. In Oracle, to return a result set, the REF CURSOR must be used as the return parameter, so the package is needed. A package is similar to a class in C + +, encapsulates some global variables, constants, and functions, and can have its own private variables in the function. By returning the cursor to the application, the application can receive it with DataReader or DataAdapter, and then do some processing. You can also update the database with the DataAdapter Update () method, which requires you to give some action to update the database (define the process for it).

As the author is learning while writing, the above examples unavoidably some improper place, please advise!