ƯƠ

CH

NG VII

STORED PROCEDURE

ươ

ả

ạ

ạ Gi ng viên: Ph m M nh C ng

1

I­ Khái ni m:ệ

• Là một chương trình con được xây dựng bởi những

phát biểu của T-SQL

• Có tham số Input và Output

• Được lưu trữ trong CSDL với 1 tên phân biệt.

• Cho phép gọi thực hiện từ các ngôn ngữ lập trình

khác.

• Stored Procedure thường dùng thực hiện truy vấn hay

cập nhật dữ liệu theo yêu cầu của người dùng.

2

ạ

II­T o Stored Procedure

1. Bằng Lệnh CREATE PROCEDURE

2. Bằng công cụ quản lý Management

Studio

3

ệ

ằ

1­ B ng L nh CREATE PROCEDURE:

Create Proc

[]

[With Recompile | Encryption | Recompile , Encryption]

As

[Begin]

[End]

4

ủ ụ ệ

ả

ồ

ẩ t kê s n ph m t n kho

ụ ạ Ví d : T o th  t c li ộ trong m t tháng

Create Proc spTonKho(@Thang int = 8, @Nam int = 2005)

As

Begin

Select * From TonKho

Where Thang = @Thang And Nam = @Nam

End

5

ụ ạ

ủ ụ

ủ

ộ

ả ề Doanh thu c a m t

Ví d : T o th  t c tr  v   năm

Create Proc spDTNam (@Nam Smallint, @DoanhThu Float Output)

AS

Begin

Select @DoanhThu=Sum(SL*DGBan)

From HoaDon hd Inner Join CTHD ct On hd.SoHD=ct.SoHD

Where Year(NgayHD) = @Nam

End

6

ủ ụ

ứ

ủ

ố

1.1 Khai báo tham s  hình th c c a th  t c:

Cú pháp: @par_name datatype [= default][OUTPUT] [,…n]

Có 2 loại tham số hình thức: Input và Output.

• Tham số Output dùng liên kết với một biến tại nơi gọi, nhận

giá trị của biến và trả giá trị về cho biến.

Ví dụ:

Create Proc spTonKho(@Thang int = 8, @Nam int = 2005)

Create Proc spDTNam(@Nam int, @DoanhThu Float Output)

• Tham số hình thức nhận giá trị mặc định khi không truyền

giá trị cho nó.

7

ự

ệ

ố

1.2 Th c hi n Stored Procedures có Tham s

• Cách 1 Truyền theo vị trí :

EXEC sp_name [OUTPUT] [,…n]

Ví dụ gọi thủ tục:

Create Proc spTonKho(@Thang int = 8, @Nam int = 2005)

Exec spTonKho 9, 2005

Sử dụng giá trị default : Exec spTonKho 9

• Cách 2 Truyền theo tên:

EXEC sp_name @par_name = [OUTPUT][,…n]

Ví dụ gọi thủ tục: Create Proc spTonKho(@Thang int = 8, @Nam int = 2005)

Exec spTonKho @Nam = 2005, @Thang = 8

Sử dụng giá trị default: Exec spTonKho @Nam = 2006 8

ị ừ

ậ

1.3 Nh n giá tr  t

ố  tham s  Output

• Phải dùng Biến với từ khóa Output trong lời gọi để nhận giá

trị từ tham số hình thức loại Output

Create Proc spDTNam(@Nam int, @DoanhThu Float Output)

Ví dụ: Thực hiện thủ tục spDTNam

Declare @DT Float

Exec spDTNam 2005, @DT Output

Print @DT

Hay

Declare @DT Float

Exec spDTNam @DoanhThu = @DT Output, @Nam = 2005

9 Print @DT

ề

ể

ể III­ Các Phát Bi u Đi u Khi n:

1. Biến cục bộ

2. Phát biểu If .. Else

3. Phát biểu WHILE

4. Phát biểu: RETURN [Biểu thức số nguyên]

5. Phát biểu: GOTO

10

ụ

ế

ộ

1­ Bi n c c b  :

a) Khai báo biến cục bộ: DECLARE @VarName data_type [,...n]

Ví dụ: Declare @n TinyInt, @m TinyInt, @y SmallInt

• Biến cục bộ phải bắt đầu bằng ký hiệu @ trước tên của nó.

