By GokiSoft.com| 20:47 15/08/2020|
SQL Server/MySQL

[Share Code] Tìm hiểu về View + Stored (Proceduce & Function) trong SQL Server - Lập trình SQL Server BT1799




Nội Dung Học
	- View
	- Store (Produce - Function)
=========================================
Function:
	- Tham so truyen vao
	- DU lieu tra ve
	- Code viet trong function

Store : SQL giong nhu vs ngon ngu lap trinh
	- Tham so truyen vao : rong, co 1 hoac nhieu tham so
	- Du lieu tra ve
	- Code viet trong store : Query (insert, update, delete, ...)




-- Active Database QuanLyNhaTrenPho
use QuanLyNhaTrenPho
go

-- TEST
select * from DuongPho
select * from NhaTrenPho
select * from QuanHuyen

-- SELECT => Hien thi thong tin => id, OwnerName, NgayDen, So Nha, Duong Pho, Quan Huyen
----- Lay du lieu tu 3 bang : DuongPho, NhaTrenPho, QuanHuyen
select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
from NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.MaDP = DuongPho.MaDP
	and DuongPho.MaQH = QuanHuyen.MaQH

----- TEAM => thao tac chung tren 1 he thong CSDL
----- A, B, C => select, insert, update, delete, ...  => Lam sao de 3 ong A, B, C => Su dung query cua nhau
---------- Facebook, Skype, ... => Gui cho nhau => OK. kha bat tien
---------- Project (Code + SQL (SQL Server, Oracle, MySQL)
---------------------- Viet truy van bang Code => Thao tac vs database.
---------------------- Viet tat ca query => luu vao CSDL => su dung trong code
----- View
create view view_xem_thong_tin_nha_tren_pho
as
select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
from NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.MaDP = DuongPho.MaDP
	and DuongPho.MaQH = QuanHuyen.MaQH

----- Chay view.
select * from view_xem_thong_tin_nha_tren_pho

----- Viet them dieu kien cho view
select * from view_xem_thong_tin_nha_tren_pho
where TenQH = 'Hoang Mai'

----- Sua lai noi dung cau select trong view
alter view view_xem_thong_tin_nha_tren_pho
as
select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
from NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.MaDP = DuongPho.MaDP
	and DuongPho.MaQH = QuanHuyen.MaQH

----- Xoa view
drop view view_xem_thong_tin_nha_tren_pho

---- STORE
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho

----- Tao 1 store => khong tham so truyen vao, ko du lieu tra ve, trong do chi co code.
create proc proc_test
as
begin
	select * from QuanHuyen
	select * from DuongPho
	select * from NhaTrenPho
end

----- test proc => goi proc do
exec proc_test

----- Sua noi
alter proc proc_test
as
begin
	select * from QuanHuyen
	select * from DuongPho
	select * from NhaTrenPho
end

drop proc proc_test

------ Tao 1 store => co tham so truyen vao, ko du lieu dau ra, code trong store
------ Vi du co du lieu can xem nhu sau
select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
from NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.MaDP = DuongPho.MaDP
	and DuongPho.MaQH = QuanHuyen.MaQH
-------- Bo sung yeu cau => loc theo ten quanhuyen => bien (thamso) trong store
create proc proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen
	@TenQH nvarchar(50)
as
begin
	select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
	from NhaTrenPho, DuongPho, QuanHuyen
	where NhaTrenPho.MaDP = DuongPho.MaDP
		and DuongPho.MaQH = QuanHuyen.MaQH
		and QuanHuyen.TenQH = @TenQH
end

exec proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen N'Hoang Mai'
exec proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen 'Hai Ba Trung'

select * from view_xem_thong_tin_nha_tren_pho

---- Tao 1 procedure nhieu tham so dau vao
create proc proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen_new
	@TenQH nvarchar(50),
	@SoNha nvarchar(100)
as
begin
	select NhaTrenPho.id, NhaTrenPho.OwnerName, NhaTrenPho.NgayDen, NhaTrenPho.SoNha, DuongPho.TenDuong, QuanHuyen.TenQH
	from NhaTrenPho, DuongPho, QuanHuyen
	where NhaTrenPho.MaDP = DuongPho.MaDP
		and DuongPho.MaQH = QuanHuyen.MaQH
		and QuanHuyen.TenQH = @TenQH
		and NhaTrenPho.SoNha like CONCAT('%', @SoNha, '%')
end

exec proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen_new N'Hoang Mai', N'18'
-- Viet cach khac
declare @TenQH nvarchar(50)
declare @SoNha nvarchar(100)
set @TenQH = N'Hoang Mai'
set @SoNha = N'18'
exec proc_xem_thong_tin_nha_tren_pho_theo_tenquanhuyen_new @TenQH, @SoNha

----------- Tao 1 store => tham so truyen, du lieu tra ve, code trong store
exec proc_test
select * from view_xem_thong_tin_nha_tren_pho

select QuanHuyen.MaQH, QuanHuyen.TenQH, COUNT(NhaTrenPho.id) as TotalHouse
from QuanHuyen, NhaTrenPho, DuongPho
where QuanHuyen.MaQH = DuongPho.MaQH
	and NhaTrenPho.MaDP = DuongPho.MaDP
group by QuanHuyen.MaQH, QuanHuyen.TenQH
order by TotalHouse asc

select QuanHuyen.MaQH, QuanHuyen.TenQH, COUNT(NhaTrenPho.id) as TotalHouse
from QuanHuyen left join DuongPho on QuanHuyen.MaQH = DuongPho.MaQH
	left join NhaTrenPho on NhaTrenPho.MaDP = DuongPho.MaDP
where QuanHuyen.TenQH = 'Hoang Mai'
group by QuanHuyen.MaQH, QuanHuyen.TenQH
order by TotalHouse asc

------ Viet proc => lay so nha tren 1 quan huyen => Lam nhu nao
create proc proc_count_house
	@TenQH nvarchar(50)
as
begin
	select QuanHuyen.MaQH, QuanHuyen.TenQH, COUNT(NhaTrenPho.id) as TotalHouse
	from QuanHuyen left join DuongPho on QuanHuyen.MaQH = DuongPho.MaQH
		left join NhaTrenPho on NhaTrenPho.MaDP = DuongPho.MaDP
	where QuanHuyen.TenQH = @TenQH
	group by QuanHuyen.MaQH, QuanHuyen.TenQH
	order by TotalHouse asc
end

exec proc_count_house N'Hoang Mai'

create proc proc_count_house_new
	@TenQH nvarchar(50),
	@COUNT int output
as
begin
	select @COUNT = COUNT(NhaTrenPho.id)
	from QuanHuyen left join DuongPho on QuanHuyen.MaQH = DuongPho.MaQH
		left join NhaTrenPho on NhaTrenPho.MaDP = DuongPho.MaDP
	where QuanHuyen.TenQH = @TenQH
end

declare @COUNT int
exec proc_count_house_new N'Hoang Mai', @COUNT = @COUNT output
print @COUNT




Tags:

Liên kết rút gọn:

https://gokisoft.com/1799

Bình luận