Bài giảng Chương III: Procedure, Function, View, Trigger & Index

Chương III. PROCEDURE, FUNCTION, VIEW, TRIGGER & INDEX

Nội dung

 1. Thủ tục (stored procedure)  2. Hàm (Function)  3. Khung nhìn (view)  4. Ràng buoc toàn vẹn (trigger)  5. Chỉ mục (index)

1. Stored procedure

 Stored procedure trong CSDL tương tự như các thủ tục trong ngôn ngữ lập trình. Mọi stored procedure có thể: Nhận tham số đầu vào, thực thi các câu lệnh bên trong

và trả vê các giá trị.

Bên trong mỗi thủ tục có thể có nhiều câu lệnh và có thể

gọi các thủ tục khác

Trả về các thông báo cho biết thủ tục thực hiện thành

công hay thất bại

Các câu trúc điều khiển (IF, WHILE, FOR) có thể được

sử dụng trong thủ tục

1. Stored procedure

 Các lợi ích của stored procedure:

Cho phép module hóa các công việc và thực thi các

câu lệnh dễ dàng hơn

Tối ưu hóa việc phân tích, biên dịch và thực thi câu

lệnh

Thực hiện một yêu cầu bằng một câu lệnh đơn giản hơn thay vì phải sử dụng nhiều dòng lệnh SQL khi thực thi  làm giảm thiểu sự lưu thông trên mạng Tăng khả năng bảo mật khi cấp phát quyền thông

qua thủ tục

1. Stored procedure

 Tạo thủ tục tên danh sách có tham số truyền vào là tên nhân viên. Liệt kê mã nhân viên, họ lót, tên nhân viên, ngày vào làm, lương: CREATE PROC sp_danhsach @ten nvarchar(20) AS

SELECT MaNV, HoLot, TenNV, NgayVaoLam,

Luong

FROM NhanVien WHERE TenNV= @ten

1. Stored procedure

 Thực thi thủ tục và truyền tham số cho thủ tục như

 Thực thi: sp_danhsach 'Linh‘ hoặc

sau: @tham_số=giá_trị

exec sp_danhsach ‘Linh’

1. Stored procedure  Người dùng có thể gán giá trị mặc định cho tham số

trong câu lệnh create procedure CREATE PROC sp_danhsach1 @ten

nvarchar(20)='Bình'

SELECT * FROM NhanVien WHERE TenNV = @ten Thực thi: sp_danhsach1 ‘Duy’

1. Stored procedure

 Tạo thủ tục tăng lương nhân viên với tham số đầu vào là

 CREATE PROC asc_salary(@ascsalary int, @idemp int)

mức lương tăng và mã nhân viên

BEGIN UPDATE NhanVien SET Luong=Luong+ @ascsalary WHERE MaNV=@idemp END

Thực thi: asc_salary 100, 1 (chú ý thứ tự giá trị truyền vào theo đúng tham số) Hoặc thực thi: asc_salary (@ascsalary=100, @idemp=1) (Nếu thực thi câu lệnh tường minh có thể hoán đổi vị trí

các tham sô)

1. Stored procedure

Tạo thủ tục tăng lương nhân viên với tham số đầu vào là mã nhân viên. Nếu lương nhân viên lớn hơn 1000$ thì tăng 150$, ngược lại tăng 100$

CREATE PROC asc_salary(@idemp int) AS

DECLARE @salary INT

BEGIN

SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp If @salary>1000 BEGIN

UPDATE NhanVien SET Luong=Luong+150 WHERE MaNV=@idemp

END Else BEGIN

UPDATE NhanVien SET Luong=Luong+100 WHERE MaNV=@idemp

END

1. Stored procedure

Tạo thủ tục tăng lương nhân viên lên 5 lần, mỗi lần tăng

50$ dùng câu lệnh While

CREATE PROCEDURE asc_salary(@idemp int) AS

DECLARE @count int BEGIN

SET @count=1 WHILE @count<=5 BEGIN

UPDATE NhanVien SET Luong=Luong+50 WHERE MaNV=@idemp SET @count=@count+1

END

1. Stored procedure

 Thông tin trả về các thủ tục:  Các thủ tục có thể trả về giá trị số nguyên để thông báo thủ tục thực hiện thành công hay thất bại. SQL Server định nghĩa sẵn tập các giá trị trả về nằm trong khoảng [-99;0]. Giá trị 0 cho biết thủ tục thực hiện thành công, các giá trị còn lại cho biết nguyên nhân lỗi xảy ra.

1. Stored procedure

 Ngoài ra, người dùng có thể định nghĩa giá trị trả về bằng cách bổ sung một tham số vào câu lệnh RETURN. Tất cả các số nguyên đều được chấp nhận ngoại trừ các số do hệ thống định nghĩa và sử dụng

CREATE PROC sp_vidu @ten nvarchar(20) AS

IF EXISTS (SELECT *

FROM NhanVien WHERE TenNV = @ten)

RETURN 1

ELSE

RETURN 2

1. Stored procedure

Thực thi thủ tục có câu lệnh RETURN: Declare @a int Exec @a = sp_vidu N‘Hà’ Select @a

1. Stored procedure

