我有这个功能:
创建或替换功能Tablereturn(SWITCHER INTEGER) Returns Table(测试) CHAR(9),测试员整数)语言SQL 包含SQL 确定性没有外部操作开始 DECLARE SELECT1,SELECT2 VARCHAR(1024); set select1 =选择测试,从测试仪开始测试; set select2 ='SELECT DUMMY,JAR,BRAND FROM TESTTAB'; IF(SWITCHER ='1')然后返回select1; ELSEIF(SWITCHER ='2')然后返回select2; END IF; 返回表; END @打电话是
从TABLE(Tablereturn(1))@或
从TABLE(Tablereturn(2))@ 中选择JAR / pre>问题是,它不起作用。编译器说,返回后,意外的令牌 SELECT1在那里。 我希望能够将其作为表调用,并根据需要从调用中选择值。 我不能仅仅将它作为选择返回的过程来调用,因为我需要将选择作为表使用,并在更大的选择中更改返回的输出。我可以在函数中立即执行吗?
任何想法吗?另一个问题是,如何使函数返回不同的表? select2返回3个值,而select1仅返回2。
感谢您的帮助。
解决方案RETURN语句必须是该函数的最后一个语句。有一类称为流水线的函数,您可以在其中使用如果否则,则逻辑。像这样:
创建或替换功能TEST_PIPELINED(P_CHOICE INT)返回表(R_COL1 VARCHAR(128),R_COL2 VARCHAR(128))开始 DECLARE SQLSTATE CHAR(5); DECLARE L_COL1 VARCHAR(128); DECLARE L_COL2 VARCHAR(128); S1的c1游标; 如果P_CHOICE = 1,则从选择COLNAME中准备S1,从SYSCAT中使COLNO。仅从前10行中获取列; ELSE 从 SELECT TABNAME中准备S1,从SYSCAT中拥有所有者。TABLES仅获取前10行。 END IF; OPEN c1; L1:循环将c1存入L_COL1,L_COL2; IF SQLSTATE<>’00000’THEN LEAVE L1;万一; PIPE(L_COL1,L_COL2); END LOOP L1; CLOSE c1; 返回; END @
Hi I have this function:
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@Calling would be
select TEST from TABLE(Tablereturn(1))@or
select JAR from TABLE(Tablereturn(2))@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?
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.
Thank you for your help.
解决方案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
发布评论