A stored procedure is a subprogram that does not have a direct return value. However, when the parameter type is OUT, a stored procedure can also return values to the caller.
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
A stored procedure is created using the CREATE PROCEDURE statement.
The
DECLAREblock must be placed within aBEGIN ... END;block, and other statements can only be defined after all declarations are completed.You can define
sp_create_chisticinformation such asDETERMINISTIC / LANGUAGE SQLto enrich the usage of stored procedures.Stored procedures in OceanBase AI Database do not support overloading.
Example 1: Create a stored procedure without 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 IN and OUT parameters.
// Create the sample table 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 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
Continue to check 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
A created stored procedure must be invoked using the CALL statement; it cannot be used as part of an SQL expression.
//Call the stored procedure my_proc
obclient> CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected
//Check 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