Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server: Chương 4

CH4: LẬP TRÌNH TRANSACT - SQL

GVPT: NGUYỄN THỊ MỸ DUNG SỐ TC: 2 SỐ TIẾT: LT: 20; TH: 20

Biên soạn: Nguyễn Thị Mỹ Dung

Chương 1: Tổng quan về SQL Server (LT: 2) Chương 2: Tạo và quản trị CSDL (LT: 6; TH: 6) Chương 3: Transact-SQL và truy vấn dữ liệu

(LT: 6: TH: 8)

Chương 4: Lập trình với Transact-SQL (LT: 4:

TH: 4)

Chương 5: Bảo Mật và Phân Quyền (LT: 2;

TH: 2)

Chương 6: Kết nối CSDL (Tự học)

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

I. Thiết kế View, Index II. Lập trình trong T-SQL III. Stored Procedure IV. Trigger

Biên soạn: Nguyễn Thị Mỹ Dung

1. Thiết kế View - Là một bảng ảo được tạo ra từ tập con của các bảng (Table) thật khác. Đối với người dùng View là bảng thật.

- Cũng tương tự như truy vấn dữ liệu là dùng để xem dữ liệu từ nhiều bảng khác nhau trong CSDL. - Làm giảm sự phức tạp của CSDL  bảo vệ dữ liệu đối với người dùng không được phép truy cập. - Các lệnh sử dụng trên View tương tự như trên

Table trong CSDL.

- Nhược điểm của View là mất thời gian truy cập

dữ liệu từ bảng (table) gốc.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL



(khung nhìn): CREATE VIEW AS SELECT FROM [WHERE <ĐIỀU_KIỆN> GROUP BY ]

Biên soạn: Nguyễn Thị Mỹ Dung

Ví dụ: Tạo View Ketquadetai gồm hotensv, học

lực, điểm trung bình đề tài

USE QLDETAISV

CREATE VIEW KETQUADETAI

AS

SELECT SINHVIEN.MASV, HOCLUC,

ROUND(AVG(KQ),2) AS DIEMTBDT

FROM SINHVIEN LEFT JOIN SV_DT ON

SV_DT.MASV = SINHVIEN.MASV

GROUP BY SINHVIEN.MASV, HOCLUC

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL



:

ALTER VIEW AS SELECT FROM [WHERE <ĐIỀU_KIỆN> GROUP BY ]



:

DROP VIEW

Biên soạn: Nguyễn Thị Mỹ Dung

VD1: Sửa View Ketquadetai gồm hotensv, học lực

từ 7 điểm trở lên và điểm trung bình đề tài.

USE QUANLYDETAISV ALTER VIEW KETQUADETAI AS SELECT SINHVIEN.MASV, HOCLUC,

ROUND(AVG(KETQUA),2) AS DIEMTBDT

FROM SINHVIEN LEFT JOIN SV_DT ON

SV_DT.MASV = SINHVIEN.MASV

HOCLUC >= 7

GROUP BY SINHVIEN.MASV, HOCLUC WHERE HOCLUC VD2:

DROP VIEW KETQUADETAI

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

Lưu ý 1:

- Có thể sử dụng các câu truy vấn trên view tương

tự như trên bảng (Table)

- Có thể truy vấn trên vừa trên bảng và vừa trên

View.

Ví dụ:

SELECT MASV, HOTENSV, HOCLUC, DTBDT FROM KETQUADETAI KQ, SINHVIEN S

WHERE KQ.MASV = S.MASV AND

DTBDT >= 8

- Khi thay đổi (xóa, thêm, sửa) dữ liệu trên View thì

dữ liệu trên bảng gốc cũng thay đổi theo.

Biên soạn: Nguyễn Thị Mỹ Dung

Lưu ý 2: - Khi tạo View có sử dụng hàm kết tập thì không thể

thực hiện được các thao tác: Insert, Update, Delete.

VD: CREATE VIEW KETQUADT AS SELECT S.MASV, HOTENSV, AVG(KQ) AS DTBDT FROM SV_DT SD, SINHVIEN S WHERE SD.MASV = S.MASV GROUP BY S.MASV, HOTENSV

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

2. Tạo Index (chỉ mục)

