对oracle当中子查询建表，merge操作，创建，修改，删除约束，创建使用触发器的复习练习

Sql代码

1. /** 对oracle当中子查询建表，merge操作，创建，修改，删除约束，创建使用触发器的复习练习**/  
2. /**    
3. 本例子的作用是熟悉使用oracle当中的触发器：有两张表  productinfo：产品表，productinfo_info：产品推荐表。  
4. 其中产品推荐表里面只有部分产品表里面的信息（当然不是所有的产品都推荐），这两张表的字段结构完全一样。  
5. 要实现：当管理者修改产品表里面的某个纪录的时候，如果该记录在产品推荐表里面也存在（根据productIdid判断），  
6. 则触发器自动修改产品推荐表里面的这条记录以达到跟产品表里面的记录保持一致的效果。  
7. **/  
8.   
9. --新建了一个产品信息表  
10. create table productinfo(  
11. productId varchar2(20) unique,  
12. productName varchar2(10) not null,  
13. productPrice varchar2(10) primary key,  
14. productAddress varchar2(10) );  
15.   
16. --练习使用sql修改约束和字段大小  
17. alter table productinfo modify   
18. productAddress varchar2(20) not null;  
19.   
20. --练习使用sql删除,修改，新增表里面的约束  
21. alter table productinfo  
22. --drop constraint sys_c009964;（删除的是productPrice为主键的约束）  
23. --modify productPrice  constraint product_price_not  not null;（给productPrice增加非空的约束）  
24. --add constraint productin_address_check check(length(productAddress)>5);（给地址增加check约束）  
25. add constraint productinfo_pk  primary key(productId);--增加productid为主键的约束  
26.   
27. --查看所有的约束名字，约束的状态（是否启用） ,约束的类型，约束是建立在哪个列上面的  
28. select c.constraint_name, c.status, c.constraint_type,n.COLUMN_NAME  
29. from user_constr  select * from productinfo_bak ;  
30. aints c, user_cons_columns n  
31. where c.CONSTRAINT_NAME = n.CONSTRAINT_NAME and c.TABLE_NAME= 'PRODUCTINFO';  

 

Sql代码

1. --修改字段的长度  
2. alter table productinfo_bak  
3. modify productName varchar2(20);  
4.   
5. --使用匿名程序块，在里面使用loop循环给表出入9条数据  
6. declare  
7. begin  
8.   for i in 1 .. 9 loop  
9.     insert into productinfo  
10.       (productid, productname, productprice, productaddress)  
11.     values  
12.       ('GD01001000'||i,'LG手机'||i,'手机价格'||i,'西安市南山区地址'||i);  
13.     commit;  
14.   end loop;  
15.   dbms_output.put_line('总共插入了'||sql%rowcount||'条记录.');  
16. end;  
17.   
18. --使用子查询建立表productinfo_bak  
19. create table productinfo_bak as  select * from productinfo  where 1<>1;  
20.   
21. --使用merge语句给表productinfo_bak里面插入一条p.productid ='GD010010001'的记录  
22. merge into productinfo_bak p_bak  
23. using productinfo p  
24. on (p_bak.productId = p.productId)  
25. when not matched then  
26.   insert  
27.     (p_bak.productid,  
28.      p_bak.productname,  
29.      p_bak.productprice,  
30.      p_bak.productaddress)  
31.   values  
32.     (p.productid, p.productname, p.productprice, p.productaddress) where p.productid = 'GD010010001';  
33.     
34. --创建触发器，行级触发器（for each row），在更新productinfo表的时候触发事件  
35. create or replace trigger tr_auto_update_productinfo  
36. after update on productinfo for each row  
37. begin  
38.   update productinfo_bak p_bak set   
39.   p_bak.productid    = :new.productid,    p_bak.productname   =  :new.productname,   
40.   p_bak.productprice = :new.productprice, p_bak.productaddress = :new.productaddress  
41.   where  p_bak.productid = :old.productid;/** 该where条件非常重要，意在只更新产品推荐表里有的数据**/  
42.   dbms_output.put_line('你在更新产品信息的时候，触发器自动更新了产品备份表里面的信息!');  
43. exception  
44.   when others then  
45.     dbms_output.put_line(sqlcode ||'  ,' ||sqlerrm);  
46. end;  
47.   
48. select * from productinfo;  
49. select * from productinfo_bak;  
50. update productinfo p set p.productname = '金鹏1' where p.productid = 'GD010010001';  
51. select * from productinfo_bak;  
52. --在本例子中我犯了一个致命的错误就是在触发器的定义当中使用了事物控制语句