v A while loop will check the condition first and then executes the block of Sql Statements within it as long as the condition evaluates to true.
Syntax:
WHILE Condition
BEGIN
Statements
END
v Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.
While Loop Example:
To create Table:
create column table "KABIL_PRACTICE"."FACTORIAL" ("NUMBER" integer, "FACTORIAL" double );
To create a Procedure:
create procedure "KABIL_PRACTICE"."WHILE_LOOP_FACTORIAL_PROCEDURE"
(in I integer)
as begin
declare X integer := 2;
declare F double := 1;
while X <= :I do
F := :X * :F;
X := :X + 1;
end while;
insert into "KABIL_PRACTICE"."FACTORIAL" values (I,F);
select * from "KABIL_PRACTICE"."FACTORIAL";
end;
To Call a Procedure:
call "KABIL_PRACTICE"."WHILE_LOOP_FACTORIAL_PROCEDURE"(10);
To Drop Procedure:
Drop Procedure "KABIL_PRACTICE"."WHILE_LOOP_FACTORIAL_PROCEDURE";
Result:
Below is what worked for me:
ReplyDeleteALTER PROCEDURE SP_LIT()
AS
BEGIN
DECLARE count INT;
DECLARE pos INT;
DECLARE value NVARCHAR(100);
value := 'R1,A';
----IF OBJECT_ID('"Comma_Split"') IS NOT NULL THEN
CREATE COLUMN TABLE "OP"
(
----ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (start with 1),
"TransID" VARCHAR(100));
IF LENGTH(:value) > 0 THEN
value := :value || ',';
pos := LOCATE(:value,',',1);
END IF;
WHILE :pos > 0 DO
BEGIN
INSERT INTO OP VALUES (LEFT(:value,:pos-1));
value := RIGHT(:value, LENGTH (:value)-:pos);
pos := LOCATE(:value,',',1);
END;
END WHILE;
SELECT * FROM "OP";
DROP TABLE "OP";
END;
It work. Thanks
ReplyDelete