SQL Server/T-SQL Tutorial/Procedure Function/Create function
Содержание
- 1 A statement that creates a scalar-valued function
- 2 A statement that creates a simple table-valued function
- 3 Creating a User-Defined Function
- 4 Scalar functions are functions that return a single value.
- 5 Syntax for Creating Inline Table-Valued Functions
- 6 Syntax for Creating Multistatement Table-Valued Functions
A statement that creates a scalar-valued function
26> create table Bankers(
27> BankerID Integer,
28> BankerName VARCHAR(20),
29> BankerContactLName VARCHAR(20),
30> BankerContactFName VARCHAR(20),
31> BankerCity VARCHAR(20),
32> BankerState VARCHAR(20),
33> BankerZipCode VARCHAR(20),
34> BankerPhone VARCHAR(20)
35> )
36> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> CREATE FUNCTION fnBankerID
4> (@BankerName varchar(50))
5> RETURNS int
6> BEGIN
7> RETURN (SELECT BankerID FROM Bankers WHERE BankerName = @BankerName)
8> END
9> GO
1>
2> drop FUNCTION fnBankerID;
3> GO
1> drop table Bankers;
2> GO
1>
A statement that creates a simple table-valued function
17> create table Billings (
18> BankerID INTEGER,
19> BillingNumber INTEGER,
20> BillingDate datetime,
21> BillingTotal INTEGER,
22> TermsID INTEGER,
23> BillingDueDate datetime ,
24> PaymentTotal INTEGER,
25> CreditTotal INTEGER
26>
27> );
28> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> create table Bankers(
4> BankerID Integer,
5> BankerName VARCHAR(20),
6> BankerContactLName VARCHAR(20),
7> BankerContactFName VARCHAR(20),
8> BankerCity VARCHAR(20),
9> BankerState VARCHAR(20),
10> BankerZipCode VARCHAR(20),
11> BankerPhone VARCHAR(20)
12> )
13> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> CREATE FUNCTION fnTopBankersDue
4> (@CutOff money = 0)
5> RETURNS table
6> RETURN
7> (SELECT BankerName, SUM(BillingTotal) AS TotalDue
8> FROM Bankers JOIN Billings ON Bankers.BankerID = Billings.BankerID
9> WHERE BillingTotal - CreditTotal - PaymentTotal > 0
10> GROUP BY BankerName
11> HAVING SUM(BillingTotal) >= @CutOff)
12> GO
1>
2>
3>
4> --A SELECT statement that invokes the function
5>
6> SELECT * FROM dbo.fnTopBankersDue(5000)
7>
8> drop FUNCTION fnTopBankersDue;
9> GO
BankerName TotalDue
-------------------- -----------
(0 rows affected)
1>
2>
3> drop table Bankers;
4> GO
1>
2> drop table Billings;
3> GO
Creating a User-Defined Function
5> CREATE Function dbo.fn_LastOfMonth(@TheDate DateTime)
6> Returns DateTime
7> AS
8> BEGIN
9> DECLARE @FirstOfMonth DateTime
10> DECLARE @DaysInMonth Int
11> DECLARE @RetDate DateTime
12> SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm,0,@TheDate), 0)
13> SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth, DATEADD(m, 1, @FirstOfMonth))
14> RETURN DATEADD(d, @DaysInMonth - 1, @FirstOfMonth)
15> END
16> GO
1>
2> drop function dbo.fn_LastOfMonth;
3> GO
1>
Scalar functions are functions that return a single value.
Scalar functions can accept zero or more input parameters.
Syntax for Creating Scalar Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH { ENCRYPTION | SCHEMABINDING } [,...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Syntax for Creating Inline Table-Valued Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH { ENCRYPTION | SCHEMABINDING } [ ,...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
Syntax for Creating Multistatement Table-Valued Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type
[ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE
( { column_definition | table_constraint } [ ,...n ] )
[ WITH { ENCRYPTION | SCHEMABINDING } [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END