sql编程小结,游标和触发器

--创立insert类型的触发器

相比较mysql5.1手册,对最近学的sql编制程序实行计算,主要涉嫌触发器、存款和储蓄进程、权限管理、主从抽离等,权当一得之见,高手请略过。

一、游标

create trigger tgr_product_insert   --创设触发器
    on product --所针没有错表
    for insert --触发器类型
    
as

意气风发、触发器 通俗的说就是在钦赐的数码表增加和删除改的前或后触发实践一定的sql语句,数据表为引用恒久性表。无法将触发程序与TEMPORA本田CR-VY表或视图关联起来。能够从多少个方面精晓触发器:

定义:存储在MySQL服务器上的数据库查询,是豆蔻梢头种被select语句检索出来的结果集。

    --表明变量
    declare @Pid int,@Wid int
    
    --获取丰硕商品时的主键(自增列) (上面三种艺术都行卡塔 尔(阿拉伯语:قطر‎
    --set @Pid=@@IDENTITY
    select @Pid=Product_Id from inserted   --(inserted 在创制触发器时自带的有时表)
    
    --证明游标 并把货仓编号放到游标中
    declare mycursor  CURSOR for select WareHose_Id from WareHose
    
    --打开游标
    open mycursor
    
    --循环游标
    fetch next from mycursor into @Wid
    
    --决断游标是不是获得成功
    while(@@FETCH_STATUS=0)
        begin
            insert into Stock(Stock_num,Stock_warn,WareHose_Id,Product_Id) values(0,5,@Wid,@Pid)
            fetch next from mycursor into @Wid
        end
        
    --关闭游标
    close mycursor
    
    --释放游标
    deallocate mycursor       

---监视地点 table
---监视事件 insert/update/delete
---触发时间 after/before
---触发事件 insert/update/delete

作用:谋福在找寻出来的结果聚焦发展或倒退黄金年代行或多行。

创设语法:

游标主要用于人机联作式应用;MySQL中的游标只好用来存款和储蓄进度(和函数卡塔尔。

create trigger 触发器名称
after/before
insert/update/delete
on 表名
for each row
begin
sql1;
..
sqlN;
end

1、成立游标

第一申明甘休符

游标使用declare语句创设;declare命名游标,并定义响应的select语句,依据须求带where和其它子句;比如:

delimiter $ ---默许是;号最终,可是sql语句以事务厅结尾后实践触发器会报语法错误,所以要超前注明截至符为分号以外的

create procedure processorders()

---成立测量试验须要的两张表

begin

CREATE TABLE `goods` (

       declare ordernumbers CURSOR

`gid` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`num` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8$

       for

CREATE TABLE `orders` (

       select order_num from orders;

`oid` int(11) DEFAULT NULL,
`gid` int(11) DEFAULT NULL,
`much` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8$
安插测量试验数据

end;

INSERT INTO goods VALUES(1,'BMW',20),(2,'mini',18),(3,'ford',50)$

那么些蕴藏进度中,declare定义和命名了游标ordernumbers,存款和储蓄进程管理完毕后,游标消失(因为它局限于积攒进程内卡塔尔国。

---创造触发器t2,监视订单表,当订单表扩大订单时,商品表就得收缩相应的物品数量;

 

delimiter $
create trigger t2
after
insert --不允许同一张表同临时刻有多少个相近的动作 
on orders
for each row
begin
update goods set num=num-new.much where gid =new.gid;-- new.much便是增创的订单表row的值;
end$

2、展开和倒闭游标

---调用

游标使用open cursor语句来展开,举例:

INSERT INTO orders VALUES(123,1,2)$

open ordernumbers;

再次select * from goods$查占星比前后数据

在拍卖open语句时进行查询,存款和储蓄检索出的多少以供浏览和滚动;

-- 创造触发器 t3,监视订单表,当订单被删掉时,商品表扩充对应数量;

游标管理到位时,使用close语句关闭,比如:

create trigger t3
after
delete
on orders
for each row
begin
update goods set num=num old.much where gid =old.gid;
end$

close ordernumbers;

-- 创立触发器 t5,监视订单表,在订单扩张前决断是不是高于仓库储存,假设过量仓库储存量,就让其相当仓库储存量 分歧before和after

close释放游标使用的拥有内部内部存款和储蓄器和能源,由此在每一个游标不在需要时都应有关闭。

create trigger t5
before
insert
on orders

PS:多个游标关闭后,若无再一次展开,则不能够使用;但若是该游标被声称过,则没有需求再行申明,用open语句张开使用就能够。

for each row
begin

若是不醒目游标是或不是关闭,MySQL将会在到达end语句时自动关闭该游标;举例:

declare rnum int;

create procedure processorders()

select num into rnum from goods where gid=new.gid;

begin

if new.much>rnum then
set new.much =rnum;
end if;

       -- declare the cursor

update goods set num=num-new.much where gid =new.gid;
end$

       declare ordernumbers cursor

 

       for

---删除触发器

       select order_num from orders;

DROP TRIGGER [schema_name.]trigger_name

       -- open the cursor

放弃触发程序。方案名称(schema_name卡塔尔国是可选的。若是轻松了schema(方案),将从此未来时此刻方案中放弃触发程序。

       open ordernumbers;

日常都以直接删除触发器名的

       -- close the cursor

6.for each row绘身绘色介绍
行级触发器
语句级触发器

       close ordernumbers;

7.囤积进程
一定于多少个尚未重返值的"函数"
create procedure procedureName()
begin
--sql 语句
end$

end;

 

以此蕴藏进度注明、张开、关闭一个游标,但对寻觅出的多寡没其余操作。

8.引进变量与调控结构
--在仓库储存进程中 用declare注脚变量
--格式 declare 变量名 变量类型[default默认值]

 

create procedure p3()
begin
declare age int default 18;
set age =age 20;
select concat('20年后',age);
end$

3、使用游标数据

create procedure p4()
begin
declare age int default 18;
if age >=18 then
select '已成年';
else
select '未成年';
end if;
end $

游标被展开后,使用fetch语句分别走访它每意气风发行;fetch钦定检索什么数据(所需的列卡塔 尔(阿拉伯语:قطر‎,检索的数量存款和储蓄在怎么地点,还上前挪动游标中的内部行指针,使下一条fetch语句检索下大器晚成行(不另行读取卡塔尔;举例:

9.仓库储存进度的参数字传送递

create procedure processorders()

给存款和储蓄进程传参
语法 [in/out/inout] 参数名 参数类型
create procedure p5(width int,height int)
begin
select concat('你的面积是',width * height) as area;
if width >height then
select '你挺胖';
elseif width <height then
select '你挺瘦';
else
select '你挺方';
end if;
end$

begin

10.历程中采取循环结构
--求1-100之和
create procedure p6()
begin
declare sum int default 0;
declare num int default 0;

       --declare local variables

while num <=100 do --while num <100 do set num :=num 1;
set sum =sum num;
set num=num 1;
end while;
select sum;
end$

       declare o int;

--in型参数 input type
create procedure p7(in n int)
begin
declare sum int default 0;
declare num int default 0;

       --declare the cursor

while num <n do
set num=num 1;
set sum =sum num;

       declare ordernumbers cursor for select order_num from orders;

end while;
select sum;
end$

       --open the cursor

11.何为输出型参数
delimiter $
create procedure p8(in n int,out total int)
begin

       open ordernumbers;

declare num int default 0;
set total :=0;
while num <n do
set num :=num 1;
set total :=total num;
end while;

       --get order number

end$

       fetch ordernumbers into o;

create procedure p9(inout age int)
begin
set age :=age 20;
end$

       --close the cursor

set @nowage=12$

       close ordernumbers;

call p9(@nowage)$

end;

select @nowage$

那条语句中fetch用来寻找当前进的order_num列(自动从第大器晚成行起头卡塔 尔(阿拉伯语:قطر‎到三个名称叫o的片段声明变量中;对搜索出的多少不做别的管理。

12.case 构造的应用
create procedure pten()
begin
declare pos int default 0;

再看一个事例,循环检索数据,从第大器晚成行到结尾意气风发行:

set pos :=floor(5*rand());
case pos
when 1 then select 'still flying';
when 2 then select 'fall in sea';
when 3 then select 'in the island';
else select 'unkonw param';
end case;
end$

create procedure processorders()  --创造存款和储蓄过程

13.repeat循环构造

begin

create procedure p11()
begin
declare i int default 0;
repeat
select i;
set i :=i 1;
until i>10 end repeat;
end$

       --declare local variables

create procedure p12()
begin
declare i int default 0;
declare sum int default 0;
repeat
set i :=i 1;
set sum :=sum i;

       declare done boolean default 0;

until i>=100 end repeat;
select sum;
end$

       declare 0 int;

14.游标的概念
--cursor 游标 游动的表明
--1条sql对应N条结果集的能源 抽出财富的接口/句柄 正是游标
--沿着游标 能够二遍抽取1行

       --declare the cursor

--declare 声明:declare 游标名 cursor for select statement
--open 打开;open 游标名
--fetch 取值;fetch 游标名 into var1,var2...
--close 关闭;close 游标名;

       declare ordernumbers cursor

create procedure p13()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

       for

declare getgoods cursor for select gid, num,name from goods;
open getgoods;

       select order_num from orders;  --结果集

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;

       --declare continue handler

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;

       declare continue handler for sqlstate '0二〇〇四' set done=1;  --在这里间,done被设置为终结时为真

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;

       --open the cursor

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

       open ordernumbers;

--02000

       --loop through all rows

15 游标循环
--way1
create procedure p14()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

       repeat

declare cnt int default 0;
declare i int default 0;

       --get order number

declare getgoods cursor for select gid, num,name from goods;
select count(*) into cnt from goods;
open getgoods;

       fetch ordernumbers into0;

repeat
set i :=i 1;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until i>=cnt end repeat;
close getgoods;
end$

       --end of loop

call p14()$ call p14$
16.declare 拍卖标准

       until done end repeat;

--越界值

       --close the cursor

create procedure p15()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

       close ordernumbers;

declare rhave int default 1;

end;

declare getgoods cursor for select gid, num,name from goods;
declare continue handler for NOT FOUND set rhave :=0;

以那件事例与前三个事例同样,使用fetch检索当前order_num到注脚的名叫0的变量中。但差距在于:那几个事例中fetch是在repeat内,因而它往往实行到done为真(由until done end repeat;规定卡塔尔国。

open getgoods;

为了使它起功能,用叁个default 0(假、不截止卡塔尔定义变量done;在讲话(declare continue handler for sqlstate '0贰零零肆' set done=1;卡塔尔中done被设置为真。

repeat

PS:用declare语句定义的部分变量必需在概念任性游标或句柄早先定义,而句柄必需在游标之后定义。

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until rhave=0 end repeat;
close getgoods;
end$

    MySQL还协助循环语句,它可用来重新实施代码,直到使用leave语句手动退出截止;经常repeat语句的语法使它更适合于对游标实行巡回。

17.对比continue与exit的区别

 

create procedure p16()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

4、使用游标的多少个注意事项

declare rhave int default 1;

①在使用游标前,必需表明(定义卡塔尔它;那么些历程实际上并未有寻找数据,只是概念要使用的select语句;

declare getgoods cursor for select gid, num,name from goods;
declare exit handler for NOT FOUND set rhave :=0;

②少年老成旦申明,则必得打开游标以供役使(那个历程用后面定义的select语句把数量实际上检索出来卡塔尔国;

open getgoods;

③对于填有数据的游标,依照需求收取(检索卡塔 尔(英语:State of Qatar)各行;

repeat

④说尽游标时,必得关闭游标。

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until rhave=0 end repeat;
close getgoods;
end$

 

18.游标循环读取的准确性逻辑
--declare getgoods cursor for select gid, num,name from goods; 不自然收取数据
create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

二、触发器

declare rhave int default 1;

MySQL语句在急需时被实行,存款和储蓄进度也是如此,尽管希望某条语句(或一些语句卡塔 尔(英语:State of Qatar)在事变发生时自动实践,那就须要接纳触发器。

declare getgoods cursor for select gid, num,name from goods;
declare exit handler for NOT FOUND set rhave :=0;

触发器是MySQL响应以下跋扈语句而机关推行的一条MySQL语句(或放在begin和end语句之间的生机勃勃组语句卡塔 尔(阿拉伯语:قطر‎:

open getgoods;
fetch getgoods into row_gid,row_num,row_name;

delete;

repeat
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;

insert;

until rhave=0 end repeat;
close getgoods;
end$

update;

19.mysql权限检查原理
[用户]<------>[服务器]
分成八个级次
1:你有木有权力连接上来
2:你有未有权力推行操作(CU科雷傲D卡塔 尔(阿拉伯语:قطر‎
对于1:服务器如何剖断客户有未有权力连接上来
依据3个参数
您从哪来?host
你是谁?user
您的密码是微微?password

别的MySQL语句不援助触发器。。。

客户的那3个音讯存款和储蓄在mysql库下的user表内

1、创制触发器

--校订host域 使得ip能够三番五次
mysql>update user set password=password('root') where user='';
mysql>flush privileges;

急需以下4条消息:

20.全局授权与收回(从user全局表上)
[user]<------->[db]<------>[table-prv]
--新扩大三个客商
grant [权限1,权限2,权限3....] on *.* to user@'host' identified by 'password';
常用权限有all,select,create,drop,insert,delete,update...

①唯风流倜傥的触发器名;

grant all on *.* to leo@'192.168.1.%' identified by '123456';

②触发器关联的表;

--收回权限
revoke all on *.* from leo@'192.168.1.%';
--左侧第七个*所有库
--左侧第叁个*所有表
flush privileges;

③触发器应该响应的运动(delete、insert或update卡塔尔;

21.库及表等第授权与收回(db品级卡塔尔

④触发器几时实行(管理从前或未来卡塔尔国;

--针对某些库做授权

PS:MySQL中,触发器名必得在各样表中唯后生可畏,但不约束各种数据库中天下第一,即:同生龙活虎数据库中七个表能够具备同样名字的触发器(其余的DBMS中不被允许卡塔尔。

grant all on trdb.* to leo@'192.168.1.%';

触发器使用create teigger语句创立,上边是二个简短的例子:

--收回权限
revoke all on trdb.* from leo@'192.168.1.%';

create trigger newproduct after insert on products

--针对表品级做授权
grant select,insert,update on trdb.goods to leo@'192.168.1.%';

for each row select 'product added';

--mysql的权限决定能够确切到列看手册

此地开创了二个newproduct的新触发器,给出了after insert,所以此触发器在insert语句成功履行后实行;当中还点名了for each row,由此代码对每一个插入行推行。

 

PS:唯有表才帮助触发器,视图不扶植(有的时候表也不扶助卡塔 尔(阿拉伯语:قطر‎。

    触发器按每一个表每一个事件每回的概念,每一种表每种事件每一次只允许一个触发器;因而各种表最多补助6个触发器(每条insert、update和delete的事先和后来卡塔 尔(英语:State of Qatar),单一触发器无法与两个事件或四个表关联。

    假使before触发器失利,则MySQL将不进行需要的操作;别的,要是before触发器或语句小编失利,MySQL将不实行after触发器(如若有的话卡塔尔国。

 

2、删除触发器

剔除触发器使用drop trigger语句,举例:

drop trigger newproduct;

PS:触发器不能够更新或掩没;为了修改三个触发器,必需先删后建。

 

3、使用触发器

insert触发器

采取insert触发器,要求知道以下三点:

在insert触发器代码内,可引用一个名称为new的设想表,采访被插入的行;

在before insert触发器内,new中的值也足以被更新(允许改良被插入的值卡塔 尔(英语:State of Qatar);

对此auto increment列,new在insert试行在此以前包涵0,在insert实行之后包蕴新的自动生成值;

事例如下:

create trigger neworder after insert on orders

for each row select new.order_num;

此代码创立三个名字为neworder的触发器,根据after insert on order实行;在插入一条新的数码orders表时,MySQL生成三个新订单号并保留到order_num中;

触发器从new.order_num取那几个值并再次来到它;此触发器必须比照after insert实行,因为在before insert语句实践早先,新order_num还未有变化。

PS:常常来说将before用于数据证实和卫生(指标是确定保障插入表中的数码确实是内需的数额卡塔 尔(英语:State of Qatar)。这些准绳也适用于update触发器。

delete触发器

利用delete触发器,要求通晓以下两点:

在delete触发器代码内,能够引用叁个名叫old的虚构表,访谈被删去的行;

old中的值全是只读的,不能够更新;

下边是叁个用到old保存将在被去除的行到一个存档表中:

 

create trigger deleteorder before delete on orders
for each row
begin
insert into archive_orders(order_num,order_date,cust_id)

values(OLD_order_num, OLD_order_date, OLD_cust_id);
end;

此代码在任意orders表中的数据被去除前施行此触发器;它选拔一条insert语句将old中的值(要被删去的数码卡塔尔国保存到三个名字为archive_orders的存档表中;

地点的例证中,使用begin end块的好处是触发器能包容多条SQL语句。

PS:行使before delete触发器的优点:假若出于一些原因,数据不可能存档,delete本人将被甩掉。

update触发器

利用update触发器,须求精晓以下三点:

在update触发器代码内,能够援引叁个名称为old的设想表访谈之前(update语句前卡塔 尔(英语:State of Qatar)的值,引用多少个名字为new的设想表访问新更新的值;

在before update触发器中,new中的值可能也被更新(允许校订将在用于update语句中的值卡塔尔国;

old中的值全部是只读的,不可能立异;

上面包车型大巴例证,保险了州名称缩写总是大写:

create trigger updateevendor before update on vendors

for each row set.vend_state = upper(new.vend_state);

本条例子中,每一次换代一个行时,new.vend_state中的值(将用来更新表行的值卡塔尔国都用upper(new.vend_state)替换。

 

△关于触发器一些必须要知道的学识:

创立触发器大概供给独特的平安访问权限,但触发器的施行是机关的;固然insert、update、delete语句能够进行,则附和触发器也能实践;

应当用触发器来保险数据的风流罗曼蒂克致性(大小写、格式等卡塔尔国;有一点点在于它总是实行这种管理,并且是晶莹剔透的开展,与客商机应用非亲非故;

触发器的风度翩翩种非常常有意义的使用是创制审计追踪。使用触发器,把更换记录到另多个表非常轻便;

MySQL触发器不扶助call语句,即不能够从触发器内调用存储进程,所需的囤积进度代码必要复制到触发器内。

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:sql编程小结,游标和触发器

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。