从 SP 调用同一架构中的存储过程 [英] Calling a stored procedures within the same schema from a SP

问题描述

在编写另一个存储过程时，如何在不指定完整模式名称的情况下调用同一模式中的存储过程.这些是 DB2 中的 SQL PL 过程.

How can I call a stored procedure in the same schema without specifying the full schema name when coding another stored procedure. These are SQL PL procedures within DB2.

第一个 SP:

CREATE PROCEDURE MYSCHEMA.SP_TEST
  LANGUAGE SQL
  BEGIN
  END

创建一个 SP 直接调用此 SP 而不使用架构名称会导致编译错误:

Creating a SP calling this SP directly without a schema name causes a compilation error:

CREATE PROCEDURE MYSCHEMA.SP_TEST2
  LANGUAGE SQL
  BEGIN
    CALL SP_TEST();
  END

它会抛出:

未找到具有兼容参数的类型为PROCEDURE"的名为SP_TEST"的授权例程.SQLCODE=-440, SQLSTATE=42884, DRIVER=3.53.71

No authorized routine named "SP_TEST" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.53.71

直接给出完整的架构名称有效:

Directly giving the full schema name works:

CREATE PROCEDURE MYSCHEMA.SP_TEST2
  LANGUAGE SQL
  BEGIN
    CALL MYSCHEMA.SP_TEST();
  END

但是，如果我移动到不同的架构，我将不得不在整个地方替换那些引用.是否有合适的解决方法或更好的解决方案?

However if I ever move to a different schema I will have to replace that references all over the place. Is there a suitable workaround or nicer solution to the problem?

推荐答案

CURRENT PATH 特殊寄存器用于解析对不合格存储过程和函数的调用.CURRENT SCHEMA 用于解析不合格的对象名称.

The CURRENT PATH special register is used to resolve calls to unqualified stored procedures and functions. CURRENT SCHEMA is used to resolve unqualified object names.

默认情况下，CURRENT PATH 具有 IBM 函数以及您的 AUTHID:

By default, CURRENT PATH has IBM functions plus your AUTHID:

$ db2 "values substr(current path,1,60)"

1
------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","IBJORHOV"

  1 record(s) selected.

您可以使用 SET CURRENT PATH 语句对其进行修改.

You can modify this with the SET CURRENT PATH statement.

当您创建存储过程时，DB2 会在编译时记录 CURRENT PATH 的值，并使用它们来解决存储过程中不合格的存储过程和函数调用.相同的逻辑适用于 CURRENT SCHEMA 和非限定表名.

When you create a stored procedure, DB2 takes note of the value of CURRENT PATH at compilation time and uses them to resolve unqualified stored procedure and function calls within the stored procedure. The same logic applies for CURRENT SCHEMA and unqualified table names.

因此，在存储过程中允许非限定过程和函数调用的正确方法是设置 CURRENT PATH 寄存器，然后创建过程.

So the proper way to allow unqualified procedure and function calls within a stored procedure is to set the CURRENT PATH register and then creating the procedure.

这篇关于从 SP 调用同一架构中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！