afterImg VARCHAR2(2000); action VARCHAR2(10); action_module VARCHAR2(10); info_category VARCHAR2(20); BEGIN
action_module := 'IM'; info_category := 'duty';
befImg := '[DUTY_ID]' || :old.duty_id || '[DUTY_NAME]' || :old.duty_name; afterImg := '[DUTY_ID]' || :new.duty_id || '[DUTY_NAME]' || :new.duty_name;
IF (:old.duty_id is null) THEN action := 'Insert'; befImg := '';
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);
ELSIF (:new.duty_id is null) THEN action := 'Delete'; afterImg := '';
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);
ELSE
action := 'Update';
IF(befImg != afterImg) THEN
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);
END IF; END IF; EXCEPTION
WHEN OTHERS then befImg := ''; end; /
mysql下：
CREATE TRIGGER MOCHA_IM_DUTY_TRG_INSERT BEFORE INSERT ON MOCHA_IM_DUTY FOR EACH ROW BEGIN
DECLARE befImg VARCHAR(2000); DECLARE afterImg VARCHAR(2000); DECLARE action VARCHAR(10); DECLARE action_module VARCHAR(10); DECLARE info_category VARCHAR(20);
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND begin
set befImg = ''; end;
set action_module = 'IM'; set info_category = 'duty';
set afterImg =CONCAT( '[DUTY_ID]' , new.duty_id , '[DUTY_NAME]' , new.duty_name);
set action = 'Insert';
set befImg = '';
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);
end; /
CREATE TRIGGER MOCHA_IM_DUTY_TRG_DEL BEFORE DELETE ON MOCHA_IM_DUTY FOR EACH ROW BEGIN
DECLARE befImg VARCHAR(2000); DECLARE afterImg VARCHAR(2000); DECLARE action VARCHAR(10); DECLARE action_module VARCHAR(10); DECLARE info_category VARCHAR(20);
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND begin
set befImg = ''; end;
set action_module = 'IM'; set info_category = 'duty';
set befImg = CONCAT('[DUTY_ID]' , old.duty_id , '[DUTY_NAME]' , old.duty_name); set action = 'Delete'; set afterImg = '';
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);
end; /
CREATE TRIGGER MOCHA_IM_DUTY_TRG_UPDATE BEFORE UPDATE ON MOCHA_IM_DUTY FOR EACH ROW BEGIN
DECLARE befImg VARCHAR(2000); DECLARE afterImg VARCHAR(2000); DECLARE action VARCHAR(10); DECLARE action_module VARCHAR(10); DECLARE info_category VARCHAR(20);
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND begin
set befImg = ''; end;
set action_module = 'IM'; set info_category = 'duty';
set befImg = CONCAT('[DUTY_ID]' , old.duty_id , '[DUTY_NAME]' , old.duty_name); set afterImg =CONCAT( '[DUTY_ID]' , new.duty_id , '[DUTY_NAME]' , new.duty_name);
IF (old.duty_id is null) THEN set action = 'Insert';
set befImg = '';
ELSEIF (new.duty_id is null) THEN set action = 'Delete'; set afterImg = ''; ELSE
set action = 'Update'; IF(befImg != afterImg) THEN
INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);
END IF; END IF; end; /
在声过程和函数时注意过程支持inout参数函数不支持，mysql下函数是returns
Oracle下过程：
create or replace procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR2) IS begin
execute immediate ' insert into MOCHA_FE_DOC_BODY_TEMP '||v_sql; end MOCHA_FE_OPEN_DOC_SP; /
Mysql下：
create procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR(200))