Topic
  • 6 replies
  • Latest Post - ‏2012-12-14T16:19:00Z by krmilligan
Mauri_Claudio
Mauri_Claudio
20 Posts

Pinned topic Declaring subroutines within an enclosing stored procedure.

‏2012-11-22T07:07:07Z |
Hi to everybody,

I'm just wondering if declaration of 'subroutines' is supported in DB2 for i. What I mean is something
similar to this:

CREATE PROCEDURE ALFA(....)
LANGUAGE SQL
P1: BEGIN
  • MAIN BODY

CREATE PROCEDURE INNERPROC(....)
END P1.
Updated on 2012-12-14T16:19:00Z at 2012-12-14T16:19:00Z by krmilligan
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-22T07:21:03Z  
    I'm really sorry, I unintentionally sent this post before completing !
    What i'm asking is if it's possible to write this pseudo-code:

    
    CREATE PROCEDURE ALFA (....) LANGUAGE SQL P1 :BEGIN -- inner the body, declare another stored locally visible CREATE PROCEDURE BETA (...) LANGUAGE SQL P2: BEGIN   END P2; ---- CALL BETA(....);   END P1
    


    Thank you for your help.
  • krmilligan
    krmilligan
    450 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-26T19:47:22Z  
    No, that's not possible. You can nest BEGIN END compound statements within a single stored procedure, but not sure how readable that will be.
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-27T06:44:18Z  
    No, that's not possible. You can nest BEGIN END compound statements within a single stored procedure, but not sure how readable that will be.
    Ok, thanks for your reply and explanation. My question arose since I need a mean to keep together stored procs that are logically dependent on each other. I tought that defining subroutines just inside the body of the "main" stored proc would achieve that.
    Is there anything similar to the concept of "package" in DB2 for i ?

    Thank you again for your support.
  • B.Hauser
    B.Hauser
    264 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-27T08:20:47Z  
    Ok, thanks for your reply and explanation. My question arose since I need a mean to keep together stored procs that are logically dependent on each other. I tought that defining subroutines just inside the body of the "main" stored proc would achieve that.
    Is there anything similar to the concept of "package" in DB2 for i ?

    Thank you again for your support.
    Why not simply calling the dependent stored procedures from the first one?

    Birgitta
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-11-27T08:35:18Z  
    • B.Hauser
    • ‏2012-11-27T08:20:47Z
    Why not simply calling the dependent stored procedures from the first one?

    Birgitta
    Hi Birgitta,

    that's of course a feasible solution,and works well. Actually I deploy all stored procedures in a "reserved" schema, and in stored procs I use dynamic SQL to address any other schema / library I may need on my i5 system. But as times goes by, number of stored increases, and, in general, they are all uncorrelated each others. It would be nice if it were possible to group them in "packages". I came from Java world, where using packages is a very neat way to separate classes. I'm searching - if it exists - for a similar mean, but applied to stored.
  • krmilligan
    krmilligan
    450 Posts

    Re: Declaring subroutines within an enclosing stored procedure.

    ‏2012-12-14T16:19:00Z  
    Hi Birgitta,

    that's of course a feasible solution,and works well. Actually I deploy all stored procedures in a "reserved" schema, and in stored procs I use dynamic SQL to address any other schema / library I may need on my i5 system. But as times goes by, number of stored increases, and, in general, they are all uncorrelated each others. It would be nice if it were possible to group them in "packages". I came from Java world, where using packages is a very neat way to separate classes. I'm searching - if it exists - for a similar mean, but applied to stored.
    DB2 for i does not yet support packages for SQL stored procedures.