- Potrebna nam je funkcija koja ce vratiti sve nivoe hijerarhije ispod bilo kog EmployeeID (i prema tome MenagerId) koji damo.
1) treba da nađemo sve ljude koji podnose izveštaj menadzeru kojeg izaberemo.
2) za svaku osobu iz koraka 1 treba da znamo ko se njoj javlja.
3) treba ponajljati koras 2 dok ne bude vise podčinjenih.
Ja ne razumem redosled izvrsavanja koda. Tek sam poceo da ucim funkcije, a do sada nisam ozbiljnije programirao. Ucim polako, pa mi nemojte zameriti zbog mozda glupih pitanja.
Prvo da kreiramo tabelu:
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Photo] [image] NULL,
[Notes] [ntext] NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [CK_Birthdate] CHECK (([BirthDate] < getdate()))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]
GO
************************************************
zatim sledi pisanje funkcije
*******************
Use Northwind
GO
CREATE FUNCTION dbo.fnGetReports(@EmployeeID AS int) /* ovaj privi deo mi je jasan*/
RETURNS @Reports TABLE
(
EmployeeID int NOT NULL,
ManagerID int NULL
)
AS
BEGIN
/* Since we’ll need to call this function recursively - that is once for each
** reporting employee (to make sure that they don’t have reports of their
** own), we need a holding variable to keep track of which employee we’re
** currently working on. */
DECLARE @Employee AS int /* ne razumem zasto je deklarisana "employee" */
/* This inserts the current employee into our working table. The significance
** here is that we need the first record as something of a primer due to the
** recursive nature of the function - this is how we get it. */
INSERT INTO @Reports
SELECT EmployeeID, ManagerID /* odakle od jednom "ManagerID", kada ne postoji u tabeli */
FROM Employees
WHERE EmployeeID = @EmployeeID
/* Now we also need a primer for the recursive calls we’re getting ready to
** start making to this function. This would probably be better done with a
** cursor, but we haven’t gotten to that chapter yet, so.... */
SELECT @Employee = MIN(EmployeeID) /* zasto MIN(employeeID)*/
FROM Employees
WHERE ManagerID = @EmployeeID
/* This next part would probably be better done with a cursor but we haven’t
** gotten to that chapter yet, so we’ll fake it. Notice the recursive call
** to our function! */
WHILE @Employee IS NOT NULL /* ovaj deo u opste ne razumem. Petlja ko petlja, ali zasto 2 puta iskaz select? */
BEGIN /* u zadatku kaze da treba ponavljati korak dva dok ne bude vise podcinjenih */
INSERT INTO @Reports
SELECT *
FROM fnGetReports(@Employee) /* ovde se poziva funkcija */
SELECT @Employee = MIN(EmployeeID) /* sta bi mu znacio ovaj deo koda*/
FROM Employees
WHERE EmployeeID > @Employee AND ManagerID = @EmployeeID;
END
RETURN;
END
GO
da li mi neko moze objasniti ovaj primer rekurzije?
e sada poziv funkcije.
SELECT *
FROM fnGetReposrts(2)
[Ovu poruku je menjao nemanjal dana 04.09.2010. u 18:19 GMT+1]
Nemsho