Tài liệu SQL Server

ta không gửi giá trị cho tham số, thì tham số sẽ lấy giá trị mặc 
nhiên.
Mô hình
(Model)
Cú pháp
(Syntax)
CREATE PROCEDURE Tên_SP
Các_tham_số
AS
Các_lệnh
- Các_tham_số:
Biên soạn: Nguyễn Viết Nam
Phân loại
Local (bắt đầu bằng #)
Global (bắt đầu bằng ##)
SP được tạo ra trực tiếp trên TempDB
40
Tài liệu SQL Server
+ Tên_tham_số Kiểu_dữ_liệu
[= Giá_trị_mặc_định] [OUTPUT]
Ví dụ
(Example)
- Tạo SP:
CREATE PROCEDURE sp_Cong2
@a INT, @b INT = 50, @c INT = 100
AS
DECLARE @kq INT
SET @kq = @a + @b + @c
PRINT @kq
RETURN
- Gọi SP:
EXEC sp_Cong2 10, 20 -> 130
EXEC sp_Cong2 @a = 10, @c = 20 -> 80
Tham số OUTPUT
Mục đích
(Purpose)
- Tham số mặc nhiên là loại INPUT (tham trị), tức là khi truyền tham số thực, 
tham số sẽ nhận giá trị gởi vào để thực hiện thủ tục.
- Nếu có từ khóa OUTPUT sau tham số, đây là loại tham biến -> tham số thực 
gởi vào sẽ là một biến để nhận giá trị trả về.
Mô hình
(Model)
Cú pháp
(Syntax)
CREATE PROCEDURE Tên_SP
Các_tham_số
AS
Các_lệnh
- Các_tham_số:
+ Tên_tham_số Kiểu_dữ_liệu
[= Giá_trị_mặc_định] [OUTPUT]
Ví dụ
(Example)
- Tạo SP:
CREATE PROCEDURE sp_Cong3
@a INT, @b INT = 50, @kq INT OUTPUT
AS
SET @kq = @a + @b 
PRINT @kq
RETURN
- Gọi SP:
DECLARE @kq INT
EXEC sp_Cong3 10, 20, @kq OUTPUT
PRINT @kq -> 30
5.2.8.2. Thực thi (Executive)
EXEC
Mục đích
(Purpose) - Thực thi Stored Procedure.
Mô hình
(Model)
Cú pháp
(Syntax) EXEC Tên_SP
Ví dụ
(Example) EXEC sp_Cong 20,30
5.2.8.3. Hiệu chỉnh (Alter)
ALTER PROCEDURE Mục đích
(Purpose) - Hiệu chỉnh Stored Procedure.
Mô hình
(Model)
Cú pháp
(Syntax)
ALTER PROCEDURE Tên_SP
Các_tham_số
AS
Các_lệnh
- Các_tham_số:
+ Tên_tham_số Kiểu_dữ_liệu
[= Giá_trị_mặc_định] [OUTPUT]
Biên soạn: Nguyễn Viết Nam 41
Tài liệu SQL Server
Ví dụ
(Example)
ALTER PROCEDURE sp_Cong
@a INT, @b INT
AS
DECLARE @kq INT
SET @kq = @a + @b
PRINT ‘Ket qua = ‘ + CONVERT(VARCHAR, 
@kq)
RETURN
5.2.9. Hàm (Function)
5.2.9.1. Vô hướng (Scalar Functions)
5.2.9.1.1.1. Tạo (Create)
CREATE FUNCTION
Mục đích
(Purpose)
- Giá trị trả về thuộc kiểu dữ liệu vô hướng như: int, 
smallint, …
- Trừ các kiểu sau không được dùng làm kiểu trả về: BLOBs 
(những đối tượng lớn: file, binary), cursor, timestamp.
- Có thể sử dụng các hàm này trong phát biểu SQL như 
SELECT.
Mô hình
(Model)
Cú pháp
(Syntax)
CREATE FUNCTION Tên_Hàm
(Các_tham_số)
RETURNS Kiểu_trả_về
AS
BEGIN
Các_lệnh
RETURN Giá_trị_trả_về
END
Ví dụ
(Example)
CREATE FUNCTION DT_HCN
(@Dai DECIMAL(4,1), @Rong DECIMAL(4,1))
RETURNS DECIMAL(10,1)
AS
BEGIN
RETURN @Dai*@Rong
END
5.2.9.1.1.2. Thực thi (Execute)
Tên_hàm
Mục đích
(Purpose)
- Sử dụng như bất kỳ hàm nào của hệ thống.
- Cần cung cấp tên sở hữu chủ (owner).
Mô hình
(Model)
Cú pháp
(Syntax) owner.Tên_Hàm(Các_tham_số)
Ví dụ
(Example) PRINT dbo.DT_HCN(8.5, 10.3)
5.2.9.2. Trả về Table
5.2.9.2.1.1. Tạo (Create)
Inline Table-Value Mục đích
(Purpose) - Giá trị trả về là một table là kết quả của phát biểu SELECT.
Mô hình
(Model)
Cú pháp
(Syntax)
CREATE FUNCTION Tên_Hàm
(Các_tham_số)
RETURNS TABLE
Biên soạn: Nguyễn Viết Nam 42
Tài liệu SQL Server
AS
RETURN Câu_lệnh_SELECT
Ví dụ
(Example)
CREATE FUNCTION DSNV
(@MS NCHAR(2))
RETURNS TABLE
AS
RETURN (SELECT *
 FROM NhanVien
 WHERE MSPB = @MS)
Multistatement Table-Valued
Mục đích
(Purpose)
- Thân hàm là một khối BEGIN … END chứa các lệnh T-SQL 
dùng để xây dựng và xử lí các dữ liệu đưa vào các mẫu tin 
trong table mà nó trả về.
Mô hình
(Model)
Cú pháp
(Syntax)
CREATE FUNCTION Tên_Hàm
(Các_tham_số)
RETURNS @Biến_Bảng
TABLE (Các_định_nghĩa_bảng)
AS
BEGIN
Các_lệnh
RETURN
END
Ví dụ
(Example)
CREATE FUNCTION fnFindReports (@InEmpID 
INTEGER)
RETURNS @retFindReports TABLE
(EmployeeID INT PRIMARY KEY NOT NULL,
 Name NVARCHAR(255) NOT NULL,
 Title NVARCHAR(50) NOT NULL,
 EmployeeLevel INT NOT NULL,
 Sort NVARCHAR(255) NOT NULL
)
AS
BEGIN
INSERT @retFindReports
SELECT EmployeeID, Name, Title, 
EmployeeLevel, Sort
FROM DirectReports
RETURN
END
5.2.9.2.1.2. Thực thi (Execute)
SELECT * FROM Tên_hàm
Mục đích
(Purpose)
- Có thể dùng trong mệnh đề FROM của câu lệnh SELECT.
- Cần cung cấp tên sở hữu chủ (owner).
Mô hình
(Model)
Cú pháp
(Syntax) SELECT … FROM owner.Tên_Hàm(Các_tham_số)
Ví dụ
(Example)
SELECT … FROM dbo.DSNV(‘P1’)
SELECT … FROM dbo.DSNV(‘P2’)
WHERE Luong>500
6. Kiểm tra dữ liệu (Data Control)
6.1. Phân quyền (Permission)
Phân quyền 
(Permission)
If Exists (Select Name From SysObjects Where Name='Sp_XemThanhVienRole'
Drop Procedure Sp_XemThanhVienRole
Go
Create Proc Sp_XemThanhVienRole (@RoleName SysName)
Biên soạn: Nguyễn Viết Nam 43
Tài liệu SQL Server
As
If Exists (Select Name From SysUsers Where Name=@RoleName)
Begin
Select t1.name[TenNhom],t2.name[CacThanhVienNhom],
t2.uid[MS],t2.password[MK],t2.gid[MSNhom]
From SysUser t1,SysUser t2,SysMembers t3
Where t1.uid=t3.groupid and t1.issqlrole=1 and
t1.name=@RoleName and t2.uid=t3.memberuid
Order By 1
End
Go
--======================================
--Role & các User bên trong nó
--Dùng biến đếm & lấy từng dòng dữ liệu 1
Create Proc Sp_XoaRoleDem (@RoleName SysName)
As
If Exists (Select Name From SysUsers Where Name=@RoleName)
Begin
--Nếu Role còn thành viên tức là không thể xóa cho nên cần 
xóa các User đang nằm trong Role trước
Declare @SoThanhVien Int
Select @SoThanhVien=Count(*)
From SysUsers t1,SysUsers t2,SysMembers t3
Where t1.uid=t3.groupid and t1.issqlrole=1 and
t1.name=@RoleName and t2.uid=t3.memberuid
If (@SoThanhVien>0)--Có User trong Role
Begin
Declare @i Int
Set @i=0
While (@i<@SoThanhVien)
Begin
Declare @Value SysName
Set @Value=(Select Top 1 t2.name
From SysUser t1,SysUser t2,SysMember 
t3
Where t1.uid=t3.groupid and 
t1.ssqlrole=1 and
t1.name=@RoleName and 
t2.uid=t3.memberuid)
Exec Sp_DropRoleMember @RoleName,@Value
Set @i=@i+1
End
End
Exec Sp_DropRole @RoleName --Sau khi rỗng thì có thể xóa
End
Go
--Dùng Cursor
Create Proc Sp_XoaRoleCursor (@RoleName SysName)
As
If Exists (Select Name From SysUsers Where Name=@RoleName)
Begin
--Nếu Role còn thành viên tức là không thể xóa cho nên cần 
xóa các User đang nằm trong Role trước
Declare @SoThanhVien Int
Select @SoThanhVien=Count(*)
From SysUsers t1,SysUsers t2,SysMembers t3
Where t1.uid=t3.groupid and t1.issqlrole=1 and
t1.name=@RoleName and t2.uid=t3.memberuid
If (@SoThanhVien>0)--Có User trong Role
Begin
Declare x Cursor For Select t2.name
From SysUsers t1,SysUsers 
t2,SysMembers t3
Where t1.uid=t3.groupid and 
t1.issqlrole and
Biên soạn: Nguyễn Viết Nam 44
Tài liệu SQL Server
t1.name=@RoleName and 
t2.uid=t3.memberuid
Declare @Value SysName
Open x
Fetch Next From x Into @Value
While (@@Fetch_Status=0)
Begin
Exec Sp_DropUser @Value
Fetch Next From x Into @Value
End
Close x
Deallocate x
End
Exec Sp_DropRole @RoleName
End
--======================================
If Exists (Select Name From SysObjects Where Name='Sp_ThemTVUserVaoRole')
Drop Procedure Sp_ThemTVUserVaoRole
Create Proc Sp_ThemTVUserVaoRole (@TenRole SysName,
@TenUser SysName,
@BiDanh SysName=Null)
As
If (@BiDanh Is Null) Set @BiDanh=@TenUser
--Xem trong Role đã có User nào với bí danh
If Not Exists (Select Name From SysUsers Where Name=@BiDanh)
Begin
Exec Sp_AddUser @TenUser,@BiDanh,@TenRole
--Exec Sp_GrandDBAccess @TenUser,@BiDanh
--Exec Sp_AddRoleMember @TenRole,@BiDanh
End
Else
--Đã tồn tại @TenUser thì ta chỉ cần thêm nó vào Role cần 
thiết
Exec Sp_AddRoleMember @TenRole,@BiDanh
Go
--======================================
--Xóa Role
Exec Sp_XoaRoleDem 'rl...'--Bao gồm xóa cả user
--hay Exec Sp_XoaRoleCursor 'rl...'
--Hủy quyền user với bí danh tương ứng là ...
If Exists (Select Name From SysUsers Where Name='BiDanh')
Exec Sp_RevokedDBAccess 'BiDanh'
Go
--Xóa Login khi không còn bí danh nào tham chiếu đến nó nữa
If Exists (Select Name From MasterSysLogins Where Name='us...')
Exec Sp_DropLogin 'us...'--Login tên là User
Go
--======================================
--Tạo Role
If Not Exists (Select Name From SysUsers Where Name='rl...')
Exec Sp_AddRole 'rl...'
--hay Exec Sp_AddGroup 'rl...'
--Tạo Login tên User có password là Null tham chiếu đến CSDL db...
If Not Exists (Select Name From Master.SysLogins Where Name='us...')
Exec Sp_AddLogin 'us...',Null,'db...'
Go
Exec Sp_ThemTVVaoRole 'rl...','us...',BiDanh'
Drop Profile pfUser;
Create Profile pfUser
 Limit Sessions_Per_User 5
 CPU_Per_Session Unlimited
 CPU_Per_Call Unlimited
 Connect_Time Unlimited
 Idle_Time Unlimited;
Biên soạn: Nguyễn Viết Nam 45
Tài liệu SQL Server
Drop Role rlUser;
Create Role rlUser;
 Grant Create Session To rlUser;
 Grant Create Synonym To rlUser;
 Grant Create Table To rlUser;
 Grant Create View To rlUser;
 Grant Create Procedure To rlUser;
 Grant Create Trigger To rlUser;
 Grant Select Any Table To rlUser;
Drop User usUser;
Create User usUser
 Identified By nam
 Default TableSpace tbsUser
 Temporary TableSpace tbsTam
 Quota 1M On tbsUser
 Quota 1M On tbsTam
 Profile pfUser;
Grant rlUser To usUser;
6.2. Trigger
Trigger
If Exists (Select Name From SysObjects Where Name Like 'Tg_Xoa...')
Drop Trigger Tg_Xoa...
Go
--Tạo trigger tên là Tg_Xoa... lên bảng tb... để Delete
Create Trigger Tg_Xoa... On tb... For Delete
As
If (@@RowCount=0)
Begin
Print 'Bảng không có dữ liệu nển không xóa được')
Return
End
Delete tb...
From tb... t1, Deleted t2
Where t1.Id=t2.Id
If Exists (Select Name From SysObjects Where Name Like 'Tg_CapNhat...')
Drop Trigger Tg_CapNhat...
Go
--Tạo trigger tên là Tg_CapNhat... lên bảng tb... để Update
Create Trigger Tg_CapNhat... On tb... For Update
As
If (@@RowCount=0)
Begin
Print 'Bảng không có dữ liệu nển không xóa được')
Return
End
If Update(tb...)
Begin
Update tb...
Set t1.Id=t3.Id
From ... t1, Deleted t2,Inserted t3
Where t1.Id=t2.Id
End
Biên soạn: Nguyễn Viết Nam 46