查询输入的门牌号码是否在标准门牌库中存在。存在则返回相应的号码。
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[];
parameters[]=new OracleParameter("mplxs",OracleType.VarChar);
parameters[]=new OracleParameter("bzdms",OracleType.VarChar);
parameters[]=new OracleParameter("mphms",OracleType.VarChar);
parameters[]=new OracleParameter("remphm",OracleType.VarChar,);
parameters[].Value="大门牌";
parameters[].Value="吕岭路";
parameters[].Value="1号";
parameters[].Value=ParameterDirection.Output;
foreach(OraclePrameter parameter in parameters)
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();
retRepMphm=parameters[].value==null?"":parameters[].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 ,'[^,]+',,level) as we
from dual
connect by level<=length(mphms )-length(replace(mphms,','))+
begin
for row_ma in curmas loop
begin
select count(*) into countnum from YW_LMP_QD where BZHM=row_mas.we ;
if(count(*)<>) 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