220415: 저장 프로시저 실습

💻 SQL 저장 프로시저 예제: 책 이름 검색, 가격에 따른 메시지 리턴

사용한 book table

CREATE TABLE Book (
  bookid      INTEGER PRIMARY KEY,
  bookname    VARCHAR(40),
  publisher   VARCHAR(40),
  price       INTEGER 
);

테이블 출처: IT CookBook, SQL Server로 배우는 데이터베이스 개론과 실습

 

프로시저

책 제목의 일부(또는 전체)를 받아와서 검색한 후 그중 첫번째 결과(책)의 가격을 비교하여 가격에 따라 다른 메시지 리턴한다.
해당 책이 없을 경우 책이 없다는 메시지를 리턴해준다.

CREATE DEFINER=`root`@`localhost` PROCEDURE `chkBookPrice`(
    in searchBookName varchar(30),
    out output varchar(30)
)
BEGIN
    declare bookPrice integer;
    declare searchBookName varchar(20);

    # '%검색어%' 형식으로 문자열을 만들어 검색한다.
    # 그 중 맨 처음 검색된 책의 가격만 가져옴
    # mysql에서는 concat('%', search,'%') 과 같이 두개 이상의 문자열을 합칠 수 있다.
    select concat(concat('%', search),'%') into searchBookName; 
    select price into bookPrice from book
    where bookname like searchBookName limit 1;

    # 책 가격 비교하여 output에 메시지 저장
    if (bookPrice >= 30000) then
        select '책 가격이 너무 비쌉니다.' into output;    
    elseif (bookPrice >= 15000) then
        select '책 가격이 적당합니다.' into output;
    elseif (bookPrice < 15000) then
        select '책 가격이 쌉니다.' into output;
    else
        select '해당 책이 없습니다.' into output;
    end if;

END

 

아래와 같이 변경해 리턴값을 없애고 if 문에서 바로 출력할 수도 있다.

    if (bookPrice >= 30000) then
        select '책 가격이 너무 비쌉니다.' as msg;    
    elseif (bookPrice >= 15000) then
        select '책 가격이 적당합니다.' as msg;
    elseif (bookPrice < 15000) then
        select '책 가격이 쌉니다.' as msg;
    else
        select '해당 책이 없습니다.' as msg;
    end if;

 

프로시저 사용

call chkBookPrice('축구의', @msg1);
call chkBookPrice('오레오오즈', @msg2);

select @msg1 as '축구의', @msg2 as '오레오오즈';

 

💻 SQL 저장 프로시저 예제: 동적 SQL 활용

위 예제와 동일한 테이블 사용

 

프로시저

컬럼명, 테이블명, where절에서 비교할 조건, 조건식의 값을 받아서 쿼리를 동적으로 생성한다.
sql에서 홑따옴표(')를 사용하기 위해서는 홑따옴표를 연속으로 두개('') 쓴다.

"'"(쌍따옴표 가운데 홑따옴표) 또는 '"' (홑따옴표 가운데 쌍따옴표)도 사용할 수 있다.

공백에 주의한다.

CREATE DEFINER=`root`@`localhost` PROCEDURE `DynamicSQL_where`(
    in tableName varchar(40),
    in columnList varchar(200),
    in whereSql varchar(40),
    in searchParam varchar(40)
)
BEGIN
    set @sqlQuery = concat(
        'select ', 
        columnList, 
        ' from ', 
        tableName,
        ' where ',
        whereSql,
        ' ''',
        searchParam,
        ''''
    );
    # 쿼리 확인
    # select @sqlQuery;

    # 동적 쿼리 생성
    prepare myQuery from @sqlQuery;

    # 동적 실행
    execute myQuery;

    # 메모리 회수
    deallocate prepare myQuery;
END

 

프로시저 사용

call DynamicSQL_where(
    'book', 
    'bookid, bookname, publisher, price', 
    'bookname like', 
    '축구%'
);

☕️ 잡담

무료 sql eBook(PDF) 이 있다. 괜찮아보인다.

헤로쿠로 무료 클라우드 호스팅이 가능하다고 한다. 확인하기..!