Lập trình T - SQL và các quản lý bảo mật
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (269.66 KB, 12 trang )
Phần 8 Lập trình T-SQL , Các giao dịch & Quản lý bảo mật
(Thực hành)
Mục tiêu bài học:
Cuối bài học này các bạn có thể:
Viết các câu lệnh T-SQL để thi hành các kiểu giao dịch khác nhau
Định nghĩa và sử dụng các biến cục bộ và biến toàn cục
Tạo lập các đăng nhập và người dùng
Các bước đưa ra trong chương này rất chi tiết, toàn diện và tỷ mỉ. Các phần thực hành được bám
sát theo mục tiêu của bài học và giúp chúng ta hiểu và sử dụng các công cụ một cách trọn vẹn.
Bạn hãy thực hành cẩn thận theo từng bước dưới đây.
Phần I: Hướng dẫn học Thời gian: 30 phút
Phần thực hành này được kết hợp với 3 chương lý thuyết trước. Bởi vì các bài tập và các phần
hướng dẫn thực hành sẽ bao gồm tất cả các khái niệm và các kỹ năng đã được giới thiệu trong các
chương này. Mục tiêu của buổi thực hành này để giúp sinh viên hiểu về các câu lệnh T-SQL, cách
sử dụng các biến toàn cục & cục bộ và các hàm thư viện. Các ví dụ và các bài tập trong phần thực
hành này sẽ tập trung vào những chủ đề này. Chúng ta sẽ sử dụng CSDL NorthWind cho các ví
dụ của chúng ta.
8.1 Các giao dịch
Giao dịch là một nhóm các thao tác của SQL Server kết hợp lại thành một nhóm đơn lẻ công việc.
Có các kiểu giao dịch sau đây:
Các giao dịch ngầm định
Các giao dịch tường minh
Các giao dịch tự động xác nhận lệnh (Auto-commit)
8.1.2 Các giao dịch ngầm định
Khi một kết nối đang làm việc trong chế độ giao dịch ngầm định, SQL Server tự động khởi động
một giao dịch mới sau giao dịch hiện hành đã được xác nhận (committed) hoặc hủy bỏ (rolled
back). Chế độ giao dịch ngầm định sinh ra một chuỗi các giao dịch.
1. Mở Query Analyzer.
2. Thực hiện lệnh sau:
CREATE TABLE A (COL1 INT, COL2 CHAR(1))
SET IMPLICIT_TRANSACTIONS ON
INSERT INTO A VALUES (1,'A')
INSERT INTO A VALUES (2,'B')
SELECT COUNT(*) FROM A
COMMIT TRANSACTION
INSERT INTO A VALUES (3,'C')
SELECT COUNT(*) FROM A
Lập trình T-SQL , Các giao dịch & Quản lý bảo mật 108
COMMIT TRANSACTION
SET IMPLICIT_TRANSACTIONS OFF
Giao dịch ngầm định trên sẽ tạo lập một bảng và chèn giá trị vào bảng. Giao dịch ngầm định thực
hiện một chuỗi các giao dịch. Vì vậy, tất cả các giao dịch được thực hiện từng bước một trừ khi
giao tác ngầm định được thiết lập là off. Lệnh SET được sử dụng để định cấu hình cấp độ phiên
giao dịch cài đặt trong SQL Server.
Kết quả của truy vấn được hiển thị trong hình 8.
Hình 8.1: Các giao dịch ngầm định
8.1.2 Các giao dịch tường minh
Giao dịch tường minh là một giao dịch chúng ta định nghĩa bằng sự bắt đầu và kết thúc của giao
dịch. Giao dịch tường minh còn được gọi là các giao dịch người sử dụng định nghĩa.
BEGIN TRANSACTION, COMMIT TRANSACTION và ROLLBACK TRANSACTION là các
câu lệnh được sử dụng để định nghĩa các giao tác tường minh.
Sử dụng BEGIN và COMMIT TRANSACTION
Câu lệnh BEGIN TRANSACTION đánh dấu điểm bắt đầu của một giao tác. COMMIT
TRANSACTION đánh dấu kết thúc giao tác
Chúng ta hãy tăng giá trị UnitPrice trong bảng Products bằng 15 với điều kiện CategoryID
bằng 1.
1. Thực hiện các lệnh dưới đây
Lập trình T-SQL , Các giao dịch & Quản lý bảo mật 109
BEGIN TRAN Tran2
SELECT productid,productname
FROM Products
UPDATE Products
SET UnitPrice=UnitPrice+15
WHERE CategoryID = 1
COMMIT TRAN
Kết quả của truy vấn được hiển thị như hình 8.2
Hình 8.2: Các giao dịch tường minh
Sử dụng Rollback Transaction
ROLLBACK TRANSACTION đưa giao dịch về điểm bắt đầu của giao dịch hoặc một điểm định
rõ trước đó.
1. Thực hiện lệnh sau đây.
BEGIN TRAN TRAN3
SELECT PRODUCTID,PRODUCTNAME
FROM PRODUCTS WHERE UNITPRICE=16
UPDATE PRODUCTS
SET UNITPRICE=UNITPRICE+14
WHERE UNITPRICE=14
IF @@ROWCOUNT>0
BEGIN
SELECT UNITPRICE FROM PRODUCTS WHERE UNITPRICE = 30
PRINT 'TRANSACTION IS ROLLED BACK'
ROLLBACK TRAN
Lập trình T-SQL , Các giao dịch & Quản lý bảo mật 110
END
SELECT UNITPRICE FROM PRODUCTS WHERE UNITPRICE=30
Kết quả của truy vấn được hiển thị như hình 8.3.
Hình 8.3: Hủy bỏ giao dịch
8.1.4 Giao dịch Autocommit
Giao dịch Autocommit là giao dịch ngầm định. SQL server thực hiện theo chế độ tự động xác
nhận bất kể chế độ mặc định này là giao dịch ngầm định hoặc tường minh.
SQL Server thực hiện trong chế độ tự động xác nhận (autocommit) tới khi BEGIN
TRANSACTION của giao dịch ngầm định hoặc tường minh được thiết lập là ON. Khi giao dịch
ngầm định hoặc tường minh thiết lập là OFF, SQL quay trở lại cơ chế autocommit.
1. Thực hiện lệnh dưới đây.
CREATE TABLE AI (Colmna INT PRIMARY KEY, Colmnb CHAR (3))
INSERT INTO AI VALUES (1, 'aaa')
INSERT INTO AI VALUSE (2, 'bbb')
Note: Giao dịch trên là giao dịch autocommit từ khi BEGIN TRANSACTION không được
thiết lập là ON. Giao dịch trên được xác nhận nếu không có lỗi nào xảy ra ngược lại thì quay lại
điểm bắt đầu (rolled back.)
Lập trình T-SQL , Các giao dịch & Quản lý bảo mật 111
Kết quả của truy vấn hiển thị như hình 8.4.
Hình 8.4: Giao dịch Autocommit
Chú ý: Giao dịch trên được hủy bỏ khi có lỗi cú pháp. Do đó, các giá trị không được chèn vào
bảng.
8.2 Các biến cục bộ và biến toàn cục
Một biến là một đối tượng mà nó có thể lưu trữ dữ liệu. Chúng ta có thể sử dụng các biến cục bộ
để lưu trữ dữ liệu trong các câu lệnh SQL
Ví dụ, hãy gán một giá trị cho biến được khai báo trước đó.
SET @Contact = 'Sales Manager'
SELECT * FROM Customers
WHERE contacttitle = @contact
Kết quả của khối lệnh SQL được hiển thị như hình 8.5.
Lập trình T-SQL , Các giao dịch & Quản lý bảo mật 112
