Stored Procedures and functions are bolstered in the basic version SQL and MySQL 5.0. Stored Procedures is an accumulation of statements, which permits adaptability and consistence for a software engineer in light of the fact that put away strategy are anything but difficult to execute in the database operations. Stored Procedures are useful where different number of client applications are composed in various languages or it can work on different stages. However they have to perform the same database operations. A store procedure can order another store procedure. In Stored Procedures, less information will be sent between the user and the server which enhances the execution in the database server.

Create procedure is utilized to create a procedure with the procedure name followed by parameters within the parentheses. Parameters can be announced for accessing the datatypes except few attributes. The procedure can adjust the values however when the procedure gives back the values then alterations is not noticeable to the client.

By using the below example, the concept of create procedure can be easily understood. [sql]sql> select * from employee01; +--------+----------+-----------+-------------------+--------+-------+ | emp_id | emp_name | city | designation | salary | perks | +--------+----------+-----------+-------------------+--------+-------+ | 1 | david | delhi | manager | 12000 | 855 | | 2 | shaha | mumbai | assistant manager | 13000 | 853 | | 3 | sha | puna | scales manager | 11000 | 850 | | 4 | jack | bangalore | designer | 14000 | 854 | | 5 | james | mangalore | web designer | 15000 | 1124 | | 6 | mike | chennai | develpoer | 15500 | 840 | +--------+----------+-----------+-------------------+--------+-------+ 6 rows in set (0.00 sec) sql> delimiter $$ sql> create procedure proc3(out p1 int) -> select count(*) into p1 from employee01; -> $$ Query OK, 0 rows affected (0.00 sec) sql> delimiter ; sql> call proc3(@a); Query OK, 1 row affected (0.00 sec) sql> select @a; +------+ | @a | +------+ | 6 | +------+ 1 row in set (0.00 sec) [/sql] In the above example, the procedure proc3 will count all the employees in the employee01 table from the IN parameter and pass the values to OUT parameter.(i.e, proc3 will count all the emp_id from the table employee01 and display the output).

Function is a accumulation of statements, which contains some particular storage in the database server. The function takes a parameter and is utilized to perform an operation by using SQL and return the outcome set. The function can call a function at any time, once the code is stored in the database.

By using the below example, the concept of create function can be easily understood. [sql] sql> CREATE FUNCTION func1(str CHAR(20)) -> RETURNS CHAR(50) -> RETURN CONCAT('WELCOME TO, ',str,'!'); Query OK, 0 rows affected (0.00 sec) sql> SELECT func('ITOOLSINFO.COM'); +-----------------------------+ | func('ITOOLSINFO.COM') | +-----------------------------+ | WELCOME TO, ITOOLSINFO.COM! | +-----------------------------+ 1 row in set (0.00 sec)[/sql] In the above example, the function will read the string character and display the output as welcome to ITOOLSINFO.COM.

Drop procedure is utilized to drop a procedure. For dropping them privileges are required. IF EXISTS clause is accessible, then it keeps them from causing an error and when the procedure does not exist its produces a notice.

By using the below example, the concept of drop procedure can be easily understood. [sql] sql> Drop procedure if exits proc3; Query OK, 0 rows affected (0.30 sec)[/sql] In the above example, drop procedure will drop the procedure name. (i.e., drop procedure will drop the proc3 name from the database).

Drop function is utilized to drop a function. For dropping them, privileges are required. IF EXISTS statement is accessible, then it keeps from causing a mistake and when the function does not exist it produces a notice.

[sql]sql> DROP FUNCTION IF EXISTS func1; Query OK, 0 rows affected (0.30 sec)[/sql] In the above example, drop function will drop the function name.(i.e., drop function will drop the function name func1 from the database.)

• Stored Procedures are the objects created on the database server.
• Function are objects that reside on the database server.
• Create procedures/functions are used to create procedures and functions.
• Drop procedures/functions are used to drop procedures and functions.