电工与电子技术电子商务电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第11章ppt

1、电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第11章第11章 MySQL存储过程与函数数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）CONTENTS存储过程与函数简介1存储过程与函数操作2系统函数3小结4存储过程与函数简介111.1.1 概念存储过程（stored procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。优点：(2)存储过程允许标准组件是编程。(1)存储过程增强了SQL语言的功能和灵活性。(3)存储过程能实现较快的执行速度。(4

2、)存储过程能过减少网络流量。(5)存储过程可被作为一种安全机制来充分利用。11.1.2 存储过程和函数区别对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。存储过程实现的功能要复杂一点，而函数的实现功能针对性比较强。存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数存储过程一般是作为一个独立的部分来执行（execute 语句执行），而函数可以作为查询语句的一个部分来调用（select调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于from关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。存储过程与函数操作211.2.1 创建和使用存

3、储过程或函数1、存储过程： 创建存储过程的语法格式：create procedure sp_name (proc_parameter，.)characteristic . routine_body 其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用beginend来标志SQL代码的开始和结束。11.2.1 创建和使用存储过程或函数proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。 in | o

4、ut | inout param_name type 其中，in表示输入参数；out表示输出参数； inout表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。11.2.1 创建和使用存储过程或函数characteristic参数有多个取值。其取值说明如下：language SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。not deterministic：指明存储过程的执行结果是否是确定的。deterministic表示结果是确定的。每次

5、执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。11.2.1 创建和使用存储过程或函数 contains SQL | no SQL | reads SQL data | modifies SQL data ：指明子程序使用SQL语句的限制。contains SQL表示子程序包含SQL语句，但不包含读或写数据的语句；no SQL表示子程序中不包含SQL语句；reads SQL data表示子程序中包含读数据的语句；modifies SQL data表示子程序中包含写数据的语句。默认情况下，

6、系统会指定为contains SQL。SQL security definer | invoker ：指明谁有权限来执行。definer表示只有定义者自己才能够执行；invoker表示调用者可以执行。默认情况下，系统指定的权限是definer。11.2.1 创建和使用存储过程或函数comment string：注释信息。技巧：创建存储过程时，系统默认指定contains SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为no SQL。而且，存储过程中最好在comment部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。调用存储过程的语

7、法格式：call sp_name(parameter，) 说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程所用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。11.2.1 创建和使用存储过程或函数创建存储函数语法格式：create function sp_name (func_parameter，.) returns type characteristic . routine_body 其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；return

8、s type指定返回值的类型；characteristic参数指定存储函数的特性；routine_body参数是SQL代码的内容，可以用beginend来标志SQL代码的开始和结束。11.2.1 创建和使用存储过程或函数func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。调用存储函数语法格式： select sp_name(func_parameter，)11.2.1 创建和使用存储过程或

9、函数3、delimiter命令在存储过程中，可能要输入较多的语句，切语句中含分号。如果还以分号作为结束标志，那么执行完第一个分号语句后，就会认为程序结束。这显然不符合我们的要求。那么，我们可以用MySQL delimiter来改变默认的结束标志。delimiter $说明，$是用户定义的结束符，通常使用一些特殊的符号。当使用delimiter命令时，应该避免使用反斜杠字符，因为那是MySQL转移字符。11.2.1 创建和使用存储过程或函数【例11-1】把结束符改为#，执行SELECT 1+1#，如下10.2.1 创建视图【例11-2】下面是一个存储过程的简单例子，根据学号查询学生的姓名。del

10、imiter $CREATE PROCEDURE getnamebysno (IN xh CHAR (10),OUT NAME CHAR (20)BEGIN SELECT sname INTO NAME FROM student WHERE sno = xh ;END$ delimiter ; 10.2.1 创建视图可以调用getnamebysno存储过程，首先我们定义一个用户变量name，用call调用getnamebysno存储过程，结果放到name中，最后输出name的值。10.2.1 创建视图【例11-3】 下面创建一个名为name_from_student的存储函数。说明：rutur

11、n子句中包含select语句时，select语句的返回结果只能是一行且只有一列值。10.2.1 创建视图可以像调用系统函数一样，直接调用自定义函数，如下：11.2.2 变量1)declare 语句申明局部变量 declare var_name1 ,var_name2 . . . type default value 其中var_name1, var_name2参数是声明的变量的名称，这里可以定义多个变量。type参数用来指明变量的类型；defalut value字句将变量默认值设置为value，没有使用default字句，默认是null可以用下列命令申明两个字符型变量：declare str1

12、,str2 varchar(6);11.2.2 变量2)用set语句给变量赋值set var_name = exper,var_name = exper其中var_name参数是变量的名称；expr参数是赋值的表达式。可为多个变量赋值。用逗号隔开。可以用下列命令在存储过程中给局部变量赋值：set str1=abc,str2=123;set可以直接申明用户变量，不需要声明类型，declare必须指定类型 ；set 位置可以任意, declare 必须在复合语句的开头，在任何其他语句之前；declare 定义的变量的作用范围是begin end块内，只能在块中使用。set 定义的变量用户变量。在变

