[DB_MySQL] 이것이 MySQL이다 ch10 스토어드 프로그램 1(스토어드 프로시저, Stored Procedure)
bobo122023. 2. 10. 18:19
728x90
1. 스토어드 프로시저의 개요
◎ 스토어드 프로시저(Stored Procedure, 저장 프로시저) ▷ MySQL에서 제공되는 프로그래밍 기능 ▷쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용 ▷쿼리 모듈화 ▶필요할 때마다 호출만 하면 훨씬 편리하게 MySQL 운영 ▶ CALL 프로시저_이름( ) 으로 호출
1. 기본 형식 2. 스토어드 프로시저 생성 예
◎ 스토어드 프로시저 사용 예제 1
use sqldb;
drop procedure if exists userProc1;
delimiter $$
create procedure userProc1(in userName varchar(10))
begin
select * from usertbl where name = userName;
end $$
delimiter ;
call userProc1('조관우');
-- 반환 값 :
-- JKW, 조관우, 1965, 경기, 018, 9999999, 172, 2010-10-10
프로시저를 생성하고 입력 값을 넣어서 call로 출력할 수 있도록 만듭니다.
형식이 변수를 넣을 수 있는 메서드와 비슷하네요!
◎스토어드 프로시저 사용 예제 2
drop procedure if exists userProc2;
delimiter $$
create procedure userProc2(in userBirth int, in userHeight int) -- in 입력 매개 변수 2개
begin
select * from usertbl
where birthYear > userBirth and height > userHeight;
end $$
delimiter ;
call userProc2(1970, 178);
-- 반환 값 :
-- LSG, 이승기, 1987, 서울, 011, 1111111, 182, 2008-08-08
-- SSK, 성시경, 1979, 서울, , 186, 2013-12-12
◎스토어드 프로시저의 수정과 삭제
▷ 수정 : ALTER PROCEDURE ▷ 삭제 : DROP PROCEDURE
◎ 매개 변수의 사용
1. 입력 매개 변수를 지정하는 형식 2. 입력 매개 변수가 있는 스토어드 프로시저 실행 방법
3. 출력 매개 변수 지정 방법
4. 출력 매개 변수가 있는 스토어드 프로시저 실행 방법
▷ 출력 매개 변수에 값 대입하기 위해 주로 SELECT… INTO문 사용
◎스토어드 프로시저 사용 예제 3
drop procedure if exists userProc3;
delimiter $$
create procedure userProc3(in txtValue char(10), out outValue int)
begin
insert into testTBL value(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 ('테스트값2', @myValue);
select concat('현재 입력된 ID 값 ==>', @myValue);
-- 반환 값 :
-- 현재 입력된 ID 값 ==>1
입력과 출력 변수를 주고 생성된 테이블에 값을 반환시킵니다. call을 할 때마다 값이 1씩 증가됩니다.
◎ if else문을 이용한 값 출력
-- if ... else 문
drop procedure if exists ifelseProc;
delimiter $$
create procedure ifelseProc(in userName varchar(10))
begin
declare bYear int; -- 변수 선언
select birthYear into bYear from userTBL
where name = userName;
if (bYear >= 1980) then
select '아직 젊군요...';
else
select '나이가 지긋하시네요.';
end if;
end $$
delimiter ;
call ifelseProc ('이승기');
-- 아직 젊군요... 반환
◎ case 예제
-- 입력한 사용자의 띠 출력 : case 문
drop procedure if exists caseProc;
delimiter $$
create procedure caseProc(in userName varchar(10))
begin
declare bYear int; -- 출생 년도
declare tti char(3); -- 띠
select birthYear into bYear from userTBL
where name = userName;
case
when (bYear % 12 = 0) then set tti = '원숭이';
when (bYear % 12 = 1) then set tti = '닭';
when (bYear % 12 = 2) then set tti = '개';
when (bYear % 12 = 3) then set tti = '돼지';
when (bYear % 12 = 4) then set tti = '쥐';
when (bYear % 12 = 5) then set tti = '소';
when (bYear % 12 = 6) then set tti = '호랑이';
when (bYear % 12 = 7) then set tti = '토끼';
when (bYear % 12 = 8) then set tti = '용';
when (bYear % 12 = 9) then set tti = '뱀';
when (bYear % 12 = 10) then set tti = '말';
else set tti = '양';
end case;
select concat(userName, '의 띠 ==> ', tti);
end $$
delimiter ;
call caseProc ('김범수'); -- 김범수의 띠 ==> 양 반환
call caseProc ('이승기'); -- 이승기의 띠 ==> 토끼 반환
◎ while문을 이용한 구구단 예제
-- 반복문(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;
while(i<10) do
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;
1~9단까지 출력
◎프로그래밍 기능 ▷7장의 후반부에서 학습한 ‘SQL 프로그래밍’의 내용 대부분이 스토어드 프로시저에 적용 가능 ▷더 강력하고 유연한 기능 포함하는 스토어드 프로시저 생성
◎ 스토어드 프로시저 내의 오류 처리 ▷스토어드 프로시저 내부에서 오류가 발생했을 경우 ▷DECLARE 액션 HANDLER FOR 오류조건 처리할_문장 구문 ▷7장의 후반부에서 학습했던 내용
-- declare ~ handler 이용한 오류 처리
drop procedure if exists errorProc;
delimiter $$
create procedure errorProc()
begin
declare i int;
declare hap int;
declare saveHap int;
declare exit handler for 1264
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();
-- 1+2+3+4+...+65536=오버플로 반환
◎ 테이블 조회
-- information_schema 데이터베이스의 routines 테이블 조회
select routine_name, routine_definition from information_schema.routines
where routine_schema = 'sqldb' and routine_type = 'PROCEDURE';
-- information_schema 데이터베이스의 파라미터 테이블 조회
select parameter_mode, parameter_name, dtd_identifier
from information_schema.parameters
where specific_name = 'userProc3';
◎ 오류 발생 출력 예시
1. 오류 발생
show create procedure sqldb.userProc3;
drop procedure if exists nameProc;
delimiter $$
create procedure nameProc(
in tblName varchar(20)
)
begin
select * from tblName;
end $$
delimiter ;
call nameProc('userTBL');
-- Error Code: 1146. Table 'sqldb.tblname' doesn't exist 에러 발생
2. 오류에 대한 식 적용
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');
userTBL의 값들이 출력됩니다.
2. 스토어드 프로시저의 특징
◎ MySQL의 성능 향상 ▷ 긴 쿼리가 아니라 짧은 프로시저 내용만 클라이언트에서 서버로 전송 ▷네트워크 부하 줄임으로 MySQL의 성능 향상
◎유지관리가 간편 ▷ 응용 프로그램에서는 프로시저만 호출 ▷ 데이터베이스에서 관련된 스토어드 프로시저의 내용 수정/유지보수
◎ 모듈식 프로그래밍 가능 ▷언제든지 실행 가능 ▷스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리 수월 ▷모듈식 프로그래밍 언어와 동일한 장점 갖음
◎보안 강화 ▷ 사용자 별로 테이블 접근 권한 주지 않고 스토어드 프로시저에만 접근 권한을 주어 보안 강화 ▶ 뷰 또한 스토어드 프로시저와 같이 보안 강화 가능
◎ 지정하는 단 출력
-- 사용자가 단을 입력하면 입력 단에 구구단을 출력 (입력 1)
drop table if exists guguTBL3;
create table guguTBL3 (txt varchar(100)); -- 구구단을 저장할 테이블
drop procedure if exists whileProc3;
delimiter $$
create procedure whileProc3(in i int)
begin
declare str varchar(100); -- 각 단을 문자열로 저장
declare k int;
set str = ' '; -- 각 단 시작시 저장할 문자열 초기화
set k = 1;
while(k<10) do
set str = concat(str, ' ', i, 'x', k, '=', i*k); -- 구구단 계산 값을 누적
set k = k + 1; -- 숫자 증가
end while;
insert into guguTBL3 values(str);
end $$
delimiter ;
call whileProc3(3);
select * from guguTBL3;
-- 반환 값
-- 3x1=3 3x2=6 3x3=9 3x4=12 3x5=15 3x6=18 3x7=21 3x8=24 3x9=27
◎ 지정하는 단의 범위 내에서 구구단 출력
-- 사용자가 단의 범위( 3 ~ 5단 )를 지정해서 단이 출력 프로시저명( 3 ,5 )
drop table if exists guguTBL2;
create table guguTBL2 (txt varchar(100)); -- 구구단을 저장할 테이블
drop procedure if exists whileProc2;
delimiter $$
create procedure whileProc2(in i int, in j int)
begin
declare str varchar(100); -- 각 단을 문자열로 저장
declare k int;
while(i <= j) do
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 guguTBL2 values(str);
end while;
end $$
delimiter ;
call whileProc2(3, 5);
select * from guguTBL2;
-- 3x1=3 3x2=6 3x3=9 3x4=12 3x5=15 3x6=18 3x7=21 3x8=24 3x9=27
-- 4x1=4 4x2=8 4x3=12 4x4=16 4x5=20 4x6=24 4x7=28 4x8=32 4x9=36
-- 5x1=5 5x2=10 5x3=15 5x4=20 5x5=25 5x6=30 5x7=35 5x8=40 5x9=45
스토어드 프로시저에 대해서 공부해 보았습니다. 네트워크 부하를 줄이면서 MySQL의 성능 향상을 한다고 하는데요!
