Oracle替代触发器的方法,Oracle （替代触发器）

create or replace trigger 名

instrad of delete from 视图

begin

执行输出

end;

比如你写删除后 触发器输出该提示后

例如:begin

dbms_output.put_line(:old.ename)

end；

这里写上这个提示后 如果执行 delete from v_emp2；的话 提示是提示 (但是实际不删除视图中内容)

如果你想真正删除表中内容的话

在begin里面执行真正的 delete 删除表中内容

DML触发器

create trigger 名

before|after insert or update or delete on 表名

referencing old as o new as n

for each row

declare

--声明

begin

--执行

:new :old

inserting updating deleting

end 名;

替代触发器 只可以添加在视图上

create trigger 名

instead of insert or update or delete on 表名

referencing old as o new as n

for each row

declare

--声明

begin

--执行

:new :old

inserting updating deleting

end 名;

drop table students cascade constraint;

create table students(

id int primary key,

name varchar2(20));

insert into students values(1001,'TOM');

insert into students values(1002,'CAT');

insert into students values(1003,'TOMCAT');

drop table s1;

create table s1(

sid int,

kid int,

score int,constraint fk_s1_sid foreign key(sid) references students(id));

insert into s1 values(1001,1,80);

insert into s1 values(1001,2,98);

insert into s1 values(1002,1,88);

drop table s2;

create table s2(

sid int,

kid int,

score int,constraint fk_s2_sid foreign key(sid) references students(id));

insert into s2 values(1002,2,68);

insert into s2 values(1003,1,100);

insert into s2 values(1003,2,45);

级联更新

1 查询到那些表和主表相关联

1查看当前主键约束的信息

select constraint_name,constraint_type,table_name

from user_constraints

where table_name='STUDENTS'

2 查看外键表

select table_name,constraint_name

from user_constraints

where r_constraint_name='SYS_C005161';

2 查看到表中相关联的字段

select column_name from user_cons_columns where constraint_name='FK_S1_SID';

create or replace trigger tri_students_update

after update on students

for each row

declare

v_update varchar2(2000);

begin

for r in (select table_name tname,constraint_name cname

from user_constraints

where r_constraint_name in (select constraint_name

from user_constraints

where table_name='STUDENTS') )

loop

for r2 in (select column_name cname from user_cons_columns where constraint_name=r.cname)

loop

v_update:='update '||r.tname||' set '||r2.cname||'='||:new.id ||' where ' ||r2.cname||'='||:old.id;

dbms_output.put_line(v_update);

execute immediate v_update;

end loop;

end loop;

end;

========================================

execute immediate

=================包

1包头

create or replace package pck_test is

--函数

function userdate return varchar2 ;

--过程

procedure p_test;

end pck_test;

2包体

create or replace package body pck_test as

--函数实现

function userdate return varchar2 is

begin

return to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day');

end userdate;

--过程的实现

procedure p_test as

begin

dbms_output.put_line('procedure ......package.');

end p_test;

end pck_test;

==========================JAVA调用存储过程和函数

1 创建一个无参数的存储过程

创建EMP2空表 如果表不存在创建，存在则清空内容记录

create or replace procedure p1 is

begin

begin

execute immediate 'create table emp2 as select * from emp where 1<>1';

exception

when others then

null;

end;

execute immediate 'delete from emp2';

end p1;

2 创建一个带IN参数的存储过程

输入员工编号修改其员工工作为经理；

create or replace procedure p2(in_empno int) is

begin

update emp set job='MANAGER' where empno=in_empno;

end;

3 创建一个带OUT参数的存储过程

返回EMP表中一共有多少条记录

create or replace procedure p3(out_count out int) is

begin

select count(*) into out_count from emp;

end;

4 创建一个带IN和OUT参数的存储过程

输入部门编号删除部门员工返回删除数量

create or replace procedure p4(in_deptno in int,out_count out int) is

begin

delete from emp where deptno=in_deptno;

out_count:=sql%rowcount;

end;

5 创建一个存储过程返回一个结果集

输入起始行和终止行返回结果集

create or replace procedure p5(in_start int,in_stop int,out_cursor out sys_refcursor) is

begin

open out_cursor for

select * from (select rownum r,emp.* from emp) where r between in_start and in_stop;

end;

6 创建一个函数返回当前时间

输入一个自定义的格式 返回指定格式的时间

create or replace function p6(in_date_format varchar2) return varchar2 as

begin

return to_char(sysdate,in_date_format);

end;