[DB] MySQL - Stored Program

aseol·2023년 8월 20일
dbmysql
0

MySQL

목록 보기
16/18

Stored Program

내장 프로그램, 프로그램 내장 방식, 저장 프로그램

스토어드 프로그램이란 MySQL 안에서 프로그래밍 언어와 같은 기능을 제공하는 것을 통틀어 말한다. 복잡한 쿼리를 하나로 묶어서 이름을 지정한 후에, 간단히 그것의 이름만을 호출하면 쿼리가 실행되기 때문에 편리하다.

특징

  1. MySQL 성능 향상
    서버로 긴 쿼리의 모든 텍스트를 전송하는 게 아니라, (쿼리를 서버에 스토어드 프로시저로 생성해 놓았다면) 프로시저의 이름 및 매개 변수 등만 전송하면 되기 때문에 네트워크 부하를 어느 정도 줄일 수 있다.

  2. 유지 및 관리 용이
    C#이나 Java 등의 클라이언트 응용프로그램에서 직접 SQL문을 작성하지 않고 스토어드 프로시저 이름만 호출하도록 설정함으로써, 데이터베이스에서 관련된 스토어드 프로시저의 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다.

  3. 보안 강화
    사용자별로 테이블에 대한 접근 권한을 주는 게 아니라 스토어드 프로시저에만 접근이 가능하도록 함으로써 좀 더 보안을 강화할 수 있다.

 ➕ 뷰(VIEW)도 스토어드 프로시저와 같이 보안을 강화할 수 있다.

✔️Stored Procedure

스토어드 프로시저 (저장 프로시저)
한마디로 쿼리문의 집합으로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다. 자주 사용되는 일반적인 쿼리를 사용하기보다는 이것을 모듈화시켜서 필요할 때마다 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

생성

DELIMITER $$ ⏩ 구분자를 $$ 로 변경
CREATE PROCEDURE 프로시저_이름 ( IN 또는 OUT 파라미터 )
⠀⠀⠀BEGIN

⠀⠀⠀~ (SQL 프로그래밍 코딩) ;

⠀⠀⠀END $$
DELIMITER ;⏩ 구분자를 다시 ; 으로 변경

❗ 프로시저 이름 : 보통 sp_ 또는 proc_ 형태로 작성한다.

세미콜론 잊지 말고 찍어 주기 !!

💡 IN / OUT Parameter

IN : 입력 매개변수

IN 입력매개변수명 데이터형식
➡️ (IN 키워드는 생략 가능)

OUT : 출력 매개변수
스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해서 얻을 수 있다.

OUT 출력매개변수명 데이터형식

호출

🔹 입력 매개변수가 있는 프로시저 실행 🔽

CALL 프로시저_이름 ( 파라미터 ) ;


🔹 출력 매개변수가 있는 프로시저 실행 🔽

CALL 프로시저_이름 ( @매개변수명) ;
💡 출력매개변수에 값을 대입하기 위해서는 주로 SELECT INTO를 사용한다 ( SELECT의 결괏값을 저장 )

* ROUND (숫자, m) : 숫자의 반올림 / m은 반올림 기준 자릿수

이렇게 변수에 담긴 결과는 후에 또 활용할 수 있다 ⬇️

* CONCAT( ①,② ) : 문자열 ①과 ② 연결

삭제

DROP PROCEDURE 프로시저_이름 ;

조건문과 반복문 사용하기

  • IF THEN

    IF 조건 THEN
    ⠀⠀⠀ ⋯
    ELSEIF 조건 THEN
    ⠀⠀⠀ ⋯
    ELSE
    ⠀⠀⠀ ⋯
    END IF ;

  • CASE문

    CASE
    WHEN 조건 THEN ⠀⋯
    WHEN 조건 THEN ⠀⋯
    ELSE
    END CASE ;

  • WHILE 반복문

