Ngôn Ngữ Dll Của Thủ Tục Lưu Trữ

Thủ tục lưu trữ do người dùng định nghĩa là loại thường dùng nhất. Chúng được chứa trong cơ sở dữ liệu người dùng và thường được viết để thực hiện một công việc nào đó. Thông thường khi nói đến thủ tục lưu trữ là nói đến loại này. Thủ tục lưu trữ thường được viết bởi người quản trị cơ sở dữ liệu hoặc lập trình viên.

Ví dụ, ta tạo ra một thủ tục từ cơ sở dữ liệu QLTS để tìm kiếm thông tin các thí sinh thì thủ tục này được gọi là thủ tục lưu trữ được định nghĩa bởi người dùng.

4) Thủ tục lưu trữ tạm thời (Temporary Stored Procedure)

Thủ tục lưu trữ tạm thời là những thủ tục lưu trữ tương tự như thủ tục lưu trữ do người dùng định nghĩa. Các thủ tục này được tạo ra trên TempDB của SQL Server nên chúng sẽ bị xóa khi kết nối tạo ra chúng bị cắt đứt hay khi SQL Server bị ngắt.

Thủ tục lưu trữ tạm thời được chia làm 3 loại: local (bắt đầu bằng #), global (bắt đầu bằng ##) và stored procedure được tạo ra trực tiếp trên TempDB. Loại local chỉ được sử dụng bởi kết nối đã tạo ra chúng và bị xóa khi kết nối bị ngắt. Loại global có thể được sử dụng bởi bất kỳ kết nối nào. Loại stored procedure được tạo trực tiếp trên TempDB. Chúng khác với 2 loại trên ở chỗ, chúng tồn tại kể cả sau khi kết nối tạo ra chúng bị cắt đứt và chỉ biến mất khi SQL Server bị ngắt.

5) Thủ tục lưu trữ từ xa (Remote Stored Procedure )

Thủ tục lưu trữ từ xa gọi các thủ tục lưu trữ từ các server khác.

4.1.3. Ngôn ngữ DLL của thủ tục lưu trữ

1) Tạo thủ tục lưu trữ

Có thể bạn quan tâm!

Xem toàn bộ 323 trang tài liệu này.

Tác dụng: Dùng để tạo một thủ tục lưu trữ Cú pháp:

CREATE PROCEDURE <Procedure_Name>

[<@Parameter_Name> <Datatype> [= <Default_Value>] [ OUT | OUTPUT ] [READONLY],

…

<@Parameter_Name> <Datatype> [= <Default_Value>] >] [ OUT | OUTPUT ] [READONLY]]

[WITH RECOMPLILE|ENCRIPTION| [ EXECUTE_AS_Clause]] [FOR REPLICATION ]

AS BEGIN

Insert statements for procedure here END

Trong đó:

- Procedure_Name: Tên thủ tục cần tạo

- @Parameter_Name: Tên tham số

- Datatype: Kiểu dữ liệu

- Default_Value: Giá trị mặc định

- OUTPUT: Xác định tham số có phải là tham số đầu ra hay không.

- READONLY: Xác định tham số không được thay đổi trong thân của thủ tục.

- RECOMPLILE: Cho biết SQL Server có biên dịch lại thủ tục mỗi khi gọi thủ tục. Tùy chọn này sẽ không được sử dụng khi FOR REPLICATION được chỉ định.

- ENCRIPTION: Cho biết SQL Server mã hóa thủ tục.

- EXECUTE_AS_Clause: Xác định nội dung bảo mật được thực thi trong thủ

tục.

- FOR REPLICATION: Chỉ ra rằng thủ tục được dùng như một bộ lọc và chỉ

được thực thi trong quá trình sao chép. Tham số hình thức không được sử dụng khi sử dụng lựa chọn này.

Ví dụ: Tạo một thủ tục lưu trữ đưa ra các thông tin gồm: Số báo danh, họ tên, ngày sinh của các thí sinh.

