DB2 SQL PL康廷处理程序未捕获错误

41zrol4v 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(197)

为什么异常处理程序在下面的示例中不起作用？

db2 => BEGIN
db2 (cont.) =>   DECLARE YADAMU_INSTANCE_ID VARCHAR(36);
db2 (cont.) =>   DECLARE FUNCTION_DEFINITION VARCHAR(256);
db2 (cont.) =>
db2 (cont.) =>   DECLARE CONTINUE HANDLER FOR SQLSTATE '42884' BEGIN
db2 (cont.) =>     SELECT  LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),8)
db2 (cont.) =>     CONCAT '-'
db2 (cont.) =>     CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
db2 (cont.) =>     CONCAT '-'
db2 (cont.) =>     CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
db2 (cont.) =>     CONCAT '-'
db2 (cont.) =>     CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
db2 (cont.) =>     CONCAT '-'
db2 (cont.) =>     CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000000 )), 'abcdef123456789', '1234567890' ),12)
db2 (cont.) =>     INTO YADAMU_INSTANCE_ID
db2 (cont.) =>     FROM SYSIBM.SYSDUMMY1;
db2 (cont.) =>   END;
db2 (cont.) =>
db2 (cont.) =>   SELECT YADAMU.YADAMU_INSTANCE_ID()
db2 (cont.) =>     INTO YADAMU_INSTANCE_ID
db2 (cont.) => FROM  SYSIBM.SYSDUMMY1;
db2 (cont.) =>
db2 (cont.) =>   SET FUNCTION_DEFINITION = 'CREATE FUNCTION YADAMU.YADAMU_INSTANCE_ID() RETURNS VARCHAR(36) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN ''' CONCAT YADAMU_INSTANCE_ID CONCAT '''';
db2 (cont.) =>   EXECUTE IMMEDIATE FUNCTION_DEFINITION;
db2 (cont.) => END;
db2 (cont.) => /
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0440N  No authorized routine named "YADAMU_INSTANCE_ID" of type "FUNCTION"
having compatible arguments was found.  LINE NUMBER=19.  SQLSTATE=42884
db2 =>

db2

来源：https://stackoverflow.com/questions/72429545/db2-sql-pl-contine-handler-does-not-catch-error

1条答案

按热度按时间

a14dhokn1#

试试这个。
由于每次都要重新创建函数，并且不处理相应的错误，所以当函数存在时，我在函数定义中添加了OR REPLACE子句。注意，复合语句的末尾没有;。

BEGIN
  DECLARE YADAMU_INSTANCE_ID VARCHAR(36);
  DECLARE FUNCTION_DEFINITION VARCHAR(256);

  DECLARE CONTINUE HANDLER FOR SQLSTATE '42884', SQLSTATE '56098' 
  BEGIN
    SELECT  LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),8)
    CONCAT '-'
    CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
    CONCAT '-'
    CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
    CONCAT '-'
    CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4)
    CONCAT '-'
    CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000000 )), 'abcdef123456789', '1234567890' ),12)
    INTO YADAMU_INSTANCE_ID
    FROM SYSIBM.SYSDUMMY1;
  END;

  PREPARE S1 FROM 'SET ? = YADAMU.YADAMU_INSTANCE_ID()';
  EXECUTE S1 INTO YADAMU_INSTANCE_ID;

  SET FUNCTION_DEFINITION = 'CREATE OR REPLACE YADAMU.FUNCTION YADAMU_INSTANCE_ID() RETURNS VARCHAR(36) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN ''' CONCAT YADAMU_INSTANCE_ID CONCAT '''';
  EXECUTE IMMEDIATE FUNCTION_DEFINITION;
END
/

赞(0）回复(0）举报 2022-11-07

我来回答

DB2 SQL PL康廷处理程序未捕获错误

1条答案

相关问题

热门标签

最新问答