.Net Project & 재태크 재무 관리

 --[1]상품등록 : ProductAdd.aspx에서 사용
--ProductsAdd : Products 테이블에 데이터 이입력 후
--현재 입력된 레코드의 ProductID값을 반환
use Market 
Create Procedure dbo.ProductsAdd
                @CategoryID Int,
                @ModelNumber VarChar(50),
                @ModelName VarChar(50),
                @Company VarChar(50),
                @OriginPrice Int,
                @SellPrice Int,
                @EventName VarChar(50),
                @ProductImage VarChar(50),
                @Explain VarChar(400), --Text
                @Description VarChar(8000),
                @Encoding VarChar(10),
                @ProductCount Int,
                @Mileage Int,
                @Absence Int, 
                @ProductID Int OUTPUT --Output 매겨변수
As

 Insert Products ( CategoryID, ModelNumber,  ModelName,
        Company, OriginPrice, SellPrice,
        EventName, ProductImage, Explain,
        Description, Encoding, ProductCount,
        Mileage, Absence
  )
  Values ( @CategoryID, @ModelNumber, @ModelName,
        @Company, @OriginPrice, @SellPrice,
        @EventName, @ProductImage,  @Explain,
        @Description, @Encoding, @ProductCount,
        @Mileage, @Absence
  )

--output 매개변수인 ProductID에 마지막 Identity값 반환
  Select @ProductID = @@IDENTITY
  Go

Declare @ProductID Int
Exec ProductsAdd 1, 'COM-01','최고컴퓨터','우리집',3000,2000,'HIT',
'COM-01.jpg','i7 CPU 사용',  '어쩌구 저쩌구 ~^^;;', 'Text', 1000,0,0,
@ProductID Output
 
Select @ProductID 

 
--[2] 상품 카테고리 리스트 : CategoryList.aspx 에서 사용
Create Procedure ProductCategoryList
As
   Select
          CategoryID,
          CategoryName
   From 
          Categories
   Order By 
    
   CategoryName Asc    
Go

--[!]테스트 : 모든 카테고리 리스트 출력
Execute ProductCategoryList
Go

--[3] 카테고리에 따른 상품리스트 : ProductsList.aspx에서 사용
Create Procedure ProductsByCategory
(
         @CategoryID Int
)    
As
    Select
            ProductID,
            ModelName,
            SellPrice,
            ProductImage
    From
            Products
    Where
           CategoryID = @CategoryID
    Order By 
   
    ModelName,
    ModelNumber   
Go

--[4] 상품 상세 설명
Create Procedure ProductDetail
(
          @ProductID Int,
          @ModelNumber VarChar(50) Output,
          @ModelName VarChar(50) Output,
          @Company VarChar(50) Output, 
          @ProductImage VarChar(50) Output,
          @OriginPrice Int Output,
          @SellPrice Int Output,
          @Description NVarChar(4000) Output,
          @ProductCount Int Output
)

As
 Select
      @ProductID = ProductID,
      @ModelNumber = ModelNumber,
      @ModelName = ModelName,
      @Company = Company,
      @ProductImage = ProductImage,
      @OriginPrice = originPrice,
      @SellPrice = SellPrice,
      @Description = --Description,
         CONVERT(NVarChar, Description),
      @ProductCount = ProductCount
 From
        Products 
 Where
        ProductID = @ProductID
Go

 
--[!]전체 상품 리스트 : ProductPages.ascs에서 사용
create Proc dbo.GetProducts --SQL Server 2005 사용
         @Page Int -- 1페이지, 2페이지와 같이 보여줄 페이지 번호를 넘겨준다
As
 --아래 구문은 최근 제품에 대해서 페이징 처리를 해서 보여줌
 --인터넷을 활용해서 더 많은 성능향상 페이징 로직 검색 권장 
 
Select Top 10 *From Products --한페이지에 10개 레코드 출력 기본
  Where
        ProductID Not In
        (
          Select Top (10 * @Page) ProductID From Products
          Order By ProductID Desc
        )
        Order By ProductID Desc  
        Go 

--GetProducts 저장 프로시저를 동적 쿼리문으로 변경
Alter Proc dbo.GetProducts --SQL Server 200이상 공용 
       @Page Int
