CREATE OR REPLACE
PROCEDURE debug_time
(
v_system_id in number,
v_procedure_id in number,
v_debug_name in varchar2,
v_debug_time in date
)
IS
--------------------------------------------------------------------------------
-- 功能:調試程式運行時間
-- 版本:Ver 1.0
-- Marvin Hong
-- 2003-02-21 08:20
--------------------------------------------------------------------------------
v_debug_flag number;
BEGIN
select count(*) into v_debug_flag from s_program_option_info
where system_id=v_system_id and program_id=v_procedure_id
and option_name='DEBUG_TIME' and flag=1;
if v_debug_flag=1 then
insert into s_debug_time_info
(system_id,program_id,parameter,value)
values
(v_system_id,v_procedure_id,v_debug_name,v_debug_time);
end if;
commit;
END; -- ProcedureCREATE OR REPLACE TRIGGER save_no_status
BEFORE INSERT OR UPDATE
ON s_no_status_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if inserting then
insert into s_no_status_info_history
(system_id,no,status,in_date)
values
(:new.system_id,:new.no,:new.status,:new.in_date);
else
insert into s_no_status_info_history
(system_id,no,status,in_date)
values
(:old.system_id,:old.no,:new.status,:new.in_date);
end if;
end;
PROCEDURE debug_time
(
v_system_id in number,
v_procedure_id in number,
v_debug_name in varchar2,
v_debug_time in date
)
IS
--------------------------------------------------------------------------------
-- 功能:調試程式運行時間
-- 版本:Ver 1.0
-- Marvin Hong
-- 2003-02-21 08:20
--------------------------------------------------------------------------------
v_debug_flag number;
BEGIN
select count(*) into v_debug_flag from s_program_option_info
where system_id=v_system_id and program_id=v_procedure_id
and option_name='DEBUG_TIME' and flag=1;
if v_debug_flag=1 then
insert into s_debug_time_info
(system_id,program_id,parameter,value)
values
(v_system_id,v_procedure_id,v_debug_name,v_debug_time);
end if;
commit;
END; -- ProcedureCREATE OR REPLACE TRIGGER save_no_status
BEFORE INSERT OR UPDATE
ON s_no_status_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if inserting then
insert into s_no_status_info_history
(system_id,no,status,in_date)
values
(:new.system_id,:new.no,:new.status,:new.in_date);
else
insert into s_no_status_info_history
(system_id,no,status,in_date)
values
(:old.system_id,:old.no,:new.status,:new.in_date);
end if;
end;
解决方案 »
- 列不能外部连接到子查询
- 请问有没有使用pl/sql developer的朋友知道为什么触发器无法右键"新建"?
- EBS Formbuilder 问题
- 请教跨数据库查询的问题
- 如何把一个表的某列的值给另外一个表的某列值,条件是两个表的某一个字段值相等
- 关于ALL_OBJECTS的奇怪问题
- oracle 归档日志满了,sqlplus中无法执行sql语句.
- oracle中怎样将字段建成象SQL2000中的ID自动+1的字段
- 怎样利用序列sequence在表中插入连续的id?
- 安装oracle9i后,8080端口被什么程序占用?如何关掉?
- solaris下安装oracle时光盘弹不出来?
- 有没有办法在linux下自动启动oracle的监听服务???
--1 USERADD 会员触发器
--
CREATE OR REPLACE TRIGGER USERADD
BEFORE INSERT ON MEMBERRG
FOR EACH ROW
BEGIN
SELECT LPAD(USERID.NEXTVAL,4,'0') INTO :NEW.NUM FROM DUAL;
END;create procedure name(a in varchar2)
as
--定义部分;
begin
--语句部分;
end;
/
CREATE OR REPLACE procedure minimum(v1 number, v2 number) as
BEGIN
IF v1 < v2 THEN
RETURN v1;
ELSE
RETURN v2;
END IF;
END;**************************************************************** CREATE OR REPLACE TRIGGER EMP_AIUR
AFTER INSERT OR UPDATE ON EMP
FOR EACH ROW
BEGIN
IF (:NEW.sal > 100000) THEN
IF INSERTING THEN
RAISE_APPLICATION_ERROR
(-20000, 'New salary limits are limited to 100000');
ELSE
RAISE_APPLICATION_ERROR
(-20000, 'You cannot increase a salary beyond 100000');
END IF;
END IF;
END;
或者在utrledit中写也比在sqlplus中写好啊。