存储过程与函数

最新推荐文章于 2023-11-14 23:05:19 发布

萍踪缘

最新推荐文章于 2023-11-14 23:05:19 发布

阅读量196

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/qq_40303031/article/details/103717921

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

存储过程与函数

1. 存储过程的定义
2. 存储过程的创建
3. 存储过程的操作
4. 自定义函数

1. 存储过程的定义

存储过程是一组完成特定功能的SQL语句集合
将常用或复杂的工作，预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程

2. 存储过程的创建

语法

CREATE PROCEDURE sp_name([proc_parameter[,...]]) [characteristic] routine_body

CREATE PROCEDURE：为创建存储过程关键字
sp_name:存储过程名称，默认为存储过程与当前数据库关联。要明确的把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name.
proc_parameter:指定存储过程的参数列表。列表形式如下：其中，IN表示输入参数；type表示参数的类型，该类型可以是MySQL数据库任意类型。

[IN / OUT / INOUT] param_name type

characteristics指定存储过程的特性，有以下取值。
- LANGUAGE SQL: 说明routine_body部分是SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
- [NOT] DETERMINISTIC: 指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，相同的输入是相同的输出；[NOT] DETERMINISTIC表示结果是不确定的，相同的输入得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC。
- COUTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA: 知名子程序使用SQL语句的限制。
  - COUTAINS SQL:表明子程序包含SQL语句，但是不包含读写数据的语句。
  - NO SQL:表明子程序不包含SQL语句
  - READS SQL DATA: 表明子程序包含写数据的语句。
  - 默认情况下，系统会指定为 CONTAINS SQL。
- SQL SECURITY {DEFINER / INVOKER}: 指明谁有权限来执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
- COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。

routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

CREATE PROCEDURE pro_emp(in deptno int)
BEGIN
SELECT *
FROM emp
WHERE dno=deptno;
END

deptno:输入参数

CREATE PROCEDURE count_emp(out cc int)
BEGIN
SELECT COUNT(empno) into cc
FROM emp
WHERE empsex='女';
END

cc为输出类型，接收女雇员的人数。
select count(empno) into cc中的into语句实现了将count(empno)统计信息赋值给输出参数cc。SQL变量名不能和列名一样。如果select … into 语句中包含一个对列的引用，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

3. 存储过程的操作

3.1 存储过程的调用

语法

CALL sp_name([parameter[,..]])

CALL调用存储过程，sp_name是存储过程名称，parameter是存储过程的参数

示例

CREATE PROCEDURE avg_emp(IN deptno INT, OUT avgage FLOAT)
BEGIN
SELECT AVG(empage) INTO avgage
FROM emp
WHERE dno = deptno;
END

CALL avg_emp(1, @aa)

SELECT @avg

3.2 存储过程的查看

3.2.1 SHOW PROCEDURE STATUS查看存储过程的状态

SHOW STATUS语句只能查看存储过程操作哪一个数据库，存储过程的名称，类型，谁定义的，创建和修改时间，字符编码等信息。 不能查询存储过程具体定义

语法

SHOW PROCEDURE STATUS [LIKE 'pattern']

like表示匹配存储过程的名称

示例

SHOW PROCEDURE STATUS like 'a%'\G

查询所有以字母‘a’开头的存储过程信息

3.2.2 SHOW CREATE PROCEDURE 查看存储过程的信息

查询存储过程具体定义

语法

SHOW CREATE PROCEDURE sp_name

返回一个可用来重新创建已命名存储过程的确切字符串

示例

SHOW CREATE PROCEDURE avg_emp

3.2.3 INFORMATION_SCHEMA.ROUTINES查看存储过程的信息

INFORMATION_SCHEMA是信息数据库，其中保存着关于Mysql服务器所维护的所有其他数据库的信息。该数据库中的ROUTINES表提供存储过程的信息。通过查询该表可以查询相关存储过程的信息

语法

select * from information_schema.routines where routine_name='sp_name';

routine_name字段存储所有存储子程序的名称；sp_name是需要查询的存储过程名称

示例

select * from information_schema.routines where routine_name='avg_emp';

修改存储过程的特性

ALTER {PROCEDURE / FUNCTION} sp_name [characteristic ...]

sp_name为待修改的存储过程名称；
characteristic来指定特性，可能取值如下：

{countains sql / no sql / reads sql data / modifies sql data / sql security { definer / invoker } / comment 'string'}

3.3 存储过程的删除

语法

DROP PROCEDURE sp_name;

示例

DROP PROCEDURE avg_emp;
select * from information_schema.routines where routine_name='avg_emp';

4. 自定义函数

4.1 自定义函数的创建

语法

CREATE FUNCTION sp_name([func_parameter[, ...]])
RETURNS type
[characteristic ...] routine_body

