Oracle 실전 pack

1. 테이블 생성

CREATE TABLE TABLE_NAME(
	COLUMN_NAME1 COLUMN_PROPERTY (CONSTRAINT)
    COLUMN_NAME2 COLUMN_PROPERTY (CONSTRAINT)
    .
    .
    .
    )
    
 COMMENT ON TABLE TABLE_NAME IS "DISCRIPTION"
 COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME1 IS "DISCRIPTION"

COLUMN PROPERTY에는 VARCHAR, NUMBER, DATE등이 있다.
CONSTRAINT에는 PRIMARY KEY등이 있다.
TABlE을 생상하면 COMMENT와 같이 설명을 달아주는 것이 좋다.

2. 시퀀스 생성

CREATE SEQUENCE MAN.ORDER_SEQ
       INCREMENT BY 1
       START WITH 1
       MINVALUE 1
       MAXVALUE 9999
       CYCLE
       NOCACHE
       NOORDER;

시퀀스를 사용하지 않고 MAX+1등의 select문을 사용하면 동시성 문제가 발생할 수 있다. 그러니 시퀀스를 사용하도록 하자.

3. 인덱스 생성

CREATE INDEX INDEX_NAME TABLE_NAME(COLUMN_NAME);

4. FUNCTION 생성

// 공통코드 테이블에서 코드값을 활용해 명칭을 뱉어내는 함수
CREATE OR FUNCTION FUNCNTION_NAME(PARAM1, PARAM2)
RETURN DATA_TYPE
IS VAR_NAME VARCHAR2(100);  //내부에서 사용할 변수 생성
BEGIN
	SELECT TARGET_COLUMN_NAME INTO V_NM
   	  FROM TARGET_TABLE_NAME
     WHERE TARGET_TABLE_COLUMN1 = PARAM1
      AND TARGET_TABLE_COLUMN2 = PARAM2
     //  TARGET_TABLE_NAME에서 WHERE 조건에 맞는 DATA를 찾고 이것을 V_NM에 입력
     RETURN V_NM;
END FN_CODE_NM;
//BEGIN~END 실행할 쿼리        

FUNCTION은 보통 db의 데이터를 사용자에게 보여줄 때 사용한다.

등록 방법


ctrl+f9를 통해 compile을 하면 등록 완료.

실행 성공

5. PROCEDURE 생성

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(
	#입력 받는 매개변수
	IV_ITEM_CD IN VARCHAR2,
	IV_ITEM_NM IN TB_ITEM.ITEM_NM%TYPE,
    #PROCEDURE가 출력하는 매개변수
	OV_REG_DT OUT TB_ITEM.REG_DT%TYPE 
)
IS 
	#BEGIN ~ END에서 사용할 지역변수
	V_CNT NUMBER;
	V_REG_DT DATE;
BEGIN
	SELECT COUNT(*) INTO V_CNT
	  FROM TB_ITEM 
	 WHERE ITEM_CD = IV_ITEM_CD;
	
	IF V_CNT = 0 THEN
		INSERT INTO TB_ITEM(ITEM_CD, ITEM_NM, REG_DT, UPD_DT)
		VALUES(IV_ITEM_CD, IV_ITEM_NM, SYSDATE, SYSDATE);
	ELSE
		UPDATE TB_ITEM 
		   SET ITEM_NM = IV_ITEM_NM
		   ,   UPD_DT = SYSDATE 
		  WHERE ITEM_CD = IV_ITEM_CD;
	END IF;

	SELECT REG_DT INTO V_REG_DT
	  FROM TB_ITEM 
	 WHERE ITEM_CD = IV_ITEM_CD;

	OV_REG_DT := V_REG_DT;
	COMMIT;
	EXCEPTION
		WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('예상치 못한 에러가 발생했습니다.');
		ROLLBACK;
	RETURN;
END PR_ITEM_MERGE;

테이블에 data를 추가하는 procedure문 만약 이미 있는 테이블이라면 이름만 update하는 procedure이다.

PROCEDURE 실행 문

#1. out변수가 있는 경우
DECLARE 
	V_REG_DT DATE;
BEGIN
	PR_ITEM_MERGE('A0006', '삼성 갤럭시', V_REG_DT);
	DBMS_OUTPUT.PUT_LINE('프로시저 결과 값' || V_REG_DT);
END;

