Skalare SQL-UDFs¶

Unter diesem Thema werden Konzepte und Nutzungsdetails behandelt, die für SQL-UDFs (benutzerdefinierte Funktionen) spezifisch sind.

Eine Einführung in UDFs und Informationen zu allen Arten von UDFs finden Sie unter Übersicht zu UDFs.

Unter diesem Thema:

Allgemeine Nutzung
Beispiele

Allgemeine Nutzung¶

Eine SQL-UDF wertet einen beliebigen SQL-Ausdruck aus und gibt die Ergebnisse des Ausdrucks zurück.

Die Funktionsdefinition kann ein SQL-Ausdruck sein, der entweder einen skalaren (d. h. einen einzelnen) Wert oder, wenn als Tabellenfunktion definiert, einen Satz von Zeilen zurückgibt. Hier ist ein einfaches Beispiel einer skalaren UDF, die die Fläche eines Kreises berechnet:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;

SELECT area_of_circle(1.0);

Ausgabe:

+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

Der Ausdruck kann ein Abfrageausdruck sein (ein SELECT-Ausdruck). Beispiel:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Wenn Sie einen Abfrageausdruck in einer SQL-UDF verwenden, müssen Sie kein Semikolon in den UDF-Funktionsrumpf einschließen, um den Abfrageausdruck zu beenden.

Sie können nur einen Abfrageausdruck einschließen. Der Ausdruck kann UNION [ALL] enthalten.

Bemerkung

Obwohl der Funktionsrumpf einer UDF eine vollständige SELECT-Anweisung enthalten kann, darf er keine DDL-Anweisung oder DML-Anweisung außer SELECT enthalten.

Beispiele¶

Grundlegendes Beispiel für skalare SQL-UDF¶

Dieses Beispiel gibt eine hart codierte Approximation der mathematischen Konstante Pi zurück.

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

SELECT pi_udf();   

Ausgabe:

+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Häufige SQL-Beispiele¶

In diesem Beispiel wird ein Abfrageausdruck verwendet (d. h. eine SELECT-Anweisung):

Erstellen Sie die zu verwendende Tabelle und Daten:

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

Erstellen Sie die UDF:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Rufen Sie die UDF in einer Abfrage auf:

SELECT profit();

Ausgabe:

+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

In diesem Beispiel wird die UDF in einer WITH-Klausel verwendet:

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;

WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

Ausgabe:

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

In diesem Beispiel wird eine komplexere Abfrage verwendet, die eine JOIN-Operation enthält:

Erstellen Sie die zu verwendende Tabelle und Daten:

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

Erstellen Sie die UDF:

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

Rufen Sie die UDF in einer Abfrage auf:

SELECT store_profit();

Ausgabe:

+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

Weitere Beispiele finden Sie unter dem Thema CREATE FUNCTION.

Verwenden von UDFs in verschiedenen Klauseln¶

Ein skalare UDF kann an jeder Stelle verwendet werden, an der ein Skalarausdruck verwendet werden kann. Beispiel:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

Verwenden von SQL-Variablen in einer UDF¶

In diesem Beispiel wird gezeigt, wie Sie eine SQL-Variable festlegen und diese Variable in einer UDF verwenden:

SET id_threshold = (SELECT COUNT(*) FROM table1) / 2;

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;