任务6.1
创建简单存储过程
1.PL/SQL的变量
变量是指在程序运行过程中其值可以改变的量,包括用户变量、系统变量和局部变量。
(1)用户变量。
用户可以在PL/SQL中使用自己定义的变量,这样的变量称为用户变量。
用户变量在使用前必须定义和初始化,如果使用没有初始化的变量,其值为NULL。
定义和初始化一个用户变量可以使用SET语句,其语法格式为:
set @ <变量名1>=<表达式1>[, @ <变量名2>=<表达式2>, ...];
说明:
① 用户变量以“@”开始,形式为“@变量名”,以便将用户变量和字段名予以区别。
② <表达式>可以为整数、实数、字符串和NULL值,例如:
set @name="OPPO";
③ 一条定义语句中,可以同时定义多个用户变量,使用半逗角号分隔,例如:
set @name,@number,@unit;
(2) 系统变量
服务器维护两种变量,全局变量影响服务器整体操作,会话变量影响具体客户端连接操作。
系统变量一般都以“@@”为前缀,如@@Version 返回 MySQL的版本。但某些特定的系统变量可以省略“@@”符号,如 Current_Date、Current_Time 和Current_User。
(3)局部变量
在语句块(BEGIN到End之间)中定义的变量为局部变量,局部变量可以保存特定类型数据,其有效作用范围在存储过程和自定义函数的语句块中,在语句块结束以后,局部变量就失效了。
MySQL的局部变量必须先声明后使用。使用 DECLARE语句声明局部变量,其声明语法格式为:
DECLARE<变量名称><数据类型>[DEFAULT<默认值>]
说明:
①“DEFAULT”子句为变量指定默认值,若不指定则默认为 NULL。
②变量名称必须符合 MySQL 标识符的命名规则,在局部变量前面不使用“@”符号。
例如:
DECLARE unit char(2);
2.PL/SQL的运算符及表达式
(1)运算符。
运算符用于执行程序代码运算,会针对一个以上操作数项目进行运算。MySQL中的运算符主要有以下类型。
①算术运算符。算术运算符用于对表达式执行数学运算,操作数可以是任何数值类型。
②赋值运算符。“-”是MySQL中的赋值运算符,可以将表达式的值赋给一个变量。
③比较运算符。比较运算符用于对两个表达式进行比较,数字以浮点值进行比较,字符串以不区分大小写的方式进行比较,若表达式成立则返回1,否则返回0。
④逻辑运算符,逻辑运算符用于对某些条件进行测试,以返回其真假。 MySQL中的逻辑运算符有And(与)、Or(或)、Not(非)。
⑤位运算符。位运算符用于对两个表达式执行二进制位操作。
⑥一元运算符,一元运算符对一个操作数执行运算,该操作数可以是任何一种数据类型。
(2)表达式
表达式是由操作数、运算符、分组符号(括号)和函数构成的组合。
表达式的值同样具有字符类型、数值类型、日期时间类型等,根据表达式的值类型可分为字符型表达式、数值表达式和日期表达式。
(3)运算符的优先级
当一个复杂的表达式有多个运算符时,运算符优先级决定执行运算的先后次序。
一元运算符按从右到左(即右结合性)的顺序运算,二元运算符按从左到右(即左结合性)的顺序运算
3.PL/SQL的控制语句
(1)BEGIN…END 语句
END语句的语法格式为:
BEGIN
<语句 1> :
<语句2>;
....
END
MySQL 中允许嵌套使用 BEGIN…END语句。
(2)IF…THEN…ELSE 语句
IF <条件> THEN
<语句块 1>
[ ELSE
<语句块 3> ]
END IF;
(3)CASE语句
①简单CASE语句。简单CASE语句用于将某个表达式与一组简单表达式进行比较,以确定其返回值,其语法格式为:
CASE <测试表达式>
WHEN<表达式1> THEN <SQL语句1>
WHEN <表达式 2> THEN <SQL语句2>
... ...
[ ELSE < SQL语句n+1> ]
END CASE ;
② 搜索 CASE 语句
搜索CASE确回结果,其语法格式为:
CASE
WHEN <逻辑表达式1> THEN <SQL 语句 1>
WHEN <逻辑表达式2> THEN <SQL 语句 2>
... ...
[ ELSE < SQL语句n+1> ]
END CASE;
(4)WHILE循环语句
WHILE循环语句用于实现循环结构,是有条件的执行循环语句,当满足指定条件时执行循环体内的语句,其语法格式为:
[begin_label:]
WHILE <条件> DO
<语句块>
END WHILE [end_label] ;
(5)LOOP循环语句
LOOP 语句用于实现循环结构。但是LOOP 语句本身没有停止循环的机制,必须遇到LEAVE语句才能停止循环。
LOOP 语句的语法格式为:
[begin_label:]
LOOP
<语句块>
END LOOP [end_label];
说明:
LOOP 语句允许语句块重复执行,实现一些简单的循环。在循环体内的语句一直重复执行直到循环被强迫终止,终止时通常使用 LEA VE 语句。
(6)REPEAT 循环语句
其语法格式为:
[begin label]
REPEAT
<语句块>
UNTIL <条件>
END REPEAT [end label];
说明:
先执行语句块,然后判断逻辑表达式的值是否为True,若为True 则停止乐, 若为 False 则继续循环。REPEAT 语句也可以被标注。
(7)LEAVE语句
其语法格式为:
LEAVE<标签>;
使用LEAVE语句可以退出被标注的循环语句,标签是自定义的。
(8)ITERATE语句
其语法格式为:
ITERATE <标签>;
ITERATE语句与LEAVE语句都是用来跳出循环语句的,但两者的功能不一样。其中,LEAVE语句用来跳出整个循环,然后执行循环语句后面的语句:而ITERATE语句是跳出本次循环,然后进行下一次循环。
4.MYSQL的存储过程
(1)存储过程主要有以下优点:
①执行效率高:存储过程编译后存储在数据库服务器端,可以直接调用从面提高了 SQL 语句的执行效率。
②灵活:存储过程可以用结构化语句编写,可以完成较复杂的判断和运算。
③数据独立;用户在程序中调用存储过程,存储过程能把数据同用户程序隔离开,其优点是当数据表结构变化时,可以随时修改存储过程,不用修改程序源代码。
④安全:存储过程可被作为一种安全机制来充分利用,系统管理员通过设置存储过程的访问权限。从而实现相应数据的访问权限限制,避免了用户对数据表的直接访问,保证了数据的安全。
⑤降低网络流量:当在客户机上调用该存储过程时,网络中传送的只是该调用语句。
(2)DELIMITER命令
ELIMITERMySQL语句的结束符,其语法格式为:
DELIMITER<自定义结束符>
DELIMITER存储程编写结束后复使用MySQL的默认结束符“;”,例如:
DELIMITER;
(3)创建简单存储过程
创建存储过程的语法格式为:
CREATE PROCEDURE 存储过程名()
BEGIN
<存储过程体>
END:
说明:
存储过程名应符合 MySQL的命名规则,避免使用与MySQL的内置两数相同的名称。
(4)查看存储过程
查看存储过程的语法格式为:
SHOW PROCEDURE STATUS[LIKE<存储过程模糊名>];
SHOW PROCEDURE STATUS proc601;
MySQL中存储过程的信息存储在information_schema数据库下的Routines表中,也可以通过查询该数据表的记录来查询存储过程的信息,例如:
SELECT * FROM inforrnation_schema.Routines WHERE Routine_ name =" proc501":
(5)调用简单存储过程
其语法格式为:
CALL 存储过程名();
(6)删除存储过程
在命令行中删除存储过程的语法格式为:
DROP PROCEDURE<存储过程名>;
任务6.2
创建带输入参数的存储过程
1.MySQL带输入参数的存储过程的创建
GREATE PROCEDURE 存储过程名([形参列表])
BEGIN
< 存储过程体>
END;
说明:
存储过程可以使用不同参数,也可以带一个或多个参数。
参数定义格式为;
[In] <参数名> <参数类型>
2.调用存储过程
其语法格式为:
CALL 存储过程名<[实参列表]);
说明; 参数个数和顺序必须一一对应。
任务6.3
创建带输入和输出参数的存储过程
MySQL带输入和输出参数的存储过程的创建
语法格式如下;
GREATE PROCEDURE 存储过程名([形参列表])
BEGIN
< 存储过程体>
END;
说明:
存储过程可以使用输入、输出和输入/输出参数。
参数的定义格式为;
[In|Out|InOut] <参数名> <参数类型>
任务6.4
创建应用游标的存储过程
MySQL带游标的存储过程
(1)声明游标。其语法格式为;
DESLARE<游标名> CURSOR FOR <selecct 语句>;
(2) 打开游标。其语法格式为;
OPEN <游标名>;
说明:
打开一个已经声明过的游标。
(3)读取游标。其语法格式为:
FETCH <游标名> INTO 变量名1 [ ,变量名2]....
(4) 关闭游标。其语法格式为:
CLOSE <游标名>;
说明:
关闭一个之前打开的游标。
任务6.5
创建与使用事务
1.事务的ACID特性
(1)原子性
一个事务(transaction)中的所有操作,要么全部执行,要么全部不执行。
(2)一致性
在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
(3)隔离性
MySQL数据库允许多个并发事务,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
(4)持久性
事务处理结束后,对数据的修改就是永久的。
2.事务控制语句
(1)开始事务
其语法格式为:
START TRANSACTION;
说明:
用于显式地启动一个事务。
(2)提交事务
其语法格式为:
COMMIT;
说明:
用于提交事务,将事务对数据所做的修改进行保存。
(3)设置保存点
其语法格式为:
SAVEPOINT<保存点名称>;
说明:
用于在事务内设置保存点。
(4)撤销事务
其语法格式为:
ROLLBACK;ROLLBACK TO SAVEPOINT<保存点名称>;
说明:
撤销事务又称为事务回滚,即事务被执行后,如果执行的 SQL语句导致业务逻辑不符或数据库操作错误,ROLLBACK语句撤销事务中所有的执行语句。 ROLLBACK TO SAVEPOINT语句撤销事务中保存点之后的执行语句。