create or replace package f is
PROCEDURE PRC_修改信息(PRM_DEPTNO IN NUMBER, PRM_ERRORMSG OUT VARCHAR2);
PROCEDURE PRC_记录日志(PRM_INFO IN VARCHAR2, PRM_MODEL IN VARCHAR2);
end;create or replace package body f is
PROCEDURE PRC_修改信息(PRM_DEPTNO IN NUMBER, PRM_ERRORMSG OUT VARCHAR2) IS
cursor cur_emp is
select JOB, DEPTNO, EMPNO from EMP where deptno=&no;
emp_JOB emp.JOB%type;
emp_DEPTNO emp.DEPTNO%type;
emp_EMPNO emp.DEPTNO%type;
begin
open cur_emp;
loop
fetch cur_emp
into emp_JOB, emp_DEPTNO, emp_EMPNO;
if cur_emp%notfound THEN
dbms_output.put_line('没有该部门:');
end if;
IF emp_JOB = 'PRESIDENT' THEN
UPDATE EMP SET SAL = SAL + SAL * 0.1 where empno = emp_EMPNO;
ELSIF emp_JOB = 'MANAGER' THEN
UPDATE EMP SET SAL = SAL + SAL * 0.2 where empno = emp_EMPNO;
ELSE
UPDATE EMP SET SAL = SAL + SAL * 0.15 where empno = emp_EMPNO;
END IF;
end loop;
close cur_emp;
EXCEPTION
WHEN OTHERS THEN
PRM_ERRORMSG := 'PKG_f.PRC_修改信息:修改员工编号为' || TO_CHAR(PRM_DEPTNO) ||
'的员工的基本信息时发生错误:' || SQLERRM;
PRC_记录日志(PRM_ERRORMSG, 'PRC_修改信息');
COMMIT;
END PRC_修改信息;
PROCEDURE PRC_记录日志(PRM_INFO IN VARCHAR2, PRM_MODEL IN VARCHAR2) IS
Pragma Autonomous_Transaction;
BEGIN
INSERT INTO LOG VALUES (LOG_NO.NEXTVAL, PRM_INFO, SYSDATE, PRM_MODEL);
COMMIT;
END PRC_记录日志;
END;
PROCEDURE PRC_修改信息(PRM_DEPTNO IN NUMBER, PRM_ERRORMSG OUT VARCHAR2);
PROCEDURE PRC_记录日志(PRM_INFO IN VARCHAR2, PRM_MODEL IN VARCHAR2);
end;create or replace package body f is
PROCEDURE PRC_修改信息(PRM_DEPTNO IN NUMBER, PRM_ERRORMSG OUT VARCHAR2) IS
cursor cur_emp is
select JOB, DEPTNO, EMPNO from EMP where deptno=&no;
emp_JOB emp.JOB%type;
emp_DEPTNO emp.DEPTNO%type;
emp_EMPNO emp.DEPTNO%type;
begin
open cur_emp;
loop
fetch cur_emp
into emp_JOB, emp_DEPTNO, emp_EMPNO;
if cur_emp%notfound THEN
dbms_output.put_line('没有该部门:');
end if;
IF emp_JOB = 'PRESIDENT' THEN
UPDATE EMP SET SAL = SAL + SAL * 0.1 where empno = emp_EMPNO;
ELSIF emp_JOB = 'MANAGER' THEN
UPDATE EMP SET SAL = SAL + SAL * 0.2 where empno = emp_EMPNO;
ELSE
UPDATE EMP SET SAL = SAL + SAL * 0.15 where empno = emp_EMPNO;
END IF;
end loop;
close cur_emp;
EXCEPTION
WHEN OTHERS THEN
PRM_ERRORMSG := 'PKG_f.PRC_修改信息:修改员工编号为' || TO_CHAR(PRM_DEPTNO) ||
'的员工的基本信息时发生错误:' || SQLERRM;
PRC_记录日志(PRM_ERRORMSG, 'PRC_修改信息');
COMMIT;
END PRC_修改信息;
PROCEDURE PRC_记录日志(PRM_INFO IN VARCHAR2, PRM_MODEL IN VARCHAR2) IS
Pragma Autonomous_Transaction;
BEGIN
INSERT INTO LOG VALUES (LOG_NO.NEXTVAL, PRM_INFO, SYSDATE, PRM_MODEL);
COMMIT;
END PRC_记录日志;
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货