[Database][SQL] 사용자 정의 함수 / VIEW / Procedure / 저장 모듈

🎄사용자  정의 함수

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

 SELECT prod_name
 	, (select lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) AS lprod_nm
    	, prod_lgu
 FROM prod;

 

• 상품분류명을 추출하는 함수 생성하기
  - IF문으로 NULL값 있는지 확인
  - Delimiter 뒤에는 무조건 띄어쓰기
  - 문자열이 맞지 않아서 CHARACTER SET UTF8MB4 적용

 -- 함수 구문 시작
Delimiter //

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

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

-- 함수 기능 시작
BEGIN
	
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50) CHARACTER SET UTF8MB4;

	-- 상품분류명을 추출하는 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 ;

•  생성한 함수 호출하여 처리하기

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

 

위에서 서브쿼리를 사용하여 작성한 코드와 동일한 결과

찾은 행 74 개

 

 

 

 

 

 

 

 

 

사용자 함수 정의 문제

문제 1

• 회원이름, 성별 조회하기
• 성별은 남성, 여성으로 조회
• 함수 이름 : udfGetMenWomen
  
• udfGetMenWomen 함수 정의
  - 남성 / 여성 추출하기

Delimiter //

CREATE FUNCTION udfGetMenWomen(paramNum INT)

-- 리턴 타입 정의
RETURNS VARCHAR(4) CHARACTER SET UTF8
BEGIN
	-- 반환할 변수 선언
	DECLARE result VARCHAR(4) CHARACTER SET UTF8;
	
	-- 남여 구분을 위한 조건문 처리
	if MOD(SUBSTRING(paramNum, 1, 1), 2) = 0 THEN
		SET result = '여성';
	ELSE 
		SET result = '남성';
	END if;

	RETURN result;
	
END //

Delimiter ;

 

 

• 생성한 함수 호출하여 처리하기

SELECT mem_name
, udfGetMenWomen(mem_regno2) AS gender
FROM member;

---

문제 2

• 새로운 주문이 발생했을 때 신규주문번호를 발급하는 함수 생성하기
• 함수이름 : udfGetNewCartNo()
  
• udfGetNewCartNo 함수 정의

Delimiter //

CREATE FUNCTION udfGetNewCartNo(paramNum VARCHAR(50))

RETURNS VARCHAR(50) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(50) CHARACTER SET UTF8;
	
	if SUBSTRING(paramNum,1,8) = CURDATE() then	
		set result = paramNum + 1;
	ELSE
		SET result = concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'00001');
	END if;
	
	RETURN result;

END //

Delimiter ;

 

• 생성한 함수 호출하여 처리하기

SELECT udfGetNewCartNo(MAX(cart_no)) AS newnum
FROM cart;

 

• udfGetNewCartNo 함수 정의 - 강사님 

--
Delimiter //

CREATE FUNCTION udfGetNewCartNo()

RETURNS VARCHAR(13) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(13) CHARACTER SET UTF8;
	
	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 ;

 

• 생성한 함수 호출하여 처리하기 - 강사님
  

SELECT udfGetNewCartNo();

🎄View

•  VIEW : 가상테이블
• 자주 사용되거나, SQL 구문이 긴 경우 조회 목적으로만 사용
• 미리 객체화 시켜서 테이블 처럼 사용하는 방식
• 조회만 가능하며, 입력/수정/삭제가 되지 않는다.
• 입력/수정/삭제가 되는 경우도 있지만, VIEW 사용 목적에 맞지 않는다.
  
• 구매 내역이 없는 회원 조회하기
  - 조회 컬럼 : 회원아이디, 회원이름

SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member FROM cart);

• 구매내역이 없는 회원 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();

 

VIEW / 저장 프로시저 문제

문제 1

• 모든 거래처별 매출 금액의 총합 조회하기
• 단, 2005년도 주문내역
• 조회컬럼 : 거래처 코드, 거래처명, 매출금액의 총합
• 위 조회에 대한 select문을 view로 생성
• 프로시저에서 위에 생성한 view를 사용하여 call
• view이름 : viewGetBuyerAll
• 프로시저이름 : spGetBuyerAll
  
  
• viewGetBuyerAll VIEW 생성

Delimiter //

CREATE VIEW viewGetBuyerAll AS

	-- 조회할 select문 생성
	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 cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name//

Delimiter ;

 

• spGetBuyerAll 프로시저 생성

Delimiter //

CREATE procedure spGetBuyerAll()
begin

	-- 조회할 select문 생성
	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 cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name;
	
END //	

Delimiter ;

 

• VIEW / 프로시저 호출

SELECT *
FROM viewgetbuyerall;

CALL spGetBuyerAll();

 

 

🎄저장 모듈(Stored Module)

•  SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.
  
  
• Procedure와 Trigger의 차이점

Procedure	Trigger
CREATE Proceduer 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