728x90
반응형
🎄사용자 정의 함수
- 상품테이블에서 아래 컬럼 조회하기
- 서브쿼리 사용 (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 실행 안됨 |
728x90
반응형
'Digital Boot > Database' 카테고리의 다른 글
| [Data Wrangling] 데이터 수집하기 (0) | 2023.11.28 |
|---|---|
| [Data Wrangling] Pandas판다스, 데이터 분석 / 조회 / 추가 / 필터링 (3) | 2023.11.28 |
| [Database][SQL] UNION / UNION ALL / EXISTS / UPDATE (2) | 2023.11.24 |
| [Database][SQL] Self Join / Outer Join (0) | 2023.11.24 |
| [Database][SQL] 데이터 조회 문제 만들어보기 (2) | 2023.11.23 |
