C# 포트폴리오 도전기(1-8. DB, Table, Proceduer)

min seung moon·2021년 8월 25일
0

C# 포트폴리오

목록 보기
9/10
post-custom-banner

1. DB


2. Table

-- 사용자 정보 테이블
CREATE TABLE Book_User_Info(
	user_no			INT		NOT NULL	PRIMARY KEY	IDENTITY(1000,1)
	,user_name		NVARCHAR(20)	NULL
	,user_address		NVARCHAR(50)	NULL
	,user_birth		DATETIME	NULL
);

-- 사용자 로그인 정보 테이블
CREATE TABLE Book_User_LogIn(
	user_no			INT			NOT NULL	PRIMARY KEY	IDENTITY(1000,1)
	,user_id		NVARCHAR(20)		NULL
	,user_pw		CHAR(64)		NULL
	,register_date		DATETIME		NULL
	,deregister_date	DATETIME		NULL
	,authority_no		INT			NULL
	,grade_no		INT			NULL
);

-- 사용자 권한 테이블
-- 0 : admin, 1 : staff, 2 : 손님
CREATE TABLE Book_User_Auth(
	authority_no		INT		NOT NULL	PRIMARY KEY
	,authority_name		NVARCHAR(10)	NULL
);

-- 사용자 등급 테이블
-- 0 : General Member, 1 : excellent member, 2 : best member
CREATE TABLE Book_User_Grade(
	grade_no		INT		NOT NULL	PRIMARY KEY
	,grade_name		NVARCHAR(5)	NULL
);

INSERT INTO Book_User_Auth VALUES(0, 'admin');
INSERT INTO Book_User_Auth VALUES(1, 'staff');
INSERT INTO Book_User_Auth VALUES(2, '손님'); -- 영어로 변경해주세요! 비공개 되서 한글로 올립니다
SELECT * FROM Book_User_Auth;
INSERT INTO Book_User_Grade VALUES(0, '일반회원');
INSERT INTO Book_User_Grade VALUES(1, '우수회원');
INSERT INTO Book_User_Grade VALUES(2, '최우수회원');
SELECT * FROM Book_User_Grade;

INSERT INTO Book_User_Info(user_name, user_address, user_birth) VALUES('문민승', '대구 남구', '1997.02.05');
SELECT * FROM Book_User_Info;

INSERT INTO Book_User_LogIn(user_id, user_pw, register_date, authority_no, grade_no) VALUES('admin', CONVERT(varbinary(256), '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918'), GETDATE(), 0, 2); -- admin
SELECT * FROM Book_User_LogIn;

-- 사용자 로그 테이블
CREATE TABLE Book_User_Log(
	user_no				INT				NULL
	,user_id			NVARCHAR(20)	NULL
	,enter_ip			NVARCHAR(20)	NULL
	,enter_date			DATETIME		NULL
);
-- 책 정보 테이블
CREATE TABLE Book_Book_Info(
	book_no				INT		NOT NULL	PRIMARY KEY	IDENTITY(10000,1)
	,book_title			NVARCHAR(30)	NULL
	,book_writer			NVARCHAR(20)	NULL
	,book_category			INT		NULL
	,book_description		TEXT		NULL
	,book_price			INT		NULL
	,book_company			NVARCHAR(20)	NULL
	,book_date			DATETIME	NULL
	,book_pages			INT		NULL
	,book_originalImagePath		NVARCHAR(MAX)	NULL
	,book_saveImagePath		NVARCHAR(MAX)	NULL
	,book_rentalcounts		INT		NULL	DEFAULT 0
);

-- 한국소설, 외국소설, 시/에세이, 경제/경영, 자기개발, 인문학, 컴퓨터/IT, 건강, 취미
CREATE TABLE Book_Book_Category(
	category_no	INT		NOT NULL	PRIMARY KEY	IDENTITY(1,1)
	,category_name	NVARCHAR(10)	NULL
);

