MySQL(5) 数据库函数

MySQL(5) 数据库函数

#自定义函数
DELIMITER //
create function func_name(
	param_name data_type(n),
	...
)
returns rtn_type
func_body
//DELIMITER;
#例子
DELIMITER //
create function addsun(
a int,b int)

returns int
begin
return a*b;
end//
DELIMITER ;

select addsun(2,3);
#-----------------------------创建数据表----------------------------------------
DROP DATABASE IF EXISTS testdb;

CREATE DATABASE testdb;

USE testdb;

create table city(
	cityId int auto_increment primary key,
	cityName varchar(20) not null unique key
);

create table city_del_bak(
	cityId int not null,
	cityName varchar(20) not null,
	del_time timestamp not null
);

insert into city(cityName) value('南京'),('徐州'),('连云港'),('镇江'),('扬州');
#新增函数
delimiter //
create function addCity2(cityName varchar(20)) returns int
begin
	declare _cityName varchar(20) default cityName;
	declare _count int default 0;
	insert into city(cityName) value(_cityName);
	select count(1) into _count from city where cityName=_cityName;
	return _count;
end//
delimiter ;

select addCity2('兰州');
#-----------------------------分页查询存储过程-----------------------------
drop procedure if exists pro_city_page;
delimiter //
create procedure pro_city_page(
	in pageNo int unsigned,
	in pagesize int unsigned,
	out total int
)
begin
	declare _begin int;
	set _begin=(pageNo-1)*pagesize;

	select ceil(count(cityId)/pageSize) into total from city;
	if total>0 and pageNo<=total then	
		select
			*
		from
			city
		order by
			cityId
		limit
			_begin,pagesize;
	end if;
end//
delimiter ;	

delimiter //
set @total=0;
call pro_city_page(1,3,@total);
select @total;
//
delimiter ;

模拟银行转账

#-----------------------------模拟银行转账（事务+异常）-----------------------------
drop table if exists account;
create table account(
	account int auto_increment primary key,
	balance decimal(10,2) not null default 0
);
###给账户存钱
insert into account(balance) value(2000.36),(200.36);
drop procedure if exists pro_transfer;
delimiter //
	###存储过程
create procedure pro_transfer(	
	in accfrom int,
	in accto int,
	in money decimal(10,2),
	out rtn int
)
begin
	declare frombalance decimal(10,2) default 0;
	declare _tocount int default 0;
	###异常
	declare continue handler for sqlexception set rtn=-1;
	start transaction;
	#查询转入账户
	select balance into frombalance from account where account=accfrom;
	select count(1) into _tocount from account where account=accto;
	if money>fromBalance then
		set rtn = 0;
	elseif _tocount = 0 then
		set rtn = 1;
	else
		update account set balance=balance-money where account=accfrom;
		update account set balance=balance+money where account=accto;
		set rtn = 2;
	end if;
	if rtn !=2 then
		rollback;
	else
		commit;
	end if;
end//
delimiter ;
###账户1和2转账33
delimiter //
set @rtn=0;
call pro_transfer(1,2,33,@rtn);
select @rtn;
//
delimiter ;

触发器

#-----------------------------创建删除备份触发器-----------------------------
drop trigger if exists tr_city_del_bak;
create trigger tr_city_del_bak
before delete
on city
for each row
insert into city_del_bak(cityId,cityname) value(old.cityId,old.cityname);
###查看触发器
select * from city_del_bak;
###删除字段
delete from city where cityId=7;
###查看触发器
select * from city_del_bak;
###删除触发器
drop tigger tr_city_del_bak;