오늘은 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 조회하기
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
위와 같이 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
My SQL Study Note. Contribute to suy379/SQL-basic development by creating an account on GitHub.
github.com
'Skillset > SQL' 카테고리의 다른 글
| [MS-SQL] 항목별 비율값 집계하기 (CAST, ROUND, CASE WHEN 구문) (0) | 2022.06.13 |
|---|---|
| SQL CASE WHEN 구문 사용법 - 예제로 알아보기 (0) | 2022.06.07 |
| SQL 집합 연산자 - UNION, UNION ALL, INTERSECT, EXCEPT (0) | 2022.06.03 |
| SQL 윈도우 함수 예제 - 순위함수(RANK), 누적 집계함수 (0) | 2022.06.02 |
| SQL 그룹함수 예제 - WITH ROLLUP, WITH CUBE, GROUPING SETS, GROUPING (0) | 2022.05.31 |
