Untitled

Hàm (function) và thủ tục (Store procedure)
GV(MaGV,HoTen,SoDT)
Lop(MaLop,TenLop,Phong,MaGV)
SV(MaSV,Hoten,QueQuan,GioiTinh,Ngaysinh,MaLop)
1. Hàm:
-- Do view không truyền được tham số => cho nên view không tuỳ biến
được kết quả trả về.
vd. create view vw_DSSVHN
as
select * from sv where quequan = N'hà Nội'
=> Khi gọi view: select * from vw_DSSVHN
=> Trả về toàn bộ sv quê HN có trong bảng sv
=> Khi cần sv Thái Bình, nam định,...????
=> cần tạo hàm để truyền tham biến
--cú pháp hàm trả về 1 giá trị cụ thể (scalar valued function):
create function fn_Tenham(@thambien1 kieudl1,@thambien2 kieudl2,...)
returns kieudltrảvề
as
begin
declare @bien kieudltrave
.........
return @bien
end
1. returns: kiểu trả về của hàm
2. return: Giá trị trả về
3. begin...end: bắt đầu và kết thúc khối lệnh
4. declare: khai báo biến
5. biến trong sql bắt đầu bởi @
vd1. viết hàm đưa ra tên sinh viên khi biết mã sinh viên nhập vào từ
bàn phím.
create function fn_vd1(@msv char(10))
returns nvarchar(20)
as
begin
declare @ten nvarchar(20)
set @ten = (select hoten from sv where masv = @msv)
return @ten
end
==> gọi hàm:
select dbo.tenham(doiso1,doiso2,...)
select dbo.fn_vd1('sv01')
select dbo.fn_vd1('sv03')
select dbo.fn_vd1('sv05')
vd2. viết hàm đếm có bao nhiêu sinh viên có tuổi từ x đến y, học lớp z.
với x,y,z nhập từ bàn phím.
create function vd2(@x int,@y int,@z nvarchar(20))
returns int
as
begin
declare @tong int
set @tong = (select count(*)
from sv inner join lop on sv.malop=lop.malop
where tenlop = @z
and year(getdate())-year(ngaysinh) between @x and @y
)return @tong
end
---gọi hàm:
select dbo.vd2(18,25,'cntt2')
select dbo.vd2(180,250,'cntt2')
vd3. Đưa ra tổng số sinh viên mà cô giáo có tên x chủ nhiệm học lớp y.
create function fn_vd3(@x nvarchar(20),@y nvarchar(20))
returns int
as
begin
declare @dem int
set @dem = (select count(*)
from sv inner join lop on sv.malop=lop.malop
inner join gv on lop.magv=gv.magv
where gv.hoten = @x and tenlop = @y
)
return @dem
end
--gọi: select dbo.fn_vd3(N'Nguyễn Mai Linh','cntt2')
2. Hàm trả về kiểu table: (1 List kết quả)
table valued function
--cú pháp:
create function fn_tenham(@thambien1 kieudl1,@thambien2 kieudl2,...)
returns @tenbang table(
cot1 kieudl1,
cot2 kieudl2,
...
)
as
begin
insert into @tenbang
select cot1,cot2,...
from....
where ....
return
end
vd4. viết hàm đưa ra masv,hoten,quequan,tuoi,tenlop,phong
của các sinh viên học lớp x, gioitinh y, gvcn z.
create function fn_vd4(@x nvarchar(20),@y nchar(10),@z nvarchar(20))
returns @bang table(
masv char(10),
hoten nvarchar(20),
quequan nvarchar(20),
tuoi int,
tenlop nvarchar(20),
phong nvarchar(20)
)
as
begin
insert into @bang
select masv,sv.hoten,quequan,year(getdate())-year(ngaysinh),
tenlop,phong
from sv inner join lop on sv.malop=lop.malop
inner join gv on lop.magv=gv.magv
where tenlop = @x and gioitinh=@y and gv.hoten = @z
return
end
--gọi hàm:
select * from tenham(doiso1,doiso2,...)select * from fn_vd4('cntt1',N'Nam',N'Nguyễn Mai Linh')
vd2. viết hàm đưa ra tenlop,số lượng sinh viên có giới tính x của các lớp.
create function fn_vd5(@x nchar(10))
returns @bang table(
tenlop nvarchar(20),
soluong int
)
as
begin
insert into @bang
select tenlop,count(*)
from sv inner join lop on sv.malop=lop.malop
where gioitinh = @x
group by tenlop
return
end
--gọi hàm:
select * from fn_vd5(N'Nam')
select * from fn_vd5(N'Nữ')
II. thủ tục - store procedure
-- hàm - trả về giá trị
-- thủ tục chỉ thực thi, không trả về
-- có 2 loại thủ tục:
+ thủ tục không output
+ thủ tục có output
1. thủ tục không output:
--cú pháp:
create proc sp_tenthutuc(@thambien1 kieudl1,@thambien2 kieudl2,...)
as
begin
...........
end
vd1. viết 1 thủ tục thêm mới 1 sinh viên, với các tham biến
masv,hoten,quequan,gioitinh,ngaysinh,malop nhập từ bàn phím.
hãy kiểm tra xem malop có trong bảng Lớp hay không? Nếu không thì đưa ra
thông báo. Hãy kiểm tra xem masv đã tồn tại trong bảng sv chưa?
Nếu rồi thì đưa ra TB trùng.
create proc sp_vd1(@msv char(10),@ht nvarchar(20),@qq nvarchar(20),
@gt nchar(10), @ns date, @ml char(10)
)
as
begin
if(not exists(select * from lop where malop = @ml))
print('Khong co ma lop nay')
else
if(exists(select * from sv where masv = @msv))
print('Da ton tai sv nay')
else
begin
insert into sv values(@msv,@ht,@qq,@gt,@ns,@ml)
print('Ban them moi thanh kong')
end
end
select * from lopselect * from sv
chú ý: thi, có mấy trường hợp => đưa ra các trường hợp chạy.
--khong co ma lop
exec sp_vd1 'sv05',N'Dách',N'Bến xe',N'Nam','05-05-1997','L04'
--trùng masv
exec sp_vd1 'sv05',N'Dách',N'Bến xe',N'Nam','05-05-1997','L02'
--thanh cong
exec sp_vd1 'sv06',N'Dách',N'Bến xe',N'Nam','05-05-1997','L02'
GV(MaGV,HoTen,SoDT)
Lop(MaLop,TenLop,Phong,MaGV)
SV(MaSV,Hoten,QueQuan,GioiTinh,Ngaysinh,MaLop)
vd2. viết 1 thủ tục thêm mới 1 lớp, với các tham biến truyền vào:
malop,tenlop,phong,magv. hãy kiểm tra xem magv có trong bảng gv không?
Nếu không thì đưa ra TB. hãy kiểm tra xem Tên lớp có bị trùng không, nếu
trùng thì đưa ra TB. ngược lại cho phép nhập.
vd3. viết 1 thủ tục xoá 1 sinh viên ra khỏi bảng sv, với tham biến
nhập vào là masv. hãy kiểm tra xem masv có trong bảng sv không?
Nếu không thì không cho phép xoá, ngược lại cho phép xoá.
create proc sp_xoasv(@msv char(10))
as
begin
if(not exists(select * from sv where masv = @msv))
print('Khong co sv can xoa')
else
begin
delete from sv where masv = @msv
print('Xoa thanh cong')
end
end
vd4. viết 1 thủ tục thêm mới 1 sinh viên, với các tham biến
masv,hoten,quequan,gioitinh,ngaysinh,tenlop nhập từ bàn phím.
hãy kiểm tra xem tenlop có trong bảng Lớp hay không? Nếu không thì đưa ra
thông báo. Hãy kiểm tra xem masv đã tồn tại trong bảng sv chưa?
Nếu rồi thì đưa ra TB trùng.
create proc sp_vd4(@msv char(10),@ht nvarchar(20),@qq nvarchar(20),
@gt nchar(10), @ns date, @tenlop nvarchar(20)
)
as
begin
if(not exists(select * from lop where tenlop = @tenlop))
print('Khong co ten lop nay')
else
if(exists(select * from sv where masv = @msv))
print('Da ton tai sv nay')
else
begin
declare @ml char(10)
set @ml = (select malop from lop where tenlop = @tenlop)
insert into sv values(@msv,@ht,@qq,@gt,@ns,@ml)
print('Ban them moi thanh kong')
end
end