csdl3_btl_trigger
1.Giới thiệu chung về thủ tục thường trú (Store Procedure)
1.1. Khái niệm thủ tục thường trú:
- Thủ tục thường trú là 1 đối tượng trong cơ sở dữ liệu (CSDL), bao gồm nhiều câu lệnh T-SQL được tập hợp lại với nhau thành 1 nhóm, và tất cả các lệnh này sẽ được thực thi chương trình được thực thi. Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá trị thông qua các tham số (như trong ngôn ngữ lập trình). Khi 1 thủ tục thường trú đã được định nghĩa, nó có thể được gọi thông qua tên thủ tục, nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong thủ tục và có thể trả về các giá trị sau khi thực hiện xong.
- Các thành phần có thể có của thủ tục thường trú:
+ Các câu lệnh điều khiển như IF…ELSE, FOR, WHILE…
+ Các biến để lưu trữ các giá trị tính toán được, lưu trữ các giá trị được truy xuất từ CSDL.
+ Các tham số nhận giá trị truyền giá trị vào
- Các đặc điểm cơ bản của thủ tục thường trú:
+ Truyền tham số
+ Gọi thủ tục khác
+ Truyền về các giá trị tham số, chuyển giá trị tham số cho các thủ tục được gọi
+Trả về giá trị trạng thái thủ tục là thành công hay không thành công
1.2. Các loại thủ tục thường trú
Thủ tục thường trú có thể được chia thành 5 nhóm như sau:
1.2.1. Thủ tục thường trú của hệ thống (System Stored Procedure):
Là những thủ tục thường trú chứa trong CSDL Master và thường bắt đầu bằng sys.sp_. Các thủ tục này thường được sử dụng trong quản trị CSDL và an ninh bảo mật. Chúng ta có thể xem các thử tục thường trú hệ thống bằng cách vào System Database -> Master -> Programmability -> System Store Procedure
Ví dụ 1.1:
sp_configure: Muốn biết cấu hình của SQL Server đang dùng.
sp_tables: Cung cấp thông tin về bảng.
sp_password: Cho phép thay đổi password của 1 user.
sp_helptext: Hiển thị nội dung của View và thủ tục thường trú.
sp_helpdb: Hiển thị nội dung của CSDL.
sp_dropuser: Xóa 1 user.
sp_adduser: Thêm 1 user vào CSDL.
sp_addlogin: Tạo mới 1 user.
Muốn biết tất cả tiến trình đang được thực hiện bởi user nào thì dùng thủ tục: sp_who @loginame=’sa’
Kết quả:
1.2.2. Thủ tục thường trú người dùng tự định nghĩa ( Local Stored Procedure) :
Đây là loại thường sử dụng nhất. Chúng được chứa trong CSDL do người dùng tạo ra. Thủ tục loại này hường được tạo bởi DBA( Database Aministrator) hoặc người lập trình.
1.2.3. Thủ tục thường trú tạm thời ( Temporary Stored Procedure)
Là những thủ tục tương tự như thủ tục thường trú người dùng tự định nghĩa nhưng do được tạo ra trên TempDB của SQL Server nên sẽ bị xóa khi kết nối đã tạo ra chúng bị đóng lại hoặc SQL Server Shutdown.
1.2.4. Thủ tục thường trú mở rộng ( Extended Stored Procedure):
Đây là 1 loại thủ tục thường trú sử dụng 1 chương trình ngoại vi (extended program) vốn được biên dịch thành 1 DLL để mở rộng chức năng hoạt động của SQL Server. Loại này thường được bắt đầu bằng xp_. Ví dụ xp_sendmail dùng để gửi mail cho 1 người nào đó hay xp_cmdshell dùng để chạy 1 lệnh DOS. Nhiều loại thủ tục thường trú mở rộng được xem như thủ tục thường trú hệ thống và ngược lại.
1.2.5. Thủ tục thường trú truy cập từ xa (Remote Stored Procedure) :
Những thủ tục thường trú gọi thủ tục thường trú ở server khác
Trong phạm vi của bài tập, chúng ta sẽ tập trung nghiên cứu về thủ tục thường trú người dùng tự định nghĩa.
1.3. Lợi ích của việc sử dụng thủ tục thường trú
- Đảm bảo hiệu suất làm việc của hệ thống khi thủ tục thường trú được gọi liên tục nhiều lần: vì SQLServerchỉbiêndịchcácthủtụcthườngtrúmộtlầnvàsửdụnglạikếtquảbiêndịchnàytrongcáclầntiếptheotrừkhingườidùngcónhữngthiếtlậpkhác.
- Thực thi nhanh hơn: vì thủtụcthườngtrúđượcphântích,tốiưukhitạoranênviệcthựcthichúngnhanhhơnnhiềusovớiviệcphảithựchiệnmộttậprờirạccáccâulệnhSQLtươngđương theocáchthôngthường.
- Giảm thiểu sự lưu thông trên mạng: vì thủtụcthườngtrúchophépchúngtathựchiệncùngmộtyêucầubằngmộtcâulệnhđơngiảnthayvìphảisửdụngnhiềudònglệnhSQL.
- Tăng khả năng bảo mật đối với hệ thống: thayvìcấpphátquyềntrựctiếpchongườisửdụngtrêncáccâulệnhSQLvàtrêncácđốitượngcơsởdữliệu,tacóthểcấpphátquyềnchongườisửdụngthôngqua cácthủtụcthườngtrú.
1.4 Cách trả về kết quả của thủ tục thường trú:
Các thủ tục thường trả về kết quả theo 4 cách:
- Sử dụng các tham số output.
- Sử dụng các lệnh trả về giá trị, các lệnh này luôn trả về giá trị số nguyên.
- Tập các giá trị trả về của mỗi câu lệnh SELECT có trong thủ tục thường trú hoặc của quá trình gọi 1 thủ tục thường trú khác trong một thủ tục thường trú.
- Một biến con trỏ toàn cục có thể tham chiếu từ bên ngoài thủ tục.
2. Tạo 1 thủ tục thường trú trong CSDL
2.1. Thủ tự thường trú được tạo ra thông qua câu lệnh CREATE PROCEDURE
CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_sô)]
[WITH RECOMPILE\ENCRYPTION\RECOMPILE,ENCRYPTION]
AS
Các _câu_lệnh_của_thủ_tục
Trong đó:
2100 tham số có thể được sử dụng trong 1 thủ tục thường trú, tham số bắt đầu bằng @ và phải chỉ ra kiểu dữ liệu của tham số.
WITH RECOMPILE : yêu cầu SQL Server biên dịch lại thủ tục thường trú mỗi khi được gọi
WITH ENCRYPTION : yêu cầu SQL Server mã hóa thủ tục thường trú
Các_câu_lệnh_của_thủ_tục: Các lệnh T-SQL. Các lệnh này có thể nằm trong cặp BEGIN…END hoặc không.
Dung lượng tối đa của thủ tục thường trú là 128MB
Ví dụ 2.1: Giả sử cần thực hiện các công việc theo thứ tự sau:
Nhập 1 đơn đặt hàng mới của khách hàng có mã khách hàng là 3
Nhập các chi tiết đơn đặt hàng cho đơn đặt hàng trên. Giả sử đơn đặt hàng có mã là 4 và khách hàng đặt 1 mặt hàng có mã là 1
Cách 1: Dùng các câu lệnh T-SQL riêng lẻ.
Để thực hiện các công việc trên chúng ta cần các câu lệnh như sau:
Insert into orders values (3,’11/30/2011’)
Insert into orderdetail values (4,1,10)
Cách viết như trên có hạn chế là trong quá trình làm việc sẽ có rất nhiều đơn đặt hàng mới, do đó người dùng sẽ phải viết đi viết lại những câu lệnh tương tự nhau cho các khách hàng khác nhau. Để giải quyết vấn đề này ta dùng cách thứ 2 là dùng thủ tục thường trú và dùng tham số để nhận các thông tin thay đổi.
Cách 2: Dùng thủ tục thường trú:
Xác định các thông tin cần thiết để tạo thủ tục thường trú:
Nơi tạo thủ tục thường trú: CSDL NORTHWIND
Loại thủ tục thường trú: người dùng tự định nghĩa
Tên thủ tục thường trú: sp_InsertOrderAndOrderDetail:
use northwind
create procedure sp_InsertOrderAndOrderDetail
(@customerid nchar(5),
@orderdate datetime,
@orderid int,
@quantity int)
as
begin
insert into Orders (CustomerID,OrderDate)
values(@customerid,@orderdate)
insert into OrderDetails (OrderID,Quantity)
values(@orderid,@quantity)
end
Thực hiện thủ tục thường trú này như sau:
sp_InsertOrderAndOrderDetail'3','11/11/2011','4','10'
2.2. Lời gọi thủ tục thường trú
Thủ tục thường trú được gọi theo cấu trúc:
Tên_thủ_tục_thường_trú [danh_sách_tham_số]
Chú ý: Danh sách tham số truyền vào trong lời gọi phải theo đúng thứ tự khai báo các tham số trong thủ tục thường trú.
Ví dụ 2.2: Gọi thủ tục thường trú sp_InsertOrderAndOrderDetail
sp_InsertOrderAndOrderDetail'3','11/11/2011','4','10'
Nếu thủ tục thường trú được gọi từ 1 thủ tục khác, thực hiện bên trong 1 trigger hay phối hợp với câu lệnh SELECT, cấu trúc như sau:
Exec Tên_thủ_tục_thường_trú [danh_sách_tham_số]
Ví dụ 2.3: Tạo thủ tục thường trú trên CSDL NorthWind, có tên là sp_hienthi để chọn ra các khách hàng có id kết thúc bằng chữ A từ bảng customers
use northwind
create procedure sp_hienthi
as
begin
select * from customers where customerid like '%A'
end
go
Khi gọi thủ tục:
exec sp_hienthi
Kết quả như sau:
Ví dụ 2.4: Tương tự như ví dụ 2.3 nhưng tạo thủ tục với tham số để có thể hiển thị ra thông tin các khách hàng từ bảng customers có id kết thúc bằng bất kỳ chữ cái nào mà bạn muốn.
Tạo thủ tục thường trú có tên là sp_hienthi1 trên CSDL NorthWind
use northwind
create procedure sp_hienthi1
@val varchar(20)
as
begin
select * from customers where customerid like '%'+@val
end
Thực thi thủ tục: chọn ra các khách hàng có id kết thúc bằng chữ cái B
exec sp_hienthi1'B'
Kết quả thu được là:
Ví dụ 2.5: Tạo thủ tục thường trú trên CSDL Pubs, tên thủ tục: au_info_all, để liệt kê tất cả tác giả, sách, nhà xuất bản mà tác giả viết sách:
use pubs
create procedure au_info_all
as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join
publishers p on t.pub_id=p.pub_id
Go
Exec au_info_all
Kết quả như sau:
Ví dụ 2.6: Tạo thủ tục thường trú có tên là au_info trên CSDL Pubs để lọc tìm các tác giả có tên họ truyền theo tham số.
Tạo thủ tục:
create procedure au_info
@lastname varchar(40),@firstname varchar(20)
as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join
publishers p on t.pub_id=p.pub_id
where au_fname=@firstname
and au_lname=@lastname
go
Thực thi thủ tục: có thể sử dụng 3 cách truyền tham số:
- Gán giá trị theo thứ tự:
exec au_info 'Dull','Ann'
- Gán giá trị theo tên biến:
exec au_info @lastname='Dull',@firstname='Ann'
- Gán gí trị theo tên biến, không theo thứ tự
exec au_info @firstname='Ann',@lastname='Dull'
Cho dù dùng cách nào ta cũng có được kết quả là:
2.3.Biến trong thủ tục thường trú
Như đã giới thiệu ở phần khái niệm về thủ tục thường trú, trong thủ tục thường trú có thể có các biến nhằm lưu các kết quả tính toán hay truy xuất từ CSDL. Các biến trong thủ tục dược khai báo bằng từ khóa DECLARE theo cấu trúc sau:
DECLARE @tên_biến kiểu_dữ_liệu
2.4. Giá trị trả về của tham số trong thủ tục thường trú
Trong các ví dụ trước, 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.
Ví dụ 2.7: Có thủ tục thường trú sau:
create procedure sp_TestOutput
(@a int,@b int,@c int)
as
select @c=@a+@b
Thực thi thủ tục:
Declare @tong int
set @tong=0
exec sp_TestOutput 100,200,@tong
select @tong
Khi đó kết quả nhận được là: 0
Như vậy kết quả không được giữ lại khi kết thúc quá trình thực hiện thủ tục.
2.4.1 Sử dụng tham số OUTPUT
Trong trường hợp này, để có thể giữu lại giá trị của đối số sau khi kết thúc thủ tục, ta phải khai báo tham số của thủ tục theo cú pháp sau:
@tên_tham_số kiểu_dữ_liệu OUTPUT
Trong lời gọi thủ tục, sau đối số được truyền cho thủ tục ta cũng phải chỉ định thêm từ khóa OUTPUT
Ví dụ 2.7 được viết lại nhu sau:
create procedure sp_TestOutput
(@a int, @b int, @c int output)
as
select @c=@a+@b
Khi thực thi:
declare @tong int
set @tong=0
exec sp_TestOutput 100,200,@tong output
select @tong
Kết quả là: 300
2.4.2.Sử dụng lệnh RETURN
Tương tự như việc sử dụng tham số OUTPUT, câu lệnh RETURN trả về giá trị cho đối tượng thực thi thủ tục thường trú.
Ví dụ 2.8: Tạo thủ tục thường trú
create procedure sp_TestOutput2
(@a int, @b int)
as
begin
declare @c int
select @c=@a+@b
return @c
end
Thực thi thủ tục:
declare @tong int
exec @tong=sp_TestOutput2 '100','200'
select @tong
Kết quả là 300
Ví dụ 2.9: Taọ thủ tục thường trú có tên là sp_TestReturn trên CSDL NorthWind để đếm số khách hàng trong bảng customer
Tạo thủ tục:
create procedure sp_TestReturn
as
begin
declare @out int
select @out=count(*)
from customers
return @out
end
Thực thi thủ tục:
declare @a int
exec @a=sp_TestReturn
select @a
Kết quả thu được là 91:
2.5. Tham số với giá trị mặc định
Các tham số được khai báo trong thủ tục có thể nhận các giá trị mặc định. Giá trị mặc định sẽ được gán cho tham số trong trường hợp không truyền đối số cho tham số khi có lời gọi đến thủ tục.
Tham số với giá trị mặc định được khái báo theo cú pháp:
@tên_tham_số kiểu_dữ_liệu=giá_trị_mặc_định
Ví dụ 2.10: Tạo thủ tục thường trú có tên là sp_InsertShipper trên CSDL NorthWind để thêm mới 1 bản ghi vào bảng Shippers, trong đó có dùng tham số với giá trị mặc định.
Tạo thủ tục:
create procedure sp_InsertShipper
@company varchar(20)='N/A',
@phone varchar(20)='N/A'
as
begin
Insert into Shippers (CompanyName,Phone)
values(@company,@phone)
end
go
Thực thi thủ tục:
-Nếu có giá trị truyền vào, thủ tục sẽ lấy các giá trị được truyền vào
exec sp_InsertShipper 'Bui Thi Mai Phuong','0978955940'
-Nếu không truyền vào giá trị thì CompanyName và Phone sẽ được lấy mặc định là rỗng.
Cả CompanyName và Phone đều rỗng:
exec sp_InsertShipper
Hoặc điện thoại rỗng nhận giá trị mặc định:
exec sp_InsertShipper 'Nguyen Huyen Nga'
Kết quả cho các trường hợp như sau
Ví dụ 2.11: Tạo thủ tục thường trú có tên là sp_au_info2 trên CSDL Pubs để lọc tìm thông tin tác giả có tên truyền theo tham số, trong đó có dùng tham số với giá trị mặc định với lastname bắt đầu bằng chữ D
Tạo thủ tục thường trú:
create procedure au_info12
@lastname varchar(40)='D%',
@firstname varchar(20)='%'
as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join
publishers p on t.pub_id=p.pub_id
where au_fname like '%'+ @firstname
and au_lname like '%'+ @lastname
Thực thi thủ tục thường trú:
- Không truyền tham số:
exec au_info12
Kết quả thu được như sau: Lọc ra thông tin các tác giả có au_lastname bắt đầu bằng chữ D, au_firstname tùy ý
- Chỉ truyền tham số đầu, tham số sau sẽ nhận giá trị ngầm định
exec au_info1 'WH%'
Kết quả thu được như sau: Lọc ra các tác giả có au_lastname bắt đầu bằng chữ Wh, au_firstname tùy ý
- Chỉ truyền 1 tham số, tham số còn lại nhận giá trị ngầm định
exec au_info1 @firstname='A%'
Kết quả thu được là: Lọc ra các tác giả có au_firstname bắt đầu bằng chữ A, au_lastname nhận giá trị mặc định nên bắt đầu bằng chữ D
- Xác định rõ giá trị tham số:
exec au_info1 'Hunter','Sheryi'
Kết quả thu được là: tác giả có au_lastname Hunter và au_firstname là Sheryl
2.6.Biên dịch lại thủ tuc thường trú:
Các thủ tục thường trú được biên dịch lại để phản ánh sự thay đổi tới các chỉ số.
Có 3 cách để biên dịch lại các thủ tục thường trú:
- Sử dụng thủ tục hệ thống sp_recomplie system
- Chỉ rõ lệnh WITH RECOMPILE với lệnh CREATE PROCEDURE
- Chỉ rõ lệnh WITH RECOPLILE với lệnh EXECUE
3. Thay đổi 1 thủ tục thường trú:
Cho phép định nghĩa lại thủ tục thường trú đã tồn tại
Cú pháp:
ALTER PROCEDURE tên_thủ_tuc_thường_trú[(Danh_sách_tham_số)]
[WITH RECOMPILE/ENCRYPTION/RECOMPILE,ENCRYPTION]
AS
Các_câu_lệnh_của_thủ_tục
Câu lệnh này sử dụng tương tự như câu lệnh CREATE PROCEDURE. Việc sửa đổi lại 1 thủ tục thường trú đã có không làm thay đổi đến các quyền đã cấp phát trên thủ tục cũng như không tác động đén các thủ tục khác hay trigger phụ thuộc vào thủ tục này.
Những lưu ý khi thay đổi nội dung của 1 thủ tục thường trú:
- Thủ tục đó phải tồn tại
- Tùy thuộc vào quyền hạn của người dùng đó có thể thay đổi thủ tục hay không
- Kiểm tra tất cả các thông tin có liên quan đến các đối tượng khác trong khi bị thay đổi.
4. Xóa 1 thủ tục thường trú
Để xóa 1 thủ tục thường trú đã có ta dùng câu lệnh DROP PROCEDURE với cú pháp
DROP PROCEDURE tên_thủ_tục
Khi xóa 1 thủ tục thường trú, tất cả các quyền đã cấp cho người sử dụng trên thủ tục đó cũng đồng thời bị xóa bỏ. Do đó, trước khi xóa cần phải kiểm tra chắc chắn các thông tin của thủ tục kể cả về nội dung và ảnh hưởng của thủ tục đó đối với hệ thống. Và nếu tạo lại thủ tục, ta phải tiến hành cấp phát lại các quyền trên thủ tục đó.
5. Vai trò của các thành viên trong nhóm:
Nhóm trưởng: Bùi Thị Mai Phương CQ46/41.01:
- Tìm hiểu chung về thủ tục thường trú Store Procedure Sql Server
- Tạo thủ tục thường trú bằng lệnh CREATE PROCEDURE
- Lời gọi thủ tục thường trú, truyền giá trị vào các tham số
Thành viên: Nguyễn Quang Ngọc CQ46/41.01:
- Tìm hiểu chung về thủ tục thường trú Store Procedure Sql Server
- Giá trị trả về trong thủ tục thường trú:
+ Sử dụng tham số OUTPUT
+ Sử dụng lệnh Return
Thành viên: Nguyễn Huyền Nga CQ46/41.01
- Tìm hiểu chung về thủ tục thường trú
- Tham số với giá trị mặc định
- Lệnh ALTER PROCEDURE
- Lệnh DROP PROCEDURE
Bạn đang đọc truyện trên: Truyen4U.Com