17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure getjob
(istitle number:=1,c1 out sys_refcursor, c2 out sys_refcursor)
is
begin
open c1 for select job_id,min_salary,max_salary from jobs;
open c2 for select job_title,min_salary,max_salary from jobs where istitle=1;
end;
/
using System;
using System.Data;
using System.Data.OracleClient;
namespace oraApp
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection conn =
new OracleConnection("Data Source=xe;User ID=hr;Password=hr;"))
{
conn.Open();
OracleCommand cmd = new OracleCommand("getjob", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("istitle", OracleType.Number).Value = 0;
cmd.Parameters.Add("c1",OracleType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add("c2", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter adpt = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
adpt.Fill(ds);
foreach (DataTable dt in ds.Tables)
{
if (dt.Rows.Count > 0)
{
Console.WriteLine("Tables: ");
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
}
}
}
Console.ReadKey();
}
}
}
}
SQL> CREATE OR REPLACE PROCEDURE test
2 (
3 cv_1 OUT SYS_REFCURSOR,
4 cv_2 OUT SYS_REFCURSOR,
5 v_a IN NUMBER DEFAULT NULL
6 )
7 as
8 begin
9 open cv_1 for select 'aa' from dual;
10 if v_a is not null then
11 begin
12 open cv_2 for select 'bb' from dual;
13 end;
14 end if;
15 end;
16 /
Procedure created.
SQL> exec test(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print :v1;
'A
--
aa
SQL>