Oracle触发器过程等的实验.docx

实验四：PL/SQL程序设计
一、实验目的掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程 序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。
熟悉和掌握PL/SQL中关于存储过程、函数、包和触Cid=:;updateCset
Smoney=v_Smoney+:,SPsum=v_SPsum+(：*(:-: )) where =:; clsif updating thenselect Mname,Mprice,Mcost into :,:,: from Mlist where =:;::=nvl(:,:)*nvI(:,:);if : is not null then
updateCset
Smoney=Smoney-:,SPsum=SPsum-:*(:-: t) where =:;updateCset
Smoney=Smoney+:,SPsum=SPsum+:*(:-:new.
Mcost) where =:;elseupdateCset
Smoney=Smoney-:+:,SPsum=SPsum-:*(: price-:)+:*(:-:) where =:;end if; elseupdateCset
Smoney=Smoney-:,SPsum=SPsum-:*(:-: t) where =:;end if;end DML_Clist;测试触发器：
Insert 测试；select Smoney,SPsum from C where =6;
SMONEY into clist (cid,sid,mid,cqty)values(6,5,7,1);select cl.*,Smoney,SPsum from clist cl,C where =6 and sid=5 and =6;
I CID | SID | IIP | MNAME | CQTY|MPRICE[CMOIIEY ISMOMH | SPSUM Ibl 11657 测试:
select cl.*,Smoney,SPsum from clist cl,C where =4 and sid=3 and =4;update clist set mid=6,cqty=5 where cid=4 and sid=3;select cl.*,Smoney,SPsum from clist cl,C where =4 and sid=3 and =4;
CID |SID MID |MNAME ]CQTY IttPRICE |MCOST CMONEY|SMONEY|SPSUM |
►)
1
4313 辣白菜 …
CID |SID
MID
MNAME
CQTY
MPRICE
MCOST |CMONEY (SHONET|SPSUM |
►
1
4F
3
6号
5


Delete 测试：
select* from c where cid=6;
CID | DID ISTARTTSME| ENDTIME[SMOHEY |SPSVII |>| 11613 2014/4/26 12:05:40 ▼ 2014/4/26 13:30:20 ▼ from clist where cid=6 and sid=5;select* from c where cid=6;
CID DID JST ARTTIME
ENDTIME