Chỉ mục được tạo ra nhằm để các dòng trong

bảng được truy xuất nhanh và hiệu quả hơn.

Chỉ mục có thể được tạo trên một hoặc nhiều

cột của bảng, và mỗi chỉ mục được đặt một tên.

Người dùng không thấy được các chỉ mục này.

Lưu ý:

Khi bảng đã được tạo chỉ mục thì việc cập nhật hay thêm dòng mới vào bảng sẽ mất nhiều thời gian hơn là đối với bảng không có chỉ mục.

Chỉ nên tạo chỉ mục cho các cột thường xuyên

dùng trong các tác vụ tìm kiếm.

Biên soạn: Nguyễn Thị Mỹ Dung

 Chỉ mục đơn nhất (Unique Index)

Cú pháp:

CREATE [UNIQUE] INDEX ON UNIQUE: bắt buộc hai dòng bất kỳ của bảng sẽ không được phép mang cùng giá trị ở cột được tạo chỉ mục.

Ví dụ 1:

CREATE UNIQUE INDEX TENDT_ID ON DE_TAI(TENDT)

Ví dụ 2: Tạo chỉ mục nhiều cột CREATE INDEX SV_Index

ON SINHVIEN (Hosv, Tensv)

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

Xóa chỉ mục

DROP INDEX

Ví dụ:

DROP INDEX DE_TAITENDT_ID DROP INDEX SINHVIEN.SV_ID

Biên soạn: Nguyễn Thị Mỹ Dung

1. Khái niệm Giống như NNLT khác, SQL cũng sử dụng biến, các lệnh rẽ nhánh (if.. else, Case … end) và vòng lặp (while) đơn giản. Ngoài ra, SQL cũng hỗ trợ xây dựng thủ tục, hàm. Điểm khác biệt với NNLT khác là SQL cho phép thiết lập trigger (bẫy lỗi sự kiện), cussor,…

thời - BiếnBiến trong SQL dùng để lưulưu giágiá trịtrị tạmtạm thời

trong quá trình xử lý tính toán.

- Các lệnh cấu trúc dùng để hỗ trợ trong lập

trình SQL.

- Chương trình con, trigger giúp SQL thực hiện

các ràng buộc dữ liệu trong CSDL.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

2. Khai báo biến

 Khai báo biến cục bộ DECLARE {

@local_variable [AS] data_type, … }

Trong đó:

@local_variable: tên biến cục bộ, phải bắt

đầu bằng ký hiệu @.

data_type: kiểu dữ liệu hệ thống hoặc kiểu

dữ liệu người dùng.

Biên soạn: Nguyễn Thị Mỹ Dung

 Sử dụng biến

Câu lệnh SET hoặc SELECT (lấy giá trị từ bảng dữ liệu) dùng để gán giá trị cho biến.

@x int, @y int @y = 5 @x = @y + 3

SET @local_variable = value OR: SELECT @local_variable = value VD1: DECLARE SET SET VD2: DECLARE SELECT @TBKP FLOAT @TBKP = ([SELECT] AVG(Kinhphi)

FROM DETAI WHERE MADT = 'DT001')

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

Hiển thị giá trị biến

Sử dụng câu lệnh PRINT hoặc SELECT để

hiển thị giá trị của biến.

VD: PRINT @x PRINT @y

SELECT @x, @y

Biên soạn: Nguyễn Thị Mỹ Dung

LưuLưu ýý:: - Khi gángán trịtrị cho biến cócó dữdữ liệuliệu từtừ bảngbảng dữ liệu SELECT phải sửsử dụngdụng từ khóa SELECT

- Khi

inin dữdữ liệuliệu vừa có chuỗi và vừa có biến chuyển đổiđổi kiểukiểu cho biến

PRINT phải chuyển bằngbằng lệnh PRINT CONVERT. sang chuỗi với hàm CONVERT

Cú pháp: CONVERT (, ) VD:

CONVERT (CHAR(5), MAXKP) CONVERT (VARCHAR(5), SUMKP)

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

@tong = @a * @b

VD1: Tính tổng 2 số DECLARE @tong float, @a float, @b float SET @a = 10.0 SET @b = 2.0 SET PRINT @tong SELECT @a as so1, @b as so2, @tong as tong VD2: Tính điểm trung bình của sinh viên A02 từ bảng KETQUA

