|
--================================================--
------- 자료실 게시판 응용 프로그램-------
--================================================--
/*================================================*/
--[0] 자료실 게시판(Upload)용 테이블 설계
Create Table dbo.Upload
(
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
---
FileName VarChar(255) Null, --파일명
FileSize Int Default(0), --파일크기
DownCount Int Default(0) --다운수
)
Go
select *From Upload
/*================================================*/
--[1] ~ [6] : 기본 SQL문 예시문 6가지 작성
Insert Into Values
/*================================================*/
--[7]~ : 6개 이상 로직을 처리하는 저장 프로시저 생성
/*================================================*/
--[7] 기본 게시판(Upload)에 글을 작성하는 저장 프로시저 :WriteUpload
Create Proc dbo.WriteUpload
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@Homepage VarChar(100),
@FileName VarChar(255),
@FileSize Int
--With Encryption
As
Insert Upload
(
Name, Email, Title, PostIP, Content, Password,
Encoding, Homepage, FileName, FileSize
)
Values
(
@Name, @Email, @Title, @PostIP, @Content,
@Password, @Encoding,
@Homepage, @FileName, @FileSize
)
Go
/*================================================*/
--[8] 기본 게시판(Upload)에서 데이터를 읽어오는 저장 프로시저 :ListUpload
Create Procedure dbo.ListUpload
As
Select * From Upload Order By Num Desc
Go
/*================================================*/
--[9] 해당 글을 세부적으로 읽어오는 저장 프로시저 :ViewUpload
Create Procedure dbo.ViewUpload
@Num Int
As
Update Upload
Set ReadCount = ReadCount + 1
Where Num = @Num
Select * From Upload Where Num = @Num
Go
/*================================================*/
--[10] 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 :ReadPassword
Create Proc dbo.ReadPasswordUpload
@Num Int
As
Select Password From Upload Where Num = @Num
Go
/*================================================*/
--[11] 해당 글을 수정하는 저장 프로시저 : ModifyUpload
--Create Proc dbo.ModifyUpload
-- @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 Upload
-- Set
-- Name = @Name,
-- Email = @Email,
-- Title = @Title,
-- ModifyIP = @ModifyIP,
-- ModifyDate = @ModifyDate,
-- Content = @Content,
-- Encoding = @Encoding,
-- Homepage = @Homepage
-- Where Num = @Num
--Go
/*================================================*/
--[11] 해당 글을 수정하는 저장 프로시저 : ModifyUpload 수정
Create Proc dbo.ModifyUpload
@Name VarChar(25),
@Email VarChar(100),
@Title VarChar(150),
@ModifyIP VarChar(15),
@Content Text,
@Encoding VarChar(10),
@Homepage VarChar(100),
@Password VarChar(20), -- <-- 추가
@FileName VarChar(255), -- <-- 추가
@FileSize Int, -- <-- 추가
@Num Int
As
Declare @cnt Int
Select @cnt = Count(*) From Upload
Where Num = @Num And Password = @Password
If @cnt > 0 -- 넘겨져 온 번호와 암호가 맞는 데이터가 있다면...
Update Upload
Set
Name = @Name,
Email = @Email,
Title = @Title,
ModifyIP = @ModifyIP,
ModifyDate = GetDate(),
Content = @Content,
Encoding = @Encoding,
Homepage = @Homepage, FileName = @FileName, FileSize = @FileSize
Where Num = @Num
Else
Return -1 -- 암호가 틀리면 -1을 반환하자..
Go
/*================================================*/
--[12] 해당 글 지우는 저장 프로시저 :DeleteUpload
Create Proc dbo.DeleteUpload
@Num Int
As
Delete Upload Where Num = @Num
Go
Alter Proc dbo.DeleteUpload
@Password VarChar(20),
@Num Int
As
Declare @cnt Int
-- 암호와 번호가 맞으면 1을 반환
Select @cnt = Count(*) From Upload
Where Num = @Num And Password = @Password
If @cnt > 0
Delete Upload Where Num = @Num And Password = @Password
Else
Return -1
Go
/*================================================*/
--[13] 검색 : SearchUpload
Create Procedure dbo.SearchUpload
@SearchField VarChar(25),
@SearchQuery VarChar(25)
As
Declare @strSql VarChar(250)
Set @strSql = '
Select * From Upload
Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%''
Order By Num Desc '
--Print(@strSql)
Exec(@strSql)
Go
SearchUpload 'FileName', 'gif'
Go
/*================================================*/
--[14] 다운로드 카운트 증가 : IncreaseDownCountUpload
Alter Proc dbo.IncreaseDownCountUpload
@filename Int
As
Update Upload
Set DownCount = DownCount + 1
Where filename = @filename
Go |
WebUpload 자료실.zip