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: