Stored Functions

Creating and Using Stored Functions

Stored Functions worden op dezelfde wijze gedefinieerd als Stored Procedures, behalve dat het commando de opdracht CREATE FUNCTION moet gebruiker. En hoewel het voor een Stored Procedure niet verplicht is om om een retourwaarde terug te geven moet een Stored Function een retourwaarde teruggeven.

Hier is een eenvoudig voorbeeld van een opgeslagen functie, die een geformatteerde versie van de huidige datum teruggeeft:

CREATE FUNCTION [dbo].[today]()
RETURNS VARCHAR(255)
BEGIN
    RETURN FORMAT(GETDATE(),'dd MMMM yyyy')
END;

Command(s) completed successfully.

DELIMITER //
CREATE FUNCTION today()
RETURNS VARCHAR(255)
BEGIN
    RETURN DATE_FORMAT(NOW(),'%D %M %Y');
END //

(0 row(s) affected)

Net als bij de opdracht CREATE PROCEDURE, moet het commando CREATE FUNCTION gevolgd worden door de naam van de opgeslagen functie. Dezelfde regels die gelden voor procedurenamen gelden ook voor functienamen. Eventuele invoerparameters van de functie worden weergegeven tussen haakjes na de naam functie, samen met hun gegevenstype. De functie van de geretourneerde waarde (slechts een enkele retourwaarde is mogelijk) wordt weergegeven door een verplichte RETURNS clausule dat de haakjes volgt; deze RETURNS clausule specificeert het gegevenstype van de return waarde.

Het hoofdgedeelte van de functie kan SQL-statements, variabele definities, conditionele testen, loops, en error handlers bevatten. Het moet eveneens RETURN statement bevatten, waarbij de waarde wordt bepaald wat naar de oproepende instantie wordt geretourneerd. Echter, omdat opgeslagen functies geen resultaat sets kan terugkeren, zorg ervoor dat uw RETURN statement niet de uivoer van een SELECT (of een andere opdracht met resultaat set ) teruggeeft.

Wanneer SQL een RETURN-instructie binnen een opgeslagen functie tegenkomt, stopt de verwerking op dat punt en verlaat de functie met de opgegeven return waarde.

Om een opgeslagen functie aan te roepen, hoeft u niet het CALL commando te gebruiken; u kunt in de plaats daarvan de functienaam in een SQL-instructie gebruiken, zoals u zou doen voor iedere andere ingebouwde functie. Hier is een voorbeeld:

SELECT [dbo].[today]();

(1 row(s) affected)

SELECT today();

(1 row(s) returned)

Een opgeslagen functie kan men verwijderen met de opdracht DROP FUNCTION met de functienaam als argument:

DROP FUNCTION [dbo].[today];

Command(s) completed successfully.

DROP FUNCTION today;

(0 row(s) affected)

Om het hoofdgedeelte van een specifieke opgeslagen functie weer te geven, gebruikt u het commando SHOW CREATE FUNCTION (MYSQL) / EXEC (MSSQL) met de functie naam als argument. Dit is een opdracht met restricties; het zal enkel worden uitgevoerd alleen als u de maker van de procedure of SELECT privileges op de proc grant table (privileges worden in detail besproken in hoofdstuk 11). Hier is een voorbeeld:

EXEC sp_helptext '[dbo].[today]';

Command(s) completed successfully.

SHOW CREATE FUNCTION today;

(1 row(s) returned)

Om een lijst van alle opgeslagen functies op de server weer te geven, gebruikt u het commando SHOW FUNCTION STATUS (MySQL) / SELECT * FROM sys.objects WHERE type_desc LIKE ‘%FUNCTION%’ (MSSQL). U kunt de uitvoer van deze opdracht te filteren met een WHERE-clausule, zoals afgebeeld:

SELECT *
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';

(1 row(s) affected)

SHOW FUNCTION STATUS WHERE db='db1';

(4 row(s) returned)

Input Parameters

Omdat opgeslagen functies een aparte RETURNS clausule gebruiken om hun uitvoer te bepalen, wordt verondersteld dat alle parameters die binnen de haakjes bevinden van de functie definitie invoerparameters te zijn, en zijn de OUT en INOUT sleutelwoorden niet nodig (of ondersteund) voor de input lijst met argumenten . Deze ingangsparameters kunnen vervolgens worden gemanipuleerd of worden gebruikt voor berekeningen in het hoofdgedeelte van de functie, zoals in het volgende voorbeeld:

CREATE FUNCTION get_circle_area(@radius INT)
RETURNS FLOAT
BEGIN
    RETURN PI() * @radius * @radius;
END;

Command(s) completed successfully.

DELIMITER //
CREATE FUNCTION get_circle_area(radius INT)
RETURNS FLOAT
BEGIN
    RETURN PI() * radius * radius;
END//

(0 row(s) affected)

U kunt aan deze functie de lengte van de straal van een cirkel doorgeven om de bijbehorende omtrek van de cirkel te ontvangen, zoals getoond:

SELECT [dbo].[get_circle_area](10);

(1 row(s) affected)

SELECT get_circle_area(10);

(1 row(s) returned)

Uit een opgeslagen functie kunt U ook direct lezen en schrijven van sessievariabelen. Beschouw het volgende voorbeeld, welk vorige voorbeeld herziet en sessievariabelen gebruikt voor zowel de input en output:

Tot Microsoft SQL Server 2016 had men enkel de mogelijkheid om één enkele vaste sessievariabele te gebruiken namelijk CONTEXT_INFO(). Deze is beperkte 128 bytes grootte. Vanaf SQL Server 2016 is de nieuwe sessievariabele SESSION_CONTEXT() geïntroduceerd welke een aantal significante beperkingen heeft weggenomen.

Het gebruik van tijdelijke tabellen biedt ook de mogelijkheid om tijdelijke variabelen aan te maken voor de duur van een sessie.

https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables#global-temporary-tables
http://www.codeproject.com/Articles/39131/Global-Variables-in-SQL-Server
http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx
https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/

DELIMITER //
CREATE FUNCTION get_circle_area()
RETURNS INT
BEGIN
    SET @area = PI() * @radius * @radius;
    RETURN NULL;
END //
(0 row(s) affected)
SET @radius=2
(0 row(s) affected)
SELECT @area;

(1 row(s) returned)

Net zoals bij stored procedures kunnen opgeslagen functies ook tabellen manipuleren. Hier is een voorbeeld:

In MSSQL is het niet mogelijk om via een function records aan een tabel toe te voegen. Men zal volgende foutmelding ontvangen:

‘Invalid use of a side-effecting operator ‘INSERT’ within a function.’

Men dient hiervoor een procedure gebruiken.

CREATE PROCEDURE add_flight_dep(
    @fid INT, @depday INT, @deptime TIME)
AS
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (@fid, @depday, @deptime);

(0 row(s) affected)

EXEC [dbo].[add_flight_dep] 
    @fid=1, @depday=2, @deptime='12:35'

(1 row(s) affected)

SELECT DepDay, DepTime
FROM flightdep
WHERE FlightID = 1;

(1 row(s) affected)

DELIMITER //
CREATE FUNCTION add_flight_dep
    (
    fid INT, depday INT, deptime TIME
    )
RETURNS INT
BEGIN
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (fid, depday, deptime);
RETURN 1;
END //

(0 row(s) affected)

SELECT add_flight_dep(1, 2, '12:35');
(1 row(s) returned)

SELECT DepDay, DepTime
FROM flightdep
WHERE FlightID = 1;

(1 row(s) returned)

Hoe kan ik een verzameling van waarden teruggeven vanuit een Stored Function?

Onder de huidige implementatie van MySQL, kan een opgeslagen functie alleen een enkele waarde teruggeven. Echter, er is een oplossing: maak een tijdelijke tabel aan in het hoofdgedeelte van de functie om de geretourneerde waarden op te slaan, en vervolgens kan men deze tabel buiten de functie benaderen. Hier is een voorbeeld:

DELIMITER //
CREATE FUNCTION get_airport_names(min_terminals INT)
RETURNS INT
BEGIN
    DECLARE count INT DEFAULT 0;
    CREATE TEMPORARY TABLE
    IF NOT EXISTS
    get_airport_names_out (value VARCHAR(255));
    DELETE FROM get_airport_names_out;
    INSERT INTO get_airport_names_out (value)
    SELECT AirportName FROM airport
    WHERE NumTerminals >= min_terminals;
    SELECT COUNT(value) INTO count
    FROM get_airport_names_out;
    RETURN count;
END//

(0 row(s) affected)

SELECT get_airport_names(3);

(1 row(s) returned)

SELECT value
FROM get_airport_names_out;

(4 row(s) returned)