SQL 효율화 및 자동화 명령어 - VIEW & PROCEDURE

오늘은 SQL을 아주 유용하게 활용할 수 있는, 효율화 & 자동화를 할 수 있는 명령어에 대해 정리한다.

바로 VIEW(뷰)와 PROCEDURE(프로시저)로, 둘 다 자주 사용하는 SQL 명령어를 저장하는 역할을 한다.

VIEW는 SQL 연산을 하여 만든 테이블을 새로운 가상 테이블로 저장하고,

PROCEDURE는 '매개변수' 기능을 활용해 자동화한다. 

별로 어렵지 않으니 명령어를 익혀서 편리하게 쿼리를 짜보자 😀

 

✔Table of Contents

 

1. VIEW : 새로운 가상 테이블로 저장하기

• 생성: CREATE VIEW [TABLE명] AS ~
• 조회: 일반 테이블 조회와 같음
• 수정: ALTER VIEW [TABLE명] AS ~
• 삭제: DROP VIEW [TABLE명]

오늘의 예제는 이 테이블이다. 다음 쿼리를 통해 테이블을 만들었다고 하자.

 

USE EDU

SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
FROM [ORDER] A
LEFT JOIN [MEMBER] B 
ON A.mem_no = B.mem_no

이렇게 결과가 뽑혔다.

다음에 또 이 테이블을 활용하고자 할 때, 불편하게도 계속 저 쿼리를 쳐서 결과를 뽑아내야 한다.

이런 비효율적인 과정을 한 번에 할 수 있는 명령어가 바로 VIEW이다.

[SY_TABLE]이라는 이름으로 VIEW 테이블을 생성해보자.

 

VIEW 생성하기

CREATE VIEW [SY_TABLE]
AS
SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
FROM [ORDER] A
LEFT JOIN [MEMBER] B 
ON A.mem_no = B.mem_no

 

CREATE VIEW 라는 명령어를 통해 VIEW를 생성했다. 어디에 있는지를 알아보려면 MS-SQL 좌측을 살펴보자.

[EDU] 데이터베이스 - Views에 들어가면 만들어진 [SY_TABLE]을 볼 수 있다.

 

VIEW 테이블 생성

VIEW 조회하기

SELECT *
FROM [SY_TABLE]

 

VIEW 조회는 아주 쉽다. 일반적인 테이블을 조회할 때와 똑같이 SELECT문을 사용해 조회한다.

 

VIEW 수정하기

너무 데이터가 많은 것 같아서, [SY_TABLE]에 채널코드(channel_code)가 1인 것만 뽑으려고 한다. 

이 때는 ALTER VIEW 명령어를 사용해 수정하면 된다.

 

-- VIEW 수정
-- 너무 데이터가 많은거 같아서, 채널코드가 1인 것만 저장

ALTER VIEW [SY_TABLE]
AS
SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no
WHERE A.channel_code = 1

 

잘 반영되었는지를 조회해보자.

 

--다시확인
SELECT * FROM [SY_TABLE]

channel_code가 모두 1인 값 2,727개만 조회가 된다. 수정이 잘 되었다.

 

VIEW 삭제하기

만들었던 VIEW를 삭제하려면 DROP VIEW 명령어를 사용한다.

 

-- VIEW 삭제
DROP VIEW [SY_TABLE]

 

다시 MS-SQL의 좌측에서 Views를 살펴보면 [SY_TABLE]이 사라져 있는 것을 확인할 수 있다.

 

---

이렇게, 앞으로도 많이 사용할 것 같은 테이블을 만들었다면 VIEW로 만들어 두면 유용하게 활용할 수 있다.

하지만 VIEW는 "가상 테이블"이기 때문에, 데이터베이스 내 'Table'이 아니라 'Views'에 저장된다는 것을 참고하자! 

(그래도 기본적인 Table의 요건은 만족해야 한다. 열 이름이 중복되면 안 된다)

 

만일 "진짜 테이블"로 저장하고 싶다면 어떻게 해야 할까?

-> INTO 명령어를 사용한다.

 

SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
INTO [EXAMPLE]
FROM [ORDER] A
LEFT JOIN [MEMBER] B 
ON A.mem_no = B.mem_no

TABLE 생성

 

위와 같이 FROM절 바로 앞에 INTO 명령어를 사용하면 위처럼 Tables에 [EXAMPLE] 테이블이 생성된다.

 

---

2. PROCEDURE : 매개변수로 자동화하기 

• 생성: CREATE PROCEDURE [TABLE명] (@매개변수) AS ~
• 실행: EXEC [TABLE명] 값
• 수정: ALTER PROCEDURE [TABLE명] (@매개변수) AS ~
• 삭제: DROP PROCEDURE [TABLE명]

프로시저(PROCEDURE)는 아주 유용하다. 매개변수로 설정을 해놓으면 자동화가 가능하기 때문이다.

예를 들어, 자주 사용하는 테이블이 있다고 하자. 이 테이블에서 성별이 여성인 것만 뽑는다거나, 나이가 20대인 것만 뽑고자 한다면 쿼리 1줄만으로 이 작업이 가능해진다. 

여기에선 앞서 JOIN으로 만든 테이블을 그대로 사용하고, 특정 연령대(ageband) 입력 시 이에 해당하는 값만 뽑히도록 프로시저를 만들어보자.

 

프로시저 생성하기

-- 생성
CREATE PROCEDURE [SY_PRO] 
(@ageband AS INT)
AS 
SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no
WHERE B.ageband = @ageband

 

프로시저 생성은 위처럼 CREATE PROCEDURE 명령어를 사용한다.

테이블명은 [SY_PRO]로 하고, ageband를 매개변수로 하였다 (인풋 값은 INT(숫자)이다.)

그럼 연령대(ageband)가 40대인 데이터만 뽑고자 할 때 얼마나 이게 간단해지는지 볼까?

 

프로시저 실행하기

EXEC [SY_PRO] 40

 

쿼리 한줄로 40대만 뽑혀나왔다. 정말 편리하지 않은가? :)

 

프로시저 수정하기

이번에는 연령대(ageband)뿐만 아니라, 다른 변수도 자동화하고 싶다.

성별(gender), 주문일자의 월(order_date의 month)도 매개변수로 만들어보자.

 

-- 수정 
ALTER PROCEDURE [SY_PRO]
(@ageband AS INT,
@gender AS VARCHAR(20),
@month_date AS INT)
AS
SELECT A.*,
	B.gender,
	B.ageband,
	B.join_date
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no
WHERE B.ageband = @ageband 
AND B.gender = @gender
AND MONTH(order_date) = @month_date

 

수정은 ALTER PROCEDURE 명령어를 사용한다.

새로운 매개변수 값도 @ageband, @gender, @month_date로 만들었다. 그리고 이 값을 필터링하기 위해 WHERE절의 내용도 추가한다.

 

수정이 완료되었으니 다시 조회를 해보자.

[SY_PRO] 테이블에서, 1월달에 구매한 30대 남성의 주문내역만 보고자 한다면 다음을 입력한다.

 

-- 다시 조회
EXEC [SY_PRO] 30, 'man', 1

결과를 확인해보자.

 

프로시저 삭제하기

DROP PROCEDURE [SY_PRO]

 

삭제는 간단히 DROP PROCEDURE 명령어를 사용하면 된다.

 

---

이렇게 해서 효율적인 SQL 쿼리 작성을 위한, VIEW와 PROCEDURE 기능을 살펴보았다. 

예제 코드는 여기서 확인하시면 됩니다 :)

GitHub - suy379/SQL-basic: My SQL Study Note