1
Bài 8: Thủ tục (Stored Procedure)
•
Khai báo thủ tục
•
Tham số EXECUTE AS
–
AS CALLER
–
AS OWNER
–
AS SELF
•
Thiết lập thủ tục tự động
–
sp_procoption
2
Khai báo thủ tục
Cú pháp:
CREATE PROC[EDURE] schema. procedure_name
[ {@parameter data_type} [= default] [OUTPUT] ]
[, n]
[WITH {RECOMPILE | ENCRYPTION |
EXECUTE AS { CALLER} }]
AS
sql_statement [ n]
return
EXECUTE AS CALLER: chỉ định là user muốn thực hiện thủ tục phải có quyền
thực hiên trên thủ tục này, và có các quyền tương ứng trên các objects trong thủ
tục.


as
declare @r int
set @r= @a % @b
While @r<>0
Begin
set @a=@b
Set @b=@r
set @r= @a % @b
End
Set @c=@b
return @c
6
Thủ tục đệ qui
Create proc gt
@i int, @j int,@m int output
as
declare @dum int, @t int
if @i>1
Begin
set @t=@i*@j
set @i=@i-1
exec @dum=gt @i,@t,@m output
End
else
set @m=@j
declare @gt1 int
exec gt 4,1, @gt1 output
print @gt1
7
Gía cao nhất của các sản phẩm %Bikes% với ngưỡng @maxprice

@currency_cursor CURSOR VARYING OUTPUT
AS
SET @currency_cursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name FROM Sales.Currency;
OPEN @currency_cursor;
GO
10
Sử dụng tham số cursor output (2)
USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
11
Tham số EXECUTE AS
•
Chúng ta phân biệt giữa:
–
EXECUTE AS CALLER (mặc định)
–
EXECUTE AS OWNER
–
EXECUTE AS SELF
•
Xem kịch bản trong lecture4-Procedure.doc (Phân biệt các tham số

With Cust AS ( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as
RowNumber FROM Customers )
Select * from Cust Where RowNumber >= @RowStart and
RowNumber <= @RowEnd
END
exec GetCustomersByPage 10, 1
14
Thiết lập một thủ tục tự động chạy
Có thể thiết lập để một thủ tục có thể tự động chạy khi mà chúng ta
khởi động dịch vụ sqlserver:
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
15
Thiết lập một thủ tục tự động chạy (2)
use master
go
CREATE PROCEDURE StartUpProcAudit
as
IF NOT EXISTS (SELECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'[dbo].[AuditTable]') AND type in (N'U'))
CREATE TABLE [dbo].[AuditTable](
[UserName] [nchar](20) COLLATE Vietnamese_CI_AI NULL,
[DateTimeLogin] [datetime] NULL
) ON [PRIMARY]
INSERT INTO [AuditTable] values(user_name(),getdate())
exec sp_procoption N'StartUpProcAudit', N'startup', N'true'
16
Bài tập 1

Soluong,DonGia)
-
Hãy viết thủ tục hoặc hàm phân tích từng xâu kí tự trong file:
-
Nếu MaSP chưa có trong DMSanPham(MaSP, TenSP) thì ta chèn MaSP và
TenSP vào bảng này.
-
Nếu MaHD chưa có trong bảng NhapHangHoa thì chèn giá trị MaHD,
NguoiNhap, GhiChu vào bảng này
-
Chèn các giá trị MaHD,MaSP,SoLuong,DonGia vào bảng
NhapHangHoaChiTiet