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;
浙公网安备 33010602011771号