Giáo Trình SQL bài 13-14-15

1
CURSOR
2
1. Khái niệm:
Cursor là một đối tượng của CSDL mà nó hỗ trợ cho phép truy xuất và thao tác dữ liệu trong một tập kết quả. Sau khi cursor được định vị trên một dòng, các hoạt động có thể thực hiện trên dòng đó.
Dùng Cursor để:
• Định vị một dòng đặc biệt trong tập kết quả.
• Truy xuất một hoặc khối dòng bắt đầu từ vị trí cursor trong tập kết quả.
• Cung cấp thao tác hiệu chỉnh dữ liệu cho các dòng tại vị trí của Cursor trong tập kết quả.
• Cung cấp các mức độ khác nhau của tính tường minh trong sự thay đổi đước tạo bởi những người dùng khác đến tập kết quả.
3
Khi sử dụng cursor trong SQL Server:
• Cursor cần phải khai báo và các thuộc tính của nó cũng cần được xác định
• Mở cursor.
• Phải lấy (fetch) các dòng cần thiết từ cursor.
• Dữ liệu trong dòng hiện hành có thể được hiệu chỉnh nếu cần thiết.
• Tạm thời không dùng cursor thì phải đóng cursor lại.
• Cursor cần phải được giải phóng khi không cần dùng nữa.
4
2. Phân Loại Cursor:
a. Static Cursor:
• Tập kết quả của con trỏ loại Static được xây dựng trong tempdb khi con trỏ được mở.
• Con trỏ không phản ánh được bất kỳ sự thay đổi nào trong database ngay cả khi những dòng dữ liệu có thay đổi.
• Tất cả thao tác Insert, Update, Delete đều không có tác dụng khi dùng static cursor
5
b. Keyset-driven Cursor:
• Thành viên và thứ tự của các dòng trong một keyset-driven cursor là cố định khi cursor được mở. Con trỏ được điều khiển bởi một tập giá trị nhận dạng gọi là Keyset. Keyset được xây dựng trong Tempdb khi con trỏ được mở.
• Cho phép hiệu chỉnh (update, delete) dữ liệu trên cột không là keyset (bởi chủ cursor hoặc từ user khác) khi user duyệt thông qua con trỏ.
• Inserts made by other users are not visible.
6
c. Dynamic Cursor:
• Cursor phản ánh được toàn bộ sự thay đổi của các dòng dữ liệu trong tập kết quả khi duyệt Cursor
• Giá trị dữ liệu, thứ tự, và thành viên của các dòng trong tập kết quả có thể thay đổi ứng với mỗi lần duyệt con trỏ.
• Tất cả các lệnh Insert, Update, Delete của các user đều hữu hiệu thông qua con trỏ.
d. Fast Forward Cursor:
Chỉ duyệt con trỏ theo một chiều từ First  Last
7
13.2 Làm việc với T-SQL server cursors
Khai báo cursor Bằng câu lệnh declare ở sau từ khóa AS trong stored procedures hoặc functions

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC |FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR UPDATE [OF column_name [,…n]]]
8
Ghi chú:
• Select_statement: Là câu lệnh truy vấn để định nghĩa tập kết quả của cursor. Từ khóa COMPUTE, COMPUTE BY, FOR BROWSE, and INTO không cho phép trong select_statement
• READ ONLY không cho phép Update trong cursor này.
• UPDATE [OF column_name [,…n]]: Quy định cột cho phép được update khi dùng cursor. Nếu OF column_name [,n…] được chỉ định rõ ràng thì chỉ có các cột được chỉ định mới được cho phép hiệu chỉnh, nếu không có column list, all columns có thể update.
9
Mở cursor: Cursor được open sau khai báo
OPEN {cursor_name}
Lấy mẫu tin hoặc điều hướng cursor (FETCH)

FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | REALTIVE n ] ]
FROM cursor_name [ INTO @variable_name [,…n]]

Truy xuất từng dòng dữ liệu. Kiểm tra phạm vi con trỏ bằng @@Fetch_status
@@fetch_status: Trả về 1 con trỏ đã được dời qua LAST. Trả về 0 vẫn còn trong phạm vi duyệt con trỏ. vòng lặp While để duyệt cả tập kết quả của Cursor
INTO @variable_name[,...n]: Allows data from the columns of a fetch to be placed into local variables
10
Có thể dùng các câu lệnh Update hoặc Delete để hiệu chỉnh dữ liệu.
Sử dụng dữ liệu của mẫu tin hiện hành: Dữ liệu gán cho các biến tương ứng trong câu lệnh Fetch
Cập nhật dữ liệu thông qua cursor: Thực chất là dữ liệu được hiệu chỉnh trực tiếp vào trong bảng.
Cập nhật giá trị cho cột
UPDATE
SET = [,…n]
WHERE CURRENT OF
Xóa dữ liệu thông qua cursor: Thực sự là dữ liệu xóa trên bảng
DELETE
WHERE CURRENT OF
11
Đóng Cursor
Kết thúc hành động của cursor cho lần mở (open), nó vẫn hiện hữu cho đến khi gặp một lệnh Open khác hoặc gặp lệnh Close cursor.

