CREATE TABLE "DBA"."z_202210_3value" (
"value" VARCHAR(222) NULL,
"jieguo_value" VARCHAR(33) NULL,
"ID" INTEGER NULL DEFAULT AUTOINCREMENT
) IN "system";
CREATE TABLE "DBA"."z_202210_4_chajie" (
"value" VARCHAR(30) NULL,
"yuanzhi_value" VARCHAR(30) NULL,
"ID" INTEGER NULL DEFAULT AUTOINCREMENT
) IN "system";
INSERT INTO "DBA"."z_202210_3value" ("value","jieguo_value","ID") VALUES('27671245Y20221103JL001JL',NULL,1);
INSERT INTO "DBA"."z_202210_3value" ("value","jieguo_value","ID") VALUES('27671245Y20221103JL002JL',NULL,2);
INSERT INTO "DBA"."z_202210_3value" ("value","jieguo_value","ID") VALUES('27671245Y20221103JL003JL',NULL,3);
INSERT INTO "DBA"."z_202210_3value" ("value","jieguo_value","ID") VALUES('27671245Y20221103JL004JL',NULL,4);
--run SQL
BEGIN
DECLARE cur_employee CURSOR FOR
SELECT value
FROM z_202210_3value;
DECLARE name CHAR(40);
declare @i int;
select 1 into @i ;
OPEN cur_employee;
lp:
LOOP
FETCH NEXT cur_employee into name;
WHILE ( @i ) <= len( name) LOOP
INSERT INTO "DBA"."z_202210_4_chajie" ("value","yuanzhi_value" )
select substring( name,@i,1),name;
set @i=@i+1;
end loop;
IF SQLCODE <> 0 THEN LEAVE lp END IF;
END LOOP lp ;
CLOSE cur_employee;
end
--According to the meaning in the help description, but this execution is an infinite loop |
The test on SQLCODE has to immediately follow the FETCH statement, otherwise SQLCODE will return the status of the last statement before this check, and therefore you don't get the expected SQLSTATE_NOTFOUND warning. That being said, I generelly suggest to use the much simpler FOR statement - which is a "cursor loop in one-go". Note, it however uses WITH HOLD implicitly. |
