MySQL general purpose stored routines reference guide

back to the homepage

Summary

arrays

for each loops

globals

named parameters

syntax helpers

testing

arrays


function array_create


    function array_create
        p_array_name varchar(50),
        p_array_size int
    --
    creates a new array, 
    with as many rows as stated by p_array_size.
    --
    returns the array_id
   --
   see also:  array_size, array_exists
   --
   module : arrays

function array_copy_complete


    function array_copy_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        on_key_conflict  enum("use_first", "use_second"),
        drop_new         boolean,
        key_regexp       varchar(100),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array. 
    if drop_new is false, then the first array is appended to the
    second array.
    If there is a conflict between keys, they are resolved according to the
    value of on_key_conflict. If "use_first", then the value from the first
    array is used, otherwise, the value from the second array is used.
    If key_regexp or value_regexp are not null, they are used to filter
    the original items, i.e. only items where the array key matches the
    key_regexp expression and the array value matches the value_regexp
    expression are copied.
    if only one of them is not null, the other is ignored.
    --
    returns the array_name

   --
   see also: array_size, array_exists, array_create, array_grep, array_copy
   --
   module : arrays

function array_grep_complete


    function array_grep_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        key_regexp       varchar(100),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array,
    filtering its contents with key_regexp and value_regexp. 
    It is a shortcut for
        array_copy_complete(p_array_name, p_new_array_name, 
            true, "use_second",key_regexp,value_regexp)
    See array_copy_complete for more detail.
    --
    If key_regexp or value_regexp are not null, they are used to filter
    the original items, i.e. only items where the array key matches the
    key_regexp expression and the array value matches the value_regexp
    expression are copied.
    If only one of them is not null, the other is ignored.
    If both are null, then it is like calling array_copy.
    --
    returns the array_name

   --
   see also: see also: array_size, array_exists, array_create, array_grep
   --
   module : arrays

function array_grep


    function array_grep
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array
    where the value matches value_regexp. 
    It is a shortcut for
        array_grep_complete(p_array_name, p_new_array_name, 
            null,value_regexp)
    See array_grep_complete for more detail.
    --
    If value_regexp is null, then it is like calling array_copy.
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create, array_grep_complete
   --
   module : arrays

function array_copy


    function array_copy
        p_array_name     varchar(50),
        p_new_array_name varchar(50)
    --
    creates a new array, copying the contents of an existing array. 
    It is a shortcut for
        array_copy_complete(p_array_name, p_new_array_name, 
            true, "use_second",null,null)
    See array_copy_complete for more detail;
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create
   --
   module : arrays

function array_append


    function array_append
        p_base_array_name     varchar(50),
        p_second_array_name varchar(50)
    --
    appends the contents of p_second_array_name to the end of p_base_array_name. 
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create
   --
   module : arrays

function array_sort_complete


    function array_sort_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        order_by         enum("K","V","VN","VK"),
        order_direction  enum("asc", "desc")
    --
    creates a new array, copying the contents of an existing array
    in the given order, i.e. one of
        - "K"   by key
        - "V"   by value
        - "VN"  by value as numeric
        - "VK"  by value and key
    The value of order_direction determines if the sorting is done 
    ascending ("asc") or descending ("desc").
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create, array_copy, array_sort
   --
   module : arrays

function array_sort


    function array_sort
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
    --
    creates a new array, copying the contents of an existing array
    order by value ascending.
    For more options, use array_sort_complete
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create, array_copy, array_sort_complete
   --
   module : arrays

function array_merge_complete


    function array_merge_complete
        p_first_array_name     varchar(50),
        p_second_array_name    varchar(50),
        p_new_array_name       varchar(50),
        on_key_conflict        enum("use_first", "use_second")
    )
    --
    creates a new array, copying the contents of two existing arrays.
    If there is any key conflict, the key of the first or second array is
    used according to the value of on_key_conflict;
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create, array_copy, array_sort,
            array_sort_complete, array_merge
   --
   module : arrays

function array_merge


    function array_merge
        p_first_array_name     varchar(50),
        p_second_array_name    varchar(50),
        p_new_array_name       varchar(50)
    )
    --
    creates a new array, copying the contents of two existing arrays.
    If there is any key conflict, the key of the first array is
    used.
    To have control on this behaviour, use array_merge_complete
    --
    returns the array_name
   --
   see also: array_size, array_exists, array_create, array_copy, array_sort,
            array_sort_complete, array_merge_complete
   --
   module : arrays

function array_from_list_complete


    function array_from_list_complete
        p_list       text,
        p_array_name varchar(50),
        p_separator  varchar(10)
    --
    creates a new array, or replaces an existing one,
    from the items in a p_separator separated list.
    --
    returns the array_name
   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
   --
   module : arrays

function array_from_list


    function array_from_list
        p_list       text,
        p_array_name varchar(50)
    --
    creates a new array, or replaces an existing one,
    from the items in a comma separated list.
    --
    It is a shortcut to array_from_list_complete(p_list,p_array_name,",");
    --
    returns the array_name
   --
   see also: array_create, array_from_list_complete, 
              array_to_list_complete, array_to_list
   --
   module : arrays

function array_from_pair_list_complete


    function array_from_pair_list_complete
        p_list       text,
        p_array_name varchar(50),
        p_list_separator  varchar(10),
        p_pair_separator  varchar(10)
    --
    creates a new array, or replaces an existing one,
    from the items in a p_list_separator separated list.
    Each item is treated as a pair, with another separator
    telling apart the key from the value. One example of
    such a list is "one => 123, two=> 456, => three => 789"
    --
    returns the array_name
   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
              array_to_pair_list_complete, array_to_pair_list
              array_from_pair_list
   --
   module : arrays

