oracle 20130916

一、日志

触发器:

触发器是当特定事件出现时自动执行的存储过程

特定事件可以是执行更新，添加删除语句和DDL语句

触发器不能被显式调用

 

 

定义语法：

Create or replace trigger触发器名称 after/before insert/update/delete 

On 表名 for each row

declare

…

Begin

…

End;

Eg：

createorreplacetrigger mytrigger --创建一个触发器的名称为：mytrigger

afterinsert--在执行insert语句之后触发

on ttttforeachrow--对行

declare-- local variables here

begin

  ---dbms_output.put_line('add row');

  insertinto t_log(id,op)values(myseq.nextval,'add row');--添加日志表中记录

end mytrigger;

 

eg：

createorreplacetrigger mytriggerupdate --创建一个触发器的名称为：mytriggerupdate

afterupdate

on tttt--在tttt表上

REFERENCINGOLDAS oldvalue NEWAS newvalue--将之前的值和修改后的值取出来 insert:只有new；update只有old new；

--delete 只有old 必须用在行级触发器中

foreachrow--行级触发器没有着个选项时是表级触发器

declare-- local variables here

begin

  dbms_output.put_line('old.name:'||:oldvalue.name);

  dbms_output.put_line('new.name:'||:oldvalue.name);

  insertinto t_log(id,op)values(myseq.nextval,'update row');--添加日志表中记录

end mytrigger;

 

触发器的操作:

关闭/开启

Alter trigger 触发器名称 disable/enable;

删除触发器：

Drop trigger触发器名称;                     

查看已有触发器：

SELECT *FROM user_TRIGGERS

 

Eg：

createorreplacetrigger mytriggerupdate --创建一个触发器的名称为：mytriggerupdate

afterupdate

on tttt--在tttt表上

REFERENCINGOLDAS oldvalue NEWAS newvalue--将之前的值和修改后的值取出来 insert:只有new；update只有old new；

--delete 只有old 必须用在行级触发器中

foreachrow--行级触发器没有着个选项时是表级触发器

declare-- local variables here

begin

  dbms_output.put_line('old.name:'||:oldvalue.name);

  dbms_output.put_line('new.name:'||:oldvalue.name);

  insertinto t_log(id,op)values(myseq.nextval,'update row');--添加日志表中记录

endmytrigger;

 

 

--使用内置程序包

DECLARE

  resultCLOB;

  xmlstr VARCHAR2(32767);

  line   VARCHAR2(2000);

  line_no INTEGER := 1;

BEGIN

  result := DBMS_XMLQuery.getXml('SELECT id,age,name,gz FROM tttt');--处理xml

  xmlstr := DBMS_LOB.SUBSTR(result,32767);--处理大数据类型

  DBMS_OUTPUT.PUT_LINE(result);--输出打印

  LOOP

    EXITWHEN xmlstrISNULL;

    line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);

    DBMS_OUTPUT.PUT_LINE(line);--输出打印

    xmlstr :=SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);

    line_no := line_no +1;

  ENDLOOP; 

END;

--

CREATEDIRECTORY TEST_DIRAS'd:\DEVELOP';--给d:\DEVELOP目录取名叫 TEST_DIR

GRANTREAD,WRITEONDIRECTORY TEST_DIR TO userorcl;--授权

 

DECLARE

  input_file   UTL_FILE.FILE_TYPE;

  input_buffer VARCHAR2(4000);

BEGIN

  input_file := UTL_FILE.FOPEN('TEST_DIR','testxml.xml','r');--创建读文件 TEST_DIR 字符大写

  LOOP

   UTL_FILE.GET_LINE(input_file,input_buffer);--从文件中获取一行

   DBMS_OUTPUT.PUT_LINE(input_buffer);

  ENDLOOP;

  UTL_FILE.FCLOSE(input_file);--关闭读文件

EXCEPTION

  WHEN NO_DATA_FOUNDTHEN

    DBMS_OUTPUT.PUT_LINE('------------------');

END;

 

--实现将数据库表记录写到本地xml文件中

declare

xmlstr varchar2(32767);

line varchar2(2000);

outfile utl_file.file_type;

resultCLOB;--大数据类型以字符为单位

begin

result :=DBMS_XMLQuery.getXml('SELECT id, age,name,gz FROM tttt');--获取表中xml

outfile := utl_file.fopen('TEST_DIR','testxml1.xml','w');--创建一个文件输出流

xmlstr := dbms_lob.substr(result,32767);--从result获取字符串

loop

exitwhen xmlstrisnull;

    line := substr(xmlstr,1, instr(xmlstr, chr(10)) -1);

    utl_file.put_line(outfile,line);

    xmlstr := substr(xmlstr,instr(xmlstr, chr(10)) +1);--从result获取字符串

endloop;

utl_file.fclose(outfile);

end;

 ************************************************************************************************************

二、代码

 create or replace trigger mytriggerupdate --创建一个触发器的名称为：mytriggerupdate
after update
on tttt--在tttt表上
REFERENCING OLD AS oldvalue  NEW AS newvalue--将之前的值和修改后的值取出来  insert:只有new；update只有old new；
--delete 只有old  必须用在行级触发器中
for each row --行级触发器 没有着个选项时是表级触发器
declare-- local variables here
begin
  dbms_output.put_line('old.name:'||:oldvalue.name);
  dbms_output.put_line('new.name:'||:oldvalue.name);
  insert into t_log(id,op) values(myseq.nextval,'update row');--添加日志表中记录
end mytrigger;


--使用内置程序包
DECLARE
  result CLOB;
  xmlstr VARCHAR2(32767);
  line   VARCHAR2(2000);
  line_no INTEGER := 1;
BEGIN
  result := DBMS_XMLQuery.getXml('SELECT id, age,name,gz FROM tttt');--处理xml
  xmlstr := DBMS_LOB.SUBSTR(result,32767);--处理大数据类型
  DBMS_OUTPUT.PUT_LINE(result);--输出打印
  LOOP
    EXIT WHEN xmlstr IS NULL;
    line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);
    DBMS_OUTPUT.PUT_LINE(line);--输出打印
    xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);
    line_no := line_no + 1;
  END LOOP;  
END;
--
CREATE DIRECTORY TEST_DIR AS 'd:\DEVELOP';--给d:\DEVELOP目录取名叫 TEST_DIR
GRANT READ,WRITE ON DIRECTORY TEST_DIR TO userorcl;--授权

DECLARE
  input_file   UTL_FILE.FILE_TYPE;
  input_buffer VARCHAR2(4000);
BEGIN
  input_file := UTL_FILE.FOPEN('TEST_DIR', 'testxml.xml', 'r');--创建读文件 TEST_DIR 字符大写
  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;

--实现将数据库表记录写到本地xml文件中
declare
xmlstr varchar2(32767);
line varchar2(2000);
outfile utl_file.file_type;
result CLOB;--大数据类型以字符为单位
begin
result := DBMS_XMLQuery.getXml('SELECT id, age,name,gz FROM tttt');--获取表中xml
outfile := utl_file.fopen('TEST_DIR', 'testxml1.xml','w');--创建一个文件输出流
xmlstr := dbms_lob.substr(result,32767);-- 从result获取字符串
loop
exit when xmlstr is null;
    line := substr(xmlstr,1, instr(xmlstr, chr(10)) -1);
    utl_file.put_line(outfile, line);
    xmlstr := substr(xmlstr, instr(xmlstr, chr(10)) +1);-- 从result获取字符串
end loop;
utl_file.fclose(outfile);
end;