DECLARE @DiemTB float SET

@DiemTB=(SELECT AVG(Diem) FROM KetQua WHERE MaSV='A02')

SELECT @DiemTB as DTB

Biên soạn: Nguyễn Thị Mỹ Dung

VDVD33:: Lấy tổng kinh phí tất cả các đề tài từ bảng đề tài.

DECLARE @tongkp float SELECT @tongkp = sum(kinhphi) FROM DETAI

VDVD44:: Tìm max kinh phí và min kinh phí của đề tài DECLARE @maxkp int, @minkp int SELECT @maxkp=MAX(kinhphi),@minkp = MIN (kinhphi)

FROM DETAI PRINT N'Kinh phí cao nhất là: ' +

CONVERT (CHAR,@maxkp)

PRINT N'Kinh phí thấp nhất là: ' +

CONVERT (CHAR,@minkp)

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

 Biến hệ thống

– Biến hệ thống là biến có sẵn và hệ thống quản lý.

– Biến hệ thống trong SQL Server được đặt tên bắt

đầu bởi 22 kýký hiệuhiệu @@.

Ví dụ:

PRINT @@CPU_BUSY PRINT @@VERSION

SELECT @@SERVERNAME

SELECT @@DATEFIRST AS SONGAYTUAN

SELECT @@CONNECTIONS AS SLGNOIKET

Biên soạn: Nguyễn Thị Mỹ Dung

DanhDanh sáchsách biếnbiến vàvà ýý nghĩa

thống nghĩa mộtmột sốsố biếnbiến hệhệ thống

BIẾN

Ý NGHĨA

@@CONNECTIONS

Số các kết nối đến Server

@@CPU_BUSY

Số lượng xử lý công việc của SQL

@@CURSOR_ROWS

Số bản ghi trong cursor mở gần nhất

@@DATEFIRST

Số ngày trong tuần

@@ERROR

Mã lỗi xảy ra gần nhất

@@FETCH_STATUS

= 0: nếu truy xuất thành công, = 1: ngược lại

@@IDENTITY

Giá trị identity gần nhất được sinh ra

@@LANGUA GE

Ngôn ngữ sử dụng

@@MAX_CONNECTIONS

Số lượng nối kết tối đa

@@ROWCOUNT

Số bản ghi bị tác động bởi SQL

@@SERV ICENA ME

Dịch vụ SQL trên máy chủ

@@TRANSCOUNT

Số giao dịch đang hoạt động trên nối kết hiện tại

@@VERSION

Phiên bản của SQL

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

3. Khối lệnh Một tập lệnh SQL được thực thi sẽ được đặt trong khối lệnh BEGIN … END

Cú pháp: BEGIN

| <đoạn lệnh>

END VD: BEGIN DECLARE @DIEMTB_DT NUMERIC,

… PRINT @DIEMTB_DT END

Biên soạn: Nguyễn Thị Mỹ Dung

3. Các lệnh cấu trúc lựa chọn Chúng ta có thể thực thi các tập lệnh SQL khác

nhau dựa vào các điều kiện chọn khác nhau.

aa.. IFIF …… ELSEELSE Cú pháp: IF <điều kiện>

< lệnh sql1> |

[ ELSE

[IF <ĐK_k> < lệnh sql2>|< tập lệnh2> [ELSE]] ]

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD1: TÌM SỐ LỚN NHẤT TRONG 3 SỐ DECLARE @A INT, @B INT, @C INT, @MAX INT SET @A = 4 SET @B = 2 SET @C = 10 SET @MAX = @A IF @A > @MAX

SET @MAX = @A

ELSE BEGIN

IF @B > @MAX

SET @MAX = @B

ELSE

SET @MAX = @C

END

PRINT N'SỐ LỚN NHẤT: '+ CONVERT(CHAR,@MAX)

Biên soạn: Nguyễn Thị Mỹ Dung

VD2: Tìm xem có đề tài nào có kinh phí

trên 20 tr

không? Nếu có in ra thông, không có hiển thị thông báo

IF (SELECT COUNT(*) FROM DETAI

WHERE KINHPHI > 20 ) > 0

