[ nemanjal @ 04.09.2010. 17:00 ] @
Imam problem sa zadatkom. Tačnije, ne razumem ga. Radi se u MS sql serveru 2008,a zadatak se radi na osnovu baze "Northwind". Zadatak bi se odnosio na tabelu Employees koja bi pozivala sama sebe (klasičan primer rekurzije), a glasio bi recimo ovako.
- 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]
[ Zidar @ 07.09.2010. 19:02 ] @
Mije ni cudo sto ne raumes, kad je u zadatku sve dato tako da te zbuni maksimalno.

Prvo, 'Osoba Aca reports to osoba Pera' ne znaci "Aca podonsi izvestaj Peri". Pravo znacenje je "Pera je Acin menadzer (nadaredjeni)".

U hijerarhijama, jedna osoba ima tacno jednog direktnog menadzera. Menadzer moze imati svog menadzera, ovaj svog i tako na gore, sve do vrhovnog menadzera. Vrhovni menadzer nema menadzera.

Kolone ( EmployeeID, ReportSTo) zajendo sa [FK_Employees_Employees] pokazuju ko je kome nadredjen. Ovako bi mogla da izgleda hijerarhija u zamisljenom primeru:

Na primer, neka je glavni menadzer Pera. Pera ima dva zamenika, zovu se Laza i Zoka. Laza ima dva radnika ispod sebe, Miku i Djoku. Zoka je nadredjena Veci i Ceci. veca je radnik, a Ceca je poslovodja i njoj su podredejni Mica i Bilja. Ovbako to moze da se nacrta:
Code:

Pera
  Laza
     Mika
     Djoka
  Zoka
     Veca
     Ceca
         Mica
         Bilja

Pera je svima menadzer, Laza je manadzer Miki i Djoki. Zoka je menadzer Ceci, Veci, Mici i Bilji.
Ceca je menadzer Mici i Bilji.

Treba razlikovati pojmove 'neposredan menadzer' i 'menadzer'. Neposredan mandzer je onaj direktno iznad zaposlenog. Na primer, za Micu i Bilju neposredni menadzer je Ceca. Za Zoku i Lazu neposredni menadzer je Pera. Zoka je menadzer Bilji ali ne neposredan, nego preko Cece.

Moramo da se vratimo na pocetak, da bi se sve razjasnilo kako treba. Zaboravi primere u Books On Line i slicno, ovo mora da se razume.

Ako oznacimo zaposlene brojevima, onda bi mogli da kazemo ovo:

Code:

IF Object_ID('Zaposleni') IS NOT NULL DROP TABLE Zaposleni
CREATE TABLE Zaposleni 
(Employee varchar(20)
, EmployeeID int PRIMARY KEY
, ReportsTo int
)
GO
ALTER TABLE Zaposleni 
ADD CONSTRAINT FK_KojeKomeManager 
FOREIGN KEY(ReportsTo)
REFERENCES Zaposleni (EmployeeID)
GO

INSERT INTO Zaposleni VALUES ('Pera' , 1, NULL)
INSERT INTO Zaposleni VALUES ('Laza', 2 , 1)
INSERT INTO Zaposleni VALUES ('Mika', 3 , 2)
INSERT INTO Zaposleni VALUES ('Djoka', 4 , 2)
INSERT INTO Zaposleni VALUES ('Zoka', 5 , 1)
INSERT INTO Zaposleni VALUES ('Ceca', 6 , 5)
INSERT INTO Zaposleni VALUES ('Veca', 7 , 5)
INSERT INTO Zaposleni VALUES ('Mica', 8 , 6)
INSERT INTO Zaposleni VALUES ('Bilja', 9 , 6)
GO

Ova prica se zasniva na pretpostavci da u tabeli Zaposleni moze postojati tacno jedan vrhovni manager. Nismo postavili CONSTRAINT koji to garantuje. To je interesantno podpitanje. Ima jednostavno resenje, ali se ne moze naci u knjigama, iz nepoznatih razloga. O tome drugi put, sad nazad na posao.

