Oracle PL/SQL到DB2 SQL/PL存储过程迁移：子过程传递方法咨询

Hey there, I’ve walked through tons of PL/SQL to SQL/PL migrations, so let’s break down how to replicate your nested procedure structure in DB2. The biggest shift is that DB2 uses explicit DECLARE PROCEDURE syntax for nested subprocedures, unlike Oracle’s more streamlined inline definition.

Here’s how to adjust your sample code to work natively in DB2:

CREATE OR REPLACE PROCEDURE SP_NOSTRADAMUS_PRODUTO()
LANGUAGE SQL
BEGIN
    -- Declare main procedure variables (DB2 uses INTEGER instead of NUMBER for integer types)
    DECLARE V_EXISTE_TAB INTEGER DEFAULT 0;

    -- Define your nested subprocedure with DECLARE PROCEDURE
    DECLARE PROCEDURE PR_HIRQ_PRODUTO_OR()
    LANGUAGE SQL
    BEGIN
        -- Add your subprocedure logic here
        -- Note: This nested procedure can access V_EXISTE_TAB from the main procedure, just like in Oracle
    END;

    -- Main procedure executable logic goes here
    -- Call the nested subprocedure using DB2's required CALL keyword
    CALL PR_HIRQ_PRODUTO_OR();
END@

Key Syntax Differences to Keep in Mind: #

• Language Explicitness: DB2 requires you to specify LANGUAGE SQL for SQL/PL procedures, whereas Oracle infers this automatically for PL/SQL.
• Variable Initialization: Instead of := for setting initial values, DB2 uses DEFAULT. For numeric types, use INTEGER (for whole numbers) or DECIMAL (for non-integers) instead of Oracle’s generic NUMBER for better type clarity.
• Nested Procedure Definition: You must define nested subprocedures with DECLARE PROCEDURE inside the main procedure’s BEGIN block, before your main executable code runs.
• Calling Subprocedures: Unlike Oracle where you can invoke subprocedures by name alone, DB2 mandates using the CALL keyword to execute nested procedures.

If you need to pass parameters to the nested subprocedure, just add them to the declaration like this:

DECLARE PROCEDURE PR_HIRQ_PRODUTO_OR(IN p_param1 INTEGER, OUT p_param2 VARCHAR(50))
LANGUAGE SQL
BEGIN
    -- Logic using input/output parameters
END;

Then call it with:

CALL PR_HIRQ_PRODUTO_OR(123, @output_var);

内容的提问来源于stack exchange，提问作者Talita Tiense