Giáo trình Quản trị SQL Server (Phần 2)

ai bảng SINHVIEN và LOP 
 CREATE VIEW dssv 
 AS 
 SELECT masv,hodem,ten, 
 DATEDIFF(YY,ngaysinh,GETDATE()) AS tuoi,tenlop 
 FROM sinhvien,lop 
 WHERE sinhvien.malop=lop.malop 
và nếu thực hiện câu lệnh: 
 SELECT * FROM dssv 
ta có được kết quả như sau: 
 Nếu trong câu lệnh CREATE VIEW, ta không chỉ định danh sách các tên cột cho 
khung nhìn, tên các cột trong khung nhìn sẽ chính là tiêu đề các cột trong kết quả của 
câu lệnh SELECT. Trong trường hợp tên các cột của khung nhìn đươc chỉ định, chúng 
phải có cùng số lượng với số lượng cột trong kết quả của câu truy vấn. 
 Ví dụ: Câu lệnh dưới đây tạo khung nhìn từ câu truy vấn tương tự như ví dụ trên 
nhưng có đặt tên cho các cột trong khung nhìn: 
 CREATE VIEW dssv(ma,ho,ten,tuoi,lop) 
 AS 
 74 
 SELECT masv,hodem,ten, 
 DATEDIFF(YY,ngaysinh,GETDATE()), tenlop 
 FROM sinhvien,lop 
 WHERE sinhvien.malop=lop.malop 
và câu lệnh: 
 SELECT * FROM dssv 
 Khi tạo khung nhìn với câu lệnh CREATE VIEW, ta cần phải lưu ý một số nguyên 
tắc sau: 
 − Tên khung nhìn và tên cột trong khung nhìn, cũng giống như bảng, phải tuân 
 theo qui tắc định danh 
 − Không thể qui định ràng buộc và tạo chỉ mục cho khung nhìn 
 − Câu lệnh SELECT với mệnh đề COMPUTE ... BY không được sử dụng để định 
 nghĩa khung nhìn. 
 − Phải đặt tên cho các cột của khung nhìn trong các trường hợp sau đây: 
 − Trong kết quả của câu lệnh SELECT có ít nhất một cột được sinh ra bởi một 
 biểu thức (tức là không phải là một tên cột trong bảng cơ sở) và cột đó không 
 được đặt tiêu đề. 
 − Tồn tại hai cột trong kết quả của câu lệnh SELECT có cùng tiêu đề cột. 
 Ví dụ: Câu lệnh dưới đây là câu lệnh sai do cột thứ 4 không xác định được tên cột 
 CREATE VIEW tuoisinhvien 
 AS 
 SELECT masv,hodem,ten,DATEDIFF(YY,ngaysinh,GETDATE()) 
 75 
 FROM sinhvien 
2. Cập nhật, bổ sung và xóa dữ liệu thông qua khung nhìn 
 Đối với một số khung nhìn, ta có thể tiến hành thực hiện các thao tác cập nhập, bổ 
sung và xoá dữ liệu. Thực chất, những thao tác này sẽ được chuyển thành những thao 
tác tương tự trên các bảng cơ sở và có tác động đến những bảng cơ sở. 
 Về mặt lý thuyết, để có thể thực hiện thao tác bổ sung, cập nhật và xoá, một 
khung nhìn trước tiên phải thoả mãn các điều kiện sau đây: 
 − Trong câu lệnh SELECT định nghĩa khung nhìn không được sử dụng từ khoá 
 DISTINCT, TOP, GROUP BY và UNION. 
 − Các thành phần xuất hiện trong danh sách chọn của câu lệnh SELECT phải là 
 các cột trong các bảng cơ sở. Trong danh sách chọn không được chứa các biểu 
 thức tính toán, các hàm gộp. 
 Ngoài những điều kiện trên, các thao tác thay đổi đến dữ liệu thông qua khung 
