[혼공S] 6주차_스토어드 프로시저, 스토어드 함수(커서)와 트리거

혼자 공부하는 SQL

✔️ 스토어드 프로시저(Stored Procedure)

✔️ 스토어드 함수와 커서

✔️ 트리거(Trigger)

✔️ 스토어드 프로시저(Stored Procedure)

스토어드 프로시저

MySQL에서 제공하는 프로그래밍 기능

MySQL 내부에서 사용하는 경우 적절한 프로그래밍 기능 제공

쿼리 문의 집합과 동일하다고 볼 수 있습니다.

(어떤 동작을 일괄 처리하기 위한 용도)

-- 스토어드 프로시저 기본 형식

delimiter $$ -- 명확히 해주기 위해 $$ 사용($, %%, &&, // 도 사용 가능)
	create procedure 스토어드_프로시저_이름(in 또는 out 매개변수) -- 입력, 출력 매개변수
    begin
    
    -- 해당 부분에 SQL프로그래밍 코드 작성
    
    end $$
delimiter ;

-- 생성된 스토어드 프로시저 호출

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;

매개변수를 활용한 스토어드 프로시저 생성 및 삭제

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('에이핑크'); -- userName 매개변수 대입

-- 스토어드 프로시저 삭제

drop procedure user_proc1;

2개의 입력변수가 있는 스토어드 프로시저 생성 및 삭제

use market_db;

-- 스토어드 프로시저 생성
drop procedure if exists user_proc2;

delimiter $$
	create procedure user_proc2(
    	in userNumber int,
        in userHeight int) -- 2개의 입력 매개변수 추가
    begin
		select 
			*
		from member
        where mem_number > userNumber
        and height > userHeight; 
	end $$
delimiter ;

-- 생성된 스토어드 프로시저 호출

call user_proc2(6,165); -- 인원이 6초과, 키가 165 초과하는 경우 확인

-- 스토어드 프로시저 삭제

drop procedure user_proc2;

출력 매개변후 활용한 프로이드 스포이저 생성 및 삭제

use market_db;

-- 스토어드 프로시저 생성
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로 실행되는 시점에는 사용할 테이블이 있어야 함

create table if not exists noTable(
	id int auto_increment primary key,
    txt char(10)
);

-- 생성된 스토어드 프로시저 호출

call user_proc3('테스트1', @myValue);
select concat('입력된 ID 값 ==>', @myValue);

-- 스토어드 프로시저 삭제

drop procedure user_proc3;

SQL프로그래밍 활용

조건문의 기본인 IF ~ ELSE문 사용

while문을 사용하여 반복 처리에 사용

동적 SQL(다이나믹하게 SQL이 변경됨) 사용 등

다양한 활용 예제들이 존재하지만 이는 도서나 유튜브를 참고해주시기 바랍니다.

더불어 날짜를 처리하는 경우는

date_format()을 활용하는 경우도 있지만

현재 날짜를 불러올 수 있는 curdate(),

년도만 불러오는 year(), 월만 불러오는 month(), 일자만 불러오는 day() 등 활용하면 좋을 것 같습니다

✔️ 스토어드 함수와 커서

스토어드 함수

스토어드 프리시저와 비슷한 방식으로 사용 가능

MySQL에서 제공하는 함수(내장 함수)는 다양하게 존재하지만,

스토어드 함수는 지원하지 않는 함수를 사용자가 필요한 함수를 직접 만들어 사용하는 함수입니다.

-- 스토어드 함수 기본 형식

delimiter $$
	create function 스토어드_함수_이름(매개변수) -- 입력 매개변수만 입력 가능
    	returns 반환형식 -- 반환할 값의 데이터 형식 지정
    begin
    	
    -- 해당 부분에 프로그래밍 코딩(SELECT문 사용X)
    	return 반환값; -- 하나의 값으로 반환
    end $$
delimiter ;

-- 생성된 함수 호출

select 스토어드_함수_이름; -- CALL이 아닌 SELECT문 안에서 호출됨

스토어드 함수 활용

2개의 매개변수가 있는 스포이드 함수 생성 및 제거

-- 스토어드 함수 생성 관한 허용(1번만 설정하면 됨)
-- Mysql 8.0.20 이전의 버전인 경우
-- 8.0.21 이후의 버전인 경우는 특별히 지정하지 않아도 됩니다
set grobal log_bin_trust_function_creators = 1;

-- 스토어드 함수 생성

use market_db;

drop function if exists sumFunc;
delimiter $$
	create function sumfunc(number1 int, number2 int)
    	returns int
        deterministic -- 안되는 경우 해당 단어 추가
    begin
    	return number1 + number2;
    end $$
delimiter ;

-- 생성된 스토어드 함수 호출

select sumFunc(100, 200) as '합계';

-- 스토어드 함수 제거

drop function sumFunc;

활동 햇수를 구하기 위한 스포이드 함수 생성 및 제거

-- 스토어드 함수 생성

use market_db;

drop function if exists calcYearFunc;
delimiter $$
	create function calcYearFunc(dYear int) -- 데뷔 년도 입력
    	returns int
        deterministic -- 안되는 경우 해당 단어 추가
    begin
    	declare runYear int;
        set runYear = year(curdate()) - dYear; -- 현재 년도 - 데뷔년도
        return runYear; -- 활동 햇수(결과)
    end $$
delimiter ;

-- 생성된 스토어드 함수 호출

select calcYearFunc(2010) as '활동 햇수';

-- 스토어드 함수 제거

drop function calcYearFunc;

함수의 반환값을 select ~ into ~로 저장했다가 사용 가능하고

member 테이블의 데뷔 년도를 활용하여 활동 햇수 구하기

-- 함수 반환 값 저장하여 활용

select calcYearFunc(2007) into @debut2007;
select calcYearFunc(2013) into @debut2013;
select @debut2007 - @debut2013 as '2007과 2013의 차이';

-- YEAR()함수 활용하여 활동 햇수 확인

select 	
	mem_id,
    mem_name,
    calcYearFunc(year(debut_date)) as '활동 햇수'
from member;

-- 스토어드 함수 제거

drop function calcYearFunc;

커서(Cursor)

테이블에서 한 행씩 처리하기 위한 방식

(첫 번째 행부터 마지막 행까지 한 행씩 접근하여 값을 처리)

커서 작동 과정

커서 선언 -> 반복 조건 선언 -> 커서 열기 -> 데이터 가져와서 처리(반복) -> 커서 닫음

-- 커서 생성

delimiter $$
create procedure cursor_proc()
begin
	-- 사용 변수 정의
	declare memNumber int;
	declare cnt int default 0; -- 누적이 필요하기 때문에 default 활용
	declare totNumber int default 0; -- 누적이 필요하기 때문에 default 활용
	declare endOfRow boolean default false; -- 행의 끝 파악(처음은 행의 끝이 아니기 때문 false)

	-- 커서 선언
	declare memberCursor cursor for -- memberCursor(커서명)
	select mem_number from member;
	
    -- 반복 조건 준비 예약어
    declare continue handler
		for not found set endOfRow = True; -- 행 마무리
	
    -- 커서 열기 
    open memberCursor;
    
    -- 행 반복 수행
    cursor_loop: LOOP
		-- 한 행씩 읽어서 수행(fetch)
		fetch memberCursor into memNumber;
        if endOfRow then 
			-- 반복을 빠져나오는데 필요(leave)
			leave cursor_loop;
		end if;
	
		set cnt = cnt + 1;
        set totNumber = totNumber + memNumber;
	end LOOP cursor_loop; -- 행 반복 종료
    
    -- 반복을 빠져나오면 회원의 평균 인원 수 계산
    select (totNumber/cnt) as '회원의 평균 인원 수';
    
    -- 커서 닫기
    close memberCursor;
    
end $$
delimiter ;

-- 생성된 커서 수행

call cursor_proc();

-- 커서 제거(스포이드 프로시저와 동일)

drop procedure cursor_proc;

✔️ 트리거(Trigger)

트리거

자동으로 수행하여 사용자가 추가 작업 잊어버리는 실수 방지

(데이터 무결성 유지)

오직 DML문(INSERT, UPDATE, DELETE)의 이벤트가 발생하는 경우에만 자동으로 실행되는데

예) 회원 테이블에서 DELETE 작업이 작업이 일어나는 경우

