具有不同返回值的函数 DB2

本文介绍了具有不同返回值的函数 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@