데이터베이스 함수
- 중복을 줄이고 더 편하게 사용하도록 도와주는 함수를 만드는 방법을 알아보자.
- 이렇게 만든 사용자정의 함수를 UDF라고 한다.
함수 생성하기
새로운 쿼리 생성 후 코드 작성 → F9로 실행
1. Delimiter(구문 문자) : 문법의 끝을 나타내는 역할
2. 사용자 정의 함수 생성 (paramGu는 매개변수)
3. 리턴 타입 정의
4. 함수 기능 시작과 끝 : BEGIN - END //
5. 반환할 값을 저장할 변수 선언 (리턴타입사이즈 = 변수사이즈) : DECLARE
6. 반환하기 : RETURN
※ ' // ' 뒤에는 아무 것도 있으면 안됨!!(스페이스바도 조심)
※ 스트링 타입이 UTF8이 아니면 타입정의 뒤에 ' CHARACTER Set UTF8MB4 ' 작성!
※ 조회한 결과를 result에 넣기 : INTO result
-- 함수 구문 시작
문제1
상품테이블에서 아래 컬럼 조회하기
- 서브쿼리 사용(join 사용하지 않기)
- 조회컬럼 : 상품명, 상품분류명, 상품분류코드
<SQL 구문>
(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 ;
<함수 적용하기>
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 ;
<함수 적용하기>
udfGetMenWomen(mem_regno2) AS gender
FROM member;
문제3
오늘 새로운 주문이 발생했습니다.
- 신규주문번호를 발급하는 함수를 생성하기
- 함수 이름 : udfGetNewCartNo
<UDF 함수 생성>
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 ;
<함수 적용하기>
가상테이블
가상테이블 - View
- 자주 사용되거나, SQL구문이 긴 경우 조회 목적으로만 사용
- 미리 객체화 시켜서 테이블 처리 사용하는 방식
- 조회만 가능하며, 입력/수정/삭제가 되지 않는다.
- 입력/수정/삭제가 되는 경우도 있지만, view 사용목적에 맞지 않습니다.
- View 사용법 : 테이블과 동일
-- 함수 구문 시작
<View 사용하기>
FROM viewNoCartMember;
프로시저
저장프로시저 - Stored Procedure ; sp
- 프로그램 영역이 아닌, DB자체에서 SQL구문을 관리하고자 할 때 사용
- 데이터베이스에 SQL구문을 객체화하여 호출방식으로만 사용하는 방식
- 프로시저 호출하기 : CALL 프로시저명();
- 좋은 이유 : SELECT문(클라이언트가 요청보낸)을 미리 메모리 상에 올려놓는 역할을 해주므로 속도가 빠르다.
-- 함수 구문 시작
<프로시저 사용하기>
문제4
모든 거래처별 매출금액의 총합 조회하기
- 단, 2005년도 주문내역
- 조회컬럼 : 거래처코드, 거래처명, 매출금액의 총합
- 위 조회에 대한 SELECT문을 view로 생성
- 프로시저에서 위에 생성한 view를 사용하여 call하여 데이터 조회
- view 이름 : viewGetBuyerAll
- procedure 이름 : spGetBuyerAll
< select 구문 >
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 함수>
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 함수>
CREATE PROCEDURE spGetBuyerAll()
BEGIN
-- 조회할 select문 생성
SELECT *
FROM viewGetBuyerAll;
END //
Delimiter ;
< 실행 결과 >
'Back-End > 데이터베이스' 카테고리의 다른 글
[DB] 데이터 수집하기 (0) | 2023.11.28 |
---|---|
[DB] 데이터 전처리 기초 - 판다스(pandas) (6) | 2023.11.28 |
[DB]데이터베이스 그룹함수 - WITH ROLLUP, CUBE, GROUPING SETS (5) | 2023.11.27 |
[DB] 데이터베이스 - self Join, outer Join, union, exists (1) | 2023.11.24 |
[DB]데이터베이스 명령어5 (MariaDB) - 조인(Join), 가상테이블(inline view) (8) | 2023.11.23 |