具有不同返回值的函数 DB2 [英] Function with different returns DB2

问题描述

您好，我有这个功能:

 CREATE or replace FUNCTION Tablereturn (SWITCHER INTEGER)
 RETURNS TABLE (Test CHAR(9),
                tester INTEGER
                )
 LANGUAGE SQL
 CONTAINS SQL
 DETERMINISTIC
 NO EXTERNAL ACTION
 BEGIN 

   DECLARE SELECT1, SELECT2 VARCHAR(1024);
   set select1 ='SELECT TEST, TESTER FROM TESTTAB';
   set select2 ='SELECT DUMMY, JAR, BRAND FROM TESTTAB';
 IF (SWITCHER = '1') THEN return select1;
 ELSEIF (SWITCHER = '2') THEN return select2;
  END IF; 
   RETURN TABLE;
   END@

打电话是

select TEST from TABLE(Tablereturn(1))@

或

select JAR from TABLE(Tablereturn(2))@

问题是，它不起作用.编译器说返回意外的令牌SELECT1"后就在那里.我希望能够将其作为表格调用，并根据需要从调用中选择值.我不能只将它作为一个过程调用，并将选择作为返回，因为我需要将选择作为一个表来使用，并在更大的选择中更改返回的输出.我可以在函数中使用 EXECUTE IMMEDIATE 吗?

The problem is, that it doesn't work. Compiler says that after return the unexpected Token "SELECT1" is there. I want to be able to call it as a table and select values as I need them from the call. I can't just call it as a procedure with the select as return, since I need to work with the select as a table and change the returned output in a bigger select. Can I have EXECUTE IMMEDIATE in a function?

有什么想法吗?另一个问题是，我怎样才能让函数返回不同的表?select2 返回 3 个值，而 select1 只返回 2 个.

Any ideas? The other question is, how can I make the function return different tables? The select2 returns 3 values while select1 returns only 2.

感谢您的帮助.

推荐答案

RETURN 语句必须是函数的最后一个语句.有一类称为流水线"的函数，您可以在其中使用if then else"逻辑.像这样:

RETURN statement must be the last statement of the function. There is a class of functions called 'pipelined' where you can use 'if then else' logic. Like this:

CREATE OR REPLACE FUNCTION TEST_PIPELINED(P_CHOICE INT)
RETURNS TABLE (R_COL1 VARCHAR(128), R_COL2 VARCHAR(128))
BEGIN 
  DECLARE SQLSTATE CHAR(5);
  DECLARE L_COL1 VARCHAR(128);
  DECLARE L_COL2 VARCHAR(128);
  DECLARE c1 CURSOR FOR S1;

  IF P_CHOICE=1 THEN
    PREPARE S1 FROM 'SELECT COLNAME, COLNO FROM SYSCAT.COLUMNS FETCH FIRST 10 ROWS ONLY';
  ELSE 
    PREPARE S1 FROM 'SELECT TABNAME, OWNER   FROM SYSCAT.TABLES  FETCH FIRST 10 ROWS ONLY';
  END IF;

  OPEN c1;
  L1: LOOP
    FETCH c1 INTO L_COL1, L_COL2;
    IF SQLSTATE<>'00000' THEN LEAVE L1; END IF;
    PIPE(L_COL1, L_COL2);
  END LOOP L1;
  CLOSE c1;
  RETURN;
END@

这篇关于具有不同返回值的函数 DB2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！