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;
/