How to create mysql stored procedure (without using mysql-query-browser)
In here I'm explaning to simple creating mysql stored procedure without using mysql query browser. So let’s say hello using mysql stored procedure.
1) First you have to open text editor to write the mysql procdure code. So windows users can use notepad and linux users can use vim for that. File name is “hello.sql”. The file format should be “.sql”.
2) Write the following code on your text editor, and save.
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`hello` $$
CREATE PROCEDURE `test`.`hello`()
BEGIN
SELECT 'hello';
END $$
DELIMITER ;
3) Explanation about the code
Normally mysql user ";" as the end of a statement (delimiter). But since stored procedures contain semicolons in the procedure body, we need to use a different delimiter to identify the end of the procedure. Therefore in here I’m using “$$” as delimiter of the procedures. To define the delimiter we can use DELIMITER key word as the first line of the above code.
“ DROP PROCEDURE IF EXISTS `test`.`hello`$$ ” line will remove the stored procedure if it already exists in the test database. In here you can see I have use “$$” procedure delimiter as the end of the statement.
“ CREATE PROCEDURE `test`.`hello`() “ line is the start of a stored procedure definition. “hello” is the name of the procedure.
“ BEGIN “ line is the start of the stored procedure program.
Between BEGIN and END we can write the procedure program with using sql stetments. And we use “;” as the delimiter in the program. “SELECT 'hello'; “ is the simplest sql statement.
“END $$ ” line ends the procedure program, and it use “$$” delimiter.
After the end of procedure we should redefine the delimiter to “;” for normal use by "DELIMITER ;".
4) Now you should log on to the mysql database in command line.
(Windows users should go to the C:\Program Files\MySQL\MySQL Server 5.1…\bin\ where the mysql.exe located)
# Mysql –u –p
5) After login to the mysql you should type SOURCE Hello.sql
Example for linux:
mysql> SOURCE /home/Hello.sql
Example for windows:
mysql > SOURCE D:/Hello.sql
6) After that you should type following code to run the procedure.
CALL test.hello();
or
USE test;
CALL hello();
The out put,
+-------+
| Hello |
+-------+
| Hello |
+-------+
