Cursor / temporäre Tabellen

[Sylvia Rühle]

Hallo,

ich habe bisher für MS SQL Server Anwendungen entwickelt und bin deshalb in Oracle noch nicht so bewandert.

Bisher bin ich es gewöhnt mit temporären Tabellen zu arbeiten. Sie werden in gespeicherten Prozeduren erstellt und miteinander verknüpft, sodass ich eine Endergebnistabelle erhalte. Bei einer solchen temp. Tabelle kann es sich z.B. auch darum handeln, dass sie die Werte der übergebenen Prozedur-Parameter beinhaltet (also ihre Werte nicht aus festen Tabellen erhält). Diese temp. Tabellen werden nach jedem Prozedurablauf gelöscht (k. glob. Tabellen). Dieses Handling vermisse ich in Oracle. Wie kann ich gescheit Daten abfragen, Zwischenmengen miteinander verknüpfen. Cursor sind zwar nett, aber ich muss sie immer durchiterieren auch wenn ich eine Teilmenge (mehrere DS) aus dem Cursor gern hätte. Außerdem muss es sehr performant sein. Ich habe in MS SQL Server viel Cursor verwendet und zwar nur dann wenn ich jeden Einzeldatensatz betrachten musste. Es gibt temp. Tabellen in Oracle, aber sie werden im Schema angelegt und sind global.

Ich hoffe es konnte mir jemand folgen worauf ich hinaus möchte und mir einen Rat geben wie ich am besten das Handling in Oracle umsetzten kann.

Vielen Dank

Sylvia

[Falk Prüfer]

Hallo Sylvia,

mglws. bieten dir die Oracle-Container eine Alternative. In diese lassen sich z.B. Datensätze einlesen und sie lassen sich auch wieder als Cursor verwenden, ohne das dazu tatsächlich "echte" Tabellen angelegt werden müssen.
Hier mal ein kleines Bsp.:
[highlight=sql]
create type TMyTest as object(
id number,
name varchar2(100));

create type TTabMyTest as table of TMyTest

create table MyTest (id number, name varchar2(100))

insert into MyTest values(1, 'Werner')
insert into MyTest values(2, 'Emil')
insert into MyTest values(3, 'Rita')

declare
TabMyTest TTabMyTest;
begin
/* Eine Instanz des Tabellencontainers erzeugen */
TabMyTest := TTabMyTest();
/* Jetzt wird der Tabellencontainer mit einem Select aus einer
"echten" Tabelle (MyTest) gefüllt */
select cast(multiset(select * from MyTest) as TTabMyTest) into TabMyTest from dual;
/* jetzt eine Cursor-For Schleife über den Tabellencontainer */
for rTest in (
select * from table(cast(TabMyTest as TTabMyTest)))
loop
dbms_output.put_line(rTest.id);
end loop;
end;
[/highlight]

Die Tabelle MyTest habe ich nur angelegt, um eine "echte" Tabelle als Ausgangsbasis zu haben.
Prinzipiell lassen sich damit sogar Rückgabewerte von Funktionen als Cursor verwenden.
[highlight=sql]
create or replace function MyFunc return TTabMyTest as
TabMyTest TTabMyTest;
begin
select cast(multiset(select * from MyTest) as TTabMyTest) into TabMyTest from dual;
return TabMyTest;
end;

select * from table(cast(MyFunc() as TTabMyTest)) t
[/highlight]

Gruß Falk

[Sylvia Rühle]

Hallo Falk,

vielen Dank. Das geht in die Richtung, die ich meine.

Ich übernehme aus dem MS SQL Server folgende Logik:
1. Schreibprozedur, deren Parameter der zu schreibende Datensatz darstellen als varchar2-Werte (diese Werte kommen direkt aus Maschinen/SPS und sind Zeichenketten).
2. Diese Werte müssen in den richtigen Datentyp der Tabellenspalte convertiert werden.
3. Es werden von verschiedenen Anwendungen unterschiedliche Informationen geschrieben. D.h. die erste Anwendung schreibt den DS (Insert) mit beispielsweise 5 Spalten und ander Anwendungen aktualisieren diesen Datensatz mit ganz anderen Spalten. Ergo, der gesamte Schreibvorgang muss dynamisch gestaltet sein - Insert/Update-Anweisungen werden zur Laufzeit generiert anhand der übergebenen Werte in den Parameter.


Deshalb müssen alle Werte der Parameter in eine temporäre Tabelle (1) geschrieben werden und in einer weiteren temporären Tabelle (2) um die Datentypen der zugrundeliegenden Tabelle zu ergänzen. Damit wird dann der auszuführende SQL-String generiert und ausgeführt.

Im Data Dictionary konnte ich keine Informationen zu Prozeduren mit ihren Parametern (außer, dass sie existieren) finden. Ich würde gern die Parameter mit ihren Werten in einem Select auslesen oder wenigstens in einer Schleife. Geht das? Kann ich einen TYPE TABLE auch dynamisch füllen?

Viele Grüße
Sylvia

[Falk Prüfer]

Hallo Sylvia,
der Pferdefuß dabei ist, daß du dich von Anfang an auf die Datentypen und die Struktur festlegen mußt. Mein Type TMyTest besteht aus einem Feld ID (Number) und einem Feld Name (VARCHAR2). Das ist später zur Laufzeit nicht mehr änderbar. Wenn du dort auf volle Dynamik - was die Anzahl und Typen der Spalten angeht - angewiesen bist, dann wirst du nicht drumherumkommen, die entsprechenden Tabellen (ob dann temporär oder nicht) ebenfalls dynamisch anzulegen und nach Abschluß wieder zu löschen. Sowas wie eine Prozedur, der ich zur Laufzeit beliebig viele Paremeter mit beliebigen Datentypen übergeben kann, um dann mit Laufzeitfunktionen zu erfragen was übergeben wurde, gibt es in Oracle nicht. Zumindest ist mir da nichts bekannt.

Gruß Falk