Các Loại Hàm Người Dùng Và Ngôn Ngữ Dll Cho Hàm Người Dùng

Bước 3: Chọn nút để kiểm tra câu lệnh T-SQL.

Bước 4: Chọn nút để thực hiện câu lệnh thực thi thủ tục lưu trữ.

Chú ý: Khi SQL Server thực thi một câu lệnh SQL hay một thủ tục lưu trữ có tham số thì các giá trị của tham số phải được định dạng đúng kiểu dữ liệu. Khi một ứng dụng nào đó viết bằng Visual Basic, Visual Basic.Net, ASP, ASP.Net, C#, JSP,..thì để gọi một thủ tục hay một phát biểu SQL trong SQL Server, giá trị từ các form nhập liệu chuyển vào cho SQL Server thực thi phải là chuỗi SQL đúng cú pháp. Nếu câu SQL sai thì SQL Server sẽ thông báo lỗi.

3) Thay đổi thủ tục lưu trữ

- Dùng công cụ MS: Chọn tên thủ tục/Chọn chuột phải/Chọn Modify.

- Dùng câu lệnh T-SQL: Dùng câu lệnh Alter Procedure

4) Xóa thủ tục lưu trữ

- Dùng công cụ MS: Chọn tên thủ tục/Chọn chuột phải/Chọn Delete/Chọn OK.

- Dùng câu lệnh T-SQL: Dùng câu lệnh Drop Procedure

Có thể bạn quan tâm!

Xem toàn bộ 323 trang tài liệu này.

4.2. Hàm người dùng (User Defined Functions)

4.2.1. Khái niệm

Hàm do người dùng định nghĩa (user defined functions-UDFs) là một đối tượng mới được bổ sung vào từ phiên bản SQL Server 2000. UDFs mang đầy đủ các ý nghĩa và tính chất của một hàm như đa số các ngôn ngữ lập trình khác, nghĩa là một UDFs là một chương trình con đảm trách một xử lý nào đó với đặc tính là sẽ nhận các tham số đầu vào và trả về một giá trị kết quả xử lý tính toán được dựa trên các tham số đầu vào đã nhận.

UDFs không những mang đầy đủ tính chất của một hàm mà UDFs còn là sự kết hợp của hai đối tượng View và Stored Procedure. Hơn thế nữa, nó còn có những tính năng sử dụng mà View và Stored Procedure không có được, tức nó khắc phục một số hạn chế của View và Stored Procedure. Ví dụ, Stored Procedure không thể là một phần của câu lệnh SELECT như UDFs, View không hỗ trợ sự đệ quy trong khi với UDFs thì hỗ trợ đệ quy.

Không chỉ UDFs mà tất cả các hàm có sẵn trong các phiên bản SQL Server có thể phân ra thành hai nhóm: hàm xác định (deterministic) và hàm không xác định (non-deterministic). Các hàm xác định sẽ luôn luôn trả về cùng giá trị nếu giá trị các tham số được truyền vào là như nhau. Các hàm không xác định có thể tạo ra các kết quả khác biệt tại mỗi thời điểm chúng được gọi thực hiện, ngay cả khi giá trị các tham số được cung cấp vẫn không thay đổi.

Hàm getdate() là một ví dụ cho cả hai hàm vô hướng (scalar) và hàm không xác định (non-deterministic). Nếu ta gọi thực hiện nó hai lần với cùng một dòng lệnh gọi, bạn sẽ nhận được hai kết quả khác nhau và chỉ có duy nhất một giá trị cho mỗi lần gọi.

4.2.2. Các loại hàm người dùng và ngôn ngữ DLL cho hàm người dùng

Hàm người dùng được chia làm 3 loại:

- Hàm vô hướng (Scalar)

- Hàm nội tuyến (Inline table-valued)

- Hàm đa lệnh (Multi-statement table-valued)

1) Tạo hàm người dùng

a) Hàm vô hướng

Hàm vô hướng được sử dụng để trả về duy nhất một giá trị dựa trên một số các tham số truyền vào. Nó là hàm xác định.

Cú pháp:

CREATE FUNCTION <Scalar_Function_Name> (

<@Parameter_Name1> <Datatype1>,

…

<@Parameter_NameN> <DatatypeN>

)

RETURNS <Function_Data_Type> AS

BEGIN

DECLARE <@ResultVar> <Function_Data_Type> SELECT <@ResultVar> =

<@Parameter_Name1,…,@Parameter_NameN> RETURN <@ResultVar>

END

Trong đó:

- Scalar_Function_Name: Tên hàm vô hướng

- @Parameter_Name1: Tham số nhập của hàm

- Datatype1: Kiểu dữ liệu

