Câu 4.1 --câu4.5

quanli_thuvien.sql

--4.1 sp_ThongtinDocGia
create proc sp_ThongtinDocGia
(@ma_docgia smallint)
as
begin
  if(exists(select * from NguoiLon where ma_docgia=@ma_docgia))
  select * from DocGia d join NguoiLon n on d.ma_docgia=n.ma_docgia where n.ma_docgia=@ma_docgia
  else
  select * from DocGia d join TreEm k on d.ma_docgia=k.ma_docgia where k.ma_docgia=@ma_docgia
end
--câu4.2 sp_ThongtinDausach
create proc sp_ThongtinDausach
as
begin
  select * from DauSach d join TuaSach t on d.ma_tuasach=t.ma_tuasach
end
--câu4.5 sp_DocGiaCoTreEmMuon
create proc sp_DocGiaCoTreEmMuon
as
begin
  select ma_docgia from NguoiLon nl
  where exists(select *from Muon m1 where m1.ma_docgia=nl.ma_docgia and m1.ngay_hethan>getdate())
  and exists(select * from Muon m2 join TreEm tre on m2.ma_docgia=tre.ma_docgia
              where nl.ma_docgia=tre.ma_docgianguoilon and m2.ngay_hethan>getdate())
end
--câu4.6 sp_CapnhatTrangthaiDausach
create proc sp_CapnhatTrangthaiDausach
(
@isbn int
)
as
begin
set @isbs=0
  if(exists(select * from DauSach where inbs=@inbs))
  set trangthai='Y' from DauSach
  else
  set trangthai='N' from DauSach
end
--câu4.9 sp_ThemNguoiLon
create proc sp_ThemNguoiLon
(
@ho nvarchar(15),
@tetlot nvarchar(10),
@ten nvarchar(15),
@ngaysinh date,
@sonha nvarchar(15),
@duong nvarchar(50),
@quan nvarchar(20),
@dienthoai nvarchar(14),
@ham_sd date
)
as
begin
  declare madg int, madg_t int
  set madg_t=0
  declare cur_magd cursor for select ma_docgia from DocGia
  open cur_magd
  fetch next from cur_magd into @madg
  while(@@petch_status=0 and @madg=@madg_t+1)
  begin
    set @madg_t=@madg_t+1
    fetch next from cur_madg into @madg
  end
  close cur_madg
  deallocate cur_madg
  set