SQL用户定义的函数

“自定义函数”是我们平常的说法，而“用户定义的函数”是 SQL Server 中书面的说法。

SQL Server 2000 允许用户创建自定义函数，自定义函数可以有返回值。

自定义函数分为：标量值函数或表值函数

• 如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。
• 如果 RETURNS 子句指定 TABLE，则函数为表值函数。

表值函数又可分为：内嵌表值函数（行内函数）或多语句函数

• 如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
• 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数。

标量值函数示例（自连接--另外一张表字段相同和原表相比）

CREATE FUNCTION UpdateDate
   (@prodate datetime)
RETURNS int 
AS
BEGIN
    declare @i int
    select @i=DATEDIFF(second,'2006-08-28', @prodate)
    while  exists (select * FROM Supply where ById=@i) 
    begin
        select @i = @i+1
    end
       RETURN @i
END 

内嵌表值函数示例

CREATE FUNCTION dbo.Foo

(@cid as char(4))
RETURNS TABLE
AS 
    return select id, title from msgs where id =cid --不能用BEGIN/END快 只能指定一个RETURN和一个select 语句（带参数的视图）

多语句表值函数示例--当需要一段程序返回表，而且用单个查询无法实现，只能用多条语句

CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
);

INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(14, 9, 'Didi', $1500.00);

CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
GO

create function myfun
(@mgrid int)
returns @sub Table
(
  empid   INT NOT NULL PRIMARY KEY NONCLUSTERED,
  mgrid   INT NULL,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  lvl     INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)
)
as
begin

declare @lvl int
set @lvl=0

insert into @sub(empid,mgrid,empname,salary,lvl) select empid,mgrid,empname,salary,@lvl from dbo.Employees where empid = @mgrid
while(@@rowcount>0)
begin

set @lvl =@lvl+1
insert into @sub(empid,mgrid,empname,salary,lvl) select e.empid,e.mgrid,e.empname,e.salary,@lvl from dbo.Employees e,@sub s 
where s.lvl = @lvl -1 and  s.empid = e.mgrid 

end
return 
end

注意其 RETURNS 部分。

多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。

• 赋值语句。
• 控制流语句。
• DECLARE 语句，该语句定义函数局部的数据变量和游标。
• SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
• 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。
• INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。
• EXECUTE 语句调用扩展存储过程。