在db2中创建一个临时表 [英] Create a temporary table in db2

问题描述

如何在db2中的存储过程中创建临时表？



下面是我的存储过程，我在执行此过程时遇到错误。

How to create a temporary table in stored procedure in db2?

Below is my stored procedure and i'm getting error while executing this Procedure.

DECLARE RTIMID_MATCH INTEGER;
DECLARE PARAMS_VALUE VARCHAR(2500);DECLARE tmp_param varchar(500); DECLARE tmp_seperated_param varchar(500); DECLARE count_val INTEGER DEFAULT 1;
CREATE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE_PARAMS( param_key varchar(500), param_value varchar(500));
DECLARE cursor2 CURSOR FOR select varchar(params) from RTIMD.V3_TICKET_LOG where RTIM_ID = IN_RTIM_ID or TICKET_ID = IN_RTIM_ID order by CREATED;
DECLARE cursor3 CURSOR FOR SELECT VARCHAR(elem, 500) FROM TABLE ( RTIMD.elements(PARAMS_VALUE,',') ) AS t(elem);
DECLARE cursor4 CURSOR FOR SELECT VARCHAR(elem, 500) FROM TABLE ( RTIMD.elements(tmp_param,'=') ) AS t(elem);
DECLARE cursor5 CURSOR FOR SELECT * FROM SESSION.TEMP_TABLE_PARAMS;

OPEN cursor2;
            FETCH FROM cursor2 INTO tmpname;
            WHILE(SQLSTATE = '00000')
                DO
                    SET PARAMS_VALUE = (select replace(replace(replace(replace(tmpname, '''', ''),'{',''), '}' , ''),'=>' , '=') from SYSIBM.SYSDUMMY1);
                    OPEN cursor3;
                        FETCH FROM cursor3 INTO tmp_param;
                        WHILE(SQLSTATE = '00000')
                            DO
                                OPEN cursor4;
                                FETCH FROM cursor4 INTO tmp_seperated_param;
                                WHILE(SQLSTATE = '00000')
                                    DO
                                    IF MOD(count_val,2) = 0 then
                                        update SESSION.TEMP_TABLE_PARAMS set param_value = tmp_seperated_param;
                                    ELSE
                                        insert into SESSION.TEMP_TABLE_PARAMS values (tmp_seperated_param,'');
                                    END IF;
                                    SET count_val = count_val + 1;
                                    fetch from cursor4 into tmp_seperated_param;
                                END WHILE;
                                CLOSE cursor4;
                            fetch from cursor3 into tmp_param;
                        END WHILE;
                    CLOSE cursor3;

                fetch from cursor2 into tmpname;
            END WHILE;
        CLOSE cursor2;

        OPEN cursor5;
            FETCH FROM cursor5 INTO tmpname;
            WHILE(SQLSTATE = '00000')
                DO
                IF mycount = 1 then
                    SET tmpval = tmpname;
                    SET mycount = 2;
                ELSE
                    SET tmpval = tmpval concat ',' concat tmpname;
                    SET mycount = mycount + 1;
                END IF;
                fetch from cursor5 into tmpname;
            END WHILE;
        CLOSE cursor5;
        SET tmpmsg = tmpval;
        DROP TABLE SESSION.TEMP_TABLE_PARAMS;

错误是，

The error is,

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include:  "<SQL statement>".  LINE NUMBER=1.
SQLSTATE=42601

请帮助。在此先感谢。

Please help. Thanks in advance.

推荐答案

看看错误 - 它甚至没有暗示你创建一个临时表从不介意错误，所以你真正要问的是如何我可以解决这个错误。



我目前没有提供DB2（或Oracle），所以我将引导您完成解决方法你自己的问题...



1.查看

Look at the error - it's not even hinting that you are creating a temporary table never mind incorrectly, so what you are really asking is "how can I solve this error".

I haven't got DB2 (or Oracle) to hand at the moment so I am going to walk you through a method of solving the problem for yourself ...

1. Have a look at the line

SET PARAMS_VALUE = (select replace(replace(replace(replace(tmpname, '''', ''),'{',''), '}' , ''),'=>' , '=') from SYSIBM.SYSDUMMY1);

尝试用硬编码值替换它并运行SP。如果它不再是错误，那么你需要仔细检查这行代码并纠正问题 - 我个人不喜欢转义单引号并且更喜欢使用CHR（39）。





2.如果没有错误...编辑你的SP并注释掉（或删除到安全的地方）除DECLARE光标线以外的所有内容。运行。如果错误，请仔细检查您剩下的代码的语法。



2.如果没有错误则开始添加位返回的代码 - 保持代码块的大小合理地小，以便在发生错误时更容易找到错误



基本上将位置放回一块一次，每次都运行SP。当它崩溃时，你知道这是你放回来的最后一件事导致了这个问题。

Try replacing that with a hard-coded value and run your SP. If it no longer errors then you need to carefully examine that line of code and correct the problem - I personally don't like escaping single quotes and prefer to use CHR(39).


2. If it doesn't error ... Edit your SP and comment out (or remove to a safe place) everything other than the DECLARE cursor lines. Run it. If it errors then carefully check the syntax of the code you have left.

2. If it didn't error then start adding bits of the code back in - keep the size of the chunks of code reasonably small to make it easier to locate the error when it does occur

Basically put bits back a piece at a time, run the SP each time. When it falls over you know it was the last thing that you put back that caused the problem.

这篇关于在db2中创建一个临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！