function Get_Operation
(
tablename in varchar2, --表名
operation in number --操作类型,1-删除、2-添加、3-编辑
)
return varchar2 as RetStr varchar2(4000);
temp varhcar2(100);
begin
temp := '';
if operation = 1 then --删除操作
temp := concat('%delete%from%',tablename);
temp := concat(temp,'%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
if operation = 2 then --添加操作
temp := concat('%insert%into%',tablename);
temp := concat(temp,'%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
if operation = 3 then --编辑操作
temp := concat('%update%',tablename);
temp := concat(temp,'%set%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
return(RetStr);
exception
WHEN OTHERS THEN
RetStr := "null";
return(RetStr);
end;系统提示:
第7行:必须说明标志符'varchar2'
第7行:item ignored
在所有用到temp变量的行中:此表达式的类型说明不完整或格式不正确
在select语句中:"temp",无效的标志符 (指我的....like temp ...中的temp)
(
tablename in varchar2, --表名
operation in number --操作类型,1-删除、2-添加、3-编辑
)
return varchar2 as RetStr varchar2(4000);
temp varhcar2(100);
begin
temp := '';
if operation = 1 then --删除操作
temp := concat('%delete%from%',tablename);
temp := concat(temp,'%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
if operation = 2 then --添加操作
temp := concat('%insert%into%',tablename);
temp := concat(temp,'%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
if operation = 3 then --编辑操作
temp := concat('%update%',tablename);
temp := concat(temp,'%set%');
select sql_text into RetStr from v$sqltext where rownum=1 and lower(sql_text) like temp order by address;
RetStr := concat(RetStr,';');
end if;
return(RetStr);
exception
WHEN OTHERS THEN
RetStr := "null";
return(RetStr);
end;系统提示:
第7行:必须说明标志符'varchar2'
第7行:item ignored
在所有用到temp变量的行中:此表达式的类型说明不完整或格式不正确
在select语句中:"temp",无效的标志符 (指我的....like temp ...中的temp)
解决方案 »
- 实体化试图(Materialized View)和一般的view有啥区别啊
- 小弟请教个sql
- 如何通过SQL语句实现添加查询结果列的查询?
- CSDN在Java板块被锁
- oracle 9i tns:监听进程不能解析
- 物化视图是不是不支持子查询?
- 关于事务和触发器的问题,请高手指教!
- 高人请指点,这个结果SYS_LOB0000031469C00016$$是什么东西,如何才能释放该空间。急!!!!!
- OEM中的数据库节点没有了,怎么加上?
- oracle的函数Replacewild如何解释???????????????????????????????
- 请问listener为什么会时好时坏
- 我在oracle中发现dbms._output.put_line 不能输出大于255的字符串?
拼写错误.................
(序列已经建好并确保成功,我要写的是语句级触发)CREATE OR REPLACE TRIGGER INF_APPLY_Trigger --表inf_apply的触发
after delete or insert or update on INF_APPLY
begin
if delete then
insert into spy_sql select id_seq.nextval,'delete' from dual;
end if;
if insert then
insert into spy_sql select id_seq.nextval,'insert' from dual;
end if;
if update then
insert into spy_sql select id_seq.nextval,'update' from dual;
end if;
end;
/我这样把三种事件的触发写在一起难道不对??如果不对,请问该怎么写???
还有,为什么我在on inf_apply 后面加上 for each statement 系统确提示“要加row”,难道语句级的触发不是这样写??