CHƯƠng 1: TỔng quan về sql server và csdl quan hệ i-giới thiệu sql server

CHƯƠNG V: STORED PROCEDURES I-Khái niệm

tải về 457.4 Kb.

trang	9/12
Chuyển đổi dữ liệu	27.07.2016
Kích	457.4 Kb.
	#7139

1 ... 4 5 6 7 8 9 10 11 12

CHƯƠNG V: STORED PROCEDURES

I-Khái niệm:

Stored Procedure là một đối tượng được xây dựng bởi những phát biểu của SQL Server, và được lưu trữ trong CSDL với 1 tên phân biệt. Mỗi Stored Procedure có thể chứa nhiều câu lệnh SQL.

Stored Procedure tương tự như các thủ tục trong các NNLT khác vì chúng có thể:

Chứa các phát biểu và có thể gọi các thủ tục khác (Nested) hoặc đệ qui (Recursive).
Có thể nhận các tham số .
Có thể trả về các giá trị tính toán được trong thủ tục.

Khi muốn thực hiện các phát biểu đã lưu trữ trong thủ tục ta chỉ cần gọi tên thủ tục.

Chú ý: Không sử dụng phát biểu CREATE tạo đối tượng bên trong Stored Procedure.

II-Tạo Stored procedure Bằng Lệnh CREATE:

Cú pháp:

CREATE PROC[EDURE] [; number]

[Danh Sách tham số]

[WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION]

AS

[Danh Sách biến cục bộ]

-1Biến cục bộ :

Khai báo biến cục bộ:

Trong T-SQL, biến chứa giá trị trung gian phải được khai báo trước khi sử dụng:

DECLARE @VarName data_type [,...n]

Tất cả các 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 khai báo trong một strored procedure hoặc một gói lệnh chỉ khả dụng bên trong vùng đã khai báo nó.
Trong T-SQL không có khái niệm biến toàn cục.

Ví dụ: DECLARE @Counter int

DECLARE @FirstName varchar(25)

Hay khai báo trên cùng dòng:

DECLARE @FirstName varchar(25), @Counter int

Sau khi khai báo biến có giá trị NULL

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

Cách 1: SET @VarName = xpression>

Ví dụ: Declare @Ngay DateTime

set @Ngay ='2005/5/15'

print @Ngay

Set @Ngay = @Ngay + 1

print @Ngay

Cách 2: Dùng câu lệnh Select:

SELECT @VarName = [FROM …]

Nếu query trả về nhiều record thì giá trị trên record cuối cùng sẽ được gán cho biến.

DECLARE @intMaNV int

SELECT @intMaNV = 5 + 6

SELECT @intMaNV = MAX(MaNV) FROM SieuThi..NhanVien

Nếu SELECT trả về nhiều dòng, thì biến được gán giá tri từ dòng cuối cùng trả về bởi Select.
T-SQL có tính năng tự động chuyển kiểu. Do đó, có khả năng bị mất dữ liệu. Bạn có thể dùng hàm để chuyển đổi kiểu:

CAST ( expression AS data_type )

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Ví dụ: Tạo thủ tục thực hiện truy vấn MaHD, tiền bán của từng hóa đơn.

-2Có 2 loại tham số : Input và Output

Cú pháp : @TênTS datatype [= default][OUTPUT]

Có thể khai báo 1024 tham số. Các tham số cách nhau bởi dấu phẩy. Không dùng Kiểu hình ảnh.
Nếu cung cấp giá trị mặc định cho tham số, thì có thể thực hiện thủ tục mà không chỉ định giá trị cho tham số.

Ví dụ: Tạo Stored Procedure Chọn 10 mặt hàng có đơn giá cao nhất gồm các cột: Tên và đơn giá.

Use SieuThi

Go

CREATE PROCEDURE MuoiMHCaoNhat

SET ROWCOUNT 10

SELECT MatHang.TenMH , MatHang.DonGia FROM MatHang

ORDER BY MatHang.DonGia DESC

SET ROWCOUNT { n | @number_var }

Chỉ chọn n dòng đầu tiên khi rút trích dữ liệu..

Để bỏ chỉ định này và cho hiện hết các dòng : SET ROWCOUNT 0

Ví dụ: Xây dựng thủ tục trả về n mặt hàng đầu tiên có đơn giá cao nhất:

CREATE PROCEDURE MuoiMHCaoNhat

@n smallint = 10

SET ROWCOUNT @n

SELECT MatHang.TenMH , MatHang.DonGia

FROM MatHang

ORDER BY MatHang.DonGia DESC

Hay

CREATE PROCEDURE MuoiMHCaoNhat

@n smallint = 10

Exec ('SELECT Top ' + @n + ' MatHang.TenMH , MatHang.DonGia

FROM MatHang

ORDER BY MatHang.DonGia DESC')

Chú ý: Sử dụng 2 dấu nháy đơn để bao các biến chuỗi hoặc ngày

Ví dụ: Declare @str Char(10)

Set @str = ‘Chao Ban’

Exec (‘Select ‘’’ + @str + ‘’’’)

-3;number:

Dùng nhóm các thủ tục có cùng tên và phân biệt nhau bởi số thứ tự number. Khi đó, tên của thủ tục có thêm số thứ tự đã khai báo: orderproc;1, orderproc;2.

Để xóa nhóm thủ tục này bạn chỉ cần dùng 1 lệnh xóa với tên của nhóm.

III-THI HÀNH STORED PROCEDURE:

-1Thi hành stored procedure không có tham số:

Sau khi tạo xong, bạn có thể gọi thủ tục trong QA như sau:

MuoiMHCaoNhat

hoặc EXEC MuoiMHCaoNhat

-2Thực hiện Stored Procedures có Tham số

Có 2 các truyền giá trị cho tham số: Truyền theo vị trí và truyền theo tên tham số.

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

Phải liệt kê theo thứ tự từ trái sang phải. Chỉ có thể bỏ qua những tham số đã gán giá trị mặc định cuối cùng. Ví dụ, thủ tục có 5 tham số đã gán mặc định, Bạn có thể bỏ qua tham số 4 và 5 nhưng không thể bỏ qua 4 và gán trị cho 5.

[EXEC[UTE]] TênTT giátrị [OUTPUT] [,…n]

Example

EXEC ThemHD 10, '2005/05/15

Cách 2: Truyển theo tên tham số:

[EXEC[UTE]] procedure_name @TênTS = value [OUTPUT] [,…n]

-3Trả về giá trị với tham số Output

CREATE PROC spTBCong

@Avg smallint Output,

@A smallint=3, @B smallint = 2,

Select @Avg = (@A + @B) / 2

Go

Để nhận giá trị Avg: đầu tiên bạn phải khai báo một biến, sau đó chạy thủ tục.

Ví dụ: Declare @KQ smallint

EXEC spTBCong @KQ OUTPUT, 10

Select ‘The Average Score is : ‘, @KQ

Hay

Declare @KQ smallint

EXEC spTBCong @A = 10, @B = 9, @AVG = @KQ OUTPUT

Select ‘The Average Score is : ‘, @KQ

tải về 457.4 Kb.

Chia sẻ với bạn bè của bạn:

1 ... 4 5 6 7 8 9 10 11 12