[DB]데이터베이스(MariaDB) - 함수, 뷰(view), 프로시저(procedure)

 

데이터베이스 함수

 

• 중복을 줄이고 더 편하게 사용하도록 도와주는 함수를 만드는 방법을 알아보자.
• 이렇게 만든 사용자정의 함수를 UDF라고 한다.

 

 

함수 생성하기

새로운 쿼리 생성 후 코드 작성 → F9로 실행

1. Delimiter(구문 문자) :  문법의 끝을 나타내는 역할

2. 사용자 정의 함수 생성 (paramGu는 매개변수)

3. 리턴 타입 정의

4. 함수 기능 시작과 끝 : BEGIN - END //

5. 반환할 값을 저장할 변수 선언 (리턴타입사이즈 = 변수사이즈) : DECLARE

6. 반환하기 : RETURN

※ ' // ' 뒤에는 아무 것도 있으면 안됨!!(스페이스바도 조심)

※ 스트링 타입이 UTF8이 아니면 타입정의 뒤에 ' CHARACTER Set UTF8MB4 ' 작성!

※ 조회한 결과를 result에 넣기 : INTO result

 

-- 함수 구문 시작

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 ;

 

---

문제1

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

 

<SQL 구문>

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

 

<UDF 함수 생성>

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

-- 함수 구문 시작
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, prod_lgu,
               udfGetLprodNm(prod_lgu) AS lprod_nm
FROM prod;

 

 

문제2

성별을 조회하는 함수 생성하기
- 조회컬럼 : 회원이름, 성별 조회하기
- 성별은 남성 or 여성으로 조회
- 함수 이름 : udfGetMenWomen

 

<UDF 함수 생성>

-- 함수 구문 시작
Delimiter //

-- 사용자 정의 함수 
CREATE FUNCTION udfGetMenWomen(paramNum INT)
-- 리턴타입 정의
RETURNS VARCHAR(4)  CHARACTER Set UTF8MB4

-- 함수 기능 시작
BEGIN
-- 변수 선언
DECLARE result VARCHAR(4) CHARACTER Set UTF8MB4;
-- 남여 구분을 위한 조건문 처리
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;

 

문제3

오늘 새로운 주문이 발생했습니다.
- 신규주문번호를 발급하는 함수를 생성하기
- 함수 이름 : udfGetNewCartNo

 

<UDF 함수 생성>

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 ;

 

<함수 적용하기>

SELECT udfGetNewCartNo();

 

 

---

 

가상테이블

가상테이블 - View

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

- View 사용법 : 테이블과 동일

 

-- 함수 구문 시작

Delimiter //

-- View 함수생성

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구문을 객체화하여 호출방식으로만 사용하는 방식
- 프로시저 호출하기 : CALL 프로시저명();
- 좋은 이유 : SELECT문(클라이언트가 요청보낸)을 미리 메모리 상에 올려놓는 역할을 해주므로 속도가 빠르다. 

 

-- 함수 구문 시작

Delimiter //

-- 프로시저 함수생성

CREATE Procedure soNoCartMember()

BEGIN

 

   -- 조회할 SELECT 구문작성

    SELECT mem_id, mem_name

     FROM member

     WHERE mem_id NOT IN (SELECT cart_member FROM cart);

 

END //

-- 함수 전체 구문 종료

Delimiter ;

 

<프로시저 사용하기>

CALL soNoCartMember();

 

---

문제4

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

- 위 조회에 대한 SELECT문을 view로 생성
- 프로시저에서 위에 생성한 view를 사용하여 call하여 데이터 조회

- view 이름 : viewGetBuyerAll
- procedure 이름 : spGetBuyerAll

 

< select 구문 >

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;

 

< View 함수>

Delimiter // 

CREATE VIEW viewGetBuyerAll AS

     -- 조회할 select문 생성
     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 ;

 

< Procedure 함수>

Delimiter // 

CREATE PROCEDURE spGetBuyerAll()
BEGIN


      -- 조회할 select문 생성
      SELECT *
      FROM viewGetBuyerAll;

END //
Delimiter ;

 

< 실행 결과 >

CALL spGetBuyerAll();