编写sql时要注意格式要求：
（1）开头要写BEGIN,结尾处写END
（2）每一个sql结束处需要打上分好，“；”，区分每一条sql语句，漏写时，事件无法保存
（3）事件示例
BEGIN
-- 推送当天门票预测数据
DELETe FROM ylxs_dp.ylxs_jqpwfx_mpydqktj WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10));
-- 添加今日数据
INSERT INTO ylxs_jqpwfx_mpydqktj (jr,jqmc,sj,jdmc,jrwj,jrjp,mr)
SELECt
count(0) as jr,
'玉龙雪山' as jqmc,
orderTime as sj,
scenicAreaName as jdmc,
0 as jrwj,
0 as jrjp,
0 as mr
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTr(orderTime,1,10) = (SUBSTr(NOW(),1,10))
GROUP BY scenicAreaName;
INSERT INTO ylxs_jqpwfx_mpydqktj (jr,jqmc,sj,jdmc,jrwj,jrjp)
SELECt
count(0) as jr,
'玉龙雪山' as jqmc,
orderTime as sj,
'玉龙雪山景区门票' as jdmc,
0 as jrwj,
0 as jrjp
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTr(orderTime,1,10) = (SUBSTr(NOW(),1,10));
-- 添加昨日数据
UPDATe
ylxs_jqpwfx_mpydqktj
SET
zr =
(SELECT
IFNULL(count(0),0) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName = '冰川公园索道'
and SUBSTr(orderTime,1,10) = date_sub(SUBSTr(NOW(),1,10),interval 1 day)
)
WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10)) and jdmc = '冰川公园索道';
UPDATe
ylxs_jqpwfx_mpydqktj
SET
zr =
(SELECT
IFNULL(count(0),0) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName = '云杉坪索道'
and SUBSTr(orderTime,1,10) = date_sub(SUBSTr(NOW(),1,10),interval 1 day)
)
WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10)) and jdmc = '云杉坪索道';
UPDATe
ylxs_jqpwfx_mpydqktj
SET
zr =
(SELECT
IFNULL(count(0),0) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName = '印象丽江'
and SUBSTr(orderTime,1,10) = date_sub(SUBSTr(NOW(),1,10),interval 1 day)
)
WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10)) and jdmc = '印象丽江';
UPDATe
ylxs_jqpwfx_mpydqktj
SET
zr =
(SELECT
IFNULL(count(0),0) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName = '牦牛坪索道'
and SUBSTr(orderTime,1,10) = date_sub(SUBSTr(NOW(),1,10),interval 1 day)
)
WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10)) and jdmc = '牦牛坪索道';
UPDATe
ylxs_jqpwfx_mpydqktj
SET
zr =
( SELECT
count(0) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
and SUBSTr(orderTime,1,10) = date_sub(SUBSTr(NOW(),1,10),interval 1 day)
)
WHERe SUBSTr(sj,1,10) = (SUBSTr(NOW(),1,10)) and jdmc = '玉龙雪山景区门票';
-- 添加玉龙雪山明日预测数据
UPDATe
ylxs_jqpwfx_mpydqktj
SET
mr =
( SELECT
tomorrow_max_value as sl
FROM
hl_data_predict.kl_predict_data
WHERe
SUBSTr(create_date,1,10) = SUBSTr(NOW(),1,10) ORDER BY SUBSTr(create_date,1,19) desc LIMIT 0,1
)
WHERe SUBSTr(sj,1,10) = SUBSTr(NOW(),1,10) and jdmc = '玉龙雪山景区门票';
-- 添加今日检票数
UPDATe ylxs_jqpwfx_mpydqktj
SET jrjp = (
SELECT
SUM(jps) as jrjp
FROM
ylxs_dp.ylxs_jqpwfx_jdjpsdqs
WHERe
SUBSTr(sj,1,10) = SUBSTr(NOW(),1,10)
and jdmc = '玉龙雪山'
and jrzr = '1'
)
WHERe
SUBSTr( sj, 1, 10 ) = SUBSTr( NOW(), 1, 10 )
AND jdmc = '玉龙雪山景区门票';
-- 添加今日未检票数
UPDATe ylxs_jqpwfx_mpydqktj
SET jrwj = ABS((
SELECT
COUNT(distinct touristCardNum) as sl
FROM
ylxs_hldc.t_dm_ticket_data
WHERe
SUBSTr(travelTime,1,10) = SUBSTr(NOW(),1,10)
and scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
)-( SELECt
SUM(jps) as jrjp
FROM
ylxs_dp.ylxs_jqpwfx_jdjpsdqs
WHERe
SUBSTr(sj,1,10) = SUBSTr(NOW(),1,10)
and jdmc = '玉龙雪山'
and jrzr = '1'
))
WHERe
SUBSTr( sj, 1, 10 ) = SUBSTr( NOW(), 1, 10 )
AND jdmc = '玉龙雪山景区门票';
END
（4）在计划中设置事件执行规律
注意：一定要写事件开始时间，且要精确到秒钟，否则无法保存该事件
4、适用场景1、对于某些需要实时更新的数据，我们可以针对业务需求，单独建立一张结果表，编写事件定时更新，而不需要在接口请求中，每次去获取原表的最新数据。
2、对于数据量特别大的表，哪怕加了索引，优化了sql，执行查询时间也严重偏长，我们可以以不同的时间维度分组，或者根据业务需求分租，定时推送到结果表中，同时，还可以设置数据库的读、取时间，来防止事件执行时间过长，数据库连接宕机
oracle.net.READ_TIMEOUT=30000 oracle.jdbc.ReadTimeout=300005、通过列表查看时间最近一次执行时间 6、查看事件是否开启
SHOW VARIABLES LIKE 'event_scheduler'7、设置当前事件开启
SET GLOBAL event_scheduler = 1;8、好处
当我们把一些业务逻辑通过编写事件、搭建结果表、sql函数等方式放在sql中去实现后，业务代码中执行，可以一定程度上减少接口的响应时间，提升项目性能以及用户体验感。
9、防止数据库连接池耗尽的超时时间设置中文社区 (MOSC)
中间件 (MOSC)
0 Replies
Last updated on March 27, 2015
1 WebLogic Server自动回收使用超时的连接WebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool -> "Inactive Connection Timeout"
设置为大于0的秒数。WebLogic Server 将自动检测出使用时间过长的连接并强制收回, 输出警告信息到日志文件:BEA-001153: Forcibly releasing inactive connection. 占用时间过长的连接分为两种情形: 一是应用程序有bug, 没有调用javax.sql.Connection.close()归还连接, 另一种是长时间执行SQL一直没有完成。
2 设置 JDBC SQL Statement TimeoutWebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool -> "Statement Timeout"
设置为大于0的秒数。当SQL语句执行时间过长, 超过这个时间时Oracle JDBC驱动程序会抛出java.sql.SQLException: ORA-01013: user requested cancel of current operation. 这个特性依赖于JDBC驱动程序如何实现java.sql.Statement.setQueryTimeout()方法。
3 JDBC driver Socket TimeoutWebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool, 在"Properties"输入框输入属性名字和值，每行一个。例如 Oracle JDBC driver 支持的:
oracle.net.READ_TIMEOUT=30000
oracle.jdbc.ReadTimeout=30000
当 Socket 操作因为网络问题或者数据库服务器端没有回应导致超过这个时间, Oracle JDBC driver 会抛出 java.sql.SQLRecoverableException:To view full details, sign in with your My Oracle Support Community account.
Don't have a My Oracle Support
Community account? Click to get started!