BEGIN

PRINT N'DANH SÁCH ĐỀ TÀI CÓ KINH PHÍ

TRÊN 20 TRIỆU: '

SELECT MADT, TENDT, CHUNHIEM, KINHPHI FROM DETAI WHERE KINHPHI > 20

END

ELSE

PRINT N'KHÔNG CÓ ĐỀ TÀI NÀO

TRÊN 20 TRIỆU!!!'

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

b. IF với EXISTS IF có kết hợp từ khóa EXISTS Để kiểm tra sự

tồn tại của các dòng dữ liệu bên trong bảng.

IF EXISTS (Câu_lệnh_SELECT) Câu_lệnh1 | Khối_lệnh1

[ ELSE

Câu _lệnh2 | Khối_lệnh2 ]

Biên soạn: Nguyễn Thị Mỹ Dung

VD1: Tìm những đề tài có kinh phí lớn hơn kinh phí

trung bình tất cả các đề tài.

DECLARE @TBKP FLOAT SET @TBKP = (SELECT AVG(KINHPHI) FROM DETAI) IF EXISTS (SELECT KINHPHI FROM DETAI WHERE

KINHPHI > @TBKP) BEGIN

PRINT N'CÁC ĐỀ TÀI CÓ KINH PHÍ CAO:' +

CONVERT(CHAR, @TBKP)

SELECT MADT, TENDT, CHUNHIEM, KINHPHI

FROM DETAI WHERE KINHPHI > @TBKP

END

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD2: Tìm thông tin những đề tài có nhiều hơn 3 sinh

viên thực hiện, nếu không có in ra thông báo để biết. IF EXISTS (SELECT MASV, COUNT (MADT) FROM SV_DT

GROUP BY MASV HAVING COUNT (MADT) > 3)

BEGIN

PRINT N'CÁC ĐỀ TÀI CÓ SỐ LƯỢNG TRÊN 2 SINH

VIÊN THỰC HIỆN!'

SELECT DISTINCT D.MADT, TENDT, CHUNHIEM,

KINHPHI, COUNT(SD.MASV) AS SL_SV

FROM DETAI D INNER JOIN SV_DT SD ON

D.MADT = SD.MADT

GROUP BY D.MADT, TENDT, CHUNHIEM, KINHPHI HAVING COUNT(SD.MASV) > 3

END

ELSE

PRINT N'KHÔNG CÓ ĐỀ TÀI NÀO TRÊN 3 SINH VIÊN

THỰC HIỆN!'

Biên soạn: Nguyễn Thị Mỹ Dung

4. Câu lệnh Case… end Dùng để lựa chọn nhiều giá trị, nếu sau Case xuất hiện khi biểu thức có kiểu dữ liệu số.

Case []

when <điều_kiện_1> then when <điều_kiện_2> then ….. [Else ]

End

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

Ví dụ1: DECLARE @TEN CHAR(3), @XUAT NVARCHAR(100) SET @TEN = 'PHI' SET @XUAT = (CASE @TEN

WHEN 'MR' THEN N'Xin chào quý ông!!!'

WHEN 'Mrs' THEN N'Xin chào quý bà!!!'

WHEN 'Ms' THEN N'Xin chào quý cô!!!'

ELSE N'XIN CHÀO MỌI NGƯỜI!!!'

END)

PRINT @XUAT

Biên soạn: Nguyễn Thị Mỹ Dung

Ví dụ 2: Xếp loại học lực cho sinh viên

SELECT SINHVIEN.MASV,HOTENSV, HOCLUC,

XEPLOAI= (CASE

WHEN HOCLUC <5 THEN 'YEU'

WHEN HOCLUC>=5 AND HOCLUC<7 THEN 'TB'

WHEN HOCLUC>=7 AND HOCLUC<8 THEN

'KHA'

ELSE 'GIOI'

END)

FROM SINHVIEN

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

5. Lệnh vòng lặp Các câu lệnh được thực thi nhiều lần (lặp) khi

nào điều kiện vẫn còn đúng. WHILE

BEGIN --Các câu lệnh END

- Break: lệnh break nằm bên trong vòng lặp

while dùng để kết vòng lặp.

