Bài giảng Microsoft SQL server - Bài 12: Thủ tục lưu trú, hàm và trigger

Chia sẻ: Việt Cường Nguyễn | Ngày: | Loại File: PDF | Số trang:85

Thêm vào BST

Báo xấu

3
lượt xem 0
download

Bài giảng Microsoft SQL server - Bài 12: Thủ tục lưu trú, hàm và trigger

Mô tả tài liệu

Download Vui lòng tải xuống để xem tài liệu đầy đủ

"Bài giảng Microsoft SQL server - Bài 12: Thủ tục lưu trú, hàm và trigger" được biên soạn giúp người học nắm được về thủ tục lưu trú; hàm do người dùng định nghĩa, bẫy lỗi. Mời các bạn cùng tham khảo bài giảng để nắm chi tiết hơn nội dung nghiên cứu.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Microsoft SQL server - Bài 12: Thủ tục lưu trú, hàm và trigger

1
Mục tiêu: - Kỹ băng: - Viết thủ tục lưu trú để giải quyết một vấn đề được đăt ra về CSDL. Bao gồm cả việc kiểm chứng dữ liệu khi cần thiết. - Viết các hàm kiểu vô hướng dựa trên một biểu thức hoặc công thức. - Viết hàm kiểu bảng để thay thế cho câu lệnh SELECT có điều kiện WHERE. - Viết trigger để ngăn chặn lỗi CSDL gây ra bởi một truy vấn hành động (Insert, Update, Delete). - Viết trigger ngăn chặn các lỗi CSDL gây ra bởi một câu lệnh định nghĩa dữ liệu (create, alter, v.v…) - Kiến thức: - Giải thích tại sao một thủ tục thực hiện nhanh hơn một SQL script tương ứng. - Mô tả các xử lý cơ bản để kiểm chứng dữ liệu trong một thủ tục lưu trú. - Mô tả ý nghĩa của một số thủ tục hệ thống. 2
- Mô tả hai kiểu hàm do người dùng định nghĩa - Mô tả hai loại triggers - Giải thích ảnh hưởng của các mệnh đề WITH ENCRYPTION và WITH CHEMABINDING trong thủ tục, hàm và triger. - Giải thích tại sao chúng ta nên sử dụng câu lệnh ALTER hơn là xóa và tạo lại một thủ tục, hàm hoặc trigger. 2
- Chúng ta có thể dùng T-SQL để viết các chương trình có tính thủ tục như scripts (tập lệnh/tệp lệnh), Store Procedures (Thủ tục lưu trú), User-defined functions (hàm do người dùng định nghĩa) và triggers (bẫy lỗi). - Các tệp lệnh hữu ích cho các NSD truy cập SQL Server thông qua các công cụ của Client như Management Studio. Các công cụ Client thường chỉ được dùng bởi những người lập trình SQL và quản trị CSDL, không được dùng bởi các chương trình ứng dụng hay NSD cuối. - Store Procedures (Thủ tục lưu trú), User-defined functions (hàm do người dùng định nghĩa) và triggers (bẫy lỗi) là các đối tượng CSDL chứa các câu lệnh SQL và có khả năng thực thi trên server. Các đối tượng này điều khiển và thực hiện tốt hơn tệp lệnh. - Bảng trên slide so sánh sự khác nhau của các chương trình có tính thủ tục trong SQL. - Script: nhiều bó lệnh, được lưu trữ trên ổ đĩa, được thực hiện bởi các công cụ phía client, không có tham biến. - Store procedure (thủ tục lưu trú): một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được thực hiện bởi một chương trình hoặc trong một script, có tham biến. - User- defined function (hàm do người dùng định nghĩa): một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được thực hiện 3
bởi một chương trình hoặc trong một script, có tham biến. - Trigger: một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được tự động thực hiện bởi server khi một truy vấn hành động được thực hiện, không có tham biến. 3
- Thủ tục lưu trú được sử dụng bởi: - Các lập trình viên SQL để điều khiển ai sẽ truy cập CSDL và truy cập như thế nào. - Các lập trình viên ứng dụng để làm đơn giản hóa việc sử dụng CSDL của họ. - Hàm do NSD định nghĩa: - Được sử dụng thường xuyên bởi các lập trình viên SQL ở trong các thủ tục hoặc các trigger mà họ viết. - Cũng có thể được sử dụng bởi lập trình viên ứng dụng và NSD CSDL. - Các trigger được sử dụng bởi: - Các lập trình viên SQL để ngăn chặn các lỗi CSDL khi một truy vấn hành động được thực hiện. - Các lập trình viên SQL để cung cấp các view có thể cập nhật dữ liệu. 4
5
Ví dụ cách tạo và thực thi thủ tục spInvoiceReport. - Thủ tục spInvoiceReport có nhiệm vụ là thực hiện một câu lệnh Select (như trên slide). Câu lệnh này lọc ra các hóa đơn còn nợ tiền. Thông tin trong kết quả lấy từ hai bảng Invoices và bảng Vendors. Kết quả được sắp xếp theo chiều tăng dần của VendorName. - Sau khi viết script như trên slide, chạy script để thủ tục spInvoiceReport được tạo ra và lưu trú trong CSDL AP (xem kết quả trong mục Programmability trong cửa sổ Object exprole). Chi tiết câu lệnh tạo thủ tục sẽ giới thiệu trong phần tiếp theo. 6
Để thực hiện (chạy) thủ tục chúng ta dùng câu lệnh EXEC. Như trên slide, sau khi thực hiện thủ tục spInvoiceReport chúng ta thu được kết quả là một bảng dữ liệu các hóa đơn chưa trả hết tiền (kết quả của câu lệnh select trong thủ tục). Trong lần chạy đầu tiên, các câu lệnh SQL trong thủ tục sẽ được biên dịch và thực thi để tạo một kế hoạch thực thi (execution plan). Sau đó, thủ tục sẽ được lưu trữ dưới dạng thực thi trong CSDL và ở những lần chạy thủ tục sau thì các câu lệnh SQL trong nó không cần phải biên dịch lại. Do vậy, quá trình trình thực thi của thủ tục lưu trú nhanh hơn quá trình thực thi của têp lệnh SQL tương đương. Chúng ta thấy rằng, NSD hay một chương trình khi gọi thủ tục spInvoiceReport không cần phải biết không cần biết cấu trúc của CSDL sử dụng trong thủ tục và cũng không nhất thiết phải biết câu lệnh SQL. Đây cũng là một cách để tăng tính bảo mật cho CSDL. 7
Cú pháp của câu lệnh tạo thủ tục CREATE PROCE: - procedure_name: tên của thủ tục được tạo, tên đặt tùy ý theo nguyên tắc đặt tên trong SQL server, tuy nhiên nên thêm hai kí tự „sp‟ vào phí trước tên để phân biệt với hàm. - Parameter_declarations: khai báo tham biến (tùy chọn) - RECOMPILE: thủ tục được biên dịch lại khi được chạy. - ENCRYPTION: thủ tục bị mã hóa không được xem bởi thủ tục sp_helptext - EXECUTE_AS_clause: - Sql_statements: tập hợp các câu lệnh SQL được sử dụng để giải quyết nhiệm vụ của thủ tục. Phía bên dưới slide là ví dụ tạo thủ tục spCopyInvoices thực hiện nhiệm vụ sao chép bảng dữ liệu Invoices vào bảng InvoiceCopy bằng câu lệnh Select…Into…From. 8
9
- Cú pháp khai báo các tham biến trong thủ tục: - @tên_biến kiểu_dữ_liệu [= giá_trị_mặc_định] [OUTPUT], các biến ngăn cách nhau bởi dấu cách. - Có 3 loại tham biến cho thủ tục: - Tham biến (tham trị) bắt buộc: luôn luôn phải có trong lời gọi thủ tục. Ví dụ @DateVar smalldatetime. - Tham biến (tham trị) tùy chọn: biến được gán giá trị mặc định trong câu lệnh khai báo biến, do đó nó có thể không cần xuất hiện trong lời gọi thủ tục. Ví dụ, VendorVar varchar(40) = NULL. - Tham biến biến: biến truyền giá trị ra ngoài thủ tục, trong khai báo có thêm từ khóa OUTPUT. Ví dụ, @InvTotal money OUTPUT Chú ý: Nên khai báo các biến bắt buộc trước các biến không bắt buộc trong định nghĩa thủ tục. 10
Ví dụ tạo thủ tục spInvotal1 có một tham trị bắt buộc (@DateVar) và một tham biến biến (@InvTotal). Thủ tục thực hiện nhiệm vụ: Tính tổng tiền của các hóa đơn có ngày lập hóa đơn (InvoiceDate) >= ngày của tham trị @DateVar rồi gán giá trị tổng này cho tham biến biến @InvTotal. 11
Ví dụ tạo thủ tục spInvTotal2 có một tham trị tùy chọn (@DateVar). Thủ tục thực hiện nhiệm vụ: kiểm tra xem nếu tham trị @DateVar nhận giá trị NULL thì sẽ gán lại giá trị cho tham trị này bằng giá trị của hóa đơn có ngày lập nhỏ nhất. Sau đó mới thực hiện lệnh Select đư ra tổng tiền của tất cả các hóa đơn có ngày lập hóa đơn (InvoiceDate) lớn hơn giá trị biến @DateVar. 12
Ví dụ định nghĩa thủ tục spInvTotal3 có 3 tham biến: 1 tham biến biến và 2 tham biến tùy chọn. Thủ tục thực hiện nhiệm vụ: - Kiểm trs biến @DateVar nhận giá trị Null thì gán lại giá trị mới như trong thủ tục spInvTotal2. - Gán giá trị cho tham biến biến @InvTotal bằng tổng tiền các hóa đơn có ngày lập >= giá trị biến @DateVar và có VendorName LIKE @VendorVar 13
Ví dụ các lời gọi thực hiện thủ tục khác nhau của thủ tục spInvTotal3: EXEC spInvTotal3 @MyInvTotal OUTPUT, '2012-02-01', 'P%';  lời gọi có đủ 3 biến. EXEC spInvTotal3 @DateVar = '2012-02-01', @VendorVar = 'P%', @InvTotal = @MyInvTotal OUTPUT;  lời gọi tường minh gán từng giá trị cho từng biến (không cần quan tâm đến thứ tự các biến trong khai báo thủ tục). EXEC spInvTotal3 @VendorVar = 'M%', @InvTotal = @MyInvTotal OUTPUT;  Lời gọi chỉ truyền giá trị cho biến tham trị tùy chọn và tham biến biến EXEC spInvTotal3 @MyInvTotal OUTPUT;  Lời gọi chỉ truyền giá trị cho tham biến biến 14
15
- Thủ tục cũng có thể trả ra một giá trị kiểu số nguyên. Khi đó trong định nghĩa thủ tục có câu lệnh RETURN. - Ví dụ tạo thủ tục spInvCount: - Thủ tục có hai tham trị tùy chọn. - Nhiệm vụ của thủ tục: đếm số hóa đơn có InvoiceDate >= @DateVar và VendorName LIKE @VendorVar rồi gán tổng số hóa đơn này cho biến @InvCount. Sau đó, thủ tục thủ tục sẽ trả ra giá trị của biến @InvCount bằng câu lệnh Return (câu lệnh được tô vàng). 17
- Thủ tục cũng có thể trả ra một giá trị kiểu số nguyên. Khi đó trong định nghĩa thủ tục có câu lệnh RETURN. - Ví dụ tạo thủ tục spInvCount: - Thủ tục có hai tham trị tùy chọn. - Nhiệm vụ của thủ tục: đếm số hóa đơn có InvoiceDate >= @DateVar và VendorName LIKE @VendorVar rồi gán tổng số hóa đơn này cho biến @InvCount. Sau đó, thủ tục thủ tục sẽ trả ra giá trị của biến @InvCount bằng câu lệnh Return (câu lệnh được tô vàng). 18
Trên slide là ví dụ cách thực hiện thủ tục spInvCount ở slide trước. Giá trị trả ra của thủ tục được gán cho một biến @InvCount khai báo trước đó. 19