CLOSE cursor_name

Giải phóng Cursor
Giải phóng cursor, hủy bỏ tham chiếu đến con trỏ từ session hiện hành. Tiến trình này làm cho tài nguyên trở về trạng thái sẵn sàng truy xuất.

DEALLOCATE cursor_name
12
DECLARE contro CURSOR FOR
Select mahang, tenhang, soluong From mathang
OPEN contro
Declare @mahang Nvarchar(10)
Declare @tenhang Nvarchar(10)
Declare @soluong Int

FETCH NEXT FROM contro
INTO @mahang, @tenhang, @soluong
While @@FETCH_STATUS = 0
Begin
Print `Ma hang: `+@mahang
Print `Ten hang:`+@tenhang
Print `So luong: `+STR(@soluong)
FETCH NEXT FROM contro
INTO @mahang, @tenhang, @soluong
End
CLOSE contro
DEALLOCATE contro
13
Declare MyCursor CURSOR FOR
Select c.CustomerId, c.CompanyName, o.OrderID, o.OrderDate
From Customers c Inner Join Orders o
ON c.CustomerID = o.CustomerID
FOR UPDATE
OPEN MyCursor

Declare @cid varchar (8), @cn varchar(80), @o int, @od datetime

FETCH NEXT FROM MyCursor
INTO @cid, @cn, @o, @od
Select @cid, @cn

Begin TRANSACTION
Update Customers SET CompanyName = `q`
Where Current Of MyCursor
Deallocate MyCursor
Select * From Customers Where CompanyName = `q`
Rollback TRANSACTION
14
TRIGGER
15
I. Khái niệm cơ bản về Trigger:
Cũng tương tự như store procedure, một Trigger là một đối tượng chứa một tập các câu lệnh SQL và tập các câu lệnh này sẽ được thực thi khi trigger được gọi.
Điểm khác biệt giữa store procedure và trigger là: các store procedure được thực thi khi người sử dụng có lời gọi đến chúng còn các trigger lại được “gọi” TỰ ĐỘNG khi xảy ra những giao tác làm thay đổi dữ liệu trong các bảng.
Mỗi một trigger được tạo ra và gắn liền với một bảng nào đó trong cơ sở dữ liệu. Khi dữ liệu trong bảng bị thay đổi thì trigger sẽ được tự đông kích hoạt.
16
Dùng trigger một cách hợp lý trong cơ sở dữ liệu sẽ có tác động rất lớn trong việc tăng hiệu năng của CSDL:
Một trigger có thể nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong cơ sở dữ liệu.
Các thao tác trên dữ liệu (Delete, Insert, Update) có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác trên CSDL nhằm đảm bảo tính hợp lệ của dữ liệu
Thông qua trigger, ta có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong cơ sở dữ liệu mà bản thân các ràng buộc không thể thực hiện được.
17
1. Định nghĩa Trigger:

CREATE TRIGGER tên_trigger
ON tên_bảng
[ WITH ENCRYPTION ]
FOR { [INSERT] [,] [UPDATE] [,] [DELETE] }
AS
[IF UPDATE (tên_cột)
[AND UPDATE (tên_cột) | OR UPDATE (tên_cột)] ...]
các_câu_lệnh_của_trigger
18
Create Trigger tg_NhatKyBanHang_insert
On NhatKyBanHang
FOR INSERT
AS
Update MatHang
Set SoLuong = m.SoLuong - i.SoLuong
From Mathang as m Inner Join Inserted as i
On m.MaHang = i.MaHang
19
Với trigger vừa tạo ở trên, nếu dữ liệu trong bảng MatHang là:



