1
Bài 9: Hàm người dùng định nghĩa
-
Hàm trả về giá trị đơn - Scalar Functions
-
Hàm UDF trả về bảng:
–
Inline Table-valued Functions
–
Multistatement Table-valued Functions
2
Hàm trả về giá trị đơn - Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ]
parameter_data_type
[ = default ] } [ , n ] ])
RETURNS return_data_type
[ WITH <function_option> [ , n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
3
Hàm trả về giá trị vô hướng
CREATE TABLE [dbo].[tmpHoaDon]( [Ngay_hd] [datetime] NULL,
[KhachHang] [nvarchar](50) NULL, [Ma_hd] [nvarchar](50) NULL,
[GhiChu] [nvarchar](100) NULL ) ON [PRIMARY]
- Ta thấy dữ liệu trong Ngay_HD gồm có cả ngày tháng năm và giờ phút giây
Select Ngay_hd, KhachHang from tmpHoaDon where Ngay_HD=getDate() order by
Ngay_HD

5
Hàm UDF trả về bảng
Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ]
parameter_data_type
[ = default ] }
[ , n ] ])
RETURNS TABLE
[ WITH <function_option> [ , n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ][ ; ]
6
Hàm UDF trả về bảng (2)
Use khohang
Create Function dbo.danhSachHoaDon(@currentDay as Datetime)
Returns table
As return
(select * from tmpHoaDon where
dbo.getDay(ngay_hd)=dbo.getDay(@currentDay))
Sử dụng:
declare @d datetime
set @d=getdate()
select * from dbo.danhSachHoadon(@d)
Như vậy ta thấy có thể sử dụng hàm giống như view, tuy nhiên ta có
thể sử dụng các tham số của hàm để cho view linh họat hơn.
7
Hàm trả về bảng
Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name

Viết hàm fn_FindReports xác định (hiển thị) tất cả các nhân viên thuộc
quyền quản lý của @InputEmpID
CREATE FUNCTION dbo.fn_FindReports
( @InputEmpId nchar(5) )
RETURNS @retFindReports TABLE
(empid nchar(5) primary key, empname nvarchar(50) NOT NULL,
mgrid nchar(5), title nvarchar(30))
10
Xác định đường dẫn đầy đủ của thư mục
create table Directory (ID int identity, FolderName varchar(128),
Parent_ID int)
CREATE FUNCTION dbo.Path (@ID int)
RETURNS varchar(1000) AS
BEGIN
if @ID is null return ''
declare @s varchar(1000), @Parent_ID int
select @s=FolderName, @Parent_ID=Parent_ID
from Directory where ID=@ID
return dbo.Path(@Parent_ID)+@s+'/'
END
declare @st varchar(40)
set @st= dbo.Path(4) print @st
11
Xác định đường dẫn đầy đủ của thư mục (2)
CREATE FUNCTION dbo.Path_while (@ID int)
RETURNS varchar(8000) AS
BEGIN
declare @s varchar(8000), @Parent_ID int
select @s=FolderName, @Parent_ID=Parent_ID from Directory where
ID=@ID

if @vt>0 set @Ho =Left(@InputSt, @vt-1)
set @InputSt = LTrim(Right(@InputSt, Len(@InputSt) - @vt))
set @vt = charindex(' ',@InputSt)
Vi tri cua dau cach cuoi cung
declare @vtcuoi int, @Dem nvarchar(30);
set @vtcuoi=0; set @Dem=''
While @vt <> 0
Begin set @vtcuoi=@vt;
set @Dem =@Dem +' '+ Left(@InputSt, @vt-1)
set @InputSt = LTrim(Right(@InputSt, Len(@InputSt) - @vt))
set @vt = charindex(' ',@InputSt)
End
return @InputSt + ' ' + Rtrim(LTrim(@Dem))+' '+ Rtrim(LTrim(@Ho))
End
go
select dbo.fn_DaonguocHoDemTen('Tran Nguyen Vu Hoa')
14
Mã unicode của các ký tự tiếng Việt
use test
go
drop table dbo.BangMaUnicode
go
CREATE TABLE [dbo].BangMaUnicode (
[ID] [int] NULL ,Kitu [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS ,
MaHoa [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]
GO
15

set @InputSt= @InputSt+N'ếỆệFfGgHhIiÌìỈỉĨĩÍíỊịJjKkLlMmNnOoÒòỎỏÕõÓóỌọÔôỒồỔổỖỗỐốỘộƠơỜờỞởỠỡỚớỢợPp'
set @InputSt=@InputSt+N'QqRrSsTtUuÙùỦủŨũÚúỤụƯưỪừỬửỮữỨứỰựVvWwXxYyỲỳỶỷỸỹÝýỴỵZz'
declare @i int,@dodai int
set @dodai=len(@InputSt)
set @i=1
declare @c nvarchar(1)
while (@i<=@dodai)
begin
set @c=substring(@InputSt,@i,1)
insert into [TestCollation](id,Kitu) values(unicode(@c),@c)
Set @i=@i+1
end
select * from [TestCollation] order by Kitu COLLATE Vietnamese_CS_AI
- Viết hàm sắp xếp theo qui tắc tiếng Việt (Lecture5-SapXepXauKituTiengViet.sql)
17
Hiển thị danh sách tên các sản phẩm
Cho biết một tập hợp các ProductID: '9 12 27 37'
Cần viết một hàm để hiển thị tên của tất cả các sản phẩm này trong
bảng AdventureWorks.Production.Product
VD:
Use AdventureWorks
Get_product_names_iter '9 12 27 37'
18
Hiển thị danh sách tên các sản phẩm (3)
Create PROCEDURE Get_product_names_iter
@ids varchar(50) AS
Chọn các sản phẩm trong
SELECT P.Name, P.ProductID
FROM AdventureWorks.Production.Product P
JOIN int_list_to_table(@ids) i

Mỗi phần tử gồm có bảy thành phần, các thành phần viết cách nhau
một dấu ‘;’
Ví dụ:Mã Hóa Đơn, Người Nhập, Ghi Chú, Mã Sản Phẩm; Tên Sản
Phẩm; Số lượng; Đơn giá.
-
Viết thủ tục hoặc hàm phân tích xâu kí tự trên và lấy ra các hóa đơn
sau đó Insert vào bảng NhapHangHoa (MaHD, NguoiNhap,
GhiChu, MaSP, TenSP, Soluong,DonGia)
21
Bài tập 2
-
Cho một file dữ liệu Input.txt gồm các xâu kí tự như trong bài tập 1
-
Giả sử có hai bảng: DMSanPham(MaSP, TenSP) và bảng
NhapHangHoa (MaHD, NguoiNhap, GhiChu, MaSP, Soluong,
DonGia)
-
Hãy viết thủ tục hoặc hàm phân tích từng xâu kí tự trong file:
-
Nếu MaSP chưa có trong DMSanPham(MaSP, TenSP) thì ta
chèn MaSP và TenSP vào bảng này.
-
Chèn các giá trị MaHD, NguoiNhap, GhiChu, MaSP, SoLuong,
DonGia lấy được từ xâu vào bảng NhapHangHoa.
22
Bài tập 3
-
Cho một file dữ liệu Input.txt như trong bài tập 3
-
Giả sử có ba bảng: DMSanPham(MaSP, TenSP), bảng