[MSSQL] 저장 프로시져2(쿼리 저장 및 실행 / 출력용 매개변수 OUTPUT)

-- 게시물 신규 등록 프로시저

CREATE PROC UP_INSERT_BOARD

@TITLE VARCHAR(300),

@CONTENT TEXT,

@USER_ID VARCHAR(20),

@WRITER VARCHAR(10),

@PASSWORD VARCHAR(10)

AS

-- (BOARD_MAIN_IDX의 최대값을 구해와서 새 게시물의 IDX로 쓰기 위해 변수지정)

DECLARE @BOARD_MAIN_IDX CHAR(8)

SET @BOARD_MAIN_IDX = (SELECT ISNULL(MAX(BOARD_MAIN_IDX), 10000000) + 1 FROM T_BOARD_MAIN)

INSERT T_BOARD_MAIN VALUES

(@BOARD_MAIN_IDX, @TITLE, @CONTENT, @USER_ID, @WRITER, @PASSWORD, 0, 0, 0, 'N', GETDATE(), GETDATE()) 

EXEC UP_INSERT_BOARD '처음이예요', '내용입니다', 'AAA', '↔', '↔'

 

SELECT * FROM T_BOARD_MAIN

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

-- 게시물 목록 보기 프로시저 (페이지당 최대 몇개 볼래?)

CREATE PROC UP_LIST_BOARD

@TOP_VAL INT

AS

SELECT TOP(@TOP_VAL) * FROM T_BOARD_MAIN

ORDER BY BOARD_MAIN_IDX DESC 

 

DROP PROC UP_LIST_BOARD
EXEC UP_LIST_BOARD 1

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

-- 주의 : 프로시저를 만들때 매개변수 사용
-- 1) SELECT TOP N 구문에서 N에 해당하는 매개변수의 사용법
-- 2) 테이블명을 매개변수 값으로 받을수는 없다

CREATE TABLE TEST1

(

 IDX INT,

 NAME VARCHAR(200)

)

INSERT TEST1 VALUES (1, 'AAA')

INSERT TEST1 VALUES (2, 'BBB')

SELECT * FROM TEST1

DROP TABLE TEST1

-- 아래 프로시저는 수행 안됨

CREATE PROC UP_SEL_T1

@TBLNAME VARCHAR(100)

AS

SELECT * FROM @TBLNAME

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

-- ★★ 문자열 매개변수 생성후 쿼리 실행

CREATE PROC UP_SEL_T1

@TBLNAME VARCHAR(100)

AS

DECLARE @QUERY VARCHAR(500)
SET @QUERY = 'SELECT * FROM ' + @TBLNAME
EXEC(@QUERY) -- 실행시
--SELECT(@QUERY) -- 쿼리문 뽑아올 시

 

EXEC UP_SEL_T1 'T_BOARD_MAIN'
DROP PROC UP_SEL_T1

 

-- 쿼리를 문자열 변수로 저장후 변수값을 이용하여 쿼리문 실행 (페이지당 몇개 볼래?)
CREATE PROC UP_SEL_T1_ALTER
@TOPCNT INT, -- 매개변수 : 페이지당 볼 글의 수
@TBLNAME VARCHAR(100) -- 게시물이 저장된 테이블명
AS
DECLARE @QUERY VARCHAR(500)
SET @QUERY = 'SELECT TOP '+CONVERT(VARCHAR(10),
@TOPCNT)+' * FROM ' + @TBLNAME
EXEC(@QUERY)

 

EXEC UP_SEL_T1_ALTER 1, 'TEST1'
DROP PROC UP_SEL_T1_ALTER
----------------------------------------------------------------

-- 페이징 쿼리 NOT IN
-- 한 페이지당 1개 -> 개발자 쪽에서 결정
--> 페이지 사이즈 -> 1
-- 사용자 -< 나 ?(1) 페이지 볼래요

 

SELECT TOP 가져올 개수 (페이지 사이즈)
가져올 컬럼들...
FROM T_BOARD_MAIN
WHERE BOARD_MAIN_IDX > 10000000
AND NOT IN(SELECT TOP(페이지 번호 -1) * 페이지 사이즈)
BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX
ORDER BY BOARD_MAIN_IDX DESC

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

-- 목록 뽑아 오기 (페이징 적용)
-- ★ 리스트 페이징 프로시저 

CREATE PROC UP_LIST_BOARD2
@PAGE_SIZE VARCHAR(3),
@PAGE_NUM VARCHAR(3)
AS
DECLARE @QUERY VARCHAR(300)
SET @QUERY = 
'SELECT TOP (' + @PAGE_SIZE + ') * FROM T_BOARD_MAIN WHERE BOARD_MAIN_IDX > 10000000
AND BOARD_MAIN_IDX NOT IN(SELECT TOP(('+ @PAGE_NUM+'-1)*'+@PAGE_SIZE+') 
BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) ORDER BY BOARD_MAIN_IDX DESC'
EXEC(@QUERY)
--SELECT(@QUERY)

 