Insert Into NhatKyBanBang
(NgayBan, NguoiMua, MaHang, SoLuong, GiaBan)
Values(`5/5/2004`, N`Trần Ngọc Thanh`, `H1`, 10, 5200)
Dữ liệu trong bảng MatHang sau khi ta thực hiện câu lênh Insert trên:
20
Hai bảng logic Inserted và Deleted để sử dụng trong các trigger. Cấu trúc của hai bảng này tương tự như cấu trúc của bảng mà trigger tác động. Dữ liệu trong hai bảng này tuỳ thuộc vào câu lệnh tác động lên bảng làm kích hoạt Trigger:
Khi câu lệnh Delete được thực thi trên bảng, các dòng dữ liệu bị xoá sẽ được sao chép vào trong bảng Deleted. Trong trường hợp này Bảng Inserted không có dữ liệu.
Dữ liệu trong bảng Inserted sẽ là dòng dữ liệu được bổ sung vào bảng gây nên sự kích hoạt đối với trigger bằng câu lệnh Insert. Trong trường hợp này Bảng Deleted không có dữ liệu.
21
Khi câu lệnh UPDATE được thực thi trên bảng:
Các dòng dữ liệu cũ chịu sự tác động của câu lệnh sẽ được sao chép vào bảng Deleted
Còn trong bảng Inserted sẽ là các dòng sau khi đã được cập nhật.
Thay vì chỉ định một trigger được kích hoạt trên một bảng, ta có thể chỉ định trigger được kích hoạt và thực hiện những thao tác cụ thể khi việc thay đổi dữ liệu chỉ liên quan đến một số cột nhất định nào đó của cột.
Trong trường hợp này, ta sử dụng mệnh đề IF UPDATE trong trigger. IF UPDATE không sử dụng được đối với câu lệnh DELETE.
22
Xét lại ví dụ với hai bảng MatHang và NhatKyBanHang, trigger dưới đây được kích hoạt khi ta tiến hành cập nhật duy nhất 1 cột Soluong cho bảng NhatKyBanHang
Create Trigger tg_nhatkybanhang_update_soluong
ON NhatKyBanHang
FOR UPDATE
AS
IF UPDATE(soluong)
Update mathang
Set MatHang.soluong = MatHang.soluong -
(Inserted.soluong - Deleted.soluong)
From (Deleted Inner Join Inserted On
Deleted.stt = Inserted.stt) Inner Join MatHang On MatHang.MaHang = Deleted. MaHang
23
Với trigger ở ví dụ trên, câu lệnh:
Update NhatKyNanHang
Set soluong=soluong+20
Where stt=1

Sẽ kích hoạt Trigger ứng với mệnh đề IF UPDATE (soluong), câu lệnh Update trong trigger sẽ được thực thi.

Tuy nhiên câu lệnh sau không kích hoạt trigger này.
Update NhatKyNanHang
Set nguoimua=N`Mai Hữu Toàn`
Where stt=3
24
Bảo mật trong SQL Server
15
Bài
25
Windows Authentication Mode:
Windows Authentication: User phải là những User được Windows quản lý mới được truy nhập.
Mixed Mode:
Windows and SQL Server Authentication: User của Windows và SQL Server để có thể thiết lập để truy nhập SQL Server
CHẾ ĐỘ BẢO MẬT – SECURITY MODE
26
Role là đối tượng xác định nhóm thuộc tính để gán quyền cho các user tham gia khai thác SQL Server
Server Role (xem bảng – Giáo trình –Tr.133)
Về cơ bản giống Server Role giống các Group trong Windwos server
Một thành viên của bất kỳ thành Server Role nào đều có thể thêm các User khác có trong Sever Role đó
Database Role (xem bảng – Giáo trình –Tr.136)
Các quyền truy xuất CSDL phải được cấp rõ ràng bởi một system administrator hoặc thành viên của administrator role trong CSDL
SERVER ROLE và DATABASE ROLE
27
Người dùng trong SQL Server được chia thành 2 mức: Người truy nhập vào SQL Server gọi là Login, người khai thác CSDL gọi là User.
Mức Login:
Là đối tượng được quyền truy nhập vào SQL Server, tùy theo chế độ bảo mật của SQL Server mà những login là account của Windows NT hay của SQL Server.
Mức User:
User là đối tượng khai thác CSDL, nếu login chỉ xác định truy nhập vào SQL Server thì User là login ID tham gia khai thác CSDL.
QUẢN TRỊ NGƯỜI DÙNG
28
sp_addlogin `login_name` , `password` , `database_name`
sp_droplogin `login_name`
sp_grantdbaccess `login_name`
sp_revokedbaccess `login_name`
sp_helprole
sp_addrolemember `role_name` , `login_name`
sp_droprolemember `role_name` , `login_name`
sp_helpsrvrole
sp_addsrvrolemember `login_name ` , `role_name`
sp_dropsrvrolemember `login_name ` , `role_name`
Tạo User Gán Role bằng câu lệnh
(Xem thêm giáo trình trang 145-147)
29
Vd1:
sp_addlogin `admin` , `123456` , `QLyDiem`
sp_grantdbaccess `admin`
sp_addrolemember `db_owner` , `admin`
sp_addsrvrolemember `admin` , `sysadmin`
Tạo User Gán Role bằng câu lệnh
(Xem thêm giáo trình trang 144-147)
30
Database role
Server role
Tạo User và gán Role
cho User bằng
Enterprise Manager
xem giáo trình
trang 140->144