MySQL Server : Open Source Database : MySQL Procedure
MySQL Procedure |
Stored procedures
- Stored procedures (SPs) are an important addition to MySQL 5.0 onwards.
- These are custom programming scripts with embedded SQL statements that are stored in a compiled form and executed directly by the MySQL server, generally with no external programming interface requirements.
- SPs allow store logic rules on the database.
Why Stored Procedures?
- Faster Execution: Reduced need for data transfer back and forth between a program and the database server. Note: The use of SPs does not guarantee improved speed as a lot depends on the code inside of the SP.
- Reduced code redundancy: There generallt are similar code segments in each application for managing data such as templated inserts and updates and can be moved into a database-side SP.
- Maintenance: If there are changes in the underlying database schema, code changes can be localized to a few SPs.
- Better database security: In security-sensitive applications, such as banking, direct access to tables by user programs is a problem and with SPs, data access can be monitored, and logged if necessary. Also, one can impose centralized security rules in database procedures.
Drawbacks for SPs
- Lack of Portability: SPs are hardly portable from one database system to another, requiring significant coding and testing efforts.
- DB Server Load: Intensive use of SPs burdens the MySQL server programs, which could be a negative.
- Limited Programming: SP programming is generally not as rich as conventional development platforms such as Java or PHP. This may also lead to sub-optimal code hurting performance somewhat.
Note:Future releases of MySQL will likely offer an interface that permits creating SP code using external programming languages so SPs can use all of the features of languages such as Java.
Mysql Create Stored Procedures Syntax
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:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
Parameter
- The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
- Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.
Note : Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.
Download Examples |
Example 1:
mysql> delimiter >>
mysql>select * from stud >>
+--------+-------+
| rollno | sname |
+--------+-------+
| 1 | james |
| 3 | TOm |
+--------+-------+
2 rows in set (0.00 sec)
mysql> create procedure p1(OUT k int)
BEGIN
select count(*) into k from stud;
END;
>>
Query OK, 0 rows affected (0.00 sec)
mysql> call p1(@h) >>
Query OK, 0 rows affected (0.00 sec)
mysql> select @h; >>
+------+
| @h |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Example 2:
mysql>DELIMITER /
mysql> use myDatabase1 /
Database changed
mysql> create procedure p2()
BEGIN
SELECT * from stud;
END;
/
Query OK, 0 rows affected (0.00 sec)
mysql> call p2/
+--------+-------+
| rollno | sname |
+--------+-------+
| 1 | james |
| 4 | sa |
| 3 | TOm |
+--------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure p3(IN rn int)
BEGIN
select * from stud where rollno=rn;
END;
/
Query OK, 0 rows affected (0.00 sec)
mysql> call p3(1)/
+--------+-------+
| rollno | sname |
+--------+-------+
| 1 | james |
+--------+-------+
1 row in set (0.00 sec)
mysql> call p3(3)/
+--------+-------+
| rollno | sname |
+--------+-------+
| 3 | TOm |
+--------+-------+
Example 3:
mysql>delimiter /
mysql> create procedure find_pwd(INOUT un text)
BEGIN
select password into un
from admin
where username=un;
END;
/
Query OK, 0 rows affected (0.00 sec)
mysql> select * from admin; /
+----------+----------+
| username | password |
+----------+----------+
| tom1 | cat |
| tom | cat |
| anuj | jAMes |
+----------+----------+
3 rows in set (0.00 sec)
mysql> set @p='tom'/
Query OK, 0 rows affected (0.00 sec)
mysql> call find_pwd(@p)/
Query OK, 0 rows affected (0.00 sec)
mysql> select @p/
+------+
| @p |
+------+
| cat | (we are getting password of tom)
+------+
Alter PROCEDURE
Alter PROCEDURE Syntax
ALTER PROCEDURE proc_name [characteristic ...]
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Alter PROCEDURE Example
Alter PROCEDURE will help to alter PROCEDURE logic in database.
mysql>alter procedure find_pwd(INOUT un text)
BEGIN
select password into un
from admin
where username=un;
END;
/
Drop PROCEDURE
Drop PROCEDURE Syntax
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
Drop PROCEDURE Example
Drop PROCEDURE will help to remove PROCEDURE from database.
mysql>DROP PROCEDURE find_pwd;
Recent Comments