A stored procedure is an executable program that is stored in the database server waiting to be called for execution. A stored procedure should be written in a programming language that is supported by the database server. Some database servers offer special languages that are extended from the standard SQL.
Syntax to create stored procedure is, CREATE PROCEDURE proc_name() body_statement;
Ex-: login to MySQL and execute the following,
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> DECLARE fanta INT DEFAULT 55;
-> DROP TABLE t2;
-> LOOP
-> INSERT INTO t3 VALUES (fanta);
-> END LOOP;
-> END//
Query OK, 0 rows affected (0.00 sec)
To view this created procedure, execute the command,
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SHOW CREATE PROCEDURE p1 \G;
=-=-=--=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-
NB: To use either statement, you must be the owner of the routine or have SELECT access to the mysql.proc table. I have come across issue where, executing this script by the user from cPanel PHPMyAdmin result NULL result. For an user to execute this, user need to have SELECT access to mysql.proc.Only root will have full access to mysql.proc. So execute the below query to
-=-=-=-==-=-=--=-=-==--=-=-=
grant select on mysql.proc to 'user'@'localhost';
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Done.
Syntax to create stored procedure is, CREATE PROCEDURE proc_name() body_statement;
Ex-: login to MySQL and execute the following,
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> DECLARE fanta INT DEFAULT 55;
-> DROP TABLE t2;
-> LOOP
-> INSERT INTO t3 VALUES (fanta);
-> END LOOP;
-> END//
Query OK, 0 rows affected (0.00 sec)
To view this created procedure, execute the command,
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SHOW CREATE PROCEDURE p1 \G;
=-=-=--=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-
NB: To use either statement, you must be the owner of the routine or have SELECT access to the mysql.proc table. I have come across issue where, executing this script by the user from cPanel PHPMyAdmin result NULL result. For an user to execute this, user need to have SELECT access to mysql.proc.Only root will have full access to mysql.proc. So execute the below query to
-=-=-=-==-=-=--=-=-==--=-=-=
grant select on mysql.proc to 'user'@'localhost';
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Done.