function array_from_pair_list


    function array_from_pair_list
        p_list       text,
        p_array_name varchar(50)
    --
    creates a new array, or replaces an existing one,
    from the items in a comma separated list.
    Each item is treated as a pair, with the symbol "=>" 
    telling apart the key from the value. One example of
    such a list is "one => 123, two=> 456, => three => 789"
    --
    This is just a shortcut for
        array_from_pair_list_complete(p_list,p_array_name,",","=>"); 
    --
    returns the array_name
   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
              array_from_pair_list_complete
   --
   module : arrays

function array_set_value_by_key


    function array_set_value_by_key
        p_array_name  varchar(50),
        p_array_key   varchar(50),
        p_array_value text
    --
    inserts or modifies an array item by key.
    if the array key exists, the original value is modified,
    otherwise, it will be inserted.
    --
    returns the array size
   --
   see also: array_set_value_by_index, array_set_key_by_index
   --
   module : arrays

function array_unshift


    function array_unshift 
        p_array_name    varchar(50),
        p_array_value   text
    --
    inserts a new item at the beginning of an array.
    (used with array_shift implements a queue)
    --
    returns the array size
   --
   see also: array_shift, array_push, array_pop
   --
   module : arrays

function array_push


    function array_push
        p_array_name    varchar(50),
        p_array_value   text
    --
    inserts a new item at the end of an array.
    (used with array_pop implements a stack)
    --
    returns the array size
   --
   see also: array_shift, array_unshift, array_pop
   --
   module : arrays

function array_set_key_by_index


    function array_set_key_by_index
        p_array_name    varchar(50),
        p_array_index   int,
        p_array_key     text
    --
    modifies the key of an existing item accessed by index
    --
    returns the 1 on success, 0 on failure
   --
   see also: array_set_value_by_key, array_set_value_by_index
   --
   module : arrays

function array_set_value_by_index


    function array_set_value_by_index
        p_array_name    varchar(50),
        p_array_index   int,
        p_array_value   text
    --
    inserts or modifies an array item at the given index
    --
    returns the array size
   --
   see also: array_set_value_by_key, array_set_key_by_index
   --
   module : arrays

function array_set


    function array_set
        p_array_name    varchar(50),
        p_array_ndx_key varchar(50),
        p_array_value   text
    --
    inserts or modifies an array item.
    if p_array_ndx_key is a number, it is treated as an index
    and array_set_value_by_key is used.
    If p_array_ndx_key is not a number, then it iss used as a key,
    and array_set_value_by_key is used.
    --
    returns the array size
   --
   see also: array_set_value_by_key, array_set_key_by_index, 
        array_set_value_by_index array_setn
   --
   module : arrays

function array_setn


    function array_setn
        p_array_name    varchar(50),
        p_array_ndx_key varchar(50),
        p_array_value   text
    --
    works exactly like function array_set,
    except that it returns the array_name instead of the
    array size.
    It is useful for concatenating several insertions into 
    a single SQL statement.
        select array_setn( array_setn( array_setn(
                             "test1","keyA", "valueA"),
                                     "keyB", "valueB"),
                                     "keyC", "valueC");
        select array_setn( array_setn( array_setn(
                             "test2",0, "valueA"),
                                     1, "valueB"),
                                     2, "valueC");
    --
    returns the array name
   --
   see also: array_set_value_by_key, array_set_key_by_index, 
        array_set_value_by_index, array_set
   --
   module : arrays

function array_get_value_by_index


    function array_get_value_by_index
        p_array_name    varchar(50),
        p_array_index   int
    --
    retrieves a value from array p_array_name at position p_array_index.
    --
    returns the array value
   --
   see also: array_get_value_by_key, array_get
   --
   module : arrays

function array_get_key_by_index


    function array_get_key_by_index
        p_array_name    varchar(50),
        p_array_index   int
    --
    retrieves a key from array p_array_name at position p_array_index.
    --
    returns the array key
   --
   see also: array_get_value_by_key, array_get, array_get_value_by_index
   --
   module : arrays

function array_to_list_complete


    function array_to_list_complete
        p_array_name    varchar(50),
        p_separator     varchar(10)
    --
    converts the array values into a p_separator separated list
    --
    returns a new list as a string
   --
   see also: array_from_list_complete, array_from_list, array_to_list
   --
   module : arrays

function array_to_list


    function array_to_list
        p_array_name    varchar(50)
    --
    converts the array values into a comma separated list.
    It is a shortcut to array_to_list_complete(p_array_name,",");
    --
    returns a new list as a string
   --
   see also: array_from_list_complete, array_from_list, array_to_list_complete
   --
   module : arrays

function array_to_pair_list_complete


    function array_to_list_complete
        p_array_name         varchar(50),
        p_list_separator     varchar(10),
        p_pair_separator     varchar(10)
    --
    converts the array values into a p_separator separated list
    of pairs, each pair having a key and a value separated by
    p_pair_separator
    --
    returns a new list as a string
   --
   see also: array_from_list_complete, array_from_list, array_to_list
    array_to_pair_list, array_from_pair_list_complete, array_from_pair_list
   --
   module : arrays

function array_to_pair_list


    function array_to_pair_list
        p_array_name    varchar(50)
    --
    converts the array values into a comma separated list of key/value pairs.
    It is a shortcut to array_to_pair_list_complete(p_array_name,",","=>");
    --
    returns a new list as a string
   --
   see also: array_from_list_complete, array_from_list, array_to_list_complete
    array_to_list, array_to_pair_list_complete
   --
   module : arrays

function array_get_value_by_key


    function array_get_value_by_key
        p_array_name    varchar(50),
        p_array_key     varchar(50)
    --
    retrieves a value from array p_array_name according to the value of p_array_key
    (simulates Perl hashes or PHP arrays)
    --
    returns the array value
   --
   see also: array_get_value_by_index, array_get
   --
   module : arrays

