7 存储过程和函数ppt课件.pptx

学习目标掌 握了解理解1掌握存储过程的创建、修改和执行、存储函数的创建和执行、常用内置函数的使用2理解存储过程的作用、常用内置函数的含义 3了解存储过程和存储函数、程序存储的类型MySQL程序设计基础7.17.1存储过程概述7.27.27.37.3创建和执行存储过程目录管理存储过程7.47.4存储函数7.57.5目录在MySQL中最常见的变量类型有局部变量和用户自定义变量两种。1.局部变量要定义局部变量必须使用DECLARE来声明，定义的同时可以使用default对局部变量初始化赋值。DECLARE语句格式如下：DECLARE var_name, type DEFAULT value value是给变量提供一个默认值，包含在一个DEFAULT子句中，它的值可以被指定为一个表达式，如果没有DEFAULT子句，初始值为NULL。例如：DECLARE num int DEFAULT 0;7.1 MySQL程序设计基础7.1.1 变量2.用户自定义变量用户自定义变量的声明以“”开头，形如：var_name。为了实现不同SQL语句中进行值的传递，可以把一些数值存储在自定义的用户变量中，不同的SQL语句都可以对它进行访问。用户变量在客户端和数据库的连接建立时被定义，当连接断开时，用户变量将会被释放。用户变量无需用DECLARE关键字进行定义，可以直接使用。例如：SET c1=1, c2=2, c3=4;在编写存储过程和存储函数时，可以使用流程控制语句对SQL语句进行组织，使其成为符合业务逻辑的代码块。MySQL中常见的流程控制语句主要有：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句、REPEAT语句等。7.1.2 流程控制语句1.IF语句IF语句可以通过判断一个逻辑条件是TRUE还是FALSE，转去执行相应的语句，它的语法格式如下：IF expr_condition THEN statement_list ELSEIF expr_condition THEN statement_list ELSE statement_list END IF 2.CASE语句CASE也是一个条件判断语句，多用于多分支判断的程序结构，它的常用语法格式如下：CASE case_expr WHEN when_value THEN statement_list WHEN when_value THEN statement_list ELSE statement_list END CASE3.LOOP语句LOOP是一个循环语句，用来进行一个语句块的循环操作。LOOP语句并不进行条件判断，会一直执行循环体的语句，如果要退出语句的执行，需要使用LEAVE等语句退出循环。LOOP语句的语法格式如下：loop_label: LOOP Statement_list END LOOt_listP loop_label4.LEAVE语句在循环语句LOOP使用过程中，当循环条件不满足时，可以使用LEAVE语句跳出循环体。LEAVE语句用于跳出有循环标志的流程控制语句，基本语法结构如下：LEAVE label5.ITERATE语句与LEAVE语句结束整个循环不同，ITERATE语句用于无条件转到语句段的开头处。ITERATE语句的格式如下：ITERATE lable6.REPEAT语句REPEAT语句用于循环执行一个语句块，采用的方式是先无条件执行语句块后再进行条件表达式判断，如果表达式为真，则循环结束，否则重复执行语句块。REPEAT语句的格式如下：repeat_lable: REPEAT statement_list UNTIL expr_condition END REPEAT repeat_lable7.WHILE语句WHILE语句也用于循环执行一个语句块，但是与REPEAT语句不同，WHILE语句执行时首先判断条件表达式是否为真，如果为真则继续执行循环体内语句，否则直接退出循环体。WHILE语句的格式如下：while_lable: WHILE expr_condition DO Statement_list END WHILE while_lable使用SQL语句进行查询过程中，查询结果将返回很多记录，如果记录量很大时，需要使用光标来对查询结果集中的记录进行逐条读取，它可以对多行数据进行轻松的处理。7.1.3 光标1.光标的声明在使用光标对结果集中的数据进行处理时，需要首先声明光标，光标的声明必须在声明变量、条件之后，声明处理程序之前。光标的声明格式如下：DECLARE cursor_name CURSOR FOR select_statement其中cursor_name表示光标的名字，select_statement代表SELECT语句的内容，返回一个用于创建光标的结果集。下面声明一个名为cur_teacher的光标。代码如下：DECLARE cur_xsxx CURSOR FOR SELECT xh,xmFROM tb_xsxxb;2.光标的使用MySQL中使用OPEN关键字来打开光标,打开光标之后就可以使用光标了。其语法的基本形式如下：OPEN cursor_name ;FETCH cursor_name INTO var_name,var_name ;下面我们使用一个名为cur_teacher的光标。将查询出来的数据存入xs_xh和xs_xm这两个变量中，代码如下：FETCH cur_xsxx INTO xs_xh,xs_xm;3.光标的关闭MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：CLOSE cursor_name ;其中，cursor_name参数表示光标的名称。示例：下面关闭一个名为cur_teacher的光标。代码如下：CLOSE cur_teacher;下面举一个例子，使用光标查询显示学生信息表tb_xsxxb中所有学生学号和姓名的字段信息，代码段如下：delimiter /CREATE PROCEDURE p_1()BEGINDECLARE no_more_record INT DEFAULT 0;DECLARE t_XH char(12);DECLARE t_XM varchar(30);DECLARE cur_record CURSOR FOR SELECT XH,XM from tb_xsxxb; /*首先对游标进行定义*/DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;/*条件处理,针对NOT FOUND的条件,当没有记录时no_more_record赋值为1*/OPEN cur_record; /*使用OPEN打开游标*/FETCH cur_record INTO t_XH,t_XM; /*读取一条记录到变量中*/WHILE no_more_record != 1 DO /*如果记录未读取结束*/SELECT t_XH,t_XM;FETCH cur_record INTO t_XH,t_XM; /*继续读取下一条记录到变量中*/END WHILE;CLOSE cur_record; /*用完后需要用CLOSE把资源释放掉*/END;/存储过程是数据库服务器上一组预先编译好的SQL语句的集合，作为一个单元存储在数据库中，可以被应用程序作为一个整体来进行调用。在调用过程中，存储过程可以接收参数，执行后返回参数值。7.2 存储过程概述数据库开发人员在进行数据库开发时，为了实现一定的功能，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句块，称之为“过程”（Procedure）。存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过过程名和给出参数值来调用它们。7.2.1 存储过程基本概念MySQL的存储程序可分以下四大类:(1)存储函数(stored function)。返回一个计算结果，该结果可以用在表达式里。(2)存储过程(stored procedure)。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。(3)触发器(trigger)。与数据表相关联，当那个数据表被INSERT、DELETE、UPDATE语句修改时，触发器将自动执行。(4)事件(event)。根据时间表在预订时刻自动执行。7.2.2 存储过程的类型(1)存储过程的使用，提高了程序设计的灵活性，增强了SQL语言的功能。(2)存储过程把一组功能代码作为单位组件。(3)使用存储过程有利于提高程序的执行速度。(4)使用存储过程能减少网络访问的负荷。(5) 作为一种安全机制，系统管理员可以充分利用存储过程对相应的数据的访问权限的进行限制。7.2.3 存储过程的作用创建存储过程语法格式如下：7.3 创建和执行存储过程7.3.1 创建和执行不带参数的存储过程创建一个存储过程 p_1，该存储过程输出学生信息表tb_xsxxb表中姓“王”的全部记录。对应的SQL语句如下：DELIMITER /CREATE PROCEDURE p_1()BEGIN SELECT * FROM tb_xsxxb WHERE xm like 王%;END;/使用SQL语句执行例7-2中创建的存储过程p_1。对应的SQL语句如下：执行结果如下所示：USE jwxt;CALL p_1();创建一个存储函数 f_1，输出1+1的和；创建一个存储函数f_2，输入两个参数，计算两个参数的和。对应的SQL语句如下：DELIMITER /CREATE FUNCTION f_1()BEGIN RETURN 1+1;END;/DELIMITER /CREATE DEFINER=rootlocalhost FUNCTION f_2(a int,b int) RETURNS int(11)BEGINRETURN a+b;END/使用SQL语句分别执行例7-4中创建的存储函数f_1和f_2。对应的SQL语句如下：执行结果如下所示：USE jwxt;select f_1();USE jwxt;select f_2(10,10);7.3.2 创建和执行带输入参数的存储过程（1）使用变量名传递参数值在执行存储过程的语句中，通过语句SET parameter_name=value设定给参数的传递值，然后按位置传递参数。其语法格式如下：CALL procedure_name (parameter_name=value ,n);创建一个名为p_2的存储过程。该存储过程能根据用户给定的教师职称值输出教师信息表tb_jsxxb中教师职称为用户给定值的全部记录。该存储过程的SQL语句如下：delimiter /CREATE PROCEDURE p_2(IN jszc varchar(10)BEGINselect * from tb_jsxxb where zc=jszc;END;/调用存储过程 p_2，给定职称值为“讲师”，运行结果如下：2.执行带输入参数的存储过程执行带输入参数的存储过程，有两种方法：一种是使用变量名传递参数值；另一种是按给定表达式值传递参数值。 DELIMITER /SET 输入职称=讲师;CALL p_2(输入职称);/DELIMITER ; （2）按给定表达式值传递参数在执行存储过程的语句中，当存储过程含有多个输入参数时，采用这种方式传递值，给定参数值的顺序必须与存储过程中定义的输入变量的顺序一致。其语法格式如下：CALL procedure_name(value1,value2,)用按给定表达式值传递参数的方式执行存储过程p_2，分别查找职称为“讲师”和“副教授”的记录。对应的SQL语句如下：CALL p_2(讲师);CALL p_2(副教授);7.3.3 创建和执行带输出参数的存储过程创建存储过程p_3，要求能根据用户给定的班级代码值，统计出学生信息表tb_xsxxb表中对应班级代码的班级人数，并将结果以输出变量的形式返回给调用者。DELIMITER /CREATE PROCEDURE p_3(IN bjdm char(2),OUT bjrs tinyint) BEGIN SELECT COUNT(*) INTO bjrs FROM tb_xsxxb WHERE bjdm=bjdm;END/DELIMITER;执行存储过程p_3，统计班级代码为“01”的班级人数。运行结果：CALL p_3(01,a01);SELECT a01;（1）我们可以用SELECT name FROM mysql.proc WHERE db=数据库名，或者SELECT routine_name FROM information_schema.routines WHERE routine_schema=数据库名， 或者SHOW PROCEDURE STATUS WHERE db=数据库名 用于显示数据库内存储过程的列表。（2）使用SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。7.4 管理存储过程7.4.1 查看存储过程查看 p_1 存储过程的信息。对应的SQL语句如下：SELECT * from mysql.proc WHERE db=jwxt;查看存储过程p_2的定义语句等信息。对应的SQL语句如下：SHOW CREATE PROCEDURE p_2;修改存储过程是由ALTER PROCEDURE语句来完成的，其语法格式如下：ALTER PROCEDURE | FUNCTION sp_name characteristic .characteristic: CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT string7.4.2 修改存储过程修改存储过程p_1的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。对应的SQL语句如下：ALTER PROCEDURE p_1 MODIFIES SQL DATA SQL SECURITY INVOKER ;SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME=p_1 ;存储过程的删除是通过DROP PROCEDURE语句来实现的。其语法格式为：DROP PROCEDURE | FUNCTION IF EXISTS sp_nameIF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它防止发生错误。7.4.3 删除存储过程存储过程与存储函数一样，都是由sql语句和过程式语句所组成的代码片段，并且可以被应用程序和其他sql语句调用。它们之间的区别在于：（1）存储函数不能有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。（2）可以直接对存储函数进行调用，而不需要使用call语句；而对存储过程的调用，需要使用call语句。（3）存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中。7.5 存储函数7.5.1 存储过程与存储函数联系与区别MySQL中，创建存储函数的基本形式如下：CREATE FUNCTION sp_name (func_parameter,.)RETURNS typecharacteristic . routine_bodyRETURN 子句用于声明存储函数返回值的数据类型。7.5.2 创建和执行存储函数创建存储函数 f_1，包含两个int参数，返回两个数之和。对应的SQL语句如下：DELIMITER /CREATE FUNCTION f_1(x INT, y INT) RETURNS INTBEGIN RETURN x + y;END/DELIMITER ;调用存储函数 f_1（方法一）： 调用存储函数 f_1（方法二）：运行结果如下： 运行结果如下：set x=10;set y=20;set result=f_1(x,y);select result;select f_1(10,20);为了能更好的为用户服务，MySQL提供了丰富的系统函数，这些函数无需定义就能直接使用，其中包括数学函数、聚合函数、字符串函数、日期和时间函数、加密函数和格式化函数等。7.5.3 MySQL的系统函数数学函数 ABS(x):返回x的绝对值。 BIN(x):返回x的二进制。 CEILING(x):返回大于x的最小整数值（向上取整）。 EXP(x)：返回值自然对数e的x次方。 FLOOR(x)：返回小于x的最大整数值（向下取整）。 LN(x)：返回x的自然对数。 LOG(x,y)：返回x的以y为底的对数。 MOD(x,y)：返回x/y的余数。 PI()：返回圆周率的值。 RAND()：返回到内的随机数。 ROUND(x,y)：返回参数x的四舍五入的有y位小数的值。 SIGN(x)：返回代表数字x的符号的值(负数，零，正数 对应-1，0，1）。 SQRT(x)：返回一个数的平方根。 TRUNCATE(x,y)：返回数字x截短为y位小数的结果。聚合函数 AVG(col)：返回指定列的平均值。 COUNT(col)：返回指定列中非NULL值的个数。 MIN(col)：返回指定列的最小值。 MAX(col)：返回指定列的最大值。 SUM(col)：返回指定列的所有值之和。字符串函数（1） ASCII(char)：返回字符的ASCII码值。 INSERT(str,x,y,instr)：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果。 LCASE(str)或LOWER(str)：返回将字符串str中所有字符改变为小写后的结果。 UCASE(str)或UPPER(str)：返回将字符串str中所有字符转变为大写后的结果。 LEFT(str,x)：返回字符串str中最左边的x个字符。 RIGHT(str,x)：返回字符串str中最右边的x个字符。 LENGTH(str)：返回字符串str的存储长度（注：一个汉字在UTF8编码为3，GBK为4）。 CHAR_LENGTH(str)：返回字符串str的字符个数（注：一个汉字为1）。 BIT_LENGTH(str)：返回字符str的字节长度（注：一个汉字在UTF8编码为3，GBK为4）。 LTRIM(str)：从字符串str中去除开头的空格。 TRIM(str)：去除字符串首部和尾部的所有空格。字符串函数（2） POSITION(substr IN str)：返回子串substr在字符串str中第一次出现的位置，无结果返回0。 REVERSE(str)：返回颠倒字符串str的结果。 STRCMP(s1,s2)：比较字符串s1和s2，完全相同返回0，反之返回-1。 SUBSTR(x)和SUBSTRING(x)：返回字符串指定位置指定长度的子串（同义函数）。 REPLACE(str,from_str,to_str)：替换str字符串中指定子串from_str为新子串to_str。 SPACE(X)：返回由X个空格构成的字符串。 CONCAT(s1,s2.,sn)：将s1,s2.,sn连接成字符串。 CONCAT_WS(seperator,str1,str2,)：将多个字符串以连接符seperator首尾相连。 FORMAT(X,D,locale)：按“#,#,#.0X”以四舍五入方式格式化字符串（数字）。 REREAT(str,count)：返回由count个str组成的结果。日期和时间函数 CURDATE()或CURRENT_DATE()：返回当前的日期。 CURTIME()或CURRENT_TIME()：返回当前的时间。 DAYOFWEEK(date)：返回date所代表的一星期中的第几天(17)。 DAYOFMONTH(date)：返回date是一个月的第几天(131)。 DAYOFYEAR(date)：返回date是一年的第几天(1366)。 HOUR(time)：返回time的小时值(023)。 MINUTE(time)：返回time的分钟值(059)。 MONTH(date)：返回date的月份值(112)。 MONTHNAME(date)：返回date的月份名。 NOW()：返回当前的日期和时间。 QUARTER(date)：返回date在一年中的季度(14)。 WEEK(date)：返回日期date为一年中第几周(053)。 YEAR(date)：返回日期date的年份(10009999)。加密函数 MD5(str)：返回str的MD5哈希值。 PASSWORD(str)：返回str的加密值（单向加密，不可逆模式）。 SHA(str)或SHA1(str)：返回str的SHA（或SHA1）哈希值。格式化函数 DATE_FORMAT(date,fmt)：依照字符串fmt格式化日期date值。 TIME_FORMAT(time,fmt)：依照字符串fmt格式化时间time值。 FORMAT(x,y) ：把x格式化为以逗号隔开的数字序列，y是结果的小数位数。 INET_ATON(ip) ：返回IP地址的数字表示。 INET_NTOA(num)：返回数字所代表的IP地址。格式化函数-日期格式格式：描述%a：缩写星期名%b：缩写月名%c：月，数值%D：带有英文前缀的月中的天%d：月的天，数值(00-31)%e：月的天，数值(0-31)%f：微秒%H：小时(00-23)%h：小时(01-12)%I：小时(01-12)%i：分钟，数值(00-59)%j：年的天(001-366)%k：小时(0-23)格式：描述%M：月名%m：月，数值(00-12)%p：AM或PM%r：时间，12-小时（hh:mm:ssAM或PM）%S：秒(00-59)%s：秒(00-59)%T：时间,24-小时(hh:mm:ss)%U：周(00-53)星期日是一周的第一天%u：周(00-53)星期一是一周的第一天%V：周(01-53)星期日是一周的第一天，与%X使用%v：周(01-53)星期一是一周的第一天，与%x使用格式：描述%W：星期名%w：周的天（0=星期日,6=星期六）%X：年，其中的星期日是周的第一天，4位，与%V使用%x：年，其中的星期一是周的第一天，4位，与%v使用%Y：年，4位%y：年，2位常用函数的使用（1）：/*数学函数*/select ABS(-10); /*输出10*/select BIN(3); /*输出11*/select pi(); /*输出圆周率值*/select round(4.5,0); /*输出5*/select rand(); /*输出(0,1)之间的随机数*/select truncate(1.2389,2); /*输出1.23*/常用函数的使用（2）：/*字符串函数（1）*/select ASCII(a); /*输出97*/select insert(abc,2,1,ee); /*输出aeec*/select lcase(ABC); /*输出ab*/select ucase(abc); /*输出ABC*/select left(abc,2); /*输出ab*/select right(abc,2); /*输出bc*/select ucase(abc); /*输出ABC*/select length(abc); /*输出3*/select length(汉字); /*输出6*/select char_length(汉字); /*输出2*/select ltrim( abc); /*输出abc*/常用函数的使用（3）：/*字符串函数（2）*/select position(b in abc); /*输出2*/select reverse(abc); /*输出cba*/select strcmp(abc,ABC); /*输出0*/select substr(abc,3,10); /*输出c*/select replace(abc,a,eee); /*输出eeebc*/select replace( a b c , ,); /*输出abc*/select space(10); /*输出10个空格*/select concat(a,b,c); /*输出abc*/select concat_ws(x,a,b,c); /*输出axbxc*/select repeat(abc,3); /*输出abcabcabc*/select format(123456,2); /*输出123,456.00*/常用函数的使用（4）：/*日期和时间函数*/select now(); /*输出当前日期和时间*/select curdate(); /*输出当前日期*/select curtime(); /*输出当前时间*/select dayofyear(now(); /*输出当前日历是当前年的第几天*/select year(now(); /*输出当前年份*/常用函数的使用（5）：/*加密函数*/select md5(abc); /*输出900150983cd24fb0d6963f7d28e17f72*/select password(abc); /*输出*0D3CED9BEC10A777AEC23CCC353A8C08A633045E*/select sha(abc); /*输出a9993e364706816aba3e25717850c26c9cd0d89d*/*格式化函数*/select DATE_FORMAT(now(),%Y %m %d); /*输出2020 12 13*/常用函数的使用（6）：/*聚合函数*/输出学生成绩tb_xscjb中的课程代码为“GG110014”的正考成绩zkcj平均值、数量、最大值、最小值、和：select avg(zkcj),count(zkcj),max(zkcj),min(zkcj),sum(zkcj) from tb_xscjb where kcdm=GG110014;注意：如果对整体正考成绩使用聚合函数时，MySQL会忽略成绩值中的等第成绩。