Implement cursor in BigQuery

Cursor: The only thing that moves faster than my mind when I’m trying to type.

Disclosure: All opinions expressed in this article are my own, and represent no one but myself and not those of my current or any previous employers.

A cursor in a database is a temporary work area that stores data from a database table. It allows you to iterate through the data in the table one row at a time. Cursors are often used in database applications to perform tasks such as updating or deleting data or generating reports.

--these are randomly written sqls and has no releveance of any result . 

DECLARE countRows INTEGER;
DECLARE flag INTEGER;
DECLARE comp_id INTEGER;

DECLARE etype VARCHAR(100);
DECLARE ename VARCHAR(10000);
DECALRE eemail VARCHAR(100);


-- below we are declaring the cursor
DECLARE sampleAccountCur CURSOR FOR
SELECT name, type,email
FROM employee_account
WHERE type  NOT IN (SELECT department_type FROM employee_department)
ORDER BY 1,2;

-- open the cursor and loop through it.
OPEN sampleAccountCur;

   SET countRows = ACTIVITY_COUNT;
   SET flag = 1;
   SET comp_id = 0;
   REPEAT
      FETCH
         sampleAccountCur
      INTO
          ename
         ,etype
         ,eemail
      ;
   
   update employee_project set type = etype, email=eemail ;
   
   -- there may be n number of sqls can run inside cursor
   --- sql 1
   -- sql 2
   ---sql 3
  
  UNTIL countRows <= 0

  END REPEAT;

  CLOSE sampleAccountCur;

The above SQL shows a cursor(in Teradata), with the name — sampleAccountCur.

In the above sql, the first part is declaring the cursor:

-- below we are declaring the cursor
DECLARE sampleAccountCur CURSOR FOR
SELECT name, type,email
FROM employee_account
WHERE type  NOT IN (SELECT department_type FROM employee_department)
ORDER BY 1,2;

Once declared, the next part is to open and execute the cursor.

Similarly, we can do the same thing in bigquery, but there is no direct way to convert the cursor in bigquery, We can implement the same logic with the “for” loop in bigquery.

Let’s see how we can implement the above SQL in bigquery :

DECLARE countRows INT64;
DECLARE flag INT64;
DECLARE comp_id INT64;

DECLARE etype STRING;
DECLARE ename STRING;
DECALRE eemail STRING;


FOR sampleAccountCur IN ( SELECT name, type,email
FROM employee_account
WHERE type  NOT IN (SELECT department_type FROM employee_department)
ORDER BY 1,2)

  DO
   SET countRows = @@row_count;
   SET flag = 1;
   SET comp_id = 0;
 BEGIN
 
-- we can use clumn name with reference of cursor name
    SET ename = sampleAccountCur.name;
    SET etype = sampleAccountCur.type;
   SET eemail = sampleAccountCur.email;
 
  update employee_project set type = etype, email=eemail ;
  
  -- there may be n number of sqls can run inside cursor
  --- sql 1
  -- sql 2
  ---sql 3
 
 END;
 END FOR;

In the above SQL, we put the declared part in the for loop(inside braces) with the name “sampleAccountCur”:

FOR sampleAccountCur IN ( SELECT name, type,email
FROM employee_account
WHERE type  NOT IN (SELECT department_type FROM employee_department)
ORDER BY 1,2)

This will execute the SQL in braces and the “for” operator helps to loop through the data coming from SQL.

We need to refer to the column name with cursor name reference to assign or use inside the loop:

SET ename = sampleAccountCur.name;
SET etype = sampleAccountCur.type;
SET eemail = sampleAccountCur.email;

And, inside the loop, it is the same way as it was there in 1st cursor SQL.

This is not the only way we can implement the cursors but I found it the simplest way to implement.

I hope you learn something new , happy coding guys.