Bài giảng Cơ sở dữ liệu - Chương 7: Ngôn ngữ T-SQL - Đỗ Thị Mai Hường

An:
sp_testDefault @noisinh=N'Nghệ An'
• Cho biết họ tên của các sinh viên lớp Tin K26 sinh tại Đà Nẵng:
sp_testdefault @tenlop='Tin K26',@noisinh='Đà Nẵng' 
Lý thuyết CSDL 73
Biên dịch lại các thủ tục lưu trữ
 Các thủ tục lưu trữ được biên dịch lại để phản ánh sự thay
đổi tới các chỉ số.
 Có ba cách để biên dịch lại các thủ tục: 
 Sử dụng thủ tục hệ thống sp_recompile system
 Chỉ rõ WITH RECOMPILE với lệnh CREATE 
PROCEDURE 
 Chỉ rõ WITH RECOMPILE với lệnh EXECUTE 
Lý thuyết CSDL 74
Sửa các thủ tục lưu trữ
 Câu lệnh ALTER PROCEDURE được sử dụng để sửa
chữa một thủ tục lưu trữ
 Cú pháp giống như lệnh CREATE PROCEDURE
 Sự thay đổi này vẫn giữ lại các quyền người dùng
 Cp: 
ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]
[WITH RECOMPILE|ENCRYPTION| 
RECOMPILE,ENCRYPTION] AS
Lý thuyết CSDL 75
Các thông báo lỗi
 Trả về các mã hoặc lệnh RAISERROR có thể được dùng
để đưa ra các lỗi của người dùng
 Trả về mã trong thủ tục lưu trữ là các giá trị nguyên
 Lệnh RAISERROR statement ghi các lỗi và gán các cấp độ
nghiêm trọng của lỗi
Lý thuyết CSDL 76
Xoá thủ tục
• Sử dụng câu lệnh DROP PROCEDURE với cú pháp như sau:
DROP PROCEDURE 
Lý thuyết CSDL 77
Tóm tắt
Một thủ tục lưu trữ là một nhóm các câu lệnh SQL được biên
dịch lại.
Người phát triển CSDL hoặc người quản trị hệ thống viết thủ
tục để chạy các nhiệm vụ quản trị thông thường, hoặc để ứng
dụng các luật giao dịch phức tạp. Thủ tục lưu trữ chứa các thao
tác hoặc các câu lệnh truy vấn dữ liệu.
Các thủ tục lưu trữ tăng tốc độ thực thi của truy vấn, hỗ trợ truy
cập dữ liệu nhanh, hỗ trợ việc lập trình theo mô đun, duy trì
tính nhất quán, và tăng tính bảo mật.
Lý thuyết CSDL 78
Tóm tắt 
Có hai kiểu thủ tục lưu trữ:
 Các thủ tục lưu trữ yêu cầu các cơ chế đối với CSDL quản
trị, và cập nhật các bảng.
 Các thủ tục người dùng định nghĩa.
Câu lệnh CREATE PROCEDURE được sử dụng để tạo lập một
thủ tục lưu trữ người dùng dịnh nghĩa.
Câu lệnh EXECUTE được sử dụng để chạy thủ tục lưu trữ.
Các tham số có thể được sử dụng để truyền các giá trị vào và
ra từ thủ tục lưu trữ.
Lý thuyết CSDL 79
Tóm tắt 
Có ba cách để biên dịch lại các thủ tục lưu trữ:
 Sử dụng thủ tục hệ thống sp_recompile
 Chỉ rõ WITH RECOMPILE với lệnh CREATE 
PROCEDURE
 Chỉ rõ WITH RECOMPILE với lện EXECUTE
