|
su.dbms
- SU.DBMS ----------------------------------------------------------------------
From : Tolik Tentser 2:5020/400 30 Jun 2001 15:05:34
To : All
Subject : Re: Только не пинайте
--------------------------------------------------------------------------------
Hi, Igor Trofimov!
В чреве акулы, пойманной Sat, 30 Jun 2001 10:02:58 +0000 (UTC),
дети капитана Гранта нашли письмо на тему 'Re: Только не пинайте':
>И чего - действительно удобоваримые UDF? Как в IB? Или надо недельку
>посидеть, прежде чем первая заработает? :)
Зачем недельку ?
A. Scalar-valued user-defined function that calculates the ISO week
In this example, a user-defined function, ISOweek, takes a date
argument and calculates the ISO week number. For this function to
calculate properly, SET DATEFIRST 1 must be invoked before the
function is called.
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
Here is the function call. Notice that DATEFIRST is set to 1.
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
Here is the result set.
ISO Week
----------------
52
B. Inline table-valued function
This example returns an inline table-valued function.
USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
C. Multi-statement table-valued function
Given a table that represents a hierarchical relationship:
CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)
The table-valued function fn_FindReports(InEmpID), which -- given an
Employee ID -- returns a table corresponding to all the employees that
report to the given employee directly or indirectly. This logic is not
expressible in a single query and is a good candidate for implementing
as a user-defined function.
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to
given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO
-- Example invocation
SELECT *
FROM fn_FindReports('11234')
Bye ...
Тенцер А.Л.
tolik@katren.nsk.ru
ICQ 15925834
--- ifmail v.2.15dev5
* Origin: AO Katren (2:5020/400)
Вернуться к списку тем, сортированных по: возрастание даты уменьшение даты тема автор
|
