How to do a sub procedure in sql / pl

I am doing a procedure migration in pl / sql (oracle) to sql / pl (DB2) and I don't know how to pass a subprocedure to DB2

As I am not a system administrator I cannot change DB2 to be pl / sql compatible

EXAMPLE

create or replace PROCEDURE         "SP_NOSTRADAMUS_PRODUTO" 
AS
V_EXISTE_TAB   NUMBER := 0;
PROCEDURE PR_HIRQ_PRODUTO_OR 
    AS
    BEGIN


END

Solution

If your Db2-server runs on Linux/Unix/Windows, in ANSI SQL PL, if you have a local procedure nested within the body of another SQL PL procedure (or compound statement), then you use the DECLARE keyword to define the local procedure. This is supported by Db2-LUW in versions v10.1 and higher.

If you have very many Oracle PL SQL stored procedures, please discuss with your solution architect (if you have one) and your DBA about the possibility of configuring your Db2 server to support Oracle compatibility . This may save you some money.

You can see the Db2 syntax for SQL PL here (be sure to select the correct version of your Db2-server product in the pull down list).

There are some restrictions on local procedures, so study the documentation carefully. But your local procedure can access variables and objects defined in surrounding block(s) that are in scope.

Your example might look like this in ANSI SQL PL:

create or replace PROCEDURE "SP_NOSTRADAMUS_PRODUTO" 
begin
  declare V_EXISTE_TAB  integer default 0;
  declare PROCEDURE PR_HIRQ_PRODUTO_OR 
  BEGIN
  -- body of pr_hirq_produto_or procedure
  END;
  -- body of sp_nostradamus_produto procedure
END
@