Ovo je dakle slika:
Code:

SELECT * FROM Zaposleni
GO
Employee    EmployeeID   ReportsTo
---------- ----------- -----------
Pera                 1        NULL
Laza                 2           1
Mika                 3           2
Djoka                4           2
Zoka                 5           1
Ceca                 6           5
Veca                 7           5
Mica                 8           6
Bilja                9           6

(9 row(s) affected)


Relativno je lako odgovoriti na sledeca tri pitanja:

1) Ja sam manager. Ko su moji direktni podredjeni?
2) Ja sam employee. Ko je moj diraktan manager?
3) Ko je vrhovni menager?
3) Ko su menageri (imaju pod sobom bar jednog zaposlenog)?
4) Ko su radnici (nisu nikome menageri)

Ovako idu odgovori:
Code:

-- 1) Ja sam manager. Ko su moji direktni podredjeni?
-- Odgovor: oni koji u ReportsTo imaju moj EmployeeID
DECLARE @mangerID int
SET @mangerID = 5
;
SELECT 
*
FROM Zaposleni
WHERE ReportsTo IN (SELECT EmployeeID 
                    FROM Zaposleni 
                    WHERE EmployeeID = @mangerID)
;
Employee    EmployeeID   ReportsTo
---------- ----------- -----------
Ceca                 6           5
Veca                 7           5

(2 row(s) affected)



Code:

-- 2) Ja sam employee. Ko je moj diraktan manager?
-- Odgovor: Onaj kome je EmployeeID ono sto meni pise u ReportsTo
DECLARE @EmployeeID int
SET @EmployeeID = 8
;
SELECT
*
FROM Zaposleni
WHERE EmployeeID = (SELECT ReportsTo 
                    FROM Zaposleni
                    WHERE EmployeeID = @EmployeeID)
;
Employee    EmployeeID   ReportsTo
---------- ----------- -----------
Ceca                 6           5

(1 row(s) affected)


Code:

-- 3) Ko je vrhovni manager?
-- Odgovor: Onaj ciji  ReportsTo ima vrednost NULL
SELECT
*
FROM Zaposleni
WHERE ReportsTo IS NULL

Employee    EmployeeID   ReportsTo
---------- ----------- -----------
Pera                 1        NULL

(1 row(s) affected)


Malo je slozeniji zadatak da se odgovori na sledeca pitanja:
5) Ja sam manager. Ko su svi moji podredjeni?
6) Ja sam employee. Ko su svi moji manager, od direktnog, pa do vrhovnog?

Ako znamo da odgovorimo na 1,2,3,4 onda se to primenjuje na pitanja 5 i 6.

Evo uputstvo za 5 - Ko su svi podredjeni za zadatog menadzera:

1. Izaberi datog menadzera i upisi u pomocnu tabelu #T (1 row affected), EmployeID, Level = 0
2. Nadji neposredne podredjene za tog managera, daj im Level = 1, ubaci u #T
3. Nadji neposredne podredjene za redove iz tabele #T ciji je Level = 1, daj im Level = 2, ubaci u #T
4. Nadji neposredne podredjene za redove iz tabele #T ciji je Level = 2, daj im Level = 3, ubaci u #T
5. Nadji neposredne podredjene za redove iz tabele #T ciji je Level = 3, daj im Level = 4, ubaci u #T

Da li primecujes pravilnost u koracima 3,4,5? U svakom sledecem koraku uzimas iz tabele #T redove koji su poslednji ubaceni, i za njih trazis neposredne podredjene. Ako ih nema vise, zavrsio si posao, tabela #T sadrzi sve podredjene za managera od koga si poceo. Ako ima nekih redova, povacaj im Level i ubaci u tabelu, pa pokusaj ponovo. To su 'iteracije' o kojima govori primer u Books On Line. Generalno, samo prvi korak je nezavisan, svi ostli koraci su itracije koje se svode na "za poslednje ubacene redove u tabelu #T pronadji neposredne podredjene, ponavljaj ovo sve dok ova operacija ne vrati prazan skup"

