oracle 常用功能

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;

/