• Phạm vi biến: Biến chỉ khả dụng bên trong thủ tục, hàm hoặc một gói

lệnh đã khai báo biến.

• Giá trị ban đầu của biến là NULL

b) Gán giá trị cho biến cục bộ:

• Cách 1: SET @VarName =

Ví dụ: Declare @Ngay DateTime

set @Ngay ='2005/5/15'

print @Ngay

• Cách 2: SELECT @VarName = [FROM …]

– Nếu SELECT trả về nhiều dòng, thì biến được gán giá tri từ dòng

11 cuối cùng trả về bởi Select.

ủ ụ ệ

ặ

t kê danh sách m t hàng có ấ

ấ

ơ

Ví d : ụ T o th  t c li ạ ấ đ n giá cao nh t và th p nh t.

CREATE PROCEDURE spMatHangCaoNhatThapNhat

AS

Begin

Declare @Dgmax float, @Dgmin float

SELECT @Dgmax = Max(DonGia), @Dgmin = Min(DonGia)

FROM MatHang

SELECT * FROM MatHang

WHERE DonGia = @Dgmax OR DonGia = @Dgmin

ORDER BY DonGia DESC

End 12

ể 2­ Phát bi u If .. Else:

IF  Create Proc spTonKho(@Thang int,

@Nam int) BEGIN

As

ệ If @Thang<1 Or @Thang>12

END Begin

Print 'Thang sai' ELSE

End

BEGIN Else

Begin ệ

Select * From TonKho END

Where Thang = @Thang

And Nam = @Nam

End 13

ể 3­ Phát bi u WHILE :

ủ ụ ổ WHILE  Ví d : ụ Th  t c tính t ng S = 1 + 3 + … + (2N­1)

Create Proc spTongLe ( @N int ) BEGIN

AS

ệ DECLARE @I int, @S int

SET @I = 1 [CONTINUE]

SET @S = 0

[BREAK] WHILE @I <= 2*@N ­1

END BEGIN

Set @S = @S + @I

Set @I = @I + 2

END

Return @S

14

ứ

ể

4­ Phát bi u ể RETURN [Bi u th c s   ố nguyên]:

Dùng kết thúc thủ tục và trả về nơi gọi một số nguyên

• Mặc định là 0 nếu không chỉ định

• Gọi hàm và nhận giá trị Return của thủ tục:

EXEC @var_name = sp_name [@par_name =] [,…n]

Ví dụ: Gọi thực hiện thủ tục spTongLe( @N int )

declare @s int

Exec @s = spTongLe 9

print 'Tong la = ' + Cast(@s As Varchar)

15

ủ ụ

ụ

ơ

ớ Ví d : Th  t c Thêm hóa đ n m i

Create Proc spThemHoaDon(@SoHD int, @MaNV int, @NgayHD DateTime) As If Exists(Select * From HoaDon Where SoHD = @SoHD)

Begin

Print 'Trung so hoa don' Return 1

End

If Not Exists(Select * From NhanVien Where MaNV = @MaNV)

Begin

Print 'Sai Ma nhan vien ' Return 2

End

If @NgayHD > GetDate()

Begin

Print 'Ngay lap hoa don lon hon ngay hien hanh ' Return 3

End Insert HoaDon (SoHD, MaNV, NgayHD) Values(@SoHD, @MaNV, @NgayHD)

if @@Error <> 0 Begin

Print 'Loi them hoa don moi' Return 4

End Return 0

16

ể

5­ Phát bi u: GOTO 

Create Proc spTongLe ( @N int ) --Thuc hien thu tuc

Declare @KQ int AS

DECLARE @I int, @S int Exec @KQ = spTongLe 9

Print @kq SET @I = 1

SET @S = 0

Lap:

IF (@I > 2*@N -1) GoTo KetQua

Set @S = @S + @I

Set @I = @I + 2

GoTo Lap

KetQua:

Return @S

17

ủ ụ

ử

IV­ S a Xóa Th  T c:

1. Đổi Tên :

sp_Rename ,

2. Xóa Thủ tục được lưu:

DROP PROC

3. Thay đổi nội dung thủ tục lưu trữ:

ALTER PROC spName

[Danh sách tham số]

[With Recompile | Encryption | Recompile, Encryption]

AS

< Các phát biểu t_sql > 18

Ả Ơ C M  N

19