create_proc.sql

来自「orale培训教材包括了所有的sql说明和实例」· SQL 代码 · 共 44 行

--建立table test10;
drop table test10_log;
drop test10;
create table test10
(name varchar2(100));

--建立对于table test10的log表test10_log
 create table test10_log
 (
  newname varchar2(100),
  oldname varchar2(100),
  timestype date,
  username varchar2(20));

------------------------------------------------建立存储过程test12--------
--向test10插入数据
--select text from user_source where name='TEST12';

/
CREATE OR REPLACE
procedure test12 is
begin
insert into test10(name) values('John');
commit;
end;

/
------------------------------------------------建立触发器update_audit--------
create or replace 
TRIGGER update_audit
  AFTER UPDATE or insert or delete on test10
  FOR EACH ROW

BEGIN
  insert into test10_log(newname,oldname,timestype,username) values(:new.name,:old.name,sysdate,user);
END;

/
delete from test10;
delete from test10_log;
commit;
desc test10;
desc test10_log;