CREATE PROCEDURE

CREATE PROCEDURE 语句用于在当前数据库中创建存储过程，与 MySQL 中 CREATE PROCEDURE 语句的行为类似。

语法图

CreateProcedureStmt
procParameters
procParameter
type
ProcedureName
IfNotExists
characteristics
characteristic
SecurityInfo
routineBody
BlockStmt
CaseStmt
simpleCaseList
simpleCase
searchCaseList
searchCase
IfStmt
elseIfList
elseIf
LoopStmt
RepeatStmt
WhileStmt
CloseCur
DeclareVar
varNames
varName
conditionValues
FetchInto
OpenCur
ProcedureSQL
statementList
statement
handler_action
conditionValue

CreateProcedureStmt ::=
    'CREATE' ('DEFINER' '=' user)? 'PROCEDURE' (IfNotExists)? ProcedureName (procParameters)?
    (characteristics)?  routineBody

procParameters ::=
    procParameter
    | procParameter ',' procParameters

procParameter ::=
    ""
    | ('IN')?  param_name type
    | 'OUT'  param_name type
    | 'INOUT'  param_name type

type ::=
    Any valid MySQL data type (exception json)

ProcedureName ::=
    Identifier ('.' Identifier)?

IfNotExists ::=
    'IF' 'NOT' 'EXISTS'

characteristics ::=
    characteristic
    |characteristic ',' characteristics

characteristic ::=
  ""
  | 'COMMENT' string
  | 'SQL' 'SECURITY' SecurityInfo

SecurityInfo ::=
   'DEFINER'
  | 'INVOKER'

routineBody ::=
    statement

BlockStmt ::=
(begin_label ':')? 'BEGIN'
    (statementList)?
    'END' (end_label)?

CaseStmt ::=
     'CASE'
     simpleCaseList
     ('ELSE' statementList)?
     'END' 'CASE'
    |'CASE' case_value
        searchCaseLists
     ('ELSE' statementList)?
     'END' 'CASE'

simpleCaseList ::=
    simpleCase
    |simpleCaseLists  simpleCase

simpleCase ::=
    'WHEN' search_condition 'THEN' statementList

searchCaseList ::=
    searchCase
    |searchCaseList  searchCase

searchCase ::=
    'WHEN' when_value 'THEN' statementList

IfStmt ::=
    'IF' search_condition 'THEN' statementList
    (elseIfList ) ?
    ('ELSE' statementList)?
    'END' 'IF'

elseIfList ::=
     elseIf
    | elseIfList elseIf

elseIf ::=
    'ELSEIF' search_condition 'THEN' statementList

LoopStmt ::=
    (begin_label ':')? 'LOOP'
        statementList
    'END' 'LOOP' (end_label)?

RepeatStmt ::=
    (begin_label ':')? 'REPEAT'
        statementList
        'UNTIL' search_condition
    'END' 'REPEAT' (end_label)?

WhileStmt ::=
    (begin_label ':')? 'WHILE' search_condition 'DO'
        statementList
    'END' 'WHILE' (end_label)?

CloseCur ::=
    'CLOSE' cursor_name

DeclareVar ::=
     'DECLARE' varNames type ('DEFAULT' value)?
    |'DECLARE' handler_action 'HANDLER'
     'FOR' conditionValues statement
    |'DECLARE' cursor_name 'CURSOR' 'FOR' select_statement

varNames ::=
    varName
    |varName ',' varNames

varName ::=
    string

conditionValues ::=
    conditionValue
    | conditionValue ',' conditionValues

FetchInto ::=
    FETCH ((NEXT)? FROM)? cursor_name 'INTO' varNames

OpenCur ::=
    'OPEN' cursor_name

ProcedureSQL ::=
     SelectStmt
    |UpdateStmt
    |InsertStmt
    |DeleteStmt
    |ShowStmt
    |ExplainStmt
    |CreateTableStmt
    |AlterTableStmt
    |DropTableStmt

statementList ::=
    statement ';'
    statement ';' statementList

statement ::=
     DeclareVar
    |FetchInto
    |OpenCur
    |CloseCur
    |WhileStmt
    |RepeatStmt
    |LoopStmt
    |IfStmt
    |CaseStmt
    |BlockStmt
    |ProcedureSQL

handler_action ::=
    'CONTINUE'
  | 'EXIT'
  | 'UNDO'

conditionValue ::=
    mysqlErrorCode
  | 'SQLSTATE' ('VALUE')? sqlstateValue
  | 'SQLWARNING'
  | 'NOT' 'FOUND'
  | 'SQLEXCEPTION'

平凯数据库不支持以下语法 characteristic:LANGUAGE SQL,[NOT] DETERMINISTIC,{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }。

参数	含义	举例
`tidb_enable_procedure`	开启或者关闭 tidb 存储过程	`tidb_enable_procedure` = ON
`tidb_enable_sp_param_substitute`	是否开启存储过程内部变量查询下推	`tidb_enable_sp_param_substitute`=ON
`max_sp_recursion_depth`	存储过程最多支持多少次递归	`max_sp_recursion_depth` = 125
`stored_program_cache`	最多缓存多少个存储过程执行计划	`stored_program_cache` = 256

注意

平凯数据库不支持在存储过程中调用 DDL、CALL 语句，在存储过程中使用 prepare 和 execute 处于测试状态，不建议使用。

示例

创建一个存储过程：

use test
delimiter $$
create procedure t1()
begin
select 1;
end $$
delimiter ;

mysql> use test
Database changed
mysql> delimiter $$
mysql> create procedure t1()
    -> begin
    -> select 1;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;