有两个包,如下:
1:
create or replace package body PKG_GEANJBZLS is
--根据个案id获取信息,并进行预处理锁定
procedure P_GEAN_LOCK(a_cid in varchar2,a_userid in varchar2)
is
v_chepaihm t_geanjbzl.c_weizhangzt%type; --车牌号码
v_zigezhm t_geanjbzl.c_zigezh%type; --资格证号码
v_anhao t_geanjbzl.c_anhao%type; --案号
v_ret varchar2(2);
begin
--根据个案id获取其他信息
select c_weizhangzt,c_zigezh,c_anhao into v_chepaihm,v_zigezhm,v_anhao
from t_geanjbzl where c_id = a_cid;
--如果案号为空
if v_anhao is null then
raise_application_error(-20001,'运政受理号不能为空!');
return ;
end if;
--如果车牌号不为空,就对车辆锁定
if v_chepaihm is not null Then
dev.Pkg_Geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
end if;
--如果资格证号不为空
if v_zigezhm is not null Then
--如果车牌号也不为空,就对车辆和人都进行锁定
if v_chepaihm is not null then
dev.pkg_geanycl.p_Lock(v_zigezhm,'t_congyeryjbxx',v_anhao,'',a_userid,'0',v_ret);
dev.pkg_geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
--如果车牌为空,就只锁定人
else
dev.pkg_geanycl.p_Lock(v_zigezhm,'t_congyeryjbxx',v_anhao,'',a_userid,'0',v_ret);
end if ;
end if;
end P_GEAN_LOCK;
end PKG_GEANJBZLS;2:
create or replace package body pkg_geanycl Is --个案预处理
--锁定对象
PROCEDURE p_Lock(a_master varchar2,a_table varchar2,a_anhao varchar2,a_memo varchar2,
a_userid VARCHAR2, a_type varchar2,a_result OUT varchar2)
IS
v_master varchar2(36); --车牌号码或从业人员服务资格证号
v_nums integer; --计数器
v_reason varchar2(50); --锁定原因
v_chepaiys t_geanjbzl.c_chepaiys%TYPE; --车牌颜色
v_geanid t_geanjbzl.c_id%TYPE; --个案ID
v_shifouklc t_geanjbzl.c_shifouklc%TYPE; --是否克隆车
v_suoleix t_suodingjl.c_suoleix%TYPE; --锁类型(投诉和违章)
BEGIN
a_result:='0';
if a_master IS NULL Then
raise_application_error(-20001,'主键无效!');
END IF;
IF a_anhao IS NULL THEN
raise_application_error(-20001,'锁定原因不能为空!');
END IF;
IF a_type IS NULL THEN
raise_application_error(-20001,'标识不能为空!');
END IF;
if a_table<>'T_CONGYERYJBXX' and a_table<>'T_CHELIANGJBXX' Then
return ; --返回
END If;
--产生锁定原因,a_reason为案号
if a_type=0 Then
raise_application_error(-20001,'BBBBBB'); --test
SELECT t.c_id,t.c_shifouklc,nvl(t.c_chepaiys,'0') INTO v_geanid,v_shifouklc,v_chepaiys FROM t_geanjbzl t WHERE t.C_ANHAO = a_anhao;
v_reason:='因违章被锁定,案号:'|| a_anhao;
v_suoleix := '违章';
else
SELECT t.c_id,decode(accuse_type,'A6D30238-04C4-4812-8527-D2973A5D87BE','黄','蓝') INTO v_geanid,v_chepaiys FROM t_accuse t WHERE t.tis_handle_no = a_anhao;
v_reason:='投诉:'|| a_anhao;
v_suoleix := '投诉';
end if;
IF v_chepaiys='0' THEN
raise_application_error(-20001,'请输入车牌颜色,否则无法锁定车辆档案!');
RETURN;
END IF;
--如果是克隆车不必锁原车
IF nvl(v_shifouklc,'0') = '1' THEN
RETURN;
END IF;
--如果是锁人,得到从业人员ID
if a_table='T_CONGYERYJBXX' then
select count(*) into v_nums from T_CONGYERYFWZGZ
where C_FUWUZGZHM = a_master;
if v_nums < 1 then
return;
else
select C_CONGYERY INTO v_master FROM T_CONGYERYFWZGZ
where C_FUWUZGZHM = a_master and rownum < 2;
end if ;
end if;
--如果是锁车,得到车辆ID
if a_table='T_CHELIANGJBXX' then
select count(*) into v_nums FROM T_CHELIANGJBXX
where C_CHEPAIHM = a_master and c_Chepaiys = v_chepaiys;
if v_nums < 1 then
return;
else
select C_ID INTO v_master FROM T_CHELIANGJBXX
where C_CHEPAIHM = a_master and c_Chepaiys = v_chepaiys and rownum < 2;
end if ;
end if;
--判断是否已存在相同的锁定记录
select count(*) into v_nums from t_suodingjl where c_suodingdx=v_master
and ((c_suodingyy=v_reason AND c_laiyuanid IS NULL ) OR (c_laiyuanid = v_geanid AND c_laiyuanid IS NOT NULL))
and c_zhuangtai='锁定';
if v_nums > 0 THEN
UPDATE t_suodingjl SET c_suodingyy = v_reason where c_suodingdx = v_master
and ((c_suodingyy = v_reason AND c_laiyuanid IS NULL ) OR (c_laiyuanid = v_geanid AND c_laiyuanid IS NOT NULL))
and c_zhuangtai='锁定' AND c_suodingyy <> v_reason;
a_result:='0';
return ;
end if ;
--插入锁定日志
INSERT INTO t_suodingjl(c_id,c_suodingdx,c_suodingdxb,c_suodingrq,c_suodingyy,c_suodingczr,c_zhuangtai,c_jiesuorq,c_jiesuoczr,c_jiesuoyy,c_laiyuanid,c_suoleix)
Values(sys_guid(),v_master,a_table,SYSDATE,v_reason,a_userid,'锁定',NULL,NULL,NULL,v_geanid,v_suoleix);
--锁定
if a_table='T_CONGYERYJBXX' then
UPDATE t_congyeryjbxx SET c_zhuangtai='锁定' WHERE c_id = v_master;
else
update T_CHELIANGJBXX set c_danganzt='锁定' WHERE c_id = v_master;
end if;
a_result:='1';
COMMIT;
EXCEPTION WHEN Others Then
raise_application_error(-20001,SQLERRM);
END p_Lock;
end pkg_geanycl;包中都有包规则说明
第一个包中的存储过程调第二个包中的存储过程为什么不执行也不报错????
1:
create or replace package body PKG_GEANJBZLS is
--根据个案id获取信息,并进行预处理锁定
procedure P_GEAN_LOCK(a_cid in varchar2,a_userid in varchar2)
is
v_chepaihm t_geanjbzl.c_weizhangzt%type; --车牌号码
v_zigezhm t_geanjbzl.c_zigezh%type; --资格证号码
v_anhao t_geanjbzl.c_anhao%type; --案号
v_ret varchar2(2);
begin
--根据个案id获取其他信息
select c_weizhangzt,c_zigezh,c_anhao into v_chepaihm,v_zigezhm,v_anhao
from t_geanjbzl where c_id = a_cid;
--如果案号为空
if v_anhao is null then
raise_application_error(-20001,'运政受理号不能为空!');
return ;
end if;
--如果车牌号不为空,就对车辆锁定
if v_chepaihm is not null Then
dev.Pkg_Geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
end if;
--如果资格证号不为空
if v_zigezhm is not null Then
--如果车牌号也不为空,就对车辆和人都进行锁定
if v_chepaihm is not null then
dev.pkg_geanycl.p_Lock(v_zigezhm,'t_congyeryjbxx',v_anhao,'',a_userid,'0',v_ret);
dev.pkg_geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
--如果车牌为空,就只锁定人
else
dev.pkg_geanycl.p_Lock(v_zigezhm,'t_congyeryjbxx',v_anhao,'',a_userid,'0',v_ret);
end if ;
end if;
end P_GEAN_LOCK;
end PKG_GEANJBZLS;2:
create or replace package body pkg_geanycl Is --个案预处理
--锁定对象
PROCEDURE p_Lock(a_master varchar2,a_table varchar2,a_anhao varchar2,a_memo varchar2,
a_userid VARCHAR2, a_type varchar2,a_result OUT varchar2)
IS
v_master varchar2(36); --车牌号码或从业人员服务资格证号
v_nums integer; --计数器
v_reason varchar2(50); --锁定原因
v_chepaiys t_geanjbzl.c_chepaiys%TYPE; --车牌颜色
v_geanid t_geanjbzl.c_id%TYPE; --个案ID
v_shifouklc t_geanjbzl.c_shifouklc%TYPE; --是否克隆车
v_suoleix t_suodingjl.c_suoleix%TYPE; --锁类型(投诉和违章)
BEGIN
a_result:='0';
if a_master IS NULL Then
raise_application_error(-20001,'主键无效!');
END IF;
IF a_anhao IS NULL THEN
raise_application_error(-20001,'锁定原因不能为空!');
END IF;
IF a_type IS NULL THEN
raise_application_error(-20001,'标识不能为空!');
END IF;
if a_table<>'T_CONGYERYJBXX' and a_table<>'T_CHELIANGJBXX' Then
return ; --返回
END If;
--产生锁定原因,a_reason为案号
if a_type=0 Then
raise_application_error(-20001,'BBBBBB'); --test
SELECT t.c_id,t.c_shifouklc,nvl(t.c_chepaiys,'0') INTO v_geanid,v_shifouklc,v_chepaiys FROM t_geanjbzl t WHERE t.C_ANHAO = a_anhao;
v_reason:='因违章被锁定,案号:'|| a_anhao;
v_suoleix := '违章';
else
SELECT t.c_id,decode(accuse_type,'A6D30238-04C4-4812-8527-D2973A5D87BE','黄','蓝') INTO v_geanid,v_chepaiys FROM t_accuse t WHERE t.tis_handle_no = a_anhao;
v_reason:='投诉:'|| a_anhao;
v_suoleix := '投诉';
end if;
IF v_chepaiys='0' THEN
raise_application_error(-20001,'请输入车牌颜色,否则无法锁定车辆档案!');
RETURN;
END IF;
--如果是克隆车不必锁原车
IF nvl(v_shifouklc,'0') = '1' THEN
RETURN;
END IF;
--如果是锁人,得到从业人员ID
if a_table='T_CONGYERYJBXX' then
select count(*) into v_nums from T_CONGYERYFWZGZ
where C_FUWUZGZHM = a_master;
if v_nums < 1 then
return;
else
select C_CONGYERY INTO v_master FROM T_CONGYERYFWZGZ
where C_FUWUZGZHM = a_master and rownum < 2;
end if ;
end if;
--如果是锁车,得到车辆ID
if a_table='T_CHELIANGJBXX' then
select count(*) into v_nums FROM T_CHELIANGJBXX
where C_CHEPAIHM = a_master and c_Chepaiys = v_chepaiys;
if v_nums < 1 then
return;
else
select C_ID INTO v_master FROM T_CHELIANGJBXX
where C_CHEPAIHM = a_master and c_Chepaiys = v_chepaiys and rownum < 2;
end if ;
end if;
--判断是否已存在相同的锁定记录
select count(*) into v_nums from t_suodingjl where c_suodingdx=v_master
and ((c_suodingyy=v_reason AND c_laiyuanid IS NULL ) OR (c_laiyuanid = v_geanid AND c_laiyuanid IS NOT NULL))
and c_zhuangtai='锁定';
if v_nums > 0 THEN
UPDATE t_suodingjl SET c_suodingyy = v_reason where c_suodingdx = v_master
and ((c_suodingyy = v_reason AND c_laiyuanid IS NULL ) OR (c_laiyuanid = v_geanid AND c_laiyuanid IS NOT NULL))
and c_zhuangtai='锁定' AND c_suodingyy <> v_reason;
a_result:='0';
return ;
end if ;
--插入锁定日志
INSERT INTO t_suodingjl(c_id,c_suodingdx,c_suodingdxb,c_suodingrq,c_suodingyy,c_suodingczr,c_zhuangtai,c_jiesuorq,c_jiesuoczr,c_jiesuoyy,c_laiyuanid,c_suoleix)
Values(sys_guid(),v_master,a_table,SYSDATE,v_reason,a_userid,'锁定',NULL,NULL,NULL,v_geanid,v_suoleix);
--锁定
if a_table='T_CONGYERYJBXX' then
UPDATE t_congyeryjbxx SET c_zhuangtai='锁定' WHERE c_id = v_master;
else
update T_CHELIANGJBXX set c_danganzt='锁定' WHERE c_id = v_master;
end if;
a_result:='1';
COMMIT;
EXCEPTION WHEN Others Then
raise_application_error(-20001,SQLERRM);
END p_Lock;
end pkg_geanycl;包中都有包规则说明
第一个包中的存储过程调第二个包中的存储过程为什么不执行也不报错????
太长了你不用看,就看下面 这行就行了
if v_chepaihm is not null Then
dev.Pkg_Geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
end if;
就是下面这行。。if v_chepaihm is not null Then
dev.Pkg_Geanycl.p_Lock(v_chepaihm,'t_cheliangjbxx',v_anhao,'',a_userid,'0',v_ret);
end if;
cenlmmx(学海无涯苦作舟) ( ) 信誉:93 2006-02-28 17:19:00 得分: 0
有意思,你不如先在sqlplus试着调用这个过程,那早就知道错误原因了.
-------------------------------------------------------------100分是你的了