Oracle （替代触发器）

create or replace trigger 名 instrad of delete from 视图 begin 执行输出end;比如你写删除后 触发器输出该提示后 例如:begindbms_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 nfor each rowdeclare--声明begin--执行 :new  :old inserting updating  deletingend 名;替代触发器 只可以添加在视图上create trigger 名 instead of   insert or update or delete on 表名referencing old as o new as nfor each rowdeclare--声明begin--执行 :new  :old inserting updating  deletingend 名;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_updateafter update on studentsfor each rowdeclare 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 isbegin return to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day');end userdate;--过程的实现procedure p_test asbegin dbms_output.put_line('procedure ......package.');end p_test;end pck_test;==========================JAVA调用存储过程和函数1 创建一个无参数的存储过程    创建EMP2空表 如果表不存在创建，存在则清空内容记录create or replace procedure p1 isbegin    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) isbegin update emp set job='MANAGER' where empno=in_empno;end;3 创建一个带OUT参数的存储过程   返回EMP表中一共有多少条记录create or replace procedure p3(out_count out int) isbegin select count(*) into out_count from emp;end;4 创建一个带IN和OUT参数的存储过程   输入部门编号删除部门员工返回删除数量create or replace procedure p4(in_deptno in int,out_count out int) isbegin 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) isbegin 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 asbegin return to_char(sysdate,in_date_format);end;