Chuong 6-Thu tuc cho người cơ bản12.ppt

cho người co bản

1. 11
Ch ng 6.
ươ
Ch ng 6.
ươ
Thủ tục lưu trữ sẵn Thủ tục lưu trữ sẵn
(stored procedure)(stored procedure)

2. 22
Kiến thức đạt đượcKiến thức đạt được
•Có thể sử dụng các lệnh transact-SQL để viết
một thủ tục đơn giản.
•Phương pháp hiệu quả cho việc thực hiện
lặp lại các phát biểu.

3. 33
Nội dung học về thủ tụcNội dung học về thủ tục
1. Khái niệm và đặc điểm của thủ tục.
- Khái niệm , tính năng.
- Các dạng thủ tục và tiến trình biên dịch.
2. Các thao tác với thủ tục.
- Lệnh tạo thủ tục
- Thực thi thủ tục.
- Quy tắc tạo thủ tục.
- Thay đổi một thủ tục đã có.
- Xóa thủ tục.
- Tham số cho thủ tục.

4. 44
•Khái niệm: Thủ tục là một tập hợp hoặc batch
các câu lệnh T-SQL statements và các dòng điêu
khiển mà nó được lưu trữ sẵn dưới một tên gọi,
và thực thi như đơn vị riêng lẻ => mục đích để
cải thiện hiệu năng – giảm thiểu tắt nghẽn
mạng – Nhất quán hơn – Cơ chế bảo mật tốt.
•Các loại thủ tục bao gồm : Do người dùng định
nghĩa. SP của hệ thống. SP mẫu (template), từ
xa (remote), mở rộng (expand).
1. Khái niệm thủ tục & phân loại1. Khái niệm thủ tục & phân loại

5. 55
Truy vấn
(procedure)
Phân tích
Chuẩn
hóa
Tối ưu
Biên dịch
Câu truy vấn
(trong
syscomments)
Kế hoạch truy
vấn
(lưu trong cache)
Tiến trình biên dịch một thủ tụcTiến trình biên dịch một thủ tục
Ghi chú: Thủ tục được tạo để
cải thiện hiệu năng thực hiện
vì hầu hết tất cả các công việc
đều được làm trên server nên
sẽ thực hiện nhanh chóng.

6. 66
2. Các thao tác với thủ tục2. Các thao tác với thủ tục
2.1. Tạo thủ tục.2.1. Tạo thủ tục.
CREATE PROC [EDURE] tên_thủ_tục [ ;
number ]
[ { @parameter data_type }
[ VARYING ] [=default] [OUTPUT ]] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION |
RECOMPILE, ENCRYPTION } ]
[ FOR REPLICATION ]
AS
câu lệnh sql [ ...n ] CREATE PROCEDURE NV_Luongcao AS
SELECT * FROM Nhanvien
Where luong > 30000

