《数据仓库》-第 7 章 数据库编程

7.1变量在程序设计里，变量是计算机分配给程序内存中的临时存储单元，变量的使用包括变量的声明、变量的赋值和变量的使用。7.1.1变量的声明用户可以使用DECLARE关键字来定义变量，其语法格式如下：DECLAREvar_name1[,var_name2]...type[defaultvalue]7.1.2变量的赋值1.用SET语句给变量赋值SETvar_name1=exper[,var_name2=exper]2.用SELECT语句给变量赋值SELECTcol_name[,…]intovar_name[,...]table_expr返回7.2定义条件和处理程序定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的方法。这种方式可以提前预测可能出现的问题，并提出解决方法。这样可以增强程序处理问题的能力，避免程序异常停止。7.2.1定义条件MySQL中可以使用DECLARE关键字来定义条件，其基本语法格式如下：DECLAREcondition_nameCONDITIONFORcondition_value其中，condition_value可参照以下格式：SQLSTATE[value]sqlstate_value|mysql_error_code下一页返回7.2定义条件和处理程序7.2.2定义处理程序MySQL中可以使用DECLARE关键字来定义处理程序，其基本语法格式如下：DECLAREhandlertypeHANDLERFORcondition_value[...]spstatement其中，handler_type可选择：CONTINUE|EXIT|UNDOcondition_value格式为：SQLSTATE[VALUE]sqlstatevalue|conditionname|SQLWARNING|NOTFOUNDSQLEXCEPTION|mysqlerrorcode上一页返回7.3存储过程SQL语句在执行的时候需要先编译再执行，存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，客户端通过指定存储过程的名字和参数（如果该存储过程需要参数）来调用执行。7.3.1创建存储过程创建存储过程，需要使用CREATEPROCEDURE语句，基本语法格式如下：CREATEPROCEDUREspname([procparameter])[characteristics…]routinebody其中，proc_parameter为存储过程参数列表，列表形式如下：[IN|OUT|INOUT]PARAM_NAMETYPE下一页返回7.3存储过程7.3.2调用存储过程使用CALL调用存储过程的语法格式如下：CALLsp_name([parameter[,...]])7.3.3查看存储过程查看系统中所有的存储过程：SHOWPROCEDURESTATUS;查看某个存储过程的详细信息：SHOWCREATEPROCEDUREsp_name;上一页下一页返回7.3存储过程7.3.4流程的控制MySQL中可以使用if语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句来进行流程控制。1.begin…end语句begin…end复合语句用来包含多个语句。其基本语法格式如下：[begin_label]:BEGIN[statement_list]end[end_label]上一页下一页返回7.3存储过程2.if语句if语句用来进行流程判断，其语法的基本形式如下：IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEIFsearch_conditionTHENstatement_list]ENDIF上一页下一页返回7.3存储过程3.case语句case语句也是进行条件判断的语句，其语法的基本格式如下：CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE上一页下一页返回7.3存储过程4.loop语句loop语句可以使用某些特定的语句充分执行，实现简单的循环。loop通常需要结合leave语句来退出循环或者iterate语句继续迭代。其基本语法格式如下：[begin_label]:LOOPStatement_listENDLOOP[end_label]5.leave语句leave语句主要用于跳出循环。其基本语法格式如下：LEAVElabel上一页下一页返回7.3存储过程6.iterate语句iterate语句是跳出本次循环，进入下一轮循环。其基本语法格式如下：ITERATElabel7.repeat语句repeat语句的作用是使其内的语句被重复。其基本语法格式如下：[begin_label:]REPEATstatement_listUNTILLsearch_conditionENDREPEAT[end_label]上一页下一页返回7.3存储过程8.while语句while语句也是有条件控制的循环语句。当满足条件时，执行循环内的语句。其基本语法格式如下：[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]上一页返回7.4函数函数在MySQL服务器中存储和执行，以完成某个功能来减少客户端和服务器端的传输。函数可以分为自定义函数和系统函数。7.4.1创建自定义函数创建自定义函数，需要使用CREATEFUNCTION语句，其基本语法格式如下：CREATEFUNCTIONsp_name([func_parameter[,...]])RETURNStype[characteristics...]routine_body其中，func_parameter为函数的参数列表，可以由多个参数组成，其中每个参数由参数名称和参数类型组成，列表形式如下：Param_nametype下一页返回7.4函数7.4.2调用函数调用自定义函数和调用系统函数一样，可以直接调用。7.4.3系统函数MySQL中常用的系统函数可参见表7-1～表7-5。通过这些系统函数，用户的操作可以得到简化。1.字符串函数（见表7-1）2.数学函数（见表7-2）3.日期时间函数（见表7-3）4.系统信息函数（见表7-4）5.加密函数（见表7-5）上一页下一页返回7.4函数7.4.4存储过程与函数的区别存储过程和函数都是在数据库中定义一些SQL语句集合，避免开发人员重复地编写相同的SQL语句，但是它们存在以下区别。（1）一般情况下，存储过程实现的功能比函数复杂一些。存储过程功能强大，函数的功能则针对性较强；自定义函数不能用于执行一族修改全局数据库状态的操作。（2）MySQL存储过程可以返回记录集，而函数只能返回值或者表对象。函数只能返回一个变量，存储过程可以返回多个变量；存储过程参数有in、out、inout等3种类型，而函数参数类型只能是输入参数（in）；存储过程和函数声明的时候，函数必须描述出返回类型。（3）存储过程可以使用非确定函数，而函数主体不允许使用非确定函数。上一页下一页返回7.4函数（4）存储过程一般作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用（可以用select调用）；函数可以返回一个表对象，因此函数可以在查询语句中位于from关键字后面。上一页返回7.5游标InnoDB是MySQL的默认存储引擎，它具有支持事务、RDBMS等特性。MySQL编程离不开游标。游标（Cursor）就是一个可读的标识，用来标识数据取到什么地方了。在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。1.声明游标声明游标的格式如下：DECLAREcursor_nameCURSORFORselect_statement下一页返回7.5游标2.打开游标声明游标后，要使用游标的时候，必须先打开游标：OPENcursor_name3.读取数据游标打开后，就可以使用fetch…into语句读取数据：FETCHcursor_nameINTOvar_name[,var_name]...4.关闭游标游标使用完以后，要及时关闭游标：CLOSEcursor_name上一页返回7.6触发器触发器（Trigger）是一种特殊的存储过程。然而触发器的执行不是由程序调用，也不是手动启动，而是由事件触发。也就是在插入、删除或修改特定表中的数据时触发。触发器常用于加强数据的完整性约束和业务规则等。触发器具有以下作用。（1）安全性。触发器可以基于数据库的值使用户具有操作数据库的某种权利。①可以基于时间限制用户的操作，如不允许下班后和节假日修改数据库数据。②可以基于数据库中的数据限制用户的操作，如不允许股票的价格升幅一次超过10%。（2）审计。可以跟踪用户对数据库的操作。例如：下一页返回7.6触发器①审计用户操作数据库的语句。②把用户对数据库的更新写入审计表。（3）实现复杂的数据完整性规则。①实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。②提供可变的默认值。（4）实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。①在修改或删除时级联修改或删除其他表中与之匹配的行。②在修改或删除时把其他表中与之匹配的行设成NULL值。③在修改或删除时把其他表中与之匹配的行级联设成默认值。（5）同步实时地复制表中的数据。上一页下一页返回7.6触发器（6）自动计算数据值，如果数据值达到了一定的要求则进行特定的处理。7.6.1创建触发器触发程序是与表有关的数据库对象，当表上出现特定事件时激活该对象。其基本语法格式如下：CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_stmt上一页下一页返回7.6触发器7.6.2使用触发器触发器尽管是一种特殊的存储过程，但是触发程序不能调用将数据返回客户端的存储程序，不能采用使用CALL语句的动态SQL（允许存储程序通过参数将数据返回）。触发程序还不能使用以显式或隐式方式开始或结束事务的语句，如STARTTRANACTION、COMMIT或ROLLBACK。MySQL支持OLD和NEW关键字。使用OLD和NEW关键字能够访问触发程序激活后被触发程序影响的行和列。用OLD.column_name访问被影响行的column_name列的旧值，用NEW.column_name访问被影响行的column_name列的新值。（1）UPDATE触发器：可以访问OLD.column_name和NEW.column_name。（2）INSERT触发器：仅可以访问NEW.column_name。上一页下一页返回7.6触发器（3）DELETE触发器：仅可以访问OLD.column_name。7.6.3查看触发器查看所有触发器信息，包括状态、语法等，可使用下列语句：SHOWTRIGGERS;若需要查看某个触发器可以使用下列语句：SHOWCREATETRIGGERS[schema_name.]trigger_name;或SELECT*FROMINFORMATION_SCHEMATRIGGERSWHERETRIGGER_NAME='trigger_name';7.6.4删除触发器MySQL中触发器的删除语句基本格式如下：DROPTRIGGER[schema_name.]trigger_name上一页返回7.7事件7.7.1开启事件调度器在使用事件调度器功能之前，需要先开启event_scheduler。（1）查看event_scheduler是否开启：SHOWVARIABLESLIKE'event_scheduler';或直接查看系统变量@@event_scheduler：SELECT@@event_scheduler;若@@event_scheduler的值为“OFF”，则说明事件调度器功能未开启。（2）开启事件调度器可以将@@event_scheduler的值改为“ON”：SETGLOBALevent_scheduler=1;或SETGLOBAL@@event_scheduler=on;或者在配置文件中添加“event_scheduler=on”。下一页返回7.7事件7.7.2创建事件创建事件的基本语法格式如下：CREATE[DEFINER={user|CURRENT_USER}]EV