function array_get


    function array_get
        p_array_name     varchar(50),
        p_array_ndx_key  varchar(50)
    --
    retrieves a value from array p_array_name.
    If p_array_ndx_key is a number, then array_get_value_by_index is used,
    otherwise it is treated as a key, and array_get_value_by_key is used.
    (simulates Perl hashes or PHP arrays)
    --
    returns the array value
   --
   see also: array_get_value_by_index, array_get_value_by_key
   --
   module : arrays

function array_clear


    function array_clear
        p_array_name    varchar(50),
    --
    removes all items from a given array
    --
    returns 1 on success, 0 on failure (if p_array_name does not exist)
   --
   see also: array_drop
   --
   module : arrays

function array_drop


    function array_drop
        p_array_name    varchar(50),
    --
    removes all items from a given array and deletes it from the array index
    --
    returns 1 on success, 0 on failure (if p_array_name does not exist)
   --
   see also: array_clear
   --
   module : arrays

function array_size


    function array_size
        p_array_name     varchar(50),
    --
    returns the size of a given array (NULL if the array does not exist)
    --
    returns the array size
   --
   see also: array_create, array_exists
   --
   module : arrays

function array_max_index


    function array_max_index
        p_array_name     varchar(50),
    --
    returns the maximum index of a given array (NULL if the array does not exist)
    --
    returns the array maximum index
   --
   see also: array_create, array_exists, array_size
   --
   module : arrays

function array_exists


    function array_exists
        p_array_name     varchar(50),
    --
    returns true if a given array exists, false otherwise.
   --
   see also: array_create, array_size
   --
   module : arrays

function array_pop


    function array_pop
        p_array_name     varchar(50),
    --
    returns the last item in an array, and removes it from the array
    (used with array_push, implements a stack)
   --
   see also: array_push, array_shift, array_unshift
   --
   module : arrays

function array_shift


    function array_shift
        p_array_name     varchar(50),
    --
    returns the first item in an array, and removes it from the array
    (used with array_unshift, implements a queue)
   --
   see also: array_push, array_pop, array_unshift
   --
   module : arrays

procedure array_create


    procedure array_create
        p_array_name varchar(50),
        p_array_size int
    --
    creates a new array, 
    with as many rows as stated by p_array_size.
    --
    returns the array_id
    --
    sets variable @array_create.

   --
   see also:  array_size, array_exists
   --
   module : arrays

procedure array_copy_complete


    procedure array_copy_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        on_key_conflict  enum("use_first", "use_second"),
        drop_new         boolean,
        key_regexp       varchar(100),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array. 
    if drop_new is false, then the first array is appended to the
    second array.
    If there is a conflict between keys, they are resolved according to the
    value of on_key_conflict. If "use_first", then the value from the first
    array is used, otherwise, the value from the second array is used.
    If key_regexp or value_regexp are not null, they are used to filter
    the original items, i.e. only items where the array key matches the
    key_regexp expression and the array value matches the value_regexp
    expression are copied.
    if only one of them is not null, the other is ignored.
    --
    returns the array_name

    --
    sets variable @array_copy_complete.

   --
   see also: array_size, array_exists, array_create, array_grep, array_copy
   --
   module : arrays

procedure array_grep_complete


    procedure array_grep_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        key_regexp       varchar(100),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array,
    filtering its contents with key_regexp and value_regexp. 
    It is a shortcut for
        array_copy_complete(p_array_name, p_new_array_name, 
            true, "use_second",key_regexp,value_regexp)
    See array_copy_complete for more detail.
    --
    If key_regexp or value_regexp are not null, they are used to filter
    the original items, i.e. only items where the array key matches the
    key_regexp expression and the array value matches the value_regexp
    expression are copied.
    If only one of them is not null, the other is ignored.
    If both are null, then it is like calling array_copy.
    --
    returns the array_name

    --
    sets variable @array_grep_complete.

   --
   see also: see also: array_size, array_exists, array_create, array_grep
   --
   module : arrays

procedure array_grep


    procedure array_grep
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        value_regexp     varchar(100)
    --
    creates a new array, copying the contents of an existing array
    where the value matches value_regexp. 
    It is a shortcut for
        array_grep_complete(p_array_name, p_new_array_name, 
            null,value_regexp)
    See array_grep_complete for more detail.
    --
    If value_regexp is null, then it is like calling array_copy.
    --
    returns the array_name
    --
    sets variable @array_grep.

   --
   see also: array_size, array_exists, array_create, array_grep_complete
   --
   module : arrays

procedure array_copy


    procedure array_copy
        p_array_name     varchar(50),
        p_new_array_name varchar(50)
    --
    creates a new array, copying the contents of an existing array. 
    It is a shortcut for
        array_copy_complete(p_array_name, p_new_array_name, 
            true, "use_second",null,null)
    See array_copy_complete for more detail;
    --
    returns the array_name
    --
    sets variable @array_copy.

   --
   see also: array_size, array_exists, array_create
   --
   module : arrays

procedure array_append


    procedure array_append
        p_base_array_name     varchar(50),
        p_second_array_name varchar(50)
    --
    appends the contents of p_second_array_name to the end of p_base_array_name. 
    --
    returns the array_name
    --
    sets variable @array_append.

   --
   see also: array_size, array_exists, array_create
   --
   module : arrays

procedure array_sort_complete


    procedure array_sort_complete
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
        order_by         enum("K","V","VN","VK"),
        order_direction  enum("asc", "desc")
    --
    creates a new array, copying the contents of an existing array
    in the given order, i.e. one of
        - "K"   by key
        - "V"   by value
        - "VN"  by value as numeric
        - "VK"  by value and key
    The value of order_direction determines if the sorting is done 
    ascending ("asc") or descending ("desc").
    --
    returns the array_name
    --
    sets variable @array_sort_complete.

   --
   see also: array_size, array_exists, array_create, array_copy, array_sort
   --
   module : arrays

