[SQL] SQL 스토어드 프로시저

in Class on SQL

스토어드 프로시저

SQL을 자동화 하지 않고 반복적으로 쓰기엔 상당핞 불편함과 한계가 있다. 스토어드 프로시저를 사용하게 되면 MySQL 안에서도 다른 프로그래밍 언어처럼 프로그램 로직 코딩이 가능하다.

SQL은 데이터베이스에서 사용되는 언어(language)이다. 그런데 SQL을 쓰다보면 다른 프로그래밍 언어 기능이 필요할 떄가 있다.

조건문이나 반복문 사용시 더 편리하고 빠른 결과를 낼 수 있는 것과 비슷하다.

MySQL의 스토어드 프로시저(Stored Procedure)는 SQL에 프로그래밍기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

20211214_213742

스토어드 프로시저 기본

스토어드 프로시저의 완전한 형식은 어렵게 보일수도 있지만 사용하는 형식은 간단하다.

스토어드 프로시저의 개념과 형식

스토어드 프로시저(저장 프로시저)란 MySQL에서 제공하는 프로그래밍 기능이다. C,자바,파이썬 등 프로그래밍과 차이가 있지만, MySQL 내부서 사용시 적절한 프로그래밍 기능을 제공해준다.

또한 스토어드 프로시저는 쿼리문의 집합으로 볼 수 있으며 어 동작을 일괄 처리하기 위한 용도로도 사용한다.

자주 사용하는 일반 쿼리를 반복하는 것 보다도 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출하면 훨씬 편리하게 MySQL을 운영할 수 있다.

(스토어드 프로시저도 데이터베이스 개체 중 하나다. 즉 테이블처럼 디비 내부에 저장된다.)

20211214_214420

1은 필수항목으로 프로시저를 묶어주는 기능을 한다.

DELIMITER의 의미는 구분자라는 의미다.

MYSQL에서 구분자는 기본적으로 세미콜론을 쓰는데, 스토어드 프로시저 안에 많은 SQL 끝에도 세미콜론을 쓴다. 문제는 세미콜론이 나오게 되었을 떄 이것이 SQL의 끝인지 스토어드 프로시저의 끝인지 모호할 수 있다. 그래서 구분자를 \(로 둬서\)가 나오기 전까진 스토어드 프로시저가 끝난것이 아니란 걸 표현하는 것이다.

2는 스토어드 프로시저 이름을 정해준다.

스토어드 프로시저를 만든 건 자판기를 만든 것과 같다.

20211214_214656

호출 방법은

CALL 스토어드_프로시저_이름()

생성할 떈 Create로 스토어드 프로시저 만들고 호출은 CALL로 실행(호출)한다.

USE market_db;
DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

CALL user_proc();

스토어드 프로시저 실습

매개변수 사용

스토어드 프로시저는 입력 매개변수를 지정할 수 있다. 자판기에 동전 넣는거라 생각하면 된다.

IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저를 실행하려면 다음같이 괄호 안에 값을 전달하면 된다.

CALL 프로시저_이름(전달 값)

마찬가지로 출력 매개변수를 통해 얻을 수도 있다.

Out 입력_매개변수_이름 데이터_형식

출력 매개변수 있는 스토어드 프로시저 실행은

CALL 프로시저_이름(@변수명);
SELECT @변수명;

으로 실행한다.


USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('에이핑크');
  • 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능이다.
  • 스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현한다.
  • 스토어드 프로시저를 작성한 후, CALL 문을 통해서 스토어드 프로시저를 호출한다.
  • 입력 매개변수는 스토어드 프로시저에 값을 전달한다. 형식은 IN을 앞에 붙인다.
  • 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려받는다. 형식은 OUT을 앞에 붙인다.
  • 동적 SQL은 다이나믹하게 SQL 생성후 실행한다.

스토어드 함수와 커서

스토어드 프로시저와 함께 SQL 프로그래밍 기능으로 사용되는 데이터베이스 개체로는 스토어드 함수와 커서가 있다. 잘 쓰면 SQL 기능을 더 강력하게 확장할 수 있다.

