데이터베이스 9. 스토어드 프로시저

1. 스토어드 프로시저는 SQL에서 프로그래밍을 사용하는 것이다.

테이블과 같이 데이터베이스 내부에 저장되는 개체이다.

조건문 반복문 등 일반 프로그래밍 언어처럼 코딩이 가능하다.

프로시저 이름을 call함으로서 사용한다.

 

2. 스토어드 프로시저 보통 구성

delimiter $$

create procedure 스토어드프로시저이름(in 또는 out 메개변수)

begin

 

-sql프로그래밍 코드 작성-

 

end$$

delimiter;

 

이름은 자유롭게 지으나 프로시저임을 나타낼 수 있는 이름 권장

delimiter 로 구분자는 $$로 바꾸었다가 다시 ;로 바꾸는 것임

 

3. 스토어드 프로시저 생성/사용

create produre user_proc1()

call user_proc1();

 

4.예제

 

use market_db;

drop procedure if exists user_proc1;

 

입력변수가 1개인 경우

delimiter $$

create producer user_proc1(in username varchar(10))

begin

     select * from member where mem_name=username;

ens$$

delimiter;

 

call user_proc1('블랙핑크');

 

입력변수가 2개인 경우

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

 

출력 매개변수 예제

값을 하나 넣고 그 중에서 가장 큰 값을 반환받음

delimiter$$

create procedure user_prod3(in usertxt varchar(10), out outvalue int)

begin 

     insert into notable values(null, usertxt)

     select max(no) into outvalue from notable;

end$$

delimiter;

create table notable(no int auto_increment primary key, txt char(10));

call user_proc3('테스트', @myValue); 

select @myValue;

 

프로시저는 테이블이 생선 된 후 실행되어야 한다.

 

5. 조건문

delimiter$$

create procedure ifelse_proc(in username varchar(10))

begin 

      declare dyear int;  변수 정의

      select year(debut_date) into dyear from member

           where mem_name = username;

      if(dyear >= 2017) then

          select '아직 신인' as 메세지;

      esle

           select '계속 팬할게요' as 메세지;

      end if;

end$$

delimiter;

call ifelse_proc('잇지');

 

6. 반복문

delimiter $$

create procedure while_proc(in usernum int)

begin

     declare hap, i int;

     set hap = 0, i = 1;

     while(i <= usernum) do

          set hap = hap + i;

               set i = i + 1;

          end while;

          select hap as 합계;

end $$

delimiter ;

call while_proc(100);

 

7. 동적 SQL

delimiter $$

create procedure dynamic_proc (in tablename varchar(20))

begin 

     set @sqlQuery = concat('select * from', tablename);

     prepare myQuery from @sqlQuery;

     excute myQuery;

     dellocate prepare myQuery;

end$$

delimiter;

call dynamic_proc('member');