SQL基础数据教程 6

单元9处理学生选课系统数据库的业务--数据库编程目录Contents学习目标9.1任务描述9.2知识准备9.2.1存储过程 9.2.2存储函数 9.2.3变量 9.2.4流程控制 9.2.5错误处理 9.2.6游标 9.2.7触发器在一个电商系统中，处理订单支付流程时，可能涉及更新订单状态、扣除库存、记录支付日志等多个操作，将这些操作封装在一个存储过程中，就能确保整个业务逻辑的原子性和一致性。在这个过程中，需要用到数据库编程。通过本单元的学习，了解​存储过程的创建、删除、修改、查看等操作。数据库编程学习目标掌握存储过程的概念，能够创建、调用以及删除存储过程；​掌握存储函数的概念，能够创建、调用以及删除存储函数；掌握变量的概念，能够定义和初始化一个用户变量；掌握MySQL中的流程控制语句；了解游标的概念，能够定义游标、打开游标、利用游标检索数据和关闭游标；掌握触发器的查看以及创建。数据库编程9.1任务描述数据库编程9.2知识准备1.存储过程的概念对于SQL编程而言，存储过程是数据中的一个重要的对象，它是在大型数据库系统中一组为了完成特定功能的SQL语句集，也就是说，存储过程可以简单的理解为一组经过编译并保存再数据库中的SQL语句的集合，随时可以被调用。数据库编程9.2.1存储过程1.存储过程的概念例如，要完成一个办公用品购买的订单处理，一般需要完成以下几步：（1）在购买订单之前，需要查看库存中是否有相应办公用品；（2）如果库存中没有，需要向供应商订货；（3）如果库存中有，需要向供应商预定，并修改库存数量。对于上述几步，显然不能通过单条SQL语句实现。在实际应用中，一个完整的操作会包含多条SQL语句。为此，可将多条SQL语句创建为存储过程或函数，以方便应用。数据库编程9.2.1存储过程2.存储过程的创建在MySQL中，创建存储过程首先需要临时修改语句结束符号，首先利用关键字PROCEDURE创建存储过程，然后再利用CREATE语句进行创建，语法形式如下。DELIMITER新结束符号CREATEPROCEDURE过程名字([[IN|OUT|INOUT]参数名称参数类型])BEGIN过程体END新结束符号DELIMITER;数据库编程9.2.1存储过程2.存储过程的创建存储过程设置参数时，在参数名前还可以指定参数的来源及用途，区别如下。IN：输入参数，即把外界数据传入到存储过程当中，传入的数据可以是直接数据（如15），也可以是保存数据的变量。OUT：输出参数，初始值为NULL，它是将存储过程产生的数据传给外界。INOUT：输入输出参数，即外界数据可以传入到存储过程，也可以将存储过程产生的数据传给外界。注意：存储过程的参数名不要与数据表中的字段名一致，否则系统会报错。数据库编程9.2.1存储过程2.存储过程的创建创建存储过程首先需要临时修改语句结束符号，首先利用关键字PROCEDURE创建存储过程，然后再利用CREATE语句进行创建，语法形式如下。DELIMITER新结束符号CREATEPROCEDURE过程名字([[IN|OUT|INOUT]参数名称参数类型])BEGIN过程体END新结束符号DELIMITER;$$：新结束符号数据库编程9.2.1存储过程3.调用存储过程调用存储过程，语法形式如下：CALL数据库名.存储过程名([参数列表]);CALL：调用存储过程。注意：调用存储过程的参数列表需要与创建存储过程的参数列表一致。由于存储过程和数据库相关，如果要执行其他数据库中的存储过程，则调用时需要指定数据库名。查看存储过程在创建完存储过程之后，可以通过语句查看存储过程，语法形式如下：SHOWCREATEPROCEDURE过程名;数据库编程9.2.1存储过程4.查看存储过程在创建完存储过程之后，可以通过语句查看存储过程，语法形式如下：SHOWCREATEPROCEDURE过程名;数据库编程9.2.1存储过程5.存储过程的修改在实际数据库管理中，业务需求有时候会有变动，我们就需要修改存储过程的特性。在MySQL中可以使用ALTER语句修改存储过程的特性，语法形式如下：ALTERPROCEDURE存储过程名称[特征]数据库编程9.2.1存储过程5.存储过程的修改注意：ALTERPROCEDURE是修改存储过程的关键字，但不能更改存储过程的参数或主体。特征是要修改存储过程的哪个部分。数据库编程9.2.1存储过程5.存储过程的删除在实际数据库管理中，需要废弃存储过程时，需要删除存储过程。在MySQL中可以使用DROP语句删除存储过程，语法形式如下：DROPPROCEDURE[IFEXISTS]存储过程名称数据库编程9.2.2存储函数1.创建存储函数存储函数是由SQL和过程式语句组成的代码片段，并且可以从应用程序和SQL中调用。它与存储过程很相似，但是有如下区别：（1）存储函数本身有输出参数，不在需要输出参数。（2）不能用CALL语句调用存储函数。（3）存储函数必须包含RETURN语句，但存储过程中不允许有RETURN语句。数据库编程9.2.2存储函数1.创建存储函数在MySQL中，创建存储函数利用关键字CREATEFUNCTION来实现，它的语法形式如下CREATEFUNCTION存储函数名([参数名[,...])RETURN类型DETERMINISTICBEGIN函数体END$$DELIMITER;参数：存储函数的参数只有参数名和数据类型。数据库编程9.2.2存储函数1.创建存储函数在MySQL中，创建存储函数利用关键字CREATEFUNCTION来实现，它的语法形式如下CREATEFUNCTION存储函数名([参数名[,...])RETURN类型DETERMINISTICBEGIN函数体END$$DELIMITER;注意：存储函数的参数只有参数名和数据类型。数据库编程9.2.2存储函数2.调用存储函数存储过程的调用使用的关键字是CALL，对于存储函数的调用使用的关键字是SELECT，语法格式如下：SELECT存储函数名(参数[,...])数据库编程9.2.2存储函数3.删除存储函数删除存储函数使用的关键字和删除存储过程的关键字相同，都是使用DROP，其语法格式如下。DROPFUNCTION(IFEXISTS)存储函数名注意：IFEXISTS表示如果函数不存在，可以使用该子句可以防止发生错误。数据库编程9.2.3变量1.变量的概念变量作为表达式的最基本的元素，可用于存储临时数据，具有变量名和数据类型两个属性，变量名用于标识该变量，数据类型用于确定该变量存储的数据类型。在MySQL中，根据变量的定义方式，分为用户变量、局部变量和系统变量。（1）用户变量：用户可以自己定义的变量，带有前缀@，仅限于当前用户。（2）局部变量：一般用于当前SQL语句块中，比如存储过程的BEGIN...END中，仅限于当前语句块。（3）系统变量：MySQL中默认的系统变量，带有前缀@@，系统变量分为全局变量和会话变量。全局变量会影响整个服务器，会话变量仅限于个人客户端。数据库编程9.2.3变量2.用户变量定义和初始化一个用户变量，可以使用SET语句，其语法格式如下。SET@用户变量=表达式;注意：表达式可以是常量、变量，也可以是表达式。数据库编程9.2.3变量3.局部变量定义和初始化一个局部变量，可以使用DECLARE关键字，其语法格式如下。DECLARE变量名

数据类型DEFAULT默认值;注意：认值可写可不写，不写的话，默认为NULL。数据库编程9.2.3变量4.系统变量当数据库启动之际，系统会自动加载一系列默认设置，这些预定义的配置参数即为系统变量。它们在数据库运行的底层默默发挥作用，从连接管理、内存分配，到数据存储格式与安全策略等各个方面，全方位奠定了数据库初始的运行环境与基础规则

。数据库编程9.2.4流程控制1.IF语句在MySQL中提供了两种IF语句，一种是在SQL语句中的条件判断，一种是在存储过程、存储函数中的条件判断。（1）在SQL语句中的条件判断在SQL编程里，条件判断是一项极为关键的操作，能够依据不同的条件执行不同的逻辑。在SQL语句中运用条件判断时，有特定的语法格式，其语法格式如下。IF(条件表达式，表达式1，表达式2）注意：在上述语法中，先去进行条件表达式的判断，若为真，则执行表达式1的语句；若为假，则取执行表达式2的语句。数据库编程9.2.4流程控制1.IF语句（2）在流程控制中的条件判断在MySQL数据库系统里，流程控制是实现复杂业务逻辑的重要手段，而条件判断作为流程控制的核心组成部分，发挥着至关重要的作用。关于流程控制中的条件判断，其语法格式如下。IF条件表达式1THEN语句列表[ELSEIF条件表达式2THEN语句列表]...[ELSE语句列表]ENDIF数据库编程9.2.4流程控制2.CASE语句在MySQL中提供了两种CASE语句，一种是在SQL语句中的条件判断，一种是在存储过程、存储函数中的条件判断。这两种情况的语法格式相同，其语法格式如下。CASE条件表达式WHEN表达式1THEN结果1[WHEN表达式2THEN结果2]...[ELSE结果n]END数据库编程9.2.4流程控制3.LOOP语句若要实现简单的循环逻辑，LOOP语句是一个常用工具，其能够按照特定规则重复执行一段代码块，直至满足特定条件才会停止。LOOP语句常用于实现简单的循环，其语法格式如下[标签：]LOOP语句列表IF条件表达式THENLEAVE[标签]ENDIF;ENDLOOP[标签];数据库编程9.2.4流程控制4.WHILE语句WHILE语句常用于带条件判断的循环，首先会对条件进行判断，若满足条件，才会执行，其语法格式如下。[标签:]WHILE条件表达式DO语句列表ENDWHILE[标签]注意：在上述语法中，只要条件表达式为真，则循环执行语句列表，直到条件表达式为假时，则退出循环。数据库编程9.2.4流程控制5.REPEAT语句REPEAT语句常用于带条件的判断，它与WHILE语句的不同之处在于，它会先对语句列表执行一次，然后再对条件表达式进行判断，若满足条件，接着执行，否则退出循环，其语法格式如下。[标签：]REPEAT语句列表UNTIL条件表达式ENDREPEAT[标签]数据库编程9.2.4流程控制6.LEAVE语句LEAVE和ITERATE语句是流程跳转语句，LEAVE常用于终止当前循环，而ITERATE常用于终止本次循环，进行下一轮的循环。其语法格式如下。{ITERATE|LEAVE}标签名;数据库编程9.2.5错误处理1.定义条件在MySQL中，可通过关键字DECLARE...CONDITION，其语法格式如下。DECLARE条件名CONDITIONFOR[条件类型];注意：在上述语法中，条件类型可取值如下：（1）MYSQL_ERROR_CODE：标识数值类型错误代码。（2）SQLSTATE_VALUE：标识长度为5的字符串类型错误代码。数据库编程9.2.5错误处理2.定义处理程序在MySQL中，可以使用关键字DECLARE...HANDLER定义处理程序，其语法格式如下。DECLARE异常处理方式HANDLERFOR错误类型[，错误类型...]程序语句段注意：在上述语法中，异常处理方式取值有两种，一种是CONTINUE（遇到错误不处理，继续执行以后语句），另一种是EXIT（遇到错误时马上退出）。数据库编程9.2.5错误处理2.定义处理程序错误类型有6种，具体取值：数据库编程9.2.6游标1.定义游标在MySQL中，游标在使用之前，需要先定义游标，用关键字DECLARE来定义，其语法格式如下。DECLARE游标名称CURSORFORSELECT语句；注意：在上述语法中，SELECT语句不能含有INTO关键字，它会返回一个结果集，用于创建游标。数据库编程9.2.6游标2.打开游标打开游标的关键字是OPEN，其语法格式如下。OPEN游标名称；数据库编程9.2.6游标3.利用游标检索数据在MySQL中，可以使用关键字FETCH检索结果集中的数据。每访问一次FETCH语句，就会获取一行记录。FETCH游标名称INTO变量名[,