-- 책 상태 테이블
CREATE TABLE Book_Book_State(
	book_no		INT		NULL
	,book_title	NVARCHAR(30)	NULL
	,book_location	INT		NULL
	,book_rental	INT		NULL
);

-- 1 재고, 2 렌탈 중
CREATE TABLE Book_Book_RentalState(
	rental_no	INT		NOT NULL	PRIMARY KEY	IDENTITY(1,1)
	,rental_name	NVARCHAR(5)	NULL
);

-- [A]한국소설, [B]외국소설, [C]시/에세이, [D]경제/경영, [E]자기개발, [F]인문학, [G]컴퓨터/IT, [H]건강, [I]취미
CREATE TABLE Book_Book_Location(
	location_no	INT		NOT NULL	PRIMARY KEY	IDENTITY(1,1)
	,location_name	NVARCHAR(10)	NULL
);

-- 책렌탈 테이블
CREATE TABLE Book_Book_Rental(
	book_no		INT		NULL
	,book_title	NVARCHAR(30)	NULL
	,rental_date	DATETIME	NULL
	,return_date	DATETIME	NULL
);

3. 로그인 및 사용자 Procudure

01. BOOK_LOGIN_S1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S1]    Script Date: 2021-08-25 오전 11:18:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.19,>
-- Description:	<도서관리 프로그램 로그인>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S1]
	@USER_ID	NVARCHAR(20)
	,@USER_PW	CHAR(64)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    IF NOT EXISTS
	(
		SELECT	user_no
		FROM	Book_User_LogIn
		WHERE	user_id	=	@USER_ID
		AND		user_pw	=	@USER_PW
	)
	BEGIN
		RAISERROR('존재하지 않는 사용자 입니다.',16,1)
		RETURN
	END

	SELECT	l.user_no, i.user_name, i.user_address, i.user_birth, l.user_id, l.user_pw, l.register_date, a.authority_no, g.grade_no
	FROM			Book_User_LogIn	as l
	INNER JOIN		Book_User_Info	as i
	ON				l.user_no = i.user_no
	INNER JOIN		Book_User_Auth	as a
	ON				l.authority_no = a.authority_no
	INNER JOIN		Book_User_Grade	as g
	ON				l.grade_no = g.grade_no
	WHERE	user_id	=	@USER_ID
	AND		user_pw	=	@USER_PW
END

02. BOOK_LOGIN_S2

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S2]    Script Date: 2021-08-25 오전 11:21:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.19>
-- Description:	<도서관리 프로그램 아이디 찾기>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S2]
	@USER_NO	INT
	, @USER_NAME	NVARCHAR(20)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    IF NOT EXISTS
	(
		SELECT		l.user_id
		FROM		Book_User_Info	as i
		INNER JOIN	Book_User_LogIn	as l
		ON			i.user_no = l.user_no
		WHERE		i.user_no = @USER_NO
		AND			i.user_name	=	@USER_NAME
	)
	BEGIN
		RAISERROR('존재하지 않는 사용자 입니다.',16,1)
		RETURN
	END

	SELECT		l.user_id
	FROM		Book_User_Info	as i
	INNER JOIN	Book_User_LogIn	as l
	ON			i.user_no = l.user_no
	WHERE		i.user_no = @USER_NO
	AND			i.user_name	=	@USER_NAME
END

03. BOOK_LOGIN_S3

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S3]    Script Date: 2021-08-25 오전 11:22:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 전체 사용자 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S3]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT	l.user_no as '사용자번호', i.user_name as '사용자이름', i.user_address as '사용자주소', CONVERT(char(10), i.user_birth, 102) as '생년월일'
			, l.user_id as '아이디', l.user_pw as '비밀번호' , l.register_date as '가입일', l.deregister_date as '탈퇴일'
			, a.authority_name  as '권한', g.grade_name as '등급'
	FROM			Book_User_LogIn	as l
	INNER JOIN		Book_User_Info	as i
	ON				l.user_no = i.user_no
	INNER JOIN		Book_User_Auth	as a
	ON				l.authority_no = a.authority_no
	INNER JOIN		Book_User_Grade	as g
	ON				l.grade_no = g.grade_no
	ORDER BY		l.user_no;