nhìn còn phải đảm bảo thoả mãn các ràng buộc trên các bảng cơ sở, tức là vẫn đảm bảo 
tính toàn vẹn dữ liệu. Ví dụ dưới đây sẽ minh hoạ cho ta thấy việc thực hiện các thao 
tác bổ sung, cập nhật và xoá dữ liệu thông qua khung nhìn. 
 Ví dụ: Xét định nghĩa hai bảng DONVI và NHANVIEN như sau: 
 CREATE TABLE donvi 
 ( 
 madv INT PRIMARY KEY, 
 endv NVARCHAR(30) NOT NULL, 
 dienthoai NVARCHAR(10) NULL 
 ) 
 CREATE TABLE nhanvien 
 ( 
 manv NVARCHAR(10) PRIMARY KEY, 
 hoten NVARCHAR(30) NOT NULL, 
 ngaysinh DATETIME NULL, 
 diachi NVARCHAR(50) NULL, 
 madv INT FOREIGN KEY 
 76 
 ON DELETE CASCADE 
 ON UPDATE CASCADE 
 REFERENCES donvi(madv) 
 ) 
 Giả sử trong hai bảng này đã có dữ liệu như sau: 
 Bảng DONVI 
 Bảng NHANVIEN 
 Câu lệnh dưới đây định nghĩa khung nhìn NV1 cung cấp các thông tin về mã nhân 
viên, họ tên và mã đơn vị nhân viên làm việc: 
 CREATE VIEW nv1 
 AS 
 SELECT manv,hoten,madv FROM nhanvien 
 Nếu ta thực hiện câu lệnh 
 INSERT INTO nv1 VALUES('NV04','Le Thi D',1) 
 Một bản ghi mới sẽ được bổ sung vào bảng NHANVIEN và dữ liệu trong bảng này 
sẽ là: 
 Bản ghi 
 mới 
 Thông qua khung nhìn này, ta cũng có thể thực hiện thao tác cập nhật và xoá dữ 
liệu. 
 Chẳng hạn, nếu ta thực hiện câu lệnh: 
 77 
 DELETE FROM nv1 WHERE manv='NV04' 
 Thì bản ghi tương ứng với nhân viên có mã NV04 sẽ bị xoá khỏi bảng 
NHANVIEN 
 Nếu trong danh sách chọn của câu lệnh SELECT có sự xuất hiện của biểu thức 
tính toán đơn giản, thao tác bổ sung dữ liệu thông qua khung nhìn không thể thực hiện 
được. Tuy nhiên, trong trường hợp này thao tác cập nhật và xoá dữ liệu vấn có thể có 
khả năng thực hiện được (hiển nhiên không thể cập nhật dữ liệu đối với một cột có 
được từ một biểu thức tính toán). 
 Ví dụ: Xét khung nhìn NV2 được định nghĩa như sau: 
 CREATE VIEW nv2 
 AS 
 SELECT manv,hoten,YEAR(ngaysinh) AS namsinh,madv 
 FROM nhanvien 
 Đối với khung nhìn NV2, ta không thể thực hiện thao tác bổ sung dữ liệu nhưng có 
thể cập nhật hoặc xoá dữ liệu trên bảng thông qua khung nhìn này. Câu lệnh dưới đây là 
không thể thực hiện được trên khung nhìn NV2 
 INSERT INTO nv2(manv,hoten,madv) 
 VALUES('NV05','Le Van E',1) 
 Nhưng câu lệnh: 
 UPDATE nv2 SET hoten='Le Thi X' WHERE manv='NV04' 
 hoặc câu lệnh 
 DELETE FROM nv2 WHERE manv='NV04' 
 lại có thể thực hiện được và có tác động đối với dữ liệu trong bảng NHANVIEN 
Trong trường hợp khung nhìn được tạo ra từ một phép nối (trong hoặc ngoài) trên nhiều 
bảng, ta có thể thực hiện được thao tác bổ sung hoặc cập nhật dữ liệu nếu thao tác này 
chỉ có tác động đến đúng một bảng cơ sở (câu lệnh DELETE không thể thực hiện được 
trong trường hợp này). 
 Ví dụ: Với khung nhìn được định nghĩa như sau: 
 CREATE VIEW nv3 
 AS 
 SELECT manv,hoten,ngaysinh, diachi,nhanvien.madv 
 78 
 AS noilamviec, donvi.madv, tendv, dienthoai 
 FROM nhanvien FULL OUTER JOIN donvi 
 ON nhanvien.madv=donvi.madv 
 Câu lệnh: 
 INSERT INTO nv3(manv,hoten,noilamviec) 
 VALUES('NV05','Le Van E',1) 
 sẽ bổ sung thêm vào bảng NHANVIEN một bản ghi mới. 
 Hoặc câu lệnh: 
 INSERT INTO nv3(madv,tendv) VALUES(3,'P. Ke toan') 
 bổ sung thêm vào bảng DONVI một bản ghi do cả hai câu lệnh này chỉ có tác 
