스토어드 프로시저(Stored Procedure).MySQL

2023.02.08 - [MySQL] - 스토어드 프로시저(Procedure)를 이용한 프로그래밍.MySQL

스토어드 프로시저(Procedure)를 이용한 프로그래밍.MySQL

 

 위 글과 관계있는 내용입니다.

---

스토어드 프로시저(Stored Procedure)의 개요

• 스토어드 프로시저(Stored Procedure, 저장 프로시저)
  
  • MySQL에서 제공되는 프로그래밍 기능
  • 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용
  • 쿼리 모듈화
    • 필요할 때마다 홀출만 하면 훨씬 편리하게 MySQL 운영
    • CALL 프로시저_이름() 으로 호출
• 기본 형식

 

---

스토어드 프로시저의 수정과 삭제

스토어드 프로시저의 수정과 삭제

• 수정 : ALTER PROCEDURE
• 삭제 : DROP PROCEDURE

매개 변수의 사용

• 입력 매개 변수를 지정하는 형식

• 입력 매개 변수가 있는 스토어드 프로시저 실행방법

• 출력 매개 변수를 지정하는 방법

• 출력 매개 변수에 값 대입하기 위해 주로 SELECT...INTO 문 사용
• 출력 매개 변수가 있는 스토어드 프로시저 실행 방법

 

---

스토어드 프로시저의 특징

MySQL의 성능 향상

• 긴 쿼리가 아니라 짧은 프로시저 내용만 클라이언트에서 서버로 전송
  • 네트워크 부하 줄임으로 MySQL의 성능 향상

 

유지관리가 간편

• 응용 프로그램에서는 프로시저만 호출
  • 데이터베이스에서 관련된 스토어드 프로시저의 내용 수정/유지보수

 

모듈식 프로그래밍 가능

• 언제든지 실행 가능
• 스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리가 수월합니다.
• 모듈식 프로그래밍 언어와 동일한 장점을 갖습니다

보안 강화

• 사용자 별로 테이블 접근 권한 주지않고 스토어드 프로시저에만 접근 권한을 주어 보안을 강화
  • 뷰 또한 스토어드 프로시저와 같이 보안 강화가 가능합니다.

---

drop database if exists sqld;

create database if not exists sqldb;

use sqldb;

create table usertbl(
   userID      char(8)         not null   primary key,   -- 사용자 아이디(PK)
    name      varchar(10)      not null,               -- 이름
    birthYear   int          not null,               -- 출생년도
    addr      char(2)         not null,               -- 지역 : 경기, 서울, 경남 식으로 2글자 입력
    mobile1      char(3),                           -- 휴대폰의 국번 : 011, 016, 017, 018, 019, 010               
    mobile2      char(8),                           -- 휴대폰의 나머지 전화번호
    height      smallint,                           -- 키
    mDate      date                              -- 회원 가입일
 );
 
 -- 회원 구매 테이블 (buytbl)
 create table buytbl(
   num         int         auto_increment not null primary key,   -- 순번(PK)
   userID      char(8)      not null,                        -- 아이디(FK)
    prodName   char(6),                                 -- 물품명
    groupName   char(4),                                 -- 분류
    price      int         not null,                        -- 단가
    amount      smallint   not null,                        -- 수량
    foreign key (userID) references usertbl(userID)
);

show tables;

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울',  null,  NULL    , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남',  NULL,  NULL    , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화',  NULL, 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000,   1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,   5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,   10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'   , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,   2);


 -- 프로시저 생성 
 
delimiter $$
create procedure userproc1(in username varchar(10))	-- in 입력 매개 변수
begin
	select * from usertbl where name = username;
end $$
delimiter ;

call userproc1('조관우');

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, 175);

-- 출력 매개 변수 사용(in 1, out 1)
drop procedure if exists userproc3;

delimiter $$
create procedure userproc3(in txtvalue char(10), out outValue int)		-- txtvalue 변수를 들고들어가서 int형 outvalue를 출력
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);

-- 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('이승기');

-- -----------------
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 ;

drop procedure if exists caseproc;

call caseproc ('김범수');

-- 구구단을 문자열로 생성해서 테이블에 이볅하는 스토어드 프로시저 생성
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;		-- 구구단 뒷자리는 항상 1부터 9까지
        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)

drop table if exists guguselect;

create table guguselect (txt varchar(100));

drop procedure if exists guguselect;

delimiter $$
create procedure guguselect(in i int)
begin
	declare str varchar(100);
    declare j int;
	
		set str = '';
        set j = 1;        
        while (j<10) do
			set str = concat(str, ' ', i, 'x', j, '=', i * j);
            set j = j + 1;
		end while;
        select (str);	-- 각 단의 결과를 테이블에 입력
end $$
delimiter ;
    
call guguselect(3);
select * from guguselect;

-- 사용자가 단의 범위 (3 ~ 5 단)을 지정해서 단이 출력. 프로시저명(3,5)


drop table if exists gugutbl3;

create table gugutbl3 (txt varchar(100)); -- 구구단 저장용 테이블

drop procedure if exists whileproc3;

delimiter $$
create procedure whileproc3(in i int, in j int)
begin
	declare str varchar(100);	-- 각 단을 문자열로 저장
    declare k int;	-- 구구단 뒷자리
    
    while (i <= j) do	-- 바깥 반복문. 2~9단 까지
		set str = ''; 	-- 각 단의 결과를 저장할 문자열 초기화
		set k = 1;		-- 구구단 뒷자리는 항상 1부터 9까지
        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 gugutbl3 values(str);	-- 각 단의 결과를 테이블에 입력
	end while;
end $$
delimiter ;

call whileproc3(3, 5);
select * from gugutbl3;

-- 프로시저 예외 처리
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+...+', i, '=오버플로어');
	end;
    
    set i = 1;
    set hap = 0;
    
    while (true) do
		set savehap = hap;	-- 오버플로어 발생 직전의 합계 savehap
        set hap = hap + i;
        set i = i + 1;
	end while;
end $$
delimiter ;

call errorproc();

-- 저장된 프로시저에 이름 내용 확인
-- routine_name = 프로시저 이름, routine_definition = 프로시저 내용
select routine_name, routine_definition from information_schema.routines
	where routine_schema = 'sqldb' and routine_type = 'PROCEDURE';	-- 프로시저는 대문자로 입력해야 합니다. 자바의 라이브러리 클래스같은 느낌?
    
-- 파라미터 확인
select parameter_mode, parameter_name from information_schema.parameters
	where specific_name = 'userproc3';
    
-- 테이블의 이름을 파라미터로 전달
drop procedure if exists nameproc;

delimiter $$
create procedure nameproc(in tblname varchar(20))
begin
	set @sqlQuery = concat('select * from', tblname);
    prepare myQuery from @sqlQuery;		-- 메모리에 myquery 이름으로 저장(준비)
    execute myQuery;					-- myQuery 실행
    deallocate prepare myQuery;			-- 메모리에서 myQuery 해제
end $$
delimiter ;

call nameproc('buytbl');

-- 프로시저 삭제
-- drop procedure 프로시저 이름;
-- 프로시저 : alter 로는 procedure가 수정 되지 않습니다.