oracle+触发器.docx
- 文档编号:14883554
- 上传时间:2023-06-28
- 格式:DOCX
- 页数:9
- 大小:17.83KB
oracle+触发器.docx
《oracle+触发器.docx》由会员分享,可在线阅读,更多相关《oracle+触发器.docx(9页珍藏版)》请在冰点文库上搜索。
oracle+触发器
oracle触发器和常用内置程序包
-触发器和常用内置程序包
--author:
shine
--一.触发器:
--1.触发器组成:
由触发器语句,触发器限制,触发器操作三部分组成。
--exp:
1.1.1
createorreplacetriggertri_test1
beforeupdateorinsert
onemp
foreachrow --以上是触发器语句(即:
什么时机触发)
when(new.sal>5000)--触发器限制(即:
满足什么条件时执行触发器操作,注意:
只能一个触发器只能有一个限制)
begin --以下是触发器操作(即:
满足触发器限制时执行什么操作)
dbms_output.put_line('会不会多啦点');
:
new.sal:
=2000;
end;
--测试
updateempsetsal=6000whereempno=7369;
--2.触发器类型:
--1)行级触发器:
(每一行触发一次)
--exp:
1.2.1:
用触发器做个自动增长列
createtablestu(stunoint,stunamevarchar2(10));
createsequenceseq_stuno;
createorreplacetriggertri_test2
beforeinsertorupdateonstu --before行级触发器,在记录进入数据库之前触发
foreachrow --行级触发器标志
declare
stu_noint;
begin
ifupdatingthen
raise_application_error(-20001,'此表不能更新!
');
elsifinsertingthen
selectseq_stuno.nextvalintostu_nofromdual;
:
new.stuno:
=stu_no;--干预了插入的新值
endif;
end;
--测试
insertintostu(stuname)
selectenamefromemp;
--2)语句级触发器:
不论一句影响了多少行,每一句只触发一次(即:
以分号为标志)
--exp:
1.2.2
createorreplacetriggertri_test3
afterinsertorupdateordeleteonstu--after语句级触发器,在记录进入数据库之后触发
begin
ifinsertingthen
dbms_output.put_line('进行了插入');
elsifupdatingthen
dbms_output.put_line('进行了更新');
elsifdeletingthen
dbms_output.put_line('进行了删除');
endif;
end;
--测试
deletefromstu;
/*
小结一下:
a.为了不使触发器内部发生混乱,触发器禁止使用rollback,commit.
b.在使用触发器时应该防止触发器之间发生递归调用。
c.after和before触发器虽然都能使用new和old,但是由于before触发器是在对数据进行操作之前触发,
所以它可以干预new值(如:
exp1.2.1中),而after触发器不能干预new值。
d.如果不说明是before,after触发器,则默认为after触发器.
*/
--3)insteadof触发器
--a.insteadof触发器只能用在视图上,不能用在表上。
--b.insteadof只能是行级,不能是语句级(即:
要写foreachrow)
--exp:
1.2.3
dropviewview_emp_dept;
createviewview_emp_deptas
selectempno,ename,b.*fromempa,deptbwherea.deptno=b.deptno;
select*fromview_emp_dept;
createorreplacetriggertri_view_emp
insteadofupdateorinsert onview_emp_dept
referencingnewasn
foreachrow
declare
cursoremp_curisselect*fromempwhereempno=:
n.empno;
cursordept_curisselect*fromdeptwheredeptno=:
n.deptno;
emp_rowemp%rowtype;
dept_rowdept%rowtype;
begin
openemp_cur;
opendept_cur;
fetchemp_curintoemp_row;
ifemp_cur%notfoundthen
insertintoemp(empno,ename,deptno)values(:
n.empno,:
n.ename,:
n.deptno);
else
updateempsetename=:
n.ename,deptno=:
n.deptnowhereempno=:
n.empno;
endif;
fetchdept_curintodept_row;
ifdept_cur%notfoundthen
insertintodeptvalues(:
n.deptno,:
n.dname,:
n.loc);
else
updatedeptsetdname=:
n.dname,loc=:
n.locwheredeptno=:
n.deptno;
endif;
closeemp_cur;
closedept_cur;
end;
--测试
insertintoview_emp_dept
values(8888,'yaoyao',33,'yaoyao','wuhan');
--4)模式触发器:
--针对的是DDL语句,如:
drop,create,alter,grant,revoke.truncate,主要用来写日志
--exp:
1.2.4
createorreplacetriggertri_test4
beforetruncateorgrantordroporcreateoralterorrevoke onschema
begin
dbms_output.put_line(ora_dict_obj_name||' '||ora_dict_obj_type||' '||sysdate||' '||ora_dict_obj_owner);
end;
--测试
grantselectonemptoshine;
droptableemp;
--5)数据库级触发器:
--针对的是数据库的实例的,如:
启动,关闭,登陆,注销等等,必需是sys/change_on_installassysdba权限
--exp:
1.2.5
createorreplacetriggertri_test5
afterstartupondatabase
begin
dbms_output.put_line('数据库启动啦');
end;
--3.启用触发器和禁用触发器
altertriggertri_test4disable;--禁用
altertriggertri_test4enable;--启用
--4.删除触发器
droptriggertri_test4;
--5.查看触发器
descuser_triggers;
select*fromuser_triggers;
--二.常用内置程序包
--1.dbms_output:
顾名思义管输出的
--1)put输出到缓冲区(没换行)
--2)putline输出一行(带换行)
begin
dbms_output.put('ssssss'||'aaaa');--若只写这一句,是把‘c’输入缓冲区,Output窗口看不见。
dbms_output.new_line();
end;
--2.dbms_lob:
largerobject,操作大对象的
--1)append,2)copy,3)erase,4)getlength,5)instr,6)read,7)substr,8)write,9)fileopen,10)filegetname,11)fileclose
--操作大对象已在"操作大对象"一文中讲过了,这里不再重复。
--3.dbms_xmlquery.getXML:
用于将查询结果转换为xml格式。
declare
result1clob;
xmlstrvarchar(32767);
strlinevarchar(2000);
line_nonumber:
=1;
begin
result1:
=dbms_xmlquery.getXML('select*fromdept');
xmlstr:
=substr(result1,1,32767);
loop
exitwhenxmlstrisnull;
strline:
=substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(line_no||':
'||strline);
xmlstr:
=substr(xmlstr,instr(xmlstr,chr(10))+1);
line_no:
=line_no+1;
endloop;
end;
--4.dbms_random:
操作随机数
--1)dbms_random.random生成8位的随机整数.
declare
numnumber;
begin
foriin1..10
loop
num:
=abs(dbms_random.randommod100);
dbms_output.put_line(num);
endloop;
end;
--2)dbms_random.value生成指定范围的随机数
--一个大于m小于n的随机数
begin
dbms_output.put_line(dbms_random.value(1,100));
end;
--一个大于m小于n的整数
begin
dbms_output.put_line(round(dbms_random.value(1,100),0));
end;
/*
3)dbms_randon.string(参数一,参数二);
其中:
参数二是指生成随机数的长度.
参数一有以下几种形式:
1)'u'生成的是大写字母
2)'l'生成的是小写字母
3)'a'生成的是大小写混合
4)'x'生成的是数字和大写字母混合
5)'p'任何形式(连特殊符号都行);
*/
begin
dbms_output.put_line(dbms_random.string('x',8));
end;
--5.utl_file:
用于从pl/sql中读写操作系统文本文件。
--由于版本原因操作上有一点点不一样
--若是9i的老版本采用方法一,若是10i则采用方法二
/*
方法一:
1)在oracle目录下搜索init.ora文件,加上UTL_FILE_DIR=D:
\myxml (后面的D:
\myxml是你放文本文件的文件夹)
2)在sqlplus上以sys/change_on_installassysdba登陆,然后输入以下命令:
3)shutdownimmediate;
4)startupmount;
5)altersystemsetUTL_FILE_DIR='D:
\myxml'scope=spfile;
6)shutdownimmediate;
7)startup;
8)再showparameterutl检验一下,若utl_file_dir对应的value值是d:
\myxml,说明你的文件夹配好了,接下来就是
pl/sql代码了。
如下:
*/
--writexml:
把src(大对象)先放入缓冲当中,再放到文件中
declare
srcclob;
xmlfileutl_file.file_type;
lengthnumber;
buffervarchar2(16384);
begin
src:
=dbms_xmlquery.getXML('select*fromdept');
length:
=dbms_lob.getlength(src);
dbms_lob.read(src,length,1,buffer);
xmlfile:
=utl_file.fopen('D:
\myxml','dept.xml','w');
utl_file.put(xmlfile,buffer);
utl_file.fclose(xmlfile);
end;
--readxml
declare
input_fileutl_file.file_type;
input_buffervarchar2(2000);
begin
input_file:
=utl_file.fopen('D:
\myxml','dept.xml','r');
loop
utl_file.get_line(input_file,input_buffer);
dbms_output.put_line(input_buffer);
endloop;
utl_file.fclose(input_file);
exception
whenno_data_foundthen
dbms_output.put_line('----------------------');
end;
/*
方法二:
1)用system/manager登陆
2)创建文件夹createdirectoryMY_XMLas'D:
\myxml';
3)授权grantwrite,readondirectoryMY_XMLtoscott;
4)用scott登陆connscott/tiger;
*/
--writexml:
把src(大对象)先放入缓冲当中,再放到文件中
declare
srcclob;
xmlfileutl_file.file_type;
lengthnumber;
buffervarchar2(16384);
begin
src:
=dbms_xmlquery.getXML('select*fromdept');
length:
=dbms_lob.getlength(src);
dbms_lob.read(src,length,1,buffer);
xmlfile:
=utl_file.fopen(MY_XML,'dept.xml','w');--就这里不同
utl_file.put(xmlfile,buffer);
utl_file.fclose(xmlfile);
end;
--readxml
declare
input_fileutl_file.file_type;
input_buffervarchar2(2000);
begin
input_file:
=utl_file.fopen(MY_XML,'dept.xml','r');--就这里不同
loop
utl_file.get_line(input_file,input_buffer);
dbms_output.put_line(input_buffer);
endloop;
utl_file.fclose(input_file);
exception
whenno_data_foundthen
dbms_output.put_line('----------------------');
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 触发器