Preface
기말고사를 끝내고 대략 한 달만에 MySQL 공부를 다시 시작했다.
중간고사 때와 달리 종강을 앞두고 있어 이것 저것 제출할 것도, 준비할 것도 너무 많아 개발 공부에 시간을 쓰지 못했다.
어찌저찌 종강을 한 후엔 일주일 정도 아무것도 안 하고 푹 쉬었다.
몸이 너무 피곤해서 7월부터 공부를 시작할까 생각도 했지만, 남은 시간이 얼마 없어 후다닥 책을 폈다.
이번 장은 한 번에 글을 업로드 할 생각이었는데 오버플로우 코드를 실행시킨 뒤부터 맥북 발열이 너무 심해서 어쩔 수 없이 파트를 나누어 업로드해야 할 것 같다.
딱히 어려운 부분은 없었지만, 오랜만에 다시 공부를 해서 그런지 기억이 잘 나지 않는 부분도 있다.
잠시 복습하는 시간을 가진 후 빡새게 공부해서 얼른 SQL을 마치고 웹 공부로 넘어가자.
1. 스토어드 프로시저
- 스토어드 프로시저(stored procedure): 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용
→ 자주 사용하는 쿼리를 모듈화 시켜서 필요할 때마다 호출한다.
- 간단한 스토어드 프로시저 생성 예
use sqldb;
drop procedure if exists userProc;
delimiter $$
create procedure userProc()
begin
select * from usertbl; -- 스토어드 프로시저 내용
end $$
delimiter ;
call userProc();
- 스토어드 프로시저나 스토어드 함수는 alter procedure나 alter function으로 내용을 바꿀 수 없고, 내용을 바꾸려면 drop시킨 후에 다시 create해야 한다.
→ 보안 및 작동 방식과 관련된 특성을 수정할 땐 alter procedure를 사용할 수 있다.
- 스토어드 프로시저에는 실행 시에 입력 매개 변수를 지정할 수 있고, 처리된 결과를 출력 매개 변수를 통해 얻을 수도 있다.
- 입력 매개 변수를 지정하는 형식
in 입력_매개변수_이름 데이터_형식
- 입력 매개 변수가 있는 스토어드 프로시저 실행 방법
call 프로시저_이름(전달 값);
- 출력 매개 변수를 지정하는 형식
out 출력_매개변수_이름 데이터_형식→ 출력 매개 변수에 값을 대입하기 위해선 주로 select~into문을 사용한다.
- 출력 매개 변수가 있는 스토어드 프로시저 실행 방법
call 프로시저_이름(@변수명);
select @변수명;
- 스토어드 프로시저 내부에서 오류가 발생한 경우 처리 방법
declare 액션 handler for 오류조건 처리할_문장
- 1개의 입력 매개 변수가 있는 스토어드 프로시저
use sqldb;
drop procedure if exists userProc1;
delimiter $$
create procedure userProc1(in userName1 varchar(10))
begin
select * from userTbl where username = userName1;
end $$
delimiter ;
call userProc1('조관우');
- 2개의 입력 매개 변수가 있는 스토어드 프로시저
drop procedure if exists userProc2;
delimiter $$
create procedure userProc2(
in userBirth int,
in userHeight int
)
begin
select * from userTbl
where birthYear > userBirth and height > userHeight;
end $$
delimiter ;
call userProc2(1970, 178);
- 출력 매개 변수를 사용하는 스토어드 프로시저
drop procedure if exists userProc3;
delimiter $$
create procedure userProc3(
in txtValue char(10),
out outValue int
)
begin
insert into testTBL values(null, txtValue);
select max(id) into outValue from testTBL;
end $$
delimiter ;
create table if not exists testTBL(
id int auto_increment primary key,
txt char(10)
);
call userProc3('테스트값', @outValue);
select concat('현재 입력된 ID 값 ==> ', @outValue);→ 실제 테이블이 없어도 스토어드 프로시저는 만들어지지만, 호출하는 시점에선 테이블이 존재해야 한다.
- 조건문을 사용하는 스토어드 프로시저
drop procedure if exists ifelseProc;
delimiter $$
create procedure ifelseProc(
in userName1 varchar(10)
)
begin
declare bYear int; -- 변수 선언
select birthYear into bYear from userTbl
where username = userName1;
if (bYear >= 1980) then
select '아직 젊군요..';
else
select '나이가 지긋하시네요';
end if;
end $$
delimiter ;
call ifelseProc('조용필');
- CASE문을 사용하는 스토어드 프로시저
drop procedure if exists caseProc;
delimiter $$
create procedure caseProc(
in userName1 varchar(10)
)
begin
declare bYear int;
declare tti char(10);
select birthYear into bYear from userTbl
where username = userName1;
case
when (bYear % 12 = 0) then set tti = 'monkey';
when (bYear % 12 = 1) then set tti = 'chicken';
when (bYear % 12 = 2) then set tti = 'dog';
when (bYear % 12 = 3) then set tti = 'pig';
when (bYear % 12 = 4) then set tti = 'ret';
when (bYear % 12 = 5) then set tti = 'cow';
when (bYear % 12 = 6) then set tti = 'tiger';
when (bYear % 12 = 7) then set tti = 'rabbit';
when (bYear % 12 = 8) then set tti = 'dragon';
when (bYear % 12 = 9) then set tti = 'snake';
when (bYear % 12 = 10) then set tti = 'horse';
else set tti = 'lamb';
end case;
select concat(userName1, '의 띠 ==> ', tti);
end $$
delimiter ;
call caseProc('김범수');
- while문을 사용하는 스토어드 프로시저 (구구단)
drop table if exists guguTBL;
create table guguTBL (txt varchar(100)); -- 구구단 저장용 테이블
drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin
declare str varchar(100); -- 각 단을 문자열로 저장
declare i int; -- 구구단 앞자리
declare k int; -- 구구단 뒷자리
set i = 2; -- 2단부터 계산
while (i < 10) do -- 바깥 반복분, 2~9단까지
set str = ''; -- 각 단의 결과를 저장할 문자열 초기화
set k = 1;
while (k < 10) do
set str = concat(str, ' ', i, 'x', k, '=', i*k);
set k = k + 1;
end while;
set i = i + 1;
insert into guguTBL values(str);
end while;
end $$
delimiter ;
call whileProc();
select * from guguTBL;
- 오류 처리가 포함된 스토어드 프로시저
drop procedure if exists errorProc;
delimiter $$
create procedure errorProc()
begin
declare i int; -- 1씩 증가하는 값
declare hap int; -- 합계(정수형), 오버플로 발생시킬 예정
declare saveHap int; -- 합계(정수형), 오버플로 직전의 값을 저장
declare exit handler for 1264 -- int형 오버플로가 발생하면 이 부분 수행
begin
select concat('int 오버플로 직전의 합계 --> ', saveHap);
select concat('1+2+3+4+...+', i, '=오버플로');
end;
set i = 1; -- 1부터 증가
set hap = 0; -- 합계를 누적
while (TRUE) do -- 무한 루프
set saveHap = hap; -- 오버플로 직전의 합계를 저장
set hap = hap + i; -- 오버플로가 나면 11행, 12행을 수행함
set i = i + 1;
end while;
end $$
delimiter ;
call errorProc();
- 현재 저장된 프로시저의 이름 및 내용 확인 방법
-- 현재 저장된 프로시저의 이름 및 내용 확인
select routine_name, routine_definition from information_schema.routines
where routine_schema = 'sqldb' and routine_type = 'PROCEDURE';
-- 프로시저 이름 꼭 대문자로 입력해야 됨
-- 스토어드 프로시저의 내용을 확인하는 다른 방법
show create procedure sqldb.userProc3;→ 스토어드 프로시저의 내용은 확인되지만, 파라미터는 보이지 않는다.
- 스토어드 프로시저의 파라미터 확인 방법
-- 특정 프로시저의 파라미터 확인
select parameter_mode, parameter_name, dtd_identifier
from information_schema.parameters
where specific_name = 'userProc3';
- 테이블 이름을 파라미터로 전달하는 예제
drop procedure if exists nameProc;
delimiter $$
create procedure nameProc(
in tblName varchar(20)
)
begin
select * from tblName;
end $$
delimiter ;
call nameProc('userTBL');→ MySQL에선 직접 테이블 이름을 파라미터로 사용할 수 없으므로 오류가 발생한다.
- 동적 SQL을 사용한 스토어드 프로시저
drop procedure if exists nameProc;
delimiter $$
create procedure nameProc(
in tblName varchar(20)
)
begin
set @sqlQuery = concat('select * from ', tblName);
prepare myQuery from @sqlQuery;
execute myQuery;
deallocate prepare myQuery; -- 준비된 명령문 할당 해제
end $$
delimiter ;
call nameProc('userTBL');
- 스토어드 프로시저의 특징
1) MySQL의 성능을 향상시킬 수 있다.
2) 유지관리가 간편하다.
3) 모듈식 프로그래밍이 가능하다.
4) 보안을 강화할 수 있다.
'DB & SQL > 이것이 MySQL이다' 카테고리의 다른 글
| 전체 텍스트 검색과 파티션 (0) | 2022.06.26 |
|---|---|
| 스토어드 프로그램 (2) (0) | 2022.06.25 |
| DB 및 테이블의 정보를 출력하는 명령어 (0) | 2022.05.29 |
| 인덱스 (2) (0) | 2022.05.29 |
| 인덱스 (1) (0) | 2022.05.24 |
댓글