Skalaarifunktioiden käyttäminen

  • 3 minuuttia

Skalaarifunktiot palauttavat yksittäisen arvon ja toimivat yleensä yksittäisen tietorivin kanssa. Niiden vastaanottamien syötearvojen määrä voi olla nolla (esimerkiksi GETDATE), yksi (esimerkiksi UPPER) tai useita (esimerkiksi ROUND). Koska skalaarifunktiot palauttavat aina yksittäisen arvon, niitä voidaan käyttää missä tahansa yksittäisessä arvossa (tulos). Niitä käytetään yleisimmin SELECT-lausekkeissa ja WHERE-lauseen predikateissa. Niitä voidaan käyttää myös UPDATE-lausekkeen SET-lauseessa.

Sisäiset skalaarifunktiot voidaan järjestää useisiin luokkiin, kuten merkkijonoon, muunnokseen, loogisiin, matemaattisiin ja muihin. Tässä moduulissa tarkastellaan joitakin yleisiä skalaarifunktioita.

Skalaarifunktioita käytettäessä kannattaa ottaa huomioon esimerkiksi seuraavat asiat:

  • Determinismi: Jos funktio palauttaa saman arvon samalle syötteelle ja tietokantatilalle aina, kun sitä kutsutaan, sanomme, että se on deterministinen. Esimerkiksi ROUND(1.1, 0) palauttaa aina arvon 1,0. Monet sisäiset funktiot ovat epädeterministisiä. Esimerkiksi GETDATE() palauttaa nykyisen päivämäärän ja ajan. Ei-terminististen funktioiden tuloksia ei voi indeksoida, mikä vaikuttaa kyselynkäsittelyn kykyyn laatia hyvä suunnitelma kyselyn suorittamiseksi.
  • Lajittelu: Mitä lajittelua käytetään, kun käytetään funktioita, jotka käsittelevät merkkitietoja? Jotkin funktiot käyttävät syötearvon lajittelujärjestystä. muut käyttävät tietokannan lajittelua, jos syötekollasta ei ole annettu.

Esimerkkejä skalaarifunktioista

Kirjoittamishetkellä SQL Serverin teknisessä dokumentaatiossa luetellaan yli 200 skalaarifunktiota, jotka kattavat useita luokkia, esimerkiksi seuraavat:

  • Määritysfunktiot
  • Muuntifunktiot
  • Kohdistimen funktiot
  • Päivämäärä ja aika -funktiot
  • Matemaattinen toiminto
  • Metatietofunktiot
  • Suojausfunktiot
  • Merkkijonofunktiot
  • Järjestelmäfunktiot
  • Järjestelmän tilastolliset funktiot
  • Teksti- ja kuvafunktiot

Tällä kurssilla ei ole tarpeeksi aikaa kunkin funktion kuvailemiseen, mutta alla oleteissa esimerkeissä näytetään joitakin yleisesti käytettyjä funktioita.

Seuraavassa oletuksena olevassa esimerkissä käytetään useita päivämäärä- ja aikafunktioita:

SELECT  SalesOrderID,
    OrderDate,
        YEAR(OrderDate) AS OrderYear,
        DATENAME(mm, OrderDate) AS OrderMonth,
        DAY(OrderDate) AS OrderDay,
        DATENAME(dw, OrderDate) AS OrderWeekDay,
        DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;

Osittaiset tulokset näkyvät alla:

SalesOrderID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

Kesäkuu

1

sunnuntai

13

...

...

...

...

...

...

...

Seuraavassa esimerkissä on matemaattisia funktioita:

SELECT TaxAmt,
       ROUND(TaxAmt, 0) AS Rounded,
       FLOOR(TaxAmt) AS Floor,
       CEILING(TaxAmt) AS Ceiling,
       SQUARE(TaxAmt) AS Squared,
       SQRT(TaxAmt) AS Root,
       LOG(TaxAmt) AS Log,
       TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;

Osittaiset tulokset:

TaxAmt

Pyöreä

Kerros

Katto

Squared

Juuri

Loki

Satunnaistettu

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...

..

...

...

...

...

...

...

Seuraavassa esimerkissä käytetään merkkijonofunktioita:

SELECT  CompanyName,
        UPPER(CompanyName) AS UpperCase,
        LOWER(CompanyName) AS LowerCase,
        LEN(CompanyName) AS Length,
        REVERSE(CompanyName) AS Reversed,
        CHARINDEX(' ', CompanyName) AS FirstSpace,
        LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
        SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;

Osittaiset tulokset:

Yrityksen nimi

Iso

Gemena

Pituus

Käänteinen

FirstSpace

FirstWord

RestOfName

Pyöräkauppa

PYÖRÄKAUPPA

pyöräkauppa

12

erotS ekiB A

2

A

Bike Store

Progressiivinen urheilu

PROGRESSIIVINEN URHEILU

progressiivinen urheilu

18

stropS evissergorP

12

Progressiivinen

Urheilu

Edistyneet polkupyörän osat

EDISTYNEET PYÖRÄKOMPONENTIT

edistyneet polkupyörän osat

24

stnenopmoC ekiB decnavdA

9

Edistynyt

Pyörän osat

...

...

...

...

...

...

...

...

Loogiset toiminnot

Toinen funktioiden luokka sallii sen määrittämisen, mitkä useista arvoista palautetaan. Loogiset funktiot arvioivat syötelausekkeen ja palauttavat tulokseen perustuvan asianmukaisen arvon.

IIF

IIF-funktio arvioi totuusarvosyötelausekkeen ja palauttaa määritetyn arvon, jos lauseke antaa tulokseksi Tosi, ja vaihtoehtoisen arvon, jos lausekkeen arvo on False.

Otetaan esimerkiksi seuraava kysely, jossa arvioidaan asiakkaan osoitetyyppiä. Jos arvo on "Päätoimisto", lauseke palauttaa arvon "Laskutus". Kaikkien muiden osoitetyyppien arvoille lauseke palauttaa arvon "Postitus".

SELECT AddressType,
      IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;

Tämän kyselyn osittaiset tulokset saattavat näyttää tältä:

AddressType

UseAddressFor

Päätoimisto

Laskutus

Lähetys

Postitus

...

...

VALITA

CHOOSE-funktio arvioi kokonaislukulausekkeen ja palauttaa vastaavan arvon luettelosta sen (1-pohjaisen) koordinaattorisijainnin perusteella.

SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;

Tämän kyselyn tulokset saattavat näyttää seuraavankaltaisilta:

SalesOrderID

Tila

OrderStatus

1234

3

Toimitetaan

1235

2

Toimitettu

1236

2

Toimitettu

1237

1

Tilattu

...

...

...