Creates a stored procedure. A stored procedure is a callable routine that accepts input parameters, executes programmatic logic, and optionally returns a single value. Stored procedures can return all the value types that UDFs can return, but stored procedures can also return query-type values. A query-type value is typically evaluated by calling the procedure with ECHO to produce a rowset.

Syntax

[USING database_name] CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
    procedure_body
  END ;

parameter_list:
  parameter_name data_type [data_type_modifier [ ...n ] ] [, ...]

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name type_expression [= value_expression ] ; [... ;]

Arguments

OR REPLACE

If specified, replaces a stored procedure if one already exists with the same name.

procedure_name

The name of the stored procedure. Procedure names must be unique within a database, and cannot be duplicates of names for other stored procedures, tables, views, user-defined scalar-valued functions (UDFs), user-defined table-valued functions (TVFs), user-defined aggregate functions (UDAFs), or other builtin functions such as CONCAT(), ABS(), COUNT(), and so on.

parameter_list

Input parameters are optional. Any number of input parameters can be specified, and each must be separated by a comma (,). Each input parameter must be given a unique identifier name within the scope of the stored procedure.

The following example shows how to declare a single input parameter:

CREATE PROCEDURE single_param_example(a INT)
...

Stored procedures allow scalar data types, non-scalar data types (ARRAY and RECORD), and query data types as input parameters. Each valid type is described in the Data Types topic. The following example demonstrates how to declare more than one input parameter, using scalar, non-scalar, and query data types:

CREATE PROCEDURE multi_param_example(
  a INT, b ARRAY(BIGINT NOT NULL), c DECIMAL(19,4), d QUERY(col1 TEXT))
...

Default values can be specified for input parameters by using the DEFAULT constraint. You cannot set a default value for a query type variable. Consider the following example:

CREATE PROCEDURE default_example(a INT DEFAULT 5)
...

Input parameters can also specify data type modifiers, namely NOT NULL, NULL, and COLLATE. Query type variables cannot be null or have a collation. Consider the following example:

CREATE PROCEDURE modifier_example(a TEXT NOT NULL COLLATE utf8_bin)
...

data_type

Any scalar-valued, non-scalar-valued, or query data type. Scalar and non-scalar-valued types are described in the Data Types topic. A query-type value is a query, which may contain bound parameters, that can evaluated to produce a row set, or stored in a query-type variable.

procedure_body

With the exception of EXPLAIN and PROFILE, the vast majority of Data Manipulation Language statements are supported in the body of a stored procedure.

Other supported SQL statements include:

• USE
• SET
• SNAPSHOT DATABASE
• DESCRIBE TABLE
• FILL/FLUSH CONNECTION POOL
• OPTIMIZE PIPELINE
• START/STOP PIPELINE
• COUNT WARNINGS
• KILL
• KILLALL

In addition to the SQL statements listed above, the following statements are also supported:

• Any CALL statement to execute another stored procedure.
• Execution of a UDF if it is used as an expression inside a query, or alternately if it is on the right side of an assignment.
• Execution of a TVF if it is inside the FROM clause of a DML statement.
• Variable assignments.

Security and Permissions

Stored Procedures in MemSQL use the Definer Security Model. The SP’s definer must have EXECUTE permissions on every UDF or SP that is used in the procedure body, and the permissions to execute every SQL statement that appears in the procedure body.

Variable and Parameter Substitution in SQL Statements

You may include the value of a parameter or variable in a SQL query inside a stored procedure by using its name in a place that a constant value may appear.

Limitations

Stored procedures are limited in the following ways:

Calling Limitations

A stored procedure can only be invoked using CALL from within other stored procedures, and can only be invoked using CALL or ECHO from a client application connected to MemSQL. Stored procedures cannot be invoked within SQL queries, views, user-defined scalar-valued functions (UDFs), user-defined table-valued functions (TVFs), or user-defined aggregate functions (UDAFs).

Function Overloading

A stored procedure’s definition cannot be overloaded by changing the procedure signature, such as adding or removing input parameters or changing the return type. This limitation is directly correlated to the naming restrictions mentioned in the arguments section above.

Unsupported SQL Statements

The following DDL SQL statements are not supported in the body of a stored procedure:

• CREATE FUNCTION
• CREATE AGGREGATE
• CREATE PROCEDURE
• CREATE DATABASE
• CREATE TABLE
• CREATE VIEW
• CREATE USER
• DROP FUNCTION
• DROP AGGREGATE
• DROP PROCEDURE
• DROP DATABASE
• DROP TABLE
• DROP VIEW
• DROP USER
• GRANT
• REVOKE
• ALTER USER
• RENAME USER
• SET PASSWORD

MySQL Client Delimiters

When using client applications like the mysql command line interface, Sequel Pro, or other similar tools to connect to MemSQL, you must bound the CREATE PROCEDURE statement with delimiters other than semi-colon. This is so that the semi-colons in the procedure won’t be interpreted as the end of the statement by the client tool. Then you should set the delimiter to semi-colon again afterwards. This is the reason for the use of DELIMITER // and DELIMITER ; in the example below.

The DELIMITER commands must be on independent lines. These lines are interpreted by the client, not the server. See the CREATE FUNCTION topic for additional discussion of MySQL client delimiters.

When creating stored procedures or functions from your own client application created in a language such as Java, C++, or Python, the delimiters are not needed.

Examples

Bank Transaction Example

The following example demonstrates how to use a stored procedure to perform a calculation on a bank account and, if sufficient funds are present, deduct the charge amount from the balance.

This example uses two tables:

CREATE TABLE accounts (
  id BIGINT AUTO_INCREMENT,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE account_balance (
  account_id BIGINT,
  remaining_balance DECIMAL(18,4),
  PRIMARY KEY (account_id)
);

The following example user and balance is inserted into the account:

INSERT INTO accounts VALUES(DEFAULT, "John", "Doe");
INSERT INTO account_balance VALUES(1, 500.0000);

Now that the example tables have been created with test data, you can create the stored procedure. The procedure is defined in the following way:

DELIMITER //
CREATE PROCEDURE charge_account(id BIGINT, amount DECIMAL(18,4)) AS
  DECLARE
    balance_tbl QUERY(bal DECIMAL(18,4)) =
      SELECT remaining_balance
      FROM account_balance
      WHERE account_id = id;
    balance DECIMAL(18,4) = SCALAR(balance_tbl);
    updated_balance DECIMAL(18,4) = balance - amount;
  BEGIN
    IF balance > amount THEN
      UPDATE account_balance
      SET remaining_balance = updated_balance
      WHERE account_id = id;
    END IF;
  END //
DELIMITER ;

You can execute this procedure by using the CALL command in the client:

memsql> CALL charge_account(1, 200.0000);
Query OK, 0 rows affected (0.43 sec)

Next, execute the following SELECT statement to see the account’s new balance.

SELECT account_balance.account_id, account_balance.remaining_balance,
accounts.first_name, accounts.last_name
FROM accounts, account_balance
WHERE accounts.id = account_balance.account_id;

This produces the following results:

+------------+-------------------+------------+-----------+
| account_id | remaining_balance | first_name | last_name |
+------------+-------------------+------------+-----------+
|          1 |          300.0000 | John       | Doe       |
+------------+-------------------+------------+-----------+

Recursive Tree Expansion Example

The following example shows how to expand a tree represented by data in a table. The result tree is stored in a working (scratch) table and a query-type value is returned that provides the results from this working table.

-------- Recursive tree expansion example 
-- 
-- Important points:
--   Only references from child to parent are used.
--   The number of queries run (trips through the loop) 
--   is proportional to the height of the tree.

CREATE TABLE emp(id int, mgr_id int, name varchar(30));
INSERT emp VALUES(1, NULL, "Maggie");  -- boss
INSERT emp VALUES(2, 1, "John");
INSERT emp VALUES(3, 1, "Felix");
INSERT emp VALUES(4, 2, "Joan");
INSERT emp VALUES(5, 2, "Richard");
INSERT emp VALUES(6, 3, "Andy");
INSERT emp VALUES(7, 3, "Jill");

/* 
Diagram of tree represented by the rows above:

             Maggie
          /         \
         John        Felix
         /  \          /   \
     Joan   Richard   Andy  Jill
*/

CREATE TABLE emps_at_and_below_mgr(
  id int, mgr_id int, name varchar(30), level int);

-- Find all employees under specific manager and output them into
-- table emps_at_and_below_mgr.

DELIMITER //
CREATE OR REPLACE PROCEDURE get_emp_tree(mname varchar(30)) 
  RETURNS query(id int, mgr_id int, name varchar(30), level int) 
AS
DECLARE
  current_level int = 0;
  num_inserted int; 
  result_q query(id int, mgr_id int, name varchar(30), level int) = 
    SELECT * FROM emps_at_and_below_mgr;
BEGIN
  DELETE FROM emps_at_and_below_mgr;
  INSERT INTO emps_at_and_below_mgr 
   SELECT *, 0 
   FROM emp WHERE name = mname;
  num_inserted = 1;
  WHILE num_inserted <> 0 LOOP
    INSERT INTO emps_at_and_below_mgr 
      SELECT emp.*, current_level + 1 
      FROM emp, emps_at_and_below_mgr t 
      WHERE emp.mgr_id = t.id and t.level = current_level;
    num_inserted = row_count();
    current_level += 1;
  END LOOP;
  RETURN result_q;
END //
DELIMITER ;

ECHO get_emp_tree("Maggie");
ECHO get_emp_tree("Felix");
ECHO get_emp_tree("Jill");

The results of the ECHO statements above illustrate the tree expansion:

memsql> ECHO get_emp_tree("Maggie");
+------+--------+---------+-------+
| id   | mgr_id | name    | level |
+------+--------+---------+-------+
|    1 |   NULL | Maggie  |     0 |
|    4 |      2 | Joan    |     2 |
|    5 |      2 | Richard |     2 |
|    2 |      1 | John    |     1 |
|    3 |      1 | Felix   |     1 |
|    7 |      3 | Jill    |     2 |
|    6 |      3 | Andy    |     2 |
+------+--------+---------+-------+
7 rows in set (0.02 sec)

memsql> ECHO get_emp_tree("Felix");
+------+--------+-------+-------+
| id   | mgr_id | name  | level |
+------+--------+-------+-------+
|    7 |      3 | Jill  |     1 |
|    6 |      3 | Andy  |     1 |
|    3 |      1 | Felix |     0 |
+------+--------+-------+-------+
3 rows in set (0.03 sec)

memsql> ECHO get_emp_tree("Jill");
+------+--------+------+-------+
| id   | mgr_id | name | level |
+------+--------+------+-------+
|    7 |      3 | Jill |     0 |
+------+--------+------+-------+
1 row in set (0.01 sec)

To use this kind of approach in a multi-user environment, it’d be necessary to put an extra column on the scratch table emps_at_and_below_mgr like results_id with a unique identifier for the current expansion. Some garbage-collection logic would also be needed to remove old rows from the scratch table when they were no longer being used.

Additional Capabilities

The body of a stored procedure can contain statements for control flow, transaction management, and exception handling.

Related Topics

• DROP PROCEDURE
• SHOW FUNCTIONS
• SHOW CREATE FUNCTION
• Control Flow Statements
• Transactions in Stored Procedures
• Exceptions and Exception Handling

Was this Article Helpful?

Thanks For Your Feedback!