Câu lệnh ALTER PROCEDURE được sử dụng để sửa chữa
một thủ tục lưu trữ.
Trả về các mã hoặc lệnh RAISERROR được sử dụng để đưa ra
các lỗi của người sử dụng.
Lý thuyết CSDL 80
Hàm
• Hàm là đối tượng cơ sở dữ liệu tương tự như thủ
tục. 
• Điểm khác biệt giữa hàm và thủ tục: Hàm trả về một
giá trị thông qua tên hàm còn thủ tục thì không. 
• Có thể sử dụng hàm như là một thành phần của một
biêu thức (chẳng hạn, trong dsách chọn của lệnh
SELECT).
• Co hàm do HQT CSDL cung cấp sẵn
• Người sử dụng có thể định nghĩa các hàm nhằm
phục vụ cho mục đích riêng của mình
Lý thuyết CSDL 81
Hàm hệ thống
Lý thuyết CSDL 82
Ba loại hàm
– Các hàm thao tác với tập bản ghi có thể được dùng
thay cho tên các bảng trong SQL.
– Các hàm tập hợp tính toán cho ra kết quả là một giá trị
đơn nhất (ví dụ tính tổng hay trung bình).
– Các hàm vô hướng thao tác trên một giá trị và trả về
một giá trị. Các hàm này có thể được dùng trong các
biểu thức.
Lý thuyết CSDL 83
Các hàm chuyển đổi
Hàm chuyển đổi được dùng để chuyển 1 giá trị từ một
kiểu dữ liệu sang kiểu dữ liệu khác. Ngoài ra nó còn
được dùng để định dạng ngày tháng. SQL Server cung
cấp cho ta hàm chuyển đổi duy nhất là CONVERT().
Cú pháp:
CONVERT(datatype[(length)], expression [,style])
Ví dụ:
SELECT ‘EMP ID:’ + CONVERT (CHAR(4), EMPLOYEEID FROM 
EMPLOYEES
Lý thuyết CSDL 84
Date Parts 
DatePart Từ viết tắt Giá trị
Hour hh 0-23
Minute Mi 0-59
Second Ss 0-59
Millisecond Ms 0-999
Day of year Dy 1-366
Day Dd 1-31
Lý thuyết CSDL 85
Date Parts (tiếp)
Datepart Từ viết tắt Giá trị
Week wk 1-53
Weekday dw 1-7
Month mm 1-12
Quarter qq 1-4
Year yy 1753-9999
Lý thuyết CSDL 86
Các hàm ngày tháng và số học
Các hàm ngày tháng
GETDATE()
DATEADD(datepart,number,date)
DATEDIFF(datepart,date1,date2)
DATENAME(datepart,date)
DATEPART(datepart,date)
Các hàm số học
ABS(num_expr)
CEILING(num_expr)
FLOOR(num_expr)
POWER(num_expr,y)
ROUND(num_expr,length)
Sign(num_expr)
Sqrt(float_expr)
Lý thuyết CSDL 87
Các hàm hệ thống
Hàm
DB_ID([‘database_name’])
DB_NAME([database_id])
HOST_ID()
HOST_NAME()
ISNULL(expr,value)
OBJECT_ID(‘obj_name’)
OBJECT_NAME(object_id)
SUSER_SID([‘login_name’])
SUSER_ID([‘login_name’])
SUSER_SNAME([server_user_id])
SUSER_NAME([server_user_id])
USER_ID([‘user_name’])
USER_NAME([user_id])
Lý thuyết CSDL 88
Các hàm tập hợp
Hàm Giá trị trả về
Sum(col_name) Trả về giá trị tổng.
Avg(col_name) Trả về giá trị trung bình.
COUNT(*) Hàm đếm các bản ghi trong bảng thỏa
mãn điều kiện
Max(col_name) Trả về giá trị lớn nhất trong một tập giá trị.
Min(col_name) Trả về giá trị nhở nhất trong một tập hợp.
Lý thuyết CSDL 89
Định nghĩa và sử dụng hàm
Cú pháp: 
CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS 
(kiểu_trả_về_của_hàm)
AS BEGIN
các_câu_lệnh_của_hàm
END
Lý thuyết CSDL 90
VD: Định nghĩa hàm tính ngày trong tuần (thứ) của
một giá trị kiểu ngày
CREATE FUNCTION thu(@ngay DATETIME) RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @st NVARCHAR(10)
SELECT @st=CASE DATEPART(DW,@ngay)
WHEN 1 THEN 'Chu nhật'
WHEN 2 THEN 'Thứ hai'
WHEN 3 THEN 'Thứ ba'
WHEN 4 THEN 'Thứ tư'
WHEN 5 THEN 'Thứ năm'
WHEN 6 THEN 'Thứ sáu'
ELSE 'Thứ bảy' END
RETURN (@st) /* Trị trả về của hàm */
END
Lý thuyết CSDL 91
Sử dụng hàm
• Sử dụng như hàm do hqt csdl cung cấp: 
• SELECT masv,hodem,ten,dbo.thu(ngaysinh),ngaysinh
FROM sinhvien
WHERE malop=’C24102’
Lý thuyết CSDL 92
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) 
RETURNS TABLE
AS
RETURN (câu_lệnh_select)
Lý thuyết CSDL 93
Các qui tắc: 
• Kiểu trả về của hàm đượ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 (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).
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 94
VD: Định nghĩa hàm func_XemSV
CREATE FUNCTION func_XemSV(@khoa SMALLINT) RETURNS TABLE
AS
RETURN(SELECT masv,hodem,ten,ngaysinh
FROM sinhvien INNER JOIN lop
ON sinhvien.malop=lop.malop
WHERE khoa=@khoa)
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 95
Dùng hàm đã định nghĩa:
• Để biết danh sách các sinh viên khoá 25, ta sử dụng câu lệnh như sau:
SELECT * FROM dbo.func_XemSV(25)
• Còn câu lệnh dưới đây cho ta biết được danh sách sinh viên khoá 26
SELECT * FROM dbo.func_XemSV(26)
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 96
• Khi cần phải sử dụng nhiều câu lệnh trong phần thân hàm, cú pháp
đnghĩa hàm:
• CREATE FUNCTION ([]) RETURNS 
@ TABLE 
AS
BEGIN
RETURN
END
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 97
Lưu ý 
• Cấu trúc bảng trả về bởi hàm được xác định dựa vào định nghĩa của
bảng trong mệnh đề RETURNS. 
• Biến @ trong mệnh đề RETURNS có phạm vi sử dụng
trong hàm và được sử dụng như một tên bảng.
• Câu lệnh RETURN trong thân hàm không chỉ định giá trị trả về. Giá trị trả
về của hàm chính là các dòng dữ liệu trong bảng có tên là @ 
được định nghĩa trong mệnh đề RETURNS
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 98
Ví dụ dưới đây minh hoạ cách sử dụng dạng hàm
này trong SQL
CREATE FUNCTION Func_Tongsv(@khoa SMALLINT) RETURNS 
@bangthongke TABLE
(
makhoa NVARCHAR(5),
tenkhoa NVARCHAR(50),
tongsosv INT
) AS
BEGIN
IF @khoa=0
INSERT INTO @bangthongke
SELECT khoa.makhoa,tenkhoa,COUNT(masv) 
Hàm với giá trị trả về là “dữ liệu kiểu
bảng”
Lý thuyết CSDL 99
• FROM (khoa INNER JOIN lop
ON khoa.makhoa=lop.makhoa)
INNER JOIN sinhvien
on lop.malop=sinhvien.malop
GROUP BY khoa.makhoa,tenkhoa
ELSE
INSERT INTO @bangthongke
SELECT khoa.makhoa,tenkhoa,COUNT(masv) FROM (khoa INNER JOIN 
lop
ON khoa.makhoa=lop.makhoa)
INNER JOIN sinhvien
ON lop.malop=sinhvien.malop
WHERE khoa=@khoa
GROUP BY khoa.makhoa,tenkhoa
RETURN /*Trả kết quả về cho hàm*/
END
Hàm với giá trị trả về là “dữ liệu
kiểu bảng”
Lý thuyết CSDL 100
• SELECT * FROM dbo.func_TongSV(25)
Sẽ cho kết quả thống kê tổng số sinh viên khoá 25 của mỗi khoa:
• Còn câu lệnh:
SELECT * FROM dbo.func_TongSV(0)
Cho ta biết tổng số sinh viên hiện có (tất cả các khoá) của mỗi khoa
Hàm với giá trị trả về là “dữ liệu
kiểu bảng”
Lý thuyết CSDL 101
Các trigger
Lý thuyết CSDL 102
Sử dụng Trigger
 So sánh kiểu dữ liệu.
 Đọc dữ liệu từ các bảng nằm trong cơ sở dữ liệu khác.
 Thay đổi theo tầng hoặc xoá liên tục các bảng liên quan
