-- 고객별로 마지막 주문만 조회하기
select * from t_ord t1;
SELECT
t2.*
FROM
(
SELECT
t1.*,
ROW_NUMBER()
OVER(PARTITION BY t1.cus_id
ORDER BY
t1.ord_dt DESC, t1.ord_seq DESC
) ord_rnk
FROM
t_ord t1
) t2
WHERE
t2.ord_rnk = 1;
create table M_CUS_CUD_TEST AS
select * from M_CUS t1;
alter table M_CUS_CUD_TEST
ADD constraint PK_M_CUS_CUD_TEST PRIMARY KEY (CUS_ID) using INDEX;
- M_CUS_CUD_TEST에 고객을 입력하거나 변경하는 프로세스가 있다고 가정
- 고객ID와 고객정보를 외부에서 입력 변수로 받는다
이때 같은 고객ID가 있으면 고객 정보를 업데이트 하고, 같은 고객 ID가 없으면 신규고객으로 등록을 한다.
* PL/SQL 작성
1. SET SERVEROUTPUT ON;
만약 블록 내에서 DBMS_OUTPUT.PUT_LINE을 사용하여 메시지를 출력했다면, 실행 전에 해당 세션에서 출력 기능을 활성화해야 합니다.
2. 저장 프로시저(Stored Procedure)로 만들어 실행
CREATE OR REPLACE PROCEDURE P_CUS_CUD_TEST
IS
v_EXISTS_YN varchar2(1);
BEGIN
SELECT NVL(MAX('Y'), 'N')
INTO v_EXISTS_YN
FROM DUAL A
WHERE EXISTS (
SELECT * FROM M_CUS_CUD_TEST T1
WHERE T1.CUS_ID='CUS_0090'
);
IF v_EXISTS_YN='N' THEN
INSERT INTO M_CUS_CUD_TEST (CUS_ID, CUS_NM, CUS_GD)
VALUES ('CUS_0090', 'NAME_0090', 'B');
DBMS_OUTPUT.PUT_LINE('INSERT NEW CUST');
ELSE
UPDATE M_CUS_CUD_TEST T1
SET T1.CUS_NM='NAME_0090', T1.CUS_GD='B'
WHERE CUS_ID='CUS_0090';
DBMS_OUTPUT.PUT_LINE('UPDATE OLD CUST');
END IF;
COMMIT;
END;
/ -- **이 슬래시가 실행 명령입니다!**
3. 프로시저를 생성한 후에는 EXECUTE 명령어로 간단하게 실행할 수 있습니다.
EXECUTE 내_프로시저_이름;
-- 또는
EXEC 내_프로시저_이름;
4. parameter를 받아서 처리하는 프로시저
CREATE OR REPLACE PROCEDURE P_CUS_CUD_TEST (
p_CUS_ID IN VARCHAR2, -- 1. 입력할 고객 ID
p_CUS_NM IN VARCHAR2, -- 2. 입력할 고객 이름
p_CUS_GD IN VARCHAR2 -- 3. 입력할 고객 등급
)
IS
v_EXISTS_YN varchar2(1);
BEGIN
SELECT NVL(MAX('Y'), 'N')
INTO v_EXISTS_YN
FROM DUAL A
WHERE EXISTS (
SELECT * FROM M_CUS_CUD_TEST T1
WHERE T1.CUS_ID=p_CUS_ID
);
IF v_EXISTS_YN='N' THEN
INSERT INTO M_CUS_CUD_TEST (CUS_ID, CUS_NM, CUS_GD)
VALUES (p_CUS_ID, p_CUS_NM, p_CUS_GD);
DBMS_OUTPUT.PUT_LINE('INSERT NEW CUST');
ELSE
UPDATE M_CUS_CUD_TEST T1
SET T1.CUS_NM=p_CUS_NM, T1.CUS_GD=p_CUS_GD
WHERE CUS_ID=p_CUS_ID;
DBMS_OUTPUT.PUT_LINE('UPDATE OLD CUST');
END IF;
COMMIT;
END;
/
-- 실행예)
exec P_CUS_CUD_TEST('CUS_0091', 'NAME_0091', 'D');
5. MERGE SQL
MERGE INTO M_CUS_CUD_TEST T1
USING (
SELECT 'CUS_0090' CUS_ID, 'NAME_0090' CUS_NM, 'A' CUS_GD FROM DUAL
) T2 ON (T1.CUS_ID = T2.CUS_ID)
WHEN MATCHED THEN UPDATE SET T1.CUS_NM=T2.CUS_NM, T1.CUS_GD = T2.CUS_GD
WHEN NOT MATCHED THEN INSERT (T1.CUS_ID, T1.CUS_NM, T1.CUS_GD) VALUES(T2.CUS_ID, T2.CUS_NM, T2.CUS_GD);
아우터-조인은 조건에 만족하지 않은 데이터도 결과에 나온다.
- 기준 데이터 집합: 아우터-조인의 기준이 되는 집합
- 참조 데이터 집합: 아우터-조인의 참조가 되는 집합
'기준 데이터 집합'은 조인 조건을 만족하지 않아도 모두 결과에 포함된다.
아우터-조인을 사용하려면, 조인 조건 컬럼 한쪽에 '(+)' 표시를 추가하면 된다.
'기준 데이터 집합'과 '참조 데이터 집합'은 '(+)' 표시로 구분한다.
조인조건에 '(+)' 표시가 붙은 쪽은 '참조 데이터 집합', 없는 쪽은 '기준 데이터 집합'이다.
1. 이너조인
select t1.*, t2.*
from m_cus t1, t_itm_evl t2
where t1.cus_id = 'CUS_0002'
and t1.cus_id = t2.cus_id;
2. 아우터조인
select t1.cus_id, t1.cus_nm, t2.cus_id, t2.itm_id, t2.evl_lst_no
from m_cus t1, t_itm_evl t2
where t1.cus_id = 'CUS_0002' and
t1.cus_id = t2.cus_id(+);
M_CUS에는 'CUS_0002'의 데이터가 있지만 T_ITM_EVL에는 'CUS_0002'의 데이터가 없다.
이 상황에서, 이너조인은 아무 결과도 조회되지 않는다.
아우터 조인을 해야만 'CUS_0002' 고객의 정보가 조회된다.
3. 아우터조인의 필터조건
select t1.cus_id, t1.cus_nm, t2.cus_id, t2.itm_id, t2.evl_lst_no, t2.evl_dt
from m_cus t1, t_itm_evl t2
where t1.cus_id = 'CUS_0073'
and t1.cus_id = t2.cus_id(+)
and t2.evl_dt >= to_date('20170201', 'YYYYMMDD')
and t2.evl_dt < to_date('20170301', 'YYYYMMDD');
select t1.cus_id, t1.cus_nm, t2.cus_id, t2.itm_id, t2.evl_lst_no, t2.evl_dt
from m_cus t1, t_itm_evl t2
where t1.cus_id = 'CUS_0073'
and t1.cus_id = t2.cus_id(+)
and t2.evl_dt(+) >= to_date('20170201', 'YYYYMMDD')
and t2.evl_dt(+) < to_date('20170301', 'YYYYMMDD');
두 SQL 모두 아우터조인을 하고 있다.
첫번째 SQL은 조회되는 결과가 없다. 두번째만 결과가 나온다.
아우터 조인을 사용할 때는 참조 데이터 집합의 필터조건에 '(+)' 표시를 해주는 것이 일반적이다.
4. 아우터조인 응용
고객별 1월의 주문건수를 구하는 SQL. 주문이 없는 고객은 주문건수가 0으로 조회되어야 한다.
아래와 같이 아우터조인을 활용할 수 있다.
select t1.cus_id, count(*) ord_cnt_1, count(t2.ord_seq) ord_cnt2
from m_cus t1, t_ord t2
where t1.cus_id = t2.cus_id(+)
and t2.ord_dt(+) >= to_date('20170101', 'YYYYMMDD')
and t2.ord_dt(+) < to_date('20170201', 'YYYYMMDD')
group by t1.cus_id
order by count(*), t1.cus_id;
고객테이블을 기준집합으로 아우터조인 했기 때문에, 주문이 없는 고객도 모두 조회가 가능하다.