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

스터디/SQL

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

꽌 2024. 5. 6. 01:11

*해당 내용은 우재남 저자님의 <혼자 공부하는 SQL>을 보고 정리한 내용입니다.

스토어드 프로시저 기본

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

스토어드 프로시저란 MySQL에서 제공하는 프로그래밍 기능이다. 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로 사용한다.

스토어드 프로시저의 필수적인 형식은 다음과 같다.

스토어드 프로시저를 호출하는 형식은 다음과 같다.

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

스토어드 프로시저의 생성

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

CALL user_proc();

스토어드 프로시저의 삭제

DROP PROCEDURE user_proc;

스토어드 프로시저 실습

매개변수의 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다. 형식은 다음과 같다.

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

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

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

스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있다. 형식은 다음과 같다.

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

출력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

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

입력 매개변수의 활용

-입력 매개변수가 1개인 경우

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('에이핑크');

-입력 매개변수가 2개인 경우

DROP PROCEDURE IF EXISTS user_proc2;
DELIMITER $$
CREATE PROCEDURE user_proc2(
    IN userNumber INT, 
    IN userHeight INT  )
BEGIN
  SELECT * FROM member 
    WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;

CALL user_proc2(6, 165);

출력 매개변수의 활용

다음 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고, id 열의 최대값을 알아내는 기능을 한다.

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue CHAR(10),
    OUT outValue INT     )
BEGIN
  INSERT INTO noTable VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM noTable; 
END $$
DELIMITER ;

CALL문으로 실행하기 전, noTable의 구조를 확인하기 위해 DESC 문으로 테이블을 확인해보자.

DESC noTable;

오류가 난다. 그 이유는 noTable을 만든 적이 없기 때문이다. noTable 테이블을 만들어보자.

CREATE TABLE IF NOT EXISTS noTable(
    id INT AUTO_INCREMENT PRIMARY KEY, 
    txt CHAR(10)
);

이제 스토어드 프로시저를 호출하면 된다. 출력 매개변수의 위치에 @변수명 형태로 변수를 전달해주면 그 변수에 결과가 저장된다.

CALL user_proc3 ('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

SQL 프로그래밍의 활용

-조건문 IF~ELSE문 활용

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE debutYear INT; -- 변수 선언
    SELECT YEAR(debut_date) into debutYear FROM member
        WHERE mem_name = memName;
    IF (debutYear >= 2015) THEN
            SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
    ELSE
            SELECT '고참 가수네요. 그동안 수고하셨어요.'AS '메시지';
    END IF;
END $$
DELIMITER ;

CALL ifelse_proc ('오마이걸');

-반복문 while문 활용

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
    DECLARE hap INT; -- 합계
    DECLARE num INT; -- 1부터 100까지 증가
    SET hap = 0; -- 합계 초기화
    SET num = 1; 
    
    WHILE (num <= 100) DO  -- 100까지 반복.
        SET hap = hap + num;
        SET num = num + 1; -- 숫자 증가
    END WHILE;
    SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

-동적 SQL 활용

DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
  SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
  PREPARE myQuery FROM @sqlQuery;
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc ('member');

스토어드 함수

스토어드 함수의 개념과 형식

MySQL에서 제공하는 함수 외 사용자가 직접 함수를 만들어서 사용하는 것을 스토어드 함수라고 한다.

스토어드 함수의 형식은 다음과 같다.

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN
    이 부분에 프로그래밍 코딩
    RETURN 반환값;
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

형식이 스토어드 프로시저와 비슷하지만 차이점이 있다.

스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN문으로 하나의 값을 반환해야 한다.
스토어드 함수의 매개변수는 모두 입력 매개변수로 IN을 붙이지 않는다.
스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.
스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용할 수 없다.
스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용한다.

스토어드 함수의 사용

스토어드 함수를 사용하기 위해 먼저 다음 SQL로 스토어드 함수 생성 권한을 허용해줘야 한다.

SET GLOBAL log_bin_trust_function_creators = 1;

-숫자 2개의 합계를 계산하는 스토어드 함수

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 '합계';

-데뷔 연도를 입력하면, 활동 기간이 얼마나 되었는지 출력해주는 함수

DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT; -- 활동기간(연도)
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

SELECT calcYearFunc(2010) AS '활동햇수';

필요하다면 다음과 같이 함수의 반환 값을 SELECT~INTO~로 저장했다가 사용할 수도 있다.

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이' ;

함수의 삭제는 DROP FUNCTION 문을 사용한다.

DROP FUNCTION calcYearFunc;

커서로 한 행씩 처리하기

커서의 기본 개념

커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다. 다음의 작동 순서를 통해 처리된다.

커서의 단계별 실습

회원(가수 그룹)의 평균 인원수를 구하는 스토어드 프로시저를 작성해보자. 커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리한다.

1. 사용할 변수 준비하기

DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)

