存储过程，删除--查询--修改

两张表 父表和字表
字表根据主键删除一条数据之后，再根据另一个参数（父id）查询是否还有其他数据，如果没有则修改父表 有无子项状态为0，如果有则修改为1，

delete from child where id = ?
select count(*) as count from child where pid = (select pid from child where id = ? ) and id != ?
if count > 0
status = 1;
else
status = 0;
update parent set status = status

delete from child where id = ?
select count(*) as count from child where pid = (select pid from child where id = ? ) and id != ?
if count > 0
status = 1;
else
status = 0;
update parent set status = status

CREATE OR REPLACE PROCEDURE "UPDATEFU_TABLE" (
IN "CID" VARCHAR(22))
P1: BEGIN
DECLARE COUNTFUID INTEGER;
DECLARE FID INTEGER;
SET COUNTFUID= SELECT CASE WHEN "TOTAL">0 THEN '1' ELSE '0' END SELECT COUNT(1) "TOTAL" FROM FU_TABLE WHERE ID=(SELECT PID FROM ZI_TABLE WHERE ID=CID);
--注意同一个子ID只能查到一个父ID,要不然就只能使用中间表、临时表先存储所有的父ID，或者先修改，再删除，但这样逻辑不对，且只能最后commit。
SET FID=SELECT DISTINCT PID FROM ZI_TABLE WHERE ID=CID;
DELETE FROM ZI_TABLE WHERE ID=CID;
UPDATE FU_TABLE SET STATUS=PID WHERE ID=FID;
COMMIT;
END P1;

个人之前有个做法是把有“无子项状态” 改为子项的数量表达

delete from child where id = ?
select count(*) as count from child where pid = (select pid from child where id = ? ) and id != ?
if count > 0
status = 1;
else
status = 0;
update parent set status = status

delete from child where id = ?
select count(*) as count from child where pid = (select pid from child where id = ? ) and id != ?
if count > 0
status = 1;
else
status = 0;
update parent set status = status

delete from child where id = ?
select count(*) as count from child where pid = (select pid from child where id = ? ) and id != ?
if count > 0
status = 1;
else
status = 0;
update parent set status = status