Bài giảng "Chương III: Procedure, Function, View, Trigger & Index" cung cấp cho các bạn những kiến thức về: thủ tục (stored procedure), hàm (function), khung nhìn (view), ràng buộc toàn vẹn (trigger), chỉ mục (index) trong Tin học.
AMBIENT/
Chủ đề:
Nội dung Text: Bài giảng Chương III: Procedure, Function, View, Trigger & Index
- Chương III. PROCEDURE, FUNCTION,
VIEW,
TRIGGER & INDEX
1
- Nội dung
1. Thủ tục (stored procedure)
2. Hàm (Function)
3. Khung nhìn (view)
4. Ràng buoc toàn vẹn (trigger)
5. Chỉ mục (index)
2
- 1. Stored procedure
Stored procedure trong CSDL tương tự như các
thủ tục trong ngôn ngữ lập trình. Mọi stored
procedure có thể:
Nhận tham số đầu vào, thực thi các câu lệnh bên trong
và trả vê các giá trị.
Bên trong mỗi thủ tục có thể có nhiều câu lệnh và có thể
gọi các thủ tục khác
Trả về các thông báo cho biết thủ tục thực hiện thành
công hay thất bại
Các câu trúc điều khiển (IF, WHILE, FOR) có thể được
sử dụng trong thủ tục
3
- 1. Stored procedure
Các lợi ích của stored procedure:
Cho phép module hóa các công việc và thực thi các
câu lệnh dễ dàng hơn
Tối ưu hóa việc phân tích, biên dịch và thực thi câu
lệnh
Thực hiện một yêu cầu bằng một câu lệnh đơn giản
hơn thay vì phải sử dụng nhiều dòng lệnh SQL khi
thực thi làm giảm thiểu sự lưu thông trên mạng
Tăng khả năng bảo mật khi cấp phát quyền thông
qua thủ tục
4
- 1. Stored procedure
Tạo thủ tục tên danh sách có tham số truyền vào là
tên nhân viên. Liệt kê mã nhân viên, họ lót, tên
nhân viên, ngày vào làm, lương:
CREATE PROC sp_danhsach @ten nvarchar(20)
AS
SELECT MaNV, HoLot, TenNV, NgayVaoLam,
Luong
FROM NhanVien
WHERE TenNV= @ten
5
- 1. Stored procedure
Thực thi thủ tục và truyền tham số cho thủ tục như
sau:
@tham_số=giá_trị
Thực thi: sp_danhsach 'Linh‘ hoặc
exec sp_danhsach ‘Linh’
6
- 1. Stored procedure
Người dùng có thể gán giá trị mặc định cho tham số
trong câu lệnh create procedure
CREATE PROC sp_danhsach1 @ten
nvarchar(20)='Bình'
AS
SELECT *
FROM NhanVien
WHERE TenNV = @ten
Thực thi: sp_danhsach1 ‘Duy’
7
- 1. Stored procedure
Tạo thủ tục tăng lương nhân viên với tham số đầu vào là
mức lương tăng và mã nhân viên
CREATE PROC asc_salary(@ascsalary int, @idemp int)
AS
BEGIN
UPDATE NhanVien
SET Luong=Luong+ @ascsalary
WHERE MaNV=@idemp
END
Thực thi: asc_salary 100, 1
(chú ý thứ tự giá trị truyền vào theo đúng tham số)
Hoặc thực thi: asc_salary (@ascsalary=100, @idemp=1)
(Nếu thực thi câu lệnh tường minh có thể hoán đổi vị trí
8
các tham sô)
- 1. Stored procedure
Tạo thủ tục tăng lương nhân viên với tham số đầu vào là mã nhân viên.
Nếu lương nhân viên lớn hơn 1000$ thì tăng 150$, ngược lại tăng 100$
CREATE PROC asc_salary(@idemp int) AS
DECLARE @salary INT
BEGIN
SELECT @salary=Luong
FROM NhanVien
WHERE MaNV=@idemp
If @salary>1000
BEGIN
UPDATE NhanVien SET Luong=Luong+150
WHERE MaNV=@idemp
END
Else
BEGIN
UPDATE NhanVien SET Luong=Luong+100
WHERE MaNV=@idemp
9 END
- 1. Stored procedure
Tạo thủ tục tăng lương nhân viên lên 5 lần, mỗi lần tăng
50$ dùng câu lệnh While
CREATE PROCEDURE asc_salary(@idemp int)
AS
DECLARE @count int
BEGIN
SET @count=1
WHILE @count
- 1. Stored procedure
Thông tin trả về các thủ tục:
Các thủ tục có thể trả về giá trị số nguyên để thông
báo thủ tục thực hiện thành công hay thất bại. SQL
Server định nghĩa sẵn tập các giá trị trả về nằm
trong khoảng [-99;0]. Giá trị 0 cho biết thủ tục thực
hiện thành công, các giá trị còn lại cho biết nguyên
nhân lỗi xảy ra.
11
- 1. Stored procedure
Ngoài ra, người dùng có thể định nghĩa giá trị trả
về bằng cách bổ sung một tham số vào câu lệnh
RETURN. Tất cả các số nguyên đều được chấp
nhận ngoại trừ các số do hệ thống định nghĩa và sử
dụng
CREATE PROC sp_vidu @ten nvarchar(20)
AS
IF EXISTS (SELECT *
FROM NhanVien
WHERE TenNV = @ten)
RETURN 1
ELSE
RETURN 2
12
- 1. Stored procedure
Thực thi thủ tục có câu lệnh RETURN:
Declare @a int
Exec @a = sp_vidu N‘Hà’
Select @a
13
- 1. Stored procedure
Tạo thủ tục với tham số mặc định
CREATE PROC sp_testdefault @MaNV int =3
AS
SELECT *
FROM NhanVien
WHERE MaNV=@MaNV
Thực thi thủ tục:
Exec sp_testdefault
Hoặc
Exec sp_testdefault 4
14
- 1. Stored procedure
Khi cả 2 câu lệnh Create Procedure và Execute
chứa mục chọn OUTPUT cho tên một tham số, thủ
tục có thể sử dụng một biến để trả về trị của tham
số đó đến người gọi
CREATE PROC Chia @sobichia real,@sochia real,
@ketqua real OUTPUT
AS
IF (@sochia =0)
Print ‘Loi chia cho 0’
ELSE
SELECT @ketqua = @sobichia / @sochia
15
- 1. Stored procedure
Thực thi thủ tục: Thực thi thủ tục
DECLARE @ketqua real DECLARE @ketqua real
EXEC Chia 100, 2, @ketqua OUTPUT EXEC Chia 100, 0, @ketqua OUTPUT
SELECT @ketqua SELECT @ketqua
16
- 1. Stored procedure
Tạo thủ tục với tham số đầu ra là lương nhân viên với tham
số đầu vào là mã nhân viên
CREATE PROCEDURE salary (@idemp int, @salary int
OUTPUT)
AS
SELECT @salary=Luong
FROM NhanVien
WHERE MaNV=@idemp
Thực thi:
DECLARE @salary int
EXEC salary 5, @salary=@salary OUTPUT
PRINT ‘Lương nhân viên:’+CAST(@salary AS
VARCHAR(10))+’$’
17
- 1. Stored procedure
Cho biết tổng lương của nhân viên với tham số đầu vào là mã phòng
ban
CREATE PROCEDURE sum_salary (@deptid int, @sumsalary int OUTPUT)
AS
BEGIN
If NOT EXISTS (SELECT *
FROM NhanVien
WHERE MaPB=@deptid)
RETURN 1
SELECT @sumsalary=SUM(Luong)
FROM NhanVien
WHERE MaPB=@deptid
RETURN
If @sumsalary IS NULL
SET @sumsalary=0
18
END
- 1. Stored procedure
Thự c thi thủ tục:
DECLARE @sumsalary, @result
EXEC @result=sum_salary 44,@sumsalary OUTPUT
If @result=1
PRINT ‘Không tồn tại phòng ban’
Else
PRINT ‘Tổng lương cả phòng là:’ +
CAST(@sumsalary AS VARCHAR(15))+ ‘$’
19
- 1. Stored procedure
Tạo thủ tục gọi trực tiêp:
CREATE PROC count_rows (@name Nvarchar(50))
WITH EXECUTE AS CALLER
AS
EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name)
Tạo thủ tục gọi bởi người tạo ra thủ tục:
CREATE PROC count_rows_as_me (@name
Nvarchar(50))
WITH EXECUTE AS SELF
AS
EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name)
20
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
