Sử dụng kết quả bài học sau để tìm hiểu kiến thức View/Store

Quản lý khách sạn - nâng cao - Lập trình Sql Server

Hướng dẫn xây dựng database cho bài trên

-- Kich hoat database BT1788
use BT1788
go

-- SQL: Hiển thị thông  tin khách sạn gồm các trường : Tên KS, địa chỉ, mã phòng, loại phòng, tầng
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
from hotel left join room on hotel.id = room.id_hotel
go

--I) View
-- Co cach nao -> save query tren -> vao trong database -> su dung cho lan sau ko?
---- Su dung view trong sql (sql server, oracle, mysql, .v.v)
---- Luu y ve view: chua duy nhat 1 cau select
---- Luu y: coi view -> nhu 1 table tam thoi -> duoc sinh ra khi chay truy van
create view view_list_hotel
as
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
from hotel left join room on hotel.id = room.id_hotel
go

-- Sua view da tao ra truoc do
alter view view_list_hotel
as
select hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.floor 'Tang', room.area 'DienTich'
from hotel left join room on hotel.id = room.id_hotel
go

---- chay de ket qua tra ve cua view
select * from view_list_hotel

select TenKS, DiaChiKS from view_list_hotel

select * from view_list_hotel
where DienTich > 100

--Xoa view
drop view view_list_hotel

-- II) Store/Proceduce -> Function trong ngon ngu lap trinh (Javascript, C)
---- Function: ten function, tham so dau vao, tham so dau ra (ket qua tra ve) - input/output
---- Khai niem cua Store/Procedure cung tuong tu nhu Function + TH khong tham so
---- Khac cua Store/Procedure so voi View 
------    -> Store/Procedure luu tat ca cac loai cau query: select, insert, update, delete, ... 
------       + viet nhieu cau truy van trong Store/Procedure
------    -> Hoat dong nhu function -> ket qua tra ve -> khong the coi Store/Procedure nhu table dc
---- Tao 1 Store khong tham so
create proc proc_list_hotel
as
begin
	-- Chi duy nhat 1 cau truy van
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
	from hotel left join room on hotel.id = room.id_hotel
end

---- Test proc vua dc tao ra
exec proc_list_hotel

create proc proc_list_hotel_2
as
begin
	-- Chi duy nhat 1 cau truy van
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
	from hotel left join room on hotel.id = room.id_hotel

	-- Truy van tiep theo
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
	from hotel left join room on hotel.id = room.id_hotel
	where room.area >= 100
end

exec proc_list_hotel_2

---- Tao store co tham so dau vao -> gom 1 tham so dau vao @minArea
create proc proc_list_hotel_by_min_area
	@minArea int
as
begin
	-- Truy van tiep theo
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
	from hotel left join room on hotel.id = room.id_hotel
	where room.area >= @minArea
end

exec proc_list_hotel_by_min_area 100
exec proc_list_hotel_by_min_area 150
exec proc_list_hotel_by_min_area 200
exec proc_list_hotel_by_min_area 300

---- Tao Store gom 2 tham so dau vao
------ Tim phong trong theo 1 ngay checkin va checkout duoc nhap vao
-- checkin: 2022-01-03 08:00:00, checkout: 2022-01-09 12:00:00
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no, room.floor
from hotel left join room on hotel.id = room.id_hotel
	left join book on room.room_no = book.room_no
where room.room_no not in 
	(
		select book.room_no from book
		where book.room_no not in
		(
			select book.room_no from book
			where book.checkin >= '2022-01-09 12:00:00' or book.checkout <= '2022-01-03 08:00:00'
		)
	)
go

---- Bien lenh tren thanh Store -> su dung nhieu lan
create proc proc_check_available_by_checkinout
	@checkin datetime,
	@checkout datetime
as
begin
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no, room.floor
	from hotel left join room on hotel.id = room.id_hotel
		left join book on room.room_no = book.room_no
	where room.room_no not in 
		(
			select book.room_no from book
			where book.room_no not in
			(
				select book.room_no from book
				where book.checkin >= @checkout or book.checkout <= @checkin
			)
		)
end

exec proc_check_available_by_checkinout '2022-01-03 08:00:00', '2022-01-09 12:00:00'
exec proc_check_available_by_checkinout '2022-01-05 08:00:00', '2022-01-09 12:00:00'
exec proc_check_available_by_checkinout '2022-01-01 08:00:00', '2022-01-03 12:00:00'

alter proc proc_check_available_by_checkinout
	@checkin datetime,
	@checkout datetime
as
begin
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no, room.floor
	from hotel left join room on hotel.id = room.id_hotel
		left join book on room.room_no = book.room_no
	where room.room_no not in 
		(
			select book.room_no from book
			where book.room_no not in
			(
				select book.room_no from book
				where book.checkin >= @checkout or book.checkout <= @checkin
			)
		)
end

drop proc proc_check_available_by_checkinout

---- Store/Procedure -> tham so dau vao & du lieu dau ra (input/output)
select * from book

-- Tim phong co the dat trong book
select book.room_no from book
where book.checkin >= '2022-01-06 12:00:00' or book.checkout <= '2022-01-04 08:00:00'
---- Kiem tra checkin (t1'): '2022-01-04 08:00:00' && checkout (t2'): '2022-01-06 12:00:00'

---- Store/Procedure -> Tham so dau vao & output
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
from hotel left join room on hotel.id = room.id_hotel

select count(*) 'total' from hotel left join room on hotel.id = room.id_hotel

create proc proc_list_hotel_and_count
	@minArea int,
	@count int output
as
begin
	-- 1) Xem danh du lieu dau ra nhu the nao.
	select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
		from hotel left join room on hotel.id = room.id_hotel
		where room.area >= @minArea
	-- 2) Lay so ban ghi trong TH tren
	select @count = count(*)
		from hotel left join room on hotel.id = room.id_hotel
		where room.area >= @minArea
	print @count
end

declare @count2 int
exec proc_list_hotel_and_count 100, @count = @count2 output
print @count2
print N'Tong so ban ghi tim duoc = ' + convert(nvarchar, @count2)

declare @count int
exec proc_list_hotel_and_count 100, @count = @count output
print @count
print N'Tong so ban ghi tim duoc = ' + convert(nvarchar, @count)