The stored procedure of Oracle returns the record set, and finds that the two Oracle segments are the same, but the C # section is slightly different and put up. I prefer to use the first one. C # Call the Oracle stored procedure to return result sets and functions Oracle segment:
First, create a package and package body in Oracle, and define the function and the returned result set of the stored procedure.
1: Create a package:
Create or replace package Scott. pk_wt
Is
Type mytype is ref cursor;
Procedure p_wt (MYCS out mytype );
Function f_get (STR in varchar2)
Return varchar2;
End;
/
Note: package is just a declaration. Here we define a stored procedure to return a collection and a function to return a string. 2: Create a package body:
Create or replace package body Scott. pk_wt
Is
Procedure p_wt (MYCS out mytype)
Is
Begin
Open MYCS for select * from test;
End p_wt; Function f_get (STR varchar2)
Return varchar2
Is
Str_temp varchar2 (100): = ''good luck! '';
Begin
Str_temp: = str_temp | STR;
Return str_temp;
End f_get; End pk_wt;
/
Note: here, the creation of the package body is a specific description and use, and how will it be implemented .. C # section:
in C #, Code consists of two parts: using functions, the other part is the result set.
define a connection and obtain it from webconfig:
private oracleconnection orcn = new oracleconnection (system. configuration. configurationsettings. appsettings ["Scott"]);
C # Call the Oracle function:
oraclecommand cmd = new oraclecommand ("pk_wt.f_get", orcn);
cmd. commandtype = commandtype. storedprocedure;
oracleparameter p1 = new oracleparameter ("str", oracletype. varchar, 10);
p1.direction = system. data. parameterdirection. indium Ut;
p1.value = This. textbox1.text;
oracleparameter P2 = new oracleparameter ("result", oracletype. varchar, 100);
p2.direction = system. data. parameterdirection. returnvalue;
cmd. parameters. add (P1);
cmd. parameters. add (P2);
orcn. open ();
cmd. executenonquery ();
orcn. close ();
This. button_function.text = p2.value. tostring ();
the result is the return variable of the system-defined function. Note that the return type of the function parameter must be specified, that is, Comm. And type also needs to be specified, and there is no difference with General stored procedures. C # Call the Oracle return result set:
Oraclecommand cmd = new oraclecommand ("pk_wt.p_wt", orcn );
Cmd. commandtype = commandtype. storedprocedure;
Oracleparameter p1 = new oracleparameter ("MYCS", oracletype. cursor );
P1.direction = system. Data. parameterdirection. output;
Cmd. Parameters. Add (P1 );
Oracledataadapter da = new oracledataadapter (CMD );
Dataset DS = new dataset ();
Da. Fill (DS, "test ");
This. datagrid1.datasource = Ds;
This. datagrid1.databind ();
There is nothing to say here .. The only difference is that the data type is cursor and the type is output .. Oracle Stored Procedure return record set Oracle stored procedures return record sets. The key is to use a cursor.
We certainly have a lot of contact with database cursor. We can use open, fetch, and close operations to control the cursor for various convenient operations. I will not repeat this example. We will introduce cursor variable ). Similar to a cursor, the cursor variable also points to the current row of a query result set. The difference is that the cursor variable can be opened for any type-similar query instead of bound to a specific query. With cursor variables, you can obtain more convenience in database data extraction.
First, create a table.
Create Table lihuan. bill_points
(
Points_id number (10, 0) not null,
Customer_id number (10, 0) not null,
Bill_point_no number (2, 0) default 1 not null,
Constraint pk_bill_points primary key (points_id)
)
/
Second, create a package
Create or replace package lihuan. yy_pkg_bill_point_no/* Get all the user's charged electricity numbers */
Is
Type t_cursor is ref cursor;
Procedure bill_point_no (p_customer_id bill_points.customer_id % type,
Re_cursor out t_cursor );
End;
/
Again, create the package body
Create or replace package body lihuan. yy_pkg_bill_point_no/* Get all the user's charged electricity numbers */
Is
Procedure bill_point_no (p_customer_id bill_points.customer_id % type,
Re_cursor out t_cursor)
Is
V_cursor t_cursor;
Begin
Open v_cursor
Select bill_point_no from bill_points where customer_id = p_customer_id;
Re_cursor: = v_cursor;
End;
End;
/
Finally, in. net Program Call.
Public dataset bill_point_no (string customer_id) // OK
{
Dataset dataset = new dataset ();
Hashtable ht = new hashtable ();
Ht. Add (p_customer_id, customer_id );
If (runprocedure (re_cursor, oracletype. cursor, ref dataset, HT, bmsoracleuser +. yy_pkg_bill_point_no.bill_point_no, bmsoracleconnectionstring ))
{
;
}
Else
{
Dataset = NULL;
}
Return dataset;
}
Public bool runprocedure (string returnparameter, oracletype paramtype, ref dataset, hashtable HT, string procedurename, string oracleconnection)
{
System. Data. oracleclient. oracleconnection dsconnection = new system. Data. oracleclient. oracleconnection (oracleconnection );
System. Data. oracleclient. oraclecommand dacommand = new system. Data. oracleclient. oraclecommand (procedurename, dsconnection );
Dsconnection. open ();
Dacommand. commandtype = commandtype. storedprocedure;
Idictionaryenumerator enumerator;
Enumerator = Ht. getenumerator ();
Object value = NULL;
Oracleparameter oracleparam;
Oracleparam = dacommand. Parameters. Add (New oracleparameter (returnparameter, paramtype ));
Oracleparam. Direction = parameterdirection. output;
While (enumerator. movenext ())
{
Value = enumerator. value;
Oracleparam = dacommand. Parameters. Add (New oracleparameter (enumerator. Key. tostring (), value ));
}
Oracledataadapter odadapter = new oracledataadapter (dacommand );
Try
{
Odadapter. Fill (Dataset );
Return true;
}
Catch (system. Exception E)
{
E. tostring ();
Return false;
}
Finally
{
Ht. Clear ();
Dacommand. Parameters. Clear ();
Dsconnection. Close ();
} |
