RESULTSET

This topic describes how to use RESULTSET.

Overview

A RESULTSET as a SQL data type, points to the result set of a query.

In order to access the results through the RESULTSET you can iterate over the rows of the RESULTSET to retrive single values.

When using RESULTSET, the query is executed when the query is assigned to the RESULTSET: so this depends on whether the RESULTSET was declared in the DECLARE section or in the BEGIN...END block.

RESULTSETs is particularly useful to use values of a query inside scripting and procedures.

Declare RESULTSET and assign the query

A RESULTSET can be declared either in the DECLARE section or in the BEGIN … END section of the block.

The following syntax declares and assigns a query to the (declared) resultset:

DECLARE
    ...
    result_set_name RESULTSET [DEFAULT (query)];
    ...
BEGIN
    ...
    result_set_name := (query);
END;

-- OR 

DECLARE
    ...
BEGIN
    ...
    LET result_set_name RESULSET [DEFAULT (query) | := (query)];
    ...
END;

Arguments

result_set_name: The RESULTSET name, which follows object naming rules and is unique to the current scope.

query: A valid SQL SELECT statement.

DEFAULT (query): Query that is executed if no other query is assigned to the result_set_name.

Examples

Declare RESULTSET in the DECLARE section

CREATE TABLE IF NOT EXISTS CAR_DEALERS_BP (
  DEALER_ID INTEGER,
  CARS_SOLD INTEGER
);

INSERT INTO CAR_DEALERS_BP (DEALER_ID, CARS_SOLD) VALUES
  (43010,  300),
  (43020,  50),
  (47030,  45),
  (47040,  840);

DECLARE 
  result_set RESULTSET;       
BEGIN 
  result_set := (SELECT * FROM  CAR_DEALERS_BP);  
  FOR row IN result_set DO  
    print(row.DEALER_ID);  
  END;  
END; 

43010
43020
47030
47040

Declare RESULTSET in a BEGIN...END section

CREATE TABLE IF NOT EXISTS CAR_DEALERS_BP (
  DEALER_ID INTEGER,
  CARS_SOLD INTEGER
);

INSERT INTO CAR_DEALERS_BP (DEALER_ID, CARS_SOLD) VALUES
  (43010,  300),
  (43020,  50),
  (47030,  45),
  (47040,  840);

BEGIN 
  LET result_set RESULTSET := (SELECT * FROM CAR_DEALERS_BP);  
  FOR row IN result_set DO  
    print(row.DEALER_ID || ' > ' || row.CARS_SOLD);  
  END; 
END; 

43010 > 300
43020 > 50
47030 > 45
47040 > 840

Build a SQL query dynamically

You can create a SQL query as a string dynamically and then execute the query string using the EXECUTE IMMEDIATE command.

Parameterized query: Use declared variables

In this example, we use a variable declared in the DECLARE section and for which a DEFAULT value has been defined to dynamically determine a query for the RESULTSET:

CREATE TABLE IF NOT EXISTS CAR_DEALERS_BP (
  DEALER_ID INTEGER,
  CARS_SOLD INTEGER
);

INSERT INTO CAR_DEALERS_BP (DEALER_ID, CARS_SOLD) VALUES
  (43010,  300),
  (43020,  50),
  (47030,  45),
  (47040,  840);

DECLARE 
    my_query STRING;  
    my_table STRING DEFAULT 'CAR_DEALERS_BP';  
BEGIN 
    my_query := 'SELECT * FROM ' || :my_table ;  
    LET result_set RESULTSET := (EXECUTE IMMEDIATE :my_query);  
    FOR row IN result_set DO  
      print(row.DEALER_ID || ' > ' || row.CARS_SOLD);  
    END; 
END; 

43010 > 300
43020 > 50
47030 > 45
47040 > 840

Using: Iterate over RESULTSET

Furthermore, it is possible to iterate over the rows of the RESULTSET to retrive single values.

Read Update column in a table, Filter data and create history of rows updates and FOR loop and RESULTSETs for more information.

The syntax to iterate over the rows of a 'RESULTSET' is as follows:

FOR row_name IN result_set_name DO
    print(row_name.attribute_name);
END;

Arguments

row_name: The name of the row variable which will store the values of a row while iterating over the resultset.

result_set_name: The RESULTSET name, which follows object naming rules and is unique to the current scope.

attribute_name: The name of an attribute / column of a resultset. Used to access the value of the iterated row.

RESULTSET as data type limitations

RESULTSETs can only be used in conjunction with scripting.

Although RESULTSET is a data type, please note the following limitations:

• a column can not be declared with data type RESULTSET.

Furthermore, RESULTSETs cannot be directly used as a table: for example, it is not allowed to SELECT from a RESULTSET.