从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屋！