해당 데이터가 삭제되기 전 자동으로 다른 테이블에 저장해주는 기능 수행

트리거 작동

-- 테이블 생성 및 데이터 입력

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 myTigger -- 트리거명(myTrigger)
	after delete -- delete문이 발생된 이후 작동 의미
    on trigger_table -- 이 트리거를 부착할 테이블 지정
    for each row -- 각 행마다 적용(트리거는 항상 작성)
begin -- 트리거 실제 작동 부분
	set @msg = '가수 그룹 삭제됨'; -- 트리거 실행 시 작동되는 코드
end $$
delimiter ;

-- INSERT, UPDATE 문에는 반응하지 않음

delete from trigger_table where id = 4;
select @msg; -- 해당하는 데이터 없어 나오지 않음

-- 데이터 삭제 경우

delete from trigger_table where id = 3;
select @msg;

트리거 활용

market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경되는 경우

변경한 사용자, 시간, 변경 전의 데이터 기록 트리거 작성

-- 테이블 생성 및 데이터 입력

use market_db;
drop table if exists singer;
create table singer(select mem_id, mem_name, mem_number, addr from member);

-- 백업 테이블 생성
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 -- update후 작동
    on singer -- 트리거 부착할 테이블명
    for each row
begin
	-- OLD 테이블은 수정, 삭제될 때 변경되기 전의 데이터 잠깐 저장되는 임시 테이블(MySQL 내부 제공 테이블)
	insert into backup_singer values(OLD.mem_id, OLD.mem_name,
    OLD.mem_number, OLD.addr, '수정', curdate(), current_user());