Ocigledno je da ti treba samo jedan ulazni parametar - EmployeeID za managera od koga pocinjes. Probaj ovo sve u nekoj skripti rucno, pa kad je gotovo, samo skriptu pretvori u funkciju. Probaj, pa dokle stignes. Nastavicemo sutra, sad moram nesto i da radim.

:-)





Ajde to sutra ili kasnije danas, moram nesto i da radim.

[ Zidar @ 07.09.2010. 20:48 ] @
Evo nastavak price. Podrazumevam da imas sacuvanu tabelu Zaposleni. Ako ne, napravi je po skripti iz prethodnog posta. Evo kako mozes da vidis sve zaposlene koji direktno ili indirektno odgovaraju (report to ;-) Zoki:
Code:

-- Kome je sve nadredjena Zoka (EmployeeID = 5)
DECLARE @Level int
DECLARE @BrojacRedova int
;
SET @Level = 0
;
-- Ovo je potrebno da bi poceli sa praznom tabelom #Temp
-- nema veze sa iteracijama i menadzerima
IF OBject_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
-- Upisemo posmatranog menadzera u temp tabelu
-- (Ovde se kreira tabela)

-- Inciramo tabelu:
SELECT
EmployeeID, Level = 0
INTO #Temp
FROM Zaposleni
WHERE EmployeeID = 5  -- ovo bi bio parametar za funkciju, sve ostalo prekopiras
;

-- Ovo su iteracije:
ITERACIJE:
INSERT INTO #Temp (EmployeeID, Level)
SELECT EmployeeID, @Level+1
FROM Zaposleni
WHERE ReportsTo IN (SELECT EmployeeID FROM #Temp WHERE Level = @Level)
;
SET @BrojacRedova = @@rowcount
;
--; SELECT @BrojacRedova AS VracenoRedova
SET @[email protected]+1    -- vazno, da ne uletiimo u beskonacnu petlju

IF @BrojacRedova>0 GOTO ITERACIJE
-- Ovde dolazimo kad vise nema sta da se ubaci u tabelu #Temp:
;
SELECT 
    T.Level
    , T.EmployeeID
    , ImeZaposlenog = Z.Employee  
FROM #Temp AS T
JOIN Zaposleni AS Z ON Z.EmployeeID = T.EmployeeID
ORDER BY T.Level, T.EmployeeID


Skriptu mozes da izvrsavas deo po deo, i da svaki put pogledas sta se upisalo u tabelu #Temp. Prvo uradi deo pre iteracija. Onda uradi sve ponovo, do do linije gde kaze IF @BrojacRedova>0 GOTO ITERACIJE. Pogledaj sta imas u tabeli #Temp. Onda uradi celu skriptu i vidi sta ces da dobijes.

Rezultat je lista svih zaposlenih kojima je Zoka nadredjena. Doduse, ne vidi se da li MIca i Bilja na nivou 2 odgovaraju Ceci ili Veci, ali to se nije ni trazilo zadatkom. Ako odas jos jednom tabelu Zaposleni, pa napravis JOIN EmployeeID = ReportsTo, dobices i ko kome neposredno odgovara u toj listi. Ako si stigao do nivoa da radis 'iteracije' i hijerarhije, ne bi smeo da imas problema sa pisanjem kompleksnih kverija. Ako je ovo ipak sve prekomplikovano, moraces malo da vezbas subkverije, JOIN i slicno.

Nije lako, probaj, pa cemo da pomazemo koliko se moze.


[ nemanjal @ 08.09.2010. 20:07 ] @
Svaka cast. Hvala puno na iscrpnom odgovoru.