SQL Server 2000 - Thủ tục nội tại

c mã hóa nội dung của câu lệnh truy vấn trong các bảng ảo (view) mà trước đây đã trình bày, trong những trường hợp cần phải mã hóa các câu lệnh bên trong nội dung của thủ tục nội tại thì bạn sử dụng thành phần WITH ENCRYPTION kèm với cú pháp của lệnh tạo thủ tục nội tại hoặc lệnh sửa đổi nội dung thủ tục nội tại. Vị trí của mệnh đề này được đặt trước từ khóa AS và phía sau của tên tham số cuối cùng trong thủ tục (nếu có tham số).
Cú pháp :
Ví dụ :
Xây dựng một thủ tục nội tại mà xử lý của nó dùng để chuyển đổi một chuỗi chứa các ký tự bất kỳ thành một chuỗi các ký số theo một quy tắc nào đó do chúng ta quy định trước. Tuy nhiên các xử lý trong thủ tục này cần phải được mã hóa để hạn chế người sử dụng phát hiện ra quy tắc chuyển đổi.
Mục đích của thủ tục này dùng để mã hóa mật khẩu của các người sử dụng chương trình và lưu vào cột mật khẩu trong bảng người dùng (NGUOIDUNG).
Nội dung của thủ tục này như sau :
Gọi thực hiện thủ tục
Kết quả trả về 
Sau khi đã mã hóa nội dung thủ tục bằng chức năng WITH ENCRYPTION, bạn hoàn toàn không có cách nào để xem lại nội dung của các lệnh bên trong thủ tục. Tuy nhiên các gọi thực hiện thủ tục nội tại đã được mã hóa vẫn bình thường như các thủ tục khác chưa mã hóa.
Có thể xem nội dung của các lệnh bên trong một thủ tục nội tại chưa được mã hóa bằng thủ tục nội tại hệ thống sp_helptext. Theo ví dụ trên nếu bạn thực hiện lệnh :
Khi đó hệ thống sẽ thông báo không thể hiển thị nội dung của thủ tục vì thủ tục này đã được mã hóa.
2/- Biên dịch thủ tục :
Cú pháp :
Ví dụ :
Cú pháp :
3/- Thủ tục lồng nhau :
Bên trong một thủ tục nội tại bạn có thể gọi thực hiện các thủ tục nội tại khác. Cấp độ lồng tối đa của các thủ tục là 32 cấp. Thủ tục ngoài cùng nhất gọi là thủ tục cấp 1, thủ tục được gọi thực hiện kế tiếp là cấp 2 … bạn có thể sử dụng biến hệ thống @@NESTLEVEL để biết được cấp độ lồng hiện hành của thủ tục nội tại là bao nhiêu. Tuy nhiên bên trong một thủ tục bạn có thể gọi thực hiện nhiều thủ tục khác nhau và số lần gọi thực hiện các thủ tục khác nhau là không giới hạn.
Ví dụ :
Để xây dựng thủ tục tính số lượng đặt hàng, tổng số lượng nhập hàng của một vật tư liên quan cho một số đơn đặt hàng chỉ định. Bạn có thể gọi thực hiện lại thủ tục spud_TinhSLDat đã tạo ra trong ví dụ trước bên trong nội dung thủ tục mới.
Gọi thực hiện thủ tục
Kết quả trả về 
4/- Sử dụng lệnh RETURN trong thủ tục :
Thông thường, bạn sẽ sử dụng lệnh RETURN dùng để thoát ra khỏi thủ tục trong các trường hợp dữ liệu không hợp lệ. Ngoài ra lệnh RETURN cũng cho phép bạn trả về một số nguyên tại nơi đã gọi thực hiện thủ tục. Khi đó thủ tục sẽ trả về giá trị là một số nguyên, khái niệm này giúp cho tính năng của thủ tục trở thành một hàm (có trả về giá trị).
Mặc định lệnh RETURN không có giá trị chỉ định thì thủ tục sẽ trả về giá trị là không (0). Phần lớn các thủ tục hệ thống hoặc các biến hệ thống sẽ trả về giá trị 0 khi thực hiện thành công, trả về giá trị khác không dùng để chỉ định có lỗi xảy ra khi thực hiện thủ tục.
Cú pháp :
Khi bên trong một thủ tục nội tại có sử dụng lệnh RETURN thì thông thường bạn phải sử dụng một biến cục bộ để đón nhận giá trị trả về của thủ tục đó. Khi đó cú pháp của lệnh gọi thực hiện thủ tục phải được bổ sung như sau :
Trong đó :
• Biến : biến cục bộ được định nghĩa để đón nhận giá trị trả về của thủ tục có sử dụng lệnh RETURN.
Ví dụ :
Để xây dựng một thủ tục nội tại tính ra tổng số lượng đặt hàng của một vật tư đối với một nhà cung cấp chỉ định, bạn cần phải kiểm tra xem giá trị của mã vật tư và mã nhà cung cấp mà người dùng truyền vào thủ tục có đúng hay không ? Quy định rằng thủ tục trả sẽ về 1 khi mã vật tư không tồn tại, trả về 2 khi mã nhà cung cấp không tồn tại.
SQL Server 2000 : Một số vấn đề khác trong thủ tục nội tại – Phần 2
Để tính ra vật tư nào có doanh thu bán ra cao nhất trong một năm tháng bất kỳ, bạn sẽ tạo một bảng tạm để tính ra tổng thành tiền bán của các vật tư, sau đó sắp xếp dữ liệu trong bảng tạm theo thứ tự giảm dần của cột tổng thành tiền.
5/- Sử dụng bảng tạm trong thủ tục :
Đôi khi việc tính toán bên trong thủ tục vô cùng phức tạp mà nếu chỉ căn cứ vào các dữ liệu hiện có bên trong các bảng, bạn không thể tính ra được, vì thế thông thường bạn có thể tạo ra các bảng ảo (view) trước đó hoặc các bảng tạm trung gian ngay bên trong thủ tục để có được các dữ liệu mong muốn cho các tính toán phức tạp. Để tạo ra bảng tạm ngay bên trong thủ tục nội tại, bạn sử dụng lệnh SELECT INTO với cú pháp mô tả như bên dưới :
Cú pháp :
Trong đó :
• Tên bảng tạm : là tên của bảng tạm sẽ được tạp lập có cấu trúc và dữ liệu từ kết quả của truy vấn chọn lựa sau đó.
• Tên bảng dữ liệu : là tên của bảng chứa dữ liệu nguồn.
Bạn có thể chỉ định các ký tự một dấu thăng (#) hoặc ký tự hai dấu thăng (##) phía trước tên bảng tạm được tạo ra trong câu lệnh SELECT INTO dùng để chỉ định việc tạo ra các bảng tạm cục bộ (#) hoặc các bảng tạm toàn cục (##). Thông thường, bạn chỉ cần sử dụng bảng tạm cục bộ bên trong thủ tục và bạn nên xóa đi các bảng tạm cục bộ đã tạo ra bằng lệnh DROP TABLE trước khi kết thúc thủ tục.
Ví dụ :
Để tính ra vật tư nào có doanh thu bán ra cao nhất trong một năm tháng bất kỳ, bạn sẽ tạo một bảng tạm để tính ra tổng thành tiền bán của các vật tư, sau đó sắp xếp dữ liệu trong bảng tạm theo thứ tự giảm dần của cột tổng thành tiền. Vật tư đầu tiên trong bảng tạm chính là vật tư có doanh thu bán ra cao nhất.
Gọi thực hiện thủ tục :
Kết quả trả về :
6/- Tham số kiểu cursor bên trong thủ tục :
Trong các trường hợp cần khai báo tham số kiểu cursor trong các thủ tục nội tại, bạn cần ghi nhớ các điều sau :
• Tham số kiểu cursor thường là loại tham số trả về giá trị danh sách các dòng dữ liệu theo một điều kiện chọn lọc nào đó.
• Các hành động liên quan khi sử dụng biến có kiểu cursor được chia ra làm 2 phần : bên trong thủ tục và bên ngoài thủ tục.
Các hành động bên trong thủ tục sẽ gồm những việc : định nghĩa dữ liệu cho biến kiểu cursor và mở cursor. Hai hành động này chính là bước 1 và bước 2 trong 4 bước cần thực hiện khi sử dụng biến có kiểu dữ liệu cursor.
Các hành động bên ngoài thủ tục sẽ gồm những việc : đọc từng dòng dữ liệu bên trong cursor và sau cùng là đóng cursor lại. Hai hành động này chính là bước 3 và bước 4 trong 4 bước cần thực hiện khi sử dụng biến có kiểu dữ liệu cursor. Tuy nhiên không đảm bảo dữ liệu bên trong cursor lúc được định nghĩa bên trong thủ tục luôn luôn có, vì thế bạn sẽ chủ động quy định giá trị trả về của thủ tục có sử dụng kiểu cursor là 0 hoặc 1 để chỉ định biến cursor có dữ liệu hoặc là không.
Ví dụ :
Xây dựng một thủ tục trả về danh sách các mã vật tư đã bán ra nhiều nhất trong năm tháng nào đó. Bạn tạo một thủ tục có tham số kiểu dữ liệu cursor chứa danh sách các vật tư đã bán ra nhiều nhất.
Sau đó gọi thực hiện thủ tục này để đón nhận danh sách các mã vật tư đã bán ra nhiều nhất trong năm tháng 01 năm 2002.
7/- Thủ tục cập nhật bảng dữ liệu :
Thông thường bạn sử dụng thủ tục để tính toán dữ liệu, ngoài ra bạn có thể sử dụng thủ tục để cập nhật dữ liệu. Các thủ tục dạng này sẽ được gọi thực hiện và truyền vào các giá trị cho tham số từ bên trong một môi trường khác (màn hình của Visual Basic, Visual Fox) hoặc Query Analyzer.
Ví dụ :
Xây dựng thủ tục thêm mới dữ liệu vào bảng VATTU với tên spud_VATTU_Them gồm có 4 tham số vào chính là các giá trị thêm mới cho các cột trong bảng VATTU : mã vật tư, tên vật tư, đơn vị tính và phần trăm. Trong đó cần kiểm tra các ràng buộc dữ liệu phải hợp lệ trước khi thực hiện lệnh INSERT INTO để thêm dữ liệu vào bảng VATTU.
• Mã vật tư phải duy nhất.
• Tỷ lệ phần trăm phải nằm trong miền giá trị từ 0 đến 100.
Bạn thực hiện lệnh CREATE PROCEDURE như sau :
Giả sử dữ liệu trong bảng VATTU hiện tại như sau :
Lần lượt gọi thực hiện thủ tục trong môi trường Query Analyzer để thêm dữ liệu vào bảng VATTU.
Khi đó hệ thống sẽ xuất hiện thông báo lỗi (mã vật tư đã có) mà bạn đã biện luận bên trong thủ tục.
Hoặc thực hiện :
Khi đó hệ thống sẽ xuất hiện bảng thông báo lỗi (tỷ lệ phần trăm không đúng) mà bạn đã biện luận bên trong thủ tục.
Cuối cùng thực hiện :
Khi đó thủ tục sẽ không thông báo lỗi và lưu lại dữ liệu vào bảng VATTU như ý của bạn.
Qua ví dụ trên, bạn có thể hiểu thêm một tính năng của thủ tục là cập nhật dữ liệu, tương tự như thế bạn có thể xây dựng các thủ tục sửa đổi hoặc hủy bỏ dữ liệu trên bảng dữ liệu thông qua các thủ tục nội tại.
8/- Thủ tục hiển thị dữ liệu :
Ngoài ra thủ tục còn có thêm một tính năng khác nữa, đó là hiển thị dữ liệu nguồn cho các báo cáo. Đối với các báo cáo, bạn có thể lấy dữ liệu từ nhiều nguồn khác nhau :
• Đối tượng bảng dữ liệu hoặc bảng ảo (view).
• Câu lệnh SELECT trực tiếp.
• Đối tượng thủ tục nội tại.
Khi lấy dữ liệu nguồn cho báo cáo thì bạn nên chọn đối tượng thủ tục nội tại. Bởi vì bên trong thủ tục được phép chứa đựng các cấu trúc điều khiển, các biến cục bộ để tính toán tạm thời dữ liệu và sắp xếp dữ liệu nguồn cho các loại báo cáo tính toán và hiển thị dữ liệu phức tạp.
Ví dụ :
Xây dựng thủ tục hiển thị dữ liệu cho báo cáo đơn đặt hàng. Các thông tin trên báo cáo gồm các cột trong bảng dữ liệu DONDH, CTDONDH và VATTU, NHACC. Thủ tục có một tham số vào là số đặt hàng dùng để lọc dữ liệu báo cáo theo đúng số đặt hàng gởi vào, tuy nhiên nếu khi gọi thủ tục mà không truyền vào thì xem như hiển thị tất cả các số đặt hàng có trong bảng DONDH.
Bạn thực hiện lệnh CREATE PROCEDURE như sau :
Lần lượt gọi thực hiện thủ tục trong môi trường Query Analyzer để kiểm tra tính đúng đắn cua thủ tục đã tạo.
Hoặc
Khi đó tùy vào số đặt hàng có truyền vào thủ tục hay không mà kết quả của thủ tục sẽ trả về là nhiều hoặc ít dòng dữ liệu có trong bảng DONDH và các bảng liên quan.
Gọi thực hiện thủ tục :
Kết quả trả về :
Nhận xét thấy rằng trong ví dụ trên, bạn định nghĩa ra một biến cục bộ tên @nKetqua có kiểu dữ liệu là số nguyên (INT) dùng để đón nhận giá trị trả về của thủ tục, sau đó căn cứ vào giá trị của biến cục bộ này để biện luận các trường hợp dữ liệu truyền vào không hợp lệ hoặc hợp lệ.