DROP PROC UP_LIST_BOARD2
EXEC UP_LIST_BOARD2 '1', '5'
----------------------------------------------------------------

-- CM 모드 (문자열 결합 형태 A = A + B)

CREATE PROC UP_LIST_BOARD3

@PAGESIZE INT,

@PAGENUM INT

AS

DECLARE @RESULT INT, @QUERY VARCHAR(500)
SET @RESULT = ((@PAGENUM-1) * @PAGESIZE)
SET @QUERY = 'SELECT TOP ' + CONVERT(VARCHAR(10), @PAGESIZE) +' * FROM '
SET @QUERY = @QUERY + ' T_BOARD_MAIN '
SET @QUERY = @QUERY + ' WHERE BOARD_MAIN_IDX > 10000000 '
SET @QUERY = @QUERY + ' AND BOARD_MAIN_IDX  NOT IN ('
SET @QUERY = @QUERY + ' SELECT TOP ' + CONVERT(VARCHAR(10),@RESULT) + ' BOARD_MAIN_IDX'
SET @QUERY = @QUERY + ' FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) '
SET @QUERY = @QUERY + ' ORDER BY BOARD_MAIN_IDX DESC '
--SELECT (@QUERY)
EXEC(@QUERY)

 

DROP PROC UP_LIST_BOARD3
EXEC UP_LIST_BOARD3 1, 2 

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

-- 출력용 매개변수 (C#의 out 키워드 역할 => OUTPUT)

CREATE PROC UP_TEST1

@PATH VARCHAR(100),

@FNAME VARCHAR(100) OUTPUT

AS

SET @FNAME = (@PATH) 

 

DECLARE @FNAME VARCHAR(100)
EXEC UP_TEST1 'AAA.TXT', @FNAME OUTPUT
SELECT (@FNAME)
----------------------------------------------------------------

-- OUTPUT TEST

CREATE PROC UP_LIST_BOARD4

@PAGE_SIZE VARCHAR(3),

@PAGE_NUM VARCHAR(3),

@OUTQ VARCHAR(300) OUTPUT

AS

DECLARE @QUERY VARCHAR(300)

SET @QUERY = 

'SELECT TOP (' + @PAGE_SIZE + ') * FROM T_BOARD_MAIN WHERE BOARD_MAIN_IDX > 10000000

AND BOARD_MAIN_IDX NOT IN(SELECT TOP(('+ @PAGE_NUM+'-1)*'+@PAGE_SIZE+') 

BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) ORDER BY BOARD_MAIN_IDX DESC'

SET @OUTQ = (@QUERY)

-- EXEC(@QUERY)

-- SELECT(@QUERY) 

 

DROP PROC UP_LIST_BOARD4

 

DECLARE @OUTQUERY VARCHAR(300)
EXEC UP_LIST_BOARD4 '1', '5', @OUTQUERY OUTPUT
SELECT @OUTQUERY
----------------------------------------------------------------

사용자가 글번호가 10000001인 녀석의

상세보기를 원했다면

-- 상세보기

SELECT * FROM T_BOARD_MAIN

WHERE BOARD_MAIN_IDX = 10000001

-- 코멘트 내용 가져오기

SELECT * FROM T_BOARD_COMMENT

WHERE BOARD_MAIN_IDX = 10000001

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

-------댓글 페이징 : BETWEEN 으로 내림차순만들기------------
-- BETWEEN A AND B
-- A : B - (페이지 사이즈 -1)
-- B : MAX(COMMENT) - 페이지 사이즈(N) * (페이지 갯수(K) -1) 

 

SELECT * FROM T_BOARD_COMMENT

WHERE BOARD_MAIN_IDX = 10000001--N=5, K=3

AND COMMENT_IDX 

BETWEEN -- (@M_COMMENT_IDX-N*(K-1)-(N-1)) AND (@M_COMMENT_IDX-N*(K-1))

(@M_COMMENT_IDX-1*(2-1)-(1-1)) AND (@M_COMMENT_IDX-1*(2-1))

ORDER BY COMMENT_IDX DESC 

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

------------NOT IN---------------------------------------------- 

 

SELECT TOP 10 *

FROM T_BOARD_COMMENT

WHERE  BOARD_MAIN_IDX = 10000001

 AND COMMENT_IDX  

NOT IN(SELECT TOP 20 COMMENT_IDX FROM T_BOARD_COMMENT  ORDER BY  COMMENT_IDX DESC)  

ORDER BY COMMENT_IDX DESC

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

SELECT COMMENT_IDX,COMMENT

FROM T_BOARD_COMMENT

WHERE BOARD_MAIN_IDX = 10000001

AND COMMENT_IDX BETWEEN (N*K-N+1) AND (N*K)