#2. out변수가 없는 경우
CALL PR_ITEM_MERGE('A0006', '삼성 갤럭시`);

6. MERGE문

MERGE
	INTO TB_ITEM ITEM -- UPDATE OR INSERT 대상 테이블
USING DUAL    -- 비교군 테이블(비교할 테이블이 없을 경우 DUAL을 쓴다)
	ON (ITEM.ITEM_CD = 'A0005')
WHEN MATCHED THEN -- 조건에 일치하는게 존재한다면 UPDATE를 
	UPDATE 
		SET ITEM.ITEM_NM = '아이오닉7'
		,	UPD_DT = SYSDATE 
WHEN NOT MATCHED THEN -- 조건에 일치하는게 없다면 INSERT를 
	INSERT(ITEM_CD, ITEM_NM, REG_DT, UPD_DT)
	VALUES('A0005', '아이오닉7', SYSDATE, SYSDATE);
	
COMMIT;

7. GROUP BY 활용하기

example 1.
고객에게 설치 완료한 상품 수는 총 몇건인가?

select count(*) from tb_ord where ord_stat_cd = 'D';

example 2.
설치 완료 건 중 가장 최근에 등록된건과 가장 최초로 등록된 건의 일자는 각각 어떻게 되는가?

select max(reg_dt), min(reg_dt) from tb_ord where ord_stat_cd = 'D';

example 3.
그럼 모든 주문 건 중 설치 완료되지 않고 설치 취소 되거나 주문 접수된 건은 얼마나 되지?

select count(*) from tb_ord where ord_stat_cd in ('B', 'E');

example 4.
모든 주문 건 중 설치 완료디지 않고 설치 취소 되거나 주문 접수된 건은 각각 얼마나 되지?

SELECT ord_stat_cd, count(*) FROM tb_ord WHERE ord_stat_cd IN ('B', 'E')
GROUP BY ord_stat_cd;

결과물 그런데 이렇게 보여주면 사용자들이 B, E가 무엇인지 파악할 수 없다. 이럴때 보여주는것이 function이다.

SELECT fn_code_nm('001', ord_stat_cd), count(*) FROM tb_ord WHERE ord_stat_cd IN ('B', 'E')
GROUP BY ord_stat_cd;

고객에게 설치 완료된 건 중 가장 인기있는 상품부터 순서대로 리스트를 가져와 보겠나?

example 5.

SELECT ITEM_CD, COUNT(*) AS CNT FROM TB_ORD WHERE ORD_STAT_CD='D' GROUP BY ITEM_CD ORDER BY CNT DESC;

결과물 그런데 이렇게 보여주면 사용자들이 B, E가 무엇인지 파악할 수 없다. join문 사용!

SELECT item.ITEM_NM, COUNT(*) AS CNT FROM TB_ORD ord LEFT JOIN tb_item item ON ord.item_cd = item.item_cd WHERE ORD.ORD_STAT_CD='D' GROUP BY item.item_nm ORDER BY CNT DESC;

example 6.
1건 이하의 제품은 안봤으면 하네..

SELECT ITEM.ITEM_NM, COUNT(*) AS CNT FROM TB_ORD ORD LEFT JOIN TB_ITEM ITEM ON ORD.ITEM_CD = ITEM.ITEM_CD WHERE ORD.ORD_STAT_CD = 'D' GROUP BY ITEM.ITEM_NM HAVING 
COUNT(*) > 1 ORDER BY CNT DESC;

example 7.
어떤 고객이 우리 제품을 몇 건씩 쓰고 있는지도 알 수 있나?

SELECT CUST.CUST_NM,
       ITEM.ITEM_NM, 
       COUNT(*) AS CNT 
FROM TB_ORD ORD 
LEFT JOIN TB_ITEM ITEM ON ORD.ITEM_CD = ITEM.ITEM_CD 
LEFT JOIN TB_CUST CUST ON ORD.CUST_CD = CUST.CUST_CD 
WHERE ORD.ORD_STAT_CD = 'D'
GROUP BY CUST.CUST_NM, ITEM.ITEM_NM
ORDER BY CUST.CUST_NM;

example 8.
우리 공통 코드 테이블에서 공통 코드명 별로 해당 공통 코드에 속한 코드명들을 ',' 구분 지어 나열해 출력해 줄 수 있겠나?

SELECT CD_NM, LISTAGG(CD_NM1, ',')
	FROM TB_CD_MST
GROUP BY CD_NM

example 9.
우리 주문 건들 중에서 설치 상태 구분 없이 주문 들어온 모든 건을 제품명과 제품별 가장 처음으로 등록된 시간으로 간추려 보여 줄 수 있겠나?

SELECT ITEM_CD, MIN(REG_DT)
	FROM TB_ORD
 	GROUP BY ITEM_CD

example 10.
그 일자에 접수된 주문 번호는 각각 무엇인가?

SELECT ORD2.ITEM_CD, ORD2.REG_DT, ORD2.ORD_NO 
	FROM(SELECT ITEM_CD,
    MIN(REG_DT),
    FROM TB_ORD 
    GROUP BY ITEM_CD) ORD
    LEFT JOIN TB_ORD ORD2 ON ORD.ITEM_CD = ORD2.ITEM_CD
    AND ORD.REG_DT = ORD.REG_DT

이것의 코드 길이를 줄여줄 수 있는 함수로 keep dense_rank함수가 있다.

SELECT ITEM_CD
,MIN(REG_DT)
,MIN(ORD_NO) KEEP(DENSE_RANK FIRST ORDER BY REG_DT)
FROM TB_ORD
GROUP BY ITEM_CD
ORDER BY ITEM_CD;