Giáo Trình SQL bài 11

1
PROCEDURES và
FUNCTIONS
2
1. Khái niệm cơ bản về Stored procedure:
Stored procedure (thủ tục) là một tập các lệnh T-SQL và một số cấu trúc điều kiện
Một khi Stored procedure được thiết lập thì những ứng dụng cần thiết tương tự chỉ cần thực gọi để thực hiện Store procedure
Thủ tục lưu được tối ưu hoá lần đầu tiên khi chúng được biên dịch.
Thủ tục nâng cao an toàn thông tin thông qua việc cô lập hoặc mã hóa
3
System sp: Được lưu trữ trong CSDL master và được đặt tên với tiếp đầu ngữ là sp. Chúng đóng vai trò khác nhau của các tác vụ được cung cấp trong SQL Server.
Ví dụ: Sp_help, Sp_helpConstraint,…
Local sp: Được lưu trữ trong các CSDL của người dùng, nó thực thi các tác vụ (Task) trong Database chứa nó.
Khi thực thi thủ tục, cần cung cấp các giá trị của tham số của các thủ tục nếu có. Một thủ tục có thể được gọi thực thi hoặc tự động thực thi khi SQL Server khởi động. Gọi thực thi bằng từ khóa EXCUTE.
4
2. Tạo Store Procedure:

CREATE PROC[EDURE] procedure_name
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ]
[ ,...n ]

[ WITH { RECOMPILE | ENCRYPTION } ]

[ FOR REPLICATION ]

AS

sql_statement [ ...n ]
5
6
VD:
Create Procedure sp_SinhVien
@NamSinh int
AS
Select * From tbl_SinhVien
Where Year(NgaySinh) = @NamSinh
-------------------
3. Thực thi Store Procedure:
EXEC[ute] procedure_name
[[@parameter =]{value |@variable [OUTPUT] | [DEFAULT]]
[,…n]

VD: EXEC sp_SinhVien 1984
7
Create Proc Sp_ChenKhachHang
@MaKH varchar (10), @TenKH nvarchar (50),
@Address nvarchar (50), @Phone varchar (24),
@Fax nvarchar (24), @mail nvarchar (50)
AS
Insert Into KhachHang (MaKH, TenKH, Address, Phone, Fax, eMail)
Values (@MaKH, @TenKH, @Address, @Phone, @Fax, @Mail)

Gọi thủ tục:
Sp_ChenKhachHang `KH01`, N`Trường Tin học ABC`, `12 Nguyễn Văn Bảo`, `886699`, `791175`, `@yahoo.com`
8
a. Store Procedure and Input Parameters:
Create Procedure sp_NhanVien
@Ho nvarchar(50),
@Ten nvarchar(10)
As
Select * From NhanVien
Where Ho = @Ho And Ten = @Ten

------------ Câu 2.10. trong Bài tập - Bài7 ---------------
Create Procedure sp_ThongTinHoaDonSo
@So_HoaDon int
As
Select d.MaNhanVien, d.NgayGiaoHang, d.NoiGiaoHang
From Dondathang as d INNER JOIN NhanVien as n
ON d.MaNhanVien = n.MaNhanVien
Where SoHoaDon = @So_HoaDon
9
a. Store Procedure and Default Parameters:
Create Procedure sp_NhanVien
@Ho nvarchar(50) ,
@Ten nvarchar(10) = `%`
As
Select * From NhanVien
Where Ho Like @Ho And Ten Like @Ten

------------ Câu 2.10. trong Bài tập - Bài7 ---------------
Create Procedure sp_ThongTinHoaDonSo
@So_HoaDon int = 1
As
Select d.MaNhanVien, d.NgayGiaoHang, d.NoiGiaoHang
From Dondathang as d INNER JOIN NhanVien as n
ON d.MaNhanVien = n.MaNhanVien
Where SoHoaDon = @So_HoaDon
10
Store Procedure and Output Parameters:
Create Procedure sp_GiaBan
@MaHang int, @GiaBan money OUTPUT
AS
Select @GiaBan = Giaban
From MatHang
Where MaHang = @MaHang


Gọi thủ tục:
Declare @price money
Execute sp_GiaBan 77, @price Output
Print Convert (varchar(10), @price)
11
Ví dụ: Giả sử ta cần thực hiện một chuỗi các thao tác như sau trên cơ sở dữ liệu:

Bổ sung thêm môn học “cơ sở dữ liệu” có mã “TI-005” và số đơn vị học trình là 5 vào bảng “MONHOC”

Nhập điểm thi môn “cơ sở dữ liệu” cho các sinh viên học lớp có mã C24102 (tức là bổ sung thêm vào bảng DIEMTHI các bản ghi với cột MAMONHOC nhận giá trị TI-005, cột MASV nhận giá trị lần lượt là mã các sinh viên học lớp có mã C24105 và các cột điểm là NULL).
12
VD: Nếu thực hiện yêu cầu trên thông qua các câu lệnh SQL như thông thường, ta phải thực thi hai câu lệnh như sau:

Insert Into MonHoc(MaMH,TenMH,SoDVHT)
Values(`TI-005`, N`Cơ sở dữ liệu`, 5)

Insert Into DiemThi(MaMonHoc,MaSV)
Select `TI-005`, MaSV
From SinhVien
Where MaLop = `C24102`
13
Create Proc sp_LenDanhSachDiem
@maMH nvarchar(10), @tenMH nvarchar(50),
@sodvht smallint, @maLop nvarchar(10)
@KiemTra int OUTPUT
As
BEGIN
Insert Into MonHoc
Values(@maMH, @ tenMH, @sodvht);

Insert Into DiemThi(MaMH, MaSV)
Select @maMH, MaSV FROM SinhVien
Where MaLop=@maLop
If @@ERROR <> 0 Then
Set @KiemTra = 1 -- SET @KiemTra = @@ERROR
Else
Set @KiemTra = 0
END
14
Ghi chú:
Khi thực thi SP có tham số OUTPUT ta phải khai báo (DECLARE) một biến trước khi gọi SP thực thi và phải có từ khóa OUTPUT ngay sau tham số đó.

VD: gọi thực thi ví dụ trên.

DECLARE @Kiem_Tra int

EXEC sp_LenDanhSachDiem `TI-005`, N`Cơ sở dữ liệu`, 5, `C24102‘, @Kiem_Tra OUTPUT

PRINT @KiemTra
15
VD (tt):
Create Proc sp_NhanVienCungNamSinh
@Dem int Output
As
Select a.Ho + ` ` + a.Ten as HoTen, a.NgaySinh
From NhanVien a Inner Join NhanVien b
On Year(a.NgaySinh) = Year(b.NgaySinh)
Where a.MaNhanVien <> b.MaNhanVien
Set @Dem = @@ROWCOUNT

Gọi thực thi:
Declare @dem int
Exec sp_NhanVienCungNamSinh @dem output
Print @Dem
16
VD: Store Procedure ENCRYPTION

IF EXISTS (SELECT name FROM sysobjects
WHERE name = sp_encrypt_this` AND type = `P`)
DROP PROCEDURE sp_encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE sp_encrypt_this
WITH ENCRYPTION
AS
SELECT * FROM authors
GO
EXEC sp_helptext sp_encrypt_this
EXEC sp_encrypt_this
17
3. Hiệu chỉnh Store Procedure:
ALTER PROC[EDURE] procedure_name
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ]
[ ,...n ]
[ WITH
{ Recompile | Encryption | Recompile , Encryption } ]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
--------------------------------
Hoặc trong SQL Query Analyzer  phải chuột  Edit
18

