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.
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;
+------------+ | msg | +------------+ | [1, red] | | [2, green] | +------------+
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
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
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
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.
- CREATE PROCEDURE: The
CREATE PROCEDUREcommand creates a stored procedure.