How do I combine CREATE PROCEDURE scripts in MySQL

Question

In MySQL I want to write a script and put multiple CREATE PROCEDURE statements into it, the usual ; won't work for some reason. Is there another way to run multiple CREATE statements in the same script? If so, how?

Answer 1

not much to it really (foo.sql)

you can run this from the mysql command line using \. foo.sql

use foo_db;

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null,
created_date datetime not null
)
engine=innodb;

-- PROCEDURES

drop procedure if exists list_users;

delimiter #

create procedure list_users()
proc_main:begin
  select * from users order by username; 
end proc_main #

delimiter ;

drop procedure if exists get_user;

delimiter #

create procedure get_user
(
p_user_id int unsigned
)
proc_main:begin
    select * from users where user_id = p_user_id;
end proc_main #

delimiter ;

-- TEST DATA

insert into users (username, created_date) values
  ('f00',now()),('bar',now()),('alpha',now()),('beta',now());

-- TESTING

call list_users();

call get_user(1);

Answer 2

Before the first procedure/function declaration define a delimiter thus:

DELIMITER |

After the first procedure/function declaration close the delimiter: |

Do the same for each procedure/function and the entire script will run as one.

So you end up with this:

DELIMITER |
 script 1 text
|

DELIMITER |
 script 2 text
|

DELIMITER |
 script 3 text
|

Answer 3

According to the MySQL FAQ:

24.4.8: Is it possible to group stored procedures or stored functions into packages?

No. This is not supported in MySQL 5.1.

If you're not really trying to form a package, you can try (temporarily) redefining the delimiter:

delimiter //