Câu 1:
declare @TenNhaCC nvarchar(255), @MaMP nvarchar(255), @DonGia
money, @SoLuongXeDangKy int
declare cur cursor for
select TenNhaCC, [Link], DonGia, SoLuongXeDangKy
from tblDangKyCungCap dk
inner join tblMucPhi mp on [Link] = [Link]
inner join tblNhaCungCap ncc on [Link] = [Link]
where [Link] = N'TP. HồChí Minh'
open cur
fetch next from cur into @TenNhaCC, @MaMP, @DonGia,
@SoLuongXeDangKy
while @@fetch_status = 0
begin
select @TenNhaCC as "Tên nhà cung cấp", @MaMP as "Mã mức
phí", str(@DonGia, 10) as "Đơn giá", str(@SoLuongXeDangKy, 10)
as "Chi tiết"
fetch next from cur into @TenNhaCC, @MaMP, @DonGia,
@SoLuongXeDangKy
end
close cur
deallocate cur
Câu 2:
create or alter proc nhacungcap
@d money as
begin
select [Link], [Link], [Link], [Link]
from tblDangKyCungCap dk
join tblMucPhi mp on [Link] = [Link]
join tblNhaCungCap ncc on [Link] = [Link]
where [Link] >= @d
end
exec nhacungcap 600000
Câu 3:
create or alter function fn_nhacungcap
@a int
returns table
as
return
select [Link], tennhacc, [Link], [Link],
ngaybatdaucungcap, ngayketthuccungcap, soluongxedangky
from tbldangkycungcap dk
inner join tblnhacungcap ncc on [Link] = [Link]
inner join tblmucphi mp on [Link] = [Link]
where [Link] >= @a
)
go
select * from fn_nhacungcap(5)
Câu 4:
create or alter proc diachinhacc
@a nvarchar(50)
as
begin
if exists
select 1
from tblNhaCungCap
where MaNhaCC = @a
begin
select diachi as "Địa chỉ"
from tblNhaCungCap
where MaNhaCC = @a
end
else
begin
print N'Không tồ
n tại nhà cung cấp có mã ' + @a
end
end
go
exec diachinhacc 'NCC003'
Câu 5:
create or alter proc ten_proc_tao_ma
@ma nvarchar(50) output
as
begin
declare @n int
select @n = isnull(max(cast(substring(mamp, len('MP') + 1,
len(mamp)) as int)), 0) + 1
from tblMucPhi
set @ma = 'MP' + right('00' + cast(@n as varchar), 2)
end
go
declare @ma nvarchar(50)
exec ten_proc_tao_ma @ma output
print @ma
Câu 6:
create or alter proc cau6 @tennhacc nvarchar(50)
as
begin
delete from tblNhaCungCap where
TenNhaCC = @tennhacc
print N'Đã xóa nhà cung cấ
p có tên: ' + @tennhacc
end
go
exec cau6 'Công ty Mai Linh'
Câu 7:
create or alter proc capnhat_dangkycungcap
@madkcc nvarchar(50),
@manhacc nvarchar(50),
@mamp nvarchar(50),
@ngaybatdaucungcap datetime,
@ngayketthuccungcap datetime,
@soluongxedangky int
as
begin
if not exists (
select 1 from tbldangkycungcap
where madkcc = @madkcc
begin
print N'ma dang ky khong ton tai'
return
end
if not exists (
select 1 from tblnhacungcap
where manhacc = @manhacc
begin
print N'ma nha cung cap khong hop le'
return
end
if not exists (
select 1 from tblmucphi
where mamp = @mamp
begin
print N'ma muc phi khong hop le'
return
end
update tbldangkycungcap
set manhacc = @manhacc,
mamp = @mamp,
ngaybatdaucungcap = @ngaybatdaucungcap,
ngayketthuccungcap = @ngayketthuccungcap,
soluongxedangky = @soluongxedangky
where madkcc = @madkcc
print N'cap nhat thanh cong'
end
go
exec capnhat_dangkycungcap
N'dk01',
N'ncc001',
N'mp01',
'2024-01-01',
'2024-12-31',
10
Câu 8:
create or alter proc xem_thongtin_nhacc
@tennhacc nvarchar(50) = null
as
begin
select
[Link],
[Link],
[Link],
[Link],
[Link]
from tblnhacungcap ncc
join tbldangkycungcap dk on [Link] = [Link]
join tblmucphi mp on [Link] = [Link]
where @tennhacc is null or [Link] = @tennhacc
end
go
exec xem_thongtin_nhacc N'Công ty TNHH An Phát'
Câu 9:
create or alter proc cursor_nhacc
@manhacc nvarchar(50)
as
begin
declare
@tennhacc nvarchar(50),
@ngaybd datetime,
@ngaykt datetime,
@sl int
declare cur_nhacc cursor for
select
[Link],
[Link],
[Link],
[Link]
from tblnhacungcap ncc
join tbldangkycungcap dk on [Link] = [Link]
where [Link] = @manhacc
open cur_nhacc
fetch next from cur_nhacc into
@tennhacc, @ngaybd, @ngaykt, @sl
while @@fetch_status = 0
begin
select @tennhacc as "Tên nhà cung cấp", @ngaybd as "Ngày
bắ
t đầ
u", @ngaykt as "Ngày kết thúc", @sl as "Sốlượng"
fetch next from cur_nhacc into
@tennhacc, @ngaybd, @ngaykt, @sl
end
close cur_nhacc
deallocate cur_nhacc
end
go
exec cursor_nhacc N'ncc001'
Câu 10:
create or alter function dongia_mamp(@mamp nvarchar(50))
returns table
as
return (
select DonGia as DonGia
from tblmucphi
where mamp = @mamp
go
select * from dongia_mamp(N'mp01')
Câu 11:
create or alter function thongtinmancc(@manhacc nvarchar(50))
returns table
as
return (
select TenNhaCC, [Link], DonGia,NgayBatDauCungCap,
NgayKetThucCungCap, SoLuongXeDangKy
from tblDangKyCungCap dk
inner join tblNhaCungCap ncc on [Link] = [Link]
inner join tblMucPhi mp on [Link] = [Link]
where [Link] = @manhacc
go
select * from thongtinmancc(N'ncc001')
Câu 12:
create or alter function fn_tao_ma()
returns nvarchar(50)
as
begin
declare @mamp nvarchar(50), @n int
select @n = isnull(max(cast(substring(mamp, len('MP')+1,
len(mamp)) as int)),0) + 1
from tblMucPhi
set @mamp = 'MP' + right('00' + cast(@n as varchar), 2)
return @mamp
end
go
select dbo.fn_tao_ma()
Câu 13:
create or alter proc cursor_xoa_nhacc_theo_sl
@a int
as
begin
declare @manhacc nvarchar(50)
declare cur_ncc cursor for
select distinct [Link]
from tblnhacungcap ncc
join tbldangkycungcap dk on [Link] = [Link]
where [Link] > @a
open cur_ncc
fetch next from cur_ncc into @manhacc
while @@fetch_status = 0
begin
-- xóa bảng liên quan trước
delete from tbldangkycungcap
where manhacc = @manhacc
-- xóa bảng chính sau
delete from tblnhacungcap
where manhacc = @manhacc
fetch next from cur_ncc into @manhacc
end
close cur_ncc
deallocate cur_ncc
print N'xoa thanh cong'
end
go
exec cursor_xoa_nhacc_theo_sl 10