A stored procedure is a subprogram that does not directly return a value. If the parameter type is OUT, the stored procedure can return values to the invoker.
Structure of a stored procedure
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 }
}
Create a stored procedure
You can use the CREATE PROCEDURE statement to create a stored procedure. Compared with that in Oracle mode, the statement in MySQL mode has the following features:
The
DECLAREblock must be placed inside theBEGIN ... ENDblock. You must declare all definitions before defining other statements.You can define
sp_create_chisticinformation such asDETERMINISTICandLANGUAGE SQLto enrich the use of a stored procedure.In MySQL mode, stored procedures will not overload.
Example 1: Create a stored procedure without the IN and 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 with the IN and OUT parameters
// Create a table named emp.
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 the parameters.
obclient> SET @emp_no='200',@emp_count=0;
Query OK, 0 rows affected
// Call the stored procedure my_proc.
obclient> CALL my_proc(@emp_no,@emp_count);
The return result is as follows:
+-----------+
| emp_count |
+-----------+
| 1 |
+-----------+
1 row in set
Query the result after emp_count is assigned a value.
obclient> SELECT @emp_count;
The return result is as follows:
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set
Call a stored procedure
To call a stored procedure that has been created, you can use the CALL statement. However, you cannot call the stored procedure as a part of an SQL expression.
// Call the stored procedure my_proc.
obclient> CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected
// View the assigned value of emp_count.
obclient> SELECT @emp_count;
The return result is as follows:
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set