제목 : [2] 테이블(Table) 및 저장 프로시저(Stored Procedure)
|
글번호:
|
|
269
|
|
작성자:
|
|
레드플러스
|
|
작성일:
|
|
2008/07/09 오전 10:23:00
|
|
조회수:
|
|
6073
|
SQL Server DBMS를 열고,
아래 스크립트 구문을 사용하여, 테이블과 저장프로시저를
생성합니다.
--[0] 기본형 게시판(Upload)용 테이블 설계
--[!] Drop Table dbo.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/Mixed)
Homepage VarChar(100) Null, --홈페이지
ModifyDate SmallDateTime Null, --수정일
ModifyIP VarChar(15) Null, --수정IP
---
FileName VarChar(255) Null, --파일명
FileSize Int Default(0), --파일크기
DownCount Int Default(0) --다운수
)
Go
--[1] 입력 : Write.aspx
Insert Upload
Values
(
'홍길동',
'h@h.com',
'홍길동입니다.(냉무)',
GetDate(),
'127.0.0.1',
'안녕하세요.',
'1234',
0,
'Text',
'http://www.a.com/',
NULL, --널
'' --빈(Empty)
)
Go
--[2] 출력 : List.aspx
Select
Num, Name, Email,
Title, PostDate, ReadCount
From Upload --Join On
--Where
--Group By
--Having
Order By Num Desc
Go
--[3] 상세 : View.aspx
Select *
From Upload
Where Num = 5
Go
--[4] 수정 : Modify.aspx
Begin Tran
Update Upload
Set
Name = '백두산',
Email = 'b@b.com',
Homepage = 'http://b.com/',
Title = '새로운 제목',
Content = '<u>내용</u>',
Encoding = 'HTML',
ModifyDate = GetDate(),
ModifyIP = '127.0.0.1'
Where Num = 5
--RollBack Tran
Commit Tran
Go
--[5] 삭제 : Delete.aspx
Begin Transaction
Delete Upload
Where Num = 5
--RollBack Transaction
Commit Transaction
Go
--[6] 검색 : Search.aspx
Select *
From Upload
Where
Name Like '%홍길동%'
Or
Title Like '홍%'
Or
Content Like '%3'
Go
--[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] 조회수 증가시켜주는 저장 프로시저 : UpdateReadCount
Create Proc dbo.UpdateReadCountUpload
@Num Int
As
Update Upload
Set ReadCount = ReadCount + 1
Where Num = @Num
Go
--[10] 해당 글을 세부적으로 읽어오는 저장 프로시저 : ViewUpload
Create Procedure dbo.ViewUpload
@Num Int
As
Update Upload
Set ReadCount = ReadCount + 1
Where Num = @Num
Select *
From Upload
Where Num = @Num
Go
--[11] 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : ReadPassword
Create Proc dbo.ReadPasswordUpload
@Num Int
As
Select Password
From Upload
Where Num = @Num
Go
--[12] 해당 글 지우는 저장 프로시저 : DeleteUpload
Create 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] 해당 글을 수정하는 저장 프로시저 : 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 And Password = @Password
Else
Return -1 -- 암호가 틀리면 -1을 반환하자...
Go
--[14] 검색 저장 프로시저 : 동적 SQL문
Create Proc dbo.SearchUpload
@SearchField VarChar(25),
@SearchQuery VarChar(25)
As
Declare @strSql VarChar(150) -- 변수 선언
Set @strSql = '
Select * From Upload
Where '
+ @SearchField + ' Like ''%'
+ @SearchQuery + '%'' Order By Num Desc'
--Print @strSql
Exec (@strSql)
Go
SearchUpload ' 1 = 1; Drop Table Upload --', '메롱~'
Go
SearchUpload 'Name', '홍길동'
Go
--[12] 검색 저장 프로시저 : 정적 쿼리문
Alter Proc dbo.SearchUpload
@SearchField VarChar(25),
@SearchQuery VarChar(25)
As
Set @SearchQuery = '%' + @SearchQuery + '%'
SELECT *
FROM Upload
WHERE
(
CASE @SearchField
WHEN 'Name' THEN Name
WHEN 'Title' THEN Title
WHEN 'Content' THEN Content
ELSE
@SearchQuery
END
)
LIKE
@SearchQuery
Order By Num Desc
Go
--테스트URL : http://sample.redplus.net/WebUpload/Upload/List.aspx
--작성자 : 박용준(RedPlus)