MySQL学习之路7-索引、事务、函数、存储过程、游标

show index from orders;#显示索引
 
alter table orders add index oid_index(oid);#添加索引

drop index oid_index on orders;#删除索引

#自定义函数
delimiter //
create function linearfunction(x int)
returns int
begin 
declare y int;
set y= 2*x+1;
return y;
end //
delimiter ;

select linearfunction(5);#调用自定义函数
drop function linearfunction;#删除自定义函数

#存储过程
delimiter //
create procedure info(score int)
begin 
select * from stuscore where Math>score;
end //
delimiter ;
call info(90);#调用存储过程
drop procedure info;#删除存储过程

create table bank_account(
	id int unsigned auto_increment primary key,
	account_name varchar(10),
	account_balance decimal(10,2)
);
show tables;
insert into bank_account(account_name,account_balance)
values
(‘客户A‘,500),
(‘客户B‘,300);
begin;#开启事务
update  bank_account set account_balance = 400 where account_name = ‘客户A‘;
update  bank_account set account_balance = 400 where account_name = ‘客户B‘;
rollback;#回退
commit;#提交
select * from bank_account;

delimiter //
create procedure info_cursor()
begin
	declare var_id int(10);
	declare var_stuname varchar(20);
	declare var_math decimal(5,1);
	
	#创建游标
	declare score_cursor cursor FOR
	select stuid,stuname,Math from stuscore where class = 1;
	
	#打开游标
	open score_cursor;
	
	#使用游标
	fetch score_cursor into var_id,var_stuname,var_math;
	select var_id,var_stuname,var_math;
	
	#关闭游标
	close score_cursor;
	
end //
delimiter ;
call info_cursor();#调用游标