As
    Declare @strSql VarChar(1000)
    Set @strSql = ' 
    Select Top 10 * From Products
    Where 
             ProductID Not In
             (
                    Select Top ' + CAST((10 * + @Page) As VarChar)
                     + ' ProductID From Products Order By ProductID Desc)
                     Order By ProductID Desc
                 '
    Exec(@strSql)
Go

결론 : 동적쿼리문 사용하지 않고 정적 쿼리문 저장 프로시저 이용하며,
        동적 SQL200사용시 사용되며, 정적쿼리문은 2008,2005, 모두 허용 및 사용
        (위의 쿼리문(정적) 사용)

 
--[5]상품평 리스트 : ReviewList.ascx에서 사용
Create Procedure ReviewsList
(
         @ProductID Int
)
As
   Select 
       ReviewID,
       CustomerName,
       Rating,
       Comments,
       AddDate
   From
       Reviews
 Where
       ProductID = @ProductID
Go

--[6] 상품평추가 : ReviewList.ascx에서 사용
Create Procedure ReviewsAdd
(
        @ProductID Int,
        @CustomerName VarChar(50),
        @CustomerEmail VarChar(50),
        @Rating Int,
        @Comments VarChar(3850),
        @ReviewID Int Output
)  

As
 Insert Into Reviews
 (
      ProductID,
      CustomerName,
      CustomerEmail,
      Rating,
      Comments
 )
 Values
 (
     @ProductID,
     @CustomerName,
     @CustomerEmail,
     @Rating,
     @Comments 
 )
 
 Select @ReviewID = @@IDENTITY
Go

--[7] 쇼핑카트 아이템 추가하기 : AddToCart.aspx에서 사용
Create Procedure ShoppingCartAddItem
(
     @CartID VarChar(50), --누가 
     @ProductID Int,  --어떤제품을
     @Quantity Int --장바구니에 담는지 ??
)
As
 --(1) 내가 보고 있는 제품이 이미 담아 있는지 확인
 Declare @CountItems Int
 
 Select 
       @CountItems = COUNT(ProductID)
 From
       ShoppingCart
 Where
        ProductID = @ProductID --장바구니에 담으려는 제품
 And
       CartID = @CartID --현재 접속자 : 회원 또는 비회원
 
 --(2) 이미 해당 제품이 담겨져 있다면, 수정
 IF @CountItems > 0
  Update
         ShoppingCart
  Set
         Quantity = (@Quantity + ShoppingCart.Quantity)
  Where
         ProductID = @ProductID
  And
         CartID = @CartID 

 --[3] 처음 구입 하는 제품이라면, 입력
 Else      
  Insert Into ShoppingCart
  (
      CartID, 
      ProductID, 
      Quantity
  )   
     Values
     (
        @CartID,
        @ProductID,
        @Quantity
    )
Go

 use Market
Create Table dbo.OrderDetails
(
 OrderID Int Not Null, --주문번호(고유일련번호)
 ProductID Int Not Null, --상품번호
 Quantity Int Not Null, --주문수량
 SellPrice Int NOt Null, --주문단가
 Price Int Null, --주문금액

 Mileage Int Null, --마일리지
 --복합키(2개의 컬럼을 기본키로 설정)
 Primary Key(OrderID, ProductID) --이러한 방식도 존재하네?
)
Go

--모두출력
Select *From OrderDetails

 use Market
Create Table dbo.ShoppingCart
(
 RecordID Int Identity(1,1) Primary Key Not Null, --쇼핑카트 고유번호
 CartID VarChar(100), --카트번호(고객구분번호) : 유일한 값(랜덤)
 --상품명, 가격, 마일리지 등....
 ProductID Int Not Null References Products(ProductID), --상품 고유번호
 Quantity Int Not Null, --수량
 DateCreated DateTime --카트 생성일 (주문날짜)
  Default(GetDate())
)
Go
--유일한 값을 만들어 내는 함수
--Select NEWID()

--[!] 예시 데이터 입력
--[A] 회원으로 로그인한 상태에서 3번 제품을 1개 장바구니 담기 :
   1번 회원이라면...
Insert Into ShoppingCart Values('1',3,1,GETDATE())
Go
--[B]비회원으로 2번 제품을 5개 장바구니 담기
Insert Into ShoppingCart Values('566C',2,5,GETDATE())
Go
Insert Into ShoppingCart Values('566CABVC',2,5,GETDATE())
Go
Insert Into ShoppingCart Values('566CDACF',2,5,GETDATE())
Go
--전체출력
Select *From ShoppingCart
Go

--특정 리스트 출력(1번고객이 2번 제품을 5개 구입 의미)
Declare @ShoppingCart Int
Set @ShoppingCart = 1
Select *From ShoppingCart Where RecordID = @ShoppingCart Order By RecordID Desc
Go

 use Market
Create Table dbo.Customers
(
 CustomerID Int Identity(1,1) Not Null Primary Key, --고객 고유번호
 CustomerName VarChar(50), --고객명(회원/비회원)
 Phone1 VarChar(4), --전화번호1
 Phone2 VarChar(4), --전화번호2
 Phone3 VarChar(4), --전화번호3
 Mobile1 VarChar(10), --휴대폰1
 Mobile2 VarChar(10), --휴대폰2
 Mobile3 VarChar(10), --휴대폰3
 zip VarChar(7) Null, --우편번호
 [Address] VarChar(100) Null, --주소
 AddressDetail VarChar(100) Null, --주소상세
 Ssn1 Char(6) Null, --주민번호 앞자리
 Ssn2 Char(7) Null, --주민번호 뒷자리
 EmailAddress VarChar(50), --이메일
 MemberDivision Int --회원구분
)
Go
--예시 데이터 입력
Insert Into Customers Values (
  '김용원','032','123','1234','010','123','1234','404-215','경기도 광명시
   광명7동','1234번지','900214','1024010','fool8585@naver.com',1)

--전체출력
Select *From Customers
Go 

 use Market
Create Table dbo.MemberShip
(
 CustomerID Int Primary Key, --고객코드(회원번호)
 UserID VarChar(25) Not Null, --아이디
 [Password] VarChar(100) Not Null, --비밀번호
 BirthYear VarChar(4) Not Null, --년
 BirthMonth VarChar(2)  Not Null, --월
 BirthDay VarChar(2)  Not Null,--일
 BirthStatus VarChar(2)  Not Null, --생일구분
 Gender Int Null, --성별
 Job VarChar(20) Null, --직업
 Wedding Int Null, --결혼여부
 Hobby VarChar(100) Null, --취미
 Homepage VarChar(100) Null, --홈페이지
 Intro VarChar(400) Null, --소개
 Mailing Int Null, --메일 수신 여부
 VisitCount Int Default 0, --방문 횟수
 LastVisit DateTime Default GetDate(), --마지막 방문 일수
 Mileage Int Null, --마일리지
 JoinDate DateTime Default GetDate() --가입일자
)
Go

use Market

Create Table dbo.zip
(
 ZipCode NVarChar(8) Not Null, --우편번호(429-325)
 Si NVarChar(150)Null, --시도(경기도)
 Gu NVarChar(150) Null, --구군(광명시)
 Dong NVarChar(255) Null, --동면(광명동)
 PostEtc NVarChar(255) Null --나머지(123번지)

)
Go
--[!!]예시문
Insert Into zip values ('423-017','경기도','광명시','광명7동','747-12')
Go
--전체출력
Select *From MemberShip --저장 프로시저 이용
Select *From zip order By ZipCode Asc

-- [!] Zip 테이블의 Dong필드에 클러스터드 인덱스 생성
Create Clustered Index idxZip On Zip(Dong)
Go

--우편번호 테이블에서 '동이름'으로 검색
Select *From zip Where Dong Like '%역삼%'
Go
Select *From zip Where Dong Like '역삼%'
Go
Select *From zip Where Dong Like '%역삼'
Go

--Index(인덱스)를 적용하여 쿼리 실행 능력 향상
--Select *From zip Where Dong Like '%역삼%'
--Go
--Select *From zip Where Dong Like '역삼%'
--Go

--[!]Zip 테이블의 Dong필드에 클러스트 인뎃스 생성
Create Clustered Index idxZip On Zip(Dong)
Go
--[!] 아래 2개의 쿼리문의 속도 비교
--Select *From Zip Where Dong Like '역삼%' 
--Go
--Select *From Zip Where Dong Like '역삼%' 
--Go