END

04. BOOK_LOGIN_S4

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S4]    Script Date: 2021-08-25 오전 11:22:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 권한 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S4]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT * FROM Book_User_Auth;
END

05. BOOK_LOGIN_S5

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S5]    Script Date: 2021-08-25 오전 11:23:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08-20>
-- Description:	<도서관리프로그램, 등급 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S5]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT * FROM Book_User_Grade;
END

06. BOOK_LOGIN_S6

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_S6]    Script Date: 2021-08-25 오전 11:23:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리 프로그램, 사용자번호로 사용자 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_S6]
	@USER_NO		INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT	l.user_no, i.user_name, i.user_address, CONVERT(char(10), i.user_birth, 102), l.user_id, l.user_pw, l.register_date, l.deregister_date, a.authority_no, g.grade_no
	FROM			Book_User_LogIn	as l
	INNER JOIN		Book_User_Info	as i
	ON				l.user_no = i.user_no
	INNER JOIN		Book_User_Auth	as a
	ON				l.authority_no = a.authority_no
	INNER JOIN		Book_User_Grade	as g
	ON				l.grade_no = g.grade_no
	WHERE			l.user_no	=	@USER_NO;
END

07. BOOK_LOGIN_I1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_I1]    Script Date: 2021-08-25 오전 11:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.19>
-- Description:	<도서관리프로그램, 로그인 로그>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_I1]
	@USER_NO	INT
	,@USER_ID	NVARCHAR(20)
	,@ENTER_IP	NVARCHAR(20)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	INSERT INTO Book_User_Log VALUES(@USER_NO, @USER_ID, @ENTER_IP, GETDATE());
END

08. BOOK_LOGIN_I2

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_I2]    Script Date: 2021-08-25 오전 11:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 사용자 등록>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_I2]
	@USER_NAME		NVARCHAR(20)
	,@USER_ADDR		NVARCHAR(50)
	,@USER_BIRTH	DATETIME
	,@USER_ID		NVARCHAR(20)
	,@USER_PW		NVARCHAR(20)
	,@USER_AUTH		INT
	,@USER_GRADE	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	INSERT INTO Book_User_Info(user_name, user_address, user_birth) VALUES(@USER_NAME, @USER_ADDR, @USER_BIRTH);
	INSERT INTO Book_User_LogIn(user_id, user_pw, register_date, authority_no, grade_no) VALUES(@USER_ID, CONVERT(varbinary(256), @USER_PW), GETDATE(), @USER_AUTH, @USER_GRADE); -- admin
END

09. BOOK_LOGIN_U1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_U1]    Script Date: 2021-08-25 오전 11:24:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.19>
-- Description:	<도서관리 프로그램 비밀번호 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_U1]
	@USER_NO	INT
	,@USER_ID	NVARCHAR(20)
	,@NEW_PW	CHAR(64)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    IF NOT EXISTS
	(
		SELECT		user_no
		FROM		Book_User_LogIn
		WHERE		user_no = @USER_NO
		AND			user_id	= @USER_ID
	)
	BEGIN
		RAISERROR('존재하지 않는 사용자 입니다.',16,1)
		RETURN
	END

	UPDATE	Book_User_LogIn 
	SET		user_pw = @NEW_PW
	WHERE	user_no = @USER_NO
	AND		user_id = @USER_ID;
END

