- 浏览: 234430 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
netwelfare:
文章的格式有点乱啊,看起来很费事啊。推荐看这个系列的json教 ...
java 操作JSON -
yangqingandjay:
ijoi刚好一个月[color=orange][align=c ...
spring 定时器配置 -
Jxdwuao:
不错, 很好用
spring 定时器配置 -
1870702720:
3楼说的可以,之前我一直以为想配置在一个定时器中,可是实现不了 ...
spring 定时器配置 -
taiwei.peng:
你可以配置两个定时器,这是我个人的想法,仅供参考。
spring 定时器配置
select cast(last_operate_tm as timestamp) query_time,
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
lostime,
warntime,
zone_code,
high_worth,
countnum,
exception_node,
appreciation_service_type,
cast(doubt_miss_tm as timestamp) doubt_miss_tm
from (select count(*) over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) countnum,
row_number() over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) rw,
last_operate_tm,
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
round(to_number(sysdate - last_operate_tm) * 1440) as lostime,
round(to_number(sysdate - doubt_miss_tm) * 1440) as warntime,
zone_code,
high_worth,
exception_node,
appreciation_service_type,
doubt_miss_tm
from tt_doubt_miss m, tm_department t
where m.zone_code = t.dept_code
and t.dist_code in ('755')) A
WHERE countnum = RW
ORDER BY LAST_OPERATE_TM ASC
1.like 查询
select * from book b where b.name like '%\_%' escape '\';
2.反索引函数
select * from book b where reverse(b.name) like reverse('%02');
3.rollup 函数
MERGE INTO TT_UNIQUE_TCMSEXP TA
USING (SELECT *
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.AEMS_EXP_ID ORDER BY A.TCMS_CREAT_TM DESC) RN
FROM TT_CONVEYANCE_EXP A
WHERE A.CREATE_TM >= V_EXP_SYNCTM
AND A.CREATE_TM <= V_SYSTM + 1 / (24 * 60))
WHERE RN = 1) TB
ON (TA.AEMS_EXP_ID = TB.AEMS_EXP_ID)
WHEN MATCHED THEN
UPDATE
SET TA.DEPART_TYPE = TB.DEPART_TYPE,
TA.VEHICLE_CODE = TB.VEHICLE_CODE,
TA.EXP_TYPE = TB.EXP_TYPE,
TA.EXP_DESC = TB.EXP_DESC,
TA.EXP_RESULT = TB.EXP_RESULT,
TA.EXP_REMARK = TB.EXP_REMARK,
TA.AGENT_NAME = TB.AGENT_NAME,
TA.MODIFY_TM = V_SYSTM
WHEN NOT MATCHED THEN
INSERT
(AEMS_EXP_ID,
DEPART_TYPE,
VEHICLE_CODE,
LINE_CODE,
CONVEYANCE_NAME,
SEND_BILL_ID,
EXP_TYPE,
EXP_DESC,
EXP_RESULT,
EXCEPTION_CODE,
EXP_REMARK,
EXP_SOURCE,
CREATE_TM,
MODIFY_TM,
AGENT_NAME,
IS_SPECIAL,
SEND_DT,
TCMS_CREAT_TM)
VALUES
(TB.AEMS_EXP_ID,
TB.DEPART_TYPE,
TB.VEHICLE_CODE,
TB.LINE_CODE,
TB.CONVEYANCE_NAME,
TB.SEND_BILL_ID,
TB.EXP_TYPE,
TB.EXP_DESC,
TB.EXP_RESULT,
DECODE(TB.EXP_RESULT,
'航班取消',
'1',
'航班拉货',
'2',
'火车取消',
'3',
'火车拉货',
'4',
'5'),
TB.EXP_REMARK,
TB.EXP_SOURCE,
V_SYSTM,
V_SYSTM,
TB.AGENT_NAME,
TB.IS_SPECIAL,
TB.SEND_DT,
TB.TCMS_CREAT_TM);
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
lostime,
warntime,
zone_code,
high_worth,
countnum,
exception_node,
appreciation_service_type,
cast(doubt_miss_tm as timestamp) doubt_miss_tm
from (select count(*) over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) countnum,
row_number() over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) rw,
last_operate_tm,
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
round(to_number(sysdate - last_operate_tm) * 1440) as lostime,
round(to_number(sysdate - doubt_miss_tm) * 1440) as warntime,
zone_code,
high_worth,
exception_node,
appreciation_service_type,
doubt_miss_tm
from tt_doubt_miss m, tm_department t
where m.zone_code = t.dept_code
and t.dist_code in ('755')) A
WHERE countnum = RW
ORDER BY LAST_OPERATE_TM ASC
1.like 查询
select * from book b where b.name like '%\_%' escape '\';
2.反索引函数
select * from book b where reverse(b.name) like reverse('%02');
3.rollup 函数
MERGE INTO TT_UNIQUE_TCMSEXP TA
USING (SELECT *
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.AEMS_EXP_ID ORDER BY A.TCMS_CREAT_TM DESC) RN
FROM TT_CONVEYANCE_EXP A
WHERE A.CREATE_TM >= V_EXP_SYNCTM
AND A.CREATE_TM <= V_SYSTM + 1 / (24 * 60))
WHERE RN = 1) TB
ON (TA.AEMS_EXP_ID = TB.AEMS_EXP_ID)
WHEN MATCHED THEN
UPDATE
SET TA.DEPART_TYPE = TB.DEPART_TYPE,
TA.VEHICLE_CODE = TB.VEHICLE_CODE,
TA.EXP_TYPE = TB.EXP_TYPE,
TA.EXP_DESC = TB.EXP_DESC,
TA.EXP_RESULT = TB.EXP_RESULT,
TA.EXP_REMARK = TB.EXP_REMARK,
TA.AGENT_NAME = TB.AGENT_NAME,
TA.MODIFY_TM = V_SYSTM
WHEN NOT MATCHED THEN
INSERT
(AEMS_EXP_ID,
DEPART_TYPE,
VEHICLE_CODE,
LINE_CODE,
CONVEYANCE_NAME,
SEND_BILL_ID,
EXP_TYPE,
EXP_DESC,
EXP_RESULT,
EXCEPTION_CODE,
EXP_REMARK,
EXP_SOURCE,
CREATE_TM,
MODIFY_TM,
AGENT_NAME,
IS_SPECIAL,
SEND_DT,
TCMS_CREAT_TM)
VALUES
(TB.AEMS_EXP_ID,
TB.DEPART_TYPE,
TB.VEHICLE_CODE,
TB.LINE_CODE,
TB.CONVEYANCE_NAME,
TB.SEND_BILL_ID,
TB.EXP_TYPE,
TB.EXP_DESC,
TB.EXP_RESULT,
DECODE(TB.EXP_RESULT,
'航班取消',
'1',
'航班拉货',
'2',
'火车取消',
'3',
'火车拉货',
'4',
'5'),
TB.EXP_REMARK,
TB.EXP_SOURCE,
V_SYSTM,
V_SYSTM,
TB.AGENT_NAME,
TB.IS_SPECIAL,
TB.SEND_DT,
TB.TCMS_CREAT_TM);
发表评论
-
时效承诺明细产品流向存储过程
2016-05-04 14:30 0CREATE OR REPLACE PROCEDURE ST ... -
oracle 小知识点
2016-05-02 15:46 507oracle 默认排序空值在后面,显示在前面用nvl(co ... -
oracle 分析函数
2016-05-02 15:17 320row_number() over(partition by ... -
时效承诺明细行政区到城市
2016-04-28 13:18 0CREATE OR REPLACE PROCEDURE ST ... -
修改行政区ID的存储过程
2016-04-28 13:15 0CREATE OR REPLACE PROCEDURE S ... -
hive 资料
2016-04-11 20:07 0hive 培训资料 -
oracle 三种表连接方式
2016-04-10 18:04 939oracle 小知识 1.oracle 查看表大小 SEL ... -
sql 基本优化
2016-03-17 21:58 6561.select语句中避免使用*;2.ORACLE 采用从 ... -
最近存储过程
2015-07-28 10:43 563CREATE OR REPLACE PROCEDURE STP ... -
临时取数脚本
2015-07-23 10:39 634--01 删除表中的现有数据 TRUNCATE TABLE T ... -
oracle case when then 用法
2014-10-11 12:15 1205SELECT D.DEPT_CODE, ... -
oracle 创建无参存储过程
2014-09-23 15:26 1472create or replace procedure pro ... -
Oracle表分区与索引的创建
2013-10-22 11:00 1423create table TT_FLOW_TEST ( i ... -
PL SQL Develop中文乱码问题
2013-03-29 10:53 22621.准备软件 下载 oracle client客服端工具包 ... -
Oracle DB link
2012-11-05 22:29 1027drop database link jc_zd cre ... -
Oracle 下的自定义函数
2012-10-21 15:25 911create or replace function MY_L ... -
Oracle 带游标的存储过程
2012-10-21 15:11 1141CREATE OR REPLACE PROCEDURE ST ... -
oracle 递归查询一个树形结构的菜单
2011-09-02 11:53 21296关键字:oracle connect by level 树状结 ... -
oracle 查询日期
2011-09-02 11:53 1073--查询一个季度的第一天select trunc(sysda ... -
返回自定义游标的存储过程
2011-09-02 11:51 1019create or replace procedure pro ...
相关推荐
有近20年使用Oracle技术产品以及Oracle数据库管理员/Oracle数据库应用管理员的经验,是真正应用集群、性能调优以及数据库内部属性方面的专家。同时是一位演讲家及Oracle ACE。 JARED STILL 从1994年就开始使用...
10.1 描述和使用SQL中的字符、数字和日期函数 348 10.1.1 定义函数 348 10.1.2 函数类型 348 10.1.3 使用大小写转换函数 349 10.1.4 使用字符操作函数 350 10.1.5 使用数字函数 353 10.1.6 使用日期 355 ...
综上所述,这份文档涵盖了 Oracle8i 数据库的基础知识,包括 SQL*PLUS 的使用、PL/SQL 的基础、表结构的管理、高级特性如视图、存储过程等,以及一些更深入的主题如序列和同义词的使用。这对于想要学习或加深对 ...
- Oracle提供了多种舍入函数,如ROUND、TRUNC等,用于数字的四舍五入处理。 **4. Oracle定时器** - 定时器可以定期执行任务,适用于需要定期维护的任务。 **5. Over分析查询** - OVER子句用于进行分析查询,如计算...
- **方法**: 使用“message”函数显示信息提示框。 - **应用场景**: 向用户提供反馈信息。 11. **用代码控制ITEM属性** - **方法**: 通过“item.property = value”设置Item的属性值。 - **应用场景**: 动态...
10.1 描述和使用SQL中的字符、数字和日期函数 348 10.1.1 定义函数 348 10.1.2 函数类型 348 10.1.3 使用大小写转换函数 349 10.1.4 使用字符操作函数 350 10.1.5 使用数字函数 353 10.1.6 使用日期 355 ...