create trigger t_insert_sc after insert on sc for each row new_credit number(8); begin --判断是否及格 if new.grade>=60 then --取该课程学分 select credit into new_credit from course where cno=new.cno; --更新该学生学分 update credit set sumcredit=sumcredit+new_credit where sno=new.sno; else if --更新不及格次数 update credit set notpass=notpass+1 where sno=new.sno; end if; end;
这个可以用触发器来实现. 建表:CREATE TABLE course ( cno VARCHAR2(10), credit NUMBER(4) );CREATE TABLE SC ( sno VARCHAR2(10), cno VARCHAR2(10), grade NUMBER(4) );CREATE TABLE credit ( sno VARCHAR2(10), sumcredit NUMBER, notpass NUMBER );创建存储过程:(已经成功编译) [code=SQL][/codeCREATE OR REPLACE TRIGGER TriggerOnSC AFTER INSERT ON SC FOR EACH ROW DECLARE newCredit number(8); tempCNO SC.cno%TYPE; BEGIN --判断是否及格 IF :NEW.grade >= 60 THEN --取该课程学分 tempCNO := :NEW.cno; SELECT credit INTO newCredit FROM course WHERE cno = tempCNO; --更新该学生学分 UPDATE credit SET sumcredit = sumcredit + newCredit WHERE sno = tempCNO; ELSE --更新不及格次数 UPDATE credit SET notpass = notpass + 1 WHERE sno = tempCNO; END IF; END TriggerOnSC;]
成功编译的代码:CREATE OR REPLACE TRIGGER TriggerOnSC AFTER INSERT ON SC FOR EACH ROW DECLARE newCredit number(8); tempCNO SC.cno%TYPE; BEGIN --判断是否及格 IF :NEW.grade >= 60 THEN --取该课程学分 tempCNO := :NEW.cno; SELECT credit INTO newCredit FROM course WHERE cno = tempCNO; --更新该学生学分 UPDATE credit SET sumcredit = sumcredit + newCredit WHERE sno = tempCNO; ELSE --更新不及格次数 UPDATE credit SET notpass = notpass + 1 WHERE sno = tempCNO; END IF; END TriggerOnSC;
after insert
on sc
for each row
new_credit number(8);
begin
--判断是否及格
if new.grade>=60 then
--取该课程学分
select credit into new_credit from course where cno=new.cno;
--更新该学生学分
update credit set sumcredit=sumcredit+new_credit where sno=new.sno;
else if
--更新不及格次数
update credit set notpass=notpass+1 where sno=new.sno;
end if;
end;
建表:CREATE TABLE course
(
cno VARCHAR2(10),
credit NUMBER(4)
);CREATE TABLE SC
(
sno VARCHAR2(10),
cno VARCHAR2(10),
grade NUMBER(4)
);CREATE TABLE credit
(
sno VARCHAR2(10),
sumcredit NUMBER,
notpass NUMBER
);创建存储过程:(已经成功编译)
[code=SQL][/codeCREATE OR REPLACE TRIGGER TriggerOnSC
AFTER INSERT ON SC
FOR EACH ROW
DECLARE
newCredit number(8);
tempCNO SC.cno%TYPE;
BEGIN
--判断是否及格
IF :NEW.grade >= 60 THEN
--取该课程学分
tempCNO := :NEW.cno;
SELECT credit INTO newCredit FROM course WHERE cno = tempCNO;
--更新该学生学分
UPDATE credit SET sumcredit = sumcredit + newCredit WHERE sno = tempCNO;
ELSE
--更新不及格次数
UPDATE credit SET notpass = notpass + 1 WHERE sno = tempCNO;
END IF;
END TriggerOnSC;]
AFTER INSERT ON SC
FOR EACH ROW
DECLARE
newCredit number(8);
tempCNO SC.cno%TYPE;
BEGIN
--判断是否及格
IF :NEW.grade >= 60 THEN
--取该课程学分
tempCNO := :NEW.cno;
SELECT credit INTO newCredit FROM course WHERE cno = tempCNO;
--更新该学生学分
UPDATE credit SET sumcredit = sumcredit + newCredit WHERE sno = tempCNO;
ELSE
--更新不及格次数
UPDATE credit SET notpass = notpass + 1 WHERE sno = tempCNO;
END IF;
END TriggerOnSC;