end $$
delimiter ;

-- delete 발생하는 경우 동작하는 singer_deleteTrg

drop trigger if exists singer_deleteTrg;
delimiter $$
create trigger singer_deleteTrg -- 트리거명
	after delete --  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와 delete 수행하고 backup_singer 조회

update singer set addr = '영국' where mem_id = 'BLK';
delete from singer where mem_number >= 7;

select * from backup_singer;

-- 테이블의 모든 행 제거

truncate table singer; -- truncate 삭제하는 경우는 트리거가 작동하지 않음
select * from backup_singer; -- 데이터가 자동으로 저장되지 않음

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

NEW테이블 : 테이블에 새로운 값이 입력되기 전 임시로 저장되는 테이블

OLD테이블 : 테이블에 예전 값이 없어지기 전 임시로 저장되는 테이블

INSERT() : insert(새 값) -> NEW테이블(새 값) -> 테이블(새 값)

DELETE() : delete(예전 값) -> 테이블(~~예전 값~~) -> OLD테이블(예전 값)

UPDATE() : update(새 값, 예전 값) -> NEW테이블(새 값) -> 테이블(~~예전 값~~) -> OLD테이블(예전 값)

이상으로 6주차 1번째 내용 정리를 마무리 하겠습니다.

혹시나 공부를 원하시는 분은 해당 도서를 구매하셔서 공부하는 더 좋을 것 같습니다.

혼자 공부하는 SQL 도서

혼자 공부하는 SQL

아무런 사전 지식 없는 입문자가 ‘꼭 필요한 내용을 제대로’ 학습할 수 있도록 구성했다. ‘무엇을’, ‘어떻게’ 학습해야 할지조차 모르는 입문자의 막연한 마음을 살펴, 과외 선생님이 알

www.aladin.co.kr

18강부터 20강까지의 내용을 다루고 있으니 궁금하신 분은 유튜브를 참고하여 공부하면 좋을 것 같습니다.

'SQL > 혼자 공부하는 SQL' 카테고리의 다른 글

[혼공S] 혼공단 13기_혼공학습단 13기, 나만의 SQL 성장기 (1)	2025.02.21
[혼공S] 6주차_파이썬과 MySQL 연동 및 GUI 프로그램 활용 (0)	2025.02.17
[혼공S] 5주차_인덱스(Index) 원리와 MySQL 워크벤치 실습 (0)	2025.02.12
[혼공S] 4주차_제약조건을 활용한 테이블 및 가상 테이블(VIEW) 생성 및 활용 (0)	2025.02.03
[혼공S] 3주차_MySQL 데이터 형식과 JOIN(조인), SQL 프로그래밍 활용 (0)	2025.01.23

'SQL > 혼자 공부하는 SQL' 카테고리의 다른 글

티스토리툴바