[Database] HeidiSQL 함수_뷰_프로시저

 

 

 

 

 

 

 

 

프로그래밍 세계를 탐구합시다.

 

 

 

 

 

 

 

 

[  함수 생성  ]

 

-- 상품분류명을 추출하는 함수 생성하기

-- 함수 구문 시작
Delimiter //

-- 사용자 정의함수
CREATE FUNCTION defGetLprodNm(paramGu VARCHAR(10))
-- 리턴타입 정의
RETURNS VARCHAR(50)

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

 

 

 

 

[  조건  ]

 

-- 상품분류명을 추출하는 함수 생성하기
-- 생성한 함수 호출하여 처리하기

 

 

[  작성 코드  ]

 

SELECT Prod_name, 
		Prod_lgu, 
		defGetLprodNm(prod_lgu) AS lprod_nm
FROM prod;

 

 

[  결과  ]

 

 

 

 

 

 

 

 

[  함수 생성  ]

/*
남성/여성 추출하기
*/

Delimiter //

CREATE FUNCTION udfGetMenWomen(paramNum INT)
-- 리턴 타입 정의
RETURNS VARCHAR(4)
BEGIN
	-- 반환할 변수 선언
	DECLARE result VARCHAR(4);
	
	-- 남여 구분을 위한 조건문 처리
	if MOD(SUBSTRING(paramNum, 1, 1), 2) = 0 Then
		SET result = '여성';
	else
		SET result = '남성';
	END if;
	
	RETURN result;
END //
Delimiter ;

 

 

 

 

 

[  조건  ]

 

-- [문제]
-- 성별을 조회하는 함수 생성하기
-- 조회 컬럼 : 회원 이름, 성별 조회하기
-- 성별은 남성 or 여성으로 조회
-- 함수 이름 : udfGetMenWomen
-- 함수 구문 시작

 

 

[  작성 코드  ]

 

SELECT Mem_name,
		 udfGetMenWomen(Mem_regno1) AS 성별
FROM member;

 

 

[  결과  ]

 

 

 

 

 

 

[  함수 생성  ]

 

/*
신규주문번호 발급하기
*/

Delimiter //

CREATE FUNCTION udfGetNewCartNo ()
-- 리턴 타입 정의
RETURNS VARCHAR(13)
BEGIN
	-- 반환할 변수 선언
	DECLARE result VARCHAR(13);
	
	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 ;

 

 

 

 

[  조건  ]

 

-- [문제]
-- 오늘 새로운 주문이 발생했습니다.
-- 신규주문번호를 발급하는 함수를 생성해 주세요
-- 함수이름 : udfGetNewCartNo()

 

 

 

[  작성 코드  ]

 

SELECT udfGetNewCartNo();

 

 

[  결과  ]

 

 

 

 

 

 

[  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;

 

 

 

 

[  결과  ]

 

 

 

 

 

 

[  저장프로시저 (Stored Procedure; sp)  ]

 

 

-- 프로그램 영역이 아닌, DB자체에서 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
*/

 

 

[  view  ]

 

/*
모든 거래처별 매출금액의 총합 조회하기
 - view이름 : viewGetBuyerAll
*/

-- view

/* 구매내역이 없는 회원 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 ;

 

 

 

[  프로시저  ]

 

-- 프로시저
/* 구매내역이 없는 회원 프로시저 생성 */

Delimiter //

CREATE PROCEDURE spGetBuyerAll ()
Begin

	-- 조회할 Select문 생성
	SELECT *
	FROM viewgetbuyerall;

END //
Delimiter ;

 

 

 

 

 

[  작성 코드  ]

 

 

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;

SELECT buyer_id, prod_name
FROM viewgetbuyerall INNER JOIN prod
		ON(buyer_id = prod_buyer);

CALL spGetBuyerAll();

 

 

 

[  결과  ]