❔ 회원의 아이디를 입력받아 회원 탈퇴를 진행하는 프로시저를 작성하세요 단, 관리자는 탈퇴 불가능 🔽
IF THEN 조건식 사용
연산자 (조건 사용)
JOIN 사용해서 DELETE ( ⬅ 데이터의 수정과 삭제는 신중해야 하므로 SELECT로 먼저 확인 후)

엔티티 관계도를 보면,

<판매자 회원>
order 테이블에서 상품코드에 외래키를 설정하여 
goods 테이블을 참조하고 있고,
goods 테이블에서 판매자 아이디에 외래키를 설정하여 
member 테이블을 참조하고 있다.

<구매자 회원>
order 테이블에서 주문자 아이디에 외래키를 설정하여
member 테이블을 참조하고 있다.

<판매자 회원 & 구매자 회원>
login 테이블에서 로그인 아이디에 외래키를 설정하여
member 테이블을 참조하고 있다. 

 ⭐ member 테이블의 데이터를 삭제하기 위해선 
 member 테이블을 참조하고 있는 다른 테이블에서 
 관련 데이터들을 삭제해 주는 것이 선행되어야 한다.

▶️ 판매자
∴ order  ➡️ goods ➡️ login ➡️ member 순서로 삭제

▶️ 구매자
member 테이블 정보 삭제하려면, 
login 테이블과 order 테이블의 데이터를 삭제해야 한다. 
∴ order ➡️ login ➡️ member 순서로 삭제

여기서 login과 member는 판매자 구매자 공통이므로 한번에 처리할 수 있다!

쿼리문 작성 🔽

DELIMITER $$
CREATE PROCEDURE proc_member_delete (IN memberId VARCHAR(20))
BEGIN
		DECLARE memberLv INT;
	SELECT 
		m.m_level INTO memberLv
	FROM
		tb_member AS m
	WHERE
		memberId = m.m_id
	IF (memberLv != 1) THEN
		IF (memberLv = 2) THEN
			DELETE
				o
			FROM
				tb_order AS o
			INNER JOIN
				tb_goods AS g
			ON o.o_g_code = g.g_code
			WHERE
				memberId = g.g_seller_id;

			DELETE
				g
			FROM
				tb_goods AS g
			WHERE
				memberId = g.g_seller_id;
		END IF;

		IF (memberId = 3) THEN
			DELETE
				o
			FROM
				tb_order AS o
			INNER JOIN
				tb_member AS m
			ON
				o.o_id = m.m_id
			WHERE
				memberId = m.m_id;
		END IF;

		DELETE
			l
		FROM
			tb_login AS l
		WHERE
			memberId = l.login_id;

		DELETE
			m
		FROM
			tb_member AS m
		WHERE
			memberId = m.m_id;

	END IF;

END $$
DELIMITER ;

✔️Stored Function

스토어드 함수 (사용자 정의 함수)
내장함수가 사용자에게 필요한 모든 함수를 제공할 수는 없기 때문에 필요에 의해 사용자가 직접 함수를 만들어 사용할 수 있다.

  • 파라미터로 IN 만 사용한다.
  • RETURNS 예약어를 통해 반환할 값의 데이터 형식을 지정하고, 본문 안에서 RETURN문으로 하나의 값을 반환해야 한다.
  • SELECT문 안에서 값을 제공하는 용도로 호출된다.

❗ 함수 사용 전 함수 생성 권한 허용해 주기

함수를 생성하려 하면 오류가 뜬다

⬇️
SET GLOBAL log_bin_trust_function_creators = 1;

작성 후 실행해 주면 함수가 잘 생성된다.

생성

DELIMITER $$
CREATE FUNCTION 스토어드함수이름 ( 파라미터 )
⠀⠀⠀ RETURNS 반환데이터타입
BEGIN
⠀⠀⠀ ~ ( 프로그래밍 코딩 )
⠀⠀⠀RETURN 반환값 ;
END $$
DELIMITER ;

:= 는 값을 대입한다는 의미이다 
(=로 작성해도 되지만 혼동될 수도 있기 때문에 :=로 사용했다)

호출

