http://www.mysqltutorial.

org/

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.

Advantages
y

Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.

Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, application only has to send the stored procedure name and get the data back to manipulate it futher.

Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don't have to program the functions which are already supported in stored procedure in all external applications.

Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.

Disadvantages
y

Stored procedures make the database server high load in both memory for and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the well designed in database server.

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++

y y

You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so. Writing and maintaining stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phase.

Example
DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM products; END // DELIMITER ;

DELIMITER statement in MySQL is used to change the standard delimiter (semicolon) to another. In this case, the delimiter is changed from semicolon(;) to //, so you can have multiple SQL statements inside stored procedure which can be separated by the semicolon. After the END keyword we use delimiter // to show the end of the stored procedure. The last command changes the delimiter back to the standard one (semicolon).

Calling the stored procedure

in order to invoke a stored procedure we use the following SQL command:
CALL STORED_PROCEDURE_NAME() Eg: CALL GetAllProducts();

Declaring variables
Variables are used in stored procedure to store the immediate result. You can declare a variable by the following syntax: DECLARE variable_namedatatype(size) DEFAULT default_value; The variable name should follow the naming convention and should not be the same name of table or column in a database. Next you can specify the data type of the variable, it can be any primitive type which MySQL supports such as INT, VARCHAR and DATETIMEalong with the data type is the size of the variable.

When you declare a variable, its initial value is NULL. You can also assign the default value for the variable by using DEFAULT statement. For example, we can define a variable name total_sale with the data type INT and default value is 0 as follows: DECLARE total_sale INT DEFAULT 0 To declare two or more variables with the same data type we can use only just one DECLARE such as:
Example DECLAREx, y INTDEFAULT0

We declared two variables x and y with the same data type INT and their default value is zero.

Assigning variables
To assign other value to a variable you can use SET statement, for example: DECLARE total_count INT DEFAULT 0 SET total_count = 10; The total_countvariables value now is ten (10). Beside SET statement, we can use SELECT INTO to assign a query result to a variable. Example : DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products

Variables scope
A variable has its own scope. If you declare a variable inside a stored procedure, it will be out of scope when the END of stored procedure reached. If you defined a variable inside block BEGIN/END inside a stored procedure it will be out of scope if the END reached. You can declare two variables or more variables with the same name in different scopes; the variable only is effective in its scope.

A variable with the @ at the beginning is session variable. It exists until the session end.

MySQL Stored Procedure Parameters

Almost stored procedures you developrequire parameters. Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.


IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter. Suppose you pass parameter Id, which is equal 10, into stored procedure GetAll(Id), after executing the stored procedure the value of Id is still 10 even though the GetAll stored procedure can change the value of it.

OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.

INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in stored procedure is as follows: MODE param_nameparam_type(param_size)

The first example is a stored procedure to get all offices in a country
DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT city, phone FROM offices WHERE country = countryName; END // DELIMITER ;

CALL GetOfficeByCountry('USA')

The second example, we will write a store procedure to count the order in a specific order status such as shipped, resolved, cancelled, on hold, disputed or in process
DELIMITER $$ CREATE PROCEDURE CountOrderByStatus( IN orderStatusVARCHAR(25), OUT total INT) BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus; END$$

DELIMITER ; CALL CountOrderByStatus('in process',@total); SELECT @total AS total_in_process;

Conditional Control in MySQL Stored Procedure

Conditional control enables you to execute the code based on the value of an expression or a combination of expression using logical operators.

The IF Statement
IF expression THEN commands ELSEIF expression THEN commands ELSE commands END IF;

The CASE Statement

CASE WHEN expression THEN commands

WHEN expression THEN commands ELSE commands END CASE;

Loop in Stored Procedures

how to use various loop statements including WHILE, REPEAT and LOOP to run a block of code repeatedly in MySQL.

WHILE loop
WHILE expression DO Statements END WHILE DELIMITER $$ Example DROP PROCEDURE IF EXISTS WhileLoopProc$$ CREATE PROCEDURE WhileLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str= ''; WHILE x <= 5 DO SET str = CONCAT(str,x,','); SET x = x + 1;

END WHILE; SELECT str; END$$ DELIMITER ;

REPEAT loop
The syntax of repeat loop is as follows: REPEAT Statements; UNTIL expression END REPEAT First the statements are executed, and then the expression is evaluated. If the expression is evaluated as true the statements are executed again and again until its value become false. Because the repeat loop checks the expression after the execution of statements so it is also known as post-test loop. We can rewrite the stored procedure above by using repeat loop as follows: DELIMITER $$ DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str= ''; REPEAT SET str = CONCAT(str,x,','); SET x = x + 1;

UNTIL x > 5 END REPEAT; SELECT str; END$$ DELIMITER ;

SQL Cursor in Stored Procedures

MySQL supports cursor in stored procedures, functions and triggers. Cursor is used to iterate through a set of rows, which returned by a query, and process individual row

MySQL cursor has following properties:

y y y

Read only: it means you cannot update the cursor. Non-scrollable: it only can traverse in one direction and cannot skip, move back or forth in result set. Asensitive: you should avoid update table while open a cursor on that table otherwise you may get unexpected results.

MySQL supports following statements for working with cursor. First you have to declare a cursor using DECLARE statement DECLARE cursor_name CURSOR FOR SELECT_statement; Second you have to open the cursor using OPEN statement. You must open cursor before fetching rows from it. OPEN cursor_name; Next you can retrieve next row from cursor and move the cursor to the following row in a result set by using FETCH statement. FETCH cursor_name INTO variable list; And finally, you must close the cursor to deactivate it and release the memory associated with that cursor. To close the cursor you use CLOSE statement:
CLOSEcursor_name;

One of the most important point when working with cursor is you should use a NOT FOUND handler to avoid raising a fatal no data to fetch condition. DELIMITER $$ DROP PROCEDURE IF EXISTS CursorProc$$ CREATE PROCEDURE CursorProc() BEGIN DECLARE no_more_products, quantity_in_stock INT DEFAULT 0; DECLARE prd_code VARCHAR(255); DECLARE cur_product CURSOR FOR SELECT productCode FROM products; &nbsp; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; /* for loggging information */ CREATE TABLEinfologs ( Id int(11) NOT NULL AUTO_INCREMENT, Msgvarchar(255) NOT NULL, PRIMARY KEY (Id) ); OPEN cur_product; FETCH cur_product INTO prd_code; REPEAT SELECT quantityInStock INTO quantity_in_stock FROM products WHERE productCode = prd_code;

IF quantity_in_stock< 100 THEN

INSERT INTOinfologs(msg) VALUES (prd_code); END IF; FETCH cur_product INTO prd_code; UNTIL no_more_products = 1 END REPEAT; CLOSE cur_product; SELECT * FROMinfologs; DROP TABLE infologs; END$$ DELIMITER;