Create procedure sp_SelectTS as

begin

select Sobd,Hoten,ngaysinh from Thisinh

end

2) Sửa thủ tục lưu trữ

Tác dụng: Thay đổi nội dung của một thủ tục Cú pháp:

ALTER PROCEDURE <Procedure_Name>

[<@Parameter_Name> <Datatype> [= <Default_Value>] [ OUT | OUTPUT ] [READONLY],

…

<@Parameter_Name> <Datatype> [= <Default_Value>] >] [ OUT | OUTPUT ] [READONLY]]

[WITH RECOMPLILE|ENCRIPTION| [ EXECUTE_AS_Clause]] [FOR REPLICATION ]

AS BEGIN

Alter statements for procedure here END

Ví dụ: Thay đổi thủ tục sp_SelectTS đưa ra các thông tin gồm: Số báo danh, họ tên, ngày sinh của những thí sinh có tuổi lớn nhất.

Alter procedure sp_SelectTS as

begin

declare @maxAge int

select @maxAge = max(year(getdate())-year(ngaysinh)) from Thisinh

select Sobd,Hoten,ngaysinh from Thisinh

where year(getdate())-year(ngaysinh)=@maxAge end

3) Xóa thủ tục lưu trữ

Tác dụng: Xóa thủ tục đã tạo Cú pháp:

DROP PROCEDURE <Procedure_Name>

Với <Procedure_Name> là tên thủ tục lưu trữ sẽ bị xóa. Ví dụ: Xóa thủ tục có tên là sp_SelectTS

DROP PROCEDURE sp_SelectTS

4.1.4. Tham số trong thủ tục lưu trữ

Thủ tục lưu trữ rất hữu ích trong mô hình khách chủ, nhưng nó sẽ trở nên hiệu quả hơn khi bạn biết cách sử dụng các tham số đi kèm với nó. Với những tham số này, ta có thể truyền vào các giá trị cần thiết cho các xử lý bên trong của thủ tục lưu trữ. Ta có thể định nghĩa ra một hoặc nhiều tham số có bên trong một thủ tục lưu trữ. Tên của các tham số mà ta định nghĩa chỉ có phạm vi cục bộ bên trong thủ tục lưu trữ. Chúng là các tham số hình thức. Giống như tên của các tham số trong các ngôn ngữ lập trình khác, ta có thể đặt tên tham số trong thủ tục lưu trữ một cách gợi nhớ và duy nhất. Một thủ tục được phép có tối đa 1024 tham số.

Khi thực hiện một thủ tục lưu trữ, ta có thể truyền tham số để thông báo cho thủ tục lưu trữ nên làm những gì trong cơ sở dữ liệu.

Có 2 loại tham số:

- Tham số nhập

- Tham số xuất

1) Tham số nhập (input parameter)

Là loại tham số do người dùng truyền vào hoặc do một thủ tục lưu trữ khác hay ứng dụng khác gửi đến.

Cú pháp:

<@Parameter_Name> <Datatype>[= <Default_Value>|NULL] Trong đó:

- Parameter_Name: Là tên tham số của thủ tục, nó phải duy nhất trong thủ tục và nên đặt tên tham số một cách gợi nhớ.

- Datatype: Là kiểu dữ liệu của tham số, quy định loại dữ liệu tương ứng được truyền vào cho thủ tục.

- Default_Value: Là giá trị mặc định được gán vào tham số nhập khi tham số nhập không được nhận giá trị khi thủ tục được gọi thực hiện. Nó có thể mang giá trị NULL.

Ví dụ 1: Tạo một thủ tục đưa ra các thông tin gồm: Số báo danh, họ tên, ngày sinh của những thí sinh mà đã đăng kí dự thi ngành có mã ngành cho trước.

Create procedure sp_SelectTS_Nganh1

@man nvarchar(25) as

begin

select Sobd,Hoten,ngaysinh from Thisinh

