稽核存储过程
CREATE OR REPLACE PROCEDURE bi_audit_status_log_p(vs_gather_date varchar2,vs_dept_id varchar2,
vs_account varchar2,
vs_old_status number,
vs_new_status number) IS
-------------------------------------------------------------------
--营业收入数据稽核记录日志信息
-------------------------------------------------------------------
BEGIN
DECLARE
vs_username varchar2(20);
BEGIN
--操作人姓名
SELECT t.name
INTO vs_username
FROM employee t
WHERE t.account = vs_account;
INSERT INTO bi_audit_detail_log
(order_id,
dept_id,
dept_name,
area_id,
area_name,
gather_date,
audit_status1,
audit_status2,
audit_account,
audit_person,
audit_date)
SELECT to_char(sysdate, 'yyyymmdd-hh24miss'),
vs_dept_id,
d.dept_name,
d.belongs_area,
a.area_name,
vs_gather_date,
vs_old_status,
vs_new_status,
vs_account,
vs_username,
SYSDATE
FROM dept_info_t d, area_info_t a
WHERE d.belongs_area = a.area_id
AND d.dept_id = vs_dept_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line('营业收入数据稽核记录日志信息失败!Reason:' || SQLERRM);
END;
END bi_audit_status_log_p;
摘自 mywhy