How to write a query for creating an stored proceedure in mysql

How to write a query for creat开发者_运维技巧ing an stored procedure in mysql

A really simple way to get a jump on creating the stored procedure is to use something like the MySQL Query Browser. I use this tool all the time for writing stored procedures and functions.

With that tool, you do the following:

1. Right-click on the database from the Schemata tab that you want to create the proc/func in.

2. Choose "Create New Stored Procedure/Function" or type CTRL-P.

3. Enter the name of the procedure/function.

4. Click the Create PROCEDURE or Create FUNCTION button.

This will create a skeleton stored procedure or function with all the DELIMITER and DROP PROCEDURE/DROP FUNCTION lines written for you. You can tweak them if you need to but I don't generally find the need to do that.

Then you can flesh out the proc/func and Click Execute and, assuming you don't have a syntax error, you've got a stored procedure.

The skeleton stored procedure it generates looks something like this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `database-name`.`proc-name` $$
CREATE PROCEDURE `database-name`.`proc-name` ()
BEGIN

END $$
DELIMITER ;

Hope that helps.

Note: The button text and prompts might vary just slightly between versions of the tool, but otherwise it works the same.

Have a look at the relevant MySQL documentation.

DELIMITER $$

CREATE PROCEDURE prc_filler (cnt INT)
BEGIN
       SELECT  name
       FROM    data;
END
$$

DELIMITER ;

a few simple examples:

 delimiter ;

 drop procedure if exists get_user;

 delimiter #

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

 delimiter ;


 drop procedure if exists list_users;

 delimiter #

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

 delimiter ;