oracle 触发器 和 常用内置程序包
--author:shine
--一.触发器：
--1.触发器组成：由触发器语句，触发器限制，触发器操作三部分组成。
--exp:1.1.1
create or replace trigger tri_test1
before update or insert
on emp
for each row --以上是触发器语句（即：什么时机触发）
when (new.sal > 5000) --触发器限制(即：满足什么条件时执行触发器操作,注意：只能一个触发器只能有一个限制)
begin --以下是触发器操作（即：满足触发器限制时执行什么操作）
dbms_output.put_line('会不会多啦点');
:new.sal := 2000;
end;
--测试
update emp set sal = 6000 where empno=7369;
--2.触发器类型：
--1)行级触发器:(每一行触发一次)
--exp:1.2.1：用触发器做个自动增长列
create table stu (stuno int ,stuname varchar2(10));
create sequence seq_stuno ;
create or replace trigger tri_test2
before insert or update on stu --before行级触发器，在记录进入数据库之前触发
for each row --行级触发器标志
declare
stu_no int;
begin
if updating then
raise_application_error(-20001,'此表不能更新！');
elsif inserting then
select seq_stuno.nextval into stu_no from dual;
:new.stuno := stu_no; --干预了插入的新值
end if;
end;
--测试
insert into stu(stuname)
select ename from emp;
--2)语句级触发器：不论一句影响了多少行，每一句只触发一次（即：以分号为标志）
--exp:1.2.2
create or replace trigger tri_test3
after insert or update or delete on stu --after语句级触发器，在记录进入数据库之后触发
begin
if inserting then
dbms_output.put_line('进行了插入');
elsif updating then
dbms_output.put_line('进行了更新');
elsif deleting then
dbms_output.put_line('进行了删除');
end if;
end;
--测试
delete from stu;
/*
小结一下：
a.为了不使触发器内部发生混乱，触发器禁止使用rollback,commit.
b.在使用触发器时应该防止触发器之间发生递归调用。
c.after和before触发器虽然都能使用new和old，但是由于before触发器是在对数据进行操作之前触发，
所以它可以干预new值（如：exp1.2.1中），而after触发器不能干预new值。
d.如果不说明是before，after触发器，则默认为after触发器.
*/
--3)instead of 触发器
--a.instead of 触发器 只能用在视图上，不能用在表上。
--b.instead of 只能是行级，不能是语句级（即：要写for each row）
--exp:1.2.3
drop view view_emp_dept;
create view view_emp_dept as
select empno,ename,b.* from emp a,dept b where a.deptno = b.deptno;
select * from view_emp_dept ;
create or replace trigger tri_view_emp
instead of update or insert on view_emp_dept
referencing new as n
for each row
declare
cursor emp_cur is select * from emp where empno = :n.empno;
cursor dept_cur is select * from dept where deptno = :n.deptno;
emp_row emp%rowtype;
dept_row dept%rowtype;
begin
open emp_cur;
open dept_cur;
fetch emp_cur into emp_row;
if emp_cur%notfound then
insert into emp(empno,ename,deptno) values(:n.empno,:n.ename,:n.deptno);
else
update emp set ename = :n.ename,deptno = :n.deptno where empno = :n.empno;
end if;
fetch dept_cur into dept_row;
if dept_cur%notfound then
insert into dept values(:n.deptno,:n.dname,:n.loc);
else
update dept set dname = :n.dname, loc = :n.loc where deptno = :n.deptno;
end if;
close emp_cur;
close dept_cur;
end;
--测试
insert into view_emp_dept
values(8888,'yaoyao',33,'yaoyao','wuhan');
--4）模式触发器：
--针对的是DDL语句,如：drop,create,alter,grant,revoke.truncate，主要用来写日志
--exp:1.2.4
create or replace trigger tri_test4
before truncate or grant or drop or create or alter or revoke on schema
begin
dbms_output.put_line(ora_dict_obj_name||' '||ora_dict_obj_type||' '||sysdate||' '||ora_dict_obj_owner);
end;
--测试
grant select on emp to shine;
drop table emp;
--5）数据库级触发器：
--针对的是数据库的实例的，如：启动，关闭，登陆，注销等等,必需是sys/change_on_install as sysdba权限
--exp:1.2.5
create or replace trigger tri_test5
after startup on database
begin
dbms_output.put_line('数据库启动啦');
end;
--3.启用触发器和禁用触发器
alter trigger tri_test4 disable;--禁用
alter trigger tri_test4 enable;--启用
--4.删除触发器
drop trigger tri_test4;
--5.查看触发器
desc user_triggers;
select * from user_triggers;
--二.常用内置程序包
--1.dbms_output:顾名思义管输出的
--1)put 输出到缓冲区（没换行）
--2)putline 输出一行（带换行）
begin
dbms_output.put('ssssss'||'aaaa'); --若只写这一句，是把‘c’输入缓冲区，Output窗口看不见。
dbms_output.new_line();
end;
--2.dbms_lob:larger object,操作大对象的
--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
result1 clob;
xmlstr varchar(32767);
strline varchar(2000);
line_no number := 1;
begin
result1 := dbms_xmlquery.getXML('select * from dept');
xmlstr := substr(result1,1,32767);
loop
exit when xmlstr is null;
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;
end loop;
end;
--4.dbms_random:操作随机数
--1）dbms_random.random生成8位的随机整数.
declare
num number;
begin
for i in 1..10
loop
num := abs(dbms_random.random mod 100);
dbms_output.put_line(num);
end loop;
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_install as sysdba登陆,然后输入以下命令：
3)shutdown immediate;
4)startup mount;
5)alter system set UTL_FILE_DIR='D:\myxml' scope=spfile;
6)shutdown immediate;
7)startup;
8)再show parameter utl检验一下，若utl_file_dir对应的value值是d:\myxml，说明你的文件夹配好了，接下来就是
pl/sql代码了。如下：
*/
--write xml:把src（大对象）先放入缓冲当中，再放到文件中
declare
src clob;
xmlfile utl_file.file_type;
length number;
buffer varchar2(16384);
begin
src := dbms_xmlquery.getXML('select * from dept');
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;
--read xml
declare
input_file utl_file.file_type;
input_buffer varchar2(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);
end loop;
utl_file.fclose(input_file);
exception
when no_data_found then
dbms_output.put_line('----------------------');
end;
/*
方法二:
1)用system/manager 登陆
2）创建文件夹 create directory MY_XML as 'D:\myxml';
3）授权 grant write,read on directory MY_XML to scott;
4)用scott登陆 conn scott/tiger;
*/
--write xml:把src（大对象）先放入缓冲当中，再放到文件中
declare
src clob;
xmlfile utl_file.file_type;
length number;
buffer varchar2(16384);
begin
src := dbms_xmlquery.getXML('select * from dept');
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;
--read xml
declare
input_file utl_file.file_type;
input_buffer varchar2(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);
end loop;
utl_file.fclose(input_file);
exception
when no_data_found then
dbms_output.put_line('----------------------');
