PL/SQL - execute immediate in Pipelining-Funktion

Möchte ich ausführen dynamische Abfrage in meinem Pipelining-Funktion und die Rückgabe der Ergebnisse dieser Abfrage.
Ist es möglich, dies zu tun?
Pipelining Funktion ist bequem für mich zu erreichen, gutes interface für meine Anwendung zu verursachen, es verhält sich wie eine Tabelle.

Funktion:

CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function
  EXECUTE IMMEDIATE v_query;
  --Results of the v_query are compatible with MyTableType's row type. But how to return them from pipelined function?
END;

InformationsquelleAutor dzb | 2012-09-25

  1. 3

    Es ist möglich, kombinieren Sie dynamische SQL-und Pipelining-Funktion, aber der Rückgabetyp wird nicht dynamisch sein: die Anzahl und Art der Spalten, die zurückgegeben werden, werden behoben.

    Können Sie EXECUTE IMMEDIATE mit BULK COLLECT (Dank @hier jetzt), dynamische Cursor oder DBMS_SQL um mehr als eine Zeile zurückgegeben. Hier ist ein Beispiel mit einem dynamischen cursor:

    SQL> CREATE OR REPLACE PACKAGE pkg AS
  2     TYPE test_tab IS TABLE OF test%ROWTYPE;
  3     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
  3        cc sys_refcursor;
  4        l_row test%ROWTYPE;
  5     BEGIN
  6        OPEN cc FOR 'SELECT * FROM test WHERE ' || l_where;
  7        LOOP
  8           FETCH cc INTO l_row;
  9           EXIT WHEN cc%NOTFOUND;
 10           PIPE ROW (l_row);
 11        END LOOP;
 12        RETURN;
 13     END;
 14  END;
 15  /

Package body created.

    Nennen wir diese dynamische Funktion:

    SQL> SELECT *
  2    FROM TABLE(pkg.dynamic_cursor('id <= 2'));

        ID DAT
---------- ---
         1 xxx
         2 xxx

    Wie immer mit dynamischen SQL, hüten Sie sich vor SQL-Injection.

    nicht die Abfrage schnell durch magic :), in der Tat, es ist ein wenig mehr Aufwand als in native dynamic SQL. Es gibt so viele Gründe, warum Ihre Abfrage könnte langsam sein, dass es gibt keine Möglichkeit, kann ich das göttliche, wie man seine Leistung verbessern mit so wenig Kontext. Der einzige Unterschied zwischen dynamischen und statischen SQL ist die Verwendung von Variablen. Verwenden Sie parametrisierte dynamische SQL?
    Vielen Dank für dieses Beispiel. Es funktioniert, aber schrecklich langsam. Ich denke, es ist Ursache ich nenne ein weiteres Pipeline-Funktion in meiner Abfrage (ich aktualisierte meine Frage) und wie es aussieht ist es zu oft aufgerufen. Konnte Sie nach ein paar Anregung zu dbms_sql?
    sofort ... bulk collect into..."; for i in col.erste .. col.Letzte loop pipe row () end loop; ?
    Sind die original-Pipeline-Funktionen schneller als die wrapper-Funktion?
    Du hast Recht, mein schlechtes. Aus irgendeinem Grund dachte ich Sie nicht nutzen konnte BULK COLLECT mit einer Tabelle von %ROWTYPE. Aber nicht nur, dass es funktioniert, wird es schneller sein, für kleine Mengen 🙂

    InformationsquelleAutor Vincent Malgrat

  2. 1

    Ich denke, so etwas wie dieses:

    CREATE OR REPLACE FUNCTION MyFunction(par1 VARCHAR2, ...) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
l_result MyTableType;
BEGIN
  v_query := --My query created based on parameters
  EXECUTE IMMEDIATE v_query into l_result;

  pipe row(l_result);
END;

    Funktioniert nur, wenn v_query liefert 1 Zeile.

    InformationsquelleAutor Rene

  3. 0

    Konnte ich nicht, @VincentMalgrat Antwort zu arbeiten. Aber es war sehr nah. Auf jeden Fall eine große Hilfe in die richtige Richtung für mich.

    Hier ist, was ich habe zu arbeiten:

    Paket

     CREATE OR REPLACE PACKAGE pkg AS
     TYPE test_row IS RECORD  ( test_name  VARCHAR2 (255), test_number number, test_date date );  
     TYPE test_tab IS TABLE OF test_row;
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
   END;

    Paket Körper

     CREATE OR REPLACE PACKAGE BODY pkg IS
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
        cc sys_refcursor;
        l_row test_row;
     BEGIN
        OPEN cc FOR 'select name_column, number_column, date_column FROM my_table where number_column ='||l_where;
        LOOP
           FETCH cc INTO l_row;
           EXIT WHEN cc%NOTFOUND;
           PIPE ROW (l_row);
        END LOOP;
        RETURN;
     END;
  END;

    InformationsquelleAutor EdHayes3

Schreibe einen Kommentar