WITH Clause Functions
A neat feature of Oracle Database 12c is the ability to put PL/SQL functions inside SQL WITH statements. It’s covered in Chapter 2 on new SQL and PL/SQL features of the Oracle Database 12c PL/SQL Programming. There’s a trick though, you must disable the SQLTERMINATOR before creating the statement or accessing it, like:
SET SQLTERMINATOR OFF |
Then, you can write a WITH statement like this:
WITH FUNCTION glue ( pv_first_name VARCHAR2 , pv_last_name VARCHAR2) RETURN VARCHAR2 IS lv_full_name VARCHAR2(100); BEGIN lv_full_name := pv_first_name || ' ' || pv_last_name; RETURN lv_full_name; END; SELECT glue(a.first_name,a.last_name) AS person FROM actor a / |
Unfortunately, you need to include it in a view to make the WITH statement useful, like:
CREATE OR REPLACE VIEW actor_v AS WITH FUNCTION glue ( pv_first_name VARCHAR2 , pv_last_name VARCHAR2) RETURN VARCHAR2 IS lv_full_name VARCHAR2(100); BEGIN lv_full_name := pv_first_name || ' ' || pv_last_name; RETURN lv_full_name; END; SELECT glue(a.first_name,a.last_name) AS person FROM actor a / |
Hope this helps those trying to use the feature.
Subscribe via RSS