executeProcedure

Versionshinweise	24.1.24, 25.1.09, 25.4

Code

GROOVY

Serializable[]  SQLUtils.executeProcedure(String statement, Serializable[] parameter, Class[] resultTyps, String[] outParamArrayTypes)

Beschreibung

Führt eine SQL-Prozedur (stored procedure) mit den übergebenen Parametern aus.

Man beachte, dass Prozeduren keine Funktionen sind. Um eine Funktion aufzurufen, muss man zusätzlich eine Prozedur schreiben, die die gewünschte Funktion aufruft und ihr Ergebnis als out-Parameter zurückgibt.

Parameter

statement - Ein SQL Statement zum Aufruf eine Prozedur. Parameter werden mit "?" gekennzeichnet. Die Ausgabeparameter müssen nach den Eingabeparametern definiert werden.
parameter - Ein Array von Werten. Sie werden an die in-Parameter der Prozedur übergeben.
resultTyps - Ein Array von Java-Klassen, welche den out-Parametern der Prozedur entsprechen.
outParamArrayTypes - 25.1.09 24.1.24 25.4 Ein Array von Strings, welche die Typen der Zeilen von Array-Out-Parametern definieren.

Beispiele

für MS SQL

SQL

CREATE PROCEDURE DoSomething (@param1 VARCHAR(50), @return VARCHAR(100) OUTPUT) AS
BEGIN
    SET @return =  'Prozedur ''DoSomething'' wurde mit Parameter ' + @param1 + ' aufgerufen';
END

Serializable[] params = [ "Parameter 1"]
Class[] classes = [ String.class ]
Serializable[] ret = LogicScriptUtils.executeProcedure("{call dbo.DoSomething(?,?)}", params, classes);

für Oracle

SQL

CREATE OR REPLACE PROCEDURE evijet.DoSomething(p_param1 IN varchar2, p_return OUT varchar2) AS
BEGIN
p_return := 'Prozedur ''DoSomething'' wurde mit Parameter ' || p_param1 || ' aufgerufen';
END;

Serializable[] params = [ "Parameter 1"]
Class[] classes = [ String.class ]
Serializable[] ret = LogicScriptUtils.executeProcedure("call evijet.DoSomething(?,?)", params, classes);

Ein weiteres Beispiel mit Tabellen OUT-Parameter für Oracle: Hier wird ein eigenes Package mit einer Prozedur und einem Tabellen-Datentyp definiert. Dieser wird als OUT Parameter der Prozedur verwendet.

SQL

--header
create or replace PACKAGE my_package AS
    TYPE my_record_type IS RECORD (
            my_result_value1 VARCHAR2(255),
            my_result_value2 VARCHAR2(50)
    );
    TYPE my_array_type IS
        TABLE OF my_record_type INDEX BY PLS_INTEGER;
    PROCEDURE my_procedure (
        myParam1    VARCHAR2,
        myParam2    DATE,
        datalist    OUT my_array_type
    );
END;

--body
create or replace PACKAGE BODY my_package AS

    PROCEDURE my_procedure (
        myParam1    VARCHAR2, --not used, just for demonstration
        myParam2    DATE,     --not used, just for demonstration
        datalist    OUT my_array_type
    ) IS
    BEGIN
		SELECT 
			ac.Subject,
			coPe.pk
        BULK COLLECT
        INTO datalist
		FROM
				Activity ac
			JOIN ContactPerson coPe ON coPe.Pk = ac.DefaultContactPerson
		ORDER BY
			ac.CreateDate DESC;
			
    END my_procedure;

END;

Beim Aufruf der Prozedur muss der vollqualifizierte Name des oben definierten Datentyps an executeProcedure übergeben werden. Als Ergebnistyp kann dann java.sql.Array verwendet werden. zu beachten ist das "{ ... }" um den Aufruf "call MY_PACKAGE.MY_PROCEDURE(?,?,?)".

SQL

Serializable[] parameter = ['601070015000', DateUtils.parseDate('01012020', 'ddMMyyyy')];
ScriptUtils.debug("Eingabe Parameter: $parameter");
Class[] resultTypes = [java.sql.Array.class];
ScriptUtils.debug("Erwarteter Ausgabetype: $resultTypes");
Serializable[] prcReturn = SQLUtils.executeProcedure("{call MY_PACKAGE.MY_PROCEDURE(?,?,?)}", parameter, resultTypes, new String[]{"MY_PACKAGE.MY_ARRAY_TYPE"});

if(prcReturn.length == 1)
{
  java.sql.Array array = prcReturn[0];
  java.sql.Struct[] structs = array.getArray();
  String[] column_names = new String[]{"my_result_value1", "my_result_value2"};
  for(int row = 0; row < structs.length; row++)
  {
    ScriptUtils.debug("Zeile $row:");
    java.sql.Struct struct = structs[row];
    Object[] values = struct.getAttributes();
    for(int column = 0; column < values.length; column++)
    {
      ScriptUtils.debug(column_names[column] + ": " + values[column]);
    }
  }
}

Beim Aufruf einer SQL Prozedur ist dringend auf die Ein- / Ausgabeparameter zu achten. Gibt die Prozedur beispielsweise keinen Wert zurück, so darf auch keine Klasse für den Rückgabewert angegeben werden.

SQL

Serializable[] params = [ "Parameter 1"]
Class[] classes = []
executeSQLProcedure("exec <Name der Prozedur>", params, classes);

Falls hier ein Fehler unterläuft, kann die Prozedur nicht ausgeführt werden.