[혼자 공부하는 SQL] chapter 7. 스토어드 프로시저(7-1)

https://www.hanbit.co.kr/store/books/look.php?p_code=B6846155853

혼자 공부하는 SQL

위 내용은 <혼자 공부하는 SQL>을 기반으로 공부하여 작성 내용입니다.

 

7-1 스토어드 프로시저 사용방법

• 스토어드 프로시저(stored procedure)는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과 냄

스토어드 프로시저 기본

스토어드 프로시저의 개념과 형식

• MySQL에서 제공하는 프로그래밍 기능
• 쿼리 문의 집합으로도 볼 수 있음
• 어떠한 동작을 일괄 처리하기 위한 용도로 사용
• 자주 사용하는 일반적인 쿼리를 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리함

-- 필수 형식
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT 매개변수)
BEGIN

  -- 이 부분에 SQL 프로그래밍 코드를 작성
  
END $$
DELIMITER;

• $$ ~ $$ : 필수 항목으로 스토어드 프로시저를 묶어주는 역할, $$ 1개만 사용해도 되지만 명확한 표시 위해 2개 사용, 다른 기호로 바꿔도 됨(##, %%, &&, // 등)
• DELIMITER : '구분자'라는 의미
• 스토어드 프로시저 이름은 가능하면 이르만으로도 스토어드 프로시저라는 것을 알 수 있도록 표현하는 것이 좋

use market_db;  -- 사용할 데이터베이스 설정

-- 기존 user_proc 삭제, 없으면 넘어감
drop procedure if exists user_proc;

delimiter $$
create procedure user_proc()  -- 스토어드 프로시저 생성 구문
begin
    select * from member;  -- 스토어드 프로시저 내용
end $$
delimiter ;

call user_proc  -- 스토어드 프로시저 실행(호출)

-- 스토어드 프로시저 삭제, 이름 뒤에 괄호 붙이지 않음
drop procedure user_proc;

 

스토어드 프로시저 실습

매개변수의 사용

-- 입력 매개변수 지정 형식
IN 입력 매개변수 이름 데이터 형식

-- 입력 매개변수가 있는 스토어드 프로시저 실행 시 괄호 안에 값을 전달
CALL 프로시저 이름(전달 값)

-- 출력 매개변수
OUT 출력 매개변수 이름 데이터 형식

-- 출력 매개변수가 있는 스토어드 프로시저 실행
CALL 프로시저 이름(@변수명);
SELECT @변수명;

 

입력 매개변수의 활용

입력 매개변수가 있는 스토어드 프로시저

use market_db;
drop procedure if exists user_proc1;
delimiter $$
-- userName 매개변수에 대입
create procedure user_proc1(in userName varchar(10))
begin
    select * from member where mem_name = userName; -- '에이핑크' 조회 수행
end $$
delimiter ;

-- '에이핑크'를 입력 매개변수로 전달
call user_proc1('에이핑크');

 

2개의 입력 매개변수가 있는 스토어드 프로시저

drop procedure if exists user_proc2;
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);

 

출력 매개변수의 활용

출력 매개변수가 있는 스토어드 프로시저 생성

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

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 user_proc3('테스트1', @myValue);
select concat('입력된 ID 값 ==>', @myValue);

SQL 프로그래밍 활용

if ~ else문 사용

drop procedure if exists ifelse_proc;
delimiter $$
create procedure ifelse_proc(
    in memName varchar(10) -- 매개변수로 가수 그룹의 이름을 받음
) 
begin
    declare debutYear int; -- 데뷔 연도 저장할 변수 선언
    -- 가수 이름으로 조회, 데뷔 일자 중에서 year() 함수로 연도만 추출해서 변수에 저장
    select year(debut_date) into debutYear from member 
        where mem_name = memName; 
	if (debutYear >= 2015) then
        select '신인 가수네요. 화이팅 하세요.' as '메시지';
    else
        select '고참 가수네요. 그동안 수고하셨어요.' as '메시지';
	end if;
end $$
delimiter ;

call ifelse_proc ('오마이걸')

 

whlie 문 사용

drop procedure if exists while_proc;
delimiter $$
create procedure while_proc() 
begin
    declare hap int; -- 합계
    declare num int; -- 1부터 100까지 증가
    set hap = 0; -- 합계 초기화
    set num = 1;
    
    while (num <= 100) do -- 100까지 반복
        set hap = hap + num;
        set num = num + 1; -- 숫자 증가
	end while;
    select hap as '1~100 합계';
end $$
delimiter ;

call while_proc ()

 

동적 SQL 사용

• 다이나믹하게 SQL이 변경됨

drop procedure if exists dynamic_proc;
delimiter $$
create procedure dynamic_proc(
    in tableName varchar(20)) 
begin
    -- 넘겨받을 테이블 이름을 문자열로 생성
    set @sqlQuery = concat('select * from ', tableName);
    
    -- select 문자열 준비하고 실행
    prepare myQuery from @sqlQuery;
    execute myQuery;
    
    -- 사용한 myQuery 해제
    deallocate prepare myQuery;
end $$
delimiter ;

call dynamic_proc ('member');