Skalare SQL-UDFs

.

Verwandte Themen

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

Unter diesem Thema:

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:

SELECT area_of_circle(1.0);
+---------------------+
| 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 Textteil einer UDF eine vollständige SELECT-Anweisung enthalten kann, darf er keine DDL-Anweisung oder DML-Anweisung außer SELECT enthalten.

Bemerkung

Skalare Funktionen (UDFs) haben einen Grenzwert von 500 Eingabeargumenten.

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:

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Häufige SQL-Beispiele

Abfrageausdruck mit 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:

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

UDF in einer WITH-Klausel

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 |
+--------+

JOIN-Operation

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:

SELECT store_profit();
+----------------+
| 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
$$
;
⇧
Zurück zum Anfang