A stored procedure is a subprogram that does not directly return a value. When the parameter type is OUT, the stored procedure can also return a value to the caller.
Structure of stored procedures
The structure of a PL stored procedure is as follows:
PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...]
BEGIN -- Start execution
SQL statement; [ SQL statement; ]...
END; -- End execution
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
Creating stored procedures
Stored procedures are created by using the CREATE PROCEDURE statement. The significant differences between MySQL mode and Oracle mode are as follows:
The
DECLAREmodule must be placed in theBEGIN ... END;module. You can define other statements only after you complete the declaration.You can define
DETERMINISTIC / LANGUAGE SQLand othersp_create_chisticinformation to enrich the usage of stored procedures.Stored procedures in MySQL mode cannot be overloaded.
Example 1: Create a stored procedure that does not contain IN or OUT parameters.
obclient> DELIMITER //
obclient> CREATE PROCEDURE proc_name()
BEGIN
DECLARE var_name VARCHAR(20) DEFAULT 'ZhangSan';
SET var_name = 'LiSi';
SELECT var_name;
END //
Query OK, 0 rows affected
obclient> DELIMITER ;
obclient> CALL proc_name();
The return result is as follows:
+----------+
| var_name |
+----------+
| LiSi |
+----------+
1 row in set
Example 2: Create a stored procedure that contains IN and OUT parameters.
//Create the emp table as an example.
obclient> CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000),(202,'Pat','MK_REP',2,12000),
(119,'Karen','PU_CLERK', 4,10000),(118,'Guy','PU_CLERK', 4,10000),
(201,'Michael','MK_MAN',3,9000);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
obclient> DELIMITER //
obclient> CREATE PROCEDURE my_proc(IN emp_no INT,OUT emp_count INT)
BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
END //
Query OK, 0 rows affected
obclient> DELIMITER ;
//Initialize parameters
obclient> SET @emp_no='200',@emp_count=0;
Query OK, 0 rows affected
//Call the my_proc stored procedure
obclient> CALL my_proc(@emp_no,@emp_count);
The return result is as follows:
+-----------+
| emp_count |
+-----------+
| 1 |
+-----------+
1 row in set
Continue to view the result after emp_count is assigned:
obclient> SELECT @emp_count;
The return result is as follows:
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set
Calling stored procedures
You can call a stored procedure by using the CALL statement. However, you cannot call a stored procedure as part of an SQL expression.
//Call the my_proc stored procedure
obclient> CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected
//View the result after emp_count is assigned
obclient> SELECT @emp_count;
The return result is as follows:
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set