MySQL第六章 存储过程与事务处理

1.本章目标

　　存储过程

2.存储过程

　　类似于编程语言中的函数(方法)

　　可以再存储过程中封装一组sql代码,以完成某个特定的功能

3.分类

　　存储过程

　　存储函数

　　区别

　　　　1.存储函数有且只有一个返回值,存储过程不能有返回值

　　　　2.函数只能有输入参数,而且不能显示的带上in关键字,而存储过程可以有多个in  out inout等参数

　　　　3.存储过程中的语句功能更强大,存储过程可以实现复杂的业务逻辑

　　　　　　存储函数有很多的限制,如不能在函数中使用insert  update,delete,create等语句

　　　　　　存储函数只完成查询工作,可以接收输入参数并返回一个值,也就是说存储函数的功能针对性更强

　　　　4.存储过程可以调用存储函数,但是存储函数不能调用存储过程

　　　　5.存储过程一般是作为一个独立的部分执行(call调用);而存储函数可以作为查询语句的一个部分来进行调用  如sum()

　　注意:

　　　　declare声明变量或条件分支或循环只能存放在存储过程或者函数的begin...end 中

　　　　若想在存储过程或函数以外声明变量使用,请使用:

　　　　　　1.@变量名            --没有初始值

　　　　　　2.set @变量名 = 值;     --有初始值

　　优点

　　　　存储过程和函数,都是提前预编译好的,所以执行时效率比我们单独执行逻辑代码要高

4.存储过程

　　语法:

　　　　create pracedure存储过程名(参数类型1 参数名1 数据类型,参数类型2 参数名2 数据类型2..... )

　　　　begin

　　　　　　逻辑代码

　　　　end

　　参数类型

　　　　in 输入参数,默认的

　　　　out 输出参数, 在使用完该类型参数后,可以重新获取出来

　　　　inout 输入输出参数 

　　调用

　　　　call  存储过程名(参数)　　

 课上代码!

create table A(
aid tinyint primary key default 0,
aname char(20) not null default '');

create table B(
bid tinyint primary key default 0,
bname char(20) not null default '');

insert into A values(1,'susam'),(2,'jenny'),(3,'mary');
insert into B values(2,'tom'),(3,'john'),(4,'george');

select * from A 
select * from A inner join B on A.aid=B.bid;

select * from A inner join B on A.aid=B.bid where aid=2;

select * from A left join B on A.aid=B.bid where aid=2;

select * from A where aid = (select bid from B where bid=3)

select * from A where aid = (select bid from B where bid=4)

select concat('my', 's', 'ql'); 
select left('foobarbar', 5);
select replace('www.mysql.com', 'w', 'ww'); 
　create view test2_view 
    as 
        select aname from A  
select * from test2_view
describe test2_view
show create view test2_view
--有输入参数的存储过程,
create procedure proc_query_name_by_id(in id int)
begin
    declare na varchar(20);
    select aname from A where aid=id;
    select na;
end

set @id =1;
call proc_query_name_by_id(@id);
-有输出参数的存储过程　　
create procedure proc_query_name_by_no(in bianhao char(4),out xingming varchar(20))
begin
    set xingming = (select aname from A where aid=bianhao);
end 
call proc_query_name_by_no('1',@n);
select @n;
--有输入输出参数的存储过程,任意传入一个数字,然后再过程中任意修改,最后输出返回
create procedure proc_inout(inout i int)
begin
    set i=i+2;
end 

set @i=10;
call proc_inout(@i);
select @i;

5.存储函数

　　参数只能是输入参数,并且不能显示指定in

　　必须有一个返回值

　　在查询语句中可以使用函数,像sum(),max()

　　语法:

　　　　create function 函数名(参数名 参数数据类型,参数名2 数据类型......)

　　　　returns 数据类型

　　　　begin

　　　　　　逻辑代码

　　　　　　return 值(必须和上面returns指定的类型相同)

　　　　end

create table A(
aid tinyint primary key default 0,
aname char(20) not null default '');

create table B(
bid tinyint primary key default 0,
bname char(20) not null default '');
--无参函数,查询A表中的总人数
create function fun_A_count()
returns int
begin
    declare c int;
    select count(*) into c from A;
    return c;
end
select fun_A_count();

