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.
31 trang |
Chia sẻ: Thục Anh | Ngày: 12/05/2022 | Lượt xem: 677 | Lượt tải: 0
Bạn đang xem trước 20 trang nội dung tài liệu Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Chương 4: Lập trình với Transact-SQL - Nguyễn Thị Mỹ Dung, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
CH4: LẬP TRÌNH TRANSACT - SQL
1
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
2
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
3
(khung nhìn):
CREATE VIEW
AS
SELECT
FROM
[WHERE
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
4
:
ALTER VIEW
AS
SELECT
FROM
[WHERE
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
GROUP BY SINHVIEN.MASV, HOCLUC
WHERE HOCLUC >= 7
VD2:
DROP VIEW KETQUADETAI
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
5
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
6
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
7
Xóa chỉ mục
DROP INDEX
Ví dụ:
DROP INDEX DE_TAITENDT_ID
DROP INDEX SINHVIEN.SV_ID
Biên soạn: Nguyễn Thị Mỹ Dung
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,
- Biến trong SQL dùng để lưu giá trị tạ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.
CH4: LẬP TRÌNH TRANSACT - SQL
8
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.
SET @local_variable = value
OR: SELECT @local_variable = value
VD1:
DECLARE @x int, @y int
SET @y = 5
SET @x = @y + 3
VD2:
DECLARE @TBKP FLOAT
SELECT @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
9
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
Biên soạn: Nguyễn Thị Mỹ Dung
SELECT @x, @y
Lưu ý:
- Khi gán trị cho biến có dữ liệu từ bảng dữ liệu
phải sử dụng từ khóa SELECT
- Khi in dữ liệu vừa có chuỗi và vừa có biến
bằng lệnh PRINT phải chuyển đổi kiểu cho biến
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
10
VD1: Tính tổng 2 số
DECLARE @tong float, @a float, @b float
SET @a = 10.0
SET @b = 2.0
SET @tong = @a * @b
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
VD3: 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
VD4: 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
11
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 2 ký hiệ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
Danh sách biến và ý nghĩa một số biến hệ thống
Biên soạn: Nguyễn Thị Mỹ Dung
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
CH4: LẬP TRÌNH TRANSACT - SQL
12
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
|
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.
a. IF ELSE
Cú pháp:
IF
|
[ ELSE
[IF
|
[ELSE]] ]
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
13
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
14
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
15
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ệnhCase 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 then
when then
..
[Else ]
End
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
16
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
17
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
18
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ư:
- Đơn giản hoá các thao tác trên CSDL nhờ vào khả
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êu cầu bằng một câu lệnh đơn giả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ủ tục có thể cấp phát quyền cho người
dùng, nhờ đó tăng khả năng bảo mật đối với hệ thống.
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
19
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
[()]
AS
--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
VD1: 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
20
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), @KETQUAFLOAT)
AS
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.
Cách 1: Theo cách thông 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
21
VD3 - Cách 2: Sử dụng Procedure
CREATE PROC THEM(@MA DT CHA R(10), @TENDT CHA R(50),
@CHUNHIEM CHAR(40), @KINHPHI INT)
AS
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
--
GO
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
22
VD: Sửa Procedure từ VD3
ALTER PROC THEM(@MAD T CHAR(10),@TEND T CHAR(50),@CHUNHIEM
CHAR(40),@KINHPHI INT)
AS
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
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ài tậ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
23
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
được thực thi trên bảng là: INSERT, UPDATE,
DELETE.
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
24
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ọi thay đổi đều bị hủ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
25
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
26
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
27
- 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
28
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
29
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)
AS
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'
Biên soạn: Nguyễn Thị Mỹ Dung
4. Sửa Trigger
Khi một trigger đã được tạo ra, ta có thể tiến hành thay
đổi trigger đó bằng câu lệnh ALTER TRIGGER. Câu lệnh
này sử dụng tương tự như câu lệnh CREATE TRIGGER.
ALTER TRIGGER
ON
[INSTEAD OF] | [FOR | AFTER]
AS
BEGIN
--TẬP LỆNH SQL
--.
END
Ví dụ: SV tự thực hiện
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
30
5. Xóa trigger
Cú pháp:
DROP TRIGGER
Ví dụ:
DROP TRIGGER TRIG_SV
Lưu ý:
- Không được tạo và tham chiếu bảng tạm
- Không tạo hay thay đổi, xóa cấu trúc các đối
tượng sẵn có trong CSDL (CREATE, ALTER, DROP)
- Không gán hay cấp quyền cho người dùng
- Khi cài đặt nhiều Trigger sẽ làm chậm hệ
thống.
Biên soạn: Nguyễn Thị Mỹ Dung
Tạo các trigger sau:
1. Khi sửa table khoa, sửa đổi makhoa trên table
sinhvien.
2. Khi sửa table môn, sửa đổi mamh trên table
ketqua.
3. Khi sửa table sinhvien, sửa đổi masv trên
table ketqua.
4. Khi xóa table sinhvien. Xóa những sinh viên
tương ứng trong table ketqua.
5. Khi xoa table khoa: để trống mã khoa cho cho
những sinh viên có mã khoa trùng với mã mã khoa
vừa bị xóa bên table khoa.
Biên soạn: Nguyễn Thị Mỹ Dung
CH4: LẬP TRÌNH TRANSACT - SQL
31
6. Khi thêm vào table monhoc: số tiết phải >=15
và <=90
7. Khi thêm vào table ketqua: Kiểm tra sự tồn tại
của masv, mamh.
8. Khi thêm table sinhvien: Nếu mã khoa không
tồn tại bên table khoa, nếu mã sv để trống hoặc bị
trùng thì không cho thêm vào.
Biên soạn: Nguyễn Thị Mỹ Dung
- Cách sử dụng, tạo View và Index
- Khai báo và lập trình trong T - SQL
+ Declare: khai báo
+ Set, Select: gán giá trị
+ Print, Select: hiển thị giá trị
+ Câu lệnh if [else]
+ Câu lệnh Case end
+ Câu lệnh While
- Xây dựng Procedure
- Xây dựng Trigger
Biên soạn: Nguyễn Thị Mỹ Dung
Các file đính kèm theo tài liệu này:
- bai_giang_he_quan_tri_co_so_du_lieu_sql_server_chuong_4_lap.pdf