- Continue: lệnh continue nằm bên trong vòng lặp while để bỏ qua các lệnh phía sau nó và bắt đầu vòng lặp mới.

Biên soạn: Nguyễn Thị Mỹ Dung

VD1: In tổng từ 1 đến 10 DECLARE @TONG INT, @I INT SET @TONG = 0 SET @I = 1 WHILE (@I <= 10) BEGIN

SET @TONG = @TONG + @I SET @I = @I + 1

END PRINT @TONG

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD2: Tính tổng các số chẵn từ 1 đến 20 DECLA RE @TONGCHA N INT, @I INT SET @TONGCHA N = 0 SET @I = 1 WHILE (@I <= 20) BEGIN

IF @I % 2 = 0 BEGIN

SET @TONGCHA N = @TONGCHA N + @I PRINT N'TỔNG ['+ CONV ERT( CHA R(2), @I)+'] = '+CONV ERT( CHAR( 5), @TONGCHA N)

END

ELSE

BEGIN

SET @I = @I + 1 CONTINUE

END

SET @I = @I + 1 END PRINT @TONGCHA N

Biên soạn: Nguyễn Thị Mỹ Dung

1. Khái niệm Thủ tục là một đối tượng trong CSDL bao gồm một tập

câu lệnh SQL được kết hợp với các câu lệnh khác như:

- Cấu trúc điều khiển (IF, CASE, WHILE,...), các biến,... - Các câu lệnh SQL. Sử dụng các thủ tục lưu trữ trong CSDL sẽ giúp tăng

hiệu năng của CSDL như:

thao táctác trêntrên CSDL

CSDL nhờ vào khả

- ĐơnĐơn giảngiản hoá cáccác thao năng module hoá các thao tác.

- Thủ tục lưu trữ cho phép chúng ta thực hiện cùng một yêuyêu cầucầu bằngbằng mộtmột câucâu lệnhlệnh đơnđơn giảngiản thay vì phải sử dụng nhiều dòng lệnh SQL (tiết kiệm thời gian thực thi).

- Ngoài ra, thủthủ tụctục có thể cấp phát quyền cho người

dùng, nhờ đó tăngtăng khảkhả năngnăng bảobảo mậtmật đối với hệ thống.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

2. Tạo procedure Procedure có thể sử dụng để kiểm tra tham số đầu

vào, đầu ra cho các thao tác dữ liệu.

- Cú pháp: CREATE PROC | PROCEDURE

[()]

--Tập lệnh SQL --…

GO - Thực thi lời gọi thủ tục: EXEC | EXECUTE

Biên soạn: Nguyễn Thị Mỹ Dung

VDVD11: Viết thủ tục nhập vào một tên. Xác định: - Nếu nhập ‘Mr’ thì print ‘Xin chào quý ông!’ - Nếu nhập ‘Mrs’ thì print ‘Xin chào quý bà!’ - Nếu nhập ‘Ms’ thì print ‘Xin chào quý cô!’ --Tham khảo CREATE PROC Kt (@tensv nvarchar(3)) AS

IF (@tensv='Mr')

PRINT (N'Xin chào quý ông!!!')

IF (@tensv='Mrs')

PRINT (N'Xin chào quý bà!!!')

IF (@tensv = 'Ms')

PRINT (N'Xin chào quý cô!!!')

GO EXECUTE kt 'Ms KIM'

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD2 : Kiểm tra MASV, MADT khi nhập dữ liệu vào bảng SV_DT CREATE PROC PRO_SV_DT (@MASV NVARCHAR(10), @MADT NVARCHAR(10),

@NOIA_D NVARCHAR(40), @KETQUA FLOAT)

IF EXISTS (SELECT MASV FROM SINHVIEN WHERE MASV = @MASV)

BEGIN

IF EXISTS (SELECT MADT FROM DETAI WHERE MADT = @MADT)

BEGIN

INSERT INTO SV_DT (MASV, MADT, NOIA_D, KETQUA) VALUES (@MASV, @MADT, @NOIA_D, @KETQUA)

PRINT N'ĐÃ THÊM THÀNH CÔNG'

END

END ELSE PRINT 'KHÔNG THE INSERT DO MASV HAY MADT KHÔNG TON TAI'

RETURN 0

GO --Thực hiện lệnh để kiểm tra

