10. Oktober 2014

PL/SQL in Oracle 12.1: Index By Tables, SQL Abfragen und TABLE()

PL/SQL in 12.1: INDEX BY tables, SQL Queries and the TABLE() function
Eins der neuen PL/SQL Features in der Oracle Version 12.1 ist, dass - im Gegensatz zu 11 - noch mehr reine PL/SQL Typen in SQL-Queries oder DML-Anweidungen genutzt werden können. Reine PL/SQL-Typen sind RECORD-, aber auch BOOLEAN- oder "PL/SQL Index-By-Table"-Datentypen. Letztere werden auch als assoziative Arrays bezeichnet. Ab der Version 12.1 ist es nun möglich, eine solche PL/SQL Index-By-Table in SQL-Queries mit der Funktion TABLE() zu verwenden. zumindest die Dokumentation. Wie das genau funktioniert, beschreibt dieses Blog-Posting.
Zunächst der Setup: Ich brauche eine PL/SQL-Funktion, die eine Index-By Table erstellt und zurückgibt - außerdem muss der Type, der als Index-By-Table definiert ist, in einem Package deklariert werden (sonst kann man ihn gar nicht als Funktionsparameter verwenden). Die Dokumentation beschreibt bereits die eine oder andere Einschränkung. So muss u.a. das assoziative Array als INDEX BY PLS_INTEGER definiert sein.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Nun könnte man versuchen, die Funktion aus einer SQL-Query zu verwenden ...
select * from table(mypkg.testit(20))
/

FEHLER in Zeile 1:
ORA-00902: Ungültiger Datentyp
Das funktioniert nicht. Und es gibt auch eine passende Einschränkung in der Dokumentation dazu: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. Doch wie kann man dieses Feature dann überhaupt nutzen?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL-Prozedur erfolgreich abgeschlossen.
Wenn man also eine SQL-Query innerhalb einer PL/SQL Prozedur, -Funktion oder eines anonymen Blocks ausführt, so kann man darin etwaige PL/SQL Index By Tables verwenden (TABLE()-Operator nutzen). Das kann sehr nützlich sein, wenn man die Index-By-Table mit einer Datenbanktabelle joinen möchte.
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL-Prozedur erfolgreich abgeschlossen.
Zusammenfassend kann man sagen, dass PL/SQL und SQL in 12.1 nochmal ein wenig besser zusammenarbeiten. PL/SQL Index-by-Tables können in SQL Queries verwendet werden, solange man sich im PL/SQL Kontext befindet. Für eine "top-level" SQL-Query sind sie aber weiterhin nicht erreichbar. So bleibt auch in 12.1. eine gewisse Grenze zwischen SQL und PL/SQL - aber die Richtung stimmt.
One of the new things in Oracle12c is, that more (compared to Oracle11g) pure PL/SQL types are accessible from SQL queries or DML statements. Pure PL/SQL types are record-, boolean or "INDEX BY" types. The latter are also called associative arrays. So, beginning with 12.1, we can access PL/SQL INDEX BY tables with a SQL query - according to the documentation. This blog posting describes how it works (and what does not work).
First, the setup. We need (of course) a PL/SQL function returing a PL/SQL INDEX BY table. And in order to create such a function, we need to have a package specification with the INDEX BY table definition. To make it easy, we put the function into the package as well. Reading the documentation carefully, we observe the first restriction for this feature: The INDEX BY table must be declared as INDEX BY PLS_INTEGER.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Now we do the first test: Use the INDEX BY table within a SQL query ...
select * from table(mypkg.testit(20))
/

ERROR at line 1:
ORA-00902: invalid datatype
Does not work. Bummer. OK, read the documentation again. Aah, there is the other restriction: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. But what's the value of that new feature then ...? And how can it be used?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL procedure successfully completed.
So, PL/SQL INDEX BY tables are accessible for a SQL statement within a PL/SQL procedure, function or anonymous block (use the TABLE() operator). This can be very useful: think about joining your PL/SQL INDEX BY table to a database table, as follows ...
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL procedure successfully completed.
In Oracle 12.1, PL/SQL and SQL work more closely together. We can use INDEX BY tables within SQL queries now, as long as this query is being executed within a PL/SQL context. So INDEX BY tables are still PL/SQL, not SQL data types. We still have some barriers for PL/SQL data types in SQL - but the development direction is good.

Keine Kommentare:

Beliebte Postings