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

asked 03 Nov, 07:08

mfkpie8's gravatar image

mfkpie8
238646872
accept rate: 12%


IF SQLCODE <> 0 THEN LEAVE lp END IF

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.

permanent link

answered 03 Nov, 07:37

Volker%20Barth's gravatar image

Volker Barth
39.1k353534804
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×22

question asked: 03 Nov, 07:08

question was seen: 114 times

last updated: 03 Nov, 07:37