oracle腳本之左右連接配接+、LOOP、MERGE

左右連接配接+

select a.dptid,name from testone a left outer join testtwo b on a.DPTID=b.dptid;//左連接配接

select a.dptid,name from testone a,testtwo b where a.DPTID=b.dptid(+);//左連接配接

備注:"+"在哪邊,哪邊可有可無,連接配接名稱在"+"号的反方向
           

LOOP

/* Formatted on 2007/10/30 20:40 (Formatter Plus v4.8.6) */
CREATE OR REPLACE PROCEDURE test_date_while (in_date IN VARCHAR2)
IS
   v_start_date   DATE;
   v_end_date     DATE;
BEGIN
   SELECT start_date, end_date
     INTO v_start_date, v_end_date
     FROM td_a_month
    WHERE MONTH = TO_NUMBER (SUBSTR (in_date, 1, 6));

   DBMS_OUTPUT.ENABLE (255);
   --BMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.put_line ('test...');

   LOOP
      EXIT WHEN v_start_date > v_end_date;
      DBMS_OUTPUT.put_line (TO_NUMBER (TO_CHAR (v_start_date, 'yyyymmdd')));
      DBMS_OUTPUT.put_line (TO_CHAR (v_start_date, 'yyyy-mm-dd'));
      v_start_date := v_start_date + 1;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;                                                       --ROLLBACK;
   WHEN OTHERS
   THEN
      NULL;                                                       --ROLLBACK;
END test_date_while;
           

MERGE

merge into tableX a
   using (select * from tableY) c
   on(	c.calling_brand_code = a.calling_brand_code
                 AND c.calling_area_code = a.calling_area_code
                 AND c.calling_tsp_code = a.calling_tsp_code
                 AND c.calling_code = a.calling_code)
   when matched then update SET
   			      a.last_date=decode(sign(last_date - to_date(i_dealdate,'yyyymmdd')),1,last_date,to_date(i_dealdate,'yyyymmdd')),
   			      a.num_cdrs= c.num_cdrs,
   			      a.DURATION= c.DURATION,
   			      a.settlement_duration= c.settlement_duration,
   			      a.sett_fees = c.sett_fees
   when not matched then insert (a.calling_code, a.calling_tsp_code, a.calling_area_code,
               a.calling_brand_code, a.first_date, a.last_date, a.num_cdrs, a.DURATION,
               a.settlement_duration, a.sett_fees)values
               (
               c.calling_code, c.calling_tsp_code, c.calling_area_code,
               c.calling_brand_code, to_date(i_dealdate,'yyyymmdd'), to_date(i_dealdate,'yyyymmdd'), c.num_cdrs,
               c.DURATION, c.settlement_duration, c.sett_fees);