Here are two interesting enhancements in Oracle database 12c PL/SQL.
PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can be invoked instantly in the SELECT statement while the procedures can be invoked from the functions used in the declaration section.
PL/SQL functions defined using PRAGMA UDF – SQL and PL/SQL have different memory representations of values. Therefore, interconversion is involved while “switching” from one engine to other and vice versa. It allows you to define the PL/SQL subprogram outside the SQL statement but matching the performance of an inlined PL/SQL program.
Let us do a small test to see the performance gains-
1. Created a test table T
CREATE TABLE t
PK integer not null,
n1 integer not…
View original post 684 more words