|
写一个过程,需要将一组ID传递给一个过程,然后这个列表将会在过程的SQL的where子句中使用到,例如: select * from my_table where my_id IN (传递给过程的ID的列表)
我编写的过程如下:
create or replace package test_modality_bdpart_pck is
type t_cursor is ref cursor;
TYPE exam_array is table of varchar2(100) index by BINARY_INTEGER;
procedure test_modality_prc(i_exam_uid IN exam_array,
cur_modality OUT t_cursor);
end test_modality_bdpart_pck;
create or replace package body test_modality_bdpart_pck is
procedure test_modality_prc(i_exam_uid IN exam_array,
cur_modality OUT t_cursor)
is
sql_mod VARCHAR2(4000) :='select exam_uid,modality from e_mod_tbl where exam_uid in (';
array_count number := 0;
begin
array_count := i_exam_uid.count;
if( array_count>1 ) then
for i in 1..array_count loop
if( i=1 ) then
sql_mod := concat(sql_mod,'''');
sql_mod := concat(sql_mod,i_exam_uid(0));
sql_mod := concat(sql_mod,'''');
else
sql_mod := concat(sql_mod,',');
sql_mod := concat(sql_mod,'''');
sql_mod := concat(sql_mod,i_exam_uid(i-1));
sql_mod := concat(sql_mod,'''');
end if;
end loop;
sql_mod := concat(sql_mod,')');
OPEN cur_modality FOR sql_mod;
else
dbms_output.put_line('i_exam_uid erroe !');
end if;
end test_modality_prc;
end test_modality_bdpart_pck;
但是不知道怎么写“i_exam_uid”这个参的类型,下面是我写的一个例子,请高手指点一下,该怎么修改。
string[] arrUID = { "20060811180452562000","20060811180453546000","20060811180454250000","20060811180455750000","20060811180456640000"};
OracleConnection conn = new OracleConnection(connstring);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "test_modality_bdpart_pck.test_modality_prc";
cmd.CommandType = CommandType.StoredProcedure;
/*********下面的两句对不对,不对怎么修改???****************/
cmd.Parameters.Add("i_exam_uid", OracleType.VarChar,100).Value = arrUID;
cmd.Parameters.Add("cur_modality", OracleType.Cursor).Direction =ParameterDirection.Output;
/*********上面的两句对不对,不对怎么修改???****************/
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
............................
conn.Close(); |
|