While Loop CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson, we learn to enhance the power of our queries using the While loop in CockroachDB. This lesson will include but not be limited to:

  • What? What does “WHILE” do and what is the syntax?
  • Why and how? Why and how do we use a “WHILE” loop and where/when can we use it in the SQL we write?

Prerequisites

  • Knowledge of how to execute SQL statements, either with CockroachDB (or Postgres, MS SQL Server, MySQL, etc.) alone, using Dbeaver or some other database administration tool, or even by using a coding language (like Python, Node, PHP, Java, etc.) that provides a connection to your database as well as a method for sending it SQL (or T-SQL) commands, to make changes to a database.
  • Beginner level understanding of basic SQL commands, including SELECT, FROM, and INSERT.

What is the “WHILE” loop?

In CockroachDB, the WHILE loop structure involves a condition and loop, much like a FOR loop.

Syntax of the While loop

While loop syntax looks like this:

1
2
3
4
WHILE [condition]
    LOOP
        [statements TO run]
    END LOOP

Note: In some other database brands, including Microsoft’s MS SQL Server, the syntax of a WHILE BEGIN END loop is as follows:

1
2
3
4
WHILE [condition]
    BEGIN
        [statements TO run]
    END

Note: In MySQL, the WHILE LOOP syntax is:

1
2
3
4
WHILE [condition]
    DO
        [statements TO run]
    END WHILE

How to use WHILE in SQL

It is important to consider that while creating an SQL statement, a WHERE or HAVING clause will usually be sufficient for limiting which records are returned or changed. Sometimes, a WHILE LOOP will add extra complexity to your code or SQL. So we advise you to get in the habit of thinking to yourself, “Can I accomplish my goal with a HAVING statement or WHERE statement instead of using a WHILE LOOP?” In the rare situations where a HAVING or WHERE clause will not suffice for your purposes, a WHILE loop might be what is best to use. Let us now examine a few use cases.

Number counting with WHILE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Declare vars
DECLARE
intBegin INT := 0;
intIncrement INT := 2;
intCurrent INT := 2;
intEndAt INT := 12;
-- Tell Cockroach your program scope, within a BEGIN container.
BEGIN
    --  Define our nested LOOP..END LOOP container.
    WHILE intCurrent <= intEndAt
        LOOP
            -- Code to be run. Here we are doing two things:
            -- (1) Increment our i_current variable.
            intCurrent := intCurrent + intIncrement;
            -- (2) Printing the value of intCurrent.
            RAISE NOTICE intCurrent;
            -- Misc information: in some other forms of SQL, we use "Print" instead of "Raise Notice".
        END LOOP;
END;

Analysis

  • First we DECLARE our program variables to use in our WHILE loop, such as intBegin, intIncrement, intCurrent, and intEndAt. Hopefully, these variable names tell you all you need to know about their purpose.
  • Next we placed a BEGIN statement to delineate the beginning point of our “program”.
  • Then we define our loop type as WHILE, along with its condition for exiting the loop. As long as this condition evaluates to true, the commands between LOOP and END LOOP will be executed.

Executing the above code will give the following results:

1
2
3
4
5
6
2
4
6
8
10
12

Now that you have played with an example of a basic use of the WHILE loop for Cockroach, we shall modify a CockroachDB database’s records.

Fill table with consecutive numbers

Now, we will use our basic understanding of the WHILE loop to add data to a Cockroach table. We’ll start by examining a case where we want to fill rows with a set of numbers.

First, we’ll set up a table named “tblTest” using CockroachDB’s CREATE TABLE statement:

1
2
3
4
5
-- CREATE TABLE establishes a new table, which has no data in it yet.
CREATE TABLE tblTest (
    -- Tells the new table we want it to have one column called "idNewNum", which is of the "int8" (integer) data type.
    idNewNum int8
);

Important information: If you do not have autocommit turned on, you may need to issue a COMMIT command. If you have not used COMMIT with ROLLBACK, we recommend you explore and learn about these statements.

When we execute the SQL above against our CockroachDB database, we get a new, empty table with one column, idNewNum. Next we will easily fill the first 260 rows of the “idNewNum” column with a number we will increment with a WHILE LOOP.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
intBegin INT := 0;
intIncrement INT := 1;
intCurrent INT := 1;
intEnd INT := 260;
BEGIN
    WHILE intCurrent <= intEnd
        LOOP
            intCurrent := intCurrent + intIncrement;
            -- Misc note: in some other forms of SQL, the ":" you see above is not needed.
            INSERT INTO tblTest (idNewNum)
            SELECT intCurrent;
        END LOOP;
END;

Important thing to remember (yes, again): if you do not have autocommit turned on, you may need to issue a COMMIT command for any changes to the database.

In your new, modified SQL above, the only change we made is to change “intEnd” from 12 to 260, “intIncrement” from 2 to 1, and remove RAISE NOTICE intCurrent, replacing it with INSERT INTO test (idNewNum) SELECT intCurrent; so to insert records into the tblTest CockroachDB table. Specifically, we filled 260 rows of the “idNewNum” column with the numbers 1 through 260.

WHILE Loop SQL results

To make sure we are getting the results we expected, after we execute that query, we can check to see how many rows were created with the following SQL:

1
SELECT COUNT(*) FROM tblTest;

When we execute the above SQL command, CockroachDB will return a count of 260 because we added 260 rows to our previously empty tblTest table.

Conclusion

In this document we learned some uses of WHILE looping in Cockroach SQL queries. We also explored some examples where one may need to use this type of looping construct as opposed to something like a FOR loop. With the potential complexity and number of possibilities for WHILE and other loops, we strove to keep this lesson as simple as possible, while throwing in some related subtopics you are encouraged to investigate. While this lesson was created specifically for users of CockroachDB and not specific to any coding language, we saw justification for making it clear that the WHILE LOOP structure changes some between the different popular relational database systems in use today.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.