Stored Procedures and Functions

Ads

Stored Routines (Procedures and Functions) are supported in version MySQL 5.0. Stored Procedure is a set of statements, which allow ease and flexibility for a programmer because stored procedure is easy to execute than reissuing the number of individual S

Stored Procedures and Functions

Stored Routines (Procedures and Functions) are
supported in version MySQL 5.0. Stored Procedure is a set of statements, which allow ease and flexibility for a programmer because stored procedure is easy to execute than reissuing the number of individual SQL statements. Stored procedure can call another stored procedure also.
Stored Procedure can very useful where multiple client applications are written in different languages or it can be work on different platforms but they need to perform the same database operations.

Store procedure can improve the performance because by using the stored procedure less information needs to be sent between the server and the client. It increase the load on the database server because less work is done on the client side and much work is done on the server side.

CREATE PROCEDURE Syntax

The general syntax of Creating a Stored Procedure is :CREATE PROCEDURE proc_name ([proc_parameter[......]])
routine_body

The parameter list is available with in the parentheses.
Parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used. By default each parameter is an IN parameter. For
specifying other type of parameter used the OUT or INOUT keyword before the parameter name.

An IN parameter is used to pass the value into a procedure. The procedure can be change the value but when the procedure return the value then modification is not visible to the caller.
An OUT parameter is used to pass the value from the procedure to the caller but its visible to the caller. An INOUT parameter is initialized by the caller and it can be modified by the procedure, and any change made by the procedure is visible to the caller.

For each OUT or INOUT parameter you have to pass a user ?defined variable because then the procedure returns the value then only you can obtain it values. But if you invoking the procedure from the other procedure then you can also pass a routine parameter or variable as an IN or INOUT parameter.

The routine_body contains the valid SQL procedure statement that can be a simple statement like SELECT or INSERT or they can be a compound statement written using BEGIN and END. Compound statement can consists declarations, loops or other control structure.

Now we are describing you a example of a simple stored procedure which uses an OUT parameter. It uses the mysql client delimiter command for changing the statement delimiter from ; to // till the procedure is being defined.
Example :

The RETURN clause is mandatory for FUNCTION. It used to indicate the return type of function.

Now we are describing you a simple example a function. This function take a parameter and it is used to perform an operation by using an SQL function and return the result. In this example there is no need to use delimiter
because it contains no internal ; statement delimiters. Example :

ALTER PROCEDURE and ALTER FUNCTION Syntax

For creating the procedure or function we used the CREATE PROCEDURE | FUNCTION statement and for altering the procedure we used the ALTER PROCEDURE | FUNCTION statement. Alter Procedure statement is used to change access permissions that preserves by the procedure. And ALTER PROCEDURE needs the use of the same encryption and recompile option as the original CREATE PROCEDURE command. ALTER PROCEDURE | FUNCTION statement can be used for renaming the stored procedure or function and for changing it characteristics also. We can specify the more than one changes also in an ALTER PROCEDURE | FUNCTION statement. But for this you required the ALTER ROUTINE privilege.

With the ALTER PROCEDURE Statement you can change only
the characteristics and if you want to change in statement list then you have to
DROP the procedure and CREATE again.

DROP PROCEDURE and DROP FUNCTION Syntax

DROP PROCEDURE | FUNCTION Statement is used to drop a Procedure or Function. But for dropping them you must have the ALTER ROUTINE privilege. If IF NOT EXISTS clause is available then its prevents you from occurring an error when the procedure or function does not exist its produced only a warning.DROP {PROCEDURE | FUNCTION} [IF EXISTS]
{proc_name | func_name};

The following example shows you a syntax of Dropping
procedure and function if it exists : Examples :

CALL Statement Syntax

The CALL statement is used to call a procedure, which has been defined previously.
CALL can return the values to its caller through its parameters that are declared as OUT or INOUT parameters. This statement is also used to returns the number of rows affected that a client program can obtain at the SQL level by calling the ROW_COUNT().
The general syntax of CALL Statement is :CALL p_name([parameter[,...]])

