Procedure mit Resultset

[mcGeorge]

Hallo @all,

ich bin nicht der Experte und muss vielleicht ein paar dumme Frage stellen:

Wenn ich das richtig verstanden habe ist es so, dass StoredProcedures (SP) wie bei der "normalen" Programmierung keinen Rückgabewert haben.
Woanders hatte ich gelesen, dass eine SP einen Resultset als Ergebnis liefern kann.

Was ist richtig und wie geht es?

Vielleicht zu meinem Problem:
Auf unsere Unternehmensdatenbank soll ein Schnittstellenserver zugreifen. Die Schnittstellenfirma will alle Daten durch den Aufruf von SPs erhalten.
Ich dachte, hört sich einfach an, da ich die SQL-Statemens zur Abfrage alle kenne und brauche die nur noch in eine SP packen, fertig.

Leider falsch! Wie geht das nun richtig?

Ich weiss nicht, wie so eine SP aussehen muss, damit die z.B. mit einem Varchar2-Parameter aus Java (die Sprache des Schnittstellenservers) aufgerufen werden kann und eine Tabelle als Result zurückliefert.

Hat jemand vielleicht ein Beispiel zu Hand?

(Oder kann man vielleicht Funktionen auch aufrufen??)

[Ulrich Vaid]

- Ich nehme an, du redest von Oracle ?
- SP können selbstverständlich Returnwerte haben, unter anderem auch Tabellen als Returnwert.

Beispiel : (Nur Interface einer Procedur in einem Package...)

PROCEDURE get_workrecord (
nsession_id IN M_GWKSERVICES.sys_session.sessionkey%TYPE,
nsector_id IN app_workrecord.sector_id%TYPE,
nsurveillance_id IN app_workrecord.surveillance_id%TYPE,
noffice_id IN app_workrecord.office_id%TYPE,
nnumberoflastrecords IN NUMBER,
rc OUT ref_cursor
);


- Es gibt 5 Eingabewerte in desem Fall, die Type werden dabei an tabellenfelder gebunden
- Es wird ein RefCursor zurückgegeben (rc) .Dies ist das Ergbnis einer SQL Query, welcher in der Procedure aufgerufen wird

Wenn du ein konkretes Beispiel brauchst, melde dich einfach nochmal..


Gruss

[mcGeorge]

Nach vielen Versuchen bin ich zu folgendem Ergebnis gekommen:

CREATE OR REPLACE PACKAGE pkg_adv_job AS
TYPE gencurtype IS REF CURSOR;
PROCEDURE adv_job (v_typ IN VARCHAR2, v_refcursor OUT pkg_adv_job.gencurtype);
END pkg_adv_job;
/

CREATE OR REPLACE PACKAGE BODY pkg_adv_job AS
PROCEDURE adv_job (v_typ IN VARCHAR2, v_refcursor OUT pkg_adv_job.gencurtype) IS
BEGIN
OPEN v_refcursor FOR SELECT * FROM mytable m
WHERE m.typ = v_typ AND m.stornierer IS NULL;
END adv_job;
END pkg_adv_job;
/


Das lässt sich fehlerfrei übersetzen!? Ist das so richtig?

Ich kann das nicht testen, da ich kein Java-Prgrammierer bin und nicht weiß, wie ich das testen kann.

[Ulrich Vaid]

- Sieht soweit gut aus. Testen kannst du es auch im SQL-Plus oder im SQLDeveloper (Freies Tool von Oracle)

[SiegfriedEhlert]

Ref Cursor

Hallo mcGeorge,

unter den Gesichtspunkten Performance und Skalierbarkeit ist die Entscheidung, die Datenbankzugriffe als PL/SQL Packages zu realisieren, genau richtig. Da Du alle SQL-Statements kennst, solltest Du sie als statisches SQL codieren und im Definer-Mode (Standard) kompilieren. Das bringt die höchste Ausführungsgeschwindigkeit und erzeugt die wenigsten Latches, da die Statements schon während der Kompilierung geparsed werden.

Ausführung und Test sind eigentlich recht einfach, hier ein Beispiel mit SQL*Plus:

SQL> CREATE Table TBL(
2 ID integer not NULL
3 , TXT varchar2(100) NULL
4 )
5 /

Tabelle wurde erstellt.

SQL> BEGIN
2 for i in 1 .. 100 loop
3 INSERT into TBL values( i, 'T' || to_char(i, 'FM009') );
4 end loop;
5 commit;
6 END;
7 /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> CREATE or replace Package DB_INTERFACE is
2 Procedure get_TBL_by_ID_range(
3 p_Lower in TBL.ID%type
4 , p_Upper in TBL.ID%type
5 , p_Result out sys_refcursor
6 );
7 END DB_INTERFACE;
8 /

Package wurde erstellt.

SQL> CREATE or replace Package Body DB_INTERFACE is
2 Procedure get_TBL_by_ID_range(
3 p_Lower in TBL.ID%type
4 , p_Upper in TBL.ID%type
5 , p_Result out sys_refcursor
6 ) is
7 BEGIN
8 open p_Result for
9 SELECT *
10 from TBL
11 where ID between p_Lower and p_Upper
12 ;
13 END get_TBL_by_ID_range;
14 END DB_INTERFACE;
15 /

Package Body wurde erstellt.

SQL> column id format 999
SQL> column txt format A10
SQL> set pagesize 80
SQL> variable rc refcursor
SQL> exec db_interface.get_tbl_by_id_range( 20, 33, :rc )

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> print rc

ID TXT
---- ----------
20 T020
21 T021
22 T022
23 T023
24 T024
25 T025
26 T026
27 T027
28 T028
29 T029
30 T030
31 T031
32 T032
33 T033

14 Zeilen ausgewählt.

SQL>

Die Cursor müssen vom Schnittstellenserver, also im Java-Code geschlossen werden.

Gruß, Siegfried