The following code uses system. Data. oracleclient (. NET for Oracle Data Provider ).
1. How to call functions with return values in Oracle PL/SQL? It can be called by matching the command and parameter objects.
The added parameter is matched as the return value and parameter. The first parameter is the return value, and then the parameters passed into PL/SQL.
Private void button#click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.say_hello ";
Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;
// Param returnvalue:
Cmd. Parameters. Add ("po_return", oracletype. varchar, 20 );
Cmd. Parameters ["po_return"]. Direction = parameterdirection. returnvalue;
// Param 1: note that here pi_name must match the name in PLSQL (Case Insensitive), otherwise a ORA-06550 error is thrown.
Cmd. Parameters. Add ("pi_name", oracletype. varchar, 10 );
Cmd. Parameters ["pi_name"]. Direction = parameterdirection. input;
Cmd. Parameters ["pi_name"]. value = "Firefox ";
Try
{
Conn. open ();
Cmd. executenonquery ();
String strret = cmd. Parameters ["po_return"]. value. tostring ();
MessageBox. Show (strret );
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
Conn. Close ();
}
}
Well, use some tips to call PL/SQL functions with returned values: Select... from dual
/// <Summary>
/// Call the function of oracle.
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Private void button2_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand ();
Cmd. Connection = conn;
Cmd. commandtype = commandtype. text;
Cmd. commandtext = "select plsql_hello_world.say_hello (: pi_name) from dual ";
Cmd. Parameters. Add ("pi_name", oracletype. varchar, 20 );
Cmd. Parameters ["pi_name"]. Direction = parameterdirection. input;
Cmd. Parameters ["pi_name"]. value = "Firefox ";
Try
{
Conn. open ();
String strret = cmd. executescalar () as string;
MessageBox. Show (strret );
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
Conn. Close ();
}
}
How to return the cursor. Here, cursor can only be output parameter. You must use executereader () to obtain cursor using command ()
Private void button3_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.open_one_cursor ";
Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;
// Param 1:
Cmd. Parameters. Add ("n_empno", oracletype. Number, 2 );
Cmd. Parameters ["n_empno"]. Direction = parameterdirection. input;
Cmd. Parameters ["n_empno"]. value = 0;
// Param 2:
Cmd. Parameters. Add ("io_cursor", oracletype. cursor );
Cmd. Parameters ["io_cursor"]. Direction = parameterdirection. output;
Oracledatareader ODR = NULL;
Try
{
Conn. open ();
ODR = cmd. executereader ();
While (ODR. Read ())
{
MessageBox. Show (ODR. getvalue (0). tostring ());
}
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
If (ODR! = NULL)
ODR. Close ();
Conn. Close ();
}
}
Of course, you can also use oracledataadapter:
Private void button5_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.open_one_cursor ";
Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;
// Param 1:
Cmd. Parameters. Add ("n_empno", oracletype. Number, 2 );
Cmd. Parameters ["n_empno"]. Direction = parameterdirection. input;
Cmd. Parameters ["n_empno"]. value = 30;
// Param 2:
Cmd. Parameters. Add ("io_cursor", oracletype. cursor );
Cmd. Parameters ["io_cursor"]. Direction = parameterdirection. output;
Oracledataadapter ADP = new oracledataadapter ();
ADP. selectcommand = cmd;
Try
{
Datatable dt = new datatable ();
ADP. Fill (DT );
This. Maid = DT;
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
}
Appendix: PL/SQL
Create or replace package body plsql_hello_world is </P> <p> function say_hello (pi_name in varchar2) return varchar2 is <br/> v_hello varchar2 (20 ); <br/> begin <br/> If pi_name is null then <br/> v_hello: = 'Hello world! '; <Br/> else <br/> v_hello: = 'hello' | pi_name; <br/> end if; <br/> return (v_hello ); <br/> end say_hello; </P> <p> procedure open_one_cursor (n_empno in number, <br/> io_cursor in out type_cursor) <br/> is <br/> v_cursor type_cursor; <br/> begin <br/> If n_empno = 10 or n_empno = 20 <br/> or n_empno = 30 then <br/> open v_cursor for <br/> select EMP. empno, <br/> EMP. ename, <br/> Dept. deptno, <br/> Dept. dname <br/> from EMP, DEPT <br/> where EMP. deptno = Dept. deptno <br/> and EMP. deptno = n_empno; <br/> else <br/> open v_cursor for <br/> select EMP. empno, <br/> EMP. ename, <br/> Dept. deptno, <br/> Dept. dname <br/> from EMP, DEPT <br/> where EMP. deptno = Dept. deptno; <br/> end if; <br/> io_cursor: = v_cursor; <br/> end open_one_cursor; </P> <p> procedure open_two_cursors (empcursor out type_cursor, <br/> deptcursor out type_cursor) <br/> is <br/> v_cursor1 type_cursor; <br/> v_cursor2 type_cursor; <br/> begin <br/> open v_cursor1 for select * from EMP; <br/> open v_cursor2 for select * from Dept; <br/> empcursor: = v_cursor1; <br/> deptcursor: = v_cursor2; <br/> end open_two_cursors; </P> <p> end plsql_hello_world; <br/>