臨回家了,把這兩天搞的oracle的點東西,放到博客上,主要是觸發器的使用,沒有涉及到具體復雜的業務,但是都是比較實際的業務需求。實際中能夠用的上,主要是用于審計,用戶實際業務需求(友好性的考慮),與view等配合使用等。直接上code吧,在pl/sql中執行相應的模塊就能得到相應的結果,注釋和清晰,主要是看書一步步搞的。有的自己備用方便。如果有用,大家也可以看看。
1 -- 好的創建表的方法 2 DECLARE 3 TB NUMBER ; 4 BEGIN 5 SELECT COUNT ( * ) INTO TB FROM TABS WHERE TABLE_NAME = ' FRAME ' ; 6 IF TB <> 0 THEN 7 EXECUTE IMMEDIATE ' DROP TABLE frame ' ; 8 EXECUTE IMMEDIATE ' 9 CREATE TABLE frame 10 ( 11 bowler_id number, 12 game_id number, 13 frame_id number, 14 strike varchar2(1) default '' N '' , 15 spare varchar2(1) default '' N '' , 16 score number, 17 constraint frame_pk 18 primary key (bowler_id,game_id,frame_id) 19 ) ' ; 20 DBMS_OUTPUT.PUT_LINE( ' 存在該表已刪除,并創建新的表 ' ); 21 ELSE 22 EXECUTE IMMEDIATE ' CREATE TABLE frame 23 ( 24 bowler_id number, 25 game_id number, 26 frame_id number, 27 strike varchar2(1) default '' N '' , 28 spare varchar2(1) default '' N '' , 29 score number, 30 constraint frame_pk 31 primary key (bowler_id,game_id,frame_id) 32 ) ' ; 33 DBMS_OUTPUT.PUT_LINE( ' 不存在該表,已創建新表 ' ); 34 END IF ; 35 END ; 36 37 -- 創建審計表 38 DECLARE 39 TB NUMBER ; 40 BEGIN 41 SELECT COUNT ( * ) INTO TB FROM TABS WHERE TABLE_NAME = ' FRAME_AUDIT ' ; 42 IF TB <> 0 THEN 43 EXECUTE IMMEDIATE ' DROP TABLE frame_audit ' ; 44 EXECUTE IMMEDIATE ' 45 CREATE TABLE frame_audit 46 ( 47 bowler_id NUMBER, 48 game_id NUMBER, 49 frame_id NUMBER, 50 old_strike VARCHAR2(1), 51 new_strike VARCHAR2(1), 52 53 old_spare VARCHAR2(1), 54 new_spare VARCHAR2(1), 55 56 old_score number, 57 new_score number, 58 59 change_date DATE, 60 operation VARCHAR2(6) 61 ) ' ; 62 DBMS_OUTPUT.PUT_LINE( ' 存在該表已刪除,并創建新的表 ' ); 63 ELSE 64 EXECUTE IMMEDIATE ' CREATE TABLE frame_audit 65 ( 66 bowler_id NUMBER, 67 game_id NUMBER, 68 frame_id NUMBER, 69 old_strike VARCHAR2(1), 70 new_strike VARCHAR2(1), 71 72 old_spare VARCHAR2(1), 73 new_spare VARCHAR2(1), 74 75 old_score number, 76 new_score number, 77 78 change_date DATE, 79 operation VARCHAR2(6) 80 ) ' ; 81 DBMS_OUTPUT.PUT_LINE( ' 不存在該表,已創建新表 ' ); 82 END IF ; 83 END ; 84 85 86 -- 創建觸發器 87 CREATE OR REPLACE TRIGGER audit_frames 88 AFTER INSERT OR UPDATE OR DELETE ON frame 89 FOR EACH ROW 90 BEGIN 91 IF inserting THEN 92 INSERT INTO frame_audit(bowler_id,game_id,frame_id, 93 new_strike,new_spare, 94 new_score,change_date,operation) 95 VALUES (:new.bowler_id,:new.game_id,:new.frame_id,:new.strike,:new.spare,:new.score,SYSDATE, ' insert ' ); 96 97 ELSIF updating THEN 98 INSERT INTO frame_audit(bowler_id,game_id,frame_id, 99 old_strike,new_strike,old_spare,new_spare, 100 old_score,new_score,change_date,operation) 101 VALUES (:new.bowler_id,:new.game_id,:new.frame_id,:old.strike,:new.strike,:old.spare,:new.spare,:old.score,:new.score,SYSDATE, ' update ' ); 102 103 ELSIF deleting THEN 104 INSERT INTO frame_audit(bowler_id,game_id,frame_id, 105 old_strike,old_spare, 106 old_score,change_date,operation) 107 VALUES (:old.bowler_id,:old.game_id,:old.frame_id,:old.strike,:old.spare,:old.score,SYSDATE, ' insert ' ); 108 109 END IF ; 110 END audit_frames; 111 112 -- 在創建之后,后來使用的時候報了一個觸發器無效且未通過認證的錯誤 113 -- 解決辦法是找到觸發器-》右鍵查看-》找到相應的錯誤(insert->inserting).問題解決。 114 115 116 INSERT INTO frame (bowler_id,game_id,frame_id,strike) 117 VALUES ( 1 , 2 , 3 , ' y ' ); 118 119 120 SELECT * FROM frame; 121 122 UPDATE frame SET strike = ' N ' , 123 spare = ' y ' 124 WHERE bowler_id = 1 125 AND game_id = 2 126 AND frame_id = 3 ; 127 128 DELETE frame WHERE bowler_id = 1 ; 129 130 COMMIT ; 131 SELECT * FROM frame; 132 SELECT * FROM frame_audit; 133 134 /* 創建更加有針對性的觸發器:只是在修改記錄的時候觸發 */ 135 /* ************Begin**************** */ 136 CREATE OR REPLACE TRIGGER audit_update 137 AFTER UPDATE OF strike,spare ,score ON frame 138 REFERENCING OLD AS prior_to_cheat NEW AS after_cheat 139 FOR EACH ROW 140 WHEN ( 141 prior_to_cheat.strike != after_cheat.strike 142 OR prior_to_cheat.spare != after_cheat.spare 143 OR prior_to_cheat.score != after_cheat.score) 144 BEGIN 145 INSERT INTO frame_audit(bowler_id,game_id,frame_id, 146 old_strike,new_strike,old_spare,new_spare, 147 old_score,new_score,change_date,operation) 148 VALUES (:prior_to_cheat.bowler_id,:prior_to_cheat.game_id,:prior_to_cheat.frame_id,:after_cheat.strike,:prior_to_cheat.strike,:after_cheat.spare,:prior_to_cheat.spare,:after_cheat.score,:prior_to_cheat.score,SYSDATE, ' update ' ); 149 END ; 150 -- 測試語句 151 INSERT INTO frame (bowler_id,game_id,frame_id,strike) 152 VALUES ( 1 , 1 , 1 , ' y ' ); 153 UPDATE frame SET strike = strike; 154 SELECT * FROM frame; 155 SELECT * FROM frame_audit; 156 /* *******************END************************* */ 157 158 /* ********************************* */ 159 -- 增加有好性,在擊球全中和2次全中的選手加10分 160 /* **************BEGIN**************** */ 161 162 CREATE OR REPLACE TRIGGER set_score 163 BEFORE INSERT ON frame 164 FOR EACH ROW 165 WHEN (NEW.score IS NOT NULL ) 166 BEGIN 167 IF :New.strike = ' y ' OR :new.spare = ' y ' 168 THEN 169 :new.score : = :new.score + 10 ; 170 END IF ; 171 END ; 172 173 -- 測試語句 174 INSERT INTO frame (bowler_id,game_id,frame_id,strike,score) 175 VALUES ( 2 , 2 , 1 , ' y ' , 10 ); 176 177 INSERT INTO frame (bowler_id,game_id,frame_id,strike,score) 178 VALUES ( 2 , 2 , 2 , ' y ' , 9 ); 179 180 INSERT INTO frame (bowler_id,game_id,frame_id,strike,score) 181 VALUES ( 3 , 1 , 1 , ' n ' , 9 ); 182 SELECT * FROM frame; 183 SELECT * FROM frame_audit; 184 /* ****************分數校驗********** */ 185 CREATE OR REPLACE TRIGGER validate_score 186 AFTER INSERT OR UPDATE ON frame 187 FOR EACH ROW 188 BEGIN 189 IF :New.strike = ' y ' AND :new.score < 10 190 THEN 191 raise_application_error( - 20001 , ' ERROR:score for strike must be >=10 ' ); 192 ELSIF :new.spare = ' y ' AND :new.score < 10 193 THEN 194 raise_application_error( - 20001 , ' ERROR:Score for spare must be >=10 ' ); 195 ELSIF :new.strike = ' y ' AND :new.spare = ' y ' 196 THEN 197 raise_application_error( - 20001 , ' ERROR: Cannot Enter Spare and strike ' ); 198 199 END IF ; 200 END ; 201 -- 測試語句 202 INSERT INTO frame (bowler_id,game_id,frame_id,strike,score) 203 VALUES ( 5 , 1 , 1 , ' y ' , 6 ); 204 205 SELECT * FROM frame; 206 SELECT * FROM frame_audit; 207 /* *************END******************* */ 208 209 /* ********************************* */ 210 /* ******************************** */ 211 /* *********一個簡單的快遞系統***************** */ 212 /* ******************************** */ 213 /* ****************************** */ 214 -- 如果表存在刪除表讓后再創建,不存在直接創建表 215 DECLARE 216 I INTEGER ; 217 BEGIN 218 SELECT COUNT ( * ) INTO I FROM USER_TABLES WHERE TABLE_NAME = ' DELIVERY ' ; -- 注意表名大寫 219 IF I > 0 THEN 220 DBMS_OUTPUT.PUT_LINE( ' 該表已存在!刪除后,創建表 ' ); 221 EXECUTE IMMEDIATE ' DROP TABLE DELIVERY ' ; 222 ELSE 223 DBMS_OUTPUT.PUT_LINE( ' 該表不存在,創建表 ' ); 224 END IF ; 225 EXECUTE IMMEDIATE ' CREATE TABLE delivery 226 ( 227 delivery_id NUMBER, 228 delivery_start DATE, 229 delivery_end DATE, 230 area_id NUMBER, 231 driver_id NUMBER 232 ) ' ; 233 END ; 234 -- 測試語句 235 SELECT * FROM delivery; 236 -- 創建area表 237 DECLARE 238 I INTEGER ; 239 BEGIN 240 SELECT COUNT ( * ) INTO I FROM USER_TABLES WHERE TABLE_NAME = ' AREA ' ; -- 注意表名大寫 241 IF I > 0 THEN 242 DBMS_OUTPUT.PUT_LINE( ' 該表已存在!刪除后,創建表 ' ); 243 EXECUTE IMMEDIATE ' DROP TABLE AREA ' ; 244 ELSE 245 DBMS_OUTPUT.PUT_LINE( ' 該表不存在,創建表 ' ); 246 END IF ; 247 EXECUTE IMMEDIATE ' CREATE TABLE area 248 ( 249 area_id NUMBER, 250 area_desc varchar(30) 251 ) ' ; 252 END ; 253 -- 測試語句 254 SELECT * FROM area; 255 256 -- 創建driver表 257 DECLARE 258 I INTEGER ; 259 BEGIN 260 SELECT COUNT ( * ) INTO I FROM USER_TABLES WHERE TABLE_NAME = ' DRIVER ' ; -- 注意表名大寫 261 IF I > 0 THEN 262 DBMS_OUTPUT.PUT_LINE( ' 該表已存在!刪除后,創建表 ' ); 263 EXECUTE IMMEDIATE ' DROP TABLE DRIVER ' ; 264 ELSE 265 DBMS_OUTPUT.PUT_LINE( ' 該表不存在,創建表 ' ); 266 END IF ; 267 EXECUTE IMMEDIATE ' CREATE TABLE driver 268 ( 269 driver_id NUMBER, 270 driver_name varchar2(30) 271 ) ' ; 272 END ; 273 -- 測試語句 274 SELECT * FROM driver; 275 276 -- 創建3個序列為表提供唯一標識符 277 CREATE SEQUENCE delivery_id_seq; 278 CREATE SEQUENCE area_id_seq; 279 CREATE SEQUENCE driver_id_seq; 280 281 -- 用一個視圖同時顯示信息 282 CREATE OR REPLACE VIEW delivery_info AS 283 SELECT d.delivery_id, 284 d.delivery_start, 285 d.delivery_end, 286 a.area_desc, 287 dr.driver_name 288 FROM delivery d ,area a,driver dr 289 WHERE a.area_id = d.area_id 290 AND dr.driver_id = d.driver_id; 291 292 -- 創建instead of insert 觸發器 293 CREATE OR REPLACE TRIGGER delivery_info_insert 294 INSTEAD OF INSERT 295 ON delivery_info 296 DECLARE 297 -- cursour to get the driver by name 298 CURSOR curs_get_driver_id (cp_driver_name VARCHAR2 ) 299 IS 300 SELECT driver_id 301 FROM 302 driver 303 WHERE driver_name = cp_driver_name; 304 v_driver_id NUMBER ; 305 -- cursor to get area by name 306 CURSOR curs_get_area_id (cp_area_desc VARCHAR2 ) 307 IS 308 SELECT area_id 309 FROM area 310 WHERE area_desc = cp_area_desc; 311 312 v_area_id NUMBER ; 313 BEGIN 314 /* make sure the delivery_end value is null */ 315 316 IF :new.delivery_end IS NOT NULL 317 THEN 318 raise_application_error( - 20000 , ' Delivery end date value must be null when delivery created ' ); 319 END IF ; 320 321 /* try to get the driver id by the name if not found 322 *then create a new brand driver id from the sequence 323 * */ 324 OPEN curs_get_driver_id( UPPER (:new.driver_name)); 325 326 FETCH curs_get_driver_id INTO v_driver_id; 327 328 IF curs_get_driver_id % NOTFOUND 329 THEN 330 SELECT driver_id_seq.nextval 331 INTO v_driver_id 332 FROM dual; 333 334 INSERT INTO driver 335 ( 336 driver_id,driver_name 337 ) 338 VALUES (v_driver_id, UPPER (:new.driver_name)); 339 END IF ; 340 341 CLOSE curs_get_driver_id; 342 /* try to get the area id by the name if not found 343 *then create a new brand area id from the sequence 344 * */ 345 346 OPEN curs_get_area_id( UPPER (:new.area_desc)); 347 348 FETCH curs_get_area_id 349 INTO v_area_id; 350 IF curs_get_area_id % NOTFOUND 351 THEN 352 SELECT area_id_seq.nextval 353 INTO v_area_id 354 FROM dual; 355 356 INSERT INTO area 357 (area_id ,area_desc) 358 VALUES (v_area_id, UPPER (:new.area_desc)); 359 END IF ; 360 361 CLOSE curs_get_area_id; 362 /* create the delivery entry 363 */ 364 INSERT INTO delivery 365 (delivery_id,delivery_start,delivery_end,area_id,driver_id) 366 VALUES (delivery_id_seq.nextval,NVL(:new.delivery_start,SYSDATE), NULL ,v_area_id,v_driver_id); 367 368 END ; 369 370 /* ************create instead of insert trigger end****************************** */ 371 372 -- 測試語句 373 INSERT INTO delivery_info(delivery_id, 374 delivery_start,delivery_end,area_desc,driver_name) 375 VALUES ( NULL , NULL , NULL , ' local college ' , ' big ted ' ); 376 377 SELECT * FROM delivery; 378 SELECT * FROM area; 379 SELECT * FROM driver; 380
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
