Editsprings@163.com (周一至周日,9:00-23:00) | (周一至周日,9:00-23:00)

数据库系统学习笔记函数和存储过程Hunk的菜鸟之路,EditSprings,艾德思

网络 | 2019/01/09 11:30:50  | 150 次浏览



函数

自定义函数

基本语法

控制语句

存储过程

自定义函数和存储过程的区别

函数 函数的意义就在于完成特定的功能,写一份函数,可以在多个语句中多次使用,简化编程工作.

数据库系统中的函数大致也可以分为内置函数和自定义函数(UDF),内置函数比如count,sum等等,自定义函数比较灵活.

本文demo所用的数据库版本:,样本数据来自<数据库系统概念>这本书的官网:.

参考:

基本语法 --创建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])

RETURNS {STRING|INTEGER|REAL}

BEGIN

   runtime_body --如果函数体有多条语句,放在BEGIN..END中,单条语句不需要BEGIN...END

END

 

--删除UDF:

  DROP FUNCTION function_name

 

--调用UDF:

  SELECT function_name(parameter_value,...) note:UDF可以有多个参数或者没有参数,但是必须有且仅有一个返回值.

Demo

创建函数:给定系的名称,返回该系的教师人数.

1 DELIMITER // --修改默认结束符分号

2 DROP FUNCTION IF EXISTS dept_count; --如果函数存在先进行删除

3 CREATE FUNCTION dept_count(dept_name VARCHAR(20))

4 RETURNS INTEGER --声明返回类型

5 BEGIN

6 DECLARE tmp_count,d_count INTEGER DEFAULT 0; --这些变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义

7 SELECT count(*) INTO tmp_count from instructor where instructor.dept_name = dept_name;

8 SET d_count = tmp_count; --这里tmp_count是多余的,只是演示一下SET的用法

9 RETURN d_count;

10 END//

11 DELIMITER ; --恢复默认结束符分号

使用dept_count函数

mysql> select dept_count('Biology');

+-----------------------+

| dept_count('Biology') |

+-----------------------+

| 2 |

+-----------------------+

1 row in set (0.00 sec)

mysql> select distinct dept_name, dept_count(dept_name) from instructor;

+------------+-----------------------+

| dept_name | dept_count(dept_name) |

+------------+-----------------------+

| Biology | 2 |

| Comp. Sci. | 4 |

| Elec. Eng. | 1 |

| Finance | 3 |

| History | 2 |

| Music | 1 |

| Physics | 2 |

+------------+-----------------------+

mysql> select distinct dept_name, dept_count(dept_name) from instructor where dept_count(dept_name) > 2;

+------------+-----------------------+

| dept_name | dept_count(dept_name) |

+------------+-----------------------+

| Comp. Sci. | 4 |

| Finance | 3 |

+------------+-----------------------+

控制语句 数据库系统也支持和通用编程语言类似的各种控制语句,MySQL中可以使用IF语句/CASE语句/LOOP语句/LEAVE语句/ITERATE语句/REPEAT语句和WHILE语句来进行流程控制,在自定义函数中也可以使用这些语句.

IF语句

IF search_condition THEN statement_list --statement_list就是不同条件下的执行语句

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF 1 DELIMITER //

2 DROP FUNCTION IF EXISTS dept_count;

3 CREATE FUNCTION dept_count(dept_name VARCHAR(20))

4 RETURNS INTEGER

5 BEGIN

6 DECLARE tmp_count, d_count INTEGER DEFAULT 0;

7 SELECT count(*) INTO tmp_count from instructor where instructor.dept_name = dept_nam e;

8 IF tmp_count = 0 THEN SET d_count = 100;

9 ELSE SET d_count = tmp_count;

10 END IF;

11 RETURN d_count;

12 END//

13 DELIMITER ;

mysql> select dept_count('Math'); --样本数据中没有Math这个系

+--------------------+

| dept_count('Math') |

+--------------------+

| 100 |

+--------------------+

CASE语句

CASE语句和IF语句也用来处理一些条件判断,和IF语句类似.

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE LOOP语句

