1 --触发器 2 drop table emp_log 3 create table emp_log( 4 empno number, 5 log_date date, 6 new_salary number, 7 action varchar2(20) --动作记录 8 ); 9 create or replace trigger log_sal_adj 10 after update of sal on emp --指定当update执行后,监控对emp表sal列的更改 before or adter 11 for each row --每update一行执行一次触发器代码 12 declare 13 v_action varchar2(20); --定义一个保存更新行为的字符串变量 14 begin 15 if :old.sal >:new.sal THEN 16 v_action :='减少工资'; 17 elsif :old.sal<:new.sal then 18 v_action :='增加工资'; 19 end if; 20 insert into emp_log 21 (empno,log_date,new_salary,action) 22 values 23 (:new.empno,sysdate,:new.sal,v_action); 24 end; 25 select * from emp_log; 26 select sal from emp where empno=7369; 27 update emp set sal=8000 where empno=7369; 28 --异常处理示例 29 declare 30 v_result number:=0; 31 v_dividend number:=&a; --定义替换变量 32 begin 33 v_result:=round(1000/v_dividend,2); 34 dbms_output.put_line('结果值为:'||v_result); 35 exception 36 when zero_divide then --如果是被0除错误 37 dbms_output.put_line('出现被0除的错误了!'); 38 when others then --所有其他错误的异常处理句柄 39 dbms_output.put_line(sqlcode); --输出错误代码 40 dbms_output.put_line(sqlerrm); --输出错误消息 41 end; 42 --记录类型示例 43 Declare 44 type emp_rec_type is record( 45 empno emp.empno%type, 46 ename varchar2(50), 47 job varchar2(20) ); 48 emp_info_rec emp_rec_type; 49 emp_row_rec emp%Rowtype; --=>(eno emp.empno%type +enme emp.ename%type) 50 begin 51 emp_info_rec.empno:=8222; 52 emp_info_rec.ename:='李三思'; 53 emp_info_rec.job :='销售人员'; 54 insert into emp (empno,ename,job) 55 values (emp_info_rec.empno,emp_info_rec.ename,emp_info_rec.job); 56 select * into emp_row_rec from emp where empno=8222; 57 dbms_output.put_line('新插入的员工记录信息:'||CHR(10)||'工号:'|| 58 emp_row_rec.empno||CHR(10)||'姓名:'|| 59 emp_row_rec.ename||CHR(10)||'职位:'|| 60 emp_row_rec.job); 61 end; 62 63 --集合类型使用示例 64 declare 65 --定义保存员工工号的索引表,其类型为emp表中的empno字段相同的类型 66 type NumTab is table of emp.empno%type index by binary_integer; --pls_integer 67 type NameTab is table of emp.ename%type index by binary_integer; 68 --定义集合类型的变量 69 enums NumTab; 70 names NameTab; 71 72 73 /* 定义块 type语句定义了NumTab和NameTab这两个索引表, 74 is table of指定索引的类型, index by指定索引的下标数据类型 75 binary_integer是PL/SQL中的整数类型 76 77 匿名块print_first_n,它的作用范围仅限于匿名块内部 78 执行 select..bulk collect into将表中查询出来的多行记录写入到集合中 79 */ 80 procedure print_first_n(n POSITIVE) is 81 BEGIN 82 IF enums.count =0 then 83 DBMS_OUTPUT.put_line('当前集合为空!'); 84 else 85 DBMS_OUTPUT.put_line('前'||n||'名员工:'); 86 for i in 1..n loop 87 dbms_output.put_line('员工工号:'||enums(i)||': '||names(i)); 88 end loop; 89 end if; 90 END; 91 92 begin 93 select empno,ename bulk collect 94 into enums,names from emp 95 order by empno; 96 print_first_n(3); 97 print_first_n(6); 98 end; 99 100 select empno,ename from emp order by empno; 101 102 --使用游标遍历结果集 103 declare 104 emprow emp%rowtype; 105 cursor emp_cur 106 is 107 select * from emp where deptno=20; 108 begin 109 open emp_cur; 110 loop 111 fetch emp_cur 112 into emprow; 113 DBMS_OUTPUT.put_line('员工编号:'||emprow.empno||' ' 114 ||'员工名称:'||emprow.ename); 115 exit when emp_cur%NOTFOUND; 116 end loop; 117 close emp_cur; --关闭游标 118 end; 119 120 1 emprow是一个记录类型,保存fetch语句提取的记录值 121 2 cursor is 指向一个返回结果集的select语句 122 3 open语句打开游标,指向结果集内存区域 123 4 游标使用完之后,必须显示调用close关闭游标,释放资源 124 125 126 子程序不像匿名块仅仅是一次使用,子程序存储在数据字典中 127 可以被其它子程序重复调用 128 129 --使用游标和索引表显示员工名称 130 DECLARE 131 --定义员工名称索引表 132 TYPE emp_table IS TABLE OF VARCHAR2(10); 133 emplist emp_table; --定义表类型的变量 134 CURSOR empcursor IS 135 SELECT ename FROM emp; 136 BEGIN 137 IF NOT empcursor%ISOPEN THEN 138 OPEN empcursor; 139 END IF; 140 FETCH empcursor BULK COLLECT 141 INTO emplist; 142 FOR i IN 1 .. emplist.COUNT LOOP 143 DBMS_OUTPUT.put_line('员工名称:' || emplist(i)); 144 END LOOP; 145 CLOSE empcursor; 146 END; 147 --rowid使用示例 148 declare 149 v_empname rowid; 150 v_other varchar2(18); 151 begin 152 select rowid into v_empname from emp where empno=&empno; 153 dbms_output.put_line(v_empname); 154 v_other:=rowidtochar(v_empname); 155 dbms_output.put_line(v_other); 156 end; 157 158 select rowid from emp where empno=7369; 159 160 --批量提取游标数据 161 DECLARE 162 type depttab_type is table of dept%rowtype; 163 depttab depttab_type; 164 cursor deptcur is select * from dept; 165 begin 166 open deptcur; 167 fetch deptcur bulk collect into depttab; 168 169 for i in 1 .. depttab.count 170 loop 171 dbms_output.put_line(depttab (i).deptno ||' '||depttab(i).dname||' '||depttab(i).loc); 172 end loop; 173 close deptcur; 174 end; 175 176 --操纵游标数据 loop循环 177 declare 178 dept_row dept%rowtype; 179 cursor dept_cursor is select * from dept; 180 begin 181 open dept_cursor; 182 loop 183 fetch dept_cursor into dept_row; 184 exit when dept_cursor%notfound; 185 dbms_output.put_line('部门名称:'||dept_row.dname); 186 end loop; 187 close dept_cursor; 188 end; 189 190 --while循环 191 declare 192 dept_row dept%rowtype; 193 cursor dept_cursor is select * from dept; 194 begin 195 open dept_cursor; 196 fetch dept_cursor into dept_row; 197 while dept_cursor%found loop 198 dbms_output.put_line('部门名称:'||dept_row.dname); 199 fetch dept_cursor into dept_row; 200 end loop; 201 close dept_cursor; 202 end; 203 调用了2次fetch语句, 判断之前fetch一次获取%found属性值 204 后面的fetch语句在循环体内对每一次的循环求值 205 206 207 /*游标for循环 尽管定义为一个显式游标,但PLSQL引擎进行特别处理 208 不需要open,close;*/ 209 declare 210 cursor dept_cursor is select * from dept; 211 begin 212 for dept_row in dept_cursor loop 213 dbms_output.put_line('部门名称:'||dept_row.dname); 214 end loop; 215 end; 216 217 218 --游标FOR循环子查询语句 219 begin 220 for dept_row in (select * from dept) loop 221 dbms_output.put_line('部门名称:'||dept_row.dname); 222 end loop; 223 end; 224 225 --使用游标更新数据 226 declare 227 cursor emp_cursor(p_deptno in number) 228 is select * from emp where deptno=p_deptno for update; 229 --使用for update子句添加互斥锁 230 begin 231 for emp_row in emp_cursor(20) 232 loop 233 update emp set sal=sal*1.12 234 where current of emp_cursor; 235 end loop; 236 commit; 237 end; 238 239 select sal,ename from emp where deptno=20; 240 241 --使用游标删除数据 242 declare 243 cursor emp_cursor(p_empno in number) 244 is select * from emp_copy where empno=p_empno for update; 245 begin 246 for emp_row in emp_cursor(7369) 247 loop 248 delete from emp_copy where current of emp_cursor; 249 end loop; 250 end; 251 252 253 --游标变量 254 declare 255 type emp_type is ref cursor return emp%rowtype; 256 emp_cur emp_type; 257 emP_row emp%rowtype; 258 begin 259 open emp_cur for select * from emp; 260 loop 261 fetch emp_cur into emp_row; 262 exit when emp_cur%notfound; 263 dbms_output.put_line('员工名称:'|| emp_row.ename); 264 end loop; 265 end; 266 267 11.14 268 --使用close 语句关闭游标变量 269 --如果type语句中未指定return子句,则可以连续地打开多次,分别为其赋不同的select语句 270 declare 271 type emp_type is ref cursor return emp%rowtype; 272 emp_cur emp_type; 273 emp_row emp%rowtype; 274 begin 275 open emp_cur for select * from emp where deptno=20; 276 fetch emp_cur into emp_row; 277 while emp_cur%found loop 278 dbms_output.put_line('员工名称:'||emp_row.ename); 279 fetch emp_cur into emp_row; 280 end loop; 281 close emp_cur; 282 end; 283 284 关闭一个还没有打开过的游标变量或已经关闭了的游标变量是非法的 285 PLSQL引发invalid_cursor异常 286 declare 287 type emp_curtype is ref cursor; 288 emp_cur1 emp_curtype; 289 emp_cur2 emp_curtype; 290 emp_row emp%rowtype; 291 begin 292 open emp_cur1 for select * from emp where deptno=20; 293 fetch emp_cur1 into emp_row; 294 dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:' 295 ||emp_row.deptno); 296 fetch emp_cur2 into emp_row; 297 exception 298 when invalid_cursor then 299 emp_cur2:=emp_cur1; 300 fetch emp_cur2 into emp_row; 301 dbms_output.put_line('员工名称:' ||emp_row.ename|| 302 '部门编号:'||emp_row.deptno); 303 --重新打开emp_cur2游标变量,利用相同的查询区域 304 open emp_cur2 for select * from emp where deptno=40; 305 fetch emp_cur1 into emp_row; 306 --emp_cur1与emp_cur2共享相同的查询区域,因此结果相同 307 dbms_output.put_line('员工名称:' ||emp_row.ename|| 308 '部门编号:'||emp_row.deptno); 309 end; 310 311 使用open for语句打开emp_cur2时候,将相同的查询区域执行另一个查询语句 312 由于emp_cur1与emp_cur2都指向相同的查询区域,提取emp_cur1时候 313 实际相当于对emp_cur2提取 314 --处理ROWTYPE_MISMATCH异常 315 declare 316 type emp_curtype is ref cursor; 317 emp_cur emp_curtype; 318 emp_row emp%rowtype; 319 dept_row dept%rowtype; 320 begin 321 open emp_cur for select * from emp where deptno=20; 322 fetch emp_cur into dept_row; 323 exception 324 when rowtype_mismatch then 325 fetch emp_cur into emp_row; 326 dbms_output.put_line('员工名称:'||emp_row.ename 327 ||'部门编号:'||emp_row.deptno); 328 end; 329 --使用sys_refcursor类型 不需要type语句显式定义弱类型游标 330 declare 331 emp_cur sys_refcursor; --定义弱类型游标变量 332 emp_row emp%rowtype; 333 dept_row dept%rowtype; 334 begin 335 open emp_cur for select * from emp where deptno=20; 336 fetch emp_cur into dept_row; 337 exception 338 when rowtype_mismatch then 339 fetch emp_cur into emp_row; 340 dbms_output.put_line('员工名称:'||emp_row.ename 341 ||'部门编号:'||emp_row.deptno); 342 end; 343 --在包中使用游标变量 344 create or replace package emp_data_action as 345 type emp_type is ref cursor return emp%rowtype; --定义强类型游标 346 procedure getempbydeptno(emp_cur in out emp_type,p_deptno number); 347 end emp_data_action; 348 349 create or replace package body emp_data_action as 350 procedure getempbydeptno(emp_cur in out emp_type,p_deptno number) 351 is emp_row emp%rowtype; 352 begin 353 open emp_cur for select * from emp where deptno=p_deptno; 354 loop 355 fetch emp_cur into emp_row; 356 exit when emp_cur%notfound; 357 dbms_output.put_line('员工名称:'||emp_row.ename 358 ||'部门编号:'||emp_row.deptno); 359 end loop; 360 close emp_cur; 361 end; 362 end emp_data_action; 363 364 365 declare 366 emp_cursors emp_data_action.emp_type; 367 begin 368 emp_data_action.getempbydeptno(emp_cursors,20); 369 end; 370 371 游标变量的限制 372 1.不能在包中声明游标变量 373 2 不能在创建表或创建视图的语句中把字段类型指定为REF CURSOR类型 374 数据库字段不能存放游标变量值 375 3 游标类型参数不支持远程过程调用 376 4 不能将ref cursor 类型作为集合的元素类型 377 5 不能在游标中使用游标for循环 378 一个事务必须满足ACID 即原子性,一致性,隔离性和持久性 379 --使用保存点局部回滚 380 declare 381 dept_no number(2) :=90; 382 begin 383 savepoint A; 384 insert into dept values(dept_no,'市场部','北京'); 385 savepoint B; 386 insert into emp values(7997,'威尔','销售人员',null,trunc(sysdate),5000,300,dept_no); 387 savepoint c; 388 insert into dept values(dept_no,'后勤部','上海'); --插入相同编号的部门记录 389 commit; 390 exception 391 when dup_val_on_index then 392 dbms_output.put_line(sqlerrm); 393 rollback to b; 394 end; 395 396 select * from dept; 397 --控制触发顺序 FOLLOWS子句 398 create or replace trigger one_trigger 399 before insert on trigger_data for EACH ROW 400 begin 401 :new.trigger_id := :new.trigger_id+1; 402 DBMS_OUTPUT.put_line('触发了one_trigger'); 403 end; 404 405 create or replace trigger two_trigger 406 before insert on trigger_data 407 for each row follows one_trigger --让该触发器在one_trigger后面触发 408 begin 409 DBMS_OUTPUT.put_line('触发了two_trigger'); 410 if :new.trigger_id>1 411 then 412 :new.trigger_id := :new.trigger_id +2: 413 end if; 414 end; 415 416 417 --系统触发器 scott 418 create table created_log 419 ( 420 obj_owner varchar2(30), 421 obj_name varchar2(30), 422 obj_type varchar2(20), 423 obj_user varchar2(30), 424 created_date DATE 425 ) 426 --system 427 create or replace trigger t_created_log 428 after create on scott.SCHEMA --在Scott方案下创建对象后触发 429 begin 430 insert into scott.created_log(obj_owner,obj_name,obj_type,obj_user, 431 created_date) values (sys.dictionary_obj_owner,sys.dictionary_obj_name, 432 sys.dictionary_obj_type,sys.login_user,sysdate); 433 end; 434 --scott 435 create table temp_table(field1 varchar2(20),field2 number(5)); 436 select * from created_log; 437 触发器属性列表 图6 438 --ora_is_drop_column和ora_is_alter_column 禁止非法更改列 439 create or replace trigger preserve_app_cols 440 after alter on schema 441 declare 442 cursor curs_get_columns (cp_owner varchar2,cp_table varchar2) 443 is select column_name from all_tab_columns 444 where owner = cp_owner and table_name=cp_table; 445 begin 446 if ora_dict_obj_type ='TABLE' 447 THEN 448 FOR v_column_rec in curs_get_columns (ora_dict_obj_owner,ora_dict_obj_name) 449 loop 450 if ora_is_drop_column(v_column_rec.column_name) 451 then 452 if v_column_rec.column_name='EMPNO' THEN 453 RAISE_APPLICATION_ERROR(-2003,'不能对empno字段进行修改'); 454 end if; 455 end if; 456 end loop; 457 end if; 458 end; 459 alter table emp drop column empno 460 select * from emp;