procedure array_sort


    procedure array_sort
        p_array_name     varchar(50),
        p_new_array_name varchar(50),
    --
    creates a new array, copying the contents of an existing array
    order by value ascending.
    For more options, use array_sort_complete
    --
    returns the array_name
    --
    sets variable @array_sort.

   --
   see also: array_size, array_exists, array_create, array_copy, array_sort_complete
   --
   module : arrays

procedure array_merge_complete


    procedure array_merge_complete
        p_first_array_name     varchar(50),
        p_second_array_name    varchar(50),
        p_new_array_name       varchar(50),
        on_key_conflict        enum("use_first", "use_second")
    )
    --
    creates a new array, copying the contents of two existing arrays.
    If there is any key conflict, the key of the first or second array is
    used according to the value of on_key_conflict;
    --
    returns the array_name
    --
    sets variable @array_merge_complete.

   --
   see also: array_size, array_exists, array_create, array_copy, array_sort,
            array_sort_complete, array_merge
   --
   module : arrays

procedure array_merge


    procedure array_merge
        p_first_array_name     varchar(50),
        p_second_array_name    varchar(50),
        p_new_array_name       varchar(50)
    )
    --
    creates a new array, copying the contents of two existing arrays.
    If there is any key conflict, the key of the first array is
    used.
    To have control on this behaviour, use array_merge_complete
    --
    returns the array_name
    --
    sets variable @array_merge.

   --
   see also: array_size, array_exists, array_create, array_copy, array_sort,
            array_sort_complete, array_merge_complete
   --
   module : arrays

procedure array_from_list_complete


    procedure array_from_list_complete
        p_list       text,
        p_array_name varchar(50),
        p_separator  varchar(10)
    --
    creates a new array, or replaces an existing one,
    from the items in a p_separator separated list.
    --
    returns the array_name
    --
    sets variable @array_from_list_complete.

   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
   --
   module : arrays

procedure array_from_list


    procedure array_from_list
        p_list       text,
        p_array_name varchar(50)
    --
    creates a new array, or replaces an existing one,
    from the items in a comma separated list.
    --
    It is a shortcut to array_from_list_complete(p_list,p_array_name,",");
    --
    returns the array_name
    --
    sets variable @array_from_list.

   --
   see also: array_create, array_from_list_complete, 
              array_to_list_complete, array_to_list
   --
   module : arrays

procedure array_from_pair_list_complete


    procedure array_from_pair_list_complete
        p_list       text,
        p_array_name varchar(50),
        p_list_separator  varchar(10),
        p_pair_separator  varchar(10)
    --
    creates a new array, or replaces an existing one,
    from the items in a p_list_separator separated list.
    Each item is treated as a pair, with another separator
    telling apart the key from the value. One example of
    such a list is "one => 123, two=> 456, => three => 789"
    --
    returns the array_name
    --
    sets variable @array_from_pair_list_complete.

   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
              array_to_pair_list_complete, array_to_pair_list
              array_from_pair_list
   --
   module : arrays

procedure array_from_pair_list


    procedure array_from_pair_list
        p_list       text,
        p_array_name varchar(50)
    --
    creates a new array, or replaces an existing one,
    from the items in a comma separated list.
    Each item is treated as a pair, with the symbol "=>" 
    telling apart the key from the value. One example of
    such a list is "one => 123, two=> 456, => three => 789"
    --
    This is just a shortcut for
        array_from_pair_list_complete(p_list,p_array_name,",","=>"); 
    --
    returns the array_name
    --
    sets variable @array_from_pair_list.

   --
   see also: array_create, array_from_list, 
              array_to_list_complete, array_to_list
              array_from_pair_list_complete
   --
   module : arrays

procedure array_set_value_by_key


    procedure array_set_value_by_key
        p_array_name  varchar(50),
        p_array_key   varchar(50),
        p_array_value text
    --
    inserts or modifies an array item by key.
    if the array key exists, the original value is modified,
    otherwise, it will be inserted.
    --
    returns the array size
    --
    sets variable @array_set_value_by_key.

   --
   see also: array_set_value_by_index, array_set_key_by_index
   --
   module : arrays

procedure array_unshift


    procedure array_unshift 
        p_array_name    varchar(50),
        p_array_value   text
    --
    inserts a new item at the beginning of an array.
    (used with array_shift implements a queue)
    --
    returns the array size
    --
    sets variable @array_unshift.

   --
   see also: array_shift, array_push, array_pop
   --
   module : arrays

procedure array_push


    procedure array_push
        p_array_name    varchar(50),
        p_array_value   text
    --
    inserts a new item at the end of an array.
    (used with array_pop implements a stack)
    --
    returns the array size
    --
    sets variable @array_push.

   --
   see also: array_shift, array_unshift, array_pop
   --
   module : arrays

procedure array_set_key_by_index


    procedure array_set_key_by_index
        p_array_name    varchar(50),
        p_array_index   int,
        p_array_key     text
    --
    modifies the key of an existing item accessed by index
    --
    returns the 1 on success, 0 on failure
    --
    sets variable @array_set_key_by_index.

   --
   see also: array_set_value_by_key, array_set_value_by_index
   --
   module : arrays

procedure array_set_value_by_index


    procedure array_set_value_by_index
        p_array_name    varchar(50),
        p_array_index   int,
        p_array_value   text
    --
    inserts or modifies an array item at the given index
    --
    returns the array size
    --
    sets variable @array_set_value_by_index.

   --
   see also: array_set_value_by_key, array_set_key_by_index
   --
   module : arrays

procedure array_set


    procedure array_set
        p_array_name    varchar(50),
        p_array_ndx_key varchar(50),
        p_array_value   text
    --
    inserts or modifies an array item.
    if p_array_ndx_key is a number, it is treated as an index
    and array_set_value_by_key is used.
    If p_array_ndx_key is not a number, then it iss used as a key,
    and array_set_value_by_key is used.
    --
    returns the array size
    --
    sets variable @array_set.

   --
   see also: array_set_value_by_key, array_set_key_by_index, 
        array_set_value_by_index array_setn
   --
   module : arrays

