SQL Server/T-SQL Tutorial/Procedure Function/Create function — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:25, 26 мая 2010

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
Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Procedure_Function/Create_function&oldid=7062»