最近正在学习数据库这门课程,虽然SQL有有统一的标准,但是各个数据库软件对于sql支持是不一样的,上机遇到各种各样的问题,就记录在这个帖子上吧;
1,mysql是不支持对视图创建触发器的;
2,oracle只支持了级联删除on delete cascade却不支持级联更新on update cascade;
3,mysql中的条件控制语句比如IF语句后是可以出现查询语句selec的,但是oracle中是不可以的,只能实现begin开始前声明变量,然后再begin语句后把查询出来的的结果赋给变量,在oracle中条件控制语句是用when 的
4,mysql中新插入,修改后的行用new来表示,而删除或者更新前的行用old来表示,而在oracle中则需要在nrow前面加上:号,还要在开头加上一句referencing new as nrow;
但是在when后面的nrow是不用加上:号的;
下面是今天晚上的几个例子(数据库使用的是oracle):
Create database student; create table Students (SNO varchar(15), SNAME varchar(20) not null, SEX varchar(7), BDATE date, HEIGHT int, DEPARTMENT varchar(20), primary key (SNO)); create table Courses (CNO varchar(8), CNAME varchar(20) not null, LHOUR int, CREDIT int, SEMESTER varchar(6), primary key (CNO)); create table SC (SNO varchar(15), CNO varchar(8), GRADE smallint, primary key (SNO,CNO), foreign key (SNO) references Students(SNO) on delete cascade, foreign key (CNO) references Courses(CNO) on delete cascade); create or replace trigger upd_creadits after insert on sc referencing new as nrow for each row declare credits_count int; credits_number int; begin select count(*) into credits_count from credits where credits.sno=:nrow.sno; select credit into credits_number from courses where courses.cno=:nrow.cno; if :nrow.grade>=60 then if credits_count<>0 then update credits set SumCredit=SumCredit+credits_number where sno=:nrow.sno; else insert into credits values(:nrow.sno,credits_number,0); end if; else if credits_count<>0 then update credits set nopass=1+nopass where sno=:nrow.sno; else insert into credits values(:nrow.sno,0,1); end if; end if; end; /
create or replace trigger Upd_StuView instead of insert on Student_Grade
referencing new as nrow for each row declare student_num int; course_num int; xuehao varchar(15); kechenghao varchar(8); begin begin select count(*) into student_num from Students where Students.sname=:nrow.sname; exception when no_data_found then dbms_output.put_line('不存在这个学生或者这门课程!'); end; begin select count(*) into course_num from courses where courses.cname=:nrow.cname; exception when no_data_found then dbms_output.put_line('不存在这个学生或者这门课程!'); end; select sno into xuehao from Students where Students.sname=:nrow.sname; select cno into kechenghao from courses where courses.cname=:nrow.cname; if student_num<>0 and course_num<>0 then insert into sc values(xuehao,kechenghao,:nrow.grade); else dbms_output.put_line('不存在这个学生或者这门课程!'); end if; end; /
下面是第一个触发器upa_creadits的mysql版本:delimiter | create trigger upd_creadits after insert on sc for each row begin if new.grade>=60 then if exists(select * from credits where credits.sno=new.sno) then update credits set SumCredit=SumCredit+(select credit from courses where courses.cno=new.cno) where sno=new.sno; else insert into credits values(new.sno,(select credit from courses where courses.cno=new.cno),0); end if; else if exists(select * from credits where credits.sno=new.sno) then update credits set nopass=1+nopass where sno=new.sno; else insert into credits values(new.sno,0,1); end if; end if; end|
mysql不支持视图触发器;