Giáo trình hệ quản trị cơ sở dữ liệu MS SQL Server 2000

nếu cần ta có thể khởi tạo giá trị mặc định cho tham số:
CREATE PROCEDURE Doanhthu
@dauthang smalldatetime =’1/1/2004’,
@cuoithang smalldatetime
 AS
…
 Tham số output:
Nếu cần xuất giá trị ra ngoài khi SP thực thi xong, hoặc sử dụng kết quả của SP làm tham số đầu vào cho SP khác thì khai báo thuộc tính Output. 
CREATE PROCEDURE test
@X OUTPUT
 AS
…
CÁC LỆNH ĐIỀU KHIỂN 
Cũng giống như các ngôn ngữ lập trình, ta có thể viết các lệnh điều khiển trong SP nói riêng và trong các Script của SQL Server nói chung. 
 Lệnh điều khiển IF … ELSE
Cú pháp: 
IF 
	Begin
	End
ELSE
	Begin
	End
 Lệnh điều khiển CASE
Cũng giống như Select Case trong Visual Basic hoặc ASP
Cú pháp: 
CASE
When …………then …………
When ……….. then ……….
[ELSE ………………………..]
END
VD: 
CASE DatePart(d,NgayBatdau)
	When 1 then ‘SUN’
	When 2 then ‘MON’
	When 3 then ‘TUE’
	When 4 then ‘WED’
	When 5 then ‘THU’
	When 6 then ‘FRI’
	When 7 then ‘SUN’
END
 Lệnh điều khiển WHILE
Cú pháp: 
WHILE
	Begin
	End
 Lệnh điều khiển WAITFOR:
SQL Server tạm dừng một thời gian trước khi xử lý các lệnh tiếp theo.
Cú pháp: 
WAITFOR
DELAY 
hoặc
WAITFOR
	TIME 
HÀM NGƯỜI DÙNG VÀ HÀM HỆ THỐNG 
KHÁI NIỆM HÀM NGƯỜI DÙNG 
SQL Server cho phép người dùng tạo ra các hàm theo những tiêu chuẩn riêng, phù hợp với yêu cầu sử dụng. User Defined Function giống như Stored Procedure. Nó bao gồm các lệnh T-SQL kết hợp tạo nên hàm và có thể được gọi thực thi như là một đơn vị độc lập. 
Một số hạn chế của Hàm người dùng so với SP là tham số không được mang thuộc tính output, nghĩa là giá trị tham số không được truyền ra ngoài hàm. Thay vào đó muốn trả về giá trị ta phải dùng return. 
TẠO VÀ XOÁ HÀM NGƯỜI DÙNG 
Tạo hàm người dùng
CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST) 
RETURNS (return_type_spec) AS 
BEGIN 
(FUNCTION BODY) 
END
Xoá hàm người dùng
DROP FUNCTION 
HÀM HỆ THỐNG 
Hàm người dùng được tạo ra trong một CSDL và chỉ có hiệu lực trong CSDL đó. Nếu hàm có tính phổ biến sử dụng chung trong nhiều CSDL thì ta nên tạo nó như là hàm hệ thống. 
Khi đã là hàm hệ thống thì ta có thể gọi hàm như các hàm của SQL Server thông thường, ví dụ: hàm GetDate(), DatePart()
Tạo hàm hệ thống từ hàm người dùng: 
Tạo hàm trong CSDL Master (là 1 trong 6 CSDL sẵn có của SQL Server ngay từ lúc cài đặt) thay vì tạo hàm trong CSDL đang dùng (nhu CSDL Quanlynha)
Dùng tiền tố fn_TenHam
Thay đổi Owner của hàm (sử dụng SP sp_changeobjectowner hoặc dùng EM)
TRIGGER
KHÁI NIỆM: 
Trigger là một dạng SP đặc biệt dùng để đáp ứng một sự kiện cụ thể (Insert, Update, Delete). Nó bao gồm một đoạn Script được gắn vào bảng dữ liệu và tự động thực thi khi có một sự kiện xảy ra tương ứng với Trigger được gán cho sự kiện đó (trong khi SP chỉ thực thi khi được gọi ra) . 
Trigger không có tham số và giá trị trả về. 
Chỉ nên sử dụng Trigger trong trường hợp cần thiết, chẳng hạn trong những trường hợp sau: 
- Referential Integrity: ràng buộc toàn vẹn dữ liệu cho phù hợp với mô hình quan hệ cơ sở dữ liệu.
- Audit Trails: kiểm soát dữ liệu hiện tại khi có thay đổi đến giá trị trong bản ghi của bảng. 
- Check: kiểm tra dữ liệu nhập vào có phù hợp với mối liên hệ giữa các bảng với nhau.
- Kiểm chứng khi xoá bản ghi 
PHÂN LOẠI 
Có thể chia Trigger thành 3 loại theo sự kiện mà nó đáp ứng: 
- Insert Trigger.
- Update Trigger.
- Delete Trigger.
- Tổng hợp cả 3.
TẠO TRIGGER
 Cú pháp
