简述
存储过程是SQL语句和控制流语句的语句串(语句集合)。它不仅可以带有输入
参数还可以带有输出参数,存储过程是能够通过介绍参数向调用者返回结果集,结果集的格式由调用者确定。返回状态值给调用者,指明调用是成功或是失败,包括针对数据库的操作语句,并且可以在一个存储过程中调用另一存储过程。
较SQL语句存储过程的优点:
1、存储过程允许组件是编程,存储过程在被创建以后,可以在程序中多次调用,而不必重新编写存储过程的SQL语句,从而提高了程序的可移植性。
2、存储过程能够实现较快的存储速度。
3、存储过程能够减少网络流量。对于针对数据库对象的相同操作如查询,修改表如果这一操作所涉及的SQL语句被组织成存储过程,那么当计算机调用该存储过程时,网络中传送的只是该调用语句,而不是多条SQL语句,从而大大提高网络流量见底网络负载。
4、存储过程可被作为一种安全机制。
一、存储过程的创建
1.1 存储过程语法:
create procedure <过程名>(参数1、参数2、...) begin sql; end
创建存储过程之前我们必须使用delimiter修改MySQL语句的默认结束符,否则不能创建成功。
存储过程语句的注释:
MySQL注释的两种风格
"--":单行注释
/*......*/:一般用于多行注释
语法:
delimiter <新执行符号> delimiter //将默认结束符修改为//。
1.2 调用存储过程
call <过程名>(参数1、参数2、...);
1.3 存储过程参数类型
1.3.1 IN参数
作用:读取外部变量值,且有效范围仅限于存储过程内部
mysql> delimiter // mysql> create procedure pin(in p_in int) -> begin -> select p_in; -> set p_in=2; -> select p_in; -> end; -> //
等同于 set @p_in=1;
call pin(2)与select @p_in结果作比较
实例1 存储过程的简单创建和调用
mysql> delimiter % mysql> create procedure selcg() -> begin -> select * from category; -> end % Query OK, 0 rows affected (0.00 sec) mysql> call selcg()% +---------+---------------+ | bTypeId | bTypeName | +---------+---------------+ | 1 | windows应用 | | 2 | 网站 | | 3 | 3D动画 | | 4 | linux学习 | | 5 | Delphi学习 | | 6 | *** | | 7 | 网络技术 | | 8 | 安全 | | 9 | 平面 | | 10 | AutoCAD技术 | +---------+---------------+ 10 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
定义存储过程getonebook,当输入书的ID后可以调用处对应的书籍记录
mysql> delimiter // mysql> create procedure getonebook(in id int) -> begin -> select * from books where bId=id; -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call getonebook(4) -> ; +-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+ | bId | bName | bTypeId | publishing | price | pubDate | author | ISBN | +-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+ | 4 | pagemaker 7.0短期培训教程 | 9 | 中国电力出版社 | 43 | 2005-01-01 | 孙利英 | 7121008947 | +-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
1.3.2 Out参数
作用:不都去外部变量值,在存储过程执行完毕后保留新值。
实例2、
mysql> delimiter // mysql> create procedure pout(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> set @p_out=1;
执行call @p_out存储过程之后,再次使用select @p_out则显示为2;则说明此时变量@P_out已经被赋予2;
1.3.3 如何调用存储过程out类型的返回值
例4、编辑存储过程,使返回值是书名相关信息。
mysql> delimiter // mysql> create procedure demo(out pa varchar(200)) -> begin -> select bName into pa from books where bId=2; -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call demo(@a); Query OK, 1 row affected (0.00 sec) mysql> select @a; +-----------------------+ | @a | +-----------------------+ | ***与网络安全 | +-----------------------+ 1 row in set (0.00 sec)
1.3.4 Inout参数
作用:读取外部变量,在存储过程执行完毕后保留心新值<类似银行存款>
mysql> create procedure pinout(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end; -> //
1.3.5 不加参数的存储过程
如果存储国恒在创建的时候没有指定参数类型,则需要在调用的时候指定参数值。
mysql> create table t1(id int(10)); Query OK, 0 rows affected (0.03 sec mysql> create procedure t2(n1 int) -> begin -> set @x=0; -> repeat set @x=@x+1; -> insert into t2 values(@x); -> until @x>n1 -> end repeat; -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> create table t2(id int(10)); Query OK, 0 rows affected (0.03 sec)
mysql> call t2(10); 循环10次 Query OK, 1 row affected (0.03 sec) 结果验证 mysql> select * from t2;
二、存储过程变量的使用
2.1 使用declare进行变量定义
变量定义:declare variable_name [,variable_name......]
datatype [default value]; datatype为MySQL的数据类型,如int,float,date,varchar(length)等 作用:变量赋值可以在不同额存储过程中的继承 create procedure decl() mysql> delimiter // mysql> create procedure decl() -> begin -> declare name varchar(20); -> set name=(select bName from books where bI=12); -> select name; -> end// Query OK, 0 rows affected (0.00 sec)
二、存储过程的流程控制语句
2.1 BEGIN......END语句
定义由顺序执行的SQL语句构成的块。
语法格式:
BEGIN Statement Block END
2.2 IF...ELSE语句
该语句用来定义有条件执行的某些语句,其中ELSE语句是可选择的
语法格式:
IF Boolean_expression statement [ELSE [IF boolean_expression] statentent]
2.3 循环语句
1、while......end while:
while 1 do ... if *** then break;end while
2、repeat ......end repeat:
执行操作后检查结果,而while则是执行前进行检查
3、loop......end loop:
loop循环不需要初始化条件,类似while循环,同时repeat循环一样不需要结束条件,leave语句的意义是离不开循环。
4、LABLES标号
可以用在begin repeat while 或者loop语句前,语句标号只能在合法的语句前使用。可以跳出循环,使运行指令达到符合语句的最后异步
5、ITERATE迭代
通过引用符合语句的标号,来重新开始符合语句
查看存储过程:
show create procedure demo \G
查看所有存储过程
mysql> show procedure status\G;
修改存储过程:
使用alter语句修改
alter {procedure|function} sp_name [characteristic...]
characteristic:
{contains SQL| NO SQL|READS SQL DATA|MODIFIES SQL DATA}
| SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
sp_name参数表示存储过程或函数名称
characreristic参数指定存储过程函数的特性。
CONTAINS SQL表示子进程包含SQL语句,但不包含读或写数据的语句;
NO SQL表示子程序中不包含SQL语句
READSSQL DATA表示子程序博阿寒写数据的语句。
SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行
DEFINER表示只有定义者自己才能够执行
INVOKER表示调用者可以执行
COMMENT 'string'是注释信息
删除存储过程
语法一:drop procedure sp_name
语法二:drop procedure if exists sp_name
注:不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
事务是由一组SQL语句组成的逻辑处理单元,要不全成功要不全失败。
事务处理:可以确保非事务性单元的多个操作都能够成功完成,否则不会更新数据资源。
数据库默认事务是自动提交的, 也就是发一条 sql 它就执行一条。如果想多条 sql 放在一个事务中执行,则需要使用事务进行处理。当我们开启一个事务,并且没有提交,mysql 会自动回滚事务。或者我们使用 rollback 命令手动回滚事务。
作用:事务是程序更加可靠,简化错误恢复
四大特性:
原子性(Autmic):事务在执行,要么全做,要么不做。
一致性(Consistency):事务必须是使数据库从一个一致性状态编导另一个一致性状态,一致性与原子性密切相关。在事务开始之前和结束之后,数据库的完整性没有被破坏。
隔离性(Isolation):一个事务的执行不能被其他事务干扰。及一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰,这些通常经过加锁来实现。
持久性(Durability):指一个事务一旦提交,他对数据中的数据的改变就应该是永久性的,接下来的其他操作或故障不应该对齐有任何影响。
mysql事物处理实例
MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit 事务确认
2.直接用set来改变mysql的自动提交模式
mysql默认是自动提交的,也就是你提交一个query,就直接执行!可以通过
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
但要注意当用set autocommit = 0 的时候,你以后所有的sql都将作为事务处理,直到你用commit确认或 rollback结束,注意当你结束这个事务的同时也开启了新的事务!按第一种方法只将当前的做为一个事务!
MYSQL只有 INNODB和BDB类型的数据表才支持事务处理,其他的类型是不支持的!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。