八、存储过程

mysql在5.0版本后提供了存储过程和函数。

语法与oracle不同在于：
1）指定参数是输入、输出、输入输出的参数（in、out、 in out）放在参数名前面。
2）以begin开始，以end结束语句块。声明在begin之后。
3）用delimiter关键字来改变语句结束的标志。（默认是分号，若不修改的话，会将存储过

程当做单条语句处理，并报错）
4）可以进行dml操作

如：
mysql> delimiter //
mysql> create procedure proc_name(in cc integer)
    -> begin
    -> declare v varchar(20);
    -> if cc=1 then
    -> set v='mysql';
    -> else
    -> set v='php';
    -> end if;
    -> insert into tb(name) values(v);
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

执行结束后需要将语句结束符改回成分号：
mysql> delimiter ;

存储过程的调用方式为：call sp_name（parameter……）；
函数的调用方式为： select function_name(……);

进行创建表操作的例子：
mysql> create procedure p_test()
    -> begin
    ->   create table tt(t_name varchar(2));
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> call p_test();
-> //
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
    -> //
+----------------+
| Tables_in_test |
+----------------+
| t              |
| tb             |
| tt             |
+----------------+
3 rows in set (0.00 sec)

创建函数：--注意是returns，不是return
mysql> create function f_name(t_n int)
    -> returns varchar(20)
    -> begin
    -> return(select t_name from tt where t_name=t_n);
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

存储过程中的变量：
mysql存储过程中声明的变量分为局部变量和全局变量两种。
局部变量作用域只在其声明所在的程序块或子块中。
全局变量又叫做会话变量，在存储过程中有效。以@作为起始符号。
定义变量的语句是declare variable_name1，variable_name2…… type default xxxx；
不设置默认值时默认是NULL。

为变量赋值
方法1：set variable_name=xxxx，variable_name2=xxxx……；
方法2：select xxx into variable_name from table_name ……;
两种方式都可以同时为多个变量赋值。

mysql中游标的使用方法和oracle显式游标使用方法类似，但声明不太一样。

create procedure p_test2(out var_out varchar)
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) default 'NO_RECORD';
open c_a;
fetch c_a into var_a;
set var_out=var_a;
close c_a;
end;
//
以上语句执行报错，不太明白。

mysql> create procedure p_test2(out var_out varchar)
    -> begin
    -> declare c_a cursor for select t_name from tt;
    -> declare var_a varchar(20) default 'NO_RECORD';
    -> open c_a;
    -> fetch c_a into var_a;
    -> set var_out=var_a;
    -> close c_a;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) ' at line 1

mysql的存储过程中，流程控制语句除if、case、loop、while外，还有iterate和leave。

if语句语法为：
if xxx then xxxx
elseif xxx then xxx
else xxx
end if;

case语法：
case xxx
when xxx then xxx
when xxx then xxx
【else xxx】
end case；

while语法：
while xxxxxxx do
……
end while;

loop语法：
loop
……
end loop；
可以用“leave 循环标签名”退出循环

repeat：
先执行一次循环体，再判断condition：
repeat
……
until condition
end repeat：

iterate表示“再次循环”。与leave功能相反。

查看存储过程和函数的状态：
show {procedure|function} status [like 'xxxxx'];
查看存储过程和函数的定义：
show create {procedure|function} sp_name；

修改存储过程和函数：
alter {procedure|function} sp_name xxxxx;
删除存储过程和函数：
drop {procedure|function}[if exists] sp_name;

捕获错误

查看：
show triggers；
select * from information_schema.triggers;
删除：
drop trigger xxxx;

触发器不能包含事务处理语句或call语句

十、事务
mysql有innodb和bdb两种类型的数据表支持事务。要创建事务，必须是这两类表中的一种。
因此首先需要创建或修改，以便得到Innodb或BDB表。
创建：create table xxx(……) type=INNODB/BDB;
修改：alter table xxx type innodb/BDB;

创建事务的一般过程为初始化事务、创建事务、查询数据是否被录入和提交事务。
1）初始化事务：
start transaction；
2）创建事务：
就是执行事务中的操作（insert、update、delete等）
3）查看
用select查看第二步的操作结果
4）提交事务
commit
5）撤销事务
rollback

mysql默认是事务自动提交的，也就是我们每输入一条DML语句后，自动执行commit语句，提

交当前事务。若需要手动提交，可以设置set autocommit=0；
查看该参数的方法为：
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

事务分为序列化、可重复读取、提交后读取、未提交读取四个隔离级别。
mysql默认是可重复读取，即repeatable read。
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

为了数据的一致性，可以将表锁住：
mysql> lock table t read;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;
+------+------+
| id   | code |
+------+------+
|    1 | ACBD |
|    2 | ABCD |
|    3 | BACD |
|    4 | BADC |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from t where code='ACBD';
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where code='ACBD';
Query OK, 1 row affected (0.13 sec)

这是一种mysql的伪事务。设置lock的类型为read后，用户只能读数据，不能进行其他操作

；若设置lock类型为write，则当前用户可以修改数据，而其他会话中的用户不能进行任何

读操作。

十一、mysql系统管理

工具：
mysqld_safe 启动、监控、重启mysqld服务器程序的工具
mysqladmin 关闭服务器或服务器不正常时检查运行状态等
mysqlcheck、isamchk、myisamchk 对数据表进行分析、优化
mysqldump、mysqlhotcopy 备份或复制工具

数据目录的位置：
mysql> show variables like 'datadir';
+---------------+---------------------------------------------------------------
--------------------+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
--------------------+
| datadir | D:\Documents and Settings\All Users\Application Data\MySQL\MyS
QL Server 5.5\Data\ |
+---------------+---------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

也可以用mysqladmin的命令：
mysqladmin variables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

从名称上初步理解为：
“information_schema”是所有数据库对象的属性信息
“mysql”数据库是用户、权限、时区、日志等基本信息
“performance_schema”存储性能相关的设置信息

mysql初识（二）