howto:mysql:procedure

About

This howto shows you how to create and execute a SQL Procedure in MySQL.

This example has been taken from the official documentation examples

Steps

Start MySQL

The next steps will be using the mysql howto connection.

We start the mysql docker image.

tabul service start mysql

Ping the connection to check that the database is up.

tabul connection ping mysql

The connection (mysql) has been pinged successfully

Cleaning, should be after the start

tabul data drop --no-strict-selection *@mysql

Creation of the schema and procedure

The Coffee Break sample schema

In the getting started, they provide the Coffee Break database.

This database is used by a proprietor of a small coffee house where coffee beans are sold by the pound and brewed coffee is sold by the cup.

We have extracted the sql scripts.

They are available at msql/coffee-break-sample-db

By using the list command with a star glob expression, we can list all files

tabul data list mysql/coffee-break-sample-db/*@howto

path                                                            media_type
-------------------------------------------------------------   ----------
mysql/coffee-break-sample-db/call-get-supplier-of-coffee.sql    text/sql
mysql/coffee-break-sample-db/call-show-suppliers.sql            text/sql
mysql/coffee-break-sample-db/drop-01-tables.sql                 text/sql
mysql/coffee-break-sample-db/install-01-create-tables.sql       text/sql
mysql/coffee-break-sample-db/install-02-populate-tables.sql     text/sql
mysql/coffee-break-sample-db/install-03-create-procedures.sql   text/sql
mysql/coffee-break-sample-db/README.md                          text/plain

The Create Procedure Statements

MySQL uses a SQL-based syntax for its stored procedures.

The script install-03-create-procedures.sql contains SQL DDL statements that will create 3 stored procedures:

SHOW_SUPPLIERS - a procedure that returns a result set
RAISE_PRICE - a procedure that raise the prices of a coffee product
GET_SUPPLIER_OF_COFFEE - a procedure that returns the supplier of a coffee product

The content is:

tabul data cat mysql/coffee-break-sample-db/install-03-create-procedures.sql@howto

drop procedure if exists SHOW_SUPPLIERS;
drop procedure if exists GET_SUPPLIER_OF_COFFEE;
drop procedure if exists RAISE_PRICE;
# Creating procedure SHOW_SUPPLIERS
create procedure SHOW_SUPPLIERS()
  begin
    select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
    from SUPPLIERS, COFFEES
    where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
    order by SUP_NAME, COF_NAME;
  end;
# Creating procedure GET_SUPPLIER_OF_COFFEE
create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40))
  begin
    select SUPPLIERS.SUP_NAME into supplierName
      from SUPPLIERS, COFFEES
      where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
      and coffeeName = COFFEES.COF_NAME;
    select supplierName;
  end;
# Creating procedure RAISE_PRICE
create procedure RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2))
  begin
    main: BEGIN
      declare maximumNewPrice numeric(10,2);
      declare oldPrice numeric(10,2);
      select COFFEES.PRICE into oldPrice
        from COFFEES
        where COFFEES.COF_NAME = coffeeName;
      set maximumNewPrice = oldPrice * (1 + maximumPercentage);
      if (newPrice > maximumNewPrice)
        then set newPrice = maximumNewPrice;
      end if;
      if (newPrice <= oldPrice)
        then set newPrice = oldPrice;
        leave main;
      end if;
      update COFFEES
        set COFFEES.PRICE = newPrice
        where COFFEES.COF_NAME = coffeeName;
      select newPrice;
    END main;
  end;

Execution of the Installation Scripts

We will execute it by:

making it a runtime data uri (ie adding the execution connection)
and executing them it

tabul data exec \
   --processing-type batch \
   '(mysql/coffee-break-sample-db/install*.sql@howto)@mysql'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)

List of runtime executed
runtime_data_uri                                                              exit_code   count   latency   data_uri                                                                                error_message
---------------------------------------------------------------------------   ---------   -----   -------   -------------------------------------------------------------------------------------   -------------
(mysql/coffee-break-sample-db/install-01-create-tables.sql@howto)@mysql               0       8   0.180s    execute/20251110-204056-896-pipe-tabul-data-exec/install-01-create-tables.log@tmp       
(mysql/coffee-break-sample-db/install-02-populate-tables.sql@howto)@mysql             0      40   0.152s    execute/20251110-204056-896-pipe-tabul-data-exec/install-02-populate-tables.log@tmp     
(mysql/coffee-break-sample-db/install-03-create-procedures.sql@howto)@mysql           0       6   0.16s     execute/20251110-204056-896-pipe-tabul-data-exec/install-03-create-procedures.log@tmp

Listing the procedures with show procedures

On MySQL, you can list the procedure status with the SHOW procedure SQL:

show procedure status where db = 'howto';
-- https://dev.mysql.com/doc/refman/8.4/en/show-procedure-status.html

This statement is in the file mysql/show-procedure-status.sql located in the howto connection and we can execute it with the print command

tabul data print '(mysql/show-procedure-status.sql@howto)@mysql'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)

(mysql/show-procedure-status.sql@howto)@mysql
ROUTINE_SCHEMA   ROUTINE_NAME             ROUTINE_TYPE   DEFINER   LAST_ALTERED          CREATED               SECURITY_TYPE   ROUTINE_COMMENT   CHARACTER_SET_CLIENT   COLLATION_CONNECTION   DATABASE_COLLATION
--------------   ----------------------   ------------   -------   -------------------   -------------------   -------------   ---------------   --------------------   --------------------   ------------------
howto            GET_SUPPLIER_OF_COFFEE   PROCEDURE      root@%    2025-11-10T20:40:58   2025-11-10T20:40:58   DEFINER                           utf8mb4                utf8mb4_general_ci     latin1_swedish_ci
howto            RAISE_PRICE              PROCEDURE      root@%    2025-11-10T20:40:58   2025-11-10T20:40:58   DEFINER                           utf8mb4                utf8mb4_general_ci     latin1_swedish_ci
howto            SHOW_SUPPLIERS           PROCEDURE      root@%    2025-11-10T20:40:58   2025-11-10T20:40:58   DEFINER                           utf8mb4                utf8mb4_general_ci     latin1_swedish_ci

Literal Execution of a Procedure

In this example, we show you how to call a procedure without any argument that returns a result set.

The literal statement

The statement is:

tabul data cat mysql/coffee-break-sample-db/call-show-suppliers.sql@howto

call SHOW_SUPPLIERS;

where:

call is the MySQL call SQL statement
SHOW_SUPPLIERS is the select procedure created previously

Execution of the literal statement

We will execute it by:

making it a runtime data uri (ie adding the execution connection)
and printing it

tabul data print '(mysql/coffee-break-sample-db/call-show-suppliers.sql@howto)@mysql'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)

(mysql/coffee-break-sample-db/call-show-suppliers.sql@howto)@mysql
SUP_NAME          COF_NAME
---------------   ------------------
Acme, Inc.        Colombian
Acme, Inc.        Colombian_Decaf
Superior Coffee   French_Roast
Superior Coffee   French_Roast_Decaf
The High Ground   Espresso

Parametrized Execution of a Procedure

The parametrized statement

The SQL statement to execute a sql procedure is:

{ call GET_SUPPLIER_OF_COFFEE(?,?) }
-- To use an OUT parameter (ie the first ? parameter), the expression should be escaped (ie enclosed by { and })
-- Otherwise you get the error: This statement does not declare an OUT parameter.  Use { ?= call ... } to declare one

It's a parameterized statement where:

{ is the start character of a jdbc escape block
call is the MySQL Call SQL statement that invokes a procedure
GET_SUPPLIER_OF_COFFEE( ?, ? ) is the function with 2 sql parameter placeholder as input
} is the end character of a jdbc escape block

The Request Data Resource

To make this statement executable, we need to create a sql request manifest so that we can define the parameters

The signature of the procedure as seen in the SQL file is:

create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40))

It has 2 parameters:

IN parameter coffeeName
OUT parameter supplierName

You pass a coffeeName and you get a supplierName.

We define them in the below sql request manifest.

kind: sql-request
spec:
  # the executable data uri
  # * takes the sql file (call-get-supplier-of-coffee.sql)
  # * located in the same directory as this manifest (md)
  # * and execute it against (mysql)
  data-uri: (call-get-supplier-of-coffee.sql@md)@mysql
  # the data definition
  data-def:
    parameters:
      # the parameter for the first placeholder
      - direction: in # retrieve the value
        type: varchar # the type of the retrieved value
        value: Colombian # the value passed to the GET_SUPPLIER_OF_COFFEE function
      # the parameter for the second placeholder
      - direction: out # set the value
        type: varchar # the type of the value

The execution

We will execute this manifest by printing it

tabul data print mysql/coffee-break-sample-db/call-get-supplier-of-coffee--sql-request.yml@howto

Output:

(call-get-supplier-of-coffee.sql@md)@mysql
supplierName
------------
Acme, Inc.

That's it. You should have learned how to call a SQL Procedure in MySQL.

Table of Contents