菜单

详细解释oracle中通过触发器记录每一个语句影响总行数,存款和储蓄函数

2020年2月11日 - 首页
详细解释oracle中通过触发器记录每一个语句影响总行数,存款和储蓄函数

详明oracle中通过触发器记录每种语句影响总行数

作者: kent鹏  

急需发生:

转发请表明出处:  

政工系统中,有一步“抽数”流程,正是把一些数目从任何服务器同步到本库的目的表。这些历程有非常大希望几人还要抽数,相互影响。有测量试验人士反应,原本抽过的数,临时就莫名其妙的找不到了,一时又会出来重复行。那个难点发出一定是抽数逻辑难题甚至相互影响的标题了!但她俩提了叁个简便的要求:想精通怎么着时候数据被剔除了,哪一天插入了,作者索要监察和控制“表的每回变动”!

 

技能选取:

澳门太阳集团,1.游标(光标)Cursor

在写java程序中有汇聚的定义,那么在pl/sql中也会用到多条记下,此时大家就要动用游标,游标能够累积查询再次来到的多条数据。

语法:

CURSOR  游标名  [ (参数名  数据类型,参数名 数据类型,…卡塔尔(قطر‎]  IS  SELECT   语句;

例如:cursor c1 is select ename from emp;

游标的接受手续:

定义:pjob emp.empjob%type;

光标属性:%isopen
%rowcount(影响的行数卡塔尔  %found  %notfound

楷模1:使用游标格局输出emp表中的工作者编号和人名

declare

  cursor pc is

    select * from emp;

  pemp emp%rowtype;

begin

  open pc;

  loop

    fetch pc

      into pemp;

    exit when pc%notfound;

    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);

  end loop;

  close pc;

end;

范例2:写意气风发段PL/SQL程序,为部门号为10的职工涨薪给。

declare

  cursor pc(dno myemp.deptno%type) is

    select empno from myemp where deptno = dno;

  pno myemp.empno%type;

begin

  open pc(20);

  loop

    fetch pc

      into pno;

    exit when pc%notfound;

    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;

  end loop;

  close pc;

end;

 

第后生可畏就想开触发器,那样能在不关乎业务类别的代码景况下,达成监督。触发器分为“语句级触发器”和“行级触发器”。语句级是每三个言辞施行前后触发一遍操作,假使自身在每三个SQL语句试行后,把表名,时间,影响行写到记录表里就能够了。

2.异常

老大是程序设计语言提供的意气风发种成效,用来增过程序的强健性和容错性。

系统定义拾分 

no_data_found    (未有找到数据卡塔尔

too_many_rows   (select …into语句相称多少个行State of Qatar

zero_divide   ( 被零除)

value_error   (算术或退换错误State of Qatar

timeout_on_resource  (在守候能源时发出超时卡塔尔(قطر‎

榜样1:写出被0除的老大的plsql程序

declare

  pnum number;

begin

  pnum := 1 / 0;

exception

  when zero_divide then

    dbms_output.put_line('被0除');

  when value_error then

    dbms_output.put_line('数值转换错误');

  when others then

    dbms_output.put_line('其他错误');

end;

 

客户也足以自定义特别,在宣称中来定义特别

DECLARE

My_job   char(10);

v_sal   emp.sal%type;

No_data    exception;

cursor c1 is select distinct job from emp    order by job;

若是蒙受极其大家要抛出raise
no_data;

表率2:查询部门编号是50的职工

declare

  no_emp_found exception;

  cursor pemp is

    select t.ename from emp t where t.deptno = 50;

  pename emp.ename%type;

begin

  open pemp;

  fetch pemp

    into pename;

  if pemp%notfound then

    raise no_emp_found;

  end if;

  close pemp;

exception

  when no_emp_found then

    dbms_output.put_line('没有找到员工');

  when others then

    dbms_output.put_line('其他错误');

end;

 

但难点来了,在讲话触发器中,不可能得到该语句的行数,sql%rowcount
在触发器里报错。只好用行级触发器去总括行数!

3.囤积进程

仓储进程(Stored
Procedure)是在大型数据库系统中,豆蔻梢头组为了做到一定功效的SQL 语句集,经编写翻译后存储在数据库中,客户通过点名存款和储蓄进度的名字并提交参数(假如该存款和储蓄进度带有参数)来施行它。存款和储蓄进度是数据库中的三个重视对象,任何几个安顿优秀的数据库应用程序都应该用到存款和储蓄进程。

 

创设存款和储蓄进度语法:

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

AS

begin

        PLSQL子程序体;

End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

is

begin

        PLSQL子程序体;

End  过程名;

 

模范1:给内定的工作者涨100薪金,并打字与印刷出涨前和涨后的工薪

分析:大家须要运用含有参数的积累进度

create or replace procedure addSal1(eno in number) is

  pemp myemp%rowtype;

begin

  select * into pemp from myemp where empno = eno;

  update myemp set sal = sal + 100 where empno = eno;

  dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100));

end addSal1;

 

调用

begin

  -- Call the procedure

  addsal1(eno => 7902);     

  commit;

end;

 

代码构造:

4.囤积函数

create or replace function 函数名(Name in type, Name out type, ...) return 数据类型 is

  结果变量 数据类型;

begin



  return(结果变量);

end[函数名];

 

存款和储蓄进度和积存函数的区分

平日来讲,进程和函数的界别在于函数能够有一个再次来到值;而经过未有再次回到值。 

但进度和函数都能够通过out钦点一个或三个出口参数。我们可以动用out参数,在进度和函数中达成重回五个值。

 

榜样:使用存款和储蓄函数来询问内定工作者的年收入

create or replace function empincome(eno in emp.empno%type) return number is

  psal  emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal into psal from emp t where t.empno = eno;

  return psal * 12 + nvl(pcomm, 0);

end;

行使存款和储蓄进度来替换上边的例子

create or replace procedure empincomep(eno in emp.empno%type, income out number) is

  psal emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;

  income := psal*12+nvl(pcomm,0);

end empincomep;

 

调用:

declare

  income number;

begin

  empincomep(7369, income);

  dbms_output.put_line(income);

end;

 

漫天监察和控制数据行的成效富含: 二个日志表,包,种类。

5.触发器

数据库触发器是叁个与表相关联的、存款和储蓄的PL/SQL程序。每当叁个一定的数目操作语句(Insert,update,deleteState of Qatar在钦定的表上发出时,Oracle自动地试行触发器中定义的语句连串。 

 

日志表:记录指标表名,SQL施行起来、结束时间,影响行数,监察和控制数据行上的少数列音信。

1.触发器功能

l 数据确定 

l 施行复杂的安全性检查

l 做审计,追踪表上所做的数据操作等 

l 数据的备份和同步 

 

包:首假使3个存款和储蓄进程,

2.触发器的类别 

语句级触发器 :在钦定的操作语句操作在此以前或之后实践贰次,不管那条语句影响
     了有一点点行 。 

行级触发器(FO奥迪Q5 EACH ROW) :触发语句成效的每一条记下都被触发。在行级触
  发器中运用old和new伪记录变量,
识别值的气象。 

语法:

CREATE  [or REPLACE] TRIGGER  触发器名

   {BEFORE | AFTER}

   {DELETE | INSERT | UPDATE [OF 列名]}

   ON  表名

   [FOR EACH ROW [WHEN(条件) ] ]

declare

    ……

begin

   PLSQL 块 

End 触发器名

 

范例:插入工作者后打字与印刷一句话“叁个新工作者插入成功”

create or replace trigger testTrigger

  after insert on person  

declare

  -- local variables here

begin

  dbms_output.put_line('一个员工被插入');

end testTrigger;

 

模范:无法在平息时间插入职员和工人

create or replace trigger validInsertPerson

  before insert on person



declare

  weekend varchar2(10);

begin

  select to_char(sysdate, 'day') into weekend from dual;

  if weekend in ('星期一') then

    raise_application_error(-20001, '不能在非法时间插入员工');

  end if;

end validInsertPerson;

当实践插入时会报错

 

在触发器中触发语句与伪记录变量的值

触发语句

:old

:new

Insert

所有字段都是空(null)

将要插入的数据

Update

更新以前该行的值

更新后的值

delete

删除以前该行的值

所有字段都是空(null)

范例:判定工作者涨薪资之后的薪酬的值应当要超越涨薪金早前的工薪

create or replace trigger addsal4p

  before update of sal on myemp

  for each row

begin

  if :old.sal >= :new.sal then

    raise_application_error(-20002, '涨前的工资不能大于涨后的工资');

  end if;

end;

 

调用

update myemp t set t.sal = t.sal - 1;

 

语句开端积攒进度:用关联数组来记录目的表名和始发时间,把别的值清0.
行操作存款和储蓄进度:把涉及数组指标表所对应的记录数加1。
语句结束存款和储蓄进程:把关周密组指标表中计算的新闻写到日志表。

3.触发器实际利用

要求:使用体系,触发器来模拟mysql中自增效果与利益

队列: 用于转移日志表的主键

1. 始建连串

1、建立表

复制代码 代码如下:

create table user  (   

    id   number(6) not null,   

    name   varchar2(30)   not null primary key  

)  

 

2、营造系列SEQUENCE

代码如下:

create sequence user_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

 

代码:

2.创制自增的触发器

解析:创造二个基于该表的before
insert 触发器,在触发器中动用刚成立的SEQUENCE。

代码如下:

create or replace trigger user_trigger   

before insert on user  

for each row   

begin  

      select   user_seq.nextval  into:new.id from sys.dual ;   

end;  

 

日志表和连串:

3.测验效果

insert into itcastuser(name) values('aa');

commit;

insert into itcastuser(name) values('bb');

commit;

 

create table T_CSLOG( n_id NUMBER not null, tblname VARCHAR2(30) not null, sj1 DATE, sj2 DATE, i_hs NUMBER, u_hs NUMBER, d_hs NUMBER, portcode CLOB, startrq DATE, endrq DATE, bz VARCHAR2(100), n NUMBER)create index IDX_T_CSLOG1 on T_CSLOG (TBLNAME, SJ1, SJ2)alter table T_CSLOG add constraint PRIKEY_T_CSLOG primary key (N_ID) create sequence SEQ_T_CSLOGminvalue 1maxvalue 99999999999start with 1increment by 1cache 20cycle;

5.Java代码访谈Oracle对象

包代码:

1.java连接oracle的jar包

能够在虚构机中xp的oracle安装目录下找到jar包 :ojdbc14.jar

 

-包头create or replace package pck_cslog is --声明一个关联数组类型,它就是日志表的关联数组 type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type; --声明这个关联数组的变量。 cslog_tbl cslog_type; --语句开始。 procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2); --行操作 procedure oneachrow_cs(v_tblname t_cslog.tblname%type, v_type varchar2, v_code varchar2 := '', v_rq date := ''); --语句结束,写到日志表中。 procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2);end pck_cslog;--包体create or replace package body pck_cslog is --私有方法,把关联数组中的一条记录写入库里 procedure write_cslog(v_tblname t_cslog.tblname%type) is begin if cslog_tbl.exists(v_tblname) then insert into t_cslog values cslog_tbl (v_tblname); end if; end; --私有方法,清除关联数组中的一条记录 procedure clear_cslog(v_tblname t_cslog.tblname%type) is begin if cslog_tbl.exists(v_tblname) then cslog_tbl.delete(v_tblname); end if; end; --某个SQL语句执行开始。 v_type:语句类型,insert时为 i, update时为u ,delete时为 d procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is begin --如果关联数组中不存在,初始赋值。 否则表示,同时有insert,delete语句对目标表操作。 if not cslog_tbl.exists(v_tblname) then cslog_tbl(v_tblname).n_id := seq_t_cslog.nextval; cslog_tbl(v_tblname).tblname := v_tblname; cslog_tbl(v_tblname).sj1 := sysdate; cslog_tbl(v_tblname).sj2 := null; cslog_tbl(v_tblname).i_hs := 0; cslog_tbl(v_tblname).u_hs := 0; cslog_tbl(v_tblname).d_hs := 0; cslog_tbl(v_tblname).portcode := ' '; --初始给一个空格 cslog_tbl(v_tblname).startrq := to_date('9999', 'yyyy'); cslog_tbl(v_tblname).endrq := to_date('1900', 'yyyy'); cslog_tbl(v_tblname).n := 0; end if; cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname).bz || v_type || ','; ----第一个语句进入,显示1,如果以后并行,则该值递增。 cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n + 1; end; --每行操作。 procedure oneachrow_cs(v_tblname t_cslog.tblname%type, v_type varchar2, v_code varchar2 := '', v_rq date := '') is begin if cslog_tbl.exists(v_tblname) then --行数,代码,起、止时间 if v_type = 'i' then cslog_tbl(v_tblname).i_hs := cslog_tbl(v_tblname).i_hs + 1; elsif v_type = 'u' then cslog_tbl(v_tblname).u_hs := cslog_tbl(v_tblname).u_hs + 1; elsif v_type = 'd' then cslog_tbl(v_tblname).d_hs := cslog_tbl(v_tblname).d_hs + 1; end if; if v_code is not null and instr(cslog_tbl(v_tblname).portcode, v_code) = 0 then cslog_tbl(v_tblname).portcode := cslog_tbl(v_tblname).portcode || ',' || v_code; end if; if v_rq is not null then if v_rq  cslog_tbl(v_tblname).endrq then cslog_tbl(v_tblname).endrq := v_rq; end if; if v_rq  cslog_tbl(v_tblname).startrq then cslog_tbl(v_tblname).startrq := v_rq; end if; end if; end if; end; --语句结束。 procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is begin if cslog_tbl.exists(v_tblname) then cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname) .bz || '-' || v_type || ','; --语句退出,将并行标志位减一。 当它为0时,就可以写表了 cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n - 1; if cslog_tbl(v_tblname).n = 0 then cslog_tbl(v_tblname).sj2 := sysdate; write_cslog(v_tblname); clear_cslog(v_tblname); end if; end if; end;begin null;end pck_cslog;

2.数据库连接字符串

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

String username="scott";

String password="tiger";

测量检验代码: 

澳门太阳集团 1

 

绑定触发器:

3.兑现进度与函数的调用

有了以上代码后,想要监察和控制的二个目的表,只要求给它增加四个触发器,调用包里对应的寄放进程就能够。
假定笔者要监督 T_A 的表:

1.调用经过

多少个触发器:

1.经过定义
--统计年薪的过程

create or replace procedure proc_countyearsal(eno in number,esal out number)

as

begin

   select sal*12+nvl(comm,0) into esal from emp where empno=eno;

end;



--调用

declare

   esal number;

begin

   proc_countyearsal(7839,esal);

   dbms_output.put_line(esal);

end;

  

--语句开始前create or replace trigger tri_onb_t_a before insert or delete or update on t_adeclare v_type varchar2(1);begin if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if; pck_cslog.onbegin_cs('t_a', v_type);end;--语句结束后create or replace trigger tri_one_t_a after insert or delete or update on t_adeclare v_type varchar2(1);begin if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if; pck_cslog.onend_cs('t_a', v_type);end;--行级触发器create or replace trigger tri_onr_t_a after insert or delete or update on t_a for each rowdeclare v_type varchar2(1);begin if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if; if v_type = 'i' or v_type = 'u' then pck_cslog.oneachrow_cs('t_a', v_type, :new.name); --此处是把监控的行的某一列的值传入包体,这样最后会记录到日志表 elsif v_type = 'd' then pck_cslog.oneachrow_cs('t_a', v_type, :old.name); end if;end;
2.历程调用
    @Test
    public void testProcedure01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");

            callSt.setInt(1, 7839);

            callSt.registerOutParameter(2, OracleTypes.NUMBER);

            callSt.execute();

            System.out.println(callSt.getObject(2));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 


2.调用函数
 

测量检验成果:

1.函数定义
--统计年薪的函数

create or replace function fun_countyearsal(eno in number)

return number

as

   esal number:=0;

begin

  select sal*12+nvl(comm,0) into esal from emp where empno=eno;

  return esal;

end;



--调用

declare

   esal number;

begin

   esal:=fun_countyearsal(7839);

   dbms_output.put_line(esal);

end;

 

 

触发器建好了,能够测验插入删除了。先插入100行,再随意删除一些行。

2.函数调用
    @Test
    public void testFunction01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");

            callSt.registerOutParameter(1, OracleTypes.NUMBER);

            callSt.setInt(2, 7839);

            callSt.execute();

            System.out.println(callSt.getObject(1));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 


4.游标援引的java测验
 

declare i number;begin for i in 1 .. 100 loop insert into t_a values (i, i || 'shenjunjian'); end loop; commit; delete from t_a where id  79; delete from t_a where id  40; commit;end;

1.概念进度,并回到援引型游标

--定义过程

create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)

as

begin

  open empList for select * from emp where deptno = dno;

end;



--pl/sql中调用

declare

  mycursor_c sys_refcursor;

  myempc emp%rowtype;

begin

  proc_cursor_ref(20,mycursor_c);



  loop

    fetch mycursor_c into myempc;

    exit when mycursor_c%notfound;

    dbms_output.put_line(myempc.empno||','||myempc.ename);

  end loop;

  close mycursor_c;

end;

 

clob列,还足以来得监察和控制删除的行:

2.java代码调用游标类型的out参数

    @Test
    public void testFunction() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");

            callSt.setInt(1, 20);

            callSt.registerOutParameter(2, OracleTypes.CURSOR);

            callSt.execute();

            ResultSet rs = ((OracleCallableStatement) callSt).getCursor(2);

            while (rs.next()) {

                System.out.println(rs.getObject(1) + "," + rs.getObject(2));

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 

相互时,在bz列中,大概会有周围音讯:

i,i,-i,-i ,这意味同一时候有2个语句在插入目的表。

i,d,-d,-i 表示在插入时,有三个去除语句也在执行。

当平台四个人在用时,制止不了有同不平时间操作同一张表的状态,通过这一个列的值,能够洞察到数据库的实市场价格况!

感谢阅读,希望能扶植到大家,多谢大家对本站的援救!

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图