本文转自 https://blog.csdn.net/qq_27610859/article/details/72397918
后台是.net写的,调用Oracle存储过程,其中返回两个游标,有一个游标的字段"link_type" decode(link_type, 0, NULL, link_type) link_type,服务端实体接link_type时,有时为int,有时为string。
PROCEDURE sp_getMyBlackListIncr(i_user_id IN NUMBER
,i_last_update_time IN VARCHAR2 --最后更新时间戳,格式YYYY-MM-DD HH24:MI:SS:FF3
,o_result OUT im_type.REF_CURSOR
,o_result1 OUT im_type.REF_CURSOR) IS
v_module NUMBER := 50531;
v_last_update_time TIMESTAMP(3);
v_get_time TIMESTAMP(3) := systimestamp;
BEGIN
IF i_last_update_time IS NOT NULL THEN
v_last_update_time := to_timestamp(i_last_update_time, 'YYYY-MM-DD HH24:MI:SS:FF3');
END IF;
IF systimestamp > v_last_update_time + 7
OR i_last_update_time IS NULL THEN
OPEN o_result FOR
SELECT partner_user_id --对方的用户id
,link_type
,(SELECT 1
FROM im_dev.im_friend_link fl
WHERE fl.user_id = i_user_id
AND fl.partner_user_id = a.partner_user_id
AND fl.link_del_type IN (0, 2)
UNION ALL
SELECT 0
FROM im_dev.im_stranger_link s
WHERE s.user_id = i_user_id
AND s.stranger_id = a.partner_user_id
AND nvl(s.isdel, 0) = 0) Friend_type
,user_name
,PHOTO
,0 del_flag --删除标记 0 不擅长 1 删除
FROM (SELECT black_user_id partner_user_id --对方的用户id
,link_type
FROM im_dev.im_blacklist a50515
WHERE a50515.user_id = i_user_id
AND link_type IN (1, 3)) a
INNER JOIN im_user b
ON a.partner_user_id = b.user_id;
OPEN o_result1 FOR
SELECT 1 INCR_ALL --增量、全量标记 1 全量 0 增量
,v_get_time get_time --拉取时间
FROM dual;
ELSE
OPEN o_result FOR
SELECT partner_user_id --对方的用户id
,link_type
,(SELECT 1
FROM im_dev.im_friend_link fl
WHERE fl.user_id = i_user_id
AND fl.partner_user_id = a.partner_user_id
AND fl.link_del_type IN (0, 2)
UNION ALL
SELECT 0
FROM im_dev.im_stranger_link s
WHERE s.user_id = i_user_id
AND s.stranger_id = a.partner_user_id
AND nvl(s.isdel, 0) = 0) Friend_type
,user_name
,PHOTO
,del_flag
FROM (SELECT black_user_id partner_user_id --对方的用户id
,decode(link_type, 0, NULL, link_type) link_type
,decode(link_type, 0, 1, 0) del_flag
FROM im_dev.im_blacklist a50515
WHERE a50515.user_id = i_user_id
AND last_update_time > v_last_update_time
AND last_update_time <= v_get_time) a
INNER JOIN im_user b
ON a.partner_user_id = b.user_id;
OPEN o_result1 FOR
SELECT 0 INCR_ALL --增量、全量标记 1 全量 0 增量
,v_get_time get_time --拉取时间
FROM dual;
END IF;
EXCEPTION
WHEN OTHERS THEN
im_log.sp_error('增量查询我的黑名单 异常', v_module);
RAISE;
END sp_getMyBlackListIncr;