C#ポートフォリオチャレンジ(1-8.DB、Table、Proceduer)
41369 ワード
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.図書プログラム
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.リースプログラム
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
Reference
この問題について(C#ポートフォリオチャレンジ(1-8.DB、Table、Proceduer)), 我々は、より多くの情報をここで見つけました
https://velog.io/@ansalstmd/C-포트폴리오-도전기1-8.-DB-Table-Proceduer
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
-- 사용자 정보 테이블
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.図書プログラム
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.リースプログラム
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
Reference
この問題について(C#ポートフォリオチャレンジ(1-8.DB、Table、Proceduer)), 我々は、より多くの情報をここで見つけました
https://velog.io/@ansalstmd/C-포트폴리오-도전기1-8.-DB-Table-Proceduer
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
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
ENDUSE [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
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDUSE [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());
ENDUSE [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
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
END01. 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;
END02. 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;
END03. 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;
END04. 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;
END05. 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;
END06. 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();
END07. 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;
END08. 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();
END09. 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;
END5.リースプログラム
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
Reference
この問題について(C#ポートフォリオチャレンジ(1-8.DB、Table、Proceduer)), 我々は、より多くの情報をここで見つけました
https://velog.io/@ansalstmd/C-포트폴리오-도전기1-8.-DB-Table-Proceduer
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
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;
ENDUSE [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();
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDUSE [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;
ENDReference
この問題について(C#ポートフォリオチャレンジ(1-8.DB、Table、Proceduer)), 我々は、より多くの情報をここで見つけました https://velog.io/@ansalstmd/C-포트폴리오-도전기1-8.-DB-Table-Proceduerテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol