(MariaDB) - Rollup / Udf / View / Procedure

 

1. ROLLUP

컬럼명1 , 컬럼명2  WITH ROLLUP

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

그룹별 중간 합계(집계함수에 대해서)를 표시

마지막행에는 전체합계가 표시됨

SELECT prod_lgu , prod_name , SUM(cart_qty)
FROM prod
	LEFT JOIN cart
	ON(prod_id = cart_prod AND left(cart_no,4) = '2005')
GROUP BY prod_lgu, prod_name WITH ROLLUP;

 

 

 

2. 사용자 정의 함수

 

사용자 정의 함수를 만들어서 사용하기 위해 새로운 쿼리를 열어 다음과 같은 함수를 작성한뒤 실행한다.

함수작성시 주의해야할 점은 Delimiter // 뒤에 공백(spacebar) 없도록 할 것 , 마지막 Delimiter 뒤에 공백 하나 두고 ; 넣기

실행과 동시에 defGetLprodNm이라는 함수가 만들어진다.

이때 paramGu에 들어가는 매개변수는 prod_lgu

Delimiter //

-- 사용자 정의함수
CREATE FUNCTION udfGetLprodNm(paramGu VARCHAR(10))
-- 리턴타입 정의
RETURNS VARCHAR(50)
-- 함수 기능 시작
BEGIN
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50);
	-- 상품분류명을 추출하는 sql구문 작성
	SELECT lprod_nm INTO result
	 FROM lprod 
	 WHERE lprod_gu = paramGu;
	 
	-- 조회 결과 있는지 확인
	if result IS NULL then
		SET result = 'null';
	END if;
	
	-- 반환하기
	RETURN result;
-- 함수 기능 종료
END //
-- 함수 전체 구문 종료
Delimiter ;

 

아래와 같이 함수를 이용해서 lprod_nm을 구할 수 있다.

매개변수를 함수 안에 넣어준다(prod_lgu)

-- 생성한 함수 호출하여 처리하기
SELECT prod_name , defGetLprodNm(prod_lgu) AS lprod_nm , prod_lgu
FROM prod;

 

[ 예제 ]

-- [문제]
/*
성별을 조회하는 함수 생성하기
조회컬럼 : 회원이름 , 성별
성별은 남성 or 여성으로 조회
함수 이름 : udfGetMenWomen
*/
SELECT mem_name , udfGetMenWomen(mem_regno2) AS mem_gender
FROM member;

 

함수 작성에서 SET을 통해 result에 여성이라는 집합을 만든다.

Delimiter //
CREATE FUNCTION udfGetMenWomen(paramNum INT)
RETURNS VARCHAR(4)
BEGIN
DECLARE result VARCHAR(4);
 	-- 조건문
	if MOD(left(paramNum,1) , 2) = 0 Then
		SET result = '여성';
	ELSE 
		SET result = '남성';
	END if;
	RETURN result;
END //
Delimiter ;

 

[ 문제 ]

 

 

Delimiter //
CREATE FUNCTION udfGetNewCartNo(paramNo VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE result VARCHAR(20);
	if left(paramNo,8)= CURDATE() then
		SET result = paramNo + 1;
	ELSE 
		SET result = concat(date_format(CURDATE(),'%Y%m%d'),'00001');		
	END if;
	RETURN result;
END //
Delimiter ;

 paramNo 라는 매개변수를 받아서 왼쪽부터 8자리의 값이 현재 날짜와 같으면 +1을 해주고 다르면 현재날짜에 '00001'을 붙여 내보내는 udfGetNewCartNo 함수를 작성하였다.

 

-- 강사님 풀이
Delimiter //
CREATE FUNCTION udfGetNewCartNo()
RETURNS VARCHAR(20)
BEGIN
DECLARE result VARCHAR(20);
	SELECT IF(MAX(cart_no) IS NOT NULL,
			 MAX(cart_no)+1 , 
			 concat(REPLACE(CURDATE(),'-',''),'00001')) INTO result 
	FROM cart
	WHERE SUBSTRING(cart_no , 1, 8) = CURDATE();
RETURN result;
END //
Delimiter ;

강사님 풀이에서는 매개변수를 따로 두지 않고 cart_no 전체를 받아 select 구문을 사용하여 max(cart_no)의 값을 통해 비교하여 신규 주문번호를 출력하도록 함수를 작성하였다.

-- [문제]
/*
오늘 새로운 주문이 발생했습니다.
신규주문번호를 발급하는 함수를 생성해주세요
함수이름 : udfGetNewCartNo()
*/
SELECT udfGetNewCartNo(Max(cart_no)) AS new_cart_no
FROM cart;

매개변수에 cart_no의 최대값(제일 최신주문번호)를 넣어주어 위의 함수를 통해 신규주문번호가 조회될 수 있도록 하였다.

 

 

3. VIEW (가상테이블)

 

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

 

/*구매내역이 없는 회원 view 생성*/

Delimiter //
CREATE VIEW viewNoCartMember AS 

	-- 조회할 Select문 생성
	SELECT mem_id , mem_name
	FROM member 
	WHERE mem_id NOT IN (SELECT cart_member FROM cart)//
	
Delimiter ;

 

View 사용법

SELECT *
FROM viewnocartmember;

 

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

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

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

 

호출하는 방법

CALL spNoCartMember;

 

 

[ 문제 ]

-- [문제]
/*
모든 거래처별 매출금액의 총합 조회하기
단 2005년도 주문내역
조회컬럼 : 거래처코드, 거래처명 , 매출금액의 총합
위 조회에 대한 select 문을 view로 생성
프로시저에서 위에 생성한 view를 사용하여 call하여 데이터 조회
View 이름 : viewGetBuyerAll
프로시저이름 : spGetBuyerAll
*/
SELECT buyer_id , buyer_name , SUM(nvl(prod_sale*cart_qty,0)) AS total
FROM buyer 
	LEFT JOIN prod 
	ON(buyer_id = prod_buyer)
	LEFT JOIN cart
	ON(prod_id = cart_prod
		AND left(cart_no,4) = '2005')
GROUP BY buyer_id;

VIEW와 PROCEDURE를 이용하지 않을때 문제 조건을 만족하는 코드를 먼저 작성한다.

 

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

VIEW 는 다음과 같이 위의 select문을 넣어 작성한다. 

 

Delimiter //
CREATE PROCEDURE spGetBuyerAll()
BEGIN
	SELECT * 
	FROM viewgetbuyerall;
END //
Delimiter ;

PROCEDURE는 VIEW를 사용하여 다음과 같이 작성한다.

 

CALL spGetBuyerAll;

프로시저를 호출하면 위의 코드와 같은 결과값이 나온다.