10. BOOK_LOGIN_U2

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_U2]    Script Date: 2021-08-25 오전 11:25:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 사용자 정보 수정>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_U2]
	@USER_NO		INT
	,@USER_NAME		NVARCHAR(20)
	,@USER_ADDR		NVARCHAR(50)
	,@USER_BIRTH	NVARCHAR(20)
	,@USER_ID		NVARCHAR(20)
	,@AUTH_NO		INT
	,@GRADE_NO		INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	
	UPDATE	Book_User_Info
	SET		user_name = @USER_NAME
			,user_address = @USER_ADDR
			,user_birth = @USER_BIRTH
	WHERE	user_no = @USER_NO;

	IF(@AUTH_NO = 3)
		UPDATE	Book_User_LogIn
		SET		user_id = @USER_ID
				,authority_no = @AUTH_NO
				,grade_no = @GRADE_NO
				,deregister_date = GETDATE()
		WHERE	user_no = @USER_NO;
	ELSE
		UPDATE	Book_User_LogIn
		SET		user_id = @USER_ID
				,authority_no = @AUTH_NO
				,grade_no = @GRADE_NO
				,deregister_date = NULL
		WHERE	user_no = @USER_NO;
	
END

11. BOOK_LOGIN_D1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_LOGIN_D1]    Script Date: 2021-08-25 오전 11:26:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 사용자 삭제>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_LOGIN_D1]
	@USER_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DELETE FROM Book_User_LogIn WHERE user_no = @USER_NO;
	DELETE FROM Book_User_Info WHERE use_no = @USER_NO;
END

4. 도서 Procedure

01. BOOK_BOOK_S1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S1]    Script Date: 2021-08-25 오전 11:26:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 전체 도서 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S1]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT I.book_no as '책 번호', I.book_title as '책 제목', I.book_writer as '책 저자', C.category_name as '카테고리'
		, I.book_description as '책 설명', I.book_price as '가격', I.book_company as '출판사', I.book_date as '출판일'
		, I.book_pages as '총 페이지수', I.book_originalImagePath as '원본 이미지 경로', I.book_saveImagePath as '저장 이미지 경로'
		, I.book_rentalcounts as '총 렌탈 횟수', RS.rental_name as '렌탈 상태', L.location_name AS '책 위치'
	FROM			Book_Book_Info			AS I
	INNER	JOIN	Book_Book_Category		AS C
	ON				I.book_category = C.category_no
	INNER	JOIN	Book_Book_State			AS S
	ON				I.book_no = S.book_no
	INNER	JOIN	Book_Book_RentalState	AS RS
	ON				S.book_rental = RS.rental_no
	INNER	JOIN	Book_Book_Location		AS L
	ON				S.book_location = L.location_no;
END

02. BOOK_BOOK_S2

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S2]    Script Date: 2021-08-25 오전 11:27:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.20>
-- Description:	<도서관리프로그램, 카테고리 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S2]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT * FROM Book_Book_Category;
END

03. BOOK_BOOK_S3

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S3]    Script Date: 2021-08-25 오전 11:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 도서 위치 리스트>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S3]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT * FROM Book_Book_Location;
END

04. BOOK_BOOK_S4

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S4]    Script Date: 2021-08-25 오전 11:27:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 도서 렌탈 상태 리스트>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S4]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT * FROM Book_Book_RentalState;
END

05. BOOK_BOOK_S5

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S5]    Script Date: 2021-08-25 오전 11:28:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 특정 도서 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S5]
	@BOOK_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT I.book_no, I.book_title, I.book_writer, C.category_no
		, I.book_description, I.book_price, I.book_company, CONVERT(char(10), I.book_date, 102)
		, I.book_pages, I.book_originalImagePath, I.book_saveImagePath
		, RS.rental_no, L.location_no
	FROM			Book_Book_Info			AS I
	INNER	JOIN	Book_Book_Category		AS C
	ON				I.book_category = C.category_no
	INNER	JOIN	Book_Book_State			AS S
	ON				I.book_no = S.book_no
	INNER	JOIN	Book_Book_RentalState	AS RS
	ON				S.book_rental = RS.rental_no
	INNER	JOIN	Book_Book_Location		AS L
	ON				S.book_location = L.location_no
	WHERE	I.book_no = @BOOK_NO;
END

