mysql脚本subscribe
来源：SegmentFault
时间：2023-01-27 09:08:28 287浏览 收藏
小伙伴们有没有觉得学习数据库很有意思？有意思就对了！今天就给大家带来《mysql脚本subscribe》，以下内容将会涉及到MySQL，若是在学习中对其中部分知识点有疑问，或许看了本文就能帮到你！
-- Created by 10169291 on 2017/4/1, 10:03:35 --------
select 'set sql_mode=ANSI' as prompt;
set sql_mode=ANSI;
select 'change database to zxinsag' as prompt;
use zxinsag;
select 'Dropping TL_NORTH_SUBID_PHONE...' as prompt;
drop table if exists TL_NORTH_SUBID_PHONE;
select 'Dropping TL_NORTH_SUBSCRIBE...' as prompt;
drop table if exists TL_NORTH_SUBSCRIBE;
select 'Dropping TL_NORTH_SUBSCRIBE_UPDATE...' as prompt;
drop table if exists TL_NORTH_SUBSCRIBE_UPDATE;
select 'Dropping TL_SOUTH_SUBSCRIBE...' as prompt;
drop table if exists TL_SOUTH_SUBSCRIBE;
select 'Dropping TO_SOUTH_SUBSCRIBE...' as prompt;
drop table if exists TO_SOUTH_SUBSCRIBE;
select 'Dropping TP_CALLEVENT_DEF...' as prompt;
drop table if exists TP_CALLEVENT_DEF;
select 'Dropping TP_DIRECTION_DEF...' as prompt;
drop table if exists TP_DIRECTION_DEF;
select 'Dropping TP_NOTIFY_MODE_DEF...' as prompt;
drop table if exists TP_NOTIFY_MODE_DEF;
select 'Dropping TT_NORTH_SUB_COMPARE...' as prompt;
drop table if exists TT_NORTH_SUB_COMPARE;
select 'Dropping TT_SOUTH_SUB_RESULT...' as prompt;
drop table if exists TT_SOUTH_SUB_RESULT;
select 'Dropping TT_SUBSCRIBE_EVENT...' as prompt;
drop table if exists TT_SUBSCRIBE_EVENT;
select 'Dropping TT_SUBSCRIBE_PHONE...' as prompt;
drop table if exists TT_SUBSCRIBE_PHONE;
-- modify by LC 20171027 北向号码表增加各种事件和每个号码南向操作的返回结果
select 'Creating table TL_NORTH_SUBID_PHONE' as prompt;
create table TL_NORTH_SUBID_PHONE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
n_subid VARCHAR(64) not null,
phone VARCHAR(32) not null,
direct_mo INT,
direct_mt INT,
ev_begin INT,
ev_ringing INT,
ev_answer INT,
ev_busy INT,
ev_notreachable INT,
ev_routefailure INT,
ev_noanswer INT,
ev_abandon INT,
ev_release INT,
ev_exception INT,
notify_block INT,
rltcode int,
opresult int COMMENT '每个号码南向操作返回的结果 0-订阅成功 1-订阅正在同步 2-订阅失败 3-更新成功 4-正在更新 5-更新失败 6-删除成功 7-正在删除 8-删除失败' -- add by LC 20171027
)
;
create index IDX_NORTH_PHONE on TL_NORTH_SUBID_PHONE (PHONE);
create index IDX_NORTH_SUBID on TL_NORTH_SUBID_PHONE (N_SUBID);
select 'Creating table TL_NORTH_SUBSCRIBE' as prompt;
CREATE TABLE TL_NORTH_SUBSCRIBE
(
intime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
n_subid VARCHAR(64) NOT NULL COMMENT '北向订阅ID',
appid VARCHAR(32),
sepid VARCHAR(64),
platform INT,
direct_mo INT,
direct_mt INT,
ev_begin INT,
ev_ringing INT,
ev_answer INT,
ev_busy INT,
ev_notreachable INT,
ev_routefailure INT,
ev_noanswer INT,
ev_abandon INT,
ev_release INT,
ev_exception INT,
notify_block INT,
rltcode INT,
constraint pk_uag_sys_code primary key (n_subid)
)
;
select 'Creating table TL_NORTH_SUBSCRIBE_UPDATE' as prompt;
create table TL_NORTH_SUBSCRIBE_UPDATE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
dataid int not null,
n_subid VARCHAR(64) not null,
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int
)
;
select 'Creating table TL_SOUTH_SUBSCRIBE' as prompt;
create table TL_SOUTH_SUBSCRIBE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
s_subid VARCHAR(64) not null COMMENT '南向订阅ID',
netcode VARCHAR(64) not null COMMENT '南向网元CODE',
phone VARCHAR(32) not null COMMENT '用户号码',
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int COMMENT '是否BLOCK',
constraint PK_SOUTH_SUBSCRIBE primary key (S_SUBID, NETCODE),
constraint UNQ_SOUTH_PHONE unique (PHONE)
)
;
select 'Creating table TO_SOUTH_SUBSCRIBE' as prompt;
create table TO_SOUTH_SUBSCRIBE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
dataid int,
dataidx int,
optype int,
s_subid VARCHAR(64),
netcode VARCHAR(64),
phone VARCHAR(32),
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int,
iftosouth int COMMENT '是否向南向同步 0-不同步 1-同步'
)
;
select 'Creating table TP_CALLEVENT_DEF' as prompt;
create table TP_CALLEVENT_DEF
(
callevent_id int not null,
description VARCHAR(100) not null,
col_index int,
constraint PK_CALLEVENT_ID primary key (CALLEVENT_ID),
constraint UNQ_CALLEVENT_DESCRIPTION unique (DESCRIPTION)
)
;
select 'Creating table TP_DIRECTION_DEF' as prompt;
create table TP_DIRECTION_DEF
(
direction_id int not null,
description VARCHAR(100) not null,
constraint PK_DIRECTION_ID primary key (DIRECTION_ID),
constraint UNQ_DIRECTION_DESCRIPTION unique (DESCRIPTION)
)
;
select 'Creating table TP_NOTIFY_MODE_DEF' as prompt;
create table TP_NOTIFY_MODE_DEF
(
notify_mode int not null,
description VARCHAR(100) not null,
constraint PK_NOTIFY_MODE primary key (NOTIFY_MODE),
constraint UNQ_NOTIFY_DESCRIPTION unique (DESCRIPTION)
)
;
-- modify by LC 20170828 以前创建的是临时表，现改为普通表
select 'Creating table TT_NORTH_SUB_COMPARE' as prompt;
CREATE TABLE TT_NORTH_SUB_COMPARE
(
phone VARCHAR(32) not null,
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int,
optype int comment '0-new,1-update,2-delete',
dataflag int comment '1-before,2-after'
)
;
create index IDX_NORTH_SUB_COMP_PHONE on TT_NORTH_SUB_COMPARE (PHONE);
-- modify by LC 20170828 以前创建的是临时表，现改为普通表
select 'Creating table TT_SOUTH_SUB_RESULT' as prompt;
CREATE TABLE TT_SOUTH_SUB_RESULT
(
s_subid VARCHAR(64),
netcode VARCHAR(64),
phone VARCHAR(32) not null,
rltcode VARCHAR(10),
constraint UNQ_SOUTH_RESULT_PHONE unique (PHONE)
)
;
-- modify by LC 20170828 以前创建的是临时表，现改为普通表
select 'Creating table TT_SUBSCRIBE_EVENT' as prompt;
CREATE TABLE TT_SUBSCRIBE_EVENT
(
event_description VARCHAR(32) not null
)
;
-- modify by LC 20170828 以前创建的是临时表，现改为普通表
select 'Creating table TT_SUBSCRIBE_PHONE' as prompt;
CREATE TABLE TT_SUBSCRIBE_PHONE
(
phone VARCHAR(32) not null,
constraint UNQ_TT_SUBSCRIBE_PHONE unique (PHONE)
)
;
-- 序列修改为表中读取数据
-- select 'Creating sequence SEQ_DATAID' as prompt;
-- create sequence SEQ_DATAID
-- minvalue 1
-- maxvalue 999999999
-- start with 1
-- increment by 1
-- cache 10
-- cycle;
-- 创建序列表
select 'Creating table zxinsag.sequence_tab' as prompt;
drop table IF EXISTS zxinsag.sequence_tab;
CREATE TABLE zxinsag.sequence_tab (
seq_name VARCHAR(50) NOT NULL, -- 序列名称
current_val BIGINT NOT NULL, -- 当前值
increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度)
CONSTRAINT PK_SEQUENCE PRIMARY KEY (seq_name)
);
INSERT INTO zxinsag.sequence_tab (seq_name, current_val, increment_val) VALUES ('SEQ_DATAID',1,1);
COMMIT;
-- 读取当前序列值
select 'Creating FUNCTION zxinsag.currval' as prompt;
DROP FUNCTION IF EXISTS zxinsag.currval;
DELIMITER //
CREATE FUNCTION zxinsag.currval(v_seq_name VARCHAR(50))
RETURNS INTEGER
BEGIN
DECLARE VALUE INTEGER; SET VALUE = 0; SELECT current_val INTO VALUE FROM sequence_tab WHERE seq_name = v_seq_name;
RETURN VALUE;
END
//
delimiter ;
-- 获取下一步序列值
select 'Creating FUNCTION zxinsag.nextval' as prompt;
DROP FUNCTION IF EXISTS zxinsag.nextval;
DELIMITER //
CREATE FUNCTION zxinsag.nextval(v_seq_name VARCHAR(50),v_step INT)
RETURNS INTEGER
BEGIN
DECLARE VALUE INTEGER;
UPDATE sequence_tab SET current_val=current_val+(increment_val * v_step) WHERE seq_name = v_seq_name;
SET VALUE = currval(v_seq_name);
IF VALUE > 999999999 THEN
UPDATE sequence_tab SET current_val = 1 WHERE seq_name = v_seq_name;
END IF;
RETURN currval(v_seq_name); END
//
delimiter ;
select 'Creating view VW_NORTH_SUBSCRIBE' as prompt;
DROP VIEW IF EXISTS vw_north_subscribe;
CREATE VIEW vw_north_subscribe AS
SELECT
a.phone, b.n_subid, b.appid, IF((b.ev_begin = 1),'Begin',NULL) ev_begin, IF((b.ev_ringing = 1),'Ringing',NULL) ev_ringing, IF((b.ev_answer = 1),'Answer',NULL) ev_answer, IF((b.ev_busy = 1),'Busy',NULL) ev_busy, IF((b.ev_notreachable = 1),'Not Reachable',NULL) ev_notreachable, IF((b.ev_routefailure = 1),'Route Failure',NULL) ev_routefailure, IF((b.ev_noanswer = 1),'No Answer',NULL) ev_noanswer, IF((b.ev_abandon = 1),'Abandon',NULL) ev_abandon, IF((b.ev_release = 1),'Release',NULL) ev_release, IF((b.ev_exception = 1),'Exception',NULL) ev_exception, IF((b.direct_mo = 1),IF((b.direct_mt = 1),'Both','MO'),'MT') direction, IF((b.notify_block = 1),'Block','Notify') notify_block, IF(ISNULL(b.rltcode),1,IF((b.rltcode = 0),0,2)) rltcode
FROM tl_north_subid_phone a
JOIN tl_north_subscribe b
ON a.n_subid = b.n_subid;
/*select 'Creating view VW_NORTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS vw_north_subscribe_out;
create view vw_north_subscribe_out as
SELECT a.phone,
a.n_subid, b.platform, b.appid, IF((b.ev_begin = 1), 'Begin', NULL) ev_begin, IF((b.ev_ringing = 1), 'Ringing', NULL) ev_ringing, IF((b.ev_answer = 1), 'Answer', NULL) ev_answer, IF((b.ev_busy = 1), 'Busy', NULL) ev_busy, IF((b.ev_notreachable = 1), 'Not Reachable', NULL) ev_notreachable, IF((b.ev_routefailure = 1), 'Route Failure', NULL) ev_routefailure, IF((b.ev_noanswer = 1), 'No Answer', NULL) ev_noanswer, IF((b.ev_abandon = 1), 'Abandon', NULL) ev_abandon, IF((b.ev_release = 1), 'Release', NULL) ev_release, IF((b.ev_exception = 1), 'Exception', NULL) ev_exception, IF((b.direct_mo = 1), IF((b.direct_mt = 1), 'Both', 'MO'), 'MT') direction, IF((b.notify_block = 1), 'Block', 'Notify') notify_block, c.s_subid, c.netcode, e.aspid, '' notify_url
FROM tl_north_subid_phone a
JOIN tl_north_subscribe b
ON a.n_subid = b.n_subid
JOIN tl_south_subscribe c
ON c.phone = a.phone
JOIN zxsdp_op.appbasic d
ON b.appid = d.appid
JOIN zxsdp_op.aspbasic e
ON d.aspindex = e.aspindex
WHERE a.rltcode = 0;*/
-- 订阅相关视图修改 数据库 zxinsag 配合苏研修改 将 zxsdp_op.appbasic d JOIN zxsdp_op.aspbasic e on d.aspindex = e.aspindex 改为视图 zxsdp_op.uag_ucp_app
-- 20171107 modify by LC 一级平台进行订阅时对应的APPID是不存在的，要在视图 vw_north_subscribe_out 视图中展示这种记录 因此将 "JOIN zxsdp_op.uag_ucp_app d" 改为 "LEFT JOIN zxsdp_op.uag_ucp_app d" 左连接。
select 'Creating view zxinsag.VW_NORTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS zxinsag.vw_north_subscribe_out;
create view zxinsag.vw_north_subscribe_out as
SELECT a.phone,
a.n_subid, b.platform, b.appid, IF((b.ev_begin = 1), 'Begin', NULL) ev_begin, IF((b.ev_ringing = 1), 'Ringing', NULL) ev_ringing, IF((b.ev_answer = 1), 'Answer', NULL) ev_answer, IF((b.ev_busy = 1), 'Busy', NULL) ev_busy, IF((b.ev_notreachable = 1), 'Not Reachable', NULL) ev_notreachable, IF((b.ev_routefailure = 1), 'Route Failure', NULL) ev_routefailure, IF((b.ev_noanswer = 1), 'No Answer', NULL) ev_noanswer, IF((b.ev_abandon = 1), 'Abandon', NULL) ev_abandon, IF((b.ev_release = 1), 'Release', NULL) ev_release, IF((b.ev_exception = 1), 'Exception', NULL) ev_exception, IF((b.direct_mo = 1), IF((b.direct_mt = 1), 'Both', 'MO'), 'MT') direction, IF((b.notify_block = 1), 'Block', 'Notify') notify_block, c.s_subid, c.netcode, d.spid as aspid, '' notify_url
FROM zxinsag.tl_north_subid_phone a
JOIN zxinsag.tl_north_subscribe b
ON a.n_subid = b.n_subid
JOIN zxinsag.tl_south_subscribe c
ON c.phone = a.phone
LEFT JOIN zxsdp_op.uag_ucp_app d
ON b.appid = d.serviceid
WHERE a.rltcode = 0;
select 'Creating view VW_SOUTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS vw_south_subscribe_out;
create view vw_south_subscribe_out as
select dataid,
dataidx, optype, phone, s_subid, netcode, IF(ev_begin = 1, 'Begin', null) ev_begin, IF(ev_ringing = 1, 'Ringing', null) ev_ringing, IF(ev_answer = 1, 'Answer', null) ev_answer, IF(ev_busy = 1, 'Busy', null) ev_busy, IF(ev_notreachable = 1, 'Not Reachable', null) ev_notreachable, IF(ev_routefailure = 1, 'Route Failure', null) ev_routefailure, IF(ev_noanswer = 1, 'No Answer', null) ev_noanswer, IF(ev_abandon = 1, 'Abandon', null) ev_abandon, IF(ev_release = 1, 'Release', null) ev_release, IF(ev_exception = 1, 'Exception', null) ev_exception, IF(direct_mo = 1, IF(direct_mt = 1, 'Both', 'MO'), 'MT') direction, IF(notify_block = 1, 'Block', 'Notify') notify_block, iftosouth
from to_south_subscribe;
-- 20171114 added by LC 增加北向用户详细信息视图
select 'Creating view VW_NORTH_PHONE' as prompt;
DROP VIEW IF EXISTS VW_NORTH_PHONE;
create view VW_NORTH_PHONE as
select intime,
n_subid, phone, IF(ev_begin = 1, 'Begin', null) ev_begin, IF(ev_ringing = 1, 'Ringing', null) ev_ringing, IF(ev_answer = 1, 'Answer', null) ev_answer, IF(ev_busy = 1, 'Busy', null) ev_busy, IF(ev_notreachable = 1, 'Not Reachable', null) ev_notreachable, IF(ev_routefailure = 1, 'Route Failure', null) ev_routefailure, IF(ev_noanswer = 1, 'No Answer', null) ev_noanswer, IF(ev_abandon = 1, 'Abandon', null) ev_abandon, IF(ev_release = 1, 'Release', null) ev_release, IF(ev_exception = 1, 'Exception', null) ev_exception, IF(direct_mo = 1, IF(direct_mt = 1, 'Both', 'MO'), 'MT') direction, IF(notify_block = 1, 'Block', 'Notify') notify_block, opresult
from TL_NORTH_SUBID_PHONE;
select 'Creating procedure P_QUERY_SOUTH_SUBSCRIBE' as prompt;
DROP PROCEDURE IF EXISTS p_query_south_subscribe;
delimiter //
create procedure p_query_south_subscribe
(
in vi_phone VARCHAR(32), in vi_s_subid VARCHAR(64), in vi_netcode VARCHAR(64), out vo_return int, out vo_errmsg VARCHAR(200), out vo_info VARCHAR(200)
)
proc:begin
DECLARE errocode int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;
DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;
set vo_return = 0;
set vo_errmsg = ' ';
set vo_info = ' ';
if vi_phone = '' and vi_s_subid = '' then
set vo_return = 1;
set vo_errmsg = 'error: either phone or subid is required!';
leave proc;
end if;
select CONCAT_WS('|',t.phone,t.s_subid,t.netcode)
into vo_info
from tl_south_subscribe t
where (t.phone = vi_phone or vi_phone = '')
and (t.s_subid = vi_s_subid and t.netcode = vi_netcode or vi_s_subid = '');
set vo_return = 0;
IF errocode = 2 THEN
set vo_return = 2;
set vo_errmsg = 'no data found!';
else
if errocode = 1 then
set vo_return = -1;
set vo_errmsg = 'other erro';
end if;
END IF;end
//
delimiter ;
select 'Creating procedure P_SUBSCRIBE_CLEAN' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_clean;
DELIMITER //
CREATE PROCEDURE p_subscribe_clean()
proc:BEGIN
DECLARE v_expiredtime TIMESTAMP DEFAULT DATE_ADD(NOW(), INTERVAL -15 MINUTE); -- 删除已过操作有效期还未成功的北向订阅记录 DELETE FROM tl_north_subscribe WHERE rltcode IS NULL AND intime
END
//
delimiter ;
-- 删除记录定时任务
select 'Creating job P_SUBSCRIBE_CLEAN_JOB' as prompt;
SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS P_SUBSCRIBE_CLEAN_JOB;
CREATE EVENT P_SUBSCRIBE_CLEAN_JOB ON SCHEDULE
EVERY 15 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
-- ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ENABLE
DO call zxinsag.p_subscribe_clean();
select 'Creating procedure P_SUBSCRIBE_DELETE' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_delete;
DELIMITER //
create procedure p_subscribe_delete
(
in vi_n_subid VARCHAR(64), out vo_return int, out vo_errmsg VARCHAR(200), out vo_dataid int, out vo_datanum int
)
proc:begin
DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE v_count int default 0;
DECLARE v_dataid bigint default 0;
-- added by LC 20171011 增加数据处理条数临时变量用于累计需要南向同步和不需要两种记录数
DECLARE v_datanum_tmp int default 0;
DECLARE errocode int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;
DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;
set vo_return = 0;
set vo_errmsg = ' ';
set vo_datanum = 0;
/*
* 北向退订请求
*/
-- 清理临时表 added by LC 20170828
TRUNCATE TABLE TT_NORTH_SUB_COMPARE;
truncate table tt_subscribe_phone;
select count(1)
into v_count
from tl_north_subscribe t
where -- t.rltcode = 0 and 删除订阅事件时，可以删除状态不正常的事件 20170922 modify by LC
t.n_subid = vi_n_subid;
if v_count = 0
then
set vo_return = 1;
set vo_errmsg = 'error: this subId is not exist!';
leave proc;
end if;
-- 获取这个 n_subid 的订阅号码
insert into tt_subscribe_phone
(phone)
select t.phone
from tl_north_subid_phone t
where t.rltcode = 0
and t.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 2;
set vo_errmsg = 'insert tt_subscribe_phone error.';
leave proc;
end if;
-- 计算本次退订前的并集
insert into tt_north_sub_compare
(phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable,
ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype,
dataflag)
select c.phone,
max(a.direct_mo),
max(a.direct_mt),
max(a.ev_begin),
max(a.ev_ringing),
max(a.ev_answer),
max(a.ev_busy),
max(a.ev_notreachable),
max(a.ev_routefailure),
max(a.ev_noanswer),
max(a.ev_abandon),
max(a.ev_release),
max(a.ev_exception),
max(a.notify_block),
1 optype,
1 dataflag
from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c
where a.n_subid = b.n_subid
and b.phone = c.phone
and a.rltcode = 0
and b.rltcode = 0
group by c.phone;
if errocode = 1 then
set vo_return = 3;
set vo_errmsg = 'insert tt_north_sub_compare error.';
leave proc;
end if;
-- 计算本次退订后的并集
-- 2017109 modify by LC 要将进入临时表的记录的各种事件初始化为0，否则所有事件字段值为null 在进行left join 时null = null 筛选不出数据
insert into tt_north_sub_compare
(phone, optype, dataflag , direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable,
ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block)
select distinct t.phone, 2 optype, 2 dataflag, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0
from (select b.phone, c.phone phone_del
from tt_subscribe_phone b
left join tl_north_subid_phone c
on b.phone = c.phone
and c.n_subid vi_n_subid
and c.rltcode = 0) t
where t.phone_del is null;
if errocode = 1 then
set vo_return = 4;
set vo_errmsg = 'insert tt_north_sub_compare error.';
leave proc;
end if;
insert into tt_north_sub_compare
(phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable,
ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype,
dataflag)
select t.phone,
max(t.direct_mo),
max(t.direct_mt),
max(t.ev_begin),
max(t.ev_ringing),
max(t.ev_answer),
max(t.ev_busy),
max(t.ev_notreachable),
max(t.ev_routefailure),
max(t.ev_noanswer),
max(t.ev_abandon),
max(t.ev_release),
max(t.ev_exception),
max(t.notify_block),
1 optype,
2 dataflag
from (select b.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c
where a.n_subid = b.n_subid
and b.phone = c.phone
and b.rltcode = 0
and a.n_subid vi_n_subid
and a.rltcode = 0) t
group by t.phone;
if errocode = 1 then
set vo_return = 5;
set vo_errmsg = 'insert tt_north_sub_compare error.';
leave proc;
end if;
select zxinsag.nextval('SEQ_DATAID',1) into v_dataid;
-- v_dataid := seq_dataid.nextval();
-- 插入需要南向操作的记录
insert into to_south_subscribe
(intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin,
ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon,
ev_release, ev_exception, notify_block, iftosouth)
select v_sysdate,
v_dataid,
@rownum:=@rownum+1 AS rownum,
t.optype,
s.s_subid,
s.netcode,
t.phone,
t.direct_mo,
t.direct_mt,
t.ev_begin,
t.ev_ringing,
t.ev_answer,
t.ev_busy,
t.ev_notreachable,
t.ev_routefailure,
t.ev_noanswer,
t.ev_abandon,
t.ev_release,
t.ev_exception,
t.notify_block,
1
from (SELECT @rownum:=0) r,
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
left join
(select * from tt_north_sub_compare where dataflag = 1) b
on (a.phone = b.phone AND a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_answer = b.ev_answer AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
where b.phone is null
) t
left join tl_south_subscribe s
on t.phone = s.phone;
if errocode = 1 then
set vo_return = 6;
set vo_errmsg = 'insert to_south_subscribe error.';
leave proc;
end if;
select ROW_COUNT() into v_datanum_tmp;
set vo_datanum = vo_datanum + v_datanum_tmp;
if vo_datanum = 0
then
-- 不需要去南向交互
delete from tl_north_subid_phone where n_subid = vi_n_subid;
delete from tl_north_subscribe where n_subid = vi_n_subid;
else
-- 20171109 added by LC 新增删除操作如果有部分需要南向同步，部分不需要的情况则直接删除不需要去南向同步的号码记录，需要向南向同步的保留，等待响应消息上来进行处理。
DELETE FROM tl_north_subid_phone
USING tl_north_subid_phone
JOIN
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
join
(select * from tt_north_sub_compare where dataflag = 1) b
on (a.phone = b.phone AND
a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_answer = b.ev_answer AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
) t
ON tl_north_subid_phone.phone = t.phone;
end if;
-- 20171107 added by LC 删除操作时每个号码状态设置为"删除中" 等待南向删除的返回结果
update tl_north_subid_phone a
set a.opresult = 7
where a.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 7;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
-- 插入不需要南向操作的记录 取交集 added by LC 20171010
insert into to_south_subscribe
(intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin,
ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon,
ev_release, ev_exception, notify_block, iftosouth)
select v_sysdate,
v_dataid,
@rownum:=@rownum+1 AS rownum,
t.optype,
s.s_subid,
s.netcode,
t.phone,
t.direct_mo,
t.direct_mt,
t.ev_begin,
t.ev_ringing,
t.ev_answer,
t.ev_busy,
t.ev_notreachable,
t.ev_routefailure,
t.ev_noanswer,
t.ev_abandon,
t.ev_release,
t.ev_exception,
t.notify_block,
0
from (SELECT @rownum:=0) r,
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
join
(select * from tt_north_sub_compare where dataflag = 1) b
on (a.phone = b.phone AND a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_answer = b.ev_answer AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
) t
left join tl_south_subscribe s
on t.phone = s.phone;
if errocode = 1 then
set vo_return = 8;
set vo_errmsg = 'insert to_south_subscribe error.';
leave proc;
end if;
select ROW_COUNT() into v_datanum_tmp;
set vo_datanum = vo_datanum + v_datanum_tmp;
-- 清理临时表 added by LC 20170828
TRUNCATE TABLE TT_NORTH_SUB_COMPARE;
truncate table tt_subscribe_phone;
commit;
set vo_return = 0;
set vo_dataid = v_dataid;
if errocode = 1 then
set vo_return = -1;
set vo_errmsg = 'other erro';
end if;end
//
delimiter ;
select 'Creating procedure p_subscribe_req' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_req;
DELIMITER //
create procedure p_subscribe_req
(
in vi_n_subid VARCHAR(64), in vi_appid VARCHAR(32), in vi_sepid VARCHAR(64), in vi_platform int, in vi_phones VARCHAR(2048), in vi_events VARCHAR(1024), in vi_direction VARCHAR(16), in vi_notify VARCHAR(32), out vo_return int, out vo_errmsg VARCHAR(200), out vo_dataid int, out vo_datanum int
)
proc:begin
/*
* 北向订阅请求
*/
DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE v_dataid bigint default 0;
DECLARE v_count int default 0;
DECLARE v_direct_mo int default 0;
DECLARE v_direct_mt int default 0;
DECLARE v_ev_begin int default 0;
DECLARE v_ev_ringing int default 0;
DECLARE v_ev_answer int default 0;
DECLARE v_ev_busy int default 0;
DECLARE v_ev_notreachable int default 0;
DECLARE v_ev_routefailure int default 0;
DECLARE v_ev_noanswer int default 0;
DECLARE v_ev_abandon int default 0;
DECLARE v_ev_release int default 0;
DECLARE v_ev_exception int default 0;
DECLARE v_notify_block int default 0;
DECLARE v_locate int default 1;
DECLARE v_all_char_langth int default 0;
DECLARE v_shot_langht int default 0;
DECLARE v_temp_phone VARCHAR(32) default ' ';
DECLARE v_temp_event VARCHAR(32) default ' ';
-- added by LC 20171011 增加数据处理条数临时变量用于累计需要南向同步和不需要两种记录数
DECLARE v_datanum_tmp int default 0;
DECLARE errocode int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;
DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;
set vo_return = 0;
set vo_errmsg = ' ';
set vo_dataid = 0;
set vo_datanum = 0;
-- 清理临时表 added by LC 20170828
TRUNCATE TABLE TT_NORTH_SUB_COMPARE;
truncate table tt_subscribe_phone;
truncate table TT_SUBSCRIBE_EVENT;
select count(1)
into v_count
from tl_north_subscribe t
where t.n_subid = vi_n_subid;
if v_count > 0
then
set vo_return = 1;
set vo_errmsg = 'error: this subId has been used!';
leave proc;
end if;
-- 分割号码
/*insert into tt_subscribe_phone
(phone)
select distinct trim(regexp_substr(vi_phones, '[^;]+', 1, rownum))
from dual
connect by rownum 0
then
set vo_return = 3;
set vo_errmsg = 'error: there are some users has been subscribed before!';
leave proc;
end if;
-- 分割事件
/*insert into tt_subscribe_event
(event_description)
select distinct trim(regexp_substr(vi_events, '[^;]+', 1, rownum))
from dual
connect by rownum end
//
delimiter ;
select 'Creating procedure p_subscribe_res' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_res;
DELIMITER //
create procedure p_subscribe_res
(
in vi_dataid int, in vi_n_subid VARCHAR(64), in vi_optype int, in vi_phone VARCHAR(2048), in vi_result VARCHAR(1000), in vi_s_subid VARCHAR(4000), in vi_netcode VARCHAR(4000), out vo_return int, out vo_errmsg VARCHAR(200)
)
proc:begin
/*
* 南向订阅交互结果返回
*/
DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE v_success_num int default 0;
DECLARE v_all_add_success_num int default 0; -- 20171101 added by LC 订阅操作不需要南向同步的号码+南向同步成功的号码数量
DECLARE v_all_update_success_num int default 0; -- 20171101 added by LC 更新操作不需要南向同步的号码+南向同步成功的号码数量
DECLARE v_failed_num int default 0;
DECLARE v_count int default 0;
DECLARE v_result VARCHAR(1000) default ' ';
DECLARE v_s_subid VARCHAR(4000) default ' '; -- 兼容全部为空的情况
DECLARE v_netcode VARCHAR(4000) default ' '; -- 兼容全部为空的情况
-- 拆分用变量
DECLARE v_locate INT DEFAULT 1;
DECLARE v_all_char_langth INT DEFAULT 0;
DECLARE v_shot_langht INT DEFAULT 0;
DECLARE v_shot_langht_phone INT DEFAULT 0;
DECLARE v_shot_langht_subid INT DEFAULT 0;
DECLARE v_shot_langht_netcode INT DEFAULT 0;
DECLARE v_shot_langht_rltcode INT DEFAULT 0;
DECLARE v_temp_phone VARCHAR(32) DEFAULT ' ';
DECLARE v_temp_subid VARCHAR(64) DEFAULT ' ';
DECLARE v_temp_netcode VARCHAR(64) DEFAULT ' ';
DECLARE v_temp_rltcode VARCHAR(10) DEFAULT ' ';
DECLARE errocode int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;
DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;
select concat(vi_result,' ') into v_result; -- 兼容全部为空的情况
select concat(vi_s_subid,' ') into v_s_subid; -- 兼容全部为空的情况
select concat(vi_netcode,' ') into v_netcode; -- 兼容全部为空的情况
set vo_return = 0;
set vo_errmsg = ' ';
select count(1)
into v_count
from to_south_subscribe t
where t.dataid = vi_dataid;
if v_count = 0
then
set vo_return = 1;
set vo_errmsg = 'error: dataid is not exist!';
leave proc;
end if;
-- 清理临时表
truncate table tt_south_sub_result;
-- 分割关联南向请求结果
/*insert into tt_south_sub_result
(s_subid, netcode, phone, rltcode)
select c.s_subid, d.netcode, a.phone, b.rltcode
from (select rownum seq, trim(regexp_substr(vi_phone, '[^;]+', 1, rownum)) phone
from dual
connect by rownum 0 then
set v_shot_langht = 1;
end if;
end if;
WHILE v_locate '0');*/
-- 20171027 modify by LC 部分成功 北向记录结果成功，录入本次事件(现有流程不变)；成功号码结果成功，录入本次事件；失败号码结果失败，将号码删除只保留成功的号码
delete from tl_north_subid_phone where rltcode is null and n_subid = vi_n_subid and phone in
(select phone from tt_south_sub_result where rltcode '0');
if errocode = 1 then
set vo_return = 8;
set vo_errmsg = 'delete tl_north_subid_phone error.';
leave proc;
end if;
update tl_north_subscribe a
set a.rltcode = 0
where a.rltcode is null
and a.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 9;
set vo_errmsg = 'update tl_north_subscribe error.';
leave proc;
end if;
end if;
else
if vi_optype = 1
then
-- 20171101 added by LC 计算更新南向同步成功+不需要南向同步默认成功的数量
select count(1) into v_all_update_success_num from tl_north_subid_phone a
where a.n_subid = vi_n_subid AND a.rltcode = 0 AND a.opresult = 3;
set v_all_update_success_num = v_success_num + v_all_update_success_num;
-- 更新操作
if v_all_update_success_num = 0
then
-- 20171102 modify by LC 没有任何更新成功的记录，将所有的号码状态改为"更新失败"，北向事件记录、用户事件记录不变
update tl_north_subid_phone a
set a.opresult = 5
where a.n_subid = vi_n_subid and a.opresult = 4;
if errocode = 1 then
set vo_return = 10;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
else
-- 只要有成功记录更新北向订阅数据
UPDATE tl_north_subscribe d,tl_north_subscribe_update b
SET d.direct_mo = b.direct_mo,
d.direct_mt = b.direct_mt,
d.ev_begin = b.ev_begin,
d.ev_ringing = b.ev_ringing,
d.ev_answer = b.ev_answer,
d.ev_busy = b.ev_busy,
d.ev_notreachable = b.ev_notreachable,
d.ev_routefailure = b.ev_routefailure,
d.ev_noanswer = b.ev_noanswer,
d.ev_abandon = b.ev_abandon,
d.ev_release = b.ev_release,
d.ev_exception = b.ev_exception,
d.notify_block = b.notify_block
WHERE d.n_subid = b.n_subid AND b.dataid = vi_dataid AND b.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 11;
set vo_errmsg = 'update tl_north_subscribe error.';
leave proc;
end if;
-- 20171102 modify by LC 将南向同步成功的号码状态改为"更新成功"，用户事件记录跟新为最新
update tl_north_subid_phone d,tl_north_subscribe_update b
set d.opresult = 3,
d.direct_mo = b.direct_mo,
d.direct_mt = b.direct_mt,
d.ev_begin = b.ev_begin,
d.ev_ringing = b.ev_ringing,
d.ev_answer = b.ev_answer,
d.ev_busy = b.ev_busy,
d.ev_notreachable = b.ev_notreachable,
d.ev_routefailure = b.ev_routefailure,
d.ev_noanswer = b.ev_noanswer,
d.ev_abandon = b.ev_abandon,
d.ev_release = b.ev_release,
d.ev_exception = b.ev_exception,
d.notify_block = b.notify_block
where d.n_subid = vi_n_subid
and d.opresult = 4
and d.n_subid = b.n_subid
and b.dataid = vi_dataid
and d.phone in (select c.phone from tt_south_sub_result c where c.rltcode = '0');
if errocode = 1 then
set vo_return = 12;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
-- 20171102 modify by LC 将南向同步失败的号码状态改为"更新失败"，用户事件记录不更新
update tl_north_subid_phone d,tl_north_subscribe_update b
set d.opresult = 5
where d.n_subid = vi_n_subid
and d.opresult = 4
and d.n_subid = b.n_subid
and b.dataid = vi_dataid
and d.phone in (select c.phone from tt_south_sub_result c where c.rltcode '0');
if errocode = 1 then
set vo_return = 13;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
delete from tl_north_subscribe_update
where dataid = vi_dataid
and n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 14;
set vo_errmsg = 'delete tl_north_subscribe_update error.';
leave proc;
end if;
end if;
else
if vi_optype = 2 and v_failed_num = 0
then
-- 退订操作，全部成功删除北向订阅数据
delete from tl_north_subid_phone where n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 15;
set vo_errmsg = 'delete tl_north_subid_phone error.';
leave proc;
end if;
delete from tl_north_subscribe where n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 16;
set vo_errmsg = 'delete tl_north_subscribe error.';
leave proc;
end if;
else
-- 20171109 added by LC 退订操作部分号码成功
-- 删除南向退订成功的记录
delete from tl_north_subid_phone
where rltcode = '0'
and opresult = 7
and n_subid = vi_n_subid
and phone in (select phone from tt_south_sub_result where rltcode = '0');
if errocode = 1 then
set vo_return = 17;
set vo_errmsg = 'delete tl_north_subid_phone error.';
leave proc;
end if;
-- 删除失败的更新为删除失败
update tl_north_subid_phone
set opresult = 8
where rltcode = '0'
and opresult = 7
and n_subid = vi_n_subid
and phone in (select phone from tt_south_sub_result where rltcode '0');
if errocode = 1 then
set vo_return = 18;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
end if;
end if;
end if;
-- 删除本次导出的南向交互数据
delete from to_south_subscribe where dataid = vi_dataid;
if errocode = 1 then
set vo_return = 19;
set vo_errmsg = 'delete to_south_subscribe error.';
leave proc;
end if;
commit;
set vo_return = 0;
-- 清理临时表
truncate table tt_south_sub_result;
if errocode = 1 then
set vo_return = -1;
set vo_errmsg = 'other erro';
end if;end
//
delimiter ;
select 'Creating procedure p_subscribe_update' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_update;
DELIMITER //
create procedure p_subscribe_update
(
in vi_n_subid VARCHAR(64), in vi_events VARCHAR(1024), in vi_direction VARCHAR(16), in vi_notify VARCHAR(32), out vo_return int, out vo_errmsg VARCHAR(200), out vo_dataid int, out vo_datanum int
)
proc:begin
/*
* 北向更新请求
*/
DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE v_count int default 0;
DECLARE v_dataid bigint default 0;
DECLARE v_direct_mo int default 0;
DECLARE v_direct_mt int default 0;
DECLARE v_ev_begin int default 0;
DECLARE v_ev_ringing int default 0;
DECLARE v_ev_answer int default 0;
DECLARE v_ev_busy int default 0;
DECLARE v_ev_notreachable int default 0;
DECLARE v_ev_routefailure int default 0;
DECLARE v_ev_noanswer int default 0;
DECLARE v_ev_abandon int default 0;
DECLARE v_ev_release int default 0;
DECLARE v_ev_exception int default 0;
DECLARE v_notify_block int default 0;
DECLARE v_locate INT DEFAULT 1;
DECLARE v_all_char_langth INT DEFAULT 0;
DECLARE v_shot_langht INT DEFAULT 0;
DECLARE v_temp_phone VARCHAR(32) DEFAULT ' ';
DECLARE v_temp_event VARCHAR(32) DEFAULT ' ';
-- added by LC 20171011 增加数据处理条数临时变量用于累计需要南向同步和不需要两种记录数
DECLARE v_datanum_tmp int default 0;
DECLARE errocode int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;
DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;
set vo_return = 0;
set vo_errmsg = ' ';
set vo_datanum = 0;
set vo_dataid = 0;
select count(1)
into v_count
from tl_north_subscribe t
where t.rltcode = 0
and t.n_subid = vi_n_subid;
if v_count = 0
then
set vo_return = 1;
set vo_errmsg = 'error: this subId is not exist!';
leave proc;
end if;
-- added by LC 20170829 清除临时表
truncate table tt_north_sub_compare;
truncate table tt_subscribe_phone;
truncate table TT_SUBSCRIBE_EVENT;
-- 获取这个 n_subid 的订阅号码
insert into tt_subscribe_phone
(phone)
select t.phone
from tl_north_subid_phone t
where t.rltcode = 0
and t.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 2;
set vo_errmsg = 'insert tt_subscribe_phone error.';
leave proc;
end if;
if vi_events = ''
then
select t.ev_begin,
t.ev_ringing,
t.ev_answer,
t.ev_busy,
t.ev_notreachable,
t.ev_routefailure,
t.ev_noanswer,
t.ev_abandon,
t.ev_release,
t.ev_exception
into v_ev_begin,
v_ev_ringing,
v_ev_answer,
v_ev_busy,
v_ev_notreachable,
v_ev_routefailure,
v_ev_noanswer,
v_ev_abandon,
v_ev_release,
v_ev_exception
from tl_north_subscribe t
where t.n_subid = vi_n_subid
and t.rltcode = 0;
else
-- 分割事件
/*insert into tt_subscribe_event
(event_description)
select distinct trim(regexp_substr(vi_events, '[^;]+', 1, rownum))
from dual
connect by rownum vi_n_subid
and a.rltcode = 0
and b.rltcode = 0
union all
select d.phone,
v_direct_mo direct_mo,
v_direct_mt direct_mt,
v_ev_begin ev_begin,
v_ev_ringing ev_ringing,
v_ev_answer ev_answer,
v_ev_busy ev_busy,
v_ev_notreachable ev_notreachable,
v_ev_routefailure ev_routefailure,
v_ev_noanswer ev_noanswer,
v_ev_abandon ev_abandon,
v_ev_release ev_release,
v_ev_exception ev_exception,
v_notify_block notify_block
from tt_subscribe_phone d) t
group by t.phone;
if errocode = 1 then
set vo_return = 5;
set vo_errmsg = 'insert tt_north_sub_compare error.';
leave proc;
end if;
select zxinsag.nextval('SEQ_DATAID',1) into v_dataid;
-- v_dataid := seq_dataid.nextval();
-- 插入需要南向操作的记录
insert into to_south_subscribe
(intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin,
ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon,
ev_release, ev_exception, notify_block, iftosouth)
select v_sysdate,
v_dataid,
@rownum := @rownum + 1 AS rownum,
t.optype,
s.s_subid,
s.netcode,
t.phone,
t.direct_mo,
t.direct_mt,
t.ev_begin,
t.ev_ringing,
t.ev_answer,
t.ev_busy,
t.ev_notreachable,
t.ev_routefailure,
t.ev_noanswer,
t.ev_abandon,
t.ev_release,
t.ev_exception,
t.notify_block,
1
from (SELECT @rownum:=0) r,
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
LEFT JOIN
(select * from tt_north_sub_compare where dataflag = 1) b
ON (a.phone = b.phone AND
a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_answer = b.ev_answer AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
WHERE b.phone IS NULL
) t
left join tl_south_subscribe s
on t.phone = s.phone;
if errocode = 1 then
set vo_return = 6;
set vo_errmsg = 'insert to_south_subscribe error.';
leave proc;
end if;
select ROW_COUNT() into v_datanum_tmp;
set vo_datanum = vo_datanum + v_datanum_tmp;
-- 20171102 added by LC 更新操作时每个号码状态设置为"更新中" 等待南向更新的返回结果
update tl_north_subid_phone a
set a.opresult = 4
where a.n_subid = vi_n_subid;
if vo_datanum = 0
then
-- 不需要去南向申请
update tl_north_subscribe t
set t.direct_mo = v_direct_mo,
t.direct_mt = v_direct_mt,
t.ev_begin = v_ev_begin,
t.ev_ringing = v_ev_ringing,
t.ev_answer = v_ev_answer,
t.ev_busy = v_ev_busy,
t.ev_notreachable = v_ev_notreachable,
t.ev_routefailure = v_ev_routefailure,
t.ev_noanswer = v_ev_noanswer,
t.ev_abandon = v_ev_abandon,
t.ev_release = v_ev_release,
t.ev_exception = v_ev_exception,
t.notify_block = v_notify_block
where t.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 7;
set vo_errmsg = 'update tl_north_subscribe error.';
leave proc;
end if;
-- 20171031 added by LC 北向号码表增加事件信息,更新操作时需要一同更新
update tl_north_subid_phone t
set t.opresult = 3,
t.direct_mo = v_direct_mo,
t.direct_mt = v_direct_mt,
t.ev_begin = v_ev_begin,
t.ev_ringing = v_ev_ringing,
t.ev_answer = v_ev_answer,
t.ev_busy = v_ev_busy,
t.ev_notreachable = v_ev_notreachable,
t.ev_routefailure = v_ev_routefailure,
t.ev_noanswer = v_ev_noanswer,
t.ev_abandon = v_ev_abandon,
t.ev_release = v_ev_release,
t.ev_exception = v_ev_exception,
t.notify_block = v_notify_block
where t.n_subid = vi_n_subid;
if errocode = 1 then
set vo_return = 8;
set vo_errmsg = 'update tl_north_subscribe error.';
leave proc;
end if;
else
-- 保存更新记录
insert into tl_north_subscribe_update
(intime, dataid, n_subid, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer,
ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release,
ev_exception, notify_block)
values
(v_sysdate, v_dataid, vi_n_subid, v_direct_mo, v_direct_mt, v_ev_begin, v_ev_ringing,
v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer,
v_ev_abandon, v_ev_release, v_ev_exception, v_notify_block);
if errocode = 1 then
set vo_return = 9;
set vo_errmsg = 'insert tl_north_subscribe_update error.';
leave proc;
end if;
-- 20171031 added by LC 北向号码表增加事件信息,更新操作时需要一同更新
update tl_north_subid_phone t,
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
join
(select * from tt_north_sub_compare where dataflag = 1) b
on (a.phone = b.phone AND a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_answer = b.ev_answer AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
) a
set t.opresult = 3,
t.direct_mo = v_direct_mo,
t.direct_mt = v_direct_mt,
t.ev_begin = v_ev_begin,
t.ev_ringing = v_ev_ringing,
t.ev_answer = v_ev_answer,
t.ev_busy = v_ev_busy,
t.ev_notreachable = v_ev_notreachable,
t.ev_routefailure = v_ev_routefailure,
t.ev_noanswer = v_ev_noanswer,
t.ev_abandon = v_ev_abandon,
t.ev_release = v_ev_release,
t.ev_exception = v_ev_exception,
t.notify_block = v_notify_block
where t.n_subid = vi_n_subid AND t.phone = a.phone;
if errocode = 1 then
set vo_return = 10;
set vo_errmsg = 'update tl_north_subid_phone error.';
leave proc;
end if;
end if;
-- 插入不需要南向操作的记录 取交集 added by LC 20171010
insert into to_south_subscribe
(intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin,
ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon,
ev_release, ev_exception, notify_block, iftosouth)
select v_sysdate,
v_dataid,
@rownum := @rownum + 1 AS rownum,
t.optype,
s.s_subid,
s.netcode,
t.phone,
t.direct_mo,
t.direct_mt,
t.ev_begin,
t.ev_ringing,
t.ev_answer,
t.ev_busy,
t.ev_notreachable,
t.ev_routefailure,
t.ev_noanswer,
t.ev_abandon,
t.ev_release,
t.ev_exception,
t.notify_block,
0
from (SELECT @rownum:=0) r,
(select DISTINCT
a.optype,
a.phone,
a.direct_mo,
a.direct_mt,
a.ev_begin,
a.ev_ringing,
a.ev_answer,
a.ev_busy,
a.ev_notreachable,
a.ev_routefailure,
a.ev_noanswer,
a.ev_abandon,
a.ev_release,
a.ev_exception,
a.notify_block
from
(select * from tt_north_sub_compare where dataflag = 2) a
JOIN
(select * from tt_north_sub_compare where dataflag = 1) b
ON (a.phone = b.phone AND
a.optype = b.optype AND
a.direct_mo = b.direct_mo AND
a.direct_mt = b.direct_mt AND
a.ev_begin = b.ev_begin AND
a.ev_ringing = b.ev_ringing AND
a.ev_busy = b.ev_busy AND
a.ev_notreachable = b.ev_notreachable AND
a.ev_answer = b.ev_answer AND
a.ev_routefailure = b.ev_routefailure AND
a.ev_noanswer = b.ev_noanswer AND
a.ev_abandon = b.ev_abandon AND
a.ev_release = b.ev_release AND
a.ev_exception = b.ev_exception AND
a.notify_block = b.notify_block)
) t
left join tl_south_subscribe s
on t.phone = s.phone;
if errocode = 1 then
set vo_return = 11;
set vo_errmsg = 'insert to_south_subscribe error.';
leave proc;
end if;
select ROW_COUNT() into v_datanum_tmp;
set vo_datanum = vo_datanum + v_datanum_tmp;
commit;
set vo_return = 0;
set vo_dataid = v_dataid;
-- added by LC 20170829 清除临时表
truncate table tt_north_sub_compare;
truncate table tt_subscribe_phone;
truncate table TT_SUBSCRIBE_EVENT;
if errocode = 1 then
set vo_return = -1;
set vo_errmsg = 'other erro';
end if;end
//
delimiter ;
select '1 records loaded' prompt;
select 'Loading TP_CALLEVENT_DEF...' prompt;
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (1, 'Begin', 1);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (2, 'Ringing', 2);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (3, 'Answer', 3);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (4, 'Busy', 4);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (5, 'Not Reachable', 5);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (6, 'Route Failure', 6);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (7, 'No Answer', 7);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (8, 'Abandon', 8);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (9, 'Release', 9);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (10, 'Exception', 10);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (11, 'Play Result', null);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (12, 'Collect Result', null);
commit;
select '12 records loaded' prompt;
select 'Loading TP_DIRECTION_DEF...' prompt;
insert into TP_DIRECTION_DEF (direction_id, description)
values (2, 'Both');
insert into TP_DIRECTION_DEF (direction_id, description)
values (0, 'MO');
insert into TP_DIRECTION_DEF (direction_id, description)
values (1, 'MT');
commit;
select '3 records loaded' prompt;
select 'Loading TP_NOTIFY_MODE_DEF...' prompt;
insert into TP_NOTIFY_MODE_DEF (notify_mode, description)
values (1, 'Block');
insert into TP_NOTIFY_MODE_DEF (notify_mode, description)
values (0, 'Notify');
commit;
select '2 records loaded' prompt;
到这里，我们也就讲完了《mysql脚本subscribe》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
-
339 收藏
-
285 收藏
-
259 收藏
-
374 收藏
-
475 收藏
-
483 收藏
-
462 收藏
-
469 收藏
-
289 收藏
-
239 收藏
-
315 收藏
-
361 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
- 立即学习 508次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
- 立即学习 484次学习
-
- 难过的玉米
- 这篇技术贴出现的刚刚好，太详细了，很好，mark，关注up主了！希望up主能多写数据库相关的文章。
- 2023-03-17 00:48:22
-
- 调皮的薯片
- 这篇技术文章真及时，很详细，很好，已收藏，关注up主了！希望up主能多写数据库相关的文章。
- 2023-02-24 15:23:13
-
- 风中的苗条
- 很棒，一直没懂这个问题，但其实工作中常常有遇到...不过今天到这，看完之后很有帮助，总算是懂了，感谢up主分享文章内容！
- 2023-01-31 08:33:52