EXEC PRO_SV_DT 'SV12','DT04','AN GIANG',8

Biên soạn: Nguyễn Thị Mỹ Dung

VD3: Giả sử ta cần thực hiện một chuỗi các thao tác

như sau trên cơ sở dữ liệu

1. Thêm vào danh sách đề tài (MADT, TênDT, Chủ nhiệm, Kinh phí): DT1001, Xây dựng Website hỗ trợ việc làm, Trần Kiến Quốc, 20

2. Lập danh sách sinh viên thực hiện đề tài DT1001 cho

tất cả sinh viên có học lực trên 8.

thường thông thường

Cách 1: TheoTheo cáchcách thông INSERT INTO DETAI VALUES (‘DT1001', N‘Xây dựng Website hỗ trợ việc làm’,

N‘Trần Kiến Quốc’, 20)

INSERT INTO SV_DT (MASV,MADT) SELECT MASV, ‘DT1001'

FROM SINHVIEN WHERE HOCLUC>8

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD3 - Cách 2: Sử dụng Procedure

CREATE PROC THEM(@MA DT CHA R( 10), @TENDT CHA R(50), INT)

@CHUNHIEM CHAR(40), @KINHPHI

BEGIN

INSERT INTO DETAI (MADT, TENDT, CHUNHIEM, KINHPHI)

VALUES (@MADT, @TENDT, @CHUNHIEM, @KINHPHI)

INSERT INTO SV_DT (MASV, MADT) SELECT MASV, @MADT FROM SINHV IEN WHERE HOCLUC > 8

END

--Thực hiện lệnh để kiểm tra EXEC THEM 'DT1001','Xây dựng Website hỗ trợ v iệc làm',

'Trần Kiến

Quốc', 20

Biên soạn: Nguyễn Thị Mỹ Dung

3. Sửa Procedure 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âu lệnh này sử dụng tương tự như câu lệnh CREATE PROCEDURE.

Cú pháp: ALTER PROC | PROCEDURE

[()]

AS [WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION]

--Tập lệnh SQL --…

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

VD: SửaSửa Procedure PROC ALTER

Procedure từ VD3 THE M( @MAD T

CHAR(10 ),@TEND T

CHAR(50 ),@CHUNHIE M

CHAR(40),@KINHPHI INT)

BEGIN

IF NOT EXISTS (SELECT MADT FROM DETAI WHERE MADT = @MADT)

BEGIN

INSERT INTO DETAI (MADT, TENDT, CHUNHIEM, KINHPHI) VALUES (@MADT, @TENDT, @CHUNHIEM, @KINHPHI)

PRINT N'THÊM THÀNH CÔNG'

END

ELSE

PRINT N'MADT ĐÃ CÓ!!!'

BEGIN

INSERT INTO SV_DT (MASV, MADT)

SELECT MASV, @MADT FROM SINHVIEN

WHERE HOCLUC > 8

PRINT N'ĐÃ THÊM HOÀN TẤT!!!'

END

Biên soạn: Nguyễn Thị Mỹ Dung

4. Xóa Procedure Cú pháp:

DROP PROCEDURE

Ví dụ:

DROP PROCEDURE PRO_SV_DT

BàiBài tậptập:: 1. Viết procedure kiểm tra một số là chẵn hay lẻ 2. Viết proc in ra thứ tương ứng với số nhập vào 3. Viết thủ tục kiểm tra khóa chính khi thêm dữ liệu

vào bảng Sinhvien, Monhoc, Khoa.

4. Viết thủ tục kiểm tra khóa chính Masv, Mamh đã có trong bảng Sinhvien và bảng Monhoc khi thêm vào bảng kết quả.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

1. Tạo Proc xóa một khoa nhập vào nếu không

vi phạm RBTV về khóa.

2. Tạo Pro xóa môn học nhập vào nếu không vi

phạm RBTV về khóa

3. Tạo Pro xóa Sinh viên nhập vào nếu không vi

phạm RBTV về khóa.

4. Tạo Proc nâng điểm của sinhvien có MASV Mã môn học, số điểm nâng được nhập vào (sửa lại bài này: mã môn học bằng tên môn học)

5.

Biên soạn: Nguyễn Thị Mỹ Dung