procedure array_list


    procedure array_list
    --
    prints a list of publicly viewable arrays.
    Private arrays are the ones with a name starting with "_". 
    These are not listed. To see them, use array_full_list.
   --
   see also: array_full_list
   --
   module : arrays

procedure array_show


    procedure array_show
        p_array_name     varchar(50),
    --
    prints all elements of an array.
   --
   see also: array_list
   --
   module : arrays

procedure array_full_list


    procedure array_full_list
    --
    prints a list of all arrays, private first.
    Private arrays are the ones with a name starting with "_"
   --
   see also: array_list
   --
   module : arrays

procedure array_clear


    procedure array_clear
        p_array_name    varchar(50),
    --
    removes all items from a given array
    --
    returns 1 on success, 0 on failure (if p_array_name does not exist)
    --
    sets variable @array_clear.

   --
   see also: array_drop
   --
   module : arrays

procedure array_drop


    procedure array_drop
        p_array_name    varchar(50),
    --
    removes all items from a given array and deletes it from the array index
    --
    returns 1 on success, 0 on failure (if p_array_name does not exist)
    --
    sets variable @array_drop.

   --
   see also: array_clear
   --
   module : arrays

for each loops


procedure for_each_counter_complete


    procedure for_each_counter_complete
        counter_start   INT,
        counter_end     INT,
        counter_delta   INT,
        sql_command     text,
        sql_before      text,
        sql_after       text,
        ba_mode         enum("once","many") 

    --
    executes a given sql_command using a counter,
    starting at counter_start, incrementing it by
    counter_delta units until it reaches counter_end.
    sql_before and sql_after are commands to be executed 
    before and after the main command. If ba_mode is "once",
    then sql_before is executed once, then all the sequence
    of sql_command according to the counter, and finally
    sql_after is executed. If ba_mode is "many", then sql_before
    and sql_after are executed before and after each sql_command
    within the loop.
   --
   User Variables:
   the value of @FOR_COUNTER is used as the counter placeholder
        instead of the default "$N"
   --
   Other variables:
   Each sql command is preprocessed for placeholders, which
    are replaced as follows:
        $N takes the counter value within the loop 
   --
   see also: for_each_counter, for_once, for_each_table_complete,
              for_each_table_value_complete, for_each_array_item_complete
   --
   module : for each loops

procedure for_each_counter


    procedure for_each_counter
        counter_start   INT,
        counter_end     INT,
        counter_delta   INT,
        sql_command     text

    --
    executes a given sql_command using a counter,
    starting at counter_start, incrementing it by
    counter_delta units until it reaches counter_end.
   --
   User Variables:
   the value of @FOR_COUNTER is used as the counter placeholder
        instead of the default "$N"
   --
   Other variables:
   Each sql command is preprocessed for placeholders, which
    are replaced as follows:
        $N takes the counter value within the loop
   --
   see also: for_each_counter_complete, for_once, for_each_table_complete,
              for_each_table_value_complete, for_each_array_item_complete
   --
   module : for each loops

procedure for_once


    procedure for_once
        sql_command     text

    --
    executes a given sql_command 
    It is a shortcut for 
        for_each_counter(1,1,1,sql_command);
    It is useful when you need to execute a query from a
    string and you do not want to use the longer procedure
    of setting a variable, calling "prepare" and "execute".
   --
   see also: for_each_counter_complete, for_each_counter, for_each_table_complete,
              for_each_table_value_complete, for_each_array_item_complete
   --
   module : for each loops

procedure for_each_table_complete


    procedure for_each_table_complete
        db_name         varchar(50), 
        condition_text  varchar(50),
        sql_command     text,
        sql_before      text,
        sql_after       text,
        ba_mode         enum("once","many") 
    --
    executes a given sql_command for each table of a given
    database.
    --
    "condition_text" is a filter to apply to the tables list.
    The filter is against a query to information_schema.tables,
    therefore any condition applicable to such table is acceptable.
    --
    sql_before and sql_after are commands to be executed 
    before and after the main command. If ba_mode is "once",
    then sql_before is executed once, then all the sequence
    of sql_command according to the counter, and finally
    sql_after is executed. If ba_mode is "many", then sql_before
    and sql_after are executed before and after each sql_command
    within the loop.
   --
   User Variables:
           - the value of @FOR_COUNTER is used as the counter placeholder
          instead of the default "$N"
        - @FOR_COUNTER_DELTA changes the counter increment (default: 1)
        - @FOR_DB changes the database placeholder (default: "$D")
        - @FOR_TABLE changes the table placeholder (default: "$T")
        - @FOR_ENGINE changes the engine placeholder (default: "$E")
        - @FOR_TYPE changes the type placeholder (default: "$Y")
        - @FOR_ROWS changes the rows placeholder (default: "$R")
   --
   Other variables:
   Each sql command is preprocessed for placeholders, which
    are replaced as follows:
        - $N takes the counter value within the loop
        - $D takes the database name
        - $T takes the table name
        - $Y takes the table type
        - $E takes the table engine
        - $R takes the table rows 

   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_array_item_complete
              for_each_table
   --
   module : for each loops

procedure for_each_table


    procedure for_each_table
        db_name         varchar(50), 
        condition_text  varchar(50),
        sql_command     text
    --
    executes a given sql_command for each table of a given
    database.
    This is a shortcut for
         for_each_table_complete(
                db_name,
                condition_text,
                sql_command,
                null,null,"once");
    --
    see syntax of for_each_table_complete, for a detailed
    description of how the condition works and how to use
    placeholders and user variables.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_complete, for_each_table_value_complete, 
              for_each_array_item_complete
   --
   module : for each loops