스토어드 함수는 MySQL에서 제공하는 내장함수 외에 직접 함수를 만드는 기능을 제공한다. 스토어드함수는 스토어드 프로시저와 모양이 비슷하지만 세부적으로 다르다. 용도가 다르며 returns예약어로 하나의 값을 반환해야 한다.

커서는 스토어드 프로시저 안에서 한 행씩 처리시 쓰는 프로그래밍 방식이다. 문법은 복잡해 보여도 형태가 대부분 비슷하게 고정되어 한번 익히면 다음엔 쉽게 사용이 가나ㅡㅇ하다.

SET GLOBAL log_bin_trust_function_creators = 1;	-- 변수 설정

USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';

커서는 첫 행부터 마지막 행 까지 하나씩 실행해간다.

20211214_221034

20211214_221118

  • MYSQL은 다양한 내장함수로 제공되지 않는 기능을 스토어드 함수로 만들어 사용이 가능하다.
  • 스토어드 함수는 RETURNS 예약어를 통해서 반환될 데이터의 형식을 미리 지정해 둬야한다.
  • 스토어드 함수의 매개변수는 모두 다 스토어드 함수로 값이 들어오는 입력 매개변수이다.
  • 커서는 한 행씩 처리되도록 하는 기능이다. DECLARE로 선언할 수 있으며 그 내용이 select문이다.
  • 커서는 행이 끝날때 까지 반복. 행의 끝을 판단하기위해 변수 endOfRow를 준비하고 Ture인지 체크하는 방식을 사용

자동 실행되는 트리거

트리거는 자동으로 수행해서 사용자가 추가작업을 잊어버리는 실수를 방지해준다.

만약 회사원이 퇴사하면 그 퇴사한 직원 내용을 백업하고 퇴사원 테이블에 옮겨야되는데 이를 수동으로 하면 백업하지 않고 삭제할 수 있다.

트리거는 이런 실수를 방지해준다. 직원테이블에서 사원 삭제시 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 있다.

트리거를 쓰면 데이터에 오류가 발생하는 것을 막을 수 있다.

이를 데이터의 무결성이라고 한다.

####트리거의 개요

트리거란 테이블에 Insert,update,또는 delete가 발생시 실행되는 코드

트리거는 테이블에서 DML문(insert,update,delete등의) 이벤트가 발생시 작동한다.

테이블에 미리 부착(attach)되는 프로그램 코드라 생각하면 된다.

보통 after트리거를 많이 쓴다(after,before트리거 2개가 있다.)

20211214_222052

트리거 활용

트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 쓸 수 있다.

트리거가 사용하는 임시 테이블에

테이블에 insert,update,delete 작업 수행되면 임시로 쓰는 테이블이 2개 있는데 그게 new, old이다. 이건 mysql이 알아서 생성하고 관리한다.

new 테이블은 insert문이 실행시 다음과 같이 작동한다.

20211214_222304

insert(새 값)로 테이블에 새 값이 들어간다. 하지만 사실 새 값은 테이블 가기 전 new에 잠깐 들어간다

(사실 new테이블은 많이 쓰지 않는다. 어차피 new테이블 간 값은 테이블에 들어가있으므로)

delete(예전값)은 old테이블과 연관있는데 old테이블은 delete문 실행시 다음과 같이 작동한다.

20211214_222448

예전값이 삭제되기 전 old테이블에 잠시 있다. 그래서 after delete트리거 만들어도 삭제 후에 old.열이름 형식으로 예전 값에 접근할 수 있었다.

마지막으로 update(새값,예전값)을 사용시 다음과 같이 new와 old테이블을 모두 사용한다.

20211214_222553

  • 트리거 는 테이블에서 DML문(insert,update,delete등)의 이벤트가 발생시 작동한다.
  • AFTER DELETE 는 DELETE문이 발생한 후 트리거를 작동하라는 의미
  • 트리거는 행 데이터가 삭제 , 수정되면 기존 데이터를 백업테이블에 저장하도록 할 ㅜ 있다.
  • 트리거에서 기존 데이터는 OLD테이블에, 새 데이터는 NEW 테이블에 잠시 저장된다. 두 테이블은 MYSQL이 내부적으로 관리한다.



© 2021.03. by yacho

Powered by github