06. BOOK_BOOK_S6

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_S6]    Script Date: 2021-08-25 오전 11:28:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 메인화면에 보여줄 랜덤한 책 정보>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S6]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	IF NOT EXISTS
	(
		SELECT		TOP 1 book_no, book_title, book_writer, category_name, book_description, book_rentalcounts, book_saveImagePath
		FROM		Book_Book_Info I
		INNER JOIN	Book_Book_Category C
		ON			I.book_category = C.category_no
		ORDER BY	NEWID()
	)
	BEGIN
		RAISERROR('존재하지 않는 데이터 입니다.',16,1)
		RETURN
	END

	SELECT		TOP 1 book_no, book_title, book_writer, category_name, book_description, book_rentalcounts, book_saveImagePath
	FROM		Book_Book_Info I
	INNER JOIN	Book_Book_Category C
	ON			I.book_category = C.category_no
	ORDER BY	NEWID();
END

07. BOOK_BOOK_I1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_I1]    Script Date: 2021-08-25 오전 11:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 도서 등록>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_I1]
	@BOOK_TITLE			NVARCHAR(30)
	,@BOOK_WRITER		NVARCHAR(20)
	,@BOOK_CATEGORY		INT
	,@BOOK_DESCRIPTION	TEXT
	,@BOOK_PRICE		INT
	,@BOOK_COMPANY		NVARCHAR(20)
	,@BOOK_DATE			DATETIME
	,@BOOK_PAGES		INT
	,@BOOK_ORIGINALPATH	NVARCHAR(MAX)
	,@BOOK_SAVEPATH		NVARCHAR(MAX)
	,@BOOK_LOCATION		INT
	,@BOOK_STATE		INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	INSERT INTO Book_Book_Info(book_title, book_writer, book_category, book_description, book_price, book_company, book_date, book_pages, book_originalImagePath, book_saveImagePath)
	VALUES (@BOOK_TITLE
			, @BOOK_WRITER
			, @BOOK_CATEGORY
			, @BOOK_DESCRIPTION
			, @BOOK_PRICE
			, @BOOK_COMPANY
			, @BOOK_DATE
			, @BOOK_PAGES
			, @BOOK_ORIGINALPATH
			, @BOOK_SAVEPATH);

	DECLARE	@BOOK_INDEX INT;

	SELECT	@BOOK_INDEX = book_no 
	FROM	Book_Book_Info 
	WHERE	book_title = @BOOK_TITLE 
	AND		book_saveImagePath = @BOOK_SAVEPATH;

	INSERT INTO Book_Book_State VALUES(@BOOK_INDEX, @BOOK_TITLE, @BOOK_LOCATION, @BOOK_STATE);

	IF(@BOOK_STATE = 2)
		INSERT INTO Book_Book_Rental(book_no, book_title, rental_date)  
		VALUES(
			@BOOK_INDEX
			,@BOOK_TITLE
			,GETDATE()
		);
		UPDATE Book_Book_Info SET book_rentalcounts = book_rentalcounts + 1 WHERE book_no = @BOOK_INDEX;
END