procedure for_each_array_item_complete


    procedure for_each_array_item_complete
        array_name   varchar(50),
        min_index    int,
        max_index    int,
        sql_command  text,
        sql_before   text,
        sql_after    text,
        ba_mode      enum("once","many") 
    --
    executes a given sql_command for each item of a given
    array, starting at min_index and ending at max_index.
    --
    If an array item is null, execution is stopped, unless
    the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set. 
    --
    sql_before and sql_after are commands to be executed 
    before and after the main command. If ba_mode is "once",
    then sql_before is executed once, then all the sequence
    of sql_command according to the counter, and finally
    sql_after is executed. If ba_mode is "many", then sql_before
    and sql_after are executed before and after each sql_command
    within the loop.
   --
   User Variables:
   - the value of @FOR_COUNTER is used as the counter placeholder
          instead of the default "$N"
        - @FOR_ITEM changes the item placeholder (default: "$I")
        - @FOR_ARRAY_CONTINUE_ON_NULL determines whether the loop should
          stop or go on when a NULL item is processed. The default
          behavior is to exit the loop. If this variable is set, then
          the item value is changed to an empty string, and the loop
          continues.
   --
   Other variables:
   Each sql command is preprocessed for placeholders, which
    are replaced as follows:
        - $N takes the counter value within the loop
        - $I takes the item value 
        - $K takes the item key

   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_table_complete
   --
   module : for each loops

procedure for_each_array_item


    procedure for_each_array_item
        array_name   varchar(50),
        min_index    int,
        max_index    int,
        sql_command  text
    --
    executes a given sql_command for each item of a given
    array, starting at min_index and ending at max_index.
    --
    If an array item is null, execution is stopped, unless
    the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set. 
    --
    This is a shortcut for 
        for_each_array_item_complete(
                array_name,
                min_index,
                max_index,
                sql_command,
                null,
                null,
                "once");
    
    See the syntax of for_each_array_item_complete, for a complete
    explanation.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_table_complete
              for_each_array_item_complete for_each_array_item_simple
   --
   module : for each loops

procedure for_each_array_item_simple


    procedure for_each_array_item
        array_name   varchar(50),
        sql_command  text
    --
    executes a given sql_command for each item of a given
    array.
    --
    If an array item is null, execution is stopped, unless
    the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set. 
    --
    This is a shortcut for 
        for_each_array_item_complete(
                array_name,
                0,
                array_max_index(array_name),
                sql_command,
                null,
                null,
                "once");
    
    See the syntax of for_each_array_item_complete, for a complete
    explanation.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_table_complete
              for_each_array_item_complete for_each_array_item
   --
   module : for each loops

procedure for_each_list_item_complete


    procedure for_each_list_item_complete
        list_value   text,
        sql_command  text,
        sql_before   text,
        sql_after    text,
        ba_mode      enum("once","many") 
    --
    executes a given sql_command for each item of a given
    comma-separated list
    --
    It is a shortcut for calling for_each_array_item_complete
    with a temporary array created from the given list.
    --
    See for_each_array_item_complete for the explanation of
    how placeholders and user variables are handled.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_table_complete
              for_each_array_item_complete, for_each_list_item
   --
   module : for each loops

procedure for_each_list_item


    procedure for_each_list_item
        list_value   text,
        sql_command  text
    --
    executes a given sql_command for each item of a given
    comma-separated list
    --
    It is a shortcut for calling for_each_array_item_complete
    with a temporary array created from the given list, and 
    without sql_before or sql_after
    --
    See for_each_array_item_complete for the explanation of
    how placeholders and user variables are handled.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_table_value_complete, for_each_table_complete
              for_each_array_item_complete, for_each_list_item_complete
   --
   module : for each loops

procedure for_each_table_value_complete


    procedure for_each_table_value_complete
        db_name          varchar(50),
        table_name       varchar(50),
        wanted_col1      varchar(50),
        wanted_col2      varchar(50),
        wanted_col3      varchar(50),
        search_condition text,
        sql_command      text,
        sql_before       text,
        sql_after        text,
        done_cond        text,
        ba_mode          enum("once","many") 
    --
    executes a given sql_command for each row of a given
    table, according to a given search_condition.
    --
    Notice that, to overcome a present limitation in cursors syntax,
    the table values are copied to a temporary table and then processed 
    from there. Thus, a loop from a large dataset can have a 
    significant overhead.
    --
    sql_before and sql_after are commands to be executed 
    before and after the main command. done_cond is a condition
    that, if given, is evaluated for each loop, and if its result is true
    it will terminate the loop immediately. It may be any SQL expression
    that will evaluate to true or false. All placeholders are honoured.
    If ba_mode is "once",
    then sql_before is executed once, then all the sequence
    of sql_command according to the counter, and finally
    sql_after is executed. If ba_mode is "many", then sql_before
    and sql_after are executed before and after each sql_command
    within the loop.
   --
   User Variables:
   - the value of @for_counter is used as the counter placeholder
          instead of the default "$N"
        - @for_item1 changes the item1 placeholder (default: "$I1")
        - @for_item2 changes the item2 placeholder (default: "$I2")
        - @for_item3 changes the item3 placeholder (default: "$I3")
   --
   Other variables:
   Each sql command is preprocessed for placeholders, which
    are replaced as follows:
        - $N takes the counter value within the loop
        - $I1 takes the value of wanted_col1
        - $I2 takes the value of wanted_col2
        - $I3 takes the value of wanted_col3

   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_array_item_complete, for_each_table_complete
              for_each_table_value_simple
   --
   module : for each loops

procedure for_each_table_value


    procedure for_each_table_value
        db_name          varchar(50),
        table_name       varchar(50),
        wanted_col1      varchar(50),
        wanted_col2      varchar(50),
        wanted_col3      varchar(50),
        search_condition text,
        sql_command      text
    --
    executes a given sql_command for each row of a given
    table, according to a given search_condition.
    --
    This is a shortcut for
        for_each_table_value_complete (
            db_name, table_name,
            wanted_col1, wanted_col2, wanted_col3,
            search_condition, sql_command,
            null, null, null, "once"); 
    See the documentation regarding for_each_table_value_complete,
    for details on the implementation.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_array_item_complete, for_each_table_complete
              for_each_table_value_complete, for_each_table_value_simple
   --
   module : for each loops