7. 77
Xem code của thủ tục: sp_helptext ‘tên_thủ_tục’
Thông tin thủ tục: sp_help ‘tên_thủ_tục’
Các đối tượng phụ thuộc thủ tục & ngược lại:
sp_depends ‘tên_thủ_tục’
Xem các thủ tục trong csdl:Sp_stored_procedures
2.2. Thực thi thủ tục: được gọi từ chương trình
client, hay từ một thủ tục khác, hoặc trực tiếp từ
câu lệnh T-SQL trong một batch.
[ EXEC [ UTE ] ] {  [ @giá_trị_trả_về = ] { tên_thủ_tục
[ ;number ] | @tham_số_giá_trị  }
[[@tham_số=]{giá tri |@biến[OUTPUT]|[DEFAULT]]
 [ ,...n ]
[ WITH RECOMPILE ]

8. 88
Ví dụ: EXECUTE NV_LuongCao
GO
DECLARE @tong int
EXECUTE @tong = TinhTong
PRINT @tong
ví dụ : tạo và thực thi thủ tục như sau
Bước 1: tạo thủ tục
CREATE PROC TongHaiSo AS
RETURN 5 + 6
GO
Bước 2: cho thực thi thủ tục
DECLARE @tong int
EXECUTE @tong = TongHaiSo
PRINT @tong
Gọi thực
thi một
thủ tục
Chạy “TongHaiSo”
sau lấy giá trị trả
về ở biến @tong
và xuất kết quả

9. 99
2.3. Quy tắc tạo thủ tục2.3. Quy tắc tạo thủ tục
•Các phát biểu (T-SQL) đúng cú pháp. Kích
thước max=128MB.
•Là thành viên của db_owner, db_ddladmin,
sysadmin hoặc được cấp quyền trên thủ tục.
•Nếu tạo 1 bảng tạm (có dấu # trước tên
bảng) trong thủ tục nó sẽ bị xóa khi thủ tục
chạy xong.
•Trong một thủ tục không được có các phát
biểu sau: CREATE DEFAULT, CREATE RULE,
CREATE PROCEDURE, CREATE TRIGGER,
CREATE VIEW.

10. 1010
•Thủ tục được tạo trên server và chạy từ client.
Nên đặt tên phân biệt để biết thủ tục nào do ta
tạo ra và thủ tục nào là của hệ thống.
•Để dấu code thì dùng WITH ENCRYPTION.
•Cần chú ý khi thiết lập quyền hạn quyền tạo
thủ tục và các đối tượng bên trong thủ tục.
•Thủ tục kết thúc khi tiến trình gặp lệnh GO.
•Trong một thủ tục, tên các đối tượng được sử
dụng phải có chủ sở hữu (owner) cũng là chủ
sở hữu của thủ tục. Các câu lệnh có thể dùng
trong thủ tục là ALTER TABLE, CREATE INDEX,
CREATE TABLE, DBCC , DROP TABLE, DROP
INDEX, TRUNCATE TABLE, UPDATE STATISTICS.

11. 1111
2.4. Thay đổi một thủ tục đã có:
Lệnh ALTER PROCEDURE để thay đổi một thủ
tục đã có, cú pháp giống CREATE PROCEDURE.
vdụ: ALTER PROCEDURE NV_Luongcao AS
BEGIN
IF NOT EXISTS(SELECT * FROM nhanvien)
BEGIN
PRINT ‘Không có nhân viên nào cả '
RETURN
END
PRINT ‘Danh sách nhân viên lương cao'
SELECT * FROM Nhanvien
Where luong > 30000
END

12. 1212
2.5. Xóa thủ tục2.5. Xóa thủ tục:
Ví dụ:
DROP PROCEDURE NV_Luongcao
hoặc
DROP PROC NV_Luongcao
Ghi chú: Khi xóa một thủ tục cần đảm bảo rằng
nó không còn bị tham chiếu bởi các thủ tục
hoặc hàm nào khác vì thế trước khi xóa nên
chạy lệnh “sp_depends” để xem thủ tục ta cần
xóa còn bị tham chiếu không ?
DROP PROCEDURE tên thủ tục

13. 1313
2.6. Tham số cho thủ tục2.6. Tham số cho thủ tục
–Thủ tục cho phép sử dụng tham số vào và ra.
– Một thủ tục có thể có trên 2,100 tham số, các
tham số cách nhau bởi dấu phẩy.
– Lệnh RETURN dùng để trả về một giá trị từ thủ
tục, và nó chỉ trả về kiểu số nguyên mà thôi.
– Một tham số được khai báo có cú pháp sau:
{@tham số kiểu_dliệu} [= default] [OUTPUT]
CREATE PROC proDSKH @Congty char(15) AS
BEGIN
PRINT ‘Danh sách khách hàng'
SELECT MaKH, TenCTy, Diachi, DienThoai
FROM KhachHang WHERE Congty = @Congty
END

14. 1414
Ví dụ: thủ tục với tham số ra (cần có thêm từ
khóa OUTPUT ngay sau khai báo kiểu dl biến).
Bước 1: tạo thủ tục như sau.
CREATE PROCEDURE Kiemtra @x1 int, @x2 int,
@kq int OUTPUT
AS
SET @kq = @x1 + @x2
Bước 2: khai báo biến, gọi thực thi thủ tục và
lấy kết quả trả về.
declare @tong int
execute Kiemtra 5, 6, @tong OUTPUT
select ‘Kết quả = ’ + @tong
print ‘Kết quả = ‘ + cast(@tong as varchar)