Tạo Trigger giống như SP, và nó phải được tạo ra cho bảng cụ thể
Ví dụ: Tạo một Trigger cho bảng Hopdong để kiểm tra ràng buộc dữ liệu với bảng Nha (không thể tạo hợp đồng cho một nhà chưa nhập vào CSDL)
CREATE TRIGGER Ins ON [dbo].[Hopdong] 
FOR INSERT 
AS
if not exists 
( select 'true' from inserted where inserted.nhaid in (select nhaid from nha)
)
begin 
raiserror ('Không thể tạo Hợp đồng này vì Nhà chưa được nhập trong table Nhà',16,1)
rollback Tran
end 
Diễn giải: 
On: chỉ ra Trigger đang viết cho bảng hay khung nhìn nào. Nếu có từ khoá after thì không hỗ trợ view. 
For Insert /For Update / For Delete: dùng khai báo đối với Insert Trigger/Update Trigger, Delete Trigger. 
AS: giống như SP, nó chỉ định cho SQL Server biết điểm bắt đầu, sau AS là Script
 Tạo Trigger bằng EM. 
	Sau khi một bảng đã được gắn Trigger,nó sẽ tự động thực thi với sự kiện tương ứng. Chẳng hạn như ví dụ dưới đây, Trigger Ins được thực thi khi người dùng Insert một bản ghi mới vào bảng Hopdong. Nó đưa ra thông báo lỗi vì Nhà có NhaID =100 chưa tồn tại trong bảng Nha. 
CHUYỂN TÁC (TRANSACTION) VÀ KHOÁ (LOCK)
KHÁI NIỆM CHUYỂN TÁC 
Xuất phát từ nhu cầu khi giao tiếp với dữ liệu có những trường hợp chúng ta mong muốn hoặc đồng thời nhiều sự kiện diễn ra, hoặc không sự kiện nào. Ví dụ: thao tác chuyển tiền bao gồm 2 sự kiện: rút tiền từ tài khoản A và cộng vào tài khoản B, yêu cầu hoặc cả 2 thao tác đều phải hoàn thành, hoặc không thao tác nào hoàn thành. Đó chính là một Chuyển tác. 
Các lệnh: 
Begin: bắt đầu một chuyển tác; Commit: xác nhận chuyển tác đã hoàn thành.
Rollback: quay ngược chuyển tác. (Huỷ toàn bộ các sự kiện của chuyển tác)
Save : đánh dấu một điểm cho phép Rollback chỉ một phần chuyển tác.
Một ví dụ là chúng ta đã thực hiện chuyển tác ngầm Rollback TRAN trong Trigger Ins ở chương Trigger, khi nhận thấy có lỗi trong ràng buộc dữ liệu thì huỷ toàn bộ quá trình Insert dữ liệu:
if not exists 
( select 'true' from inserted where inserted.nhaid in (select nhaid from nha)
)
begin 
raiserror ('Không thể tạo Hợp đồng này vì Nhà chưa được nhập trong table Nhà',16,1)
rollback Tran
end 
KHOÁ (LOCK) VÀ ĐỒNG HÀNH (CONCURRENCY)
Khi có nhiều chuyển tác cùng thực hiện trên một đối tượng, người ta gọi là đồng hành. Dễ hiểu hơn, đồng hành là hiện tượng tại một thời điểm có nhiều người cùng kết nối và truy cập một đối tượng của CSDL. Trong nhiều trường hợp điều này sẽ xảy ra xung đột, ví dụ nhiều người đồng thời cố gắng sửa dữ liệu trong một bảng. 
Để kiểm soát người dùng thực hiện những chuyển tác đồng hành, ngăn ngừa xung đột, đụng độ, người ta dùng khoá. Có nghĩa là một người không thể làm gì để sửa đổi một đối tượng khi có một người khác đang truy cập nó trước người đó. Shared lock: Thực tế có nhiều kiểu khoá. Khoá căn bản nhất là khoá chia sẻ (Shared lock). Nó cho phép đọc dữ liệu và không cho phép thay đổi bất kỳ thuộc tính nào của dữ liệu. 
SỬ DỤNG NGÔN NGỮ LẬP TRÌNH TƯƠNG TÁC VỚI SQL SERVER
SQL Server thuần tuý chứa CSDL. Để cho phép người sử dụng khai thác được CSDL SQL Server, cần xây dựng những ứng dụng được phát triển bằng ngôn ngữ lập trình. 
LẬP TRÌNH BẰNG ASP
Sử dụng ASP để thao tác với CSDL nói chung và SQL Server nói riêng được đề cập kỹ trong giáo trình môn “Lập trình Web bằng ASP”. Ở đây chúng ta đề cập đến một số khía cạnh thực hành. 
Kết nối đến CSDL SQL Server: 
Dùng đối tượng Connection
‘tạo một Connection
Set Conn = Server.CreateObject("ADODB.Connection")
‘ Khởi tạo chuỗi kết nối
 strConn = "driver={SQL Server}; server=127.0.0.1; uid=iitm; pwd=test; database=quanlynha"