procedure for_each_table_value_simple


    procedure for_each_table_value
        db_name          varchar(50),
        table_name       varchar(50),
        wanted_col       varchar(50),
        search_condition text,
        sql_command      text
    --
    executes a given sql_command for each row of a given
    table, according to a given search_condition.
    --
    This is a shortcut for
        for_each_table_value_complete (
            db_name, table_name,
            wanted_col, null, null,
            search_condition, sql_command,
            null, null, "once"); 
    See the documentation regarding for_each_table_value_complete,
    for details on the implementation.
   --
   see also: for_each_counter, for_once, for_each_counter_complete,
              for_each_array_item_complete, for_each_table_complete
              for_each_table_value_complete, for_each_table_value
   --
   module : for each loops

globals


function table_exists


    function table_exists
        p_db_name     varchar(50),
        p_table_name     varchar(50)
    --
    Returns TRUE if a given table exists, FALSE otherwise.
    Notice that it will return FALSE if given a name of a view
    instead of a table. To get a view, use either view_exists
    or table_or_view_exists
    
   --
   see also: view_exists, table_or_view_exists
   --
   module : globals

function table_or_view_exists


    function table_or_view_exists
        p_db_name     varchar(50),
        p_table_name     varchar(50)
    --
    returns TRUE if a given table or view exists, FALSE otherwise
    
   --
   see also: table_exists, table_or_view_exists
   --
   module : globals

function view_exists


    function view_exists
        p_db_name     varchar(50),
        p_view_name   varchar(50)
    --
    Returns TRUE if a given view exists, FALSE otherwise.
    Notice that it will return FALSE if given a name of a table
    instead of a view. To get a table, use either table_exists
    or table_or_view_exists
    
   --
   see also: table_exists, table_or_view_exists
   --
   module : globals

function routine_exists


    function routine_exists
        p_db_name       varchar(50),
        p_routine_name  varchar(50),
        p_routine_type  enum("procedure", "function")
    --
    returns TRUE if a given routine exists, FALSE otherwise
    
   --
   see also: function_exists, procedure_exists
   --
   module : globals

function function_exists


    function function_exists
        p_db_name        varchar(50),
        p_function_name  varchar(50)
    --
    Returns TRUE if a given function exists, FALSE otherwise.
    Notice that it will return FALSE if given a name of an existing procedure
    instead of a function.
    --
    It is a shortcut for calling 
    routine_exists(p_db_name, p_routine_name, "function");
    
   --
   see also: procedure_exists, routine_exists
   --
   module : globals

function procedure_exists


    function procedure_exists
        p_db_name         varchar(50),
        p_procedure_name  varchar(50)
    --
    Returns TRUE if a given procedure exists, FALSE otherwise.
    Notice that it will return FALSE if given a name of an existing function
    instead of a procedure.
    --
    It is a shortcut for calling 
    routine_exists(p_db_name, p_routine_name, "procedure");
    
   --
   see also: function_exists, routine_exists
   --
   module : globals

function library_user


    function library_user
    --
    Returns the username of the current user.
    Depending on its implementation, it allows for multi-user versions
    of globals and arrays, or for shared version.
    If it returns "all users", then it is the shared version.
    --
    Not to be called directly. It is used internally by routines in
    globals and arrays modules.
    --
    Previously called "array_user", was moved to "globals" to be widely
    available.
    
   module : globals

function global_var_set


    function global_var_set
        p_var_name varchar(50),
        p_value    text
    --
    creates or modifies a global variable p_var_name
    with value p_value.  
    --
    returns the variable value
   --
   see also:  global_var_get, global_var_exists, global_var_drop
   --
   module : globals

function global_var_drop


    function global_var_drop
        p_var_name varchar(50)
    --
    Removes a global variable p_var_name
    --
    returns 1 if the variable existed, 0 otherwise
   --
   see also:  global_var_set, global_var_exists
   --
   module : globals

function global_var_get


    function global_var_get
        p_var_name varchar(50)
    --
    Returns the value of a global variable p_var_name
    --
    returns the variable value, null if variable does not exist
   --
   see also:  global_var_set, global_var_exists
   --
   module : globals

function global_var_exists


    function global_var_exists
        p_var_name varchar(50)
    --
    Returns TRUE if a given global variable exists, FALSE otherwise
    
   --
   see also:  global_var_set, global_var_get
   --
   module : globals

procedure global_var_set


    procedure global_var_set
        p_var_name varchar(50),
        p_value    text
    --
    creates or modifies a global variable p_var_name
    with value p_value.  
    --
    returns the variable value
    --
    sets variable @global_var_set.

   --
   see also:  global_var_get, global_var_exists, global_var_drop
   --
   module : globals

procedure global_var_drop


    procedure global_var_drop
        p_var_name varchar(50)
    --
    Removes a global variable p_var_name
    --
    returns 1 if the variable existed, 0 otherwise
    --
    sets variable @global_var_drop.

   --
   see also:  global_var_set, global_var_exists
   --
   module : globals

named parameters


function simple_sp


    function simple_sp
        routine_name       varchar(50),
        parameters_array   varchar(50)
    --
    returns a query to invoke a complex procedure with many parameters,
    using an array as a list of named parameters.
    Named parameters can be mentioned in any order. 
    --
    returns the query with the parameters in the appropriate order.
   --
   see also: procedure simple_sp
   --
   module : named parameters

