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;