- @ResultVar: Biến lưu trữ kết quả trả về hàm

- Function_Data_Type: Kiểu dữ liệu trả về của hàm

Ví dụ 1: Xây dựng hàm trả về thứ của một ngày trong cơ sở dữ liệu QLTS. CREATE FUNCTION Scalar_Date(@bngay date )

RETURNS nvarchar(10) AS

BEGIN

declare @st nvarchar(10)

select @st=case datepart(dw,@bngay)

when 1 then 'chu nhat' when 2 then 'thu hai' when 3 then 'thu ba' when 4 then 'thu tu' when 5 then 'thu nam' when 6 then 'thu sau' else 'thu bay'

end

return (@st)

END

Ví dụ 2: Xây dựng hàm chuyển điểm số về điểm chữ. Biết rằng A (8,5 - 10), B (7,0 - 8,4), C (5,5 - 6,9), D (4,0 - 5,4), F (dưới 4,0) trong cơ sở dữ liệu QLD.

CREATE FUNCTION Scalar_Convert(@diemso float) RETURNS char(1)

AS BEGIN

DECLARE @diemchu char(1)

If @diemso>=8.5 SELECT @diemchu='A' else If @diemso>=7 SELECT @diemchu='B' else If @diemso>=5.5 SELECT @diemchu='C' else If @diemso>=4 SELECT @diemchu='D' else SELECT @diemchu='F'

RETURN @diemchu

END

Ví dụ 3: Xây dựng hàm chuẩn một chuỗi trong cơ sở dữ liệu QLMBH, theo quy ước cắt bỏ các khoảng trắng đầu và cuối chuỗi, cắt bỏ các khoảng trắng thừa sao cho giữa hai từ chỉ cách nhau bởi duy nhất một khoảng trắng, ký tự đầu tiên của từ là ký tự hoa, các ký tự còn lại là chữ thường.

CREATE FUNCTION Scalar_Chuanhoa(@st varchar(50)) RETURNS varchar(50)

BEGIN

DECLARE @chuoichuan varchar(50)

DECLARE @i int

declare @space1 nvarchar(30) declare @space2 nvarchar(30) set @space1=char(32)

set @space2=char(32)+char(32)

while CharIndex(@space2,@st)>0

set @st=replace(@st,@space2,@space1)

Set @st=ltrim(rtrim(@st)) Set @st=Lower(@st)

Set @st=@space1+@st

set @i=CharIndex(@space1,@st)

while @i<>0 begin

set @st= STUFF(@st,@i+1,1,Upper(substring(@st,@i+1,1))) set @i=charindex(@space1,@st,@i+1)

end

set @st=STUFF(@st,1,1,null) SELECT @chuoichuan=@st RETURN @chuoichuan END

b) Hàm nội tuyến

Hàm nội tuyến trả về một bảng dựa trên một câu lệnh SQL duy nhất định nghĩa các dòng và các trường trả về. Nó là hàm không xác định. Với loại hàm này, ta chỉ có thể thực hiện bên trong nó câu lệnh SELECT rò ràng không quá phức tạp. Hàm nội tuyến thực chất được dùng để thay thế cho các đối tượng VIEW, bởi vì nó khắc phục được nhược điểm không có tham số của VIEW. Ta có thể coi hàm nội tuyến giống như là một VIEW có tham số.

Cú pháp:

CREATE FUNCTION <Inline_Function_Name> (

<@Parameter_Name1> <Datatype1>,

…

<@Parameter_NameN> <DatatypeN>

)

RETURNS TABLE AS

RETURN (

SELECT 0

)

Trong đó kiểu trả về của hàm phải được chỉ định bởi mệnh đề RETURNS TABLE. Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT, không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm.

Ví dụ 1: Xây dựng hàm trả về các thông tin gồm: Số báo danh, họ tên, ngày sinh của những thí sinh có quê cho trước.

CREATE FUNCTION Inline_VD1(@bdc nvarchar(15)) RETURNS TABLE

AS RETURN (

SELECT Dbo.Thisinh.sobd, Dbo.Thisinh.hoten, Dbo.Thisinh.ngaysinh,

from Dbo.Thisinh

Where Dbo.Thisinh.quequan=@bdc

)

Ví dụ 2: Xây dựng hàm trả về các thông tin gồm: Số báo danh, họ tên, ngày sinh, tên ngành, tổng điểm 3 môn và điểm cộng của những thí sinh đã dự thi với tên ngành cho trước.

CREATE FUNCTION [dbo].[Inline_VD2](@btenn nvarchar(50)) RETURNS TABLE