13、量定义时，变量名称前使用符号修饰，如set var=12。11.2.2 变量3) 使用select语句给变量赋值select col_name,. . . into var_name, . . . table_expr其中col_name是列名，var_name是要赋值的变量名称。table_var是select语句中的from字句及后面【例11-4】定义一个存储过程，作用是输出连个字符串拼接后的值10.2.2 删除视图如果我们直接用调用它，会输出null,因为我们没有定义str1和str2需定义str1和str2后再调用，如下所示：11.2.3 定义条件和处理条件的定义和处理主要用于定义在处

14、理过程中遇到问题时，相应的处理步骤。1、定义条件declare condition_name condition for condition_valuecondition_valueSQLstatevalue SQLstate_value| MySQL_error_codecondition_name参数表示的是所有定义的条件，condition_value是用来实现设置条件的类型，SQLstate_value和MySQL_error_code用来设置条件的错误。11.2.3 定义条件和处理【例11-5】 下面定义error 1111 (13d12)这个错误，名称为can_not_find。可

15、以用两种不同的方法来定义，代码如下：方法一：使用SQLstate_value DECLARE can_not_find CONDITION FOR SQLSTATE 13d12;方法二：使用MySQL_error_code DECLARE can_not_find CONDITION FOR 1111;11.2.3 定义条件和处理2)定义处理程序MySQL中可以使用declare关键字来定义处理程序。其基本语法如下：declare handler_type handler for condition_value,. sp_statement handler_type: continue | e

16、xit | undo condition_value: SQLstate value SQLstate_value |condition_name | SQLwarning | not found | SQLexception | MySQL_error_code11.2.3 定义条件和处理下面是定义处理程序的几种方式。代码如下：方法一：捕获SQLstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE 42s02SET info = can not find;方法二：捕获MySQL_error_code DECLARE CONTINUE HANDLE

17、R FOR 1146SET info = can not find;方法三：先定义条件，然后调用 DECLARE can_not_find CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR can_not_findSET info = can not find;11.2.3 定义条件和处理方法四：使用SQLwarning DECLARE EXIT HANDLER FOR SQLWARNINGSET info = error; 方法五：使用not found DECLARE EXIT HANDLER FOR NOT foundSET info = c

18、an not find;方法六：使用SQLexception DECLARE EXIT HANDLER FOR SQLEXCEPTIONSET info = error;11.2.4 游标的使用游标就是一个cursor，就是一个标识，用来标识数据取到什么地方了。可以把它理解成数组中的下标。游标(cursor)具有以下特性： （1）只读的，不能更新的；（2）不滚动的；（3）不敏感的，不敏感意为服务器可以或不可以复制它的结果表 。11.2.4 游标的使用游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。1）声明游标declare cursornam

19、e cursor for select _ statementselect _statement是一个select语句，返回的是一行或多行的数据。这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。 特别提醒，这里的select子句不能有into子句。11.2.4 游标的使用2）打开游标 声明游标后，要使用游标从中提取数据，就必须先打开游标。open cursor_ name 在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。3）读取数据 游标打开后，就可以使用fetch into语句从中读取数据。fet

20、ch cursor_name into var_ name ， var_name 11.2.4 游标的使用4）关闭游标 游标使用完以后，要及时关闭。关闭游标使用close语句【例11-6】利用游标读取student表中总人数，此功能可以直接使用count函数直接完成，此实例主要为演示游标的使用方法。close cursorname10.2.4 修改视图定义10.2.4 修改视图定义注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。调用如下：11.2.5 流程的控制3）loop语句 loop语句可以使用某些特定的语句重复执行，实现简单的循环。begin_label: loopsta

21、tement_listend loop end_label【例11-9】 loop语句的应用add_num: LOOPSET count=count+1;END LOOP add_num11.2.5 流程的控制4）leave语句level label【例11-10】leave语句的应用add_num: LOOPSET count=count+1;IF count=10 THEN level add_num;END LOOP add_num11.2.5 流程的控制5）itebate语句itebate label【例11-11】itebate语句的应用add_num: LOOPSET count=

22、count+1;IF count=10 THEN LEVEL add_num;ELSEIF MOD(count,2)=0 THEN ITERATE add_num;END LOOP add_num11.2.5 流程的控制6）repeat语句的应用begin_label: repeatstatement_list until search_confitionend repeat end_label【例11-12】repeat语句的使用SET count=count+1;UNTIL count=10;END REPEAT11.2.5 流程的控制7）while语句的应用begin_label: wh

23、ile search_condition dostatement_listend while end_label【例11-13】while语句的应用WHILE count10 DOSET count=count+1;END WHILE11.2.6 查看存储过程或函数1、查看存储过程或函数的状态【例11-14】查看studentcount 存储过程的状态（表单查看）show procedure | function status like pattern;11.2.6 查看存储过程或函数2、查看存储过程或函数的具体信息【例11-15】查看numofstudent 自定义函数的具体信息，包含函数的名称、定义、字符集等信息。（表单查看）show create procedure | function sp_name; 11.2.6 查看存储过程或函数3、查