08. BOOK_BOOK_U1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_U1]    Script Date: 2021-08-25 오전 11:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 도서 수정>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_U1]
	@BOOK_NO			INT
	,@BOOK_TITLE		NVARCHAR(30)
	,@BOOK_WRITER		NVARCHAR(20)
	,@BOOK_CATEGORY		INT
	,@BOOK_DESCRIPTION	TEXT
	,@BOOK_PRICE		INT
	,@BOOK_COMPANY		NVARCHAR(20)
	,@BOOK_DATE			DATETIME
	,@BOOK_PAGES		INT
	,@BOOK_ORIGINALPATH	NVARCHAR(MAX)
	,@BOOK_SAVEPATH		NVARCHAR(MAX)
	,@BOOK_LOCATION		INT
	,@BOOK_STATE		INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	UPDATE	Book_Book_Info
	SET		book_title = @BOOK_TITLE
			,book_writer = @BOOK_WRITER
			,book_category = @BOOK_CATEGORY
			,book_description = @BOOK_DESCRIPTION
			,book_price = @BOOK_PRICE
			,book_company = @BOOK_COMPANY
			,book_date = @BOOK_DATE
			,book_pages = @BOOK_PAGES
			,book_originalImagePath = @BOOK_ORIGINALPATH
			,book_saveImagePath = @BOOK_SAVEPATH
	WHERE	book_no = @BOOK_NO;

	UPDATE	Book_Book_State
	SET		book_title	= @BOOK_TITLE
			,book_location = @BOOK_LOCATION
			,book_rental = @BOOK_STATE
	WHERE	book_no = @BOOK_NO;

	DECLARE @COUNT INT;
	SELECT @COUNT = COUNT(*)  FROM Book_Book_Rental WHERE book_no = 10003;

	IF(@BOOK_STATE = 2 AND @COUNT <= 0)
		INSERT INTO Book_Book_Rental(book_no, book_title, rental_date)  
		VALUES(
			@BOOK_NO
			,@BOOK_TITLE
			,GETDATE()
		);
		UPDATE Book_Book_Info SET book_rentalcounts = book_rentalcounts + 1 WHERE book_no = @BOOK_NO;
	
	IF(@BOOK_STATE = 1 AND @COUNT > 0)
		UPDATE	Book_Book_Rental
		SET		return_date = GETDATE();
		
END

09. BOOK_BOOK_D1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_BOOK_D1]    Script Date: 2021-08-25 오전 11:30:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.23>
-- Description:	<도서관리프로그램, 특정 도서 삭제>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_D1]
	@BOOK_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DELETE FROM Book_Book_Info WHERE book_no = @BOOK_NO;
	DELETE FROM Book_Book_State WHERE book_no = @BOOK_NO;
	DELETE FROM Book_Book_Rental WHERE book_no = @BOOK_NO;
END

5. 렌탈 Procedure

01. BOOK_RENTAL_S1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S1]    Script Date: 2021-08-25 오전 11:31:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈 리스트 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S1]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT		r.user_no as '사용자번호', i.user_name as '사용자이름', r.book_no as '도서번호'
				,r.book_title as '도서명', r.rental_date as '렌탈날짜', r.return_date as '반납날짜'
	FROM		Book_Book_Rental	r
	INNER JOIN	Book_User_Info		i
	ON			r.user_no = i.user_no
	WHERE		r.return_date is null;
END

02. BOOK_RENTAL_S2

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S2]    Script Date: 2021-08-25 오전 11:31:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 연체자 리스트 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S2]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT			r.user_no as '사용자번호', i.user_name as '사용자이름', r.book_no as '도서번호'
					,r.book_title as '도서명', r.rental_date as '렌탈날짜'
	FROM			Book_Book_Rental	r
	INNER JOIN		Book_User_Info		i
	ON				r.user_no = i.user_no
	WHERE			r.return_date is null
	AND				(select DATEADD(dd, 7, r.rental_date)) < GETDATE();
END

03. BOOK_RENTAL_S3

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S3]    Script Date: 2021-08-25 오전 11:32:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈페이지, 도서정보 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S3]
	@BOOK_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	IF NOT EXISTS
	(
		SELECT		I.book_title, book_writer, book_description, book_saveImagePath 
		FROM		Book_Book_Info I
		INNER JOIN	Book_Book_State S
		ON			I.book_no = S.book_no
		WHERE		I.book_no = @BOOK_NO
		AND			S.book_rental = 1
	)
	BEGIN
		RAISERROR('데이터가 없습니다.',16,1)
		RETURN
	END

	SELECT		I.book_title, book_writer, book_description, book_saveImagePath 
	FROM		Book_Book_Info I
	INNER JOIN	Book_Book_State S
	ON			I.book_no = S.book_no
	WHERE		I.book_no = @BOOK_NO
	AND			S.book_rental = 1;
END

