存储过程,判断子表数据的状态再更新主表数据状态
输入:主表编码、更新时间
CREATE OR REPLACE
procedure pro_proxy
(proxyno_1 in VARCHAR2,date_1 in varchar2)
as
icount number;
icount2 number;
begin
--查询明细总数和明细状态改变总数
select count(ST6_ID) into icount from ST_PROXYDETAIL where ST5_ID=(SELECT ST5_ID from ST_PROXY
where ST5_PROXYNO =proxyno_1);
select count(ST6_ID) into icount2 from ST_PROXYDETAIL where ST5_ID=(SELECT ST5_ID from ST_PROXY
where ST5_PROXYNO =proxyno_1) and DP1_ISSIGNIN =1;
--如果两个相等 则都更改了
if icount=icount2 then
-- 更新主表 全部更新 --状态(0初始状态,1部分更改,2全部更改)
UPDATE ST_PROXY SET ST5_SIGNINSTATE ='2',ST5_SignInDate=to_date(date_1, 'yyyy-mm-dd hh24:mi:ss') WHERE ST5_PROXYNO =proxyno_1;
else
-- 更新主表 部分更新
UPDATE ST_PROXY SET ST5_SIGNINSTATE ='1',ST5_SignInDate=to_date(date_1, 'yyyy-mm-dd hh24:mi:ss') WHERE ST5_PROXYNO =proxyno_1;
end if;
end;
调用:
exec pro_proxy('QWDF332','2018-09-12 12:12:11');