SQL Server/T-SQL Tutorial/Procedure Function/Recursive function
Содержание
Implementing the fibonacci2() User-Defined Function with a Loop
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)
Implementing the fibonacci() User-Defined Function with Recursion
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
iterative solution does not have the restriction 32 nesting levels
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
Nesting Stored Procedures
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
Recursively call itself
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>