파라메터를 사용한 동적쿼리 프로시저 예제

------------------------------------------------------------------------

sp설명:  단순 게시판형태의 리스트 표시

            제목,작성자, 글번호 중하나를 선택하여 검색가능

제약사항: 데이터가 적은 테이블이기때문에 어떤 튜닝이나 재사용성,keyset같은 개념의 작업을 가미하지 않았습니다.

             문의:답변 =1:1 게시판이기때문에 계층형 표시 감안하지 않습니다.

             <= 계층형은 조금만 응용하시면 되겠죠? 여기서는 sp사용방법을 기술하고자하기때문에 해석하기쉽게 간단히 갑니다.

------------------------------------------------------------------------

/*******************************************************************************
 PROCEDURE NAME   : sp_list_select
 DATABASE     : forr                
*******************************************************************************/

DROP PROCEDURE IF EXISTS sp_list_select;

DELIMITER //

CREATE PROCEDURE sp_list_select(
    c_condition int , # 검색조건 (1:글번호, 2:작성자, 3:제목)
    c_questsearch varchar(30), # 검색어  
    c_nowpageno int ,  # 페이지번호(조회하고자하는)
    c_rowsapage int  # 한 페이지에 표시할 글 갯수
)
begin
   declare v_colsql varchar(200);  # 입력값에 따라 검색조건의 문자열 조합하여 저장할 변수

   set @v_startlimit = c_rowsapage*(c_nowpageno-1); # 표시할 글의 범위 시작 위치계산 
   set @v_endlimit  = c_rowsapage;  # 표시할 글의 범위 마지막 위치 계산
   set @v_questsearch = c_questsearch; # 검색어 입력
   set @v_sql = " SELECT  questid,questtitle,questopen,tb_quest.regname, collationtype, " ; # 가져올 컬럼 정의
   set @v_sql = concat(@v_sql," DATE_FORMAT(tb_quest.regdate,'%Y-%m-%d') regdateymd, queststatus,answerid  FROM tb_quest left outer join tb_answer on questid=answerid "); #가져올 컬럼정의추가와 답변테이블과 left outer join

 

#------ 검색어 조건 조합 start ---------------

   set v_colsql = " where queststatus=1 "; #status=1인것은 기본으로 들어가기때문에 무조건 추가 
    if     c_questsearch != "" AND c_condition =1 then   #글번호 검색이고 글번호 검색어가 입력되었다면 
     set v_colsql = concat(v_colsql," and questid = ? ");
   elseif  c_questsearch != "" AND c_condition =2 then #작성자 검색이고 작성자 검색어가 입력되었다면 
     set v_colsql =concat(v_colsql, " and tb_quest.regname like concat('%',?,'%') ");
   elseif  c_questsearch != "" AND c_condition =3 then  #제목 검색이고 제목 검색어가 입력되었다면 
     set v_colsql = concat(v_colsql," and questtitle like concat('%',?,'%') ");
   end if;

#------ 실행문장 완성 ---------------
   set @v_cntsql = concat("select count(*) cnt  from  tb_quest  ", v_colsql); #전체 개수(페이징처리하지 않고 검색조건에 맞는) 
   set @v_sql = concat(@v_sql,v_colsql," order by tb_quest.regdate desc,questid desc limit ?,?");#페이징 처리하여 order by

  

   PREPARE stmt2 FROM @v_sql ;   #리스트표시

   PREPARE stmt1 FROM @v_cntsql ;#전체 개수
  if c_questsearch!="" AND c_condition in (1,2,3) then #검색어 입력이 있다면 
          EXECUTE stmt2 USING @v_questsearch,@v_startlimit,@v_endlimit;
          EXECUTE stmt1 USING @v_questsearch;
  else #검색어 입력이 없다면
          EXECUTE stmt2 USING @v_startlimit,@v_endlimit;
          EXECUTE stmt1  ;
  end if;
   DEALLOCATE PREPARE stmt1;
   DEALLOCATE PREPARE stmt2;
end
//