động đến đúng một bảng cơ sở. 
 Câu lệnh dưới đây không thể thực hiện được do có tác động một lúc đến hai 
bảng cơ sở. 
 INSERT INTO nv3(manv,hoten,noilamviec,madv,tendv) 
 VALUES('NV05','Le Van E',1,3,'P. Ke toan') 
3. Sửa, xóa khung nhìn 
 • Sửa đổi khung nhìn 
 Câu lệnh ALTER VIEW được sử dụng để định nghĩa lại khung nhìn hiện có 
nhưng không làm thay đổi các quyền đã được cấp phát cho người sử dụng trước đó. 
 Câu lệnh này sử dụng tương tự như câu lệnh CREATE VIEW và có cú pháp như 
sau: 
 ALTER VIEW tên_khung_nhìn [(danh_sách_tên_cột)] 
 AS 
 Câu_lệnh_SELECT 
 Ví dụ: Ta định nghĩa khung nhìn như sau: 
 CREATE VIEW viewlop 
 AS 
 SELECT malop,tenlop,tenkhoa 
 79 
 FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa 
 WHERE tenkhoa='Khoa Vật lý’ 
 và có thể định nghĩa lại khung nhìn trên bằng câu lệnh: 
 ALTER VIEW view_lop 
 AS 
 SELECT malop,tenlop,hedaotao 
 FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa 
WHERE tenkhoa='Khoa 
 • Xóa khung nhìn 
 Khi một khung nhìn không còn sử dụng, ta có thể xoá nó ra khỏi cơ sở dữ liệu 
thông qua câu lệnh: 
 DROP VIEW tên_khung_nhìn 
 Nếu một khung nhìn bị xoá, toàn bộ những quyền đã cấp phát cho người sử dụng 
trên khung nhìn cũng đồng thời bị xoá. Do đó, nếu ta tạo lại khung nhìn thì phải tiến 
hành cấp phát lại quyền cho người sử dụng. 
 Ví dụ: Câu lệnh dưới đây xoá khung nhìn VIEW_LOP ra khỏi cơ sở dữ liệu 
 DROP VIEW view_lop 
 80 
 Bài 5: Thủ tục lưu trữ (Stored Procedure) 
Thủ tục lưu trữ (Stored Procedure): 
 − Là một nhóm câu lệnh Transact-SQL đã được compiled (biên dịch) 
 − Được lưu trữ trong CSDL dưới một tên phân biệt 
 − Được xử lý như một đơn vị (chứ không phải nhiều câu lệnh SQL riêng lẻ) 
 − Có tham số Input và Output 
 − Cho phép gọi thực hiện từ các ngôn ngữ lập trình 
Thủ tục lưu trữ thường dùng thực hiện truy vấn hay cập nhật dữ liệu theo yêu cầu của 
người dùng. 
Khi một thủ tục lưu trữ đã được định nghĩa, nó có thể được gọi thông qua tên thủ tục, 
nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong thủ tục và có thể trả 
về các giá trị sau khi thực hiện xong. 
Thủ tục lưu trữ có thể có các thành phần sau: 
 − Các cấu trúc điều khiển (IF, WHILE, FOR) 
 − Các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính toán được, 
 các giá trị được truy xuất được từ cơ sở dữ liệu. 
 − Một tập các câu lệnh SQL được kết hợp lại với nhau thành một khối lệnh bên 
 trong một thủ tục. 
 − Một thủ tục có thể nhận các tham số truyền vào, có thể trả về các giá trị thông 
 qua các tham số (như trong các ngôn ngữ lập trình). 
 81 
1. Tạo, gọi thủ tục lưu trữ 
1.1. Cú pháp tạo thủ tục lưu trữ 
CREATE PROC[EDURE] tên_thủ_tục [(các_tham_số_hình_thức)] 
[WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION] 
AS 
[BEGIN] 
các_câu_lệnh_của_thủ_tục 
[END] 
Trong đó: 
 − tên_thủ_tục: Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và 
 không được vượt quá 128 ký tự. 
 − các_câu_lệnh_của_thủ_tục: Tập hợp các câu lệnh sử dụng trong nội dung thủ 
 tục. 
 − các_tham_số_hình_thức: Nếu nhiều tham số thì các khai báo phân cách nhau 
 bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: 
 + tên tham số được bắt đầu bởi dấu @. 
 + kiểu dữ liệu của tham số 
 + Ví dụ: @MaMonHoc nvarchar(10) 
 − RECOMPILE : Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở 
 lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ 
 được dịch lại mỗi khi được gọi. 
 − ENCRYPTION: Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION 
 được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung 
 của thủ tục. 
 82 
