Stored Procedure

m dụng đường truyền mạng Bảo mật. Xử lý các chức năng và chia sẽ với các ứng dụng khác Cú pháp tạo procedure CREATE PROCEDURE procedure_name     [ { @parameter data_type }     [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ] procedure_name: tên stored procedure @parameter: tham số, có tối đa 2.100 parameters trong một rpocedure data_type : kiểu dữ liệu của tham số, bao gồm tất cả kiểu dữ liệu trong SQL Server. VARYING: chỉ định kết quả của tham số trả về là một result set. Chỉ được áp dụng cho cursor parameters. default: giá trị mặc nhiên, nếu tham số có giá trị mặc nhiên thì khi thực hiện procedure, có thể user không cần truyền tham số vào khi thực thi OUTPUT : chỉ định rằng đây là output parameter RECOMILE: procedure sẽ được dịch lại mỗi khi thực thi ENCRYPTION: mã hoá mã lệnh của lệnh create procedure khi lưu vào table syscomment Cú pháp tạo procedured (2) procedure_name: tên stored procedure @parameter: tham số, có tối đa 2.100 parameters trong một rpocedure data_type : kiểu dữ liệu của tham số, bao gồm tất cả kiểu dữ liệu trong SQL Server. Tuy nhiên, kiểu cursor chỉ được dùng cho output parameter default: giá trị mặc nhiên, nếu tham số có giá trị mặc nhiên thì khi thực hiện procedure, có thể user không cần truyền tham số vào khi thực thi (nó sẽ có giá trị default) OUTPUT : chỉ định rằng đây là output parameter RECOMILE: procedure sẽ được dịch lại mỗi khi thực thi ENCRYPTION: mã hoá mã lệnh của lệnh create procedure khi lưu vào table syscomment VARYING: chỉ định kết quả của tham số trả về là một result set. Chỉ được áp dụng cho cursor parameters. Ví dụ: CSDL Northwind Tạo procedure P1 để liệt kê danh sách tất cả các products Create procedure Tạo procedure P2 để cập nhật gía cho các sản phẩm tăng 10% Create procedure Lưu ý Có thể tham chiếu đến các tables, view, procedure khác cũng như các temporary table Để tạo một procedure, user phải có quyền CREATE PROCEDURE (sysadmin, hoặc database owner) Kích thước của procedure tối đa là 128 MB Có thể lồng 32 cấp procedure Dùng procedure sp_helptext để hiển thị nội dung text của stored procedure mà user đã tạo Không thể kết hợp lệnh create procedure với các lệnh SQL khác để tạo thành một bó lệnh (batch) Chỉ có thể tạo procedure trong database hiện hành. Thực thi procedure Lệnh để thực thi một stored procedure: EXECUTE [ @return_status = ] procedure_name [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [ ,...n ] [ WITH RECOMPILE ] Chỉ định rằng lấy giá trị trả về của tham số Chỉ định rằng lấy giá trị default của tham số Chỉ định rằng procedure phải recompile trước khi thực hiện Ví dụ Thực thi procedure P1 và P2: Execute P1 go Execute P2 go Ví dụ 2: Create procedure Mexico_Customers as select * from customers where country=‘Mexico’ go Execute Mexico_customers Procedure có tham số create proc CustomerListOfCountry 	@country varchar(40) as select customerid, CompanyName from customers where country=@country go execute CustomerListOfCountry ‘Canada’ Hoặc truyền tham số với giá trị khác execute CustomerListOfCountry ‘USA’ Nếu không truyền tham số: Execute CustomerListOfCountry ???????? Procedure có tham số có giá trị default create proc CustomerList @country varchar(40)='canada' 	as 	select customerid, CompanyName from customers where country=@country go Gọi thực thi có : execute CustomerList ‘Mexico’ Gọi thực thi không truyền tham số: Execute CustomerList Procedure dùng output parameter Tạo Procedure để trả về số lượng khách hàng có giá trị country là tham số truyền vào: create proc P2 	@country varchar(40) = '%', @total integer OUTPUT AS 	SELECT @total = count(*) FROM customers WHERE country like @country Go Thực thi procedure P2 declare @sluong integer Execute P2 'canada', @sluong output SELECT 'The total customers of canada is ' +str(@sluong,4) go Tham số có kiểu là cursor Chỉ dùng cho tham số OUTPUT. Nếu kiểu của tham số là cursor thì VARYING và OUTPUT là bắt buộc. Nếu VARYING được chỉ định cho một tham số thì kiểu dữ liệu của tham số phải là cursor và từ khoá OUTPUT phải được chỉ định. Kiểu dữ liệu Cursor (1) Được dùng trong procedure hoặc trigger Chứa result set column, record Xử lý cursor: Khai báo biến kiểu cursor chứa dữ liệu trả về Kết hợp cursor với câu lệnh select bằng lệnh DECLARE CURSOR Dùng lệnh OPEN để mở cursor Dùng lệnh FETCH INTO để đổ một record hiện hành vào các biến tương ứng với từng column. Dùng lệnh CLOSE để đóng cursor Kiểu dữ liệu Cursor (2) sp_cursor_list để lấy ra danh sách các cursor hiện có sp_describe_cursor, sp_describe_cursor_column và sp_describe_cursor_tables để xem đặc tính của cursor Sau khi cursor mở, hàm @@CURSOR_ROWS tra về số lượng record Sau lệnh FETCH, hàm @@FETCH_STATUS để phản ánh trạng thái fetch sau cùng (0,-1)  SCROLL: Cursor có thể di chuyển hai chiều Local: Biến có phạm vi local Cú pháp khai báo cursor DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] Cursor_name: tên biến cursor Global: Biến có phạm vi Global, có thể truy xuất trong bất kỳ procedure hoặc bó lệnh nào FORWARD_ONLY: Cursor chỉ di chuyển một chiều từ dòng đầu đến dòng cuối Static: định nghĩa một cursor là static DYNAMIC: Định nghĩa một cursor phản ánh tất cả các thay đổi của dữ liệu trong result set. FAST_FORWARD: chỉ định FORWARD_ONLY, READ_ONLY cursor. FAST_FORWARD không thể được chỉ định với SCROLL hoặc FOR_UPDATE KEYSET: các thành phần và thứ tự các dòng trong cursor cố định khi cursor được mở. Tập các khoá trong cursor được lưu trong một table trong database tempdb gọi là keyset. READ_ONLY: không cho phép câp nhật thông qua cursor OPTIMISTIC: SQL Server không lock các dòng nếu như chúng được đọc vào cursor. FOR UPDATE [OF column_name [,...n]]: định nghĩa các cột có thể cập nhật trong cursor. SCROLL_LOCKS: khoá các dử liệu mà đã được đọc vào cursor Ví dụ 1 DECLARE customer_cursor CURSOR 	FOR SELECT * FROM customers OPEN customer_cursor -- mở cursor FETCH NEXT FROM customer_cursor Ví dụ 2 (1) DECLARE @customerId varchar(11), @CompanyName varchar(30), @message varchar(80) PRINT "-------- Customer report --------“ DECLARE customer_cursor CURSOR 	FOR SELECT customerid, companyName FROM customers WHERE country = "USA" OPEN customer_cursor FETCH NEXT FROM customer_cursor INTO @customerid, @companyName Ví dụ 2 (2) While @@FETCH_STATUS = 0 begin print ‘Customer ID: ‘ + @customerID print ‘Company Name: ‘ + @companyName 	 Fecth next from customer_cursor into 	@customerid, @companyName end Close customer_cursor Deallocate customer_cursor go Sử dụng OUTPUT cursor parameter USE northwind CREATE PROCEDURE customer_cursor @customer_cursor CURSOR VARYING OUTPUT AS SET @customer_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM CUSTOMERS OPEN @customer_cursor GO Sử dụng tham số cursor trả về DECLARE @MyCursor CURSOR EXEC customer_cursor @customer_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor Bài tập ứng dụng (NorthWind) Tạo procedure và thực thi để in ra company name có số lượng orders nhiều nhất Tạo proc p1 để trả về doanh thu của năm truyền vào, nếu user không truyền ngày vào thì lấy năm hiện hành Khai báo một procedure CustomersOfCountryCursor để lấy ra một cursor chứa các record của table customers có country bằng giá trị truyền vào. Thực thi CustomersOfCountryCursor và in ra các dữ liệu có trong cursor trả về. Đăng ký Extended stored procedure sp_addextendedproc [ @functname = ] 'procedureName' ,     [ @dllname = ] 'dll fileName ' @functname : Là tên của function trong DLL. procedure: procedure là tên procedure. [ @dllname = ] 'dll‘: Là tên của DLL chứa function đăng ký. Giá trị trả về là 0 (success) or 1 (failure) USE master EXEC sp_addextendedproc xp_hello, 'xp_hello.dll' Nesting stored procedure Có thể lồng 32 cấp Mức nesting hiện hành được lưu trong hàm @@nestlevel Nếu procedure thứ nhất gọi procedure thứ hai thì procedure thứ hai có thể truy xuất đến tất cả các đối tượng mà được tạo ra bởi procedure thứ nhất, ngay cả table tạm Có thể gọi đệ qui Bài tập áp dụng (database QLVT) Tạo procedure P1 để lấy ra danh sách các hoá đơn gồm các thông tin: MAHD, NGAY, TENKH, TONGTG Tạo procedure P2 để xoá các chi tiết hoá đơn của hoá đơn có mã là tham số truyền vào Tạo procedure P3 để tính tổng doanh thu của năm với năm là tham số truyền vào và trả về giá trị là tổng doanh thu đã tính được. Tóm tắt nội dung buổi học Stored procedure trong SQL Serever giống procedure trong các ngôn ngữ lập trình Xử lý nhanh hơn batch Procedure có thể có các tham số input và output thực thi một stored procedure dùng lệnh execute Q & A Tạo proc lấy ra danh sách khách hang có dịa chị là tham số truyền vào: CREATE PROC P2 @DC VARCHAR(50) AS select * from khachhang where diachi=@dc Create proc P1 As Select hd.mahd, tenkh, ngay, sum(sl*giaban) as tongtg From hoadon hd, khachhang kh, chitiethoadon cthd Where (hd.mahd=cthd.mahd) and (kh.makh = hd.makh) Group by hd.mahd, tenkh, ngay Create proc p2 @mahd nvarchar(10) As delete * from chitiethoadon where mahd=@mahd Go -- thuc thi Exec p2 ‘hd002’ go Create proc p3 @nam int, @dt int OUTPUT As select @dt=sum(sl*giaban) From chitiethoadon cthd, hoadon hd Where (hd.mahd=cthd.mahd) AND 	 year(ngay)= @nam Go Declare @dt int Exec p3 2000,@dt OUTPUT Print ‘Doanh thu nam 2000 la ‘ + str(@dt,8) Trong QLVT Tạo procedure để lấy ra tên của các khách hàng đã mua hàng trong tháng …. Và năm ….. (tham số input). Danh sách này được trả về trong một kiểu cursor. Thực thi P4 để lấy ra danh sách các khách hàng của tháng 6 năm 2000 và in ra tên của các khách hàng đó. Trong NorthWind Khai báo một procedure CustomersOfCountryCursor để lấy ra một cursor chứa các record của table customers có country bằng giá trị truyền vào. Thực thi CustomersOfCountryCursor và in ra các dữ liệu có trong cursor trả về.