04. BOOK_RENTAL_S4

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S4]    Script Date: 2021-08-25 오전 11:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈페이지, 사용자정보 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S4]
	@USER_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	IF NOT EXISTS
	(
		SELECT		I.user_name, L.user_id, G.grade_name, CONVERT(char(10), L.register_date, 102) as register_date, I.user_address
		FROM		Book_User_Info I
		INNER JOIN	Book_User_LogIn L
		ON			I.user_no = L.user_no
		INNER JOIN	Book_User_Grade G
		ON			L.grade_no = G.grade_no
		WHERE		I.user_no = @USER_NO
	)
	BEGIN
		RAISERROR('데이터가 없습니다.',16,1)
		RETURN
	END


	SELECT		I.user_name, L.user_id, G.grade_name, CONVERT(char(10), L.register_date, 102) as register_date, I.user_address
	FROM		Book_User_Info I
	INNER JOIN	Book_User_LogIn L
	ON			I.user_no = L.user_no
	INNER JOIN	Book_User_Grade G
	ON			L.grade_no = G.grade_no
	WHERE		I.user_no = @USER_NO;
END

05. BOOK_RENTAL_S5

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S5]    Script Date: 2021-08-25 오전 11:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S5]
	@BOOK_NO	INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	IF NOT EXISTS
	(
		SELECT * FROM Book_Book_State WHERE book_no = @BOOK_NO AND book_rental= 2
	)
	BEGIN
		RAISERROR('데이터가 없습니다.',16,1)
		RETURN
	END


	SELECT		R.user_no, I.user_name, R.book_no, R.book_title, CONVERT(char(10), R.rental_date, 102) AS rental_date
	FROM		Book_Book_Rental R
	INNER JOIN	Book_User_Info I
	ON			R.user_no = I.user_no
	WHERE		book_no = @BOOK_NO;


END

06. BOOK_RENTAL_S6

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_S6]    Script Date: 2021-08-25 오전 11:33:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈 모든 기록 리스트>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_S6]
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT		r.user_no as '사용자번호', i.user_name as '사용자이름', r.book_no as '도서번호'
				,r.book_title as '도서명', r.rental_date as '렌탈날짜', r.return_date as '반납날짜'
	FROM		Book_Book_Rental	r
	INNER JOIN	Book_User_Info		i
	ON			r.user_no = i.user_no;
END

07. BOOK_RENTAL_I1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_I1]    Script Date: 2021-08-25 오전 11:34:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 렌탈등록>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_I1]
	@USER_NO		INT
	,@BOOK_NO		INT
	,@BOOK_TITLE	NVARCHAR(30)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	INSERT INTO Book_Book_Rental(user_no, book_no, book_title, rental_date)
	VALUES(@USER_NO, @BOOK_NO, @BOOK_TITLE, GETDATE());

	UPDATE Book_Book_State SET book_rental = 2 WHERE book_no = @BOOK_NO;
	
	UPDATE Book_Book_Info SET book_rentalcounts = book_rentalcounts + 1 WHERE book_no = @BOOK_NO;
END

08. BOOK_RENTAL_U1

USE [BookManagement]
GO
/****** Object:  StoredProcedure [dbo].[BOOK_RENTAL_U1]    Script Date: 2021-08-25 오전 11:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<문민승>
-- Create date: <2021.08.24>
-- Description:	<도서관리프로그램, 도서 렌탈 반납>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_RENTAL_U1]
	@USER_NO		INT
	,@BOOK_NO		INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	UPDATE Book_Book_Rental SET return_date = GETDATE() WHERE user_no = @USER_NO AND book_no = @BOOK_NO;
	UPDATE Book_Book_State SET book_rental = 1 WHERE book_no = @BOOK_NO;
END
profile
min seung moon
아직까지는 코린이!
이전 포스트

C# 포트폴리오 도전기(1-7. 렌탈 페이지 구현 및 v1 완성)

다음 포스트

C# 포트폴리오 도전기(1-9. 코드 수정)

post-custom-banner

0개의 댓글