MySQL开发与实践第9章存储过程与存储函数.pdf

第9章存储过程与存储函数本章要求:了解流程控制语句的使用 了解MyS QL存储过程和函数中光标的使用和一般步骤 掌握MyS QL中存储过程和存储函数的创建 掌握MyS QL存储过程应用函数的参数使用方法 掌握存储过程和函数的调用、查看、修改和删除 掌握各种运算符的使用方法第9章存储过程与存储函数主要内容1.创建存储过程和存储函数2.存储过程和存储函数的调用3.查看存储过程和函数4.修改存储过程和函数5.删除存储过程和函数6.综合实例一一使用存储过程实现用户注册9.1创建存储过程和存储函数二二.9.1.1创建存储过程9.1.2创建存储函数9.1.3变量的应用9.1.4光标的运用9.1.1创建存储过程 在MyS QL中，创建存储过程的基本形式如下：CREATE PROCEDURE sp_name(proc_parameter,.)characteristic.routine_body其中sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表；characteristic参数指定存储过程的特性；routine_body参数是S QL代码的内容，可以用BEGIN.END.来标识S QL代码的开始和结束。说明：.proc_parameter中的参数由3部分组成，它们分别是输入输出类型、参数名称和参数类型。其形9 式为IN|OUT|INOUT param name type。其中IN表示输入参数；OUT表示输出参数；INOUT表示.既可以输入也可以输出；paramjame参数是存储过程参数名称；type参数指定存储过程的参数类型,该类型可以为MyS QL数据库的注意数据类型。一个存储过程包括名字，参数列表，还可以包括很多SQL语句集。下面创建一个存储过程，其代 码如下：delimiter/create procedure proc_name(in parameter integer)begindeclare variable varchar(20);if parameter：1 thenset variable=MySQL;elseset variable=PHP;end if;insert into tb(name)values(variable);end;MyS QL中存储过程的建立以关键字create procedure开始，后面仅跟存储过程的名称和参数。MyS QL的存储过程名称不区分大小写，例如PR 0C E1()和procel()代表同一存储过程名。存储过程名或 存储函数名不能与MyS QL数据库中的内建函数重名。二二二二MyS QL存储过程的语句块以begin开始，以end结束。语句体中可以包含变量的声明、控制语句、S QL查询语句等。由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志”更改为其他字符，并且应降低该字符在存储过程中出现的机率，更改结束标志可以用关键字“delimiter”定义，例如：mysqldelimiter/存储过程创建之后，可用如下语句进行删除，参数projname指存储过程名。drop procedure proc_name下面创建一个名称为count of student的存储过程。首先，创建一个名称为students的MyS QL数 据库，然后创建一个名为studentinfo的数据表。数据表结构如表9T所示：表9-1 studentinfo数据表结构字段名类型（长度）默认额外说明sidINT(11)auto_increment主键自增型sidnameVAR C H AR(50)学生姓名ageVAR C H AR(ll)学生年龄sexVAR C H AR(2)M学生性别telBIGIN T(ll)联系电话【例9-1】创建一个名称为count_of_student的存储过程，统计studentinfo数据表中的记录数。.代码如下：delimiter/.create procedure count_ofLstudent(OUT count_num INT)reads sql databeginselect count(*)into count_num from studentinfo;end、人*_在上述代码中，定义一个输出变量count_num。存储过程应用S ELEC T语句从studentinfo表中获取.记录总数。最后将结果传递给变量countnum。存储过程的执行结果如图9T所示。nysql nysql-delimiter/create procedure count_of_student reads sql data beginselect count into count_num from student info；end/Query OK,0 rows affected 图9-1创建存储过程countofstudent.代码执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个.存储过程，数据库中会执行存储过程中的S QL语句。.说明：MyS QL中默认的语句结束符为分号;存储过程中的S QL语句需要分号来结束。为了避免冲突，首 先用“DELIMITER”将MyS QL的结束符设置为。最后再用DELIMITER;来将结束符恢复成分号 o这与创建触发器时是一样的。9.1.2创建存储函数创建存储函数与创建存储过程大体相同。其创建存储函数的基本形式如下:CREATE FUNCTION sp_name(func_parameter,.)RETURNS typecharacteristic.routine_body创建存储函数的参窥说明如表9-2所示。表9-2 创建存储函数的参数说明参数说明sp_nam e存储函数的名称fun_pa rameter存储函数的参数列表R ETUR NS type指定返回值的类型charac teristic指定存储过程的特性routin e bodyS QL代码的内容func_parameter可以由多个参数组成，其中每个参数均由参数名称和参数类型组成，其结构如下：param_name typeparam_name参数是存储函数的函数名称；type参数用于指定存储函数的参数类型。该类型可以 是MyS QL如据库所支持的类型。【例9-2】同样，应用studentinfo表。创建名为name_of_student的存储函数。其代码如下：delimiter/create function name_ofLstudent(std_id INT)returns varchar(50)beginreturn(select name from studentinfo where sid=std_id);end/上述代码中，存储函数的名称为name_of_student；该函数的参数为std_id；返回值是VAR C HAR 类型。该函数实现从studentinfo表查询与std_id相同sid值的记录。并将学生名称字段name中的值 返回。存储函数的执行结果如图9-2所示。nysql delimiter/mysql create function nane_ofstudent-returns uarchar-begin-ieturn-end /Query OK,0 vows affected 图9-2 创建name of student()存储函数J二9.1.3变量的应用 MyS QL存储过程中的参数主要有局部参数和会话参数两种，这两种参数又可以被称为局部变量和.会话变量。局部变量只在定义该局部变量的beginend范围内有效，会话变量在整个存储过程范围 内均有效。.1.局部变量局部变量以关键字declare声明，后跟变量名和变量类型，例如：declare a int.当然在声名局部变量时也可以用关键字default为变量指定默认值，例如：.declare a int default 10下述代码为读者展示如何在MyS QL存储过程中定义局部变量以及其使用方法。在该例中，分别在.内层和外层beginend块中都定义同名的变量x,按照语句从上到下执行的顺序，如果变量x在整个 程序中都有效，则最终结果应该都为inner,但真正的输出结果却不同，这说明在内部beginend块 中定义的变量只在该块内有效。【例9-3】该例子说明局部变量只在某个beginend块内有效。代码如下:delimiter/create procedure p 1()begindeclare x char(10)default outerbegindeclare x char(10)default innerselect x;end;select x;end;/上述代码的运行结果如图9-3所示。iysql delimiter/)ysql create procedure pl-begin-declare x char default,outer,；-begin-declare x char default 9 inner 9；-select x；-end；-select x；end；/!uery OK,0 rows affected 一图9-3定义局部变量的运行结果 应用MyS QL调用该存储过程的运行结果如图9-4所示。iysql call-+Xinner i row in set xouter row in set!uery OK,0 rows affected 图9-4调用存储过程pl()的运行结果2.全局变量MyS QL中的会话变量不必声明即可使用，会话变量在整个过程中有效，会话变量名以字符“作为起始字符。下述代码为会话变量的使用方法。【例9-4】在该例中，分别在内部和外部beginend块中都定义了同名的会话变量觥，并且最终输出结果相同，从而说明会话变量的作用范围为整个程序。设置全局变量的代码如下：delimiter/create procedure p2()beginset t=l;beginset t=2;select t;end;select t;end;/上述代码的运行结果如图9-5所示。mysql delimiter/nysql create procedure p2-begin-set(?t=1；-begin-set(?t=2；-select Pt；-end；-select Pt；-end；-/Query OK，0 rows affected call p2O/Z-:et-!2!-1 row in set.-:et s-!2!-1 row in set 0.01 sec)Query OK.0 rows affected 图9-6调用存储过程p2()运行结果3.为变量赋值MyS QL中可以使用DEC LAR E关键字来定义变量。定义变量的基本语法如下：DECLARE var_name,.type DEFAULT valueDEC LAR E是用来声明变量的；vajname参数是设置变量的名称。如果用户需要，也可以同时定义多个 变量；type参数用来指定变量的类型；DEFAULT value的作用是指定变量的默认值，不对该参数进行 设置时，其默认值为NULL。MyS QL中可以使用S ET关键字为变量赋值。S ET语句的基本语法如下：SET var_name=expr,var_name=expr.S ET关键字是用条为变量赋值；vajname参数是变量的名称；expr参数是赋值表达式。一个S ET语 句可以同时为多个变量赋值，各个变量的赋值语句之间用“，”隔开。例如：为变量mr_soft赋值，代码如下：SET mr_soft=10:另外MyS QL中还可以应用另一种方式为变量赋值。其语法结构如下：SELECT col_name,.INTO var_name,.FROM table_name where condition其中col_name参数标识查询的字段名称；vajname参数是变量的名称；table_name参数为指定数据 表的名称；condition参数为指定查询条件。例如：从studentinfo表中查询name为“LeonS K”的记 录。将该记录下的tel字段内容赋值给变量customer_tel。其关键代码如下：SELECT tel INTO customer_tel FROM studentinfo WHERE name=LeonSK说明：上述赋值语句必须存在于创建的存储过程中。且需将赋值语句放置在BEGINEND之间。若脱离 此范围，该变量将不能使用或被赋值。9.1.4光标的运用通过MyS QL查询数据库，其结果可能为多条记录。在存储过程和函数中使用光标可以实现逐条读 取结果集中的记录。光标使用包括声明光标(DEC LAR E C UR S OR)、打开光标(OPEN C UR S OR)、使用光 标(FETC H C UR S OR)和关闭光标(C LOS E C UR S IR)0值得一提的是，光标必须声明在处理程序之前，且 声明在变量和条件之后。1.声明光标在MyS QL中，声明光标仍使用DEC LAR E关键字，其语法如下：DECLARE cursor_name CURSOR FOR select_statementcursor name是光标的名称，光标名称使用与表名同样的规则；select statement是一个S ELEC T 语句，返回一行或多行数据。其中这个语句也可以在存储过程中定义多个儿标，但是必须保证每个 光标名称的唯一性。即每一个光标必须有自己唯一的名称。通过上述定义来声明光标info_of_student,其代码如下：DECLARE info_oLstudent CURSOR FOR SELECTsid,name,age,sex,ageFROM studentinfoWHERE sid=l;说明：这里S ELEC T子句中不能包含INTO子句。并且光标只能在存储过程或存储函数中使用。上述代码 并不能单独执行。2.打开光标在声明光标之后，要从光标中提取数据，必须首先打开光标。在MyS QL中，使用OPEN关键字来打 开光标。其基本的语法如下：OPEN cursor_name其中cursojname参数表示光标的名称。在程序中，一个光标可以打开多次。由于可能在用户打 开光标后，其他司户或程序正在更新数据表。所以可能会导致用户在每次打开光标后，显示的结果 都不同。打开上面已经声明的光标info_of_student,其代码如下：OPEN info_oLstudent4HFL 二二3光标在顺利打开后，可以使用FETC HINTO语句来读取数据。其语法如下：FETCH cursor_name INTO var_name,var_name.其中cursojname代表已经打开光标的名称；vajname参数表示将光标中的S ELEC T语句查询出 来的信息存入该委数中。vajname是存放数据的变量，必须在声明光标前定义好。FETC H-INTO 语句与S ELEC TINTO语句具有相同的意义。将已打开的光标info of student中S ELEC T语句查询出来的信息存入tmp nanie和tmp tel中。其 中tmp_name和必须在使用前定义。其代码如下：FETCH info_ofLstudent INTO tmp_name,tmp_tel;4.关闭小标光标使用完毕后，要及时关闭，在MyS QL中采用C LOS E关键字关闭光标，其语法格式如下：CLOSE cursor_namecursojname参数表示光标名称。下面关闭已打开的光标info_of_student。代码如下：CLOSE info_of_student说明：对于已关闭的光标，在其关闭之后则不能使用FETC H来使用光标。光标在使用完毕后一定要关 闭。存储过程和存储函数的调用9.2.1 调用存储过程9.2.2 调用存储函数J 9.2.1调用存储过程存储过程的调用在前面的示例中多次被用到。MyS QL中使用C ALL语句来调用存储过程。调用存储 过程后，数据库系统将执行存储过程中的语句。然后将结果返回给输出值。C ALL语句的基本语法形 式如下：CALL sp_name(parameter其中sp_name是存储过程的名称；parameter是存储过程的参数。9.2.2调用存储函数在MyS QL中，存储函数的使用方法与MyS QL内部函数的使用方法基本相同。用户自定义的存储函数.与MyS QL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MyS QL自带。其语法 结构如下：.SELECT function_name(parameter,.);查看存储过程和函数9.3.1 S HOW S TATUS语句9.3.2 S HOW C R EATE语句9.3.1 S HOW S TATUS语句 在MyS QL中可以通过S HOW S TATUS语句查看存储过程和函数的状态。其基本语法结构如下：.SHOW PROCEDURE|FUNCTION STATUS LIKE pattern 其中，PR OC EDUR E参数表示查询存储过程；FUNC TION参数表示查询存储函数；LIKE，pattern.参数用来匹配存储过程或函数名称。9.3.2 S HOW C R EATE语句MyS QL中可以通过S HOW C R EATE语句来查看存储过程和函数的状态。其语法结果如下：SHOW CREATE PROCEDURE|FUNCTION sp_name;其中，PR OC EDUR E参数表示存储过涯；FUNC TION参数表示查询存储函数；sp_name参数表示存储 过程或函数的名称。【例9-5】下面查询名为count_of_student的存储过程，其代码如下：show create procedure count_o匚student;其运行结果如图9-7而示。row in set)ysql show create procedure count_of_student/Zi Procedure!sqljnode!Create Procedure!count_of.student：NO_AUTO_CREATE_USER!CREATE DEFINER=root0localhost PRO CEDURE count_of_student READS SQL DATA begin select count into count_nun from studentinfo；.图9-7应用S HOW C R EATE语句查看存储过程查询结果显示存储过程的定义、字符集等信息说明：S HOW S TATUS语句只能查看存储过程或函数所操作的数据库对象，如存储过程或函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或函数的具体定义。如果需要查看详细定义,需要使用S HOW C R EATE语句。/I I一9.4修改存储过程和函数修改存储过程和存储函数是指修改已经定义好的存储过程和函数。MyS QL中通过ALTER PR OC EDUR E语句来修改存储过程。通过ALTER FUNC TION语句来修改存储函数。MyS QL中修改存储过程和函数的语句的语法形式如下：ALTER PROCEDURE|FUNCTION sp_name characteristic.characteristic:CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA|SQL SECURITY DEFINER|INVOKER|COMMENT string其参数说明如表9-3所示。表9-3 修改存储过程和函数的语法的参数说明参数说明sp name存储过程或函数的名称characteristic指定存储函数的特性C ONTAINS S QL表示子程序包含S QL语句，但不包含读写数据的语句NO S QL表示子程序不包含S QL语句R EADS S QL DATA表示子程序中包含读数据的语句MODIFIES S QL DATA表示子程序中包含写数据的语句S QLS EC UR ITYDEFINER|INVOKE R)指明权限执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行C OMMENT string是注释信息【例9-6】下面应用此语句修改存储过程count_of_student。其代码如下:.alter procedure count_oiLstudent modifies sql datasql security invoker;其运行结果如图9-8所示。iysql alter procedure count_of_student-modifies sql data-sql security inuoker；一/luery OK,0 rows affected 图9-8修改存储过程count of student的定义说明：如果读者希望查看修改后的结果。可以应用S ELEC TFR OM studentinfo.R uotines WHER E R OUTINE NAME=sp name来查看表的信息。由于篇幅限制，这里不进行详细讲解。9.5删除存储过程和函数删除存储过程和函数指删除数据库中已经存在的存储过程或函数。MyS QL中使用DR OP PR OC EDUR E语句来删除存储过程。通过DR OP FUNC TION语句来删除存储函数。在删除之前，必须确认 该存储过程或函数没有任何依赖关系，否则可能会导致其他与其关联的存储过程无法运行。删除存储过程和函数的语法如下：DROP PROCEDURE|FUNCTION)IF EXISTS sp_name其中sp_name参数表示存储过程或函薮的名称；IF EXIS TS是MyS QL的扩展，判断存储过程或函 数是否存在，以免发生错误。【例9-7】下面删除名称为count_of_student的存储过程。其关键代码如下：drop procedure count_oiLstudent;删除存储过程count_of_student的运行结果如图9-9所示。nysql drop procedure count_of_student/Z Query OK,0 rows affected|mysql图9-9删除countofstudent存储过程【例9-8】下面删除名称为name_of_student的存储函数。其关键代码如下:drop function name_of_student;删除存储函数name_of_student的运行结果如图9T0所示。iysql drop function name_of_student/iuery OK,0 rows affected iysql图9To 删除nameofstudent存储函数当返回结果没有提示警告或报错时，则说明存储过程或存储函数已经被顺利删除。用户可以通 过查询students数据库下的R outines表来确认上面的删除是否成功。9.6综合实例一一使用存储过程实现用户 注册在数据库系统开发过程中如果能够应用存储过程可以使整个系统的运行效率有明显的提高，本实 例将向读者介绍MyS QL存储过程的创建以及PHP调用MyS QL存储过程的方式。运行本实例前首先应在命 令提示符下创建如图9-11所示的存储过程，然后运行本实例，在文本框中输入如图9-12所示的注册信 息后，nysql delimit”/nysql create procedure pro_regin nc uarcharCSB),in pwd uai*char/.in email ua rcharin address uarchar-begin-insert into tb_regname.pwd,email,addressualues ；-end；-/|uei*y OK,0 rows affected 图9-12录入注册信息单击“注册”按钮即可将用户填写的注册信息保存到数据库中，最终保存结果如图9-13所示。丁 id namepwdemailaddress/X 1 mrlzh 25d55ad283aa400af464c76d713c07ad jlnu_lzh* 吉林长春图9-13注册信息被存储到MyS QL数据库实现过程如下：(1)创建pro_reg存储过程，其代码如下：delimiter/create procedure pro_reg(in nc varchar(50),in pwd varchar(50),in email varchar(50),in address varchar(50)begininsert into tb_reg(name,pwd,email,address)values(nc,pwd,email,address);end;/(2)通过PHP预定义类mysqli实现与MyS QL数据库的连接，代码如下：query(set names utf8);设置编码格式$name=$_POSTname;$pwd=md5($_P0STpwd);$emailPOSTemair;$address=$_POSTaddress;(3)调用荐储过程projeg实现将用户录入的注册信息保存到数据库，代码如下:if($sql=$conn-query(call pro_reg(.$name.,.$pwd.,.$email.,.$address.)/调用存储过程echo alertC用户注册成功!，)；v/script;elseecho”alert(用户注册失败!);/script”;)?)使用光标(FETC H C UR S OR)和关闭光标(C LOS E C UR S IR)(5)(6)在MyS QL中可以通过S HOW S TATUS语句查看存储过程和函数的状态。MyS QL中可以通过S HOW C R EATE语句来查看存储过程和函数的状态。MyS QL中使用DR OP PR OC EDUR E语句来删除存储过程。通过DR OP FUNC TION语句来删除存储函数。行散 1.如何创建存储过程?.2.如何查看存储过程?3.如何删除存储过程