레코드가 있으면 UPDATE하고 없으면 INSERT해라 였는데,
오라클9i에서 새로 추가된 MERGE INTO 구문을 사용하면
대략 구문은 아래와 같습니다.
pl/sql환경에서는 위와 같이 사용해도 되지만,
sql을 꼭 사용해야한다면 이 방법이 낫겠네요.
[SYNTAX]
MERGE INTO 테이블 A -- update 또는 insert할 테이블
USING (테이블명 | SUBQUERY | 뷰) B -- 비교 대상
ON(A와 B의 조인식) -- 대상테이블과 비교테이블의 조인식
WHEN MATCHED THEN -- on절에서 일치하는 로우가 있다면
UPDATE SET = , , .... -- 업데이트하고,
WHEN NOT MATCHED THEN -- 일치하는 로우가 없다면
INSERT INTO(컬럼명) -- insert를
VALUES(값, 값, ...) -- 이 값으로 넣어라.
f_u1:
merge into tb_result a
using dual b
on ( a.plt_no = '$in_plt_no' and
a.srt_id = '$in_srt_id' )
when matched then
update set srt_qty = srt_qty + to_number('$in_srt_qty'),
fin_chgr_id = '$in_user_id',
fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
when not matched then
insert ( plt_no , srt_id , proj_no , blk_no ,
sass_no , prcd_sass_no , ptlst_no , pcs_gb ,
stype , srt_qty , uom_wgt , bom_org_cmpo ,
io_gb , req_gb , ptlst_stat , req_ser ,
lug_gb , mpart_plt_no , newprod_qty , reprod_qty ,
imprv_yn , plt_incd_yn , fs_srt_usr_id , fs_srt_dtm ,
fs_inpr_id )
values ( '$in_plt_no' , '$in_srt_id' , '$in_proj_no' , '$in_blk_no' ,
'$in_sass_no', '$in_prcd_sass_no', '$in_ptlst_no' , '$in_pcs_gb' ,
'$in_stype' , to_number('$in_srt_qty'), to_number('$in_uom_wgt'), '$in_bom_org_cmpo',
'$in_io_gb' , '$in_req_gb' , 'C' , to_number('$in_req_ser'),
'$in_lug_gb' , '$in_mpart_plt_no', to_number('$in_new_srt_qty'), to_number('$in_re_srt_qty'),
'N' , 'Y' , '$in_user_id' , to_char(sysdate, 'yyyymmddhh24miss'),
'$in_user_id' )
;
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
f_u2:
declare cursor csr1 is
select a.lug_gb as lug_gb ,
a.ptlst_no as lug_type,
'$in_bizcls' as bizcls ,
sum(a.srt_qty) as srt_qty
from tb_result a
where a.plt_no = '$in_plt_no'
group by a.lug_gb, a.ptlst_no
$in_semicolon
begin
for rec1 in csr1 loop
update tb_result
set stk_qty = stk_qty - rec1.srt_qty * decode('$in_out_gb', 'O', 1, -1),
fin_chgr_id = '$in_user_id',
fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
where lug_gb = rec1.lug_gb
and lug_type = rec1.lug_type
and bizcls = rec1.bizcls
$in_semicolon
if sql%notfound then
insert into tb_stk
( lug_gb ,
lug_type ,
bizcls ,
stk_qty ,
fs_inpr_id )
values ( rec1.lug_gb ,
rec1.lug_type,
rec1.bizcls ,
rec1.srt_qty * decode('$in_out_gb', 'O', -1, 1),
'$in_user_id' )
$in_semicolon
end if $in_semicolon
end loop $in_semicolon
end $in_semicolon
;
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
테이터를 넣어야할 테이블에
값이 이미 있으면 Update
값이 없으면 Insert
MERGE INTO table_name alias --테이블 명만 올수 있음(셀렉트 쿼리 No)
USING (table|view|subquery) alias --일반적으로 쓰는 쿼리 Ok
ON (join condition) --where 절이라고 생각하면됨(조건에 서브 OK)
WHEN MATCHED THEN --데이터 중복건이 있는경우 Update
UPDATE SET col1 = val1[, col2 = val2…]
WHEN NOT MATCHED THEN -- 중복건이 없는 경우 처리 Insert
INSERT (컬럼리스트) VALUES (값들....);
ex)
MERGE INTO CRCD_WF_CARD A
USING DUAL
ON ( A.WF_NO = ? AND A.CRD_NO =? )
WHEN MATCHED THEN
UPDATE SET
A.CRD_TYPE =?
, A.FML_SSN =?
, A.FMLHG_NM =?
, A.FMLENG_NM =?
, A.OCRD_NO =?
, A.DSN_CD =?
, A.DSN_NM =?
, A.BRD_CD =?
, A.CRD_GRD =?
, A.INTGLMT_AMT =?
, A.FML_STLACCT_NO =?
, A.CRD_ISSUE_DT =?
WHEN NOT MATCHED THEN
INSERT (
A.WF_NO
, A.CRD_NO
, A.CRD_TYPE
, A.FML_SSN
, A.FMLHG_NM
, A.FMLENG_NM
, A.OCRD_NO
, A.DSN_CD
, A.DSN_NM
, A.BRD_CD
, A.CRD_GRD
, A.INTGLMT_AMT
, A.FML_STLACCT_NO
, A.CRD_ISSUE_DT
) VALUES ( ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ? )
-------------------------------------------------------
ex2)
MERGE INTO TC_CUSTAGENT A
USING (SELECT BUSI_CODE, CUST_CODE, CHPE_NAME, CUST_PHNB, CUST_FANU
FROM TB_ESTMAST
WHERE BUSI_CODE = '___parameter___'
AND CUST_CODE = '____parameter___') B
ON (A.BUSI_CODE = B.BUSI_CODE
AND A.AFON_COMP = B.CUST_CODE
AND A.SEQU_NUMB = (SELECT MAX(SEQU_NUMB)
FROM TC_CUSTAGENT
WHERE BUSI_CODE = A.BUSI_CODE
AND AFON_COMP = A.AFON_COMP))
WHEN MATCHED THEN
UPDATE
SET A.CHPE_NAME = B.CHPE_NAME
,A.CABL_PHNB = B.CUST_PHNB
,A.FAXM_NUMB = B.CUST_FANU
WHEN NOT MATCHED THEN
INSERT ( A.AFON_COMP
,A.CHPE_NAME
,A.CABL_PHNB
,A.FAXM_NUMB
)
VALUES ( B.CUST_CODE
,B.CHPE_NAME
,B.CUST_PHNB
,B.CUST_FANU
);
>>>>>>>>>>>>>>>>
MERGE INTO
실제 적용하면서 발견한 문제점 (ORACLE 9.2.0.3.0 에서)
9.2.0.4.0 버젼에선 아래와 같은 문법으로도 MERGE INTO 가 정상적으로 동작하였다.
MERGE INTO POPULAR_KEYWORD
USING DUAL B
ON (FIX_YN = 'Y' AND CATEGORY = 'ALL' AND RANK = '1')
WHEN MATCHED THEN
UPDATE SET REG_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
VALUES ('ALL','TEST22','1','100',SYSDATE)
즉, ON 절에서 값이 NULL 이더라도 WHEN NOT MATCHED THEN 절을 정상적으로 수행하였는데
이상하게 다른서버(9.2.0.3.0) 에선 ON절의 값이 NULL 이면 정상적인 루틴을 밟지 못하드라..
즉, INSERT 문을 수행하지 못했다.
[ 0 rows Merged ]
그래서 아래와 같이 USING절에서 NULL값이 리턴하지 않게 변경해주고
ON 절에서 A 와 B 를 JOIN 해주니까 정상적으로 동작한다.
MERGE INTO POPULAR_KEYWORD2 A
USING (SELECT 'ALL' CATEGORY, '4' RANK, 'Y' FIX_YN FROM DUAL) B
ON (A.CATEGORY = B.CATEGORY AND A.RANK = B.RANK AND A.FIX_YN = B.FIX_YN)
WHEN MATCHED THEN
UPDATE SET REG_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
VALUES ('ALL','TEST22','4','100',SYSDATE)
[ 1 rows Merged ]
결론 ==> USING 다음의 SELECT 문의 결과 데이타는 반드시 존재하여야 한다 (NULL 불가).
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
9i이상에서는 데이타가 존재할 경우 갱신 없으면 등록하는 명령어로
MERGE INTO ~ USING ~ ON ~ WHEN ~ 명령어가 있습니다.
MERGE INTO oc_whslr_trd_rslt A
USING (
SELECT std_yymm, cust_nbr, whlsr_cust_nbr, sol_tp_cd, sol_cd,chnn_rt,
src_tp_cd,org_cd, emp_nbr,sell_amt, qty
FROM oc_temp_whslr_trd_rslt
) B
ON (
A.std_yymm = B.std_yymm AND
A.cust_nbr = B.cust_nbr AND
A.whlsr_cust_nbr = B.whlsr_cust_nbr AND
A.sol_cd = B.sol_cd AND
A.sol_tp_cd = B.sol_tp_cd AND
A.chnn_rt = B.chnn_rt
)
WHEN MATCHED THEN
UPDATE SET A.sell_amt = A.sell_amt + B.sell_amt,
A.qty = A.qty + B.qty
WHEN NOT MATCHED THEN
INSERT (
A.std_yymm,
A.cust_nbr,
A.whlsr_cust_nbr,
A.sol_cd,
A.sol_tp_cd,
A.chnn_rt,
A.src_tp_cd,
A.org_cd,
A.emp_nbr,
A.sell_amt,
A.qty,
A.drtn_amt,
A.drtn_qty,
A.last_chng_emp,
A.last_chng_dt )
VALUES (
B.std_yymm,
B.cust_nbr,
B.whlsr_cust_nbr,
B.sol_cd,
B.sol_tp_cd,
B.chnn_rt,
B.src_tp_cd,
B.org_cd,
B.emp_nbr,
B.sell_amt,
B.qty,
0,
0,
'CRMSP',
TO_CHAR(sysdate,'YYYYMMDD')
);
MERGE into KAC_PERSON a USING
(select user_id, OPER_FG, VARDATA, NUMDATA from KAC_PERSON
where user_id='57CCF7ADE63F3AC1' AND OPER_FG = 'MAIL_CAPACITY') b
ON (a.user_id=b.user_id and a.OPER_FG=b.OPER_FG)
WHEN MATCHED THEN UPDATE set a.NUMDATA = 15
WHEN NOT MATCHED THEN INSERT (a.user_id,a.OPER_FG,a.VARDATA,a.NUMDATA)
VALUES ('57CCF7ADE63F3AC1','MAIL_CAPACITY','mail',50)
CREATE TABLE KAC_PERSON
(
USER_ID CHAR(16) NOT NULL,
OPER_FG VARCHAR2(20) NOT NULL,
VARDATA VARCHAR2(30),
NUMDATA NUMBER(4),
CONSTRAINT PK_KAC_PERSON PRIMARY KEY (USER_ID, OPER_FG)
);
>>>>>>>>>>>>>>>>>>>>>>>>>
MERGE INTO tb_target a
USING (
select proj_no, spl_no, tag_no, msch_actv, instl_plt_no, wk_ord_dt
from tb_source
where lot_no = 'AJ'
and proj_no = '1697'
) b
ON ( a.proj_no = b.proj_no
and a.spl_no = b.spl_no
and a.tag_no = b.tag_no)
WHEN MATCHED THEN
update
set a.mk_fndt = b.wk_ord_dt,
a.matdlv_dt = b.wk_ord_dt,
a.gr_dt = b.wk_ord_dt,
a.stk_dt = b.wk_ord_dt,
a.gi_dt = b.wk_ord_dt
WHEN NOT MATCHED THEN
insert(a.proj_no, a.spl_no, a.tag_no, a.msch_actv, a.instl_plt_no,
a.mk_fndt, a.matdlv_dt, a.gr_dt, a.stk_dt, a.gi_dt)
values(b.proj_no, b.spl_no, b.tag_no, b.msch_actv, b.instl_plt_no,
b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt)
'Database > ORACLE' 카테고리의 다른 글
| 오라클 패키지 백업 프로그램 (2) | 2009.03.08 |
|---|---|
| ORACLE 10g 정규식 표현하기 (0) | 2009.03.08 |
| SYNONYMS (동의어) 일괄 생성하기 (0) | 2009.03.08 |
| 테이블 스페이스 생성 및 사용자 사용 테이블스페이스 변경 (0) | 2009.03.08 |
| 내장 환경 함수 USERENV (0) | 2009.03.08 |