DECLARE Statement Syntax

The DECLARE statement is used to specify the various items. Its allowed only inside the BEGIN?.END compound statements. Declarations must follow the order like Cursor must be declare before declaring handlers and the variables and conditions must be declare before declaring either handler or cursors

DECLARE Local Variable

The general syntax of declaring local variable is :DECLARE var_name[,...] type
[DEFAULT value]

DECLARE statement is used for declaring the local variables. DEFAULT clause is used for providing a default value to the variable but if this clause is missing then the initial value is NULL. Local variable scope is limited within the BEGIN?.END compound block.

In stored procedure SET statement is extended version of general SET statement and its implements as part of the preexisting SET Syntax. It allows an extended syntax of j=a , k=b?.. where different variables
types (like local variables, global variables etc) can be mixed.

SELECT......INTO Statement Syntax

The general syntax of SELECT....INTO Statement is:SELECT
column_name1,column_name2[...] INTO var_name1,var_name2[....] table_expr

This SELECT statement is used to store selected columns into variables. But by this we can retrieve only single row.
The number of columns and the number of variable name must be same in this
statement.

In the following example we are demonstrating you the
use of all above three statement. Example :

Conditions and Handlers

Some conditions needs specific handling and these conditions can be related to errors or may be general flow control inside a routine. Handlers are the methods of handling conditions that need to be dealt with.
Before describing conditions and handlers lets try to produce some errors. Example :

But in this example we get a different error number 1048 but the same SQLSTATE. If these errors occur in our functions and procedures then they will terminate out programs. To deal with these conditions we have to create a handler.
The general syntax of Handler is as follows: DECLARE handler_type HANDLER FOR condition_value[,...]
statement

Firstly we have to use DECLARE for creating a handler,
handler_type can be of the following like CONTINUE, EXIT or
UNDO. If we are using CONTINUE the the program will carry on the process after the handler has been called. When we are using EXIT then the program will end immediately. The UNDO is used on transactional tables to
rollback work carried out up to that point. HANDLER FOR tells the compiler, we are declaring a handler.
Condition_value is used so that the handler fires when a define conditions met. The
statement is section of code that we want to execute when the handler is fired.

Now we are creating a
simple procedure and in this we are trying to deal with duplicate entry. In this
procedure we are not handling the error. Example :

In the above example we got the error message but our parameter is empty because error stopped the procedure. But if we wouldn?t want that then we must use handler. In the following example lets include a handler to the procedure.
Example
:

Now in this example we didn?t get the error message and our parameter also passed out the value because when the error occurred then handler deal with the problem and continue the procedure processing.

In the above example we are using a handler to deal with SQLSTATE but the handler can deal with a set of different errors. Now in the following example we are taking the different error numbers but they had the same SQLSTATE that situation we looked at earlier.
Example :

In the above section we have seen how we can handle various conditions. Additionally MySQL allows us to define our own named conditions. But these conditions only be linked to SQLSTATE values or mysql_error_code.
Syntax for creating a conditions is as follows :DECLARE condition_name CONDITION FOR condition_value

condition_value can be the SQLSTATE [value] or mysql_error_code. In the following example we are describing how we can create a condition and how we can use it within a handler.
Example :

Rash
January 6, 2012
About this web site
This is an excellent web site to learn lot of things regarding mySQL scenarios. If you can give me any idea to update a procedure using IF conditions it would be a great help.
Thanks!!!

Jay Patel
February 26, 2012
Auto Generation of Stored Procedures
Hi,
Really nice article for beginners.
Even I also learnt few new things.
One thing I like to mention is that CRUD stored procedures are typical and they have common format for any table or database.
This can be exploited by Generating them automatically. Stored Procedure Generators are gr8 tools for it, like www.tools4sql.net

Mike
July 12, 2012
Best Example for Stored Procedure
really thanks,great tutorial for beginners and easy to understand. Again thanks for posting these type of topics.