SELECT * FROM NhanVien WHERE MaNV=@MaNV

Tạo thủ tục với tham số mặc định CREATE PROC sp_testdefault @MaNV int =3 AS

Thực thi thủ tục: Exec sp_testdefault Hoặc Exec sp_testdefault 4

1. Stored procedure

 Khi cả 2 câu lệnh Create Procedure và Execute

chứa mục chọn OUTPUT cho tên một tham số, thủ tục có thể sử dụng một biến để trả về trị của tham số đó đến người gọi

CREATE PROC Chia @sobichia real,@sochia real, @ketqua real OUTPUT AS

IF (@sochia =0)

Print ‘Loi chia cho 0’

ELSE

SELECT @ketqua = @sobichia / @sochia

1. Stored procedure

Thực thi thủ tục DECLARE @ketqua real EXEC Chia 100, 0, @ketqua OUTPUT SELECT @ketqua

Thực thi thủ tục: DECLARE @ketqua real EXEC Chia 100, 2, @ketqua OUTPUT SELECT @ketqua

1. Stored procedure

 Tạo thủ tục với tham số đầu ra là lương nhân viên với tham

số đầu vào là mã nhân viên

CREATE PROCEDURE salary (@idemp int, @salary int

OUTPUT)

AS

SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp

Thực thi: DECLARE @salary int EXEC salary 5, @salary=@salary OUTPUT PRINT ‘Lương nhân viên:’+CAST(@salary AS

VARCHAR(10))+’$’

1. Stored procedure

Cho biết tổng lương của nhân viên với tham số đầu vào là mã phòng

ban

CREATE PROCEDURE sum_salary (@deptid int, @sumsalary int OUTPUT)

AS

BEGIN

If NOT EXISTS (SELECT *

FROM NhanVien WHERE MaPB=@deptid)

RETURN 1

SELECT @sumsalary=SUM(Luong) FROM NhanVien WHERE MaPB=@deptid

RETURN

If @sumsalary IS NULL SET @sumsalary=0

END

1. Stored procedure

Thự c thi thủ tục: DECLARE @sumsalary, @result EXEC @result=sum_salary 44,@sumsalary OUTPUT If @result=1

PRINT ‘Không tồn tại phòng ban’

Else

PRINT ‘Tổng lương cả phòng là:’ +

CAST(@sumsalary AS VARCHAR(15))+ ‘$’

1. Stored procedure

Tạo thủ tục gọi trực tiêp: CREATE PROC count_rows (@name Nvarchar(50)) WITH EXECUTE AS CALLER AS

EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name)

Tạo thủ tục gọi bởi người tạo ra thủ tục: CREATE PROC count_rows_as_me (@name

Nvarchar(50))

WITH EXECUTE AS SELF AS

EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name)

1. Stored procedure

Tạo thủ tục thực thi bởi người dùng: CREATE PROC count_rows_as_user1 (@name

Nvarchar(50))

WITH EXECUTE AS ‘User1’ AS

EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name)

1. Stored procedure

Thực thi thủ tục: DECLARE @sname VARCHAR(50) SET @sname = ‘authors;drop table customers’ count_rows_as_me @sname

1. Stored procedure

Một thủ tục có thể sử dụng bất kỳ hoặc tất cả khả

năng sau để trả về giá trị:  Một hoặc nhiều giá trị  Một giá trị trả vê rõ ràng (lệnh RETURN)  Một tham sô OUTPUT

Bên trong câu lệnh Create Procedure có thể bao gồm bất kỳ câu lệnh nào trừ các câu lệnh sau: Create Procedure, Create View, Create Rule, Create Default, Create Trigger

1. Stored procedure

Cần chỉ rõ tên chủ sở hữu đối tượng khi gọi đối tượng

bên trong thủ tục

CREATE PROC sp_index AS CREATE INDEX PhongBan_ind ON user1.PhongBan (TenPB)

1. Stored procedure

Cú pháp sửa thủ tục:

ALTER PROC proc_name …

Cú pháp đổi tên thủ tục:

SP_RENAME old_name, new_name SP_RENAME sp_index, sp_index_user1

Cú pháp xóa thủ tục:

DROP PROCEDURE proc_name DROP PROCEDURE sp_index_user1

2. Function

 2.1 Hàm Scalar  2.2 Hàm Inline table valued  2.3 Hàm Multi statement table valued

2.1 Hàm Scalar

Hàm vô hướng trả về duy nhất một giá trị dựa trên tham số truyền vào. Cú pháp như sau: CREATE FUNCTION func_name ([parametername datatype, parameter2,…]) RETURNS (function datatype) AS

BEGIN các_câu_lenh_của_hàm END

2.1 Hàm Scalar

Câu lệnh dưới đây định nghĩa hàm tính ngày trong tuần(thứ trong tuần) của một giá trị kiểu ngày

CREATE FUNCTION f_thu (@ngay datetime)

returns nvarchar(10)

Begin

declare @st nvarchar(10) select @st=case datepart(dw,@ngay)

when 1 then N'chủ nhật' when 2 then N'thứ hai’ when 3 then N 'thứ ba'

2.1 Hàm Scalar

CREATE FUNCTION f_thu(@ngay datetime)

Returns nvarchar(10)