AS RETURN (

SELECT Dbo.Thisinh.sbd, Dbo.Thisinh.hoten, Dbo.Thisinh.ngaysinh,Dbo.Nganh.Tenn,

Dbo.Thisinh.dtoan+Dbo.Thisinh.dhoa+Dbo.Thisinh.dly+Dbo.Uutien.dc as td from Dbo.Thisinh inner join dbo.nganh on Dbo.Thisinh.man=dbo.nganh.man inner join dbo.uutien on Dbo.Thisinh.maut=dbo.uutien.maut

Where ltrim(rtrim(Dbo.nganh.tenn))=ltrim(rtrim(@btenn))

)

c) Hàm đa lệnh

Hàm đa lệnh cũng trả về kết quả là một bảng nhưng có thể dựa trên nhiều câu lệnh SQL. Nó là hàm không xác định. Hàm đa lệnh rất giống các thủ tục lưu trữ. Nó cho phép thực hiện các câu lệnh SELECT phức tạp. Hơn nữa, nó còn cho phép thực hiện các câu lệnh logic khác như UPDATE, INSERT INTO, … Đồng thời, ta có thể thiết lập cấu trúc trong cặp dấu ngoặc đơn ngay sau câu lệnh RETURNS. Loại này sẽ luôn trả về một biến table (chỉ duy nhất một biến table).

Cú pháp:

CREATE FUNCTION <Table_Function_Name> (

<@Parameter_Name1> <Datatype1>,

…

<@Parameter_NameN> <DatatypeN>

)

RETURNS <@Table_Variable_Name> TABLE

(

<Column_1> <Data_Type_For_Column1>,

…,

<Column_N> <Data_Type_For_ColumnN>

) AS

BEGIN

…

Insert into <@Table_Variable_Name>[(<Column_1>,…<Column_N>)]

… RETURN END

Trong đó:

- Table_Function_Name:

- @Table_Variable_Name: Biến bảng

- Column_1: Tên trường

- Data_Type_For_Column1: Kiểu dữ liệu của trường

Ta thấy rằng, sau từ khóa RETURNS là một biến bảng được định nghĩa. Và sau từ khóa RETURN ở cuối hàm không có tham số nào đi kèm. Ta chú ý rằng danh sách các trường đưa ra sau từ khóa TABLE phải tương ứng với các trường trong câu lệnh Select ..From…Where trong thân hàm.

Hạn chế khi sử dụng hàm đa lệnh:

- Không thể gọi một thủ tục lưu trữ từ các câu lệnh bên trong nó.

- Không thể sử dụng các hàm loại không xác định được xây dựng sẵn trong SQL Server. Ví dụ Getdate, Rand, …

- Không thể sử dụng được RAISERROR và @@ERROR

- Không thể được sử dụng để sửa đổi thông tin trên bảng cơ sở.

Ví dụ 1: Xây dựng hàm trả về các thông tin gồm: Số báo danh, họ tên, tổng điểm ba môn của những thí sinh có tổng điểm cao nhất của mỗi ngành với mã ngành cho trước.

CREATE FUNCTION Table_VD1(@man nvarchar(20)) RETURNS @ThuKhoaN

TABLE (

sobaodanh nvarchar(10), hoten nvarchar(50),

man nvarchar(20), tongdiem float

) AS

BEGIN

declare @diemmax float set @diemmax =

(select Max(dtoan+dly+dhoa) from thisinh where man=@man)

insert into @thukhoan

select sbd, hoten,man,dtoan+dly+dhoa as tdiem from thisinh

where dtoan+dly+dhoa=@diemmax and man=@man

RETURN

END

Ví dụ 2: Xây dựng hàm trả về các thông tin gồm: Mã mặt hàng, tên mặt hàng, mua bán, đơn giá của mỗi mặt hàng trong một năm. Biết rằng nếu với mỗi mặt hàng mua thì đơn giá tăng lên x%, còn với mỗi mặt hàng bán thì đơn giá tăng lên y%.

CREATE FUNCTION Table_VD2(@nam int, @x int, @y int) RETURNS @DSMH

TABLE (

Ma nvarchar(10), Ten nvarchar(50), MB bit,

DG float

) AS

BEGIN

insert into @DSMH(Ma, Ten,MB, DG)

select mathang.mamh, mathang.tenmh, muaban.muaban,muaban.dongia from mathang inner join muaban on mathang.mamh=muaban.mamh where year(ngaymb)=@nam

update @DSMH set DG= Case MB

when 0 then DG+DG*@x/100 when 1 then DG+DG*@y/100 end

RETURN END

2) Sửa hàm người dùng

a) Hàm vô hướng Cú pháp:

ALTER FUNCTION <Scalar_Function_Name> (

<@Parameter_Name1> <Datatype1>,

…

<@Parameter_NameN> <DatatypeN>