Tag-uri: Exemple de cod, Microsoft SQL Server, Programare, SQL, Baze de date

Exemplu de utilizare a unei funcţii recursive şi a unui cursor în T-SQL

Utilizarea unei funcţii recursive şi a unui cursor în T-SQL Exemplul următor poate fi utilizat pentru dezvoltarea unei aplicaţii tip forum de discuţii. În exemplu se foloseşte o funcţie recursivă şi un cursor (nu recomandăm utilizarea codului aşa cum este, din motive de performanţă, acest cod este furnizat doar cu rol de exemplificare!).

1. Tabela de utilizatori

Primul tabel utilizat într-o o aplicatie de tip forum este tabela de utilizatori:
----------------------------------------------------------
-- Users
-----------------------------------------------------------
CREATE TABLE Users
(
     [ID] INT PRIMARY KEY IDENTITY,
     UserName VARCHAR (30) NOT NULL,
     [Password] NVARCHAR (60) NOT NULL,
     Email VARCHAR (100) NOT NULL
)

2. Tabela de mesaje

Apoi se defineste tabela de mesaje:
----------------------------------------------------------
-- Messages
-----------------------------------------------------------
CREATE TABLE Messages
(
     [ID] INT PRIMARY KEY IDENTITY,
     UserID INT REFERENCES Users ([ID]) NOT NULL,
     [Date] DATETIME DEFAULT GetDate(),
     Message NVARCHAR (4000) NOT NULL,
     ParentMessage INT REFERENCES Messages ([ID])
)

In acest tabel campul ParentMessage contine ID-ul mesajului parinte. Un mesaj care are ParentMessage NOT NULL
este un raspuns la mesajul cu ID-ul din acest camp.
In acest fel tabela "Messages" descrie mai multi arbori, cu radacinile in acele inregistrari care au ID-ul nul.
Pentru a obţine ramurile unui arbore vom utiliza o funţie recursivă.

3. Inserarea de valori

Inserarea de valori in cele două tabele:
----------------------------------------------------------
-- Inserting values into Users table -----------------------------------------------------------
INSERT INTO Users VALUES ('user', 'parola', 'office@techit.ro')

----------------------------------------------------------
-- Inserting values into Messages tablee
-----------------------------------------------------------
INSERT INTO Messages VALUES (1, DEFAULT, 'first message', null)
--responses to the first message (ID = 1)
INSERT INTO Messages VALUES (1, DEFAULT, 'first response', 1)
INSERT INTO Messages VALUES (1, DEFAULT, 'second response', 1)

4. Obţinerea detaliilor unui mesaj

Se utilizează un query de genul:

----------------------------------------------------------
-- Selecting message details
-----------------------------------------------------------
SELECT Messages.[Date], Messages.Message, Users.UserName, Users.Email
FROM Messages
INNER JOIN Users ON Messages.UserID = Users.[ID]
WHERE Messages.[ID] = 1

Rezultat:

Date	Message	UserName	Email
2008-04-07 14:17:59	first message	user	office@techit.ro

5. Funcţia recursivă

In conţinutul funcţiei puteţi găsi comentarii utile:
----------------------------------------------------------------------------------------
--Functia: fGetMessageChilds
--Parametri IN: @ID INT - este un ID diin tabela Messages
--Parametri OUT: @tbl TABLE - functia reeturneaza un tabel
--Descriere: returneaza raspunsurile daate mesajului cu ID dat, precum si raspunsurile
-- primite de acestea
-----------------------------------------------------------------------------------------
CREATE FUNCTION fGetMessageChilds (@ID INT)
    --functia returneaza un tabel:
    RETURNS @tbl TABLE ([ID] INT, UserID INT, [Date] DATETIME, Message NVARCHAR (4000), ParentMessage INT)
AS

BEGIN

--declararea variabilei pentru navigarea intre inregistrarile din cursor
DECLARE @i INT

--declararea cursorului
DECLARE c CURSOR FOR
SELECT [ID]
FROM Messages
WHERE ParentMessage = @ID

OPEN c
--navigarea la prima inregistrare din cursor
FETCH NEXT FROM c INTO @i

WHILE @@FETCH_STATUS = 0
BEGIN
     INSERTINTO @tbl
     SELECT [ID], UserID, [Date], Message, ParentMessage
     FROM Messages
     WHERE [ID] = @i

     --apelul recursiv al functiei
     INSERT INTO @tbl SELECT [ID], UserID, [Date], Message, ParentMessage FROM fGetMessageChilds (@i)

     --avans la urmatorul rand al cursorului
     FETCH NEXT FROM c INTO @i
END

CLOSE c
DEALLOCATE c

RETURN

END

GO

6. Utilizarea funcţiei

Pentru a vedea toate răspunsurile date mesajului cu ID-ul 1 se utilizează:
SELECT * FROM fGetMessageChilds (1)

Notă importantă: numărul maxim de recurenţe permise este de 32!

techit.ro

Colecţia: Exemple de cod

Articolul precedent: Funcţii recursive in T-SQL

Articolul următor: Exemplu de utilizarea a tabelelor temporare în T-SQL

Spune-ti parerea despre acest articol!