LOOP语句可以实现循环条件执行.LOOP语句如果不用LEAVE等语句来控制退出条件,就会死循环执行下去,类似于通用编程语言中不带控制条件的for循环语句.

[begin_label:] LOOP

statement_list

END LOOP [end_label] 1 DELIMITER //

2 DROP FUNCTION IF EXISTS loop_100;

3 CREATE FUNCTION loop_100()

4 RETURNS INTEGER

5 BEGIN

6 DECLARE result INTEGER DEFAULT 0;

7 loop_100: LOOP

8 SET result=result + 1;

9 IF result=100 THEN LEAVE loop_100; --满足条件,用LEAVE跳出循环

10 END IF;

11 END LOOP loop_100;

12 RETURN result;

13 END//

14 DELIMITER ; mysql> select loop_100();

+------------+

| loop_100() |

+------------+

| 100 |

+------------+

1 row in set (0.00 sec) LEAVE语句

LEAVE语句主要用于跳出循环控制,在LOOP语句的demo中已经演示.

LEAVE label ITERATE语句

ITERATE语句也是用来跳出循环的语句.但是,ITERATE语句是跳出本次循环,然后直接进入下一次循环,类似于通用编程语言中的continue语句.

TERATE语句只可以出现在LOOP/REPEAT/WHILE语句内.

ITERATE label REPEAT语句

REPEAT语句是有条件控制的循环语句.当满足特定条件时,就会跳出循环语句,类似于设置了退出条件的for循环语句.REPEAT语句的基本语法形式如下:

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label] WHILE语句

WHILE语句也是有条件控制的循环语句.但WHILE语句和REPEAT语句是不一样的.

WHILE语句是当满足条件时,执行循环内的语句.

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label] 1 DELIMITER //

2 DROP FUNCTION IF EXISTS while_100;

3 CREATE FUNCTION while_100()

4 RETURNS INTEGER

5 BEGIN

6 DECLARE result INTEGER DEFAULT 0;

7 WHILE result < 100 DO

8 SET result=result + 1;

9 END WHILE;

10 RETURN result;

11 END//

12 DELIMITER ; mysql> select while_100();

+-------------+

| while_100() |

+-------------+

| 100 |

+-------------+

1 row in set (0.00 sec) 存储过程         SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它,由于可以像通用编程语言那样使用各种控制语句,因此使用存储过程可以实现比较复杂的业务逻辑.

存储过程的优点:

执行效率高:为存储过程是预编译的,在创建好存储过程后,第一次执行一个存储过程时,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划,所以相对于执行多条SQL语句,存储过程执行效率也更高. 标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新撰写该存储过程的SQL语句,增强了代码复用性.而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响. 减少网络流量:针对同一个数据库对象的操作(如查询/修改),如果这一操作所涉及的SQL语句被写成存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载. 安全可控性:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全. 自定义函数和存储过程的区别 前面讨论的函数和存储过程非常类似,但它们之间还是有以下区别:

存储过程实现的过程复杂一些,而函数的针对性更强,或者说函数一般用于完成某个特定的运算,而存储过程往往包含了更为复杂的业务逻辑,作为较为独立的业务功能. 存储过程可以有多个返回值,而自定义函数只有一个返回值. 存储过程一般独立的执行,自定义函数往往包含在SQL语句中使用.

 

更多科研论文服务,动动手指,请戳 论文润色投稿期刊推荐论文翻译润色论文指导及修改论文预审

语言不过关被拒?美国EditSprings--专业英语论文润色翻译修改服务专家帮您!

上一篇:【严正申明】关于我公司网站被恶意抄袭严正声明

下一篇:首都学产品营销的学院推荐,EditSprings,艾德思

特别声明:本文转载仅仅是出于传播信息的需要,并不意味着代表本网站观点或证实其内容的真实性;如其他媒体、网站或个人从本网站转载使用,须保留本网站注明的“来源”,并自负版权等法律责任;作者如果不希望被转载或者联系转载稿费等事宜,请与我们接洽。

凡注明来源为“EditSprings”的论文,如需转载,请注明来源EditSprings并附上论文链接。

最热论文