Begin

when 4 then N 'thứ tư' when 5 then N 'thứ năm' when 6 then N 'thứ sáu' else N 'thứ bảy'

End Return (@st) /* trị trả về của hàm */

End

2.1 Hàm Scalar Thực thi hàm: Select MaNV, TenNV, dbo.f_thu (NgayVaoLam) From NhanVien Kết qủa:

2.2 Hàm Inline table valued

Hàm nội tuyến, trả về dạng bảng. Cú pháp như sau: CREATE FUNCTION func_name ([parameter

datatype])

RETURNS TABLE AS

RETURN (câu_lenh_select)

2.2 Hàm Inline table valued

Cú pháp của hàm nội tuyến phải tuân theo các

qui tắc sau:

- Kiểu trả về của hàm phải được chỉ định bởi mệnh

đề RETURNS TABLE

- Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT. Ngoài ra, không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm

2.2 Hàm Inline table valued

Tạo hàm trả về các khách hàng tùy thuộc vào giá trị

mã khách hàng truyền vào cho tham số

CREATE FUNCTION f_KhachHang (@MaKH int)

RETURNS TABLE

RETURN (Select *

From KhachHang Where MaKH > @MaKH)

2.2 Hàm Inline table valued

Thực thi hàm: SELECT tmp.TenKH, dh.NgayDatHang FROM DonHang dh, dbo.f_KhachHang(3) as tmp WHERE dh.MaKH = tmp.MaKH

2.3 Hàm Multi statement table valued

Hàm gồm nhiều câu lệnh SQL bên trong, trả vê dạng bảng. Cú pháp như sau: CREATE FUNCTION func_name (parameter datatype) RETURNS @biên_bng TABLE dnh_nghia_bng AS

BEGIN các_câu_lenh_trong_thân_hàm

RETURN

END

Lưu ý: sau từ khóa RETURNS là một biến bảng được định nghĩa

và sau từ khóa RETURN cuối hàm không có tham sô

nào đi kèm

2.3 Hàm Multi statement table valued

CREATE FUNCTION f_DSKhachHang (@MaKH int)

returns @myKhachHang table (MaKH int, TenKH

nvarchar(50), NgayDatHang datetime)

Begin

If @MaKH = 0

Insert into @myKhachHang Select kh.MaKH, kh.TenKH,

dh.NgayDatHang

From KhachHang kh, DonHang dh Where dh.MaKH = kh.MaKH ---

2.3 Hàm Multi statement table valued

CREATE FUNCTION f_DSKhachHang (@MaKH int)

returns @myKhachHang table (MaKH int, TenKH

nvarchar(50), NgayDatHang datetime)

Begin

--- Else

Insert into @myKhachHang Select kh.MaKH, kh.TenKH, dh.NgayDatHang From KhachHang kh, DonHang dh Where kh.MaKH=dh.MaKH and kh.MaKH = @MaKH

Return

End

2.3 Hàm Multi statement table valued

Thực thi hàm: Select * From f_DSKhachHang (0)

2.3 Hàm Multi statement table valued

Lệnh ALTER FUNCTION dùng để thay đổi hàm ALTER FUNCTION f_KhachHang (@MaKH

int)

Returns table AS

Return (Select * From KhachHang Where MaKH > @MaKH)

2.3 Hàm Multi statement table valued

Lệnh DROP FUNCTION dùng để xóa hàm DROP FUNCTION func_name DROP FUNCTION f_KhachHang

3. View

 View là một bảng logic hay ‘bảng ảo’ truy cập đến một hoặc nhiều bảng dữ liệu hoặc view khác. View truy xuât dên các cột và dòng dữ liệu bên trong bảng và hiển thị ra đúng các thông tin tối thiểu mà người sử dụng cần dùng.

3. View

Đối với một số view, người dùng có thể thực hiện các thao tác thêm, xóa, sửa dữ lieu. Việc thực hiện các thao tác này Phải thỏa các điều kiện sau: - Trong câu lệnh SELECT định nghĩa view không được sử Dụng các từ khóa DISTINCT, TOP, GROUP BY, UNION - Các thành phần xuất hiện trong danh sách chọn của câu Lệnh SELECT không xuất hiển các biểu thức tính toán,

các hàm gộp - Các ràng buộc toàn vẹn trên các bảng cơ sở phải được

đảm bảo

3. View

Cú pháp tạo view: CREATE VIEW [schema_name.] tên_view [(column[,...n])][WITH ENCRYPTION] AS câu_lenh_select [ ; ] [ WITH CHECK OPTION ]

3. View

Tạo view cho biết mã nhân viên, tên nhân viên và tên

chức vụ của nhân viên ( phòng ban có mã phòng ban là 45)

CREATE VIEW empvu45 AS

SELECT MaNV, TenNV, TenCV FROM NhanVien nv, ChucVu cv WHERE nv.MaCV=cv.MaCV and MaPB=45

Truy vấn đến view empvu45: Select * From empvu45

3. View

Tạo view cho biết mã nhân viên, họ lót có bí danh là ‘FIRST NAME’, tên nhân viên có bí danh là ‘LAST NAME’ và lương của nhân viên có bí danh là ‘MONTHLY_SALARY’ ( phòng ban có mã phòng ban là 41)