function simple_spl


    function simple_spl
        routine_name       varchar(50),
        parameters_list    text
    --
    returns a query to invoke a complex procedure with many parameters,
    using a list of named parameters.
    Named parameters can be mentioned in any order. 
    --
    returns the query with the parameters in the appropriate order.
    --
    the default list and pair separators can be changes using
    @list_separator and pair_separator user variables.
   --
   see also: procedure simple_spl
   --
   module : named parameters

procedure simple_sp


    procedure simple_sp
        routine_name       varchar(50),
        parameters_array   varchar(50)
    --
    calls a given procedure using an array as a list
    of named parameters. 
    Named parameters can be mentioned in any order.
   --
   see also: function simple_sp
   --
   module : named parameters

procedure simple_spl


    procedure simple_sp
        routine_name       varchar(50),
        parameters_list    text
    --
    calls a given procedure using a list
    of named parameters. List elements are separated by semicolons
    (note that this is different from array_from_list defaults)
    and pair elements are separated by "=>".
    Named parameters can be mentioned in any order. 
    --
    the default list and pair separators can be changes using
    @list_separator and pair_separator user variables.
   --
   see also: function simple_sp, simple_spl
   --
   module : named parameters

syntax helpers


function routine_syntax


    function routine_syntax 
        p_db_name       varchar(50),
        p_routine_name  varchar(50),
        p_routine_type  enum("function", "procedure")
    --
    returns the syntax of a given routine
    (if it has been stored in the syntax table)
   --
   see also: fsyntax, psyntax
   --
   module : syntax helpers

function fsyntax


    function fsyntax 
        p_function_name  varchar(50)
    --
    returns the syntax of a given function
    (if it has been stored in the syntax table)
    if the same function name is used in different databases,
    then p_function_name could be espressed as "db_name.p_routine_name"
   --
   see also: routine_syntax, psyntax
   --
   module : syntax helpers

function psyntax


    function psyntax 
        p_procedure_name  varchar(50)
    --
    returns the syntax of a given procedure
    (if it has been stored in the syntax table)
    if the same procedure name is used in different databases,
    then p_routine_name could be espressed as "db_name.p_routine_name"
   --
   see also: routine_syntax, fsyntax
   --
   module : syntax helpers

procedure my_routines


    procedure my_routines 
        pattern  varchar(50)
    --
    gives a list of routines in the syntax database,
    according to the given pattern.
    The pattern is treated with the REGEXP operator.
   --
   see also: my_procedures, my_functions
   --
   module : syntax helpers

procedure my_procedures


    procedure my_procedures 
        pattern  varchar(50)
    --
    gives a list of procedures in the syntax database,
    according to the given pattern.
    The pattern is treated with the REGEXP operator.
   --
   see also: my_routines, my_functions
   --
   module : syntax helpers

procedure my_functions


    procedure my_functions 
        pattern  varchar(50)
    --
    gives a list of functions in the syntax database,
    according to the given pattern.
    The pattern is treated with the REGEXP operator.
   --
   see also: my_routines, my_procedures
   --
   module : syntax helpers

testing


procedure check_table


    procedure check_table
        p_db_name     varchar(50),
        p_table_name  varchar(50)
    --
    Checks for a table existence and sets a test log for the record.
    
   --
   see also: check_routine, check_view
   --
   module : test utilities

procedure check_view


    procedure check_view
        p_db_name     varchar(50),
        p_view_name  varchar(50)
    --
    Checks for a view existence and sets a test log for the record.
    
   --
   see also: check_routine, check_table
   --
   module : test utilities

procedure check_routine


    procedure check_routine 
        p_db_name       varchar(50),
        p_routine_name  varchar(50),
        p_routine_type  enum("procedure", "function")
    --
    Checks for a routine existence and sets a test log for the record.
    
   --
   see also: check_routine_simple, check_for_routines_existence
   --
   module : test utilities

procedure check_routine_simple


    procedure check_routine_simple
        p_routine_name  varchar(50),
        p_routine_type  enum("procedure", "function")
    --
    Checks for a routine existence and sets a test log for the record.
    Works like check_routine, without the "database" parameter. Instead
    of that, a @database user variable is considered. It is useful
    when you need to test several routines from the same database, and 
    you do not want to repeat the same parameter several times.
    
   --
   User Variables:
   @database is used to determine the routine position
   --
   see also: check_routine, check_for_routines_existence
   --
   module : test utilities

procedure check_for_routines_existence


    procedure check_for_routines_existence
        p_db_name  varchar(50)
    --
    Checks for several routines existence and sets appropriate test logs for the record.
    It should be used when checking many routines at once.
    Their name and type should be entered into a _routine_list table,
    and a call to check_for_routines_existence will invoke check_routine
    for each record in such table.
    --
    Example:
        insert into _routine_list (routine_name, routine_type)
            values ("routine_a", "function"), ("routine_b", "procedure");
        call check_for_routines_existence(database());
    --
    It should be used after a call to initialize_tests();
    
   --
   see also: check_routine, check_routine_simple, initialize_tests
   --
   module : test utilities

procedure initialize_tests


    procedure initialize_tests
    --
    Creates a table for routine checking and prepares the log table for
    testing.
    --
    It should be called before any other routine in a test unit.
    
   --
   see also: check_routine, check_routine_simple, initialize_tests
   --
   module : test utilities

procedure show_test_results


    procedure show_test_results
    --
    shows the details of all tests recorded in the testing unit,
    including a summary of passed and failed tests.
    If any test failed, their list is shown after the summary.
    
   --
   see also: log_test, show_test_results
   --
   module : test utilities

procedure log_test


    procedure log_test
        p_description   varchar(200),
        p_result        text,
        p_expected      text,
        p_outcome       boolean
    --
    Inserts a test result into table _test_results.
    p_description is a description of what is being tested;
    p_result is the value being tested;
    p_expected is a textual description of what to expect;
    p_outcome should be passed an expression, whose result will show if the
    test passed or failed.
    
   --
   see also: initialize_tests, show_test_results
   --
   module : test utilities