Đề tài Stored Procedure trong SQL Server

Online. SQL Server 2005 : Lập Trình Nâng Cao – Phạm Hữu Khang, Đoàn Thiện Ngân, Nhà Xuất Bản Lao Động Xã Hội, năm 2008. Lập trình Windows Nâng Cao (C#) : Xây dựng Ứng Dụng với ADO.NET, Trường Đại Học Công Nghiệp Tp.Hồ Chí Minh, 2008. MICROSOFT STUDENT PARTNER 2009 * PHẦN 1 : TỔNG QUAN VỀ STORED PROCEDURE MICROSOFT STUDENT PARTNER 2009 * 1.1 Giới thiệu Stored Procedure (SP) : Store Procedure (SP) : “A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users”. Lý do sử dụng : Khi duyệt hay xử lý Dữ Liệu trong Cơ Sở Dữ Liệu SQL Server, ngôn ngữ SQL (Structured Query Language) là ngôn ngữ thực thi hiệu quả nhất. Do đó, khi tính toán với lượng lớn Dữ Liệu trong SQL Server, ta nên sử dụng đối tượng SP để xây dựng đoạn Chương trình và giải quyết vấn đề nếu cần thiết. MICROSOFT STUDENT PARTNER 2009 * 1.1 Giới thiệu Stored Procedure (SP) : SP chứa các lệnh T-SQL để thực thi 1 nhiệm vụ (task). SP được thiết kế, mã hóa, kiểm tra và biên dịch thành mã thực thi (single execution plan) đặt ngay trên server. Các ứng dụng (application) khi cần thực thi nhiệm vụ, chỉ cần gọi SP. Server sẽ chạy execution plan và trả kết quả về lại client. Thực thi nhanh hơn Giảm lưu lượng mạng (network traffic) MICROSOFT STUDENT PARTNER 2009 * 1.1 Giới thiệu Stored Procedure : SP trong SQL Server tương tự như thủ tục trong các Ngôn Ngữ Lập trình khác : Nhận các tham số từ bên ngoài và trả về nhiều giá trị. Bao gồm một số phát biểu thực hiện các tác vụ trong Cơ Sở Dữ Liệu, và có thể gọi các thủ tục khác. Trả về trạng thái Thành Công hay Thất Bại của quá trình gọi Thủ tục hay bó (Batch) trong Cơ sở Dữ Liệu. Lưu ý : SP khác với Functions : chúng không thể trả về giá trị trực tiếp trong Biểu Thức. MICROSOFT STUDENT PARTNER 2009 * 1.2 Lợi ích khi dùng Stored Procedures : Lập trình theo từng Module. Tạo 1 lần và có thể gọi bất kỳ lúc nào từ các Ứng Dụng. Thực thi nhanh hơn (do nó đã được biên dịch trước). Giảm bớt việc nghẽn đường truyền trên mạng, do SP được xử lý trong Cơ Sở Dữ Liệu và trả về kết quả. Sau đó từ Ứng Dụng, bạn chỉ cần đọc kết quả này và trình bày cho Người Sử Dụng. Sử dụng Cơ Chế Bảo Mật, người sử dụng cần thiết được Cấp Quyền Thực Thi SP. MICROSOFT STUDENT PARTNER 2009 * 1.3 Phân loại Stored Procedure : Chủ yếu có 2 loại : Thủ tục Hệ Thống (System Stored Procedure) và Thủ tục do Người Dùng tạo ra (User – Defined Stored Procedure). MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : “A set of SQL Server-supplied stored procedures that can be used for actions such as retrieving information from the system catalog or performing administration tasks”. Ghi chú : 	Các tác vụ Quản Trị (administration tasks) bao gồm : Liệt kê, Thêm, Cập Nhật, Xóa đối tượng Cơ Sở Dữ Liệu. MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : Kí hiệu : sp_... , xp_... Để thực thi 1 SP, ta sử dụng lệnh EXEC. Ví dụ : 	EXEC sp_databases 	//Thực thi thủ tục liệt kê danh sách các Database có trong SQL Server MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : Kết quả : 	 MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : Được chia thành các Nhóm sau : (xem thêm “SQL Server Book Online”) SP dùng để liệt kê Thông tin (liệt kê danh sách database, liệt kê danh sách Linked Servers, …) SP dùng để trình bày Thông Tin (trình bày thông tin Table, liệt kê thông Tin của SP, liệt kê danh sách đối tượng Trigger, …) SP dùng để Thêm, Xóa, Cập Nhật Thông Tin (thay đổi Chủ Nhân của Database, thay đổi Default Database, đổi Mật Khẩu, …) MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : Ngoài ra, SQL Server còn cung cấp một số Thủ tục Hệ Thống mở rộng, với tên tiền tố là xp_... Ví dụ : xp_cmdshell, xp_sendmail. MICROSOFT STUDENT PARTNER 2009 * 1.3.1 System Stored Procedures : Minh họa (xp_cmdshell) : // liệt kê danh sách Thư Mục và Tập Tin MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Là các SP do người sử dụng tự xây dựng. Khi 1 thủ tục được tạo ra, tên thủ tục được viết vào bảng hệ thống sysobjects, nội dung của thủ tục thì được lưu trong bảng hệ thống syscomments. Thủ tục khi chạy lần đầu hay được biên dịch lại sẽ theo 3 bước sau: Resolution Optimization Compilation MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Thực thi lần đầu : Resolution — Công cụ xử lý truy vấn (query processor) đọc và kiểm tra lỗi chính tả của thủ tục. Optimization — Công cụ tối ưu hoá truy vấn phát triển một execution plan cho các lệnh trong thủ tục. Compilation — trong quá trình biên dịch, thủ tục được phân tích và execution plan được tạo ra. Execution plan được nạp vào bộ nhớ (procedure cache) MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Một số quy tắc khi tạo SP : Mỗi thủ tục chỉ nên hoàn thành 1 nhiệm vụ. Lệnh tạo thủ tục (CREATE PROCEDURE) không thể chứa các lệnh CREATE VIEW, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, hay CREATE TRIGGER. Các lệnh CREATE TABLE và CREATE INDEX có thể được tạo và tham chiếu ngay trong cùng 1 thủ tục. MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Một số Quy tắc khi tạo SP : Một thủ tục không thể tạo/xóa 1 đối tượng rồi lại tham chiếu đến đối tượng đó. Các thủ tục có thể tham chiếu đến các bảng tạm thời. Các bảng tạm (Temporary table) có thể được tạo ra bên trong thủ tục và được tự động xoá khi thủ tục kết thúc. Có thể tham chiếu đến các đối tượng từ các CSDL khác và server từ xa. Cho phép các thủ tục đệ quy (recursive) – thủ tục có thể gọi chính nó. MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Một số Quy tắc khi tạo SP : Nhiều nhất là 2100 Parameters trong 1 SP. Các thủ tục có thể được gọi lồng nhau tối đa tới 32 mức. Kích cỡ cực đại của 1 thủ tục là 128 MB, và còn tùy thuộc vào bộ nhớ. MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Tạo 1 SP : Cú pháp : CREATE PROC [ EDURE ] procedure_name [; number]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Giải thích : Procedure_name : tên Procedure cần tạo. Lưu ý : procedure toàn cục – global procedure (##procedure_name), procedure cục bộ - local procedure (#procedure_name). ;number : là 1 tùy chọn (optional) kiểu Số Nguyên (integer), để nhóm các SP có cùng tên lại với nhau (ví dụ : orderproc;1, orderproc;2), và sẽ cùng bị xóa bằng lệnh DROP PROC (ví dụ Drop Proc orderproc). MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Giải thích (tt) : @parameter data_type : là Tham Số của SP, có kiểu Dữ Liệu nào đó (bao gồm cả kiểu text, ntext, image, trừ kiểu cursor chỉ được dùng làm Tham số output), ví dụ : @masv char. OUTPUT : nếu parameter nào được khai báo là Output, thì parameter đó là 1 “return parameter”. Dùng Output Parameter để trả về thông tin khi gọi SP. MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } : Recompile : SP sẽ được biên dịch lại trong lúc Thực Thi (run time). Dùng tùy chọn “recompile” khi ta dùng các giá trị tạm hoặc các giá trị không điển hình (atypical values) mà không muốn ghi đè (override) lên execution plan (bảng thực thi) được trữ sẵn trong bộ nhớ. MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : ENCRYPTION : SQL Server sẽ mã hóa (Encrypt) Danh Mục (trong table syscomments) có chứa nội dung của câu lệnh Create Proc. Dùng để ngăn không cho Thủ tục bị xuất (published) thành 1 phần bản sao của SQL Server. FOR REPLICATION : 1 SP được tạo kèm theo tùy chọn “FOR REPLICATION”, sẽ được dùng như 1 bộ lọc SP và chỉ thực thi trong quá trình Replication. Không sử dụng cùng với tùy chọn “WITH RECOMPILE”. AS sql_statement [ ...n ] : các lệnh T-SQL kèm theo trong SP. (Xem Ví dụ Minh Họa trong “Phần 2”). MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Thay đổi thủ tục : Cú pháp (tương tự CREATE PROC) : ALTER PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         	[ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION         | RECOMPILE , ENCRYPTION     } ][ FOR REPLICATION ] AS     sql_statement [ ...n ] MICROSOFT STUDENT PARTNER 2009 * 1.3.2 User – Defined Stored Procedures : Đổi tên Thủ tục : (dùng sp_rename) Cú pháp : sp_rename ‘old_proc_name’, ‘new_proc_name’ Xóa Thủ Tục : (dùng Drop Proc) Cú pháp : Drop Proc proc_name Ví dụ minh họa : (Xem “Phần 2”). MICROSOFT STUDENT PARTNER 2009 * PHẦN 2 : CÁC VÍ DỤ MINH HỌA MICROSOFT STUDENT PARTNER 2009 * 2.1 Tạo 1 User – Defined Stored Procedure (có Tham số truyền vào) : Các Biến Cục Bộ (Local variables) nhận giá trị từ bên ngoài truyền vào MICROSOFT STUDENT PARTNER 2009 * 2.1 Tạo 1 User – Defined Stored Procedure (có Tham số truyền vào) Kết quả : MICROSOFT STUDENT PARTNER 2009 * 2.2 Tạo 1 User – Defined Stored Procedure (có sử dụng Output Parameter) Biến Toàn Cục (Global Variable) Biến Toàn Cục, và là Output Parameter MICROSOFT STUDENT PARTNER 2009 * 2.2 Tạo 1 User – Defined Stored Procedure (có sử dụng Output Parameter) Kết quả: Kết quả sau khi Thực Thi SP MICROSOFT STUDENT PARTNER 2009 * 2.3 SP với tùy chọn “WITH RECOMPILE” MICROSOFT STUDENT PARTNER 2009 * 2.3 SP với tùy chọn “WITH RECOMPILE” Kết quả : MICROSOFT STUDENT PARTNER 2009 * 2.4 SP với tùy chọn “WITH ENCRYPTION” MICROSOFT STUDENT PARTNER 2009 * 2.4 SP với tùy chọn “WITH ENCRYPTION” Kết quả : SP đã bị Mã Hóa (Encrypt), Không xem được Nội dung Không thấy gì, Phần Text đã bị Mã Hóa ! MICROSOFT STUDENT PARTNER 2009 * 2.5 Tự tạo 1 “System Stored Procedure” MICROSOFT STUDENT PARTNER 2009 * 2.5 Tự tạo 1 “System Stored Procedure” Kết quả : MICROSOFT STUDENT PARTNER 2009 * THANK YOU !