1. Khái niệm Trigger là một kiểu thủ tục được lưu trữ đặc biệt, chúng được tự động gọi khi có sự sửa đổi dữ liệu mà trigger được thiết kế để bảo vệ.

Trigger giúp đảm bảo tính toàn vẹn dữ liệu trong CSDL bằng cách ngăn không cho những sự thay đổi không nhất quán được thực hiện.

Trigger chỉ được kích hoạt khi các câu lệnh INSERT, UPDATE,

trên bảng là:

được thực thi DELETE.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

2. Giao tác - transaction Dùng để chỉ công việc gồm nhiều bước, các bước

được thi hành lần lượt.

- Mặc định SQL Server thực hiện các chế độ giao tác không tường minh. Mỗi một câu lệnh INSERT, UPDATE, DELETE là một Transaction. Sau khi thực hiện lệnh, các thay đổi sẽ cập nhật vào CSDL.

- Giao tác tường minh + Khai báo trước với từ khóa: BEGIN TRAN + Kết thúc giao tác với từ khóa ROLLBACK TRAN

nếu có lỗi xảy ra hoặc không, mọimọi thay thay đổiđổi đềuđều bịbị hủyhủy.

+ Kết thúc giao tác với từ khóa COMMIT TRAN khi

các lệnh hoàn tất, dữ liệu thay đổi sẽ giữ lại.

Biên soạn: Nguyễn Thị Mỹ Dung

VD: Đếm số lượng đề tài trước và sau khi thêm SELECT COUNT(*) AS [SL_OLD] FROM DETAI BEGIN TRAN

