Learning to use execute block in Firebird with a simple example

I haven’t used yet execute block in Firebird, but Im struggling to learn how to use it.

I am trying to learn from a simple example and then will expand to a more complex situation.

Suppose I have 2 tables like below:

+------------+------+
| Clientcode | name |
+------------+------+
|        123 | A    |
|        234 | B    |
+------------+------+

+----------+------------+---------+
| order_id | clientcode |  value  |
+----------+------------+---------+
|       12 |        234 |  10,00  |
|       13 |        123 |  20,00  |
|       14 |        123 |  10,00  |
+----------+------------+---------+

I have the following code.

execute block returns (client integer,amount double)
as
declare variable  client_code integer;
begin
for
select clientcode from clients
into  : client_code
do
begin
select :client_code,sum(value) from orders group by 1
into :client, :order_id;
end
end

Basically, I want for every client in the client table to calculate the sum of the orders in the orders table. I know that it is not necessary execute block in this case, but with a simple example I might be able to understand it better.

+-------------+--------+
| client_code | amount |
+-------------+--------+
|         123 |     30 |
|         234 |     10 |
+-------------+--------+

What is the correct syntax?

Answer

There are a few things missing in your code:

1. You need a where-clause when selecting from orders to limit it to only the rows of the current client
2. You need to output the sum to amount, not to the non-existent variable order_id
3. You need to add suspend to output a row (otherwise an execute block can only produces a single row, with the last values assigned).
4. (optional) You don’t need the variable client_code when client fulfills the same purpose

The resulting code would be something like (note that I used decimal(18,2) instead of double precision, using exact precision numeric types like decimal is a better choice for money values):

execute block returns (client_code integer,amount decimal(18,2))
as
begin
  for select clientcode from clients 
    into client_code
  do
  begin
    select sum(amount) from orders where clientcode = :client_code
      into amount;
    suspend;
  end
end

This is the equivalent of the following query:

select clients.clientcode as client_code, sum(orders.amount) as amount
from clients
left join orders on clients.clientcode = orders.clientcode
group by clients.clientcode

See also this dbfiddle.

Keep in mind, execute block is essentially a Firebird stored procedure that isn’t stored. An execute block with a suspend behaves as a selectable procedure which can produce zero or more rows, and one without suspend behaves as an executable procedure which can produce one row only.