MySQL의 저장 프로시저(저장 프로시저 및 함수) 생성에 대한 자세한 설명

간단히 저장 프로시저는 하나 이상의 SQL 문을 조합한 것으로 배치 파일로 간주할 수 있지만 그 역할은 배치 처리에만 국한되지 않습니다.

(1)저장 프로시저 만들기
(2)저장 함수 만들기
(3)변수 사용
(4)조건 및 핸들러 정의
(5)커서 사용
(6)프로세스 제어 사용

( 무료 학습 권장사항: mysql 비디오 튜토리얼)

---

(1) 저장 프로시저 생성

저장 프로시저를 생성하려면 create 프로시저 문을 사용해야 합니다. 구문 형식은 다음과 같습니다. create procedure 语句，基本语法格式如下：

create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body

create procedure 为用来创建存储函数的关键字；sp_name为存储过程的名称；proc_parameter为存储过程的参数列表，列表形式如下：

[in | out | inout] param_name type

• 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 ;

• 提示:“delimiter //“语句的作用是将MySQL的结束符设置为//，因为MySQL默认语句结束符号为分号”;”，这样做是为了避免与存储过程中SQL语句结束符相冲突。存储过程定义完之后再使用"delimiter ；“恢复默认结束符。使用delimiter命令时，应避免使用反斜杠”"，因为反斜杠是MySQL中的转义字符。

【例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 ;

(2)创建存储函数

创建存储函数，需要使用create function语句，基本语法如下：

create function func_name ( [ func_parameter] )
returns type
[characteristic ...] routine_body

• create function为用来创建存储函数的关键字
• func_name表示存储函数的名称
• func_parameter为存储过程的参数列表，参数列表形式为：[in | out | inout] param_name type

  create function NameByZip()returns char( 50)return(select s_name from suppliers where s_call ='48075');

create Procedure는 저장 함수를 생성하는 데 사용되는 키워드입니다. sp_name은 저장 프로시저의 이름입니다. proc_parameter는 저장 프로시저의 매개변수 목록입니다.

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은 매개변수 이름을 나타내고

• characteristics는 저장 프로시저의 특성을 지정합니다.

언어 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;

🎜🎜 프롬프트: "구분자 //" 문 이 함수는 MySQL의 기본 문 끝 기호가 세미콜론 ";"이기 때문에 MySQL의 끝 문자를 //로 설정하는 것입니다. 이는 SQL 문 끝 문자와의 충돌을 피하기 위해 수행됩니다. 저장 프로시저. 저장 프로시저를 정의한 후 "delimiter;"를 사용하여 기본 종결자를 복원합니다. 구분 기호 명령을 사용할 때는 백슬래시 ""를 사용하지 않아야 합니다. 백슬래시는 MySQL에서 이스케이프 문자이기 때문입니다. 🎜🎜🎜[예제 2] CountProc이라는 저장 프로시저를 생성합니다. 코드는 다음과 같습니다. 🎜

set var_name = expr [,var_name = expr]...;

🎜위 코드는 과일 테이블의 레코드 수를 가져오는 저장 프로시저를 생성합니다. 이름은 CountProc입니다. ) 결과는 Parameter paraml에 저장됩니다. 코드 실행 결과는 다음과 같습니다. 🎜

declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;

(2) 저장 함수 생성 🎜🎜 저장 함수를 생성하려면 create function 문을 사용해야 합니다. 기본 구문은 다음과 같습니다. : 🎜

select col_name[,...] into var_name[,...] table_expr;

🎜🎜create 함수는 저장 함수를 생성하는 데 사용됩니다. 키워드 🎜🎜func_name은 저장 함수의 이름을 나타냅니다. 🎜🎜func_parameter는 저장 프로시저의 매개변수 목록입니다. 매개변수 목록은 [ 형식입니다. 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

🎜저장 함수에 있는 경우 ruturn 문은 함수의 return 절에 지정된 유형과 다른 유형의 값을 반환하며, 반환 값은 적절한 유형으로 강제 변환됩니다. 🎜🎜🎜참고: 매개변수를 in, out 또는 inout으로 지정하는 것은 프로시저에만 적합합니다. (in 매개변수의 기본값은 항상 function입니다.) return 절은 function에 대해서만 지정할 수 있으며 function에 대해서는 필수입니다. 함수의 반환 유형을 지정하는 데 사용되며 함수 본문에는 반환 값 문이 포함되어야 합니다. 🎜🎜🎜(3) 변수 사용🎜🎜변수는 서브 프로그램에서 선언하고 사용할 수 있으며 이러한 변수의 범위는 시작...끝 프로그램에 있습니다. 🎜🎜1. 변수 정의🎜🎜declar 문을 사용하여 저장 프로시저에서 변수를 정의합니다. 구문 형식은 다음과 같습니다. 🎜

[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148

🎜var_name은 지역 변수의 이름입니다. 기본값 절은 변수에 기본값을 제공합니다. 값은 상수로 선언되는 것 외에도 표현식으로 지정할 수도 있습니다. default 절이 없으면 초기값은 null입니다. 🎜

【例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;

(4)定义条件和处理程序

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。

1.定义条件
定义条件使用declare语句，语法格式如下：

declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code

• condition_name表示条件的名称
• condition_type表示条件的类型
• sqlstate_value和mysql_error_code都可以表示MySQL的错误
• sqlstate_value为长度为5的字符类型错误代码
• 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

其中，

• handler_type为错误处理方式，参数取3个值：continue、exit和undo。
• continue表示遇到错误不处理，继续执行；
• exit遇到错误马上退出；
• undo表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。

condition_value表示错误类型，可以有以下取值：

• sqlstate[value] sqlstate_value包含5个字符串错误值
• condition_name表示declare condition定义的错误条件名称
• sqlwarning匹配所有以01开头的sqlstate错误代码
• notfound 匹配所有以02开头的sqlstate错误代码
• sqlexception匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码
• mysql_error_code匹配数值类型错误代码

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被执行到程序末尾。

• "var_name"表示用户变量，使用set语句为其赋值。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

(5)光标的使用

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;

(6)流程控制的使用

流程控制语句用来根据条件控制语句的执行。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

• case_expr表示条件判断的表达式，决定了哪一个when语句会被执行
• when_value表示表达式可能的值。
• 如果某个when_value表达式和case_expr表达式结果相同，则执行对应的then关键字后面的statement_list中的语句。
• statement_list表示不同when_value值的执行语句。

【例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

• expr_condition表示条件判断语句
• statement_list表示不同条件的执行语句

该语句中，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 &#39;val is less than 0&#39;;
	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]

• while_label为while语句的标注名称
• expr_condition为进行判断的表达式，如果表达式结果为真，while语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：

declare i int default 0；while i <10 doset i = i + 1;end while;

相关免费学习推荐：mysql数据库(视频)

위 내용은 MySQL의 저장 프로시저(저장 프로시저 및 함수) 생성에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!