trong một cơ sở dữ liệu
 Huỷ bỏ các thay đổi không đúng
 Tuân theo các giới hạn,các giới hạn đó phức tạp hơn việc
bắt lỗi bằng ràng buộc CHECK 
 Thực thi các thủ tục lưu cục bộ và các thủ tục lưu từ xa
Lý thuyết CSDL 103 103
- Trigger là một kiểu thủ tục được kích họat tự động
theo các sự kiện (events).
- Có 02 lọai triggers:
+ Data Modification Language –DML (For | After 
triggers, Instead-of triggers)
+ DDL triggers (For | After triggers)
Khái niệm về trigger
Lý thuyết CSDL 104 104
Data Definition Language (DDL) 
Trigger
Cú pháp:
CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ 
ENCRYPTION ] | [ EXECUTE AS CALLER | SELF | 'user_login' ] { FOR | AFTER } 
{ event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] } 
Execute As Caller là option mặc định.
Execute As User = 'user' 
Cú pháp: DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER } 
DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL } ON {DATABASE | ALL 
SERVER } [ ; ]
DDL triggers là các triggers được tự động gọi sau khi máy thực hiện các lệnh
sau:
Create Table, Drop Table, Alter Procedure, Drop Schema, Create Login, 
(Xem BOL: DDL triggers, events used for firing)
Lý thuyết CSDL 105 105
DML Triggers
• Cú Pháp:
-Tạo CREATE TRIGGER ON 
|
[With encryption|EXECUTE AS { CALLER | SELF | 'user_name' } ]
{[FOR| AFTER] [insert],[update],[delete] | Instead of}
AS Transact-SQL statements 
- Xoá Drop Trigger 
- DISABLE TRIGGER { trigger_name [ ,...n ] | ALL } ON object_name
ON: Chỉ ra rằng Trigger đang được viết cho bảng hoặc view nào. 
With encryption: nội dung của trigger sẽ được mã hóa.
Lý thuyết CSDL 106 106
Các dạng họat động của DML trigger
• AFTER (FOR): các câu lệnh bên trong trigger sẽ được thực
hiện sau khi các sự kiện tạo nên trigger đã xảy ra rồi. 
• INSTEAD OF: sẽ bỏ qua sự kiện đã kích hoạt trigger mà thay
vào đó sẽ thực hiện các dòng lệnh SQL bên trong Trigger 
Ví dụ: ta có một Update trigger trên một table với câu
INSTEAD OF: Khi đó nếu ta thực hiện việc update dữ liệu
trong bảng thì thay vì update dữ liệu, SQL Server sẽ thực
hiện các lệnh đã được viết sẵn bên trong trigger.
Lý thuyết CSDL 107
- Trigger Insert: Trigger được phát biểu bởi For insert. Trigger 
được thực hiện khi tiến hành thêm một mẫu tin vào bảng. Mẫu
tin cần thêm sẽ được lưu trong một bảng tạm có tên là Inserted.
- Trigger Delete: Trigger được phát biểu bởi For delete. Trigger 
được thực hiện khi tiến hành xóa một mẫu tin trong bảng. Mẫu
tin bị xóa sẽ được lưu trong một bảng tạm có tên là deleted.
- Trigger Update: Trigger được phát biểu bởi for update. Trigger 
được thực hiện khi tiến hành sửa một mẫu tin trong bảng. Mẫu
tin bị thay đổi sẽ được lưu trong 2 bảng tạm có tên là Inserted 
(chứa giá trị mới) và Deleted (chứa giá trị cũ).
Các kiểu Trigger
Lý thuyết CSDL 108 108
Chú ý
- Trigger không thể được tạo ra trên bảng tạm thời hay bảng hệ
thống. Trigger chỉ có thể được kích hoạt một cách tự động bởi 
một trong các event Insert, Update, Delete mà không thể chạy 
manually được. Có thể áp dụng trigger cho View.
- Inserted và Deleted là 2 table tạm chỉ chứa trên bộ nhớ và chỉ có
giá trị bên trong trigger mà thôi (nghĩa là chỉ nhìn thấy được
trong trigger mà thôi). Ta có thể dùng thông tin trong 2 table này
để so sánh data cũ và mới hoặc kiểm tra xem data mới. 
Lý thuyết CSDL 109 109
Trigger dạng INSTEAD OF – Thay thế
Dạng INSTEAD OF sẽ bỏ qua sự kiện đã kích hoạt trigger mà thay vào đó sẽ thực hiện các
dòng lệnh SQL bên trong Trigger
INSTEAD OF được chia làm 3 loại nhỏ: INSTEAD OF INSERT, INSTEAD OF UPDATE và
INSTEAD OF DELETE.
Ví dụ:create database Test1 go use Test1 go
create table HangHoa(MaHD int, NgayHD Datetime, GhiChu ntext,
MaSP NVarchar(10), Soluong int, DonGia float) go
create Table NhapHangHoa(MaHD int, NgayHD Datetime, GhiChu ntext) go
create Table NhapHangHoaCT(MaHD int,MaSP NVarchar(10), Soluong int, DonGia float) go
CREATE TRIGGER dbo.Trig_Instead ON HangHoa instead of INSERT
AS Begin
insert into NhapHangHoa select MaHD , NgayHD , GhiChu from inserted
insert into NhapHangHoaCT select MaHD ,MaSP , Soluong , DonGia from inserted
End go
insert into HangHoa values(1,getdate() , 'GhiChu ntext','MaSP', 101, 12.1)
select * from hanghoa
select * from NhapHangHoa
select * from NhapHangHoaCT
--Drop database Test1
Lý thuyết CSDL 110 110
Trigger dạng FOR 
CREATE TRIGGER Ktra_DonGia1 ON [dbo].[Products] 
AFTER INSERT
AS
If exists( Select * from inserted i where 
i.dongiamua>i.dongiaban)
Begin
RollBack tran
RaisError ('Khong hop le', 16,1)
End
Lý thuyết CSDL 111 111
Hàm Update() trong các trigger
CREATE TRIGGER Ktra_DonGia2 ON [dbo].[Products] 
FOR UPDATE AS
If Update(dongiamua) or Update(dongiaban)
Begin
If exists( Select 'true' from inserted i where 
i.dongiamua>i.dongiaban) 
Begin
RollBack tran
RaisError ('Khong hop le', 16,1)
End
End
Lý thuyết CSDL 112 112
Debug trigger
Để gỡ rối một trigger chúng ta phải viết một procedure có gọi các thao tác làm
phát sinh sự kiện để thực thi trigger. Ví dụ để kiểm tra các trigger 
Ktra_DonGia1 và Ktra_DonGia2 chúng ta viết thủ tục sau
CREATE PROCEDURE dbo.DebugTrigger as
insert into Products(Masp, Tensp, Mota, Donvitinh, Dongiamua, Dongiaban, 
VAT)
Values ( 'SP-01', ‘San Pham-01 ' , '@Mota' ,'@donvi', 20,10 , 1 )
GO
Lý thuyết CSDL 113
Tổng kết
Các trigger là các thủ tục lưu mà được thực hiện tự động để
tương tác với các tác vụ thêm, cập nhật và xoá trên một bảng. 
Các trigger thường được sử dụng để thực hiện các quy tắc
nghiệp vụ đòi hỏi.
Lệnh CREATE TRIGGER được sử dụng để tạo một trigger.
Các trigger truy nhập tới các bảng logic Inserted và Deleted. 
Các bảng này chứa các hình ảnh của dữ liệu trước đó, và sau
quá trình cập nhật.
Lý thuyết CSDL 114
Tổng kết
Các kiểu trigger:
 INSERT: Thực hiện mỗi khi xuất hiện việc thêm mới dữ
liệu vào bảng. Các trigger này đảm bảo rằng dữ liệu được
chèn vào bảng là hợp lệ.
 UPDATE: Thực hiện khi một tác vụ cập nhật xảy ra trên
một bảng. Các trigger này có thể được thi hành ở mức
bảng hoặc mức cột.
 DELETE: Thực hiện khi dữ liệu được xoá khỏi một bảng.