Mis on funktsioonid SQL-is?

Funktsioon on nimega koodiplokk, mis võtab sisendi (parameetrid), teeb arvutuse ja tagastab tulemuse. Erinevalt protseduurist tagastab funktsioon alati väärtuse ja seda saab kasutada otse SELECT lauses.

---

Funktsioonide tüübid

1. Sisseehitatud (built-in) funktsioonid — need on juba SQL-is olemas:

Sinu failist on neid palju kasutatud:

sql

-- Kuu eraldamine kuupäevast
WHERE MONTH(synniaeg) IN (9, 10, 11)

-- Keskmine väärtus
SELECT AVG(palk) AS keskmine_palk FROM praktikajuhendaja

-- Summa
SELECT SUM(palk) AS kogupalk FROM praktikajuhendaja

-- Loendamine
COUNT(praktikabaas.praktikabaasID) AS praktikakohti

2. Kasutaja loodud (user-defined) funktsioonid — sina või keegi teine loob need ise.

Sinu faili 8. ülesandes kasutati kahte sellist funktsiooni:

sql

-- SQL Serveris kasutati neid funktsioone vanuse arvutamiseks:
SELECT
  eesnimi,
  perekonnanimi,
  synniaeg,
  dbo.fnComputeAge(CAST(synniaeg AS DATETIME)) AS vanus
FROM praktikajuhendaja;

ja

sql

SELECT
  eesnimi,
  perekonnanimi,
  synniaeg,
  dbo.CalculateAge(synniaeg) AS vanus
FROM praktikajuhendaja;

Need funktsioonid fnComputeAge ja CalculateAge võtavad sünniaja sisse ja arvutavad vanuse aastates.

---

Kuidas ise funktsioon luua? (SQL Server)

sql

CREATE FUNCTION dbo.CalculateAge(@DOB DATE)
RETURNS INT
AS
BEGIN
    DECLARE @vanus INT
    SET @vanus = DATEDIFF(YEAR, @DOB, GETDATE())
    RETURN @vanus
END

Seejärel saab seda kasutada nagu sinu failis:

sql

SELECT eesnimi, perekonnanimi, dbo.CalculateAge(synniaeg) AS vanus
FROM praktikajuhendaja;

---

Funktsioon vs. Protseduur — erinevus

	Funktsioon	Protseduur (sinu 7. ülesanne)
Tagastab väärtuse?	Jah, alati	Ei pea
Kasutus SELECT-is	Jah	Ei saa
Käivitamine	SELECT dbo.FunktsiooniNimi()	EXEC protseduuriNimi
Näide failist	dbo.CalculateAge(synniaeg)	EXEC lisa_firma 'UusFirma', ...

Kokkuvõttes: funktsioon = arvutab ja annab väärtuse tagasi; protseduur = teeb mingi toimingu (lisab andmeid, muudab tabelit jne).

fnComputeAge(@DOB datetime)

näitab sünniaega sõnadega

create function fnComputeAge(@DOB datetime)
returns nvarchar(50)
as begin
	declare @tempdate datetime, @years int, @months int, @days int
		select @tempdate = @DOB

		select @years = datediff(year, @tempdate, getdate()) - case when (month(@DOB) > month(GETDATE())) or (MONTH(@DOB)
		= month (getdate()) and day(@DOB) > DAY(getdate())) then 1 else 0 end
		select @tempdate = dateadd(year, @years, @tempdate)

		select @months = datediff(month, @tempdate, getdate()) - case when day(@DOB) > day(getdate()) then 1 else 0 end
		select @tempdate = dateadd(MONTH, @months, @tempdate)

		select @days = datediff(day, @tempdate, getdate())

	declare @Age nvarchar(50)
		set @Age = cast(@years as nvarchar(4)) + ' Years ' + cast(@months as nvarchar(2)) + 
		' Months ' + cast(@days as nvarchar(2)) + ' Days old'
	return @Age
end

dbo.CalculateAge Näitab sünniaega numbritena

CREATE FUNCTION dbo.CalculateAge (@DOB DATE)
RETURNS INT
AS
BEGIN
DECLARE @Age INT;

SET @Age = DATEDIFF(YEAR, @DOB, GETDATE()) 
- CASE 
WHEN DATEADD(YEAR, DATEDIFF(YEAR, @DOB, GETDATE()), @DOB) > GETDATE()
THEN 1 
ELSE 0 
END;