INSERT INTO DETAI VALUES ('DT1003', N'QUẢN LÝ ĐIỂM RÈN

LUYỆN SINH VIÊN', N'MAI THANH TÂM', 15)

SELECT COUNT() AS [SL MOI] FROM DETAI ROLLBACK TRAN | COMMIT TRAN SELECT COUNT() AS [SL HIENTAI] FROM DETAI

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

3. Trigger Kiểm tra RBTV khi thao tác dữ liệu đối với các chức năng: thêm (insert), sửa (update), xóa (delete),…

a/ Khai báo:

CREATE TRIGGER ON [INSTEAD OF] | [FOR | AFTER]

AS BEGIN

--TẬP LỆNH SQL --….

END

Biên soạn: Nguyễn Thị Mỹ Dung

b/ Các thành phần - Tên bảng: Trigger có ảnh hưởng trên bảng này - INSTEAD OF: loại Trigger này sẽ kiểm tra dữ liệu trước, dữ liệu không bị thay đổi, thay thế cập nhật dữ liệu bằng hành động khác.

- FOR | AFTER: loại Trigger thông thường sẽ kiểm tra các Rule và ràng buộc, dữ liệu tạm thời sẽ thay đổi

- INSERT | UPDATE | DELETE: các hành động

này sẽ kích hoạt trigger hoạt động.

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

c/ Các bảng trung gian và bảng tạm - Inserted: Chứa dữ liệu được thêm mới trong INSERT | UPDATE, có cấu trúc bảng giống bảng thực.

- Deleted: Chứa dữ liệu bị xóa trong DELETE | UPDATE, cấu trúc bảng giống bảng thực được cập nhật.

- Chức năng UPDATE trong SQL sẽ xóa dòng dữ liệu cũ, thêm dòng dữ liệu mới với thông tin được cập nhật.

Biên soạn: Nguyễn Thị Mỹ Dung

VD1: Tạo trigger cập nhật, chèn dữ liệu năm sinh cho sinh viên

phải trên 18

CREATE TRIGGER TRIG_SV ON SINHVIEN FOR INSERT, UPDATE AS BEGIN --CAP NHAT DU LIEU IF UPDATE(NGAYSINH) BEGIN

IF EXISTS (SELECT * FROM DELETED A, INSERTED B WHERE (A.NAMSINH- B.NAMSINH)<18

BEGIN

PRINT 'SINH VIEN PHAI TREN 18 TUOI' ROLLBACK TRAN

END END --CHEN DU LIEU IF EXISTS (SELECT * FROM INSERTED

WHERE (YEAR(GETDATE()) - NAMSINH))<18) BEGIN

PRINT 'SINH VIEN PHAI TREN 18 TUOI' ROLLBACK TRAN

END

END--KETTHUCTRIGGER

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

- Nhập dòng lệnh INSERT INTO để kiểm tra INSERT INTO SINHVIEN VALUES ('SV100', N'NGUYỄN THỊ MỸ DUNG',

1999, 7.0, N'ĐỒNG THÁP') - Thông báo lỗi như sau:

Biên soạn: Nguyễn Thị Mỹ Dung

VD2: Cài đặt Trigger khi xóa dữ liệu ở bảng Sinh viên sẽ

xóa luôn dữ liệu ở bảng tham chiếu.

B1: Tạo Trigger

CREATE TRIGGER DEL_SV ON SINHVIEN FOR DELETE AS BEGIN

IF (@@ROWCOUNT = 0) BEGIN

PRINT N'BẢNG SINH VIÊN KHÔNG CÓ DỮ LIỆU!' RETURN -- NẾU KHÔNG CÓ THÌ KHỎI XÓA

END DELETE SV_DT FROM SV_DT S, DELETED T WHERE S.MASV = T.MASV PRINT N'ĐÃ XÓA DỮ LIỆU CÁC BẢNG SINHVIEN, DETAI!'

END

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

Bước 2: TẠO THỦ TỤC KIỂM TRA TRIGGER XÓA

CREATE PROC XOASV @MASV CHAR(10) AS

IF EXISTS (SELECT * FROM SINHVIEN WHERE MASV =

@MASV)

BEGIN

--VÔ HIỆU HÓA TẠM THỜI CÁC RÀNG BUỘC ALTER TABLE SV_DT NOCHECK CONSTRAINT ALL --CHẠY TRIGGER DELETE FROM SINHVIEN WHERE MASV = @MASV --KÍCH HOẠT TRỞ LẠI CÁC RÀNG BUỘC ALTER TABLE SV_DT CHECK CONSTRAINT ALL

END ELSE

PRINT N'KHÔNG CÓ SINH VIÊN CÓ MÃ SỐ: '+ @MASV

GO EXEC XOASV 'SV100'

Biên soạn: Nguyễn Thị Mỹ Dung

VD3: Tạo trigger thỏa mãn ràng buộc khi thay đổi mã số

đề tài sẽ thay đổi các thông tin liên quan

B1: Tạo Trigger cập nhật CREATE TRIGGER UPDATE_DT ON DETAI FOR UPDATE AS BEGIN

IF (@@ROWCOUNT = 0) BEGIN

PRINT N'BẢNG ĐỀ TÀI KHÔNG CÓ DỮ LIỆU!' RETURN -- NẾU KHÔNG CÓ THÌ KHỎI XÓA

END IF UPDATE (MADT) BEGIN

UPDATE T1 SET T1.MADT = T3.MADT FROM SV_DT T1, DELETED T2, INSERTED T3 WHERE T1.MADT = T2.MADT --Tương tự cho các bảng khác (nếu có)

PRINT N'ĐÃ CẬP NHẬT XONG DỮ LIỆU!' END

END

Biên soạn: Nguyễn Thị Mỹ Dung

CH4: LẬP TRÌNH TRANSACT - SQL

B2: Tạo thủ tục kiểm tra Trigger cập nhật CREATE PROC CAPNHATDT @MADT_OLD CHAR(10),

@MADT_NEW CHAR(10)

IF EXISTS (SELECT * FROM DETA I

WHERE MADT = @MADT_OLD)

BEGIN

--VÔ HIỆU HÓA TẠM THỜI CÁC RÀNG BUỘC ALTER TABLE SV_DT NOCHECK CONSTRA INT ALL --CHẠY TRIGGER UPDATE DETA I SET MADT = @MADT_NEW WHERE MADT = @MADT_OLD --KÍCH HOẠT TRỞ LẠI CÁC RÀNG BUỘC ALTER TABLE SV_DT CHECK CONSTRA INT ALL

END ELSE

PRINT N'KHÔNG CÓ ĐỀ TÀI CÓ MÃ SỐ: '+ @MADT_OLD

GO EXEC CAPNHATDT 'DT3003',

'DT2002'