Alexander Schulz wrote :
>I'm trying to write a recursive databasa procedure like this:
>CREATE DBPROC read_komp_ids_rec (IN parentID FIXED(20,0), IN serialID
>FIXED(20,0), IN rec_level FIXED(20,0)) AS
>VAR komponente_child fixed(20,0);
> zeilennummer fixed(20,0);
> tmp fixed(20,0);
>TRY
> DECLARE c1 CURSOR FOR
> SELECT komponente_child,
> zeilennummer,
> FROM dba.komponente_childs
> WHERE komponente_parent = :parentID;
> WHILE $rc<>100 DO BEGIN
> FETCH NEXT c1 INTO :komponente_child,
> :zeilennummer;
> INSERT INTO dba.serial_read_table( serial_id,
> komponente_child,
> zeilennummer )
> VALUES( :serialID,
> :komponente_child,
> :zeilennummer);
> SET tmp=rec_level+1;
> CALL read_komp_ids_rec (:komponente_child, :serialID, :tmp);
> END;
> CLOSE c1;
>CATCH
> IF $rc <> 100 THEN STOP ($rc, 'Unexpected error');
>It seems like the recursion works up to the point where the select
>returns an empty result set. Then it don't step back to the previous
>recursion level, it just stops the procedure.
>The maximum recursion depth in my testcase is 3.
>I tried to remove the TRY/CATCH block but then the procedure never
>returns and there is no cpu load on the server but I had to stop and
>restart the database to make it working again.
The problem is that every recursive call of your procedure uses the same
cursor name. This means, the an inner call of the procedure implicitly
closes the cursor of the outer call.
This explains, why the procedure ends when an inner calls reaches the
end of the cursor.
Unfortunately there is no way to use different cursor names in every
recursive call. You would need dynamic sql having parameters for that,
but this kind of dynamic sql isn't yet implemented.
This means that you can only try to manage the recursion by yourself.
The following proposal follows that idea using a temporary table
as stack :
CREATE DBPROC READ_KOMP_IDS_REC (
IN PARENTID FIXED(20,0),
IN SERIALID FIXED(20,0),
IN REC_LEVEL FIXED(20,0)) AS
VAR KOMPONENTE_CHILD FIXED(20,0);
ZEILENNUMMER FIXED(20,0);
TMP FIXED(20,0);
ROWID CHAR(8) BYTE;
RC INTEGER;
TRY
RC = 0;
IF REC_LEVEL = 0
THEN
CREATE TABLE TEMP.STACK(LEVEL FIXED(20),
KOMPONENTE_CHILD FIXED(20),
ZEILENNUMMER FIXED(20));
INSERT TEMP.STACK SELECT :REC_LEVEL , KOMPONENTE_CHILD, ZEILENNUMMER
FROM SUT.KOMPONENTE_CHILDS
WHERE KOMPONENTE_PARENT = :PARENTID;
WHILE $RC<>100 DO
BEGIN
DECLARE C1 CURSOR FOR SELECT SYSKEY, KOMPONENTE_CHILD, ZEILENNUMMER
FROM TEMP.STACK WHERE LEVEL = :REC_LEVEL;
FETCH C1 INTO :ROWID, :KOMPONENTE_CHILD, :ZEILENNUMMER;
INSERT INTO USER.SERIAL_READ_TABLE( SERIAL_ID, KOMPONENTE_CHILD, ZEILENNUMMER )
VALUES( :SERIALID, :KOMPONENTE_CHILD, :ZEILENNUMMER);
DELETE FROM TEMP.STACK WHERE SYSKEY = :ROWID;
SET TMP=REC_LEVEL+1;
CALL READ_KOMP_IDS_REC (:KOMPONENTE_CHILD, :SERIALID, :TMP);
END;
CATCH
RC = $RC;
CLOSE C1;
IF REC_LEVEL = 0
THEN
DROP TABLE TEMP.STACK;
IF RC <> 0 THEN STOP (RC, 'Unexpected error');
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
