제목 : 1. 답변형 게시판 테이블 설계(Reply.sql)
-- 답변형 게시판(Reply)용 테이블 설계
Create Table dbo.Reply
(
Num Int Identity(1, 1) Not Null Primary Key, --번호
Name VarChar(25) Not Null, --이름
Email VarChar(100) Null, --이메일
Title VarChar(150) Not Null, --제목
PostDate DateTime Default GetDate() Not Null, --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text Not Null, --내용
Password VarChar(20) Not Null, --비밀번호
ReadCount Int Default 0, --조회수
Encoding VarChar(10) Not Null, --인코딩(HTML/Text)
Homepage VarChar(100) Null, --홈페이지
ModifyDate DateTime Null, --수정일
ModifyIP VarChar(15) Null, --수정IP
----------
Ref Int Not Null, --참조(부모글)
Step Int Default 0, --답변깊이(레벨)
RefOrder Int Default 0 --답변순서
)
Go
--기본 SQL문 4가지 작성
Select * From Reply
Go
-- 답변 게시판(Reply)에 글을 작성하는 저장 프로시저 : procWriteReply
--Drop Proc dbo.procWriteReply
Create Proc dbo.procWriteReply
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
@Ref Int
As
--With Encryption
Insert Reply
(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref)
Values
(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding,
@Homepage, @Ref)
Go
-- 답변 게시판(Reply)에 글을 답변하는 저장 프로시저 : procWriteReply
--Drop Proc dbo.procReplyReply
Create Proc dbo.procReplyReply
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
@Ref Int,
@Step Int,
@RefOrder Int
As
--With Encryption
Insert Reply
(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref, Step, RefOrder)
Values
(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding,
@Homepage, @Ref, @Step, @RefOrder)
Go
-- 답변 게시판(Reply)에서 데이터를 읽어오는 저장 프로시저 : procListReply : 반드시 수정
--Drop Proc dbo.procListReply
Create Procedure dbo.procListReply
As
Select * From Reply Order By Ref Desc, RefOrder Asc
Go
-- 조회수 증가시켜주는 저장 프로시저 : procUpdateReadCount
Create Proc dbo.procUpdateReadCountReply
@Num Int
As
Update Reply Set ReadCount = ReadCount + 1 Where Num = @Num
Go
-- 해당 글을 세부적으로 읽어오는 저장 프로시저 : procViewReply
Create Procedure dbo.procViewReply
@Num Int
As
Select * From Reply Where Num = @Num
Go
-- 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : procReadPassword
Create Proc dbo.procReadPasswordReply
@Num Int
As
Select Password From Reply Where Num = @Num
Go
-- 해당 글 지우는 저장 프로시저 : procDeleteReply
Create Proc dbo.procDeleteReply
@Num Int
As
Delete Reply Where Num = @Num
Go
-- 해당 글을 수정하는 저장 프로시저 : procUpdateReply
Create Proc dbo.procUpdateReply
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@ModifyIP VarChar(15),
@ModifyDate DateTime,
@Content Text,
@Encoding VarChar(10),
@Homepage VarChar(100),
@Num Int
As
Update Reply
Set
Name = @Name,
Email = @Email,
Title = @Title,
ModifyIP = @ModifyIP,
ModifyDate = @ModifyDate,
Content = @Content,
Encoding = @Encoding,
Homepage = @Homepage
Where Num = @Num
Go