--有参数的函数  根据员工编号 返回员工姓名
create function fun_query_name_by_no(bh int)
returns varchar(20)
begin
    return(select aname from A where aid=bh);
end 

select fun_query_name_by_no(1)

6.删除存储过程或参数

　　drop procedure/function  过程名/函数名

7.查看存储过程或函数的创建语法

　　show create procedure/function 过程名

8.MySQL编程

　　变量:

　　　　声明变量

　　　　　　declare  变量名  数据类型

　　　　变量赋值:

　　　　　　set 变量名=值

　　　　查询赋值:

　　　　　　select 字段1,字段2......into 变量1,变量2......from 表名

　　　　输出变量:

　　　　　　select 变量名 as 别名

　　注意:

　　　　在存储过程或函数区域之外声明变量

　　　　　　1.@变量名

　　　　　　2.set @变量名 = 值

　　1.if 语句

　　　　语法:

　　　　　　if 条件 then 语句1;

　　　　　　elseif 条件2 then 语句2;

　　　　　　else 语句3;

　　　　　　end if

　　

create procedure proc_demo1()
begin
    declare c int default 0;
    select count(*) into c from A;
    if c>=10 then select '太多';
    elseif c>=5 then select '刚好';
    else select '太少';
    end if;
end 
    call proc_demo1();
　　　     

　　2.case语句

　　　　语法:

　　　　　　case

　　　　　　　　when 条件1 then 语句1;

　　　　　　　　when 条件2 then 语句2;

　　　　　　　　else 语句3;

　　　　　　end case;
数值匹配 条件在前面    条件匹配在后面

--case条件匹配　　     
create procedure proc_demo2()
begin
    declare c int default 0;
    select count(*) into c from A;
    case 
        when c>10 then  select '太多';
        when c>5 then  select '刚好';
        else select '太少';
    end case;
end
    call proc_demo2();

--匹配一个具体的数值 

create procedure proc_demo3()
begin
    declare c int default 0;
    select count(*) into c from A;
    case c
        when 10 then  select '太多';
        when 3 then  select '刚好';
        else select '太少';
    end case;
end
    call proc_demo3();

　　3.loop循环:

　　　　语法:

　　　　　　循环名:loop

　　　　　　　　逻辑代码

　　　　　　end loop;

　　　　注意:

　　　　　　loop需要使用条件判断来退出循环:leave  循环名;

　　

--计算1-100的和
create procedure proc_loop100()
begin
    declare sum int default 0;-- 和
    declare i int default 1;-- 循环变量
    
    loop100:loop
        set sum=sum+i;
        set i=i+1;
        if i>100 then leave loop100;
        end if;
    end loop ;

    select sum as '100以内的和是';
end 
call proc_loop100();

　　4.repeat 循环

　　　　语法:

　　　　　　repeat

　　　　　　　　逻辑代码

　　　　　　　　until 条件

　　　　　　end repeat

　　　　注意: 如果条件为真,repeat循环结束

　　　　条件后面没有分号

--计算1-n的和,n是一个输入参数
create procedure proc_repert(n int)
begin
    declare sum int default 0;
    declare i int default 1;
    
    repeat 
        set sum=sum+i;
        set i=i+1;
        until i>n
    end repeat;
    
    select sum as '以内的和是';
end 
call proc_repert(10);

 　　5.while循环

　　　　　语法:

　　　　　　　while 条件 do

　　　　　　　　　逻辑代码;

　　　　　　　end while;

 　　　　注意:

　　　　条件如果为真,循环继续;

　

--计算1-n的和,n是一个输入参数
create procedure proc_while(n int)
begin
    declare sum int default 0;
    declare i int default 1;
    
    while i<=n do
        set sum= sum+i;
        set i=i+1;
    end while;
    select sum;
end 
call proc_while(10);

9.事务:

一、事务定义

    Transaction
    事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
    一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
    事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

二、转账操作理解事务

关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分————————也就是说银行账户转账是一个事务

三、事务四大特征(ACID)

• 原子性(A)：事务是最小单位，不可再分
• 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
• 隔离性(I)：事务A和事务B之间具有隔离性
• 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

 四、关于事务的一些术语

    开启事务：Start Transaction
    事务结束：End Transaction
    提交事务：Commit Transaction
    回滚事务：Rollback Transaction

五、和事务相关的两条重要的SQL语句(TCL)

    commit:提交
    rollback：回滚