읽는 즐거움

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;

고객테이블을 기준집합으로 아우터조인 했기 때문에, 주문이 없는 고객도 모두 조회가 가능하다.

베트남갈때 세일리 이심, 그리고 말톡 이심

이렇게 사용을 해봤는데 세일리 이심은 거의 사용할 수가 없었음

거의 터지지 않았어서 와이파이가 있는곳을 찾아야 하는 수준이었음

하지만 말톡 이심은 베트남 호치민 기준으로 와이파이가 필요 없는 수준으로 너무 잘터짐

한번도 안터진 적이 없었음

말톡 강추

내돈내산임