Cộng đồng chia sẻ tri thức Lib24.vn

2. Các store procedure – Các thủ tục

Gửi bởi: Nguyễn Thị Thu Hiếu 17 tháng 2 2020 lúc 17:20:15


Mục lục
* * * * *

1. Khái niệm

Store Procedure là một tập các phát biểu T-SQL mà SQL Server biên dịch thành một kế hoạch thực thi đơn. Lần đầu tiên khi SQL Server thực thi store procedure thì nó biên dịch store procedure thành kế hoạch và lưu trong bộ nhớ đệm. Mỗi khi gọi thực hiện store procedure này thì nó sử dụng lại kế hoạch này mà không phải biên dịch lại lần nữa.

T- SQL store procedure tương tự như các ngôn ngữ lập trình khác, chúng chấp nhận các tham số nhập, trả về giá trị xuất thông qua tham số hoặc trả về thông điệp cho biết thủ tục thành công hay thất bại.

Các ứng dụng có thể giao tiếp với SQL Server thông qua hai cách:

  1. Chương trình ứng dụng gửi các phát biểu T-SQL từ client đến server. Các phát biểu này được gửi qua mạng và được SQL Server biên dịch lại mỗi khi thực thi chúng.
  2. Tạo store procedure, chúng được lưu và biên dịch thành một kế hoạch ở server. Như vậy với cách này, sử dụng store procedure sẽ giảm được lưu thông mạng, hiệu quả nhanh hơn so với cách gửi các phát biểu T-SQL.

2. Tạo store procedure

Cú pháp:

CREATE {PROC|PROCEDURE}[schema_name.] procedure_name [ ; number ]