2. 커서 선언하기

 DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

3. 반복 조건 설정하기

  DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

4. 커서 열기

OPEN memberCuror;  -- 커서 열기

5. 행 반복하기

cursor_loop: LOOP
        FETCH  memberCuror INTO memNumber; 

        IF endOfRow THEN 
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;        
    END LOOP cursor_loop;

    SELECT (totNumber/cnt) AS '회원의 평균 인원 수';

6. 커서 닫기

CLOSE memberCuror;

위 코드를 통합하면 다음과 같다.

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT; -- 회원의 인원수
    DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
    DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)

    DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

    OPEN memberCuror;  -- 커서 열기

    cursor_loop: LOOP
        FETCH  memberCuror INTO memNumber; 

        IF endOfRow THEN 
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;        
    END LOOP cursor_loop;

    SELECT (totNumber/cnt) AS '회원의 평균 인원 수';

    CLOSE memberCuror; 
END $$
DELIMITER ;

CALL cursor_proc();

트리거의 기본

트리거의 개요

트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다. 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행된다.

트리거의 기본 작동

트리거는 테이블에 DML(Date Manipulation Language)문의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다.

간단한 테이블을 만든 후 트리거를 부착해보자.

USE market_db;
CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));
INSERT INTO trigger_table VALUES(1, '레드벨벳');
INSERT INTO trigger_table VALUES(2, '잇지');
INSERT INTO trigger_table VALUES(3, '블랙핑크');

DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$ 
CREATE TRIGGER myTrigger  -- 트리거 이름
    AFTER  DELETE -- 삭제후에 작동하도록 지정
    ON trigger_table -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행시 작동되는 코드들
END $$ 
DELIMITER ;

실행을 해보면 다음과 같다.

DELETE FROM trigger_table WHERE id = 3;
SELECT @msg;

트리거 활용

market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해보자.

-singer 테이블 생성

USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

-가수 테이블에 INSERT나 UPDATE 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 백업 테이블 생성

DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer
( mem_id  		CHAR(8) NOT NULL , 
  mem_name    	VARCHAR(10) NOT NULL, 
  mem_number    INT NOT NULL, 
  addr	  		CHAR(2) NOT NULL,
  modType  CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);

-UPDATE가 발생했을 때 작동하는 singer_updateTrg 트리거 생성

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg  -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

- DELETE가 발생했을 때 작동하는 singer_delteTrg 트리거 생성

DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg  -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

-데이터 변경하기

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

-변경된 내용이 잘 보관되어 있는지 백업 테이블에서 조회

SELECT * FROM backup_singer;

-테이블의 모든 행 데이터 삭제

TRUNCATE TABLE singer;

*이때 백업 테이블에는 삭제된 내용이 들어가지 않는다. DELETE 트리거는 오직 DELETE 문에만 작동하기 때문이다.

'스터디 > SQL' 카테고리의 다른 글

[SQL] 8. SQL과 파이썬 연결 (0)	2024.05.06
[SQL] 6. 인덱스 (0)	2024.04.29
[SQL] 5. 테이블과 뷰 (0)	2024.03.29
[SQL] 4. SQL 고급 문법 (0)	2024.03.25
[SQL] 3. SQL 기본 문법 (2)	2024.03.18

현재글[SQL] 7. 스토어드 프로시저

데사 전공생 공부 기록

knn #svm #결정 트리 #로지스틱 회귀 #선형 회귀, k-means #pca #dbscan, 경사하강법 #손실함수 #에포크,

Today :
Yesterday :

:D