---json
{
    "page_id": "75sg7krdu24veg07kvid0"
}
---
====== How to execute a procedure in MySQL ? ======


===== About =====
This ''howto'' shows you how to create and execute a [[:docs:system:database:sql_procedure|SQL Procedure]]  in <icon name="logos:mysql"/> [[:docs:system:mysql:mysql|MySQL]].


This example has been taken from the [[https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html#creating_stored_procedure_mysql|official documentation examples]]


===== Steps =====


==== Start MySQL ====


The next steps will be using the [[howto_connection|mysql howto connection]].

  * We [[howto_connection#start_the_mysql_service|start the mysql docker image]].
<unit>
<code bash>
tabul service start mysql
</code>
</unit>
  * [[docs:tabul:connection:ping|Ping]] the connection to check that the database is up.
<unit>
<code bash>
tabul connection ping mysql
</code>
<console>
The connection (mysql) has been pinged successfully
</console>
</unit>


<unit display=none>
Cleaning, should be after the start
<code bash>
tabul data drop --no-strict-selection *@mysql
</code>
</unit>

==== Creation of the schema and procedure ====

=== The Coffee Break sample schema ===

In the [[https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html#step6|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 [[:docs:tabul:data:list|list command]] with a [[:howto:tabul:glob|star glob expression]], we can list all files
<unit>
<code bash>
tabul data list mysql/coffee-break-sample-db/*@howto
</code>
<console sql >
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
</console>
</unit>


=== The Create Procedure Statements ===

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

The script ''install-03-create-procedures.sql'' contains [[:docs:resource:sql_ddl|SQL DDL statements]] that will create 3 [[:docs:resource:sql_stored_procedure|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:
<unit>
<code bash>
tabul data cat mysql/coffee-break-sample-db/install-03-create-procedures.sql@howto
</code>
<console sql height="300px" scroll="toggle">
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;
</console>
</unit>



=== Execution of the Installation Scripts ===

We will [[docs:resource:runtime#execution|execute it]] by:
  * making it a [[docs:resource:runtime#data uri|runtime data uri]] (ie adding the execution connection)
  * and [[:docs:tabul:data:execute|executing them it]]


<unit>
<code bash>
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)
</code>
<console>
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
</console>
</unit>

=== Listing the procedures with show procedures ===

On MySQL, you can list the procedure status with the [[https://dev.mysql.com/doc/refman/8.4/en/show-procedure-status.html|SHOW procedure]] SQL:

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

</file>

This statement is in the file `mysql/show-procedure-status.sql` located in the [[:docs:connection:howto|howto connection]] and we can execute it with [[:docs:tabul:data:print|the print command]]
<code bash>
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)
</code>
<console>
(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
</console>
</unit>
==== 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:
<unit>
<code bash>
tabul data cat mysql/coffee-break-sample-db/call-show-suppliers.sql@howto
</code>
<console sql >
call SHOW_SUPPLIERS;
</console>
</unit>
where:
  * ''call'' is the [[https://dev.mysql.com/doc/refman/8.4/en/call.html|MySQL call SQL statement]]
  * ''SHOW_SUPPLIERS'' is the ''select'' procedure created previously

=== Execution of the literal statement ===

We will [[docs:resource:runtime#execution|execute it]] by:
  * making it a [[docs:resource:runtime#data uri|runtime data uri]] (ie adding the execution connection)
  * and [[:docs:tabul:data:print|printing it]]

<unit>
<code bash>
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)
</code>
<console>
(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
</console>
</unit>


==== Parametrized Execution of a Procedure ====

=== The parametrized statement ===
The [[:docs:system:database:sql_statement|SQL statement]] to execute a [[:docs:system:database:sql_procedure|sql procedure]] is:
<unit>
<file sql mysql/coffee-break-sample-db/call-get-supplier-of-coffee.sql>
{ 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

</file>
</unit>
It's a [[:docs:system:database:sql_parameterized_statement|parameterized statement]] where:
  * ''{'' is the start character of a [[:docs:system:database:jdbc_escape|jdbc escape block]]
  * ''call'' is the [[https://dev.mysql.com/doc/refman/8.4/en/call.html|MySQL Call SQL statement]] that invokes a procedure
  * ''GET_SUPPLIER_OF_COFFEE( ?, ? )'' is the function with 2 [[:docs:system:database:sql_parameterized_statement#placeholder|sql parameter placeholder]] as  input
  * ''}'' is the end character of a [[:docs:system:database:jdbc_escape|jdbc escape block]]




=== The Request Data Resource ===



To make this [[:docs:system:database:sql_statement|statement]] [[docs:resource:runtime|executable]], we need to create a [[:docs:resource:sql_request#manifest|sql request manifest]] so that we can define the [[:docs:system:database:sql_parameterized_statement#parameters|parameters]]


The signature of the procedure as seen in the  [[#the_create_procedure_statements|SQL file]] is:
```sql
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 [[:docs:resource:sql_request#manifest|sql request manifest]].

<unit>
<file yml mysql/coffee-break-sample-db/call-get-supplier-of-coffee--sql-request.yml>
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

</file>
</unit>

=== The execution ===

We will [[docs:resource:runtime#execution|execute]] this [[:docs:resource:manifest|manifest]] by [[:docs:tabul:data:print|printing]] it

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

Output:
<console>
(call-get-supplier-of-coffee.sql@md)@mysql
supplierName
------------
Acme, Inc.
</console>
</unit>





==== Next ====

That's it. You should have learned how to call a [[:docs:system:database:sql_procedure|SQL Procedure]] in MySQL.