잡지식창고

저장 프로시저를 사용하면 여러 SQL문장을 하나의 프로시저로 저장할 수 있고, SQL 문장과 함께 파라미터를 사용할 수 있게 된다.

저장 프로시저를 사용하는 법에 대해 간략히 알아보자.

저장 프로시저 생성과 실행

저장 프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 실행해야한다.

기본적인 형태는 

CREATE PROCEDURE (프로시저이름)
AS (파라미터 선언)
BEGIN
(SQL 문장)
END

이다.

위 예시는 CustID 파라미터를 받아 Customers 테이블에서 해당 ID를 가지고 있는 행을 출력하게 하는 프로시저이다.

프로시저를 실행하려면 위의 EXEC을 사용한다.

파라미터가 존재하는경우 명시해주고, 없는 경우에는 쓰지 않으면 된다.

저장 프로시저의 수정/삭제

이미 저장되어 있는 프로시저를 수정하려면 ALTER PROCEDURE를 사용하면 된다.

ALTER PROCEDURE를 사용하는것 이외엔 처음 생성할떄와 같다. 

프로시저를 삭제할때엔 DROP PROCEDURE를 사용한다.

저장 프로시저가 함수와 다른 점은 크게 두가지가 있다.

1. 저장 프로시저는 출력 파라미터의 개수에 제한이 없다. 반면 함수는 항상 하나의 출력 파라미터를 갖는다.

2. 저장 프로시저는 콜링 프로그램에 의해 실행된다(SELECT 문장에서 직접 부를 수 없다). 반면, 함수는 문장에서 호출할 수 있다. 

서브쿼리는 하나의 SQL문에 포함되어 있는 또 다른 SQL문을 말한다.

서브쿼리는 SELECT 문장의 어디든 들어가도 상관 없다. 하지만 어디에 들어가느냐에 따라 사용법이 조금씩 달라진다. 

데이터 소스(FROM)

위 예시는 데이터 소스(FROM 절) 부분에 적용된 예시이다.

서브쿼리가 FROM절의 일부로 명시되면 새로운 데이터 소스가 생성된다. 이는 뷰의 역할과 비슷한데, 뷰는 데이터베이스에 영구히 저장되고, 서브쿼리는 그렇지 않다는 차이점이 존재한다.

위 문장에서 서브쿼리는 FROM절 다음에 나타나는 () 소괄호로 둘러싸인 SELECT 문장이다.

조인해줄 테이블을 서브쿼리로 그 자리에서 생성해준것을 볼 수 있다.

선택 기준(WHERE, HAVING)

서브쿼리는 선택 기준(WHERE, HAVING)에도 적용될 수 있다.

해당 문장에서는 서브쿼리로 IN에 들어갈 조건을 지정해주었다.

서브쿼리가 Cash 타입을 가진 UserID를 반환하고, 반환된 값을 WHERE절에서 조건으로 사용하는 예시이다.

상관 서브쿼리

위에서 본 두 예시는 모두 비상관 서브쿼리였다.

이는 서브쿼리와 쿼리가 연관되어있지 않아 서로 독립적임을 의미한다.

이번에 볼 예시는 상관 서브쿼리이다. 비상관 서브쿼리와는 반대로 서브쿼리와 쿼리가 연결되어있는 경우이다.

위 예시가 상관 서브쿼리인 이유는 마지막 문장의 User.UserID 때문이다.

만약 위의 서브쿼리를 독립적으로 실행하려고 시도하면 에러가 출력될것이다. User.UserID가 서브쿼리의 구문 안에 없기 때문이다.

EXISTS 연산자

EXISTS 연산자는 상관 서브쿼리에 데이터가 있는지 없는지 알 수 있게 해준다.

서브쿼리에서 UserID가 일치하는 열을 반환하고, 반환된 열을 출력했다.

계산된 열 생성

서브쿼리로 계산된 열을 생성할 수 있다.

Number of Transactions이라는 열을 서브쿼리를 통해 생성한 예시이다.

해당 열은 유저마다 실행된 거래 수 나타내는 열이다. 해당 계산을 서브쿼리를 통해 실행한 후, 그대로 그 결과를 열로 생성할 수 있다. 

공통 테이블 식

공통 테이블 식은 서브쿼리는 미리 서술하는것이다.

이에 사용되는 키워드는 WITH이다. WITH 키워드 뒤에 서브쿼리의 별칭을 지정하고, AS 뒤에 서브쿼리를 작성한다.

공통 테이블 식을 사용하면 서브쿼리의 세부사항이 주 쿼리 앞에 독립체로 분리되어있기 때문에 주 쿼리의 가독성이 높아진다. 

뷰(view)는 데이터베이스에 저장된 SELECT문장이다.

뷰를 통해서 이미 데이터베이스에 있는 데이터에 대해 가상의 뷰를 만들 수 있다.