Ví dụ: 
Xây dựng thủ tục liệt kê tên các môn học có mã bắt đầu bằng "MD" 
Create Proc SP_MonHocLaMoDun 
As 
 Select MaMH, TenMH 
 From MonHoc 
 Where MaMH like 'MD%' 
1.2. Lời gọi thủ tục lưu trữ 
Tên_thủ_tục [Danh_sách_đối_số] 
Hoặc EXEC Tên_thủ_tục [Danh_sách_đối_số] 
(Trong trường hợp lời gọi thủ tục được thực hiện bên trong một thủ tục khác) 
Ví dụ 1: SP_MonHocLaMoDun 
Hoặc Exec SP_MonHocLaMoDun 
Ví dụ 2: SP_DSLopCuaKhoa Hoặc Exec SP_DSLopCuaKhoa 
 → Hiện danh sách lớp của khoa có mã CNTT 
 Exec SP_DSLopCuaKhoa ‘KT’ 
 → Hiện danh sách lớp của khoa có mã KT 
 83 
 2. Khai báo tham số, biến trong thủ tục 
 @tên_tham_số kiểu_dữ_liệu [= giá_trị_mặc_định] [OUTPUT] 
Tham số hình thức có 2 loại: 
 − Input: Nhận giá trị từ người dùng gửi vào cho thủ tục xử lý 
 @tên_tham_số kiểu_dữ_liệu [= giá_trị_mặc_định] 
 Khi có khai báo giá trị mặc định: tham số hình thức sẽ nhận giá trị mặc định 
 nếu không truyền giá trị cho nó trong lời gọi thủ tục 
 − Output: Nhận kết quả trả về từ thủ tục 
 Trong trường hợp cần giữ lại sự thay đổi giá trị của tham số sau khi kết thúc thủ 
 tục, ta sử dụng OUTPUT 
 @tên_tham_số kiểu_dữ_liệu OUTPUT 
Ví dụ: 
Xây dựng thủ tục cho biết danh sách các lớp của một mã khoa cho trước 
Create Proc SP_DSLopCuaKhoa (@MaKhoa nvarchar(10) = 'CNTT') 
AS 
 Select TenLop 
 From Lop inner join Khoa 
 on Lop.MaKhoa = Khoa.MaKhoa 
 Where Lop.MaKhoa = @MaKhoa 
3. Khai báo và sử dụng biến trong thủ tục 
3.1. Biến cục bộ 
Biến cục bộ là một đối tượng có thể chứa giá trị thuộc một kiểu dữ liệu nhất định, tên 
biến bắt đầu bằng một ký tự @ 
Khai báo biến cục bộ: 
 Declare Tên_biến Kiểu_dữ_liệu 
Ví dụ: 
 ▪ Declare @MaSV nvarchar(10) 
 ▪ Declare @NgaySinh datetime 
 84 
 ▪ Declare @SoTiet int 
 ▪ Declare @Diem float 
Gán giá trị cho biến cục bộ: 
 ▪ Set Tên_biến = Giá_trị 
 ▪ Set Tên_biến = Tên_biến 
 ▪ Set Tên_biến = Biểu_thức 
 ▪ Set Tên_biến = Kết_quả_truy_vấn 
Giá trị gán cho biến phải phù hợp với kiểu dữ liệu của biến. 
 ▪ Set @MaSV = ‘SV0101’ 
 ▪ Set @SoTiet = 60 
 ▪ Set @SoSV = (Select count(*) From SinhVien) 
 ▪ Set @Diem = 7.5 
Đưa kết quả truy vấn vào biến 
 ▪ Select @Ho = HoDem, @Ten = Ten 
 From SinhVien 
 Where MaSV = ‘SV0101’ 
3.2. Biến toàn cục 
Biến toàn cục là các biến hệ thống do SQL Server cung cấp, tên biến bắt đầu bằng 2 ký 
tự @. Người sử dụng không thể gán giá trị trực tiếp cho các biến này. 
Một số biến hệ thống: 
 ▪ @@error: thông báo mã lỗi, nếu @@error = 0: thao tác thực hiện thành 
 công. 
 ▪ @@rowcount: cho biết số dòng bị ảnh hưởng bởi lệnh cuối (insert, 
 update, delete) 
 4. Sử dụng cấu trúc điều khiển trong thủ tục 
 4.1. Sử dụng cấu trúc IfElse 