where man=@man end

Ví dụ 2: Tạo một thủ tục đưa ra các thông tin gồm: Số báo danh, họ tên, ngày sinh của những thí sinh mà đã đăng kí dự thi ngành có mã ngành là „MN001‟.

Create procedure sp_SelectTS_Nganh2

@man nvarchar(25)=‟MN01‟ as

begin

select Sobd,Hoten,ngaysinh from Thisinh

where man=@man end

2) Tham số xuất (output parameter)

Trong những ví dụ trên, ta thấy rò ý nghĩa sử dụng của các tham số đầu nhập. Tuy nhiên, trong thực tế ta cũng mong muốn nhận các giá trị trả về từ những xử lý bên trong thủ tục lưu trữ thông qua các tham số. Khái niệm này còn được gọi là tham số xuất, là những tham số mà giá trị của nó sẽ được tính toán từ bên trong thủ tục lưu trữ và các giá trị đó sẽ được giữ nguyên sau khi thoát ra khỏi thủ tục. Khái niệm này hoàn

toàn giống khái niệm tham số truyền vào theo địa chỉ (by Reference) trong một số các ngôn ngữ lập trình như : Pascal, Visual Basic.

Cú pháp:

<@Parameter_Name> <Datatype> OUTPUT

Ví dụ: Tạo một thủ tục đưa ra tổng điểm 3 môn của thí sinh có số báo danh cho trước. CREATE PROCEDURE sp_TONGD

@sbd nvarchar(25),

@tongd float output AS

BEGIN

select @tongd=dtoan+dly+dhoa from Thisinh

where sbd =@sbd END

4.1.5. Giá trị trả về trong thủ tục lưu trữ

Nếu đối số truyền cho thủ tục khi có lời gọi đến thủ tục là biến, những thay đổi giá trị của biến trong thủ tục sẽ không được giữ lại khi kết thúc quá trình thực hiện thủ tục.

Nếu cần giữ lại giá trị của đối số sau khi kết thúc thủ tục thì dùng tham số OUTPUT. Ngoài ra, ta có thể sử dụng câu lệnh RETURN trả về giá trị cho đối tượng thực thi thủ tục lưu trữ.

Ví dụ: Nhập vào 2 số và tính tổng. Giả sử a=100, b=200 Cách 1:

Create procedure sp_TestOutput1

@a int, @b int, @c int as

Begin

select @c = @a * @b end

Kết quả trả về là: 0 Cách 2:

CREATE PROCEDURE sp_TestOutput2

@a int, @b int, @c int output AS

BEGIN

select @c = @a * @b END

Kết quả trả về là: 20000 Cách 3:

CREATE PROCEDURE sp_TestOutput3

@a int, @b int AS

BEGIN

declare @c int

select @c = @a * @b return @c

END

Kết quả trả về là: 20000

4.1.6. Thao tác với thủ tục lưu trữ

1) Tạo thủ tục lưu trữ

- Dùng công cụ MS

Bước 1: Chọn tên cơ sở dữ liệu/Chọn Programmability/Chọn Stored Procedure/Chọn chuột phải/Chọn New Stored Procedure.

Hình 4.3. Tạo thủ tục lưu trữ

Bước 2: Viết đoạn mã lệnh tạo thủ tục lưu trữ.

Hình 4.4. Viết mã lệnh tạo thủ tục lưu trữ bằng MS

Bước 3: Chọn nút để kiểm tra câu lệnh T-SQL.

Bước 4: Chọn nút để thực hiện câu lệnh tạo thủ tục lưu trữ.

- Dùng câu lệnh T-SQL

Bước 1: Chọn nút New Query , xuất hiện cửa sổ New Query

Bước 2: Khai báo câu lệnh tạo thủ tục lưu trữ (Create Procedure); trước khi viết câu lệnh tạo thủ tục lưu trữ phải khai báo sử dụng cơ sở dữ liệu bằng lệnh USE.