‘ Kết nối đến CSDL 
Conn.Open strConn
Thực thi câu lệnh SQL: 
conn.execute "update hopdong set giatien=10000000 where hopdongid=1"
Thao tác với các bản ghi của một bảng: 
Dùng đối tượng Recordset
<% 
‘ câu lệnh SQL
sqlString= “select * from Hopdong”
‘ Tạo đối tượng Recordset
Set RS = Server.CreateObject("ADODB.Recordset")
‘ thực thi câu lệnh SQL thông qua đối tượng kết nối Conn, kết quả trả về cho đối tượng Recordset.
Rs.Open sqlString,Conn
‘ duyệt từng bản ghi trong bảng Hopdong bằng Recordset, hiện thi dạng bảng trên ‘giao diện Web. 
<%
‘ vòng lặp do while để duyệt tất cả các bản ghi
do while not Rs.EOF
%>
<%
	‘ dịch chuyển đến bản ghi kế tiếp
	RS.MoveNext 	
 Loop
RS.close
%>
Cập nhật vào CSDL bằng Recordset: 
sqlString =”select * from hopdong”
Set RS1 = Server.CreateObject("ADODB.Recordset")
'de co the update 
adOpenDynamic=2
adLockPessimistic=2
Rs1.Open sqlString,Conn,adOpenDynamic,adLockPessimistic
do while not Rs1.EOF
‘ lấy giá tiền thuê nhà từ trường Giatien
tempGiatien=rs1(“giatien”)
‘ tăng giá tiền thuê nhà lên gấp đôi rồi cập nhật vào trường Giatien
rs1("giatien")=tempGiatien*2 
‘ xác nhận việc cập nhật
rs1.Update
rs1.MoveNext
loop
Rs1.Close
Thực thi một SP của SQL Server 
Trong ví dụ ở chương Stored Procedure ta đã tạo một SP tên “Doanhthu” trong CSDL Quanlynha
CREATE PROCEDURE Doanhthu
@dauthang smalldatetime,
@cuoithang smalldatetime
 AS
…
Gọi thực hiện SP đó từ ASP: 
‘ gán giá trị cho các biến
dauthang=”1/1/2004” ‘month/date/year
cuoithang=”1/31/2004”
‘câu lệnh sql gọi SP có tham số 
 sql="execute doanhthu " + dauthang + "," + cuoithang
 Set RS = Server.CreateObject("ADODB.Recordset")
 RS.Open sql,Conn
LẬP TRÌNH BẰNG VISUAL BASIC 
Sử dụng Visual Basic để thao tác với CSDL nói chung và SQL Server nói riêng được đề cập kỹ trong giáo trình môn “Lập trình VISUAL BASIC”. Về căn bản cũng tương đối giống ASP. 
Kết nối đến CSDL: 
‘Khai báo biến Connection
Dim Conn as New ADO
‘ chuỗi kết nối CSDL 
Dim StrConn as String
StrConn=”Provider=SQLOLEDB.1;Persist Security = False; uid=iitm; pwd=test; Initial Catalog=Quanlynha;server=127.0.0.1 ;”
‘ Tạo kết nối: 
Set Conn=CreateObject (“ADODB.Connection”)
Conn.Open StrConn
Thực hiện câu lệnh SQL:
Conn.Execute “Delete from hopdong where hopdongid=2”
Thực thi SP
Conn.Execute “Doanhthu ‘”&”1/1/2004”&” ‘,’ ”&”1/31/2004”&”’”
Thao tác với bản ghi
‘ Khai báo biến Recordset
Dim RS
‘ Tạo đối tượng Recordset
Set RS=CreateObject (“ADODB.Recordset”)
‘ Thực thi câu lệnh SQL cho kết quả trả về Recordset: 
Rs.open “Select * from Hopdong”, Conn, 2, 2
‘ Insert dữ liệu
RS.Addnew
RS(“NhaID”)=”2”
RS(“NhaCoquanID”)=”3”
RS(“NgayBatdau”)=”1/1/2004”
RS(“NgayKetthuc”)=”1/1/2006”
RS(“Giatien”)=”5000000”
RS.Update
‘ Đóng kết nối
RS.Close
Conn.Close
Set RS=nothing
Set Conn= nothing
MỘT SỐ VẤN ĐỀ NÂNG CAO. 
SECURITY
CHUYỂN TÁC VÀ TRUY VẤN PHÂN TÁN 
 XML
TÌM KIẾM FULL TEXT SEARCH
DỊCH VỤ PHÂN TÍCH ANALYSIS SERVICES
DATABASE REPLICATE
ENGLISH QUERY