• 周四. 3月 28th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

存储过程中update,然后用sql%判断update是否成功的存储过程

admin

11月 28, 2021

–更新用户状态
PROCEDURE P_UPDATE_USER_STATUS
(
v_SrcID IN NUMERIC,
v_DstID IN NUMERIC,
v_DstType IN NUMERIC,
v_Offline IN NUMERIC
)
IS
BEGIN
IF v_DstType=1 THEN –群组
BEGIN
–获取群组用户
IF v_Offline=0 THEN
BEGIN
UPDATE T_OFFLINE_NUM SET OFFLINE_NUM=OFFLINE_NUM+1 WHERE SRC_ID=v_SrcID AND DST_ID=v_DstID AND DST_TYPE=v_DstType;
IF sql%rowcount<1 THEN
INSERT INTO T_OFFLINE_NUM(SRC_ID,DST_ID,DST_TYPE,OFFLINE_NUM) VALUES(v_SrcID,v_DstID,v_DstType,1);
END IF;
END;
ELSE
BEGIN
UPDATE T_OFFLINE_NUM SET OFFLINE_NUM=0 WHERE SRC_ID=v_SrcID AND DST_ID=v_DstID AND DST_TYPE=v_DstType;
IF sql%rowcount<1 THEN
INSERT INTO T_OFFLINE_NUM(SRC_ID,DST_ID,DST_TYPE,OFFLINE_NUM) VALUES(v_SrcID,v_DstID,v_DstType,0);
END IF;
END;
END IF;
END;
ELSE
BEGIN –好友间消息
–更新自己状态
UPDATE T_OFFLINE_NUM SET OFFLINE_NUM=0 WHERE SRC_ID=v_SrcID AND DST_ID=v_DstID AND DST_TYPE=v_DstType;
IF sql%rowcount<1 THEN
BEGIN
INSERT INTO T_OFFLINE_NUM(SRC_ID,DST_ID,DST_TYPE,OFFLINE_NUM) VALUES(v_SrcID,v_DstID,v_DstType,0);
END;
END IF;
–更新目标用户
IF v_Offline=0 THEN –目标用户离线
BEGIN
UPDATE T_OFFLINE_NUM SET OFFLINE_NUM=OFFLINE_NUM+1 WHERE SRC_ID=v_DstID AND DST_ID=v_SrcID AND DST_TYPE=v_DstType;
IF sql%rowcount<1 THEN
BEGIN
INSERT INTO T_OFFLINE_NUM(SRC_ID,DST_ID,DST_TYPE,OFFLINE_NUM) VALUES(v_DstID,v_SrcID,v_DstType,1);
END;
END IF;
END;
ELSE
BEGIN
UPDATE T_OFFLINE_NUM SET OFFLINE_NUM=0 WHERE SRC_ID=v_DstID AND DST_ID=v_SrcID AND DST_TYPE=v_DstType;
IF sql%rowcount<1 THEN
BEGIN
INSERT INTO T_OFFLINE_NUM(SRC_ID,DST_ID,DST_TYPE,OFFLINE_NUM) VALUES(v_DstID,v_SrcID,v_DstType,0);
END;
END IF;
END;
END IF;
END;
END IF;
COMMIT;
END;

上面的每条IF sql%rowcount<1 THEN,都是针对该语句上面的update语句的执行结果

删除也一样

–删除专家
PROCEDURE P_DELETE_NEWS
(
v_InfoID IN NUMERIC,
v_delNum OUT NUMERIC
)
IS
BEGIN
DELETE FROM T_EXPERT_INFO WHERE EXPERT_ID=v_InfoID;
v_delNum:=sql%rowcount;
COMMIT;

END P_DELETE_NEWS;

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注