對oracle當中子查詢建表，merge操作，建立，修改，删除限制，建立使用觸發器的複習練習

Sql代碼

1. --建立了一個産品資訊表  
2. create table productinfo(  
3. productId varchar2(20) unique,  
4. productName varchar2(10) not null,  
5. productPrice varchar2(10) primary key,  
6. productAddress varchar2(10) );  
7. --練習使用sql修改限制和字段大小  
8. alter table productinfo modify   
9. productAddress varchar2(20) not null;  
10. --練習使用sql删除,修改，新增表裡面的限制  
11. alter table productinfo  
12. --drop constraint sys_c009964;（删除的是productPrice為主鍵的限制）  
13. --modify productPrice  constraint product_price_not  not null;（給productPrice增加非空的限制）  
14. --add constraint productin_address_check check(length(productAddress)>5);（給位址增加check限制）  
15. add constraint productinfo_pk  primary key(productId);--增加productid為主鍵的限制  
16. --檢視所有的限制名字，限制的狀态（是否啟用） ,限制的類型，限制是建立在哪個列上面的  
17. select c.constraint_name, c.status, c.constraint_type,n.COLUMN_NAME  
18. from user_constr  select * from productinfo_bak ;  
19. aints c, user_cons_columns n  
20. 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. --使用匿名程式塊，在裡面使用loop循環給表出入9條資料  
5. declare  
6. begin  
7.   for i in 1 .. 9 loop  
8.     insert into productinfo  
9.       (productid, productname, productprice, productaddress)  
10.     values  
11.       ('GD01001000'||i,'LG手機'||i,'手機價格'||i,'西安市南山區位址'||i);  
12.     commit;  
13.   end loop;  
14.   dbms_output.put_line('總共插入了'||sql%rowcount||'條記錄.');  
15. end;  
16. --使用子查詢建立表productinfo_bak  
17. create table productinfo_bak as  select * from productinfo  where 1<>1;  
18. --使用merge語句給表productinfo_bak裡面插入一條p.productid ='GD010010001'的記錄  
19. merge into productinfo_bak p_bak  
20. using productinfo p  
21. on (p_bak.productId = p.productId)  
22. when not matched then  
23.   insert  
24.     (p_bak.productid,  
25.      p_bak.productname,  
26.      p_bak.productprice,  
27.      p_bak.productaddress)  
28.   values  
29.     (p.productid, p.productname, p.productprice, p.productaddress) where p.productid = 'GD010010001';  
30. --建立觸發器，行級觸發器（for each row），在更新productinfo表的時候觸發事件  
31. create or replace trigger tr_auto_update_productinfo  
32. after update on productinfo for each row  
33. begin  
34.   update productinfo_bak p_bak set   
35.   p_bak.productid    = :new.productid,    p_bak.productname   =  :new.productname,   
36.   p_bak.productprice = :new.productprice, p_bak.productaddress = :new.productaddress  
37.   where  p_bak.productid = :old.productid;  
38.   dbms_output.put_line('你在更新産品資訊的時候，觸發器自動更新了産品備份表裡面的資訊!');  
39. exception  
40.   when others then  
41.     dbms_output.put_line(sqlcode ||'  ,' ||sqlerrm);  
42. end;  
43. select * from productinfo;  
44. select * from productinfo_bak;  
45. update productinfo p set p.productname = '金鵬1' where p.productid = 'GD010010001';  
46. select * from productinfo_bak;  
47. --在本例子中我犯了一個緻命的錯誤就是在觸發器的定義當中使用了事物控制語句