5. Xem nội dung Store Procedure:
sp_helptext procedure_name

6. Xóa Store Procedure:
DROP PROCEDURE procedure_name
19
II. FUNCTION:
CREATE FUNCTION [ owner_name. ] function_name
([{ @parameter_name data_type [ = default ]} [ ,...n ]])
RETURNS return_data_type
AS
BEGIN
[ function_body ]
RETURN ( expression )
END
20
1. Ví dụ về Hàm trả về một giá trị:
CREATE FUNCTION fn_Thu (@ngay datetime)
RETURNS nvarchar(10)
AS
BEGIN
Declare @st nvarchar(10)
Select @st=CASE Datepart (DW, @ngay)
When 1 Then N`Chu nhật`
When 2 Then N`Thứ hai`
When 3 Then N`Thứ ba`
When 4 Then N`Thứ tư`
When 5 Then N`Thứ năm`
When 6 Then N`Thứ sáu`
Else N`Thứ bảy`
END
RETURN ( @st ) /* Trị trả về của hàm */
END
21
Sử dụng hàm:
SELECT MaSV, HoDem, Ten, dbo.Thu(ngaysinh), NgaySinh
FROM SinhVien
WHERE MaLop= `C24102`
22
2. Ví dụ về Hàm trả dữ liệu kiểu TABLE:
CREATE FUNCTION fn_XemSV(@khoa smallint)

RETURNS TABLE

AS
RETURN ( SELECT MaSV, HoDem, Ten, NgaySinh
FROM SinhVien INNER JOIN Lop
ON SinhVien.MaLop = Lop.MaLop
WHERE Khoa=@khoa )


Gọi hàm:
SELECT * FROM dbo.fn_XemSV (25)
23
CREATE FUNCTION fn_TongSV(@khoa smallint)
RETURNS @bangthongke TABLE
( MaKhoa nvarchar(5), TenKhoa nvarchar(50), tongsosv int )
AS
BEGIN
IF @khoa=0
Insert Into @bangthongke
Select k.MaKhoa, k.TenKhoa, Count(s.MaSV)
From Khoa as k Inner Join Lop as l On k.MaKhoa = l.MaKhoa
Inner Join SinhVien as s On l.MaLop = s.MaLop
Group By k.makhoa, k.TenKhoa
ELSE
Insert Into @bangthongke
Select k.MaKhoa, k.TenKhoa, Count(s.MaSV)
From Khoa as k Inner Join Lop as l On k.MaKhoa = l.MaKhoa
Inner Join SinhVien as s On l.MaLop = s.MaLop
Where l.Khoa=@khoa
Group By k.makhoa, k.TenKhoa
RETURN /*Trả kết quả về cho hàm*/
END /* Select * From fn_TongSV(25) */