oracle脚本之左右连接+、LOOP、MERGE

uuogwxyiqs

　　左右连接+
　　

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