各个数据库软件对于sql的支持
各个数据库软件对于sql的支持
最近正在学习数据库这门课程，虽然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）：
[sql]
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不支持视图触发器；
