Oracle （替代触发器）

xcohokuewe

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;