CREATE VIEW salvu41 AS

SELECT MaNV, HoLot AS ‘FIRST NAME’, TenNV AS ‘LAST NAME’, Luong AS ‘MONTHLY_SALARY’ FROM NhanVien WHERE MaPB=41

3. View

Cú pháp xóa view: DROP VIEW [tên_schema.] tên_view

Xóa view salvu41: DROP VIEW salvu41

4. Trigger

 Trigger là một dạng đặc biệt của thủ tục lưu trữ và thực thi tự động khi người dùng áp dụng câu lệnh cập nhật dữ liệu lên một table chỉ định nhằm mục đích đảm bảo tính toàn vẹn dữ liệu. Nếu trigger bị vi phạm, câu lệnh sẽ không được thực thi

4. Trigger

 Trigger được sử dụng trong các cách sau:  Có thể thay đổi đồng loạt các table có liên quan với

 Có thể không cho phép hoặc hủy bỏ những thay

nhau trong CSDL

đổi vi phạm ràng buoc toàn vẹn tham chiêu và các giao dịch sửa đổi dữ liệu

4. Trigger

 Có thể áp đặt các giới hạn phức tạp hơn những giới hạn được định nghĩa bằng ràng buộc CHECK và có thể tham chiếu đến các cột trong các bảng khác

 Có thể tìm sự khác biệt giữa các trạng thái của một

table trước và sau khi sửa đổi dữ liệu và lấy ra những tác động dựa trên sự thay đổi đó

4. Trigger

 Cơ chế hoạt động của trigger  3 biến cố kích hoạt 1 trigger

 INSERT  UPDATE  DELETE

 Trigger lưu trữ dữ liệu của mẩu tin vừa thêm vào một

table mới có tên là INSERTED.

 Trigger lưu trữ dữ liệu của mẩu tin vừa xoá vào một

table có tên là DELETED.

 Trigger lưu trữ dữ liệu của mẩu tin vừa cập nhật là sự

phối hợp của 2 table DELELTED và INSERTED

4. Trigger

 Cú pháp:

CREATE TRIGGER Tên_trigger

ON tên_table| tên_view AFTER | INSTEAD OF biến_cố_kích_hoạt_trigger

-- Các câu lệnh T-SQL

Có thể thay bằng FOR. After là mặc định, chỉ định nghĩa duy nhất được trên view

4. Trigger

INSERT

CREATE TRIGGER Them_HH ON HANG_HOA AFTER AS

HANG_HOA(MaHH, TenHH)

Select * From Inserted

 Thêm dữ liệu INSERT VALUES(‘TV01’, ‘Tivi Sony’)

4. Trigger

UPDATE

CREATE TRIGGER SUA_HH ON HANG_HOA AFTER AS

 Cập nhật dữ liệu

Select * From Inserted Select * From Deleted

UPDATE HANG_HOA SET WHERE

Ten_HH = ‘Man Hinh Sony’ MaHH = ‘TV01’

4. Trigger

DELETE

CREATE TRIGGER Xoa_HH ON HANG_HOA AFTER AS

Select * From Inserted Select * From Deleted

 Xóa dữ liệu

DELETE HANG_HOA WHERE

MaHH = ‘TV01’

4. Trigger

 Các thao tác trigger phổ biến  Thêm mới mẩu tin  Xóa mẩu tin  Sửa mẩu tin

4. Trigger - Thêm mới mẩu tin

 Kiểm tra ràng buộc dữ liệu

 Khoá ngoại  Miền giá trị  Liên bộ trên một quan hệ  Liên thuộc tính trong cùng một bảng  Liên thuộc tính của nhiều bảng khác nhau

