1student表日志记录

1student表日志记录

--创建student表
create table student(
sno number primary key,
sname varchar2(20)
);

insert into student values(1,'zhangsan1');
insert into student values(2,'zhangsan2');
insert into student values(3,'zhangsan3');
insert into student values(4,'zhangsan4');
insert into student values(5,'zhangsan5');
insert into student values(6,'zhangsan6');
insert into student values(7,'zhangsan7');





insert into student values(11,'zhangsan11');
insert into student values(12,'zhangsan12');
insert into student values(13,'zhangsan13');
insert into student values(14,'zhangsan14');

--创建log表
create table log_g(
id number primary key,
beforeName varchar2(20),
afterName varchar2(20),
oper char(7) check (oper in('update','delete','insert')),
sno number,
thisDate date default sysdate
);

--创建序列
create sequence log_id;
--触发器
create or replace trigger t_delete
before delete on student for each row
declare
v_beforeName varchar2(20):=''''||:old.sname||'''';
v_afterName varchar2(20):=''''||:new.sname||'''';
v_sno number:=:old.sno;
begin
dbms_output.put_line('已删除');
dbms_output.put_line('beforeName='||v_beforeName);
dbms_output.put_line('afterName='||v_afterName);
dbms_output.put_line('sno='||v_sno||'----');
insert into log_g values(log_id.nextval,v_beforeName,v_afterName,'delete',v_sno,sysdate);
end;
/



create or replace trigger t_update
before update on student for each row
declare
v_beforeName varchar2(20):=''''||:old.sname||'''';
v_afterName varchar2(20):=''''||:new.sname||'''';
v_sno number:=:old.sno;
begin
dbms_output.put_line('已修改');
dbms_output.put_line('beforeName='||v_beforeName);
dbms_output.put_line('afterName='||v_afterName);
dbms_output.put_line('sno='||v_sno||'----');
insert into log_g values(log_id.nextval,v_beforeName,v_afterName,'update',v_sno,sysdate);
end;
/







create or replace trigger t_insert
before insert on student for each row
declare
v_beforeName varchar2(20):=''''||:old.sname||'''';
v_afterName varchar2(20):=''''||:new.sname||'''';
v_sno number:=:old.sno;
begin
dbms_output.put_line('已插入');
dbms_output.put_line('beforeName='||v_beforeName);
dbms_output.put_line('afterName='||v_afterName);
dbms_output.put_line('sno='||v_sno||'----');
insert into log_g values(log_id.nextval,v_beforeName,v_afterName,'insert',v_sno,sysdate);
end;
/



2
--创建一个包 myPackage
create or replace package myPackage
as 
type v_cursor is ref cursor;
end myPackage;
/



create or replace procedure p_a(
sno_s number,
p_cursor out myPackage.v_cursor)
as

v_sql varchar2(1000);
begin
v_sql:='select * from log_g where sno= '|| sno_s ;
--||' and thisDate between trunc(sysdate,'dd') and sysdate';
open p_cursor for v_sql;
end;
/



declare 
p_cursor myPackage.v_cursor;
v_g log_g%rowtype;
begin
p_a(&sno,p_cursor);
loop
fetch p_cursor into v_g;
exit when p_cursor%notfound;
dbms_output.put_line('beforeName='||v_g.beforeName);
dbms_output.put_line('afterName='||v_g.afterName);
dbms_output.put_line('oper='||v_g.oper);
dbms_output.put_line('sno='||v_g.oper);
dbms_output.put_line('thisDate='||v_g.thisDate);
dbms_output.put_line('======');
end loop;
end;
/



--3job作业
create or replace procedure p_del
as
begin
delete from log_g;
end;
/



declare
v_job1 number;
begin
dbms_job.submit(v_job1,'p_del;',(trunc(sysdate,'dd'))+23,'sysdate+1');
end;
/
手动运行job
execute dbms_job.run(1);