CREATE FUNCTION创建函数的关键字
sp_name参数是自定义函数的名称
func_parameter表示自定义函数的参数列表。func_parameter可以由多个参数组成，其中每个参数名称和参数类型组成，其形式如下。
- param_name type
- 其中：parma_name是自定义函数的参数名称，type是参数类型，可以是任意数据类型
returns type: 指的是返回值的类型
characteristic：是自定义函数的特性，同存储过程一样。
routine_body: 是SQL代码的内容，可以用begin…end来标志SQL代码的开始和结束

示例

CREATE FUNCTION name_emp(eno INT)
RETURNS VARCHAR(20)
BEGIN
 RETURN(
  SELECT empname FROM emp WHERE empno=eno
 );
END;

returns语句只能对function做指定，对函数而言是强制的。它用来指定函数的返回类型，而且函数体必须包含一个return value语句

4.2 自定义函数的调用

自定义函数的调用和内部函数的调用方法一致，没有本质区别

select name_emp(1);

4.3 变量

存储过程和自定义函数中，都可以定义和使用变量

变量的定义使用DECLARE关键字，定义后为变量赋值。作用域：begin…end程序段中

4.3.1 定义变量

语法

DECLARE var_name[,...] TYPE [DEFAULT VALUE]

default value 为变量默认值。可以是常数，也可以是表达式。如果没有默认值，初始值为NULL

示例

declare empdept char(10) default '财务部';

4.3.2 变量赋值

4.3.2.1 变量赋值(1)

语法

SET var_name = expr [, var_name = expr] ...

set是赋值关键字；expr：赋值表达式

示例

declare var1, var2 int;
declare var3 char(20);
set var1=10, var2=20, var3='hello';

4.3.2.2 变量赋值(2)

语法

select col_name [,...] into var_name [,...] from table_name where condition

j将select选定的列值直接存储在对应位置的变量中；col_name是查询的字段名称

示例

declare emp_name char(20);
select empname into emp_name
from emp
where empno = 10;

4.4 流程控制语句

4.4.1 if语句

语法

if search_condition then statement_list
[elseif search_condition then statement_list]...
[else statement_list]
end if

search_condition：表示条件判断语句，如果该参数值为TRUE，执行相应的SQL语句，如果为假，执行ELSE子句中的语句。
statement_list: 表示不同条件的执行语句，可以包含一条或多条语句。

4.4.2 CASE语句

也是条件判断，可以实现比if更复杂的条件判断

形式1

语法

case case_value
when when_value then statement_list
[when when_value then statement_list]...
[else statement_list]
end case

case_value参数表示条件判断的表达式，决定哪个when子句被执行。
when_value 参数表示表达式可能的取值，
如果某个when_value和case_value表达式的结果相同，则执行then关键字后面的语句

case deptno
when 1 then select '电脑部';
when 2 then select '财务部';
when 3 then select '营销部';
end case;

形式2

语法

case
when search_condition then statement_list
[when search_condition then statement_list]...
[else statement_list]
end case

search_condition参数表示条件判断语句
statement_list参数表示不同条件的执行语句。
when语句逐条执行

case
when deptno = 1 then select '电脑部';
when deptno = 2 then select '财务部';
when deptno = 3 then select '营销部';
end case;

4.4.3 LOOP语句

LOOP语句可以重复执行特定语句，本身不进行条件判断，没有停止循环的语句，必须使用LEAVE语句才能停止循环，跳出循环过程
语法

[begin_label:]LOOP
statement_list
END LOOP [end_label]

begin_label和end_label参数分别表示循环开始和结束的标志，两个标志必须相同，且都可以省略。
statement_list表示需要循环执行的语句

declare ss int default 0;
add_sum: LOOP
set ss=ss+1
END LOOP add_sum;

4.4.4 LEAVE语句

t跳出任何被标注的流程控制语句

语法

LEAVE label

label参数表示循环的标志。LEAVE和循环或BEGIN…END语句一起使用

示例

declare ss int default 0;
add_sum:loop
set ss=ss+1;
if ss>100 then leave add_sum;
end if;
end loop add_sum;

4.4.5 ITERATE语句

跳出本次循环，进入下一次循环

只可以出现在LOOP，repeat，和while语句中

语法

ITERATE label

label参数表示循环的标志。

示例

create procedure pp(a int)
begin
la: loop
set a = a+1;
if a < 10 then iterate la;
end if;
leave la;
end loop la;
set @x = a;
end

4.4.6 REPEAT语句

带条件判断的循环过程。

循环语句执行完对表达式进行判断：为真，结束循环；为假，继续重复循环

语法

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

begin_label和end_label为开始标记和结束标记，两者均可省略；
statement_list:循环的执行语句
search_condition:表示结束循环的条件，为真，跳出循环，为假，重复循环

示例

declare aa int default 0;
repeat
set aa = aa + 1;
until aa >= 20;
end repeat;

4.4.7 WHILE语句

