MariaDB 데이터베이스[database, DB] with rollup / view / procedure

1.  with rollup 

- 2개 이상의 컬럼을 이용하여 그룹을 짓는 경우.

- 그룹별 중간합계(집계함수에 대해서)를 표시.
- 마지막 행에는 전체합계가 표시됨.

 

 

* with rollup 예제

 

상품분류 전체에 대한 상품이름과 구매수량의 총 합 조회하기. 단, 구매 연도가 2005년
prod, cart테이블만 사용

SELECT LEFT(prod_id, 4) AS lgu,
       prod_name,
       SUM(cart_qty) AS total
FROM prod
		LEFT OUTER JOIN cart
		ON(prod_id = cart_prod AND SUBSTRING(cart_no,1,4) = '2005')
GROUP BY LEFT(prod_id,4), prod_name WITH ROLLUP;

 

 

 

 

 

 

 

 

---

2. view (가상테이블)

-  자주 사용되거나, SQL구문이 긴 경우, 조회 목적으로만 사용.
-  미리 객체화 시켜서 테이블처럼 사용하는 방식.
-  조회면 가능하며, 입력/수정/삭제 불가.
-  입력/수정/삭제가 되는 경우도 있지만, view 사용 목적에 맞지 않다.

 

* 장점

1. 특정 사용자에게 테이블 전체가 아닌 필요한 필드만을 보여줄 수 있다.

2. 복잡한 쿼리를 단순화해서 사용할 수 있다.

3. 쿼리를 재사용할 수 있다.

* 단점

1. 한 번 정의된 뷰는 변경할 수 없다.

2. 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가진다.

3. 자신만의 인덱스를 가질 수 없다.

 

* view 예제

 - 상품데이블에서 아래 컬럼 조회하기
 - 서브쿼리 사용 (join 사용 x)
 - 조회컬럼 : 상품명, 상품분류명, 상품분류코드

 

view 구문 만들기

-- 함수구문 시작
Delimiter //

-- 사용자 정의 함수
CREATE FUNCTION defGetLprodNm(paramGu VARCHAR(10))

-- 리턴타입 정의
RETURNS VARCHAR(50)

-- 함수 기능 시작
BEGIN
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50);
	
	-- 상품분류명을 추출하는 sql 구문 작성
	SELECT lprod_nm INTO result
	FROM lproddefGetLprodNmdefGetLprodNm
	WHERE lprod_gu = paramGu;
	
	-- 반환하기
	RETURN result;
	
-- 함수 기능 종료
END //
-- 함수전체 구문 종료
Delimiter ;

 

F9으로 실행하면 다음과 같이 함수가 만들어졌다.

 

 

SELECT  prod_name, prod_lgu, defGetLprodNm(prod_lgu) AS lprod_nm
FROM prod;

 

 

 

만들어진 함수를 다음과 같이 

그냥 컬럼쓰듯이 써준 뒤 파라미터에 들어갈 값을 정해주면 된다.

 

 

 

 

---

3. 저장프로시저(Stored Procedure; sp)

 - 프로그램 영역이 아닌, 테이블에서 SQL구문을 관리하고자 할 때 사용.
 - 데이터베이스에 SQL구문을 객체화하여 호출방식으로만 사용하는 방식

 

* Procedure 예제

구매내역이 없는 회원 Procedure 생성

Delimiter //
CREATE PROCEDURE spNoCarMember()
BEGIN 
	-- 조회할 select문 생성
	SELECT mem_id, mem_name
	FROM member
	WHERE mem_id NOT IN (SELECT cart_member
								FROM cart);
END //
Delimiter ;

 

view와 비슷하지만 조금 더 단순하다.

 

CALL spNoCarMember();

 

 

 

만들어둔 프로시저(procedure)는 CALL을 사용하여 불러온다.

 

 

 

 

 

---

예제)

모든 거래처별 매출금액의 총 합 조회하기. 단, 2005년도 주문내역
조회컬럼 : 거래처코드, 거래처면, 매출금액의 총합

위 조회에 대한 결과는 select문을 view로 생성
포로시저에서 view를 사용하여 call 한 데이터 조회

view이름 : viewGetBUyerAll
프로시저 이름 : spGetBuyerAll

 

* 기본 코드

SELECT buyer_id, buyer_name, SUM(nvl(cart_qty*prod_sale, 0)) AS total
FROM buyer
		LEFT OUTER JOIN prod
			ON(buyer_id = prod_buyer)
		LEFT OUTER JOIN cart
			ON(prod_id = cart_prod
			    AND LEFT(cart_no,4) = '2005')
GROUP BY buyer_id;

 

 

결과는 다음과 같다.

 이 코드를 바탕으로 

view와 procedure를 만들어보겠다.

 

 

 

 

* view 만들기

Delimiter //
CREATE VIEW viewGetBuyerAll AS
	SELECT buyer_id, buyer_name, SUM(nvl(cart_qty*prod_sale, 0)) AS total
	FROM buyer
	LEFT OUTER JOIN prod
	ON(buyer_id = prod_buyer)
	LEFT OUTER JOIN cart
	ON(prod_id = cart_prod
	    AND LEFT(cart_no,4) = '2005')
	GROUP BY buyer_id, buyer_name//
Delimiter ;

 

 

 

* view 실행

SELECT *
FROM viewGetBuyerAll;

 

 

위의 기본 코드의 결과와 같다.

다른곳에서 만들어둔 가상테이블을 가져와서 사용하면

복잡한 쿼리를 단순화해서 사용할 수 있으며, 쿼리를 재사용할 수 있다.

 

 

 

 

* procedur 만들기

Delimiter //
	CREATE procedure spGetBuyerAll()
	BEGIN
	SELECT *
	FROM viewGetBUyerAll;
	END //
Delimiter ;

 

 

 

* procedur 실행

CALL spGetBuyerAll();

 

 

 

결과적으로 세 개 모두 값은 똑같다.