1、oracle的两种分页方式
①、select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum
where rn >=6;
②、select * from (select e.*,rownum rn from emp e) a1 where a1.rn between 6 and 10;
2、序列
select max(HANDOVER_BILL_ID) FROM HK_INTERCHANGE --30014589
select SQ_HK_INTERCHANGE.nextval as id from dual --30000246
alter sequence SQ_HK_INTERCHANGE increment by 10000;(序列的递增递减跨度,正的是递增,负的是递减)
-- Modify the last number
alter sequence SQ_HK_ADD_CON increment by 99899999 nocache;
select SQ_HK_ADD_CON.nextval from dual;
alter sequence SQ_HK_ADD_CON increment by 1 nocache;
declare
LastValue integer;
begin
loop
select SQ_HK_ADD_CON.currval into LastValue from dual;
exit when LastValue >= 99999999 - 1;
select SQ_HK_ADD_CON.nextval into LastValue from dual;
end loop;
end;
/
alter sequence SQ_HK_ADD_CON increment by 1 nocache;
3、将查询结果列转化成逗号分隔的字符串
select TO_CHAR(wm_concat(flightorplate_no)) flightorplate_no From HK_AIRMASTERBILLS t where master_bill_code='222-333333' GROUP BY t.master_bill_code
4、复杂sql
UPDATE HK_AIRMASTERBILLS ha SET
HA.FLIGHTORPLATE_NO =
(
SELECT HAU.FLIGHT_NO FROM HK_HAULINFO hau WHERE
HAU.AIRLINE_ID = HA.AIRLINE_ID AND
HAU.HAUL_SEQ = HA.LINE_TYPE AND
HAU.REAL_LINE_ID = HA.REAL_LINE_ID AND
HAU.FROMCITY = HA.FLY_FROM_POINT AND
HAU.ARRIVALCITY = HA.FLY_ARRIVE_POINT AND
HAU.HAUL_TYPE = HA.TRANS_TYPE
),
HA.AIRWAY =
(
SELECT HAU2.AIRWAY FROM HK_HAULINFO hau2 WHERE
HAU2.AIRLINE_ID = HA.AIRLINE_ID AND
HAU2.HAUL_SEQ = HA.LINE_TYPE AND
HAU2.REAL_LINE_ID = HA.REAL_LINE_ID AND
HAU2.FROMCITY = HA.FLY_FROM_POINT AND
HAU2.ARRIVALCITY = HA.FLY_ARRIVE_POINT AND
HAU2.HAUL_TYPE = HA.TRANS_TYPE
)
WHERE
ha.FLIGHTORPLATE_NO IS NULL AND
ha.AIRWAY IS NULL AND
ha.MASTER_BILL_TYPE IN ('0','1') AND
ha.CREATE_TIME > to_date('2013-10-07 00:00','yyyy-mm-dd hh24:mi') AND
ha.REAL_LINE_ID IS NOT NULL AND
(SELECT hairnew.TRANSITCITY FROM HK_AIRLINE_NEW hairnew WHERE hairnew.AIRLINE_ID = ha.AIRLINE_ID) =
ha.TRANSFER_CITY AND
ha.AIRLINE_TYPE = (SELECT HAIRNEW2.AIRLINE_TYPE FROM HK_AIRLINE_NEW hairnew2 WHERE hairnew2.AIRLINE_ID = ha.AIRLINE_ID)
5、特殊的update
update BD_CXXX set REQ_SPD=((CASE WHEN MLG_CNT >0 THEN MLG_CNT ELSE EST_MLG_CNT END) *60)/RUN_TM
WHERE RUN_TM>0 and REQ_SPD <= 0;
6、查看Oracle执行计划:
explain plan for select count(*) from BD_CAR_LINE_BIND;
select * from table(DBMS_XPLAN.DISPLAY);
7、Oracle的CASE When
select decode(depaofav_pro,'上海市',depaofav_pro,'天津市',depaofav_pro,'重庆市',depaofav_pro,'北京市',depaofav_pro,depaofav_pro||'.'||depaofav_city) city
from HK_DEPAOFAVIATION
8、一条select语句动态产生update sql
select 'update car_bill_record set billdatetime=`'
|| TO_CHAR(start_tm,'yyyy-mm-dd hh24:mi:ss')
|| '` where billnumber=`' || bill_id
|| '` and lineId=`' || ship_id ||'`'
as updateSql from BD_TRAN_VCHR where
arv_site_tm > TO_DATE('2014-08-24 00:00:00','yyyy-mm-dd hh24:mi:ss')
select 'update repetition_schedule_stop_d'||
' set day_adjustment=(select LEV_DT from bd_tjd where'||
' CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') ),'||
'hour_adjustment= (select to_number(substr(STD_LEV_TM,0,2)) from bd_tjd where CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='
||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') ),'||
'minute_adjustment= (select to_number(substr(STD_LEV_TM,4)) from bd_tjd where CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='
||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') )'||
'where repetition_schedule_gid in ('||''''||'YD.'||b.CAR_LN_CD||''''||') and rep_sched_activity_gid='||''''||'DEPART'||''''||'AND SEQUENCE='||b.PATH_PLC_ID
from (
select (select car_ln_cd from BD_cxxx where id=cxxx_id) CAR_LN_CD,DRV_DT,STD_ARV_TM,LEV_DT,STD_LEV_TM,PATH_PLC_ID from bd_tjd order by cxxx_id asc,PATH_PLC_ID asc
) b
select 'update repetition_schedule_stop_d'||
' set day_adjustment=(select DRV_DT from bd_tjd where'||
' CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') ),'||
'hour_adjustment= (select to_number(substr(STD_ARV_TM,0,2)) from bd_tjd where CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='
||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') ),'||
'minute_adjustment= (select to_number(substr(STD_ARV_TM,4)) from bd_tjd where CXXX_ID in ( select id from BD_CXXX where DEL_FLG=0 and CAR_LN_CD='
||''''||b.CAR_LN_CD||''''|| 'AND PATH_PLC_ID='||b.PATH_PLC_ID||') )'||
'where repetition_schedule_gid in ('||''''||'YD.'||b.CAR_LN_CD||''''||') and rep_sched_activity_gid='||''''||'ARRIVE'||''''||'AND SEQUENCE='||b.PATH_PLC_ID
from (
select (select car_ln_cd from BD_cxxx where id=cxxx_id) CAR_LN_CD,DRV_DT,STD_ARV_TM,LEV_DT,STD_LEV_TM,PATH_PLC_ID from bd_tjd order by cxxx_id asc,PATH_PLC_ID asc
) b
9、
DECLARE
V_REQ_SPD NUMBER(16, 6);
V_MLG_CNT NUMBER(10, 2);
V_TTL_ALL_MIN INTEGER;
V_TTL_GAP_MIN INTEGER;
V_FIRST_LEV_DT INTEGER;
V_FIRST_LEV_TM VARCHAR2(20);
V_DRV_DT INTEGER;
V_LEV_DT INTEGER;
V_ARV_TM VARCHAR2(20);
V_LEV_TM VARCHAR2(20);
V_DATE_GAP INTEGER;
V_HOUR_GAP INTEGER;
V_MIN_GAP INTEGER;
V_DATE_ALL INTEGER;
V_HOUR_ALL INTEGER;
V_MIN_ALL INTEGER;
V_SEQ INTEGER;
V_COUNT INTEGER;
CURSOR CX_INFO IS
SELECT ID, MLG_CNT, EST_MLG_CNT, REQ_SPD FROM BD_CXXX;
CX_ROW CX_INFO%ROWTYPE;
CURSOR TJD_INFO(P_CXXX_ID VARCHAR2) IS
SELECT DRV_DT, LEV_DT, STD_ARV_TM, STD_LEV_TM, PATH_PLC_ID
FROM BD_TJD
WHERE CXXX_ID = P_CXXX_ID
ORDER BY PATH_PLC_ID;
BEGIN
V_COUNT := 0;
FOR CX_ROW IN CX_INFO LOOP
V_TTL_GAP_MIN := 0;
V_TTL_ALL_MIN := 0;
V_MLG_CNT := 0;
V_REQ_SPD := 0;
OPEN TJD_INFO(CX_ROW.ID);
LOOP
FETCH TJD_INFO
INTO V_DRV_DT, V_LEV_DT, V_ARV_TM, V_LEV_TM, V_SEQ;
EXIT WHEN TJD_INFO%NOTFOUND;
IF V_SEQ = 1 THEN
V_FIRST_LEV_DT := V_LEV_DT;
V_FIRST_LEV_TM := V_LEV_TM;
END IF;
IF V_SEQ > 1 THEN
V_DATE_GAP := V_LEV_DT - V_DRV_DT;
V_HOUR_GAP := TO_NUMBER(SUBSTR(V_LEV_TM,
0,
INSTR(V_LEV_TM, ':') - 1)) -
TO_NUMBER(SUBSTR(V_ARV_TM,
0,
INSTR(V_ARV_TM, ':') - 1));
V_MIN_GAP := TO_NUMBER(SUBSTR(V_LEV_TM,
INSTR(V_LEV_TM, ':') + 1)) -
TO_NUMBER(SUBSTR(V_ARV_TM,
INSTR(V_ARV_TM, ':') + 1));
V_TTL_GAP_MIN := V_TTL_GAP_MIN + V_DATE_GAP * 24 * 60 +
V_HOUR_GAP * 60 + V_MIN_GAP;
END IF;
END LOOP;
V_DATE_ALL := V_LEV_DT - V_FIRST_LEV_DT;
V_HOUR_ALL := TO_NUMBER(SUBSTR(V_LEV_TM, 0, INSTR(V_LEV_TM, ':') - 1)) -
TO_NUMBER(SUBSTR(V_FIRST_LEV_TM,
0,
INSTR(V_FIRST_LEV_TM, ':') - 1));
V_MIN_ALL := TO_NUMBER(SUBSTR(V_LEV_TM, INSTR(V_LEV_TM, ':') + 1)) -
TO_NUMBER(SUBSTR(V_FIRST_LEV_TM,
INSTR(V_FIRST_LEV_TM, ':') + 1));
V_TTL_ALL_MIN := V_DATE_ALL * 24 * 60 + V_HOUR_ALL * 60 + V_MIN_ALL;
IF CX_ROW.MLG_CNT IS NOT NULL AND CX_ROW.MLG_CNT > 0 THEN
V_MLG_CNT := CX_ROW.MLG_CNT;
ELSIF CX_ROW.EST_MLG_CNT IS NOT NULL AND CX_ROW.EST_MLG_CNT > 0 THEN
V_MLG_CNT := CX_ROW.EST_MLG_CNT;
END IF;
IF V_TTL_ALL_MIN - V_TTL_GAP_MIN > 0 THEN
V_REQ_SPD := V_MLG_CNT / (V_TTL_ALL_MIN - V_TTL_GAP_MIN) * 60;
END IF;
IF V_REQ_SPD != CX_ROW.REQ_SPD AND V_REQ_SPD != 0 THEN
V_COUNT := V_COUNT + 1;
UPDATE BD_CXXX SET REQ_SPD = V_REQ_SPD WHERE ID = CX_ROW.ID;
END IF;
CLOSE TJD_INFO;
END LOOP;
DBMS_OUTPUT.PUT_LINE('更新的记录总数为:' || V_COUNT);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误信息:' || SQLERRM());
END;
/
相关推荐
主要内容:Oracle常用功能介绍,基础帮扶、新手利器
oracle常用函数功能演示,一天之内成为万人羡慕的oracle专家!
ORACLE内置函数,相当于C语言中的库函数,提供了常规数据库编程中所需的绝大多数基本功能,并且通过函数的组合或嵌套使用,可以发挥其强大的功能。由于这些内置函数都是由ORACLE公司的编程大家所写,而且其执行代码...
ORACLE内置函数,相当于C语言中的库函数,提供了常规数据库编程中所需的绝大多数基本功能,并且通过函数的组合或嵌套使用,可以发挥其强大的功能。由于这些内置函数都是由ORACLE公司的编程大家所写,而且其执行代码...
数值函数: abs(m) m的绝对值 mod(m,n) m被n除后的余数 power(m,n) m的n次方 round(m[,n]) m四舍五入至小数点后n位的值(n缺省为0) trunc(m[,n]) m截断n位小数位的值(n缺省为0) ——————————————...
oracle常用经典sql查询功能代码,43页,有目录,各种常用代码,工具,备查!
oracle 常用命令_oracle 命令大全_oracle命令集合_oracle基本命令 非常方便,带查询功能,能很方便的查询你要找的oracle命令写法
oracle常用功能函数汇总********************
jsp 加 oracle 数据库分页,自行创建表结构,表很简单的,比较适合初学者使用,共5个例子程序,其中常用的分页效果都有.
Oracle数据库11g各版本介绍及功能比较
oracle维护方面的参考资料,包括基本架构、功能和一些常用的命令
介绍oracle存储过程的常用使用技巧
Oracle分区功能提高应用程序性能 79 Oracle数据库处理时间基本准则 81 入侵Oracle数据库常用操作命令 82 Oracle数据库优化及其应用程序研究 83 Instance实例和数据库 85 Oracle数据缓冲区内部机制 85 Oracle 9i...
本书作为一本创作之初就定位于技术应用的实践参考书,虽然前前后后串联了 Oracle 数据库中十余个常用特性或工具,在涉及特性或工具应用的每一个章节,均有相关实例演示如何应用,所有示例的最终目的,就是希望阅读...
此文档包含了ORACLE常用的功能:数据库解锁,查看当前连接数,检查IO,更改数据库字符集,统计数据, 建表空间,查询表空间占用情况,删除表空间,恢复表空间
第一部分:ORACLE常用经典SQL查询 第二部分:Oracle 最常用功能函数经典汇总
Oracle11g_新特性及常用维护工具详解
ORACLE常用命令大全 数据查询,是数据库操作中最主要的功能之一;有时候数据库查询性能的好坏,直接关系到数据库的运行效率,关系到数据库的选型。下面笔者不谈大道理,只是对其中对一些平时大家容易忽略的查询小技巧...
Oralce Database Client 是从 Oralce 11gR2...仅仅 72M,就几乎包括了原版 700M 安装包里的全部常用功能。 1、本软件适用于 32 位系统,64 位系统也可用; 2、本次修正了 64 位系统安装此 32 位包的一处读写注册表问题;