간단히 저장 프로시저는 하나 이상의 SQL 문을 조합한 것으로 배치 파일로 간주할 수 있지만 그 역할은 배치 처리에만 국한되지 않습니다.
(1)저장 프로시저 만들기
(2)저장 함수 만들기
(3)변수 사용
(4)조건 및 핸들러 정의
(5)커서 사용
(6)프로세스 제어 사용
( 무료 학습 권장사항: mysql 비디오 튜토리얼)
저장 프로시저를 생성하려면 create 프로시저 문을 사용해야 합니다. 구문 형식은 다음과 같습니다. create procedure 语句，基本语法格式如下：
create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body
create procedure 为用来创建存储函数的关键字；sp_name为存储过程的名称；proc_parameter为存储过程的参数列表，列表形式如下：
[in | out | inout] param_name type
characteristics指定存储过程的特征，有以下取值：
language SQL ： 说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是language特性的唯一值。
[not] deterministic: 指明存储过程执行的结果是否正确。deterministic表示每次执行存储过程时，相同的输入会得到相同的输出；而not deterministic表示相同的输入可能得到不同的输出。默认为not deterministic。
{contains SQL | no SQL | reads SQL date | modifies SQL date } ：指明子程序使用SQL语句的限制。contains SQL表明子程序包含SQL语句；no SQL表明子程序不包含SQ；reads SQL data 表明子程序包含读数据的语句；modifies SQL data表明子程序包含写数据的语句。默认为contatins SQL。
SQL security {definer | invoker}：指明谁有权限来执行。definer表示只有定义者才能执行；invoker表示拥有权限的调用者可以执行。默认为definer。
comment ‘string’：注释信息，可以用来描述存储过程或函数。
routine_body是SQL代码的内容，可以用begin…end来表示SQL代码的开始和结束。
【例1】创建查看fruits表的存储过程，代码语句如下：
create procedure proc() BEGIN select * from fruits; END ;
这个代码创建了一个查看fruits表的存储过程，代码执行过程如下:
mysql> delimiter //mysql> create procedure Proc() -> begin -> select * from fruits; -> end //Query OK, 0 rows affected (0.36 sec)mysql> delimiter ;
【例2】创建名称为CountProc的存储过程，代码如下：
create procedure CountProc (OUT paraml int)beginselect count(*) into paraml from fruits;end;
上述代码创建了一个获取fruits表记录条数的存储过程,名称是CountProc，count(*)计算后把结果放入参数paraml中。代码的执行结果如下：
mysql> delimiter //mysql> create procedure CountProc(OUT paraml int ) -> begin -> select count(*) into paraml from fruits; -> end //Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;
创建存储函数，需要使用create function语句，基本语法如下：
create function func_name ( [ func_parameter] ) returns type [characteristic ...] routine_body
[in | out | inout] param_name typecreate function NameByZip()returns char( 50)return(select s_name from suppliers where s_call ='48075');
mysql> delimiter //mysql> create function NameByZip() -> returns char(50) -> return (select s_name from suppliers where s_call = '48075'); -> //Query OK, 0 rows affected (0.06 sec)mysql> delimiter;
in은 입력 매개변수를 나타내고 out은 출력 매개변수를 나타냅니다. inout은 둘 다를 나타냅니다. param_name은 매개변수 이름을 나타내고
언어 SQL: 설명 routine_body 부분은 현재 시스템에서 지원하는 언어는 SQL이며, 언어 속성의 유일한 값은 SQL입니다.
[not] 결정적: 저장 프로시저 실행 결과가 올바른지 여부를 나타냅니다. 결정적이란 저장 프로시저가 실행될 때마다 동일한 입력이 동일한 출력을 얻는다는 것을 의미하고, 결정적이지 않은 것은 동일한 입력이 다른 출력을 얻을 수 있음을 의미합니다. 기본값은 결정적이지 않습니다.
🎜{SQL 포함 | SQL 읽기 없음 | SQL 날짜 수정 }: 서브루틴에 의한 SQL 문 사용에 대한 제한 사항을 나타냅니다. SQL 포함은 서브루틴에 SQL 문이 포함되어 있음을 나타내고, SQL이 없다는 것은 서브루틴에 SQ가 포함되어 있지 않음을 나타내고, SQL 데이터 수정은 서브루틴에 데이터 쓰기용 명령문이 포함되어 있음을 나타냅니다. 기본값은 SQL을 포함하는 것입니다. 🎜🎜🎜🎜SQL 보안 {definer | 호출자}: 실행 권한이 있는 사람을 나타냅니다. 정의자는 정의자만 실행할 수 있음을 의미하고, 호출자는 권한이 있는 호출자가 실행할 수 있음을 의미합니다. 기본값은 정의자입니다. 🎜🎜🎜🎜comment 'string': 저장 프로시저나 함수를 설명하는 데 사용할 수 있는 주석 정보입니다. 🎜🎜🎜🎜routine_body는 SQL 코드의 내용입니다. Begin...end를 사용하여 SQL 코드의 시작과 끝을 나타낼 수 있습니다. 🎜🎜[예제 1] 과일 테이블을 보기 위한 저장 프로시저를 생성합니다. 🎜declare var_name[,varname]... date_type [default value];
declare myparam int default 100;
set var_name = expr [,var_name = expr]...;
declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;
select col_name[,...] into var_name[,...] table_expr;
[ 형식입니다. in | out | inout] param_name 유형🎜🎜🎜여기서 in은 입력 매개변수를 나타내고, inout은 입력과 출력을 모두 나타내며, type은 매개변수 유형을 나타냅니다. 함수가 반환하는 데이터 유형은 저장 함수의 특성을 지정하며 해당 값은 저장 프로시저를 생성할 때 사용됩니다. 🎜🎜[예제 3] NameByZip이라는 저장 함수를 만듭니다. 이 함수는 숫자 유형은 문자열입니다. 🎜declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id='a1;
declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code
[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148
【例4】定义名称为myparam的变量，类型为int类型，默认值为100，代码如下：
declare myparam int default 100;
2.为变量赋值
set var_name = expr [,var_name = expr]...;
存储程序中的set语句是一般set语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。
【例5】声明3个变量，分别为var1，var2和var3，数据类型为int，使用set为变量赋值，代码如下：
declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;
MySQL中还可以通过select…into为一个或多个变量赋值，语句如下：
select col_name[,...] into var_name[,...] table_expr;
这个select语法把选定的列直接存储到对应位置的变量。col_name表示字段名称;var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和where子句。
【例6】声明变量fruitname和fruitprice，通过select…into语句查询指定记录并为变量赋值,代码如下:
declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id='a1;
特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。
1.定义条件
定义条件使用declare语句，语法格式如下：
declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code
例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值为1142。
这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的declare handler语句中。
【例7】定义"error 1148(42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：
[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148
2.定义处理程序
定义处理程序时，使用declare语句的语法如下：
declare handler_type handler for condition_value[,...] sp_statement handler_type: continue|exit|undo condition_value: sqlstate[value] sqlstate_value |condition_name |sqlwarning |not found |sqlexception |mysql_error_code
其中，
condition_value表示错误类型，可以有以下取值：
sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。
【例8】定义处理程序的几种方式如下：
方法1：捕获sqlstate_valuedeclare continue handler for sqlstate '42S02' set @info='No_SUCH_TABLE';方法2:捕获mysql_error_codedeclare continue handler for 1146 set @info='No_SUCH_TABLE';方法3:先定义条件,然后调用declare no_such_table condition for 1146;declare continue handler for NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare exit handler for sqlwarning set @info='ERROR';方法5:使用not founddeclare exit handler for not found set @info=' NO_SUCH_TABLE ';方法6:使用sqlexceptiondeclare exit handler forsqlexception set @info='ERROR';
上述代码是6种定义处理程序的方法。
第一种，捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第二种，捕获mysql_error_code值。如果遇到mysql_error_code值为1146，就执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第三种，先定义条件再调用条件。这里先定义no_such_table条件，遇到1146错误就执行continue操作。
第四种，使用sqlwarning。sqlwarning捕获所有以01开头的sqlstate_value值，然后执行exit操作，并且输出"ERROE"信息。
第五种，使用not found。not found捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且输出"NO_SUCH_TABLE"信息。
第六种，使用SQLEXCEPTION。sqlexception捕获所有没有被sqlwarning或not found捕获的sqlstate_value值，然后执行exit操作，并且输出"ERROR"信息。
【例9】定义条件和处理程序，具体执行的过程如下：
mysql> create table test.t(s1 int,primary key (s1));Query OK, 0 rows affected (0.14 sec)mysql> delimiter //mysql> create procedure handlerdemo() -> begin -> declare continue handler for sqlstate '23000' set @x2=1; -> set @x =1; -> insert into test.t values(1); -> set @x=2; -> insert into test.t values(1); -> set @x=3; -> end; -> //Query OK, 0 rows affected (0.06 sec)[调用存储过程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看调用过程结果]mysql> select @x;+------+| @x |+------+| 3 |+------+1 row in set (0.00 sec)
可以看到，@x 是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序末尾。
MySQL中光标只能在存储过程和函数中使用。
查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。
1.声明光标
MySQL中使用declare关键字来声明光标，语法形式如下：
declare cursor_name cursor for select_statement
其中，cursor_name参数表示光标的名称；select_statement表示select语句的内容，返回一个用于创建光标的结果集。
【例10】声明名称为cursor_fruit的光标，代码如下：
declare cursor_fruit cursor for select f_name,f_price from fruits;
该代码中光标名称为cursor_fruit，select语句部分从fruits表汇总查询出f_name和f_price字段的值。
2.打开光标
open cursor_name{光标名称}这个语句打开先前声明的名称为cursor_name的光标。
【例11】打开名称为cursor_fruit的光标，代码如下：
open cursor_fruit ;
3.使用光标
使用光标的语法格式:
fetch cursor_name into var_name [，var_name ] ... {参数名称}其中，cursor_name参数表示光标的名称；var_name表示将光标中的select语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。
【例12】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price两个变量中，代码如下:
fetch cursor_fruit into fruit_name,fruit_price;
4.关闭光标
关闭光标的语法格式:
close cursor_name(光标名称)
这个语句关闭先前打开的光标。
如果未被明确地关闭，那么光标将在它被声明的复合语句的末尾被关闭。
【例13】关闭名称为cursor_fruit的光标，代码如下：
close cursor_fruit;
流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。每个流程中可能包含一个单独语句，或者是使用begin…end构造的符合语句，构造可以被嵌套。
1.if语句
if语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法格式如下：
if expr_condition then statement_list [elseif expr_condition then statement_list]... [else statement_list]end if
如果expr_condition求值为真，相应的SQL语句列表被执行；如果没有expr_condition匹配，则else子句里的语句列表被执行。statement_list列表可包括一个或多个语句。
MySQL中还有一个if()函数，它不同于这里描述的if语句。
【例14】if语句示例
if val is null then select ‘val is null’; else select 'val is not null';end if
该示例判断val值是否为空，如果为空输出字符串"val is null"；否则输出字符串"val is not null"。if语句都需要使用end if来结束。
2.case语句
case是另一个进行条件判断的语句，有两种语句格式，第一种：
case case_expr when when_value then statement_list [when when_value then statement_list]... [else statement_list]end case
【例15】使用case流程控制语句的第1种格式，判断val值等于1、等于2或者两者都不等，SQL语句如下：
case val when 1 then select ‘val is 1’； when 2 then select ‘val is 2’； else select ‘val is not 1 or 2’；end case;
当val值为1时，输出字符串"val is 1";当val值为2时，输出字符串"val is 2";否则输出字符串"val is not 1 or 2"。
case语句的第2种格式如下：
case when expr_condition then statement_list [when expr_condition then statement_list] [else statement_list]end case
该语句中，when语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应then关键字后面的statement_list语句。如果没有条件匹配，else子句里的语句被执行。
注意：存储程序中的case语句和case控制流程函数的区别：
存储程序中的case语句不能有else null子句，并且用end case替代end来终止。
【例16】使用case流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0，SQL语句如下：
case when val is null then select ‘val is null’; when val < 0 then select 'val is less than 0'; when val > 0 then select 'val is greater than 0'; else select 'val is 0';end case;
当val值为空时，输出字符串"val is null";当val值小于0时，输出字符串"val is less than 0";当val值大于0时，输出字符串"val is greater than 0";否则输出字符串"val is 0"。
3.loop语句
loop循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作过的过程，并不进行条件判断。退出循环过程使用leave子句。loop语法格式如下:
[loop_label:] loop statement_listend loop [loop_label]
loop_label表示loop语句的标注名称，该参数可省略。statement_list参数表示需要循环执行的语句。
【例17】使用loop语句进行循环操作，id值小于等于10之前，将重复执行循环过程，SQL语句如下：
declare id int default 10add_loop:loopset id = id +1; if >=10 then leave add_loop; end if;end loop add_ loop;
该示例循环执行id加1的操作。当id值小于10时，循环重复执行。当id值大于或等于10时，使用leave语句退出循环。loop循环都以end loop结束。
4.leave语句
leave语句用来退出任何被标注的流程控制构造，leave语句基本格式如下：
leave label
其中，label参数表示循环的标志。leave和begin…end或循环一起被使用。
【例18】使用leave语句退出循环，代码如下：
add_num:loopset @count=@count+1;if @count=50 then leave add_num;end loop add_num;
该示例循环执行count加1的操作，当count的值等于50时，使用leave语句跳出循环。
5.iterate语句
iterater label语句将执行顺序转到语句段开头处，语法格式如下：
iterate label
iterate只可以出现在loop、repeat和while语句内。iterate的意思为"再次循环"，label参数表示循环的标志。iterate语句必须跟在循环标志前面。
【例19】iterate语句示例：
create procedure doiterate()begin declare p1 int default 0; declare p1 int default 0; my_loop:loop; set p1 = p1 + 1; if p1 < 10 then iterate my_loop; elseif p1 > 20 then leave my_loop; end if; select 'p1 is between 10 and 20';end loop my_loop;end
首先定义p1=0，当p1的值小于10时重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息"p1 is between 10 and 20";当p1大于20时，退出循环。
6.repeat语句
repeat语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。repeat语句的语法格式如下：
[repeat_label:] repeat statement_list until expr_conditionend repeat [repeat_label]
repeat_label为repeat语句的标注名称，该参数可以省略；repeat语句内的语句或语句群被重复，直至expr_condition为真。
【例20】repeat语句示例，id值等于10之前，将重复执行循环过程，代码如下：
declare id int default 0；repeatset id = id + 1； until id >= 10end repeat;
该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。repeat循环都以end repeat结束。
7.while语句
while语句创建一个带条件判断的循环过程，与repeat不同，while在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。while语句的基本格式如下：
[while_label:] while expr_condition do statement_listend while [while_label]
【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：
declare i int default 0；while i <10 doset i = i + 1;end while;
相关免费学习推荐：mysql数据库(视频)
위 내용은 MySQL의 저장 프로시저(저장 프로시저 및 함수) 생성에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
