1111111111111

交谈中请勿轻信汇款、中奖信息，勿轻易拨打陌生电话。

周绍清 2009-12-21 10:14:46
这也是操作存储过程的方式

周绍清 2009-12-21 10:14:33
// 获取行业应用接入省代码
public static String getInfoAppJoinPrvCode(String subsNumber)
{
String prvcode = "";

DASCommand dasCmd = DASCommandFactory.getDASCommand();
//一个命令请求对象
CmdRequest cmdRequest = new CmdRequest();
//设置命令请求属性
cmdRequest.setProcedure("c_p_pub_getPrvcodeByhyyy(:subsnumber, :o_Ret)");
//设置输入输出参数
cmdRequest.addDataParam("subsnumber", DataType.STRING, subsNumber,DataParam.PARAM_TYPE_IN);
cmdRequest.addDataParam("o_Ret", DataType.INT, null,DataParam.PARAM_TYPE_OUT);
cmdRequest.setDataSource("csppubDataSource");

DataResult dataResult = null;
try
{
dataResult = dasCmd.execute(cmdRequest);
List<DataParam> outParams = dataResult.getOutParams();
if (outParams != null) {

DataParam ret = (DataParam) outParams.get(0);

if (null != ret)
{
prvcode = ret.getString();

}

}

}
catch (DASException e)
{
e.printStackTrace();
}
finally
{
if (dataResult != null)
{
dataResult.close();
}
}

return prvcode;
}
周绍清 2009-12-21 10:15:52
create or replace procedure c_p_pub_getPrvcodeByhyyy
/*
Description : 获取行业应用接入省代码
Author : 周绍清 11040
Date : 2009-12-18
Comments :
retvalue ：省代码
*/
(subsnumber in varchar2, --号码
o_ret out varchar2 --如返回值为 02为本地，09 为无效数据，其它返回全网（省代码）
) is
v_local varchar2(50); --02 本地 ， 01 全网
v_prv varchar2(30); --省编码

begin
--查询接入方式 本地接入
select t.localflag
into v_local
from t_c_bi_servicecode t
where (t.servicecode_start = substr(subsNumber, 0, 2) or
t.servicecode_start = substr(subsNumber, 0, 3) or
t.servicecode_start = substr(subsNumber, 0, 5) or
t.servicecode_start = substr(subsNumber, 0, 8))
and t.servicetype = '03' -- 行业应用
and rownum = 1;

if v_local = '02' then
o_ret := '02'; --本地，返回根据举报受理号码查询归属省
return;
end if;

if v_local = '01' then
--v_local = '01' 全网接入 查找省编码，如果查到数据，返回省编码，否则，视为无效数据
select tt.province
into v_prv
from (select substr(subsNumber, length(t1.servicecode_start) + 1, 3) a1
from t_c_bi_servicecode t1
where (t1.servicecode_start = substr(subsNumber, 0, 2) or
t1.servicecode_start = substr(subsNumber, 0, 3) or
t1.servicecode_start = substr(subsNumber, 0, 5) or
t1.servicecode_start = substr(subsNumber, 0, 8))
and t1.servicetype = '03') aa,
t_c_bi_tradecode tt
where aa.a1 = tt.trade_startcode
and rownum = 1;
o_ret := v_prv;
return ;
end if;

o_ret := '09'; --失败，默认为无效数据
return;


EXCEPTION
when others then
o_ret := '09'; --失败，默认为无效数据
rollback;

end c_p_pub_getPrvcodeByhyyy;

周绍清 20:32:52
select s.* from t_sr_srmappingother s where s.mappingtype='SR_PROBLEMPROCESS' ; 两张表的关联