The COLLECT function takes a query type variable as an argument and returns an array of records, which contains the result of executing the SELECT statement represented by the query type variable.

Example

create table t(id int, name varchar(30));
insert into t values(1, "red"), (2,"green");
create table output_log(msg varchar(70));

delimiter //
create or replace procedure p() as
declare q query(id int, name varchar(30)) = select * from t;
declare a array(record(id int, name varchar(30)));
declare _id int;
declare _name varchar(30);
begin
  a = collect(q);
  for x in a loop
     _id = x.id;
     _name = x.name;
     insert into output_log values(concat("[", _id, ", ", _name, "]"));
  end loop;
end
//
delimiter ;

call p();

select * from output_log;

Result:

+------------+
| msg        |
+------------+
| [1, red]   |
| [2, green] |
+------------+

Remarks

Expressions of the form record.field for a record type value cannot be substituted directly into an SQL statement in a stored procedure. To include a record field’s value in an SQL statement, assign the value into a variable, and use the variable in the SQL statement instead. This approach is used in the example above, with the variables _id and _name.

MemSQL does not support cursors. To get the equivalent effect of a read-only cursor, use COLLECT and iterate over the values in the returned array in a loop. The array may be processed forwards, backwards, or in an arbitrary order.

Because all the values returned by a query using COLLECT must be accumulated in memory in an array, it’s good practice to avoid using COLLECT on queries that return very large numbers of values, to avoid running out of memory. The practical limit on the number of values returned depends on your hardware and application.

Related Topics

• CREATE PROCEDURE: The CREATE PROCEDURE command creates a stored procedure.

Was this Article Helpful?

Thanks For Your Feedback!