Credits

Monday, August 29, 2011

SQL’S Stored Procedure


A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.

You can create stored procedures in command line window or at least in MySQL 5.0 as earlier versions don't support stored procedures or triggers. You can even create stored procedures in MySQLyog for easy writing.

Below are excerpts of stored procedure's advantages and disadvantages along with its syntax and sample.

Stored Procedures Advantages
  • Stored procedure increases performance of application.
  • Stored procedure reduced the traffic between application and database server
  • Stored procedure is reusable and transparent to any application.
  • Stored procedure is secured.

Stored Procedures Disadvantages
  • Stored procedures make the database server high load in both memory for and processors.
  • Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business logic like other languages in application layer such as Java, C#, C++…
  • You cannot debug stored procedure in almost RDMBSs and in MySQL also.
  • Writing and maintaining stored procedure usually required specialized skill set that not all developers possess.

[+/-] show/hide


MySQL Stored Procedures
The syntax for using Stored Procedures is as follows:

CREATE

[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement
Writing the first stored procedure
The first stored procedure is very simple. It retrieves all products from products table. First let’s take a look at the stored procedure simple source code below:

1 DELIMITER //
2 CREATE PROCEDURE GetAllProducts()
3 BEGIN
4 SELECT * FROM products;
5 END //
6 DELIMITER ;

AddThis