[{@parameter [type_schema_name.] data_type } [VARYING][= default ][[ OUT|OUTPUT ] [,...n ]

[ WITH <procedure_option> [ ,...n ] AS { [ BEGIN ] statements [ END ] } [;]


<procedure_option> ::= [ ENCRYPTION ]

[ RECOMPILE ]

Trong đó:

  • procedure_name: là tên của store procedure sẽ được tạo.
  • parameter: Là các tham số truyền vào store procedure, ta phải định nghĩa chúng trong phần khai báo của store procedure. Khai báo báo gồm tên của tham số (trước tên tham số sử dụng tiền tố @), kiểu dữ liệu của tham số và một số chỉ định đặc biệt phụ thuộc vào mục đích sử dụng của tham số đó.
  • ; number: Là số nguyên tùy chọn được sử dụng trong nhóm các thủ tục có cùng tên.
  • data type: Kiểu của tham số trong phần khai báo.
  • [VARYING]: Đây là tùy chọn được chỉ định khi cursor trả về như một tham số.
  • [= default] : Gán giá trị mặc định cho tham số. Nếu không gán giá trị mặc định thì tham số nhận giá trị NULL.
  • OUTPUT: Đây là từ khóa chỉ định tham số đó là tham số xuất. Tham số xuất không dùng được với kiểu dữ liệu Text và image.
  • [,...n]: Chỉ định rằng có thể khai báo nhiều tham số.
  • RECOMPILE: Chỉ định Database Engine không xây dựng kế hoạch cho thủ tục này và thủ tục sẽ được biên dịch tại thời điểm thực thi thủ tục.
  • ENCRYPTION: Chỉ định SQL Server sẽ mã hóa bản text lệnh CREATE PROCEDURE. Users không thể truy cập vào các bảng hệ thống hoặc file dữ liệu để truy xuất bản text đã mã hóa.

Thực thi store procedure trong SQL Server: Để thực thi một thủ tục trong SQL Server ta sử dụng cú pháp sau:

{ EXEC | EXECUTE }

{ module_name [ ;number ]}

[ [ @parameter = ] { value

| @variable [ OUTPUT ]

| [ DEFAULT ]

}

]

[ ,...n ]

[ WITH RECOMPILE ]

Trong đó:

  1. module_name: Là tên thủ tục cần thực hiện.
  2. ;number: Chỉ định thủ tục trong nhóm thủ tục cùng tên.
  3. @parameter: Tên tham số trong thủ tục.
  4. @variable: Chỉ định biến chứa các tham số hoặc trả về tham số.
  5. DEFAULT: Chỉ định lấy giá trị mặc định của biến.

Ví dụ 4.30. Xây dựng thủ tục XemDSSV.

Use QLDiemSV Go

IF EXISTS(Select name from sysobjects

where name ='p_DSSV' and type='p') DROP PROCEDURE p_DSSV

GO

CREATE PROCEDURE p_DSSV AS

SELECT MaSV, Hodem + ' '+TensV as Hoten, Ngaysinh, MaLop From HOSOSV

GO

Thực thi thủ tục p_DSSV

Use QLDiemSV Go

EXEC p_DSSV

Truyền tham số nhập vào trong store procedure.

Ví dụ 4.32. Xây dựng thủ tục pp_DSSV để hiển thị danh sách sinh viên theo tham số mã lớp. Mã lớp được truyền vào khi thủ tục được thực hiện.

Use QLDiemSV Go

IF EXISTS(Select name from sysobjects where name ='p_DSSV' and type='p') DROP PROCEDURE p_DSSV

GO

CREATE PROCEDURE p_DSSV

@parMaLop Varchar(10)='TH%' AS

SELECT MaSV, Hodem + ' '+TensV as Hoten, Ngaysinh From HOSOSV Where MaLop like @parMaLop

GO

Gọi thực thi thủ tục trên với truyền giá trị cho tham số nhập như sau:

EXEC p_DSSV ‘TH03A’

EXEC p_DSSV @parMaLop=DEFAULT

* Sử dụng tham số xuất trong store procedure.

Ví dụ 4.33. Xây dựng thủ tục pp_Siso để xuất giá trị sĩ số của một lớp theo tham số mã lớp. Mã lớp được truyền vào khi thủ tục được thực hiện.

Use QLDiemSV Go

IF EXISTS(Select name from sysobjects where name

='pp_Siso' and type='p') DROP PROCEDURE pp_Siso GO

CREATE PROCEDURE pp_Siso

@parMaLop Char(10), @parSiso Int OUTPUT AS

SELECT @parSiso=count(*)

From HOSOSV Where MaLop=@parMaLop

GO

DECLARE @siso int

exec pp_Siso 'TH03A',@parSiso=@siso OUTPUT

Print 'Si so lop TH03A là :'+ convert(varchar(3),@siso) Go

Kết quả thực hiện chương trình:

Si so lop TH03A là :12

Sử dụng biến cục bộ: Các biến cục bộ được sử dụng trong bó lệnh, trong chương trình gọi (Scipt) hoặc trong thủ tục (xem ví dụ 4.5 và 4.6). Biến cục bộ thường được giữ các giá trị sẽ được kiểm tra trong phát biểu điều kiện và giữ giá trị sẽ được trả về bởi lệnh RETURN. Phạm vị của biến cục bộ trong store procedure là từ điểm biến đó được khai báo cho đến khi thoát store procedure. Ngay khi store procedure kết thúc thì biến đó không được tham chiếu nữa. Cú pháp khai báo biến cục bộ:

DECLARE <parameter> [AS] <data type>

Giống như khai báo các biến ở trên, trước tên biến phải có tiền tố @. Giá trị khởi tạo ban đầu của biến là NULL.

Để thiết lập giá trị của biến ta sử dụng cú pháp:

SET <parameter> = <expression> SELECT <parameter> = <expression>

* Câu lệnh PRINT: Dùng để hiển thị chuỗi thông báo tới người sử dụng. Chuỗi thông báo này nó thể dài tới 8000 ký tự. Cú pháp của lệnh PRINT như sau:

PRINT < messages>

Sử dụng SELECT đề trả về giá trị: Ta có thể trả về giá trị bằng việc sử dụng SELECT trong thủ tục hoặc trả về kết quả thiết lập từ truy vấn SELECT.  

Ví dụ 4.34. Xây dựng thủ tục pp_Siso để xuất giá trị sĩ số của một lớp theo tham số mã lớp ra ngoài. Mã lớp được truyền vào khi thủ tục được thực hiện.

Use QLDiemSV Go

IF EXISTS(Select name from sysobjects where name

='pp_Siso' and type='p') DROP PROCEDURE pp_Siso GO

CREATE PROCEDURE pp_Siso

@parMaLop Char(10), @parSiso Int OUTPUT AS

SELECT @parSiso=count(*) From HOSOSV Where MaLop=@parMaLop

GO

DECLARE @siso int

exec pp_Siso 'TH03A',@parSiso=@siso OUTPUT

SELECT 'Si so lop TH03A là :'= @siso Go

* Lệnh RETURN: Ta có thể sử dụng lệnh RETURN để thoát không điều kiện khỏi thủ tục. Khi lệnh RETURN được thực thi trong thủ tục, khi đó các câu lệnh sau RETURN trong thủ tục sẽ bị bỏ qua và thoát khỏi thủ tục để trở về dòng lệnh tiếp theo trong chương trình gọi.

Ngoài ra, ta có thể sử dụng lệnh RETURN để trả về giá trị cho chương trình gọi, giá trị trả về phải là một số nguyên, nó có thể là một hằng số hoặc một biến. Cú pháp như sau:

RETURN [ integer_expression ]

Ví dụ 4.35. Cho CSDL pubs. Xây dựng thủ tục usp_4_31 kiểm tra một chủ đề có tồn tại trong bảng titles hay không? Nếu tồn tại một chủ đề thì hiển thị chủ đề đó. Nếu không tồn tại chủ đề đó thì thủ tục trả về giá trị 1 hoặc có nhiều hơn một chủ đề đó thì trả về giá trị 2.  

Use pubs Go

IF EXISTS(Select name from sysobjects

where name ='usp_4_31' and type='p') DROP PROCEDURE usp_4_31

GO

CREATE PROCEDURE usp_4_31

@vchTitlePattern VARCHAR(80) = '%'

AS

SELECT @vchTitlePattern = '%' + @vchTitlePattern + '%' IF (SELECT COUNT(*) FROM titles

WHERE title LIKE @vchTitlePattern) < 1

BEGIN

RETURN 1

END

IF (SELECT COUNT(*) FROM titles

WHERE title LIKE @vchTitlePattern) > 1

BEGIN

RETURN 2

END

SELECT title, price FROM titles

WHERE title LIKE @vchTitlePattern RETURN 0

GO

DECLARE @intReturnValue INT

EXEC @intReturnValue = usp_4_31 'Tin hoc' IF (@intReturnValue = 1)

BEGIN

PRINT 'There are no corresponding titles.'

END

IF (@intReturnValue = 2) BEGIN

PRINT 'There are multiple titles that match this

criteria. Please narrow your search.'

END GO

3. Thay đổi, xóa, xem nội dung store procedure

a) Thay đổi store procedure

Cú pháp:

ALTER {PROC|PROCEDURE}[schema_name.] procedure_name [ ; number ]

[{@parameter [type_schema_name.] data_type } [VARYING][= default ][[ OUT|OUTPUT ] [,...n ]

[ WITH <procedure_option> [ ,...n ] AS { [ BEGIN ] statements [ END ] } [;]

<procedure_option> ::= [ ENCRYPTION ]

[ RECOMPILE ]

Ví dụ 4.36. Ta sửa lại thủ tục p_DSSV trong ví dụ 4.32 như sau:

Use QLDiemSV Go

ALTER PROCEDURE p_DSSV

@parMaLop Char(10) AS

SELECT MaSV, Hodem, TensV, Ngaysinh

From HOSOSV Where MaLop=@parMaLop

GO

b) Xóa store procedure

Cú pháp:

DROP{ PROC|PROCEDURE}{[schema_name.] procedure }

Ví dụ 4.37. Xóa thủ tục p_DSSV:

Use QLDiemSV Go

DROP PROCEDURE p_DSSV

Go

c) Xem nội dung store procedure

Để xem nội dung của thủ tục ta sử dụng thủ tục hệ thống sp_helptext.

Ví dụ 4.38. Xem nội dung thủ tục pp_DSSV:

Use QLDiemSV Go
Exec sp_helptext pp_DSSV Go

Kết quả việc thi hành các câu lệnh này cho trong hình 4.8.

Hình 4.8. Sử dung thủ tục sp_helptext

Được cập nhật: 10 giờ trước (11:27:26) | Lượt xem: 854

Các bài học liên quan