SQL Server/T-SQL Tutorial/Procedure Function/Recursive function

Материал из SQL эксперт
Версия от 13:25, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Implementing the fibonacci2() User-Defined Function with a Loop

   <source lang="sql">

4> CREATE FUNCTION dbo.fibonacci2( 5> @n AS int 6> ) 7> RETURNS int 8> AS 9> BEGIN 10> IF @n < 0 11> RETURN NULL 12> ELSE 13> IF @n in (0, 1) 14> RETURN @n 15> ELSE 16> BEGIN 17> DECLARE 18> @i AS int, 19> @f AS int 20> SET @i = @n 21> SET @f = 0 22> WHILE @i > 0 23> BEGIN 24> SET @f = @f + @i 25> SET @i = @i - 1 26> END -- loop while @i > 0 27> END -- if @n > 0 28> RETURN @f 29> END -- function 30> GO 1> --Invoking the Loop fibonacci2() User-Defined Function 2> SELECT dbo.fibonacci2(32) -- succeeds 3> SELECT dbo.fibonacci2(33) -- succeeds 4> 5> drop function dbo.fibonacci2 6> GO


       528

(1 rows affected)


       561

(1 rows affected)</source>


Implementing the fibonacci() User-Defined Function with Recursion

   <source lang="sql">

4> CREATE FUNCTION dbo.fibonacci( 5> @n AS int 6> ) 7> RETURNS int 8> AS 9> BEGIN 10> RETURN CASE 11> WHEN @n > 1 THEN @n + dbo.fibonacci(@n - 1) --recursive invocation 12> WHEN @n IN (0, 1) THEN @n 13> ELSE NULL 14> END 15> END 16> GO 1> 2> --Invoking the Recursive fibonacci() User-Defined Function 3> 4> SELECT dbo.fibonacci(2) -- succeeds 5> GO


         3

(1 rows affected) 1> 2> drop function dbo.fibonacci 3> GO</source>


iterative solution does not have the restriction 32 nesting levels

   <source lang="sql">

6> CREATE PROC factorial2 @param1 int, @answer NUMERIC(38,0) OUTPUT 7> AS 8> DECLARE @counter int 9> IF (@param1 < 0 OR @param1 > 33) 10> BEGIN 11> RAISERROR ("Illegal Parameter Value. Must be between 0 and 33", 12> 16, -1) 13> RETURN -1 14> END 15> 16> SET @counter=1 SET @answer=1 17> 18> WHILE (@counter < @param1 AND @param1 <> 0 ) 19> BEGIN 20> SET @answer=@answer * (@counter + 1) 21> SET @counter=@counter + 1 22> END 23> 24> RETURN 25> GO 1> 2> DECLARE @answer numeric(38, 0), @param int 3> SET @param=0 4> WHILE (@param <= 32) 5> BEGIN 6> EXEC factorial2 @param, @answer OUTPUT 7> PRINT CONVERT(varchar(50), @param) + "! = " 8> + CONVERT(varchar(50), @answer) 9> SET @param=@param + 1 10> END 11> GO 0! = 1 1! = 1 2! = 2 3! = 6 4! = 24 5! = 120 6! = 720 7! = 5040 8! = 40320 9! = 362880 10! = 3628800 11! = 39916800 12! = 479001600 13! = 6227020800 14! = 87178291200 15! = 1307674368000 16! = 20922789888000 17! = 355687428096000 18! = 6402373705728000 19! = 121645100408832000 20! = 2432902008176640000 21! = 51090942171709440000 22! = 1124000727777607680000 23! = 25852016738884976640000 24! = 620448401733239439360000 25! = 15511210043330985984000000 26! = 403291461126605635584000000 27! = 10888869450418352160768000000 28! = 304888344611713860501504000000 29! = 8841761993739701954543616000000 30! = 265252859812191058636308480000000 31! = 8222838654177922817725562880000000 32! = 263130836933693530167218012160000000 1> 2> drop PROC factorial2; 3> GO</source>


Nesting Stored Procedures

   <source lang="sql">

A stored procedure cannot be nested more than 32 levels deep. You can test how far down you are by checking the value of the @@NESTLEVEL function. 25> CREATE TABLE employee( 26> id INTEGER NOT NULL PRIMARY KEY, 27> first_name VARCHAR(10), 28> last_name VARCHAR(10), 29> salary DECIMAL(10,2), 30> start_Date DATETIME, 31> region VARCHAR(10), 32> city VARCHAR(20), 33> managerid INTEGER 34> ); 35> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid


---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> CREATE PROC usp_FindBoss( 5> @EmployeeID int 6> ) 7> AS 8> DECLARE 9> @ReportsTo int 10> SELECT 11> @ReportsTo = managerid 12> FROM 13> Employee 14> WHERE 15> Id = @EmployeeID 16> IF @ReportsTo IS NOT NULL AND @@NESTLEVEL <= 32 17> BEGIN 18> SELECT 19> @EmployeeID AS Employee, 20> @ReportsTo AS Manager 21> EXEC usp_FindBoss 22> @ReportsTo 23> END 24> GO 1> 2> drop procedure usp_FindBoss 3> GO 1> 2> 3> drop table employee; 4> GO</source>


Recursively call itself

   <source lang="sql">

2> CREATE PROC factorial @param1 decimal(38,0), @answer decimal(38,0) output 3> AS 4> DECLARE @one_less decimal(38,0), @status int 5> 6> IF (@param1 < 0 OR @param1 > 32) 7> BEGIN 8> -- Illegal parameter value. Must be between 0 and 32. 9> RETURN -1 10> END 11> 12> IF (@param1=0 or @param1=1) 13> SET @answer=1 14> ELSE 15> BEGIN 16> SET @one_less=@param1 - 1 17> EXEC @status=factorial @one_less, @answer output 18> 19> IF (@status= -1) 20> BEGIN 21> RETURN -1 22> END 23> 24> SET @answer=@answer * @param1 25> 26> IF (@@ERROR <> 0) 27> RETURN -1 28> END 29> 30> RETURN 0 31> GO 1> 2> 3> DECLARE @answer decimal(38,0), @param int 4> SET @param=0 5> WHILE (@param <= 32) BEGIN 6> EXEC factorial @param, @answer output 7> IF (@answer= -1) 8> BEGIN 9> RAISERROR("Error executing factorial procedure.", 16, -1) 10> RETURN 11> END 12> PRINT CONVERT(varchar, @param) + "! = " + CONVERT(varchar(50), @answer) 13> SET @param=@param + 1 14> END 15> GO 0! = 1 1! = 1 2! = 2 3! = 6 4! = 24 5! = 120 6! = 720 7! = 5040 8! = 40320 9! = 362880 10! = 3628800 11! = 39916800 12! = 479001600 13! = 6227020800 14! = 87178291200 15! = 1307674368000 16! = 20922789888000 17! = 355687428096000 18! = 6402373705728000 19! = 121645100408832000 20! = 2432902008176640000 21! = 51090942171709440000 22! = 1124000727777607680000 23! = 25852016738884976640000 24! = 620448401733239439360000 25! = 15511210043330985984000000 26! = 403291461126605635584000000 27! = 10888869450418352160768000000 28! = 304888344611713860501504000000 29! = 8841761993739701954543616000000 30! = 265252859812191058636308480000000 31! = 8222838654177922817725562880000000 32! = 263130836933693530167218012160000000 1> 2> drop PROC factorial; 3> GO 1></source>