Cấu trúc IfElse xét điều kiện để quyết định những lệnh T-SQL nào sẽ được thực 
hiện 
Cú pháp: 
 85 
If Biểu_thức_điều_kiện 
 Khối_lệnh 
 [ Else Khối_lệnh ] 
Khối lệnh là một hoặc nhiều lệnh nằm trong cặp từ khóa Begin.....End 
Ví dụ: 
Cho 2 mã SV bất kỳ, kiểm tra xem 2 SV đó có cùng ngày sinh hay không? 
Create Proc SP_SVCungNgaySinh 
(@MaSV1 nvarchar(10), @MaSV2 nvarchar(10)) 
As 
Begin 
 declare @NS1 smalldatetime 
 Select @MaKhoa1 = MaKhoa 
 From SinhVien sv inner join Lop l on sv.MaLop = l.MaLop 
 Where MaSV = @MaSV1 
 declare @NS2 smalldatetime 
 Select @MaKhoa2 = MaKhoa 
 From SinhVien sv inner join Lop l on sv.MaLop = l.MaLop 
 Where MaSV = @MaSV2 
 If @NS1 = @NS2 
 print N'2 SV có cùng ngày sinh' 
 Else 
 print N'2 SV khác ngày sinh với nhau' 
End 
Exec SP_SVCungNgaySinh 'SV0101','SV0102' 
 4.2. Sử dụng cấu trúc While 
Thực hiện lặp lại một đoạn lệnh T-SQL khi điều kiện đúng 
 86 
Cú pháp: 
While Biểu_thức_điều_kiện 
[Begin] 
 Khối_lệnh 
 [Break] 
 [Continue] 
[End] 
Trong đó: 
 − Break: thoát khỏi vòng while hiện hành 
 − Continue: trở lại đầu vòng while, bỏ qua các lệnh sau đó 
Ví dụ: 
Xét lược đồ quan hệ: SV(MaSV: int, HoTen: nvarchar(40)) 
Viết lệnh xác định 1 mã SV mới theo quy định: Mã SV tăng dần, nếu có chỗ trống thì 
mã mới sẽ chèn vào chỗ trống đó. Chẳng hạn nếu trong bảng SV đã có các mã SV 
1,2,3,6 thì mã SV mới là 4 
Declare @STT int 
Set @STT = 1 
While exists(select * from SV where MaSV = @STT) 
 set @STT = @STT+1 
Insert into SV(MaSV, HoTen) values(@STT, N 'Nguyễn Văn Nam') 
 4.3. Sử dụng cấu trúc Case 
Kiểm tra một dãy các điều kiện và trả về kết quả phù hợp với điều kiện đúng. 
Lệnh Case được sử dụng như một hàm trong câu lệnh Select 
Cú pháp: 
Dạng 1 (simple case): 
Case Biểu_thức_đầu_vào 
When Giá_trị then Kết_quả 
 87 
[...n] 
[ Else Kết_quả_khác] 
End 
Dạng 2 (searched case): 
Case 
When Biểu_thức_đk then Kết_quả 
[...n] 
[ Else Kết_quả_khác] 
End 
Ví dụ: 
Xét lược đồ quan hệ: 
 NhanVien(MaNV: int, 
 HoTen: nvarchar(40), 
 GioiTinh: nvarchar(3), 
 NgaySinh: datetime, 
 CapBac: smallint ) 
Cho biết những nhân viên đến tuổi nghỉ hưu biết rằng tuổi về hưu của nam là 60, của nữ là 
55 
select * 
from NhanVien 
where year(getdate()) – year(NgaySinh) > = Case GioiTinh 
 when ‘Nam’ then 60 
 when ‘Nu’ then 55 
 End 
5. Sửa, xóa thủ tục lưu trữ 
5.1. Sửa thủ tục lưu trữ 
Khi một thủ tục đã được tạo ra, ta có thể tiến hành định nghĩa lại thủ tục đó bằng câu 
lệnh ALTER PROCEDURE có cú pháp như sau: 
 88 
ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)] 
[WITH RECOMPILE | ENCRYPTION 
 | RECOMPILE,ENCRYPTION] 
AS 
Các_câu_lệnh_Của_thủ_tục 
5.2. Xóa thủ tục lưu trữ 
Drop procedure tên_thủ_tục 
 89