4. Trigger

 HOADON_DH(MaHD, NgayDH, MaKH)  PHIEU_XUAT(MaPX, NgayXuat, #MaHD )  CHITIET_DH(MAHD, MaHH, SoLuong, DonGia)

 Xây dựng trigger trong bảng PHIEU_XUAT để kiểm tra các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới thông tin của một phiếu xuất hàng cho một bảng hoá đơn đặt hàng trước đó. Các ràng buộc toàn vẹn dữ liệu bao gồm.  Khoá ngoại: cần kiểm tra số đặt hàng phải tồn tại trong bảng đơn

đặt hàng.

 Miền giá trị: cần kiểm tra ngày giao hàng phải ở sau ngày đặt

hàng.

4. Trigger

PHIEU_XUAT INSERT

CREATE TRIGGER tg_PhieuXuat_Insert ON FOR AS

DECLARE @NgayHD datetime, @ErrMsg varchar(200)

-- Kiểm tra số hoá đơn đã có trong bảng DONDH không? IF NOT EXISTS(Select *

From Inserted I, HOADON_DH D Where I.MaHD= D.MaHD)

Begin

Rollback Tran Raiserror(‘Số đơn đặt hàng không tồn tại’, 16,1) Return

End

4. Trigger

--Tính ra ngày đặt hàng

Select From Where

@NgayDH=NgayDH HoaDon_DH D, Inserted I D.MaHD = I.MaHD -- Kiểm tra ngày giao hàng phải sau ngày đặt hàng IF @NgayDH < (Select ngayxuat From Inserted) Begin

Set @ErrMsg = ‘ngày giao hàng phải ở sau ngày:’

+ Convert(char(10), ngayDH, 103 )

Raierror(@ErrMsg,16,1) Rollback tran

End

4. Trigger

 Kiểm tra ràng buộc

 Kiểm tra ràng buộc khóa ngoại

 Ví dụ: khi xoá một số hoá đơn đặt hàng trong bảng HOADON_DH cần phải kiểm tra các RBTV dữ liệu sau:  Kiểm tra xem đơn đặt hàng bị xoá đã được xuất hàng chưa? Nếu đã được xuất rồi thì thông báo không thể xoá đơn đặt hàng được.

 Ngược lại thì xoá dữ liệu liên quan bên bảng chi tiết đơn

đặt hàng (CHITIET_HD)

4. Trigger

tg_HOADON_Delete

TRIGGER HOADON_DH DELETE

@SoPX char(5), @ErrMsg char(200), @Delete_Err int

CREATE ON FOR AS DECLARE -- Kiểm tra xem đơn hàng đã được xuất chưa IF EXISTS(Select MaPX From PHIEU_XUAT Where MaHD IN(Select MaHD From Deleted))

Begin

Select @MaPX = MaPX From PHIEU_XUAT Where MaHD In(Select MaHD From Deleted) Set @ErrMsg = ‘Đơn đặt hàng đã được nhập theo ’+

‘số xuất hàng ’+ @SoPX + char(13) + ‘.Không thể huỷ được’

RaiseError(@ErrMsg,16,1) Rollback tran

End

4. Trigger

Else Begin -- Xoá tự động chi tiết các đơn đặt hàng liên quan

Delete FROM CHITIET_DH Where MaHD In(Select MaHD From DELETED) Set @Delete_Err = @@ERROR IF @Delete_Err <> 0 Begin

Set @ErrMsg = ‘Lỗi vi phạm xóa trên bảng chi tiết đặt

hàng’

RaisError(@ErrMsg, 16, 1) Rollback Tran

End

4. Trigger - Sửa đổi mẩu tin

 Kiểm tra ràng buộc dữ liệu

 Khoá ngoại  Miền giá trị  Liên bộ trên một quan hệ  Liên thuộc tính trong cùng một bảng  Liên thuộc tính của nhiều bảng khác nhau

4. Trigger

 Hàm Update  Ý nghĩa

 kiểm tra dữ liệu của cột bên trong bảng có bị thay đổi trong

các trigger sửa đổi dữ liệu

 Cú pháp

 UPDATE (tên_cột) (biểu thức luận lý)

 Tên_cột: tên cột mà chúng ta muốn kiểm tra xem dữ liệu

tại đó có bị sửa đổi trong trigger không.

 Biểu thức luận lý: trả về True khi giá trị dữ liệu của cột đã bị sửa đổi, ngược lại trả về False khi giá trị dữ liệu của cột không bị sửa đổi

4. Trigger

 Sửa đổi thông tin của một số đặt hàng bên trong bảng HOADON_DH cần phải kiểm tra các ràng buộc toàn vẹn dữ liệu sau:  Không cho phép sửa đổi dữ liệu tại cột MaDH hoặc MaKH

vì khi đó dữ liệu sẽ ảnh hưởng đến nhiều bảng.

 Sửa đổi giá trị cột ngày đặt hàng thì phải đảm bảo luôn luôn trước ngày giao hàng đầu tiên của số đặt hàng đó (nếu đơn đặt hàng đã có giao hàng).

4. Trigger

Declare @MinNgayXH date, @ErrMsg varchar(200)

CREATE TRIGGER tg_HOADON_DH_Update ON HOADON_DH FOR UPDATE AS -- Khi sửa đổi các cột MaDH hoặc MaKH IF Update(MaDH) OR Update(MaKH) Begin

Rollback Tran Set @ErrMsg = ‘Không thể thay đổi số đặt hàng hoặc

mã khách hàng’

RaisError(@ErrMsg, 16, 1) Return

End

4. Trigger

-- Khi sửa đổi ngày đặt hàng IF Update(NgayDH) Begin -- Kiểm tra đơn đặt hàng đã được xuất chưa

IF EXISTS (Select MaPX From PHIEU_XUAT PX, Deleted d

where px.madh=d.madh

Begin

-- Tính ra ngày nhập hàng đầu tiên

Select @MinNgayXH = Min(NgayXuat) From PHIEU_XUAT PX, DELETED D Where PX.MaDH = D.MaDH

4. Trigger

--kiểm tra giá trị ngày đăt hàng sau khi sửa đổi --phải luôn trước ngày giao hàng đầu tiên

IF @MinNgayXH < (Select NgayDH From Inserted) Begin

Rollback tran Set @ErrMsg = ‘Ngày đặt hàng phải ở trước ngày:’ + Convert(char(10), @MinNgayXH, 103) RaisError(@ErrMsg, 16, 1)

End

4. Trigger

Cài đặt trigger ngăn chặn tạo login ở cấp độ server IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = ‘rtg_KhongTaoLoginMoi') DROP TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER GO CREATE TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER FOR CREATE_LOGIN AS

PRINT N'Phi DROP trigger rtg_KhongTaoLoginMoi trưc khi to account' rollback

4. Trigger

Cho phép (enable) hoặc vô hiệu hóa (disable) trigger: ENABLE | DISABLE TRIGGER trigger_name ON {OBJECT | DATABASE | SERVER}

Cho phép trigger rtg_KhongTaoLoginMoi hoạt động trở

lại:

enable trigger rtg_KhongTaoLoginMoi

Vô hieu hóa trigger rtg_KhongTaoLoginMoi: disable trigger rtg_KhongTaoLoginMoi

4. Trigger

Hiển thị thông tin vê các triggger: Tất cả các đối tượng trong CSDL được liệt kê trong

bảng hệ thống sysobjects. Cột type trong sysobjects xác định các trigger với chữ viết tắt là TR

SELECT * FROM sysobjects WHERE type=‘TR’

4. Trigger

Cú pháp hiển thị thông tin về triggger: sp_help tên_trigger Hiển thị thông tin trigger tgr_test: Sp_help tgr_test

4. Trigger

 Câu lệnh Create triggger của mỗi trigger được lưu trữ trong bảng hệ thống syscomments. Người dùng có thể hiển thị nội dung câu lệnh trigger bằng cách sử dụng thủ tục sp_helptext

 Hiển thị nội dung trigger tgr_test:

Sp_helptext tgr_test

Biến kiểu dữ liệu cursor

 CSDL quan hệ thường làm việc trên dữ liệu có nhiều dòng mẫu tin – còn gọi là các bộ mẫu tin. Ví dụ lệnh SELECT kết quả luôn trả về nhiều dòng dữ liệu hơn là một dòng dữ liệu. Tuy nhiên có một số ngôn ngữ lập trình việc xử lý và tính toán dữ liệu trên từng dòng riêng lẻ. Để đáp ứng được yêu cầu này SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor.

Biến kiểu dữ liệu cursor

Các bước sử dụng kiểu dữ liệu cursor  Định nghĩa biến kiểu cursor bằng lệnh DECLARE. Sử dụng lệnh OPEN để mở ra cursor đã định  nghĩa trước đó.

 Đọc và xử lý trên từng dòng dữ liệu bên trong

cursor.

 Đóng cursor bằng lệnh CLOSE và DEALLOCATE.

Biến kiểu dữ liệu cursor

 Cú pháp định nghĩa biến có kiểu cursor

DECLARE

Tên_cursor CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | DYNAMIC | KEYSET]

[READ_ONLY | SCROLL_LOCK] FOR Câu_lệnh SELECT [FOR UPDATE [OF danh_sách_cột_n]]

Biến kiểu dữ liệu cursor

Trong đó:  Tên cursor: tên của biến kiểu cursor  Từ khoá LOCAL | GLOBAL: dùng chỉ phạm vi hoạt động của biến cursor hoặc là cục bộ (local) bên trong một thủ tục.

 FORWARD_ONLY: đọc dữ liệu trong cursor theo chiều đi tới duyệt từ mẫu tin đầu tiên đến mẫu tin cuối cùng.

Biến kiểu dữ liệu cursor

 SCROLL: Đọc dữ liệu trong cursor được phép di

chuyển tới lui, qua lại các dòng mẫu tin bên trong

cursor tùy thích.

Biến kiểu dữ liệu cursor

 STATIC: Đọc dữ liệu bên trong cursor tĩnh. Khi đó nếu những người dùng khác có thay đổi bên dưới dữ liệu gốc thì các thay đổi đó sẽ không được cập nhật tự động trong dữ liệu của cursor. Bởi vì khi đó dữ liệu trong cursor chính là dữ liệu của bảng tạm đã được hệ thống sao chép và lưu trữ trong CSDL tempdb của hệ thống khi định nghĩa cursor.

Biến kiểu dữ liệu cursor

 DYNAMIC: dùng chỉ định dữ liệu trong cursor là động. Khi đó việc cập nhật dữ liệu trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật tự động trong dữ liệu cursor có kiểu là DYNAMIC.

Biến kiểu dữ liệu cursor

 KEYSET: hoạt động giống với kiểu DYNAMIC, các thay đổi dữ liệu trên các cột không là khóa chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong dữ liệu cursor. Tuy nhiên đối với mẫu tin vừa thêm mới hoặc các mẫu tin đã bị hủy bỏ bởi những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET.

Biến kiểu dữ liệu cursor

 READ_ONLY: chỉ định dữ liệu trong cursor

chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên

trong cursor. Khi khai báo cursor với kiểu dữ

liệu tĩnh (STATIC) thì dữ liệu trong cursor xem

như chỉ đọc.

Biến kiểu dữ liệu cursor

 SCROLL_LOCK: chỉ định hệ thống SQL

Server tự động khóa các dòng mẫu tin cần phải

thay đổi giá trị hoặc hủy bỏ bên trong bảng

nhằm bảo đảm các hành động cập nhật luôn

thành công.

Biến kiểu dữ liệu cursor

 SELECT: dùng để chỉ đến các cột bên trong

bảng mà chúng ta cần đọc dữ liệu.

 Danh sách các cột cập nhật: chỉ định danh sách

tên các cột sẽ được phép thay đổi giá trị trong

cursor.

Biến kiểu dữ liệu cursor

 Ví dụ 1: để định nghĩa một biến cursor chứa

toàn bộ các dòng dữ liệu bên trong bảng SV, các

dòng dữ liệu trong cursor cho phép được cập

nhật.

Declare C_SV cursor dynamic

For select * From SV

Biến kiểu dữ liệu cursor

 Ví dụ 2: Định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng MH, các dữ liệu trong cursor chỉ được phép đọc và việc đọc dữ liệu trong cursor chỉ theo một chiều đi tới. Declare C_MH cursor forward_only static

Read_only

For select * From MH

Biến kiểu dữ liệu cursor

 Mở Cursor

 Cú pháp:

OPEN

Tên_cursor

 Trong đó:

Tên cursor: tên của biến cursor đã được định

nghĩa trước đó bằng lệnh DECLARE

Biến kiểu dữ liệu cursor

 Ví dụ: Mở các cursor đã định nghĩa ở ví dụ 1

trên. Chúng ta sử dụng lệnh OPEN như sau:

OPEN

C_SV

Biến kiểu dữ liệu cursor

 Đọc và xử lý dữ liệu trong cursor

FETCH [Next | Prior | First | Last |

Absolute n | Relative n ]

FROM

Tên_cursor

[INTO danh_sách_biến]

Biến kiểu dữ liệu cursor

Trong đó:  Next, Prior, First, Last: dùng để đọc dữ liệu kế tiếp,

trước, đầu, sau cùng.

 Absolute: dữ liệu chính xác thứ n trong cursor. N>0 chỉ định việc đọc dữ liệu tại dòng thứ n đếm từ dòng đầu tiên, n<0 dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối trở lên.

Biến kiểu dữ liệu cursor

Trong đó:

 Relative: dùng chỉ định việc đọc dữ liệu tại một

dòng tương đối so với dòng dữ liệu hiện hành. N

là một số nguyên có thể dương có thể âm để chỉ

định theo chiều tới hoặc lui so với dòng dữ liệu

hiện hành.

Biến kiểu dữ liệu cursor

 Tên_cursor: tên của biến cursor đã định

nghĩa trước đó bằng lệnh DECLARE.

 Danh sách biến: danh sách tên các biến cục bộ đã được định nghĩa trước đó. Các biến này sẽ lưu trữ các giá trị dữ liệu được đọc từ lệnh FETCH.

Biến kiểu dữ liệu cursor

Đóng cursor

 Cú pháp:

CLOSE Tên_cursor

DEALLOCATE

Tên_cursor

Biến kiểu dữ liệu cursor

Trong đó

 CLOSE giải phóng các dòng dữ liệu tham chiếu

bên trong cursor.

 Lệnh DEALLOCATE giải phóng thật sự biến

cursor ra khỏi bộ nhớ

Biến kiểu dữ liệu cursor

 SQL Server cung cấp một biến hệ thống

@@FETCH_STATUS dùng để kiểm tra trình

trạng đọc dữ liệu thành công hay thất bại. Giá

trị trả về 0 khi việc đọc dữ liệu là thành công.

Biến kiểu dữ liệu cursor

Cho lược đồ quan hệ như sau:

 MAT_HG (MaMH,TenMH, DVT, MaNCC)

 Pnhap (MaPN, NgayNhap,ThanhTien)

 CTPNhap (MaMH, MaPN, SLNhap, DonGia)

Biến kiểu dữ liệu cursor

 Ví dụ 1: Đọc dữ liệu cursor của bảng

MAT_HANG chỉ đọc các vật tư là Tivi

Biến kiểu dữ liệu cursor

-- Khai báo biến cursor

declare

cr_MatHang cursor

keyset

FOR

SELECT * FROM MAT_HANG

WHERE

MaMH like ‘TV%’

ORDER BY MaMH

-- Mở cursor

OPEN

cr_MatHang

Biến kiểu dữ liệu cursor

-- Đọc dữ liệu

FETCH NEXT FROM cr_MatHang

WHILE @@FETCH_STATUS = 0

BEGIN

-- Đọc tiếp dòng kế

FETCH NEXT FROM cur_MatHang

END

Biến kiểu dữ liệu cursor

-- Đóng cursor

CLOSE cr_MatHang

DEALLOCATE cr_MatHang

Biến kiểu dữ liệu cursor

 Ví dụ 2: Đọc dữ liệu cursor của bảng SV chỉ

đọc các sinh viên có họ bắt đầu là L.

Biến kiểu dữ liệu cursor

Khai báo biến cursor

Declare cr_sv cursor keyset

For select * from SV

Where tensv like ‘L%'

Order by masv

Mở cursor

OPEN

cr_SV

Biến kiểu dữ liệu cursor

Đọc dữ liệu

Fetch next from cr_sv

While @@fetch_status =0

Begin

-- Đọc tiếp dòng kế

FETCH NEXT FROM cr_SV

END

Biến kiểu dữ liệu cursor

Đóng cursor

CLOSE cr_SV

DEALLOCATE cr_SV

Biến kiểu dữ liệu cursor

 Ví dụ 3: Cập nhật dữ liệu cho cột ThanhTien trong bảng PNHAP bằng cách duyệt qua từng phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cột ThanhTien.

Biến kiểu dữ liệu cursor

-- Khai báo biến cursor, các biến cục bộ

declare @Sopn char(4), @TongTT

Money

DECLARE

cr_Pnhap

CURSOR

FORWARD_ONLY

FOR

SELECT

MAPN

FROM

PNHAP

Biến kiểu dữ liệu cursor

-- Mở cursor

OPEN

cr_Pnhap

Biến kiểu dữ liệu cursor

con

trỏ

chuyển Dịch cr_Pnhap vào @SoPN

-- Đọc dữ liệu và cập nhật giá trị fetch next from cr_Pnhap into @Sopn while @@fetch_status = 0 begin

select @Tongtt = sum(SLNhap*dongia) from ctpnhap where mapn = @sopn Print ‘dang cap nhat phieu nhap: ’ + @SoPN pnhap update set Thanhtien = @TongTT Where Current OF cr_Pnhap// mapn=@SOPN -- dịch con trỏ đến dòng kế tiếp fetch next from cr_Pnhap into @Sopn

end

Biến kiểu dữ liệu cursor

-- Đóng cursor

CLOSE cr_Pnhap

DEALLOCATE cr_Pnhap

Biến kiểu dữ liệu cursor

-- Khai báo biến cursor, các biến cục bộ

@sopn char(4), @tongtt money

declare

cr_pnhap

cursor forward_only

declare

for

select

mapn from pnhap

-- Mở cursor

Open

cr_Pnhap

Biến kiểu dữ liệu cursor

-- Đọc dữ liệu và cập nhật giá trị while (0 = 0) begin

fetch next from cur_pnhap into @sopn if @@fetch_status<>0 Break Select @Tongtg = Sum(SLNhap*dongia) From Ctpnhap where mapn = @sopn Print ‘dang cap nhat phieu nhap: ’ + @sopn Update Pnhap Set Thanhtien = @TongTT where

current of cr_pnhap

end

Biến kiểu dữ liệu cursor

-- Đóng cursor

CLOSE cr_Pnhap

DEALLOCATE cr_Pnhap

Biến kiểu dữ liệu cursor

DECLARE Tên_cursor CURSOR

{kiểu đọc | cập nhật dữ liệu}

FOR

Câu lệnh SELECT

-- Mở cursor

OPEN

Tên_cursor

Biến kiểu dữ liệu cursor

-- Đọc dữ liệu và cập nhật giá trị WHILE (0=0)

Begin

FETCH NEXT FROM [INTO danh_sách_biến] IF @@FETCH_STATUS <> 0

Break

End

Biến kiểu dữ liệu cursor

-- Đóng cursor

CLOSETên_cursor

DEALLOCATETên_cursor

Biến kiểu dữ liệu cursor

Khi nào chúng ta cần sử dụng kiểu dữ liệu cursor

trong Transaction-SQL để giải quyết các vấn đề:



SQL Server là một hệ quản trị CSDL quan hệ

(Relational Database Management System) do đó

chúng ta nên chọn giải pháp làm việc trên các bộ

mẫu tin.

Biến kiểu dữ liệu cursor

 Khi cần giải quyết vấn đề cập nhật dữ liệu thì luôn

ưu tiên chọn các hướng giải quyết trên bộ mẫu tin

bởi vì khi đó làm cho các bộ xử lý được nhanh

hơn.



Sau cùng là hướng giải quyết theo kiểu cursor là

giải pháp sau cùng nhất để chọn lựa khi không còn

giải pháp nào tốt hơn

Vô hiệu hóa ràng buộc

 Vô hiệu hóa ràng buộc

 Cho kích hoạt việc kiểm tra các ràng buộc

Alter table nocheck constraint all

120

Alter table check constraint all

5. Index

Tạo Index nhằm tăng tốc độ truy xuất dữ liệu. Thông thường, việc tạo index thường trên các thuộc tính PRIMARY KEY, UNIQUE

CREATE INDEX tên_index ON tên_bng (tên_cot1(,tên_cot2),…)

121

Tạo index tên cột TenNV trong bảng NhanVien: CREATE INDEX NV_idx ON NhanVien (TenNV)

5. Index

 Nên tạo Index trong các trường hợp sau:  Những cột thường dùng trong mệnh đề WHERE để

liên kết cột này với các bảng khác  Những cột có miền trị lớn và nhiều  Những cột có giá trị NULL lớn  Đối với các bảng lớn và truy vấn dữ liệu trên bảng

122

trả về kết quả <4% sô dòng trong bảng

5. Index

 Không nên tạo Index trong các trường hợp sau:

 Những bảng nhỏ  Những cột thường không dùng trong mệnh đề

WHERE

 Những bảng thường xuyên cập nhật dữ liệu

123

5. Index

Cú pháp câu lệnh xóa index

DROP INDEX tên_bng.tên_index

Xóa index NV_idx:

124

DROP INDEX NhanVien.NV_idx

Bài giảng Chương III: Procedure, Function, View, Trigger & Index