뷰는 SELECT 문장을 저장한것이기 때문에 뷰가 참조한 테이블의 데이터가 바뀌면 뷰가 출력하는 데이터도 함께 바뀐다.

따라 뷰를 사용하면 데이터베이스가 갱신될 때마다 함께 갱신되는 동적인 테이블을 만들 수 있다.

뷰를 사용할때는 지정하려는 열 이름 앞뒤로 []를 붙여서 사용한다. 

뷰를 불러올 때에는 create할때 지정한 이름으로 똑같이 SELECT *FROM (view name)으로 불러오면 된다.

뷰를 수정하고싶다면 CREATE를 ALTER로 바꾸어 실행해주면 된다. 그러면 저장된 SELECT문장이 변경된다.

뷰를 삭제하려면 DROP VIEW (Viewname)을 사용하면 된다.

뷰의 장점으로는 다음이 있다.

1. 복잡성을 줄여준다.
2. 재사용 가능성을 증가시킨다.
3. 데이터를 적절하게 포맷한다.
4. 계산된 열을 생성할 수 있다.
5. 열의 이름을 다시 명명하는데에 사용할 수 있다.
6. 데이터 하위 집합을 생성할 수 있다.
7. 보안 제약을 강화하는 데 사용될 수 있다.

보안 제약의 경우 보안상 사용자에게 데이터를 제공하는데에 있어서 best의 케이스는 api 형태로 제공하는 것이다. 하지만 사용자가 계정을 가지는 경우, 특정 데이터에 접근을 제한해야 하는 경우가 있다. 이럴 때 해당 데이터 테이블에 접근을 제한하면서, 해당 테이블에서 제공해도 되는 데이터를 가진 뷰에 대한 접근을 허가하는것으로 이 문제를 해결할 수 있다.

뷰를 사용하면서 알아야하는 점은 뷰는 disc에 저장되지 않고 메모리에 체류한다는 점이다. 뷰를 생성할때마다 차지하는 메모리가 점점 누적된다.  

자체 조인은 자기 자신을 참조해 테이블을 조인하게 해준다.

위 예시를 보면 첫번째 열에는 직원 ID, 세번째 열에는 해당 열의 매니저의 ID를 가지고 있다.

자체 조인을 통해 해당 테이블에서 직원과 매니저의 관계를 명시하는 테이블을 만들어낼 수 있다.

자체 조인을 시행할 때 내부조인과 외부조인을 모두 사용할 수 있다. 자신이 원하는 테이블의 형태를 고려해 선정하면 된다.   

교차 조인은 외부 조인도 아니고 내부 조인도 아니다.

교차 조인은 테이블간의 관계를 나타내지 않고 조인하는 방법이다.

교차조인은 테이블들에게서 나올 수 있는 행의 모든 조합을 다 만들어낼 수 있다. 이를 Cartesian product라고 부른다.

 위 예시처럼 3가지 size와 4가지 color의 조합으로 12개 행을 가진 테이블을 만들 수 있다.

 또한 교차조인은 위 예시처럼 원하는 행을 선택해서 보는 용도로도 사용할 수 있다.

외부 조인의 유형으로는 왼쪽 조인(LEFT JOIN), 오른쪽 조인(RIGHT JOIN), 전체 조인(FULL JOIN) 세가지가 있다.

LEFT/RIGHT JOIN

왼쪽 조인과 오른쪽 조인은 어느 테이블이 왼쪽으로 올지 순서를 제외하고는 본질적으로는 같은 논리를 가지고있다.

위 예시에서는 Customers 테이블을 기준으로 Orders와 Refunds 테이블이 조인된 형태이다.

이 경우에는 Customers가 Primary, Orders와 Refunds가 Secondary 테이블이 된다. 

Secondary테이블을 Primary 테이블에 조인시킬 때, Secondary테이블의 행과 매치되는 행이 없어도 Primary테이블의 모든 행들이 출력된다. 내부 조인에서는 두 테이블 간 매치되는 행이 없는 경우에는 해당 행이 아예 출력되지 않았었다. 

위 예시는 Right Join을 사용한 예시이다.

Primary/Secondary관계가 반대로 된것 외에는 Left Join과 결과물이 완전히 같다.

떄문에 Left Join을 활용할 줄 안다면 Right Join은 사용하지 않아도 무방하다.

FULL JOIN

FULL JOIN(전체 조인)에서는 Left/Right Join과는 다르게 Primary가 존재하지 않고 양 테이블이 모두 Secondary이다. 

따라서 테이블_1과 테이블_2 사이에 서로 매칭되는 행이 없더라도 서로의 모든 행을 보여줘야 한다.

위 예시에서 확인할 수 있는것처럼 서로 매칭되는 행이 없더라도 해당 행이 출력된다.