SELECT 스토어드함수이름 ( 파라미터 ) ;

❔ 출생년도를 입력하면 나이를 출력하는 함수를 작성

함수 실행 결과 🔽

삭제

DROP FUNCTION 스토어드함수이름 ;

💡 스토어드 프로시저 vs 스토어드 함수

Stored ProcedureStrored Function
용도여러 SQL문, 숫자 계산 등 다양한 용도계산을 통해 하나의 값을 반환하는 데 주로 사용
파라
미터		IN, OUT모두 입력파라미터로 사용
반환별도의 반환하는 구문❌
➡️ 꼭 필요하다면 여러 개의 OUT 파라미터를 사용해서 값을 반환할 수 있다.		RETURNS문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN문으로 하나의 값을 반환해야 한다.
호출CALL로 호출SELECT 문장 안에서 호출
S
E
L
E
C
T		SELECT문 사용 가능SELECT문 사용 불가 (SELECT INTO는 예외)
SELECT INTO 는 집합 결과를 반환하는 것이 아니므로 
예외적으로 스토어드 함수에서 사용할 수 있다.

✔️ 트리거

해당 테이블에 데이터가 변경되는 이벤트가 발생하면 자동으로 실행 ( ∴ 직접 실행 ❌ )

  • 트리거 이벤트 : INSERT, DELETE, UPDATE
  • 트리거 타임 [발동 시간] : BEFORE , AFTER

작성방식

생성 🔽

DELIMITER $$
CREATE TRIGGER 트리거이름, 트리거타임, 트리거이벤트
⠀⠀⠀ON
⠀⠀⠀테이블명 ( 트리거가 적용될 테이블 )
⠀⠀⠀FOR EACH ROW -- 각 행(ROW)마다 적용한다는 의미 ( 필수 작성 )
BEGIN
⠀⠀⠀~ ( 트리거 동작 시 실행될 SQL문 작성 ) ~
END $$
DELIMITER ;

1. 로그를 저장할 테이블 생성

  1. 트리거 생성

    💡 NEW / OLD : 트리거의 컨텍스트에서 현재 작업 중인 데이터와 이전 데이터에 접근하는 데 사용되는 가상적인 레코드. 트리거에서 변경 작업이 수행되면 임시로 사용되는 시스템 테이블로, 트리거 안에서만 사용할 수 있는 키워드이다.
  • OLD : 이전 값에 접근할 때 사용된다.
    ⠀⠀⠀⠀⠀⠀⏩ DELETE로 삭제된 데이터 / UPDATE로 바뀌기 전의 데이터
  • NEW : 새로운 값에 접근할 때 사용된다.
    ⠀⠀⠀⠀⠀⠀⏩ INSERT로 삽입된 데이터 / UPDATE로 바뀐 후의 데이터

    ∴ ➡ UPDATE는 NEW, OLD 테이블 둘 다 사용

  1. 이벤트 발생 ➡ 트리거 자동 실행 
이제 tb_member2에서 데이터를 update 하면 🔽

그 update 전 데이터가 tb_member2_backup에 쌓인다 🔽

삭제 🔽

DROP TRIGGER ;

DROP <TRIGGER IF EXISTS member2_delete_trigger;

종류

BEFORE 트리거 : 데이터가 삽입되기 전에 실행되는 트리거. 이벤트가 발생하기 전에 데이터 변경 전처리 작업을 수행할 수 있다.
AFTER 트리거 : 데이터가 삽입된 후에 실행되는 트리거. 이벤트가 발생한 후에 데이터 변경 후 처리 작업을 수행할 수 있다.
INSTEAD OF 트리거 : 데이터 삽입, 업데이트, 삭제 작업을 수행할 때 기본 동작 대신에 실행되는 트리거. 실제 데이터 변경 대신에 트리거에서 정의한 동작이 실행된다.

profile
aseol
이전 포스트

[DB] MySQL - GROUP CONCAT

다음 포스트

[DB] MySQL - Transaction

0개의 댓글