Oracle存储过程 使用游标、数组的配合查询

查询输入的门牌号码是否在标准门牌库中存在。存在则返回相应的号码。

public string  GetValidate（）{

OracleConnection conn = ConnectOra();
string  retRepMphm;
try
{
 conn.open();
 OracleComandType cmd=conn.CreateCommand();
 cmd.CommandType=Commandype.StoredProcedure；
 cmd.CommandText=""sp_querybzdm;
 OracleParameter[] parameters =new OracleParameter[4];
 parameters[0]=new OracleParameter("mplxs",OracleType.VarChar);
 parameters[1]=new OracleParameter("bzdms",OracleType.VarChar);
 parameters[2]=new OracleParameter("mphms",OracleType.VarChar);
 parameters[3]=new OracleParameter("remphm",OracleType.VarChar,2000);

 parameters[0].Value="大门牌";
 parameters[1].Value="吕岭路";
 parameters[2].Value="1号";
 parameters[3].Value=ParameterDirection.Output;

foreach(OraclePrameter parameter in parameters) 
  cmd.Parameters.Add(parameter);
}
 cmd.ExecuteNonQuery();
 retRepMphm=parameters[3].value==null?"":parameters[3].Value.Tostring();
catch(Exception){}
 throw ex;
}
finaly{

conn.close();
}

create or replace proceduce sp_querybzdm
(
       mplxs in varchar2;----定义变量
       bzdm  in varchar2;
       mphms in varchar2;
       remphm out varchar2 ---输出变量
)   is
countnum  number;

--游标
cursor  curmas is 
               select regexp_substr(mphms ,'[^,]+',1,level) as we
               from dual
               connect by level<=length(mphms )-length(replace(mphms,','))+1

begin 
     for row_ma  in curmas loop
       begin
         select count(*) into countnum from YW_LMP_QD where BZHM=row_mas.we ;
         if(count(*)<>0)  then
           if str_result  is not null then
              str_result:=str_result||','|| row_ma.we
           else 
              str_result:=row_ma.we
           end if;
        end if;
       end 
     end loop;
     if    str_result is not null  then
      remphm:=rtrim(str_result,',');
     end if 
end