存储过程和函数-数据库

1.1、创建存储过程和函数

1.1.1、创建存储过程

---

create procedure 存储过程名([in|out|inout] 参数名称 参数类型],.....)
[characteristic...] rountie_body

• in 表示输入参数
• out 表示输出参数
• inout表示既可以输入也可以输出

characteristic 指定存储过程的特性

• language sql ： 说明rountie_body部分是由sql语句组成的，当前系统的支持的语言为sql，sql是language特性的唯一值
• [not] deterministic ：说明存储过程执行的结果是否正确。deterministic表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值。默认为not deterministic。
• {contains sql|no sql|reads sql data|modifies sql data}：指明子程序使用sql语句的限制。contians sql 表明子程序包含sql语句，但是不包含读写数据的语句；no sql 表明子程序不包含sql语句；reads sql data说明子程序包含读数据的语句；modifies sql data 表明子程序包含写数据的语句。默认情况下，系统会指定为contains sql。
• sql security { definer|invoker} ：指明谁有权限来执行。definer表示只有定义者才能执行。invoker表示拥有权限的调用者可以执行。默认值为 definer
• commit “string” ： 注释信息，可以描述存储过程或函数

---

存储过程体

create procedure 存储过程名()
begin
	/*过程体*/
end;

• begin和end语句用来限定存储过程体

1.1.2、创建存储函数

---

create function 函数名([in|out|put] 参数名称 参数类型]...)
	returns type
[characteristic...] rountie_body

---

存储函数体

create function 函数名()
returns 返回值类型
/*
	函数体
*/
return 返回值;

1.2、变量的使用

1.2.1、在存储过程中定义变量

---

定义变量

declare 变量1[,变量2...] 变量类型 [default 初始值];

---

为变量赋值

• 定义变量后，为变量赋值可以改变变量的默认值，mysql中使用set语句为变量赋值

set 变量名1=值/表达式,[变量名2=值/表达式...];

---

示例

declare x int,y int,z int;
set x=1;
set y=2;
set z=x+y;

1.3、定义条件和处理程序

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

---

定义条件

• 定义条件使用declare语句，与定义变量的关键字相同

declare condition_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 为数值类型错误代码。

另外

• 这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的declaer handler语句中。

---

示例

• 定义“ERROR 1148(42000)"错误，名称为command_not_allowed

/*1.使用sqlstate_value定义*/
declare command_not_allowed condition for sqlstate '42000';
/*2.使用mysql_error_code定义*/
declare command_not_allower condition for 1148;

---

定义处理程序

• 定义处理程序时，使用declare语句的语法如下

declare handler_type handler for condition_value[,...] sp_statement
handler_type:
	continue|exit|undo
condition_value:
	sqlstatep [value] sqlsate_value
	|condition_name
	|sqlwarning
	|not found
	|sqlexception
	|mysql_error_code

参数含义

1.4、光标的使用

---

概述

• 查询语句可能返回多条记录，如果数据盘非常大，需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要接动或浏览其中的数据。

---

1.声明光标

declare cursor_name cursor for select_statement;

参数含义

• cursor_name 光标的名称
• select_statement 表示select语句的内容 返回一个用于创建光标的结果集

---

2.打开光标

open cursor_name; 

---

3.使用光标

fetch cursor_name into var_name[,var_name...];

参数含义

• var_name 参数表示将光标中select语句查询出来的信息存入到该参数中，var_name必须提前声明

---

4.关闭光标

close cursor_name;

• 建议在光标使用完毕之后，关闭光标

1.5、流程控制的使用

---

概述

• 流程控制语句用来根据条件控制语句的执行. MySQL 中的用来构造控制流程的话’句有： IF 语句， CASE 语句 、 LOOP 语句、 WHILE 语句、 LEAVE 语句、 ITERATE 语句、 REPEAT语句和 WHILE语句。

1.5.1、分支语句

---

1.if语句

if expr_condition 
then
	/*执行语句*/
elseif expr_condition
then
	/*执行语句*/
else
	/*执行语句*/
end if; /*结束*/

---

2.case语句

case的第一种形式

case case_expr
	when when_value then /*执行语句*/;
    when when_value then /*执行语句*/;
    ...
    else
    	/*执行语句*/
end case;

• case语句相当于 Java中switch case 语句
• case_expr 表示条件表达式
• when_value表示表达式可能的值
• else 相当于 default语句

case的第二种形式

此时when语句将被逐个执行，直到某个expr_ condition表达式为真，则执行对应when关

键字后面的语句。如果没有条件匹配， else子句里的语句被执行。

case 
	when expr_condition then /*执行语句*/;
    when exor_condition then /*执行语句*/;
    ...
    else
    	/*执行语句*/
end case;

• expr_condition参数表示条件判断语句

1.5.2、循环语句

---

1.loop语句

• loop语句用于重复执行某些语句，只是创建一个循环操作的流程，并不进行条件的判断。可以使用leave子句跳出循环

[loop_label:]loop
	/*执行语句*/
end loop [loop_label]

• loop_label表示LOOP语旬的标注名称， 该参数可以省略

---

2.repeat语句

• 每循环一次，再对条件进行判断。循环体至少执行一次。
• 条件为真，循环结束

[repeat_label:]repeat
	/*执行语句*/
	until /*判断条件*/
end repeat [repeat_label]

---

3.while语句

• 在循环之前，先对条件进行判断。
• 条件为真，继续循环

[while_label:] while expr_condition 
do
	/*执行语句*/
end while [while_label];

1.5.3、leave和iterate语句

---

leave语句

• leave语句用来退出任何被标注的流程控制构造
• 相当于break语句

leave label;

---

iterate语句

• iterate语句跳出本次循环，执行下一次循环
• 相当于continue

iterate label;

1.6、调用存储过程或函数

---

调用存储过程

CALL sp_name([paramete[，．．．］］｝

• CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程， 其中sp_name为存储过程名称， parameter为存储过程的参数．

---

调用存储函数

• 在MySQL中， 存储函数的使用方法与MySQL内部函数的使用方法是一样的。 换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。 区别在于， 存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

select 函数名(参数列表);

1.7、注意

---

1. 在存储函数和存储过程中 select "输出内容"相当于输出语句

1.8、示例

---

1.创建一个名称为sch的数据表，将表中的数据插入到sch表中

create table sch(id int primary key, name varchar(50) not null,glass varchar(50) not null);
insert into sch values(1,'xiaoming','glass 1'),(2,'xiaojun','glass 2');

2.创建一个存储过程函数用来统计sch表中的记录数

create function count_sch()
returns int /*指定返回值类型*/
return (select count(*) from sch); /*函数的返回值*/

3.创建一个存储过程，通过调用存储函数的方法来获取sch表中的记录数和sch表中id的和

create procedure add_id(out `count` int)
begin
    declare itmp int;
    declare cur_id cursor for select id from sch; /*定义光标*/
    declare exit handler for not found close cur_id;
    select count_sch() into count;
    set @sum=0;
    open cur_id; /*开启光标*/
    repeat
    fetch cur_id into itmp; /*使用光标*/
    if itmp<10
	then set @sum=@sum+itmp;
    end if;
    until 0 /*条件为0，表示为假，则一直循环*/ 
    end repeat;
    close id; /*关闭光标*/
end