各个数据库软件对于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不支持视图触发器；