Oracle PL/SQL cookbook¶
Practical recipes for the plxplsql dialect. Every recipe here was run on PostgreSQL; plx transpiles the body to plpgsql and the standard interpreter executes it. The function signature uses PostgreSQL types; the body is PL/SQL. See the plxplsql chapter for the full language reference.
Scalar function with branching¶
Declare a local with an Oracle type and pick a value through an IF / ELSIF / ELSE chain. VARCHAR2(10) in the declaration section is translated to varchar(10).
CREATE FUNCTION grade(score numeric) RETURNS text LANGUAGE plxplsql AS $$
result VARCHAR2(10);
BEGIN
IF score >= 90 THEN
result := 'A';
ELSIF score >= 80 THEN
result := 'B';
ELSIF score >= 70 THEN
result := 'C';
ELSE
result := 'F';
END IF;
RETURN result;
END;
$$;
Accumulating loop¶
A numeric accumulator over an inclusive FOR i IN 1..n LOOP range. NUMBER maps to PostgreSQL numeric, which is arbitrary precision, so the factorial does not overflow.
CREATE FUNCTION factorial(n int) RETURNS numeric LANGUAGE plxplsql AS $$
result NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
result := result * i;
END LOOP;
RETURN result;
END;
$$;
Building a string in a loop¶
The || operator concatenates text. This body is already valid plpgsql, so plx passes it through and only translates the VARCHAR2 declaration.
CREATE FUNCTION csv_row(n int) RETURNS text LANGUAGE plxplsql AS $$
s VARCHAR2(200) := '';
BEGIN
FOR i IN 1..n LOOP
IF i > 1 THEN
s := s || ',';
END IF;
s := s || i;
END LOOP;
RETURN s;
END;
$$;
Cursor loop¶
An explicit CURSOR c IS ... declaration, iterated with FOR r IN c LOOP. plx rewrites the declaration to the plpgsql c CURSOR FOR ... form. The recipe needs a table, so create and populate one first.
CREATE TABLE product (id int, name text, price numeric);
INSERT INTO product VALUES (1, 'pen', 2.50), (2, 'notebook', 5.00), (3, 'stapler', 8.75);
CREATE FUNCTION price_list() RETURNS text LANGUAGE plxplsql AS $$
CURSOR c IS SELECT name, price FROM product ORDER BY id;
out VARCHAR2(400) := '';
BEGIN
FOR r IN c LOOP
out := out || r.name || '=' || r.price || ';';
END LOOP;
RETURN out;
END;
$$;
Set-returning function¶
RETURNS TABLE(...) with RETURN QUERY streams rows back to the caller. The body is standard plpgsql; nothing Oracle-specific is needed. This uses the product table from the previous recipe.
CREATE FUNCTION affordable(max_price numeric)
RETURNS TABLE(name text, price numeric) LANGUAGE plxplsql AS $$
BEGIN
RETURN QUERY
SELECT p.name, p.price FROM product p
WHERE p.price <= max_price
ORDER BY p.price;
END;
$$;
Exception handling¶
Raise an application error with RAISE_APPLICATION_ERROR, which plx translates to RAISE EXCEPTION, and catch it in an EXCEPTION section. The error code (the first argument) is required; a single-argument form is not supported.
CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric LANGUAGE plxplsql AS $$
BEGIN
IF b = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'cannot divide by zero');
END IF;
RETURN a / b;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
$$;
Trigger function¶
A BEFORE INSERT OR UPDATE trigger validates the row and stamps a timestamp. NEW and OLD are available as in plpgsql, and SYSDATE is translated to LOCALTIMESTAMP.
CREATE TABLE account (id int, name text, balance numeric, updated_at timestamp);
CREATE FUNCTION stamp_account() RETURNS trigger LANGUAGE plxplsql AS $$
BEGIN
IF NEW.balance < 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'balance may not be negative');
END IF;
NEW.updated_at := SYSDATE;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_stamp BEFORE INSERT OR UPDATE ON account
FOR EACH ROW EXECUTE FUNCTION stamp_account();
INSERT INTO account (id, name, balance) VALUES (1, 'checking', 100.00);
SELECT id, name, balance, (updated_at IS NOT NULL) AS stamped FROM account;
Dynamic SQL¶
EXECUTE IMMEDIATE runs a query built at runtime. plx rewrites it to plpgsql EXECUTE; INTO captures a scalar and USING binds parameters by position ($1, $2, ...). This uses the product table from the cursor recipe.
CREATE FUNCTION count_where(min_price numeric) RETURNS bigint LANGUAGE plxplsql AS $$
n bigint;
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) FROM product WHERE price >= $1'
INTO n USING min_price;
RETURN n;
END;
$$;
Oracle idioms: NVL, SYSDATE, %TYPE, NEXTVAL¶
Four Oracle spellings in one function. NVL becomes coalesce, SYSDATE becomes LOCALTIMESTAMP, %TYPE passes through, and seq.NEXTVAL becomes nextval('seq'). SYSDATE and NVL are not translated inside a RAISE_APPLICATION_ERROR or DBMS_OUTPUT.PUT_LINE argument, so assign them to a variable first as shown here. This uses the product table from the cursor recipe.
CREATE SEQUENCE ticket_seq;
CREATE FUNCTION next_ticket(label text) RETURNS text LANGUAGE plxplsql AS $$
v_label product.name%TYPE;
v_id NUMBER;
v_year VARCHAR2(4);
BEGIN
v_label := NVL(label, 'untitled');
v_id := ticket_seq.NEXTVAL;
v_year := to_char(SYSDATE, 'YYYY');
RETURN v_year || '-' || v_id || ':' || v_label;
END;
$$;
WHILE loop over a numeric¶
A WHILE loop that counts the decimal digits of a value. It mixes PLS_INTEGER (translated to integer) and NUMBER (translated to numeric) locals.