query_script_expressions

QueryScript Expressions: conditional truth values

SYNOPSIS

while(expression)
{
  if (expression)
    statement;
}

DESCRIPTION

Expressions are truth valued clauses. QueryScript accepts any valid SQL expression, and adds additional particular cases.

Expressions are used by flow control structures: if-else, while, loop-while.

Standard SQL expressions

Any expression on which SELECT expression IS TRUE can be used as a QueryScript expression. The following are examples of valid expressions:

  • TRUE
  • NULL
  • 0
  • 4 < 5
  • @x < 5
  • (@x = 5) OR (COALESCE(@y, @z) BETWEEN 10 AND 20)
  • @n IN (SELECT name FROM world.City)
  • SELECT COUNT(*) > 100 FROM world.Country WHERE Continent='Africa'

QueryScript valid expressions

In addition to any standard SQL expression, QueryScript also acknowledges the following statements as valid expressions:

  • INSERT [IGNORE]
  • INSERT .. ON DUPLICATE KEY UPDATE
  • UPDATE [IGNORE]
  • DELETE [IGNORE]
  • REPLACE
An expression in the above form is considered to hold a TRUE value, when the number of rows affected by the DML query is non-zero. In particular, the value of ROW_COUNT() is examined.

For example, consider the following: 

mysql> DELETE FROM world.Country WHERE Continent='flatlandia'
Query OK, 0 rows affected (0.00 sec)
The above query does not actually delete any row; hence its truth value is FALSE.

Notes:

  • A standard INSERT makes no sense to use, since it will either succeed (resolving to TRUE) or completely fail, aborting the evaluation. It only makes sense to use INSERT IGNORE or INSERT .. ON DUPLICATE KEY UPDATE.
  • REPLACE always succeeds, and so will always resolve to TRUE. It is included for completeness.

EXAMPLES

DELETE statement as expression; delete all 'Asia' records in small chunks: 

while (DELETE FROM world.Country WHERE Continent = 'Asia' LIMIT 10)
{
  do sleep(1);
}

SELECT and INSERT statements as expressions: 

if (SELECT COUNT(*) > 0 FROM world.Country WHERE Continent = 'Atlantis')
{ 
  INSERT INTO weird_logs VALUES ('Have found countries in Atlantis');
  if (DELETE FROM world.Country WHERE Continent = 'Atlantis')
    INSERT INTO weird_logs VALUES ('And now they''re gone');
}

Simple arithmetic expression: generate Fibonacci sequence: 

var $n1, $n2, $n3, $seq;
set $n1 := 1, $n2 := 0, $n3 := NULL;
set $seq := '';

loop
{
  set $n3 := $n1 + $n2;
  set $n1 := $n2;
  set $n2 := $n3;
  set $seq := CONCAT($seq, $n3, ', ');
}
while ($n3 < 100);

SELECT $seq AS fibonacci_numbers;

+---------------------------------------------+
| fibonacci_numbers                           |
+---------------------------------------------+
| 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144,  |
+---------------------------------------------+

SEE ALSO

if-else, while, loop-while

AUTHOR

Shlomi Noach
 
common_schema documentation