带条件判断的循环过程。

首先对表达式进行判断：为真，执行循环内语句；为假，退出循环过程

语法

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

begin_label和end_label为开始标记和结束标记，两者均可省略；
statement_list:循环的执行语句
search_condition:表示条件判断语句，为真，执行循环内语句，为假，退出循环

示例

declare aa int default 0;
while aa <= 20 do
set aa = aa + 1;
end while;

4.5 光标的使用

光标又叫游标

MySQL中的光标只能在存储过程和自定义函数中使用

作用：存储过程或自定义函数中查询可能返回多条记录，光标可以逐条读取查询结果集中的记录

光标必须在处理程序之前声明，在变量和条件之后声明。

光标使用步骤

声明光标
打开光标
使用光标
关闭光标

4.5.1 声明光标

语法

DECLARE cursor_name CURSOR FOR select_statement

cursor_name:光标的名称
select_statement:查询语句，返回一个结果集，声明的光标基于该结果集进行操作。

可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名称。

示例

declare cursor_emp cursor for select empno, empname from emp

4.5.2 打开光标

语法

OPEN cursor_name

cursor_name:先前声明的光标

示例

open cursor_emp;

4.5.3 使用光标

语法

FETCH cursor_name INTO var_name [, var_name]...

cursor_name:先前声明并打开的光标
var_name: 表示将光标声明中的select语句中的查询信息存储在该参数中；必须在光标声明前定义好。

示例

fetch cursor_emp into e_no, e_name;

表示将select语句查询得到的empno, empname存储在变量e_no,e_name中

4.5.4 关闭光标

语法

CLOSE cursor_name

cursor_name:先前声明并打开的光标
如果未被明确的关闭，光标在它被声明的复合语句的末尾被关闭。

示例

close cursor_emp;

4.6 定义条件和处理程序

程序运行过程可能出现问题，用定义条件和处理程序事先定义该问题，保证存储过程和自定义函数在遇到警告或错误时能够继续进行

4.6.1 定义条件

语法

DECLARE condition_name CONDITION FOR condition_value

其中：
condition_value:
SQLSTATE[VALUE] sqlstate_value / mysql_error_code

condition_name：条件名称
condition_value: 为条件类型
sqlstate_value和mysql_error_code都可以表示MySQL的错误
sqlstate_value：长度为5的字符串类型的错误代码
mysql_error_code: 为数值类型错误代码

方法1：使用sqlstate_value

示例

declare command_not_find condition for sqlstate '43000';

方法2：使用mysql_error_code

示例

declare command_not_find condition for 1120;

4.6.2 定义处理程序

语法

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type表示 CONTINUE / EXIT / UNDO 语句中的handler_type为错误处理的方式
- continue：表示遇到错误不处理，继续执行。
- exit: 表示遇到错误马上退出。
- undo: 表示遇到错误后撤销之前的操作。
condition_value:可能有以下取值

SQLSTATE[VALUE] sqlstate_value
/ condition_name
/ SQLWARNING
/ NOT FOUND
/ SQLEXCEPTION
/ mysql_error_code

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

SQLSTATE[VALUE]sqlstate_value: 字符串错误值。
condition_name: 使用declare dondition 定义的错误条件名称。
SQLWARNING: 匹配所有01开头的SQLSTATE错误代码。
NOT FOUND: 匹配所有02开头的SQLSTATE错误代码。
SQLEXCEPTION: 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATEC错误代码。

示例1

DECLARE CONTINUE HANDLER FOR SQLSTATE'23S00'
SET @X=20;

该方法是定义捕获sqlstate_value值。如果遇到sqlstate_value值为23S00,执行CONTINUE操作，并且给变量x赋值为2

示例2

DECLARE CONTINUE HANDLER FOR 1146 SET @X=20;

该方法是定义捕获mysql_error_code值。如果mysql_error_code值为1146,执行CONTINUE操作，并且给变量x赋值为2

示例3

declare no_table condition for 1150;
DECLARE CONTINUE HANDLER FOR no_table
set @info=`no_table`;

先定义no_table条件，遇到1150错误就执行CONTINUE操作，并输出NO_TABLE信息

示例4

DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出‘ERROR’信息

示例5

DECLARE EXIT HANDLER FOR NOT FOUND SET @info='ERROR';

NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出‘ERROR’信息

示例6

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出‘ERROR’信息

萍踪缘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程与函数

存储过程与函数1. 存储过程的定义2. 存储过程的创建3. 存储过程的操作3.1 存储过程的调用3.2 存储过程的查看3.2.1 SHOW PROCEDURE STATUS查看存储过程的状态3.2.2 SHOW CREATE PROCEDURE 查看存储过程的信息3.2.3 INFORMATION_SCHEMA.ROUTINES查看存储过程的信息3.3 存储过程的删除4. 自定义函数4.1 自定义...
复制链接

扫一扫