数据库存储函数

思想：

(问题：数据库字符集，配置问题)

#存储过程做插入

delimiter //

create procedure insertdata(oid int,oprice int,odate date,ocus varchar(64) charset utf8)

begin

insert into orders values

(oid,oprice,odate,ocus);

select * from orders;

end //

delimiter;

drop procedure insertdata;

show create procedure insertdata;

call insertdata(7,500,'2001-5-6','pika');

---

#修改数据库字符集

alter schema 'day06' default character set utf8;

#变量

#局部变量 申明定义 declare    default 设置初始值

delimiter //

create procedure getsum(in a int,in b int)

begin

declare c int default 0;#定义一个变量c  初始值为0；

set c=a+b;

select c as '和'；

end//

delimiter;

call getsum(20,23);

#用户变量 生命周期当前连接

set @x=10;

set @y=23;

set @sum=@x+@y;

select @x;

select @sum;

#会话变量  当前连接有效  重新连接恢复默认值

show session variables;

#全局变量 当前服务有效 重启服务恢复默认值

show global variables;

select @@version;

select @@wait_timeout;

---

#流程控制：相当于c++里的{}

#begin  end     {}

create procedure test1()

begin

declare x int;

declare y date;

begin

set x=100;

set y=now();

select x,y;

end;

end//

delimiter;

call test1();

#if 结束用end if

delimiter //

create procedure test2(in x int)

begin

if x>100 then

select '大于100'；

elseif x=100 then

select '等于100'；

else

select '小于100'；

endif;

end//

delimiter;

call test2(200);

#case 分支 end case;

delimiter //

create procedure test3(in socre int)

begin

case score

when 10 then select 'A';

when 20 then select 'B';

when 30 then select 'C';

else select 'D';

end case;

end//

delimiter;

call test3(60);

#while 循环语句 end while;

dlimiter //

create produce test4(out sum int)

begin

delete i int default 1;

declare s int default 0;

while i<=10 do

set s=s+i;

set i=i+1;

end while;

set sum=s;

end //

delimiter;

call test4(@s);

select @s;

#loop循环 end loop

delimiter //

create procedure test5()

begin

declare i int default 1;

ins:loop

set i=i+1;

if i=11 then

leave ins;

end if;

end loop;

select i;

end//

delimiter;

call test5();

#repeat 循环 end repeat;

delimiter //

create procedure test6()

begin

declare i int default 1;

repeatb

set i=i+1;

until i>11 #直到满足条件退出，until不能有；

select i;

end//

delimiter;

call test6();

#iterate 必须用在循环里 结束本次循环 进入下次循环

#自定义函数

create function getGrade(mark int)

returns varchar(10)

begin

#...

return(

case floor(mark/10)

when 100 then 'A'

when 90 then 'B'

when 80 then 'C'

else '不及格'

end

);

end//

delimiter;

select getGrade(100);

drop function getgrade;#不区分大小写

---

存储过程和函数-{DDL,参数类型（in,out,inout）,流程控制（

begin end,if,case,while,loop,repeat,iterate）}

---

---

触发器：trigger{3种触发方式（insert,update,delete）}

#6种触发器{(before,insert),(after,insert),(before,update) ..........}

new(新的数据)     old(原来的数据)

#创建一个触发器(最初格式)

create trigger trigger_name

before |after   insert|update|delete

on table_name for each row

[trigger_order]

trigger_body

##

create database day09;

use day09;

create table test1(

id int primary key auto_increment,

number decimal(10,2)

)engine innodb default charset utf8;

insert into test1 values(null,1),(null,2),(null,3);

select * from test1;

#创建一个触发器，限制表的修改

#更新成<0的数   =0  更新成>100的数  =100

delimiter //

create trigger tir_test1_update

    before update

on test1 for each row

begin

if new.number<0 then

set new.number=0;

elseif new.number>100 then

set new.number=100;

endif;

end;

//

delimiter;

update test1 set number=-20 where id=1;

select * from test1 where id=1;

update test1 set number=200 where id=2;

select * from test1 where id=2;

#

#触发器时机要搞清楚

---

#mysql触发器都存放在 information_schema.triggers;

show databases;

use information_schema;

show tables;

select * form information_schema.Triggers ;

select * form information_schema.Triggers  where trigger_name='tir_test1_update1';

use day09;

drop trigger tir_test1_update1;

#做日志(对表做的一切操作都会被记录在日志)触发器做日志

---

create table  review

(

id int primary key auto_increment,

username varchar(64),

action varchar(20),

action_time datetime

)engine innodb default charset utf8;

delimiter //

create trigger test_insert

after insert  //在插入之后做操作

on test1 for each row

begin

insert into review values(null,user(),'insert',now());

end;//

delimiter;

---

create trigger test_update

after update  //在插入之后做操作

on test1 for each row

begin

insert into review values(null,user(),'update',now());

end;//

delimiter;

---

create trigger test_delete

after delete  //在插入之后做操作

on test1 for each row

begin

insert into review values(null,user(),'delete',now());

end;//

delimiter;

---

select * from review;

insert into test1 values(null,4);

update  test1 set number=20 where id=4;

delete from test1 where id=4;

---

#触发器中不能将数据返回的存储过程 也不能采用call的动态sql

（可以通过参数带出数据）

#不能使用开始和结束事务

delimiter //

create trigger tri

after delete

on test1 for each row

begin

#select 100;//错误

#declare msg varchar(100);

#select '100' into msg;

select '100' into @msg;

#1 成功

#2 成功

#3 失败   三条语句，中间有一条错误，整个过程就当没有执行过

#保持数据的一致性，回到最初的状态，当作没发生一样

end;//

delimiter;

delete from test1 where id=7;

select @msg;