Question & Answer
Question
Can I use control and iteration statements in stored procedures?
Answer
You can use a variety of conditional structures to control the execution flow of stored procedures. There are also several iterative statements that are valid. See the NPS Stored Procedures Developer's Guide for a more complete description of NZPLSQL control structures.
The following example illustrates the use of control and iteration statements in a stored procedure:
CREATE OR REPLACE PROCEDURE my_proc1() RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
number1 INT;
number2 INT;
number3 INT;
BEGIN
number1 := 0;
number3 := 0;
WHILE number1 < 100 LOOP
number2 := number1 * 10;
IF number1 < 10 THEN
INSERT INTOmy_proc1_tab VALUES (number1, number2, number1 + number2);
ELSIF number1 > 90 THEN
INSERT INTOmy_proc1_tab VALUES (number1, number2, number1 + number2);
ELSE
INSERT INTO my_proc1_tab VALUES (number1, number2, number1 + number2);
END IF;
number1 = number1 + 10;
RAISE NOTICE 'Number is = %', number1;
END LOOP;
FOR i IN 1..100 LOOP
EXECUTE IMMEDIATE 'UPDATE my_proc1_tab SET col3=0 WHERE col1=' || i ;
IF ROW_COUNT > 0 THEN
RAISE NOTICE 'Rows updated in last update query are %.', ROW_COUNT;
number3 = number3 + ROW_COUNT;
END IF;
END LOOP;
RETURN number3;
END;
END_PROC;
The following is the result of the procedure:
host(admin)=> CALL my_proc1();
NOTICE: Number is = 10
NOTICE: Number is = 20
NOTICE: Number is = 30
NOTICE: Number is = 40
NOTICE: Number is = 50
NOTICE: Number is = 60
NOTICE: Number is = 70
NOTICE: Number is = 80
NOTICE: Number is = 90
NOTICE: Number is = 100
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
NOTICE: Rows updated in last update query are 1.
my_proc1
----------
9
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ812702
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21570826