存储过程
create or replace procedure a(j out int)
as
begin
j:=0;
for i in 1..100 loop
j:=j+i;
end loop;
end;
set serveroutput on
declare
suma int;
begin
a(suma);
dbms_output.put_line(suma);
end;
事务
事务是单个的工作单元。如果某一事务成功,则在该事务中进行的所有数据更改均会提交,成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚,则所有数据更改均被清除。
oracle
sqlserver
触发器
触发器是一种特殊类型的存储过程,不由用户直接调用。创建触发器时会对其进行定义,以便在对特定表或列作特定类型的数据修改时执行。
drop table super;
drop table sub;
create table super(bh int primary key,age int);
insert into super values(1,22);
insert into super values(2,23);
create table sub(id int primary key,sm char(30),bh int ,foreign key(bh) references super(bh));
insert into sub values(1,'os',2);
insert into sub values(2,'db',2);
insert into sub values(3,'db',1);
insert into sub values(4,'linux',1);
create or replace trigger tri_del after delete on super
for each row
begin
delete from sub where bh=:old.bh;
end;
--drop object
drop trigger mytri;
drop table student;
drop table borrowrecord;
--create table
create table student(studentid int,name char(20));
insert into student values (1,'abc');
insert into student values (1,'abc');
insert into student values (2,'abc');
insert into student values (3,'abc');
create table borrowrecord (borrowid int,studentid int);
insert into borrowrecord values (1,1);
insert into borrowrecord values (2,1);
insert into borrowrecord values (3,2);
insert into borrowrecord values (4,3);
--create trigger
■SqlServer
create trigger mytri on student for delete
as
delete borrowrecord from borrowrecord br,deleted d where br.studentid=d.studentid
■Oracle
create trigger mytri after delete on student
for each row
begin
delete from borrowrecord br where br.studentid=:old.studentid;
end;
■Mysql5.0
delimiter |
create trigger mytri after delete on student
for each row
begin
delete from borrowrecord where borrowrecord.studentid=old.studentid;
end;
|
或
mysql> delimiter | 设定分隔符
mysql> create trigger beforeinsertuserinfo
-> before insert on userinfo
-> for each row
-> begin
-> insert into userinfolog values(now(),CONCAT(new.userid,new.username));
-> end;
-> |
mysql> delimiter ; 恢复;分隔符
mysql> show triggers;
或
create trigger mytri after delete on student
for each row
delete from borrowrecord where borrowrecord.studentid=old.studentid;
--test trigger
select * from student;
select * from borrowrecord;
delete from student where studentid=1;
select * from student;
select * from borrowrecord;
--显示触发器
mysql: SELECT trigger_name,trigger_schema FROM information_schema.TRIGGERS T; show triggers
oracle:select trigger_name from user_triggers
sqlserver:sp_helptrigger tablename
表序列,触发器
关联触发器
主外键关联
删除修改触发器
修改
删除