2017-07-21异常练习

declare
v_empno emp.empno%TYPE :=&empno;
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
IF v_sal<=1500 THEN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');
ELSE
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
END IF;
end;



select * from emp;

DECLARE
v_deptno dept.deptno%TYPE :=&deptno;
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
/* -2292 是违反一致性约束的错误代码 */
BEGIN
DELETE FROM dept WHERE deptno=v_deptno;
EXCEPTION
WHEN deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM)
END;

 

declare
V_empno emp.empno%type :=&empno;
no_result exception;
begin
update emp set sal=sal+100 where V_empno=empno;
if sql%notfound then
raise no_result;
end if;
exception
when no_result then
dbms_output.put_line('你的数据更新语句失败了！')
when others then
dbms_output.put_line(SQLCODE||'---'||SQLERRM)
end;


declare
v_deptid departments.department_id%type := &no;
v_dname departments.department_name%type;
begin
select department_name into v_dname from departments
where department_id = v_deptid;
dbms_output.put_line(v_dname);
exception
when others then
raise_application_error(-20001 , 'department'||v_deptid||' does not exists');
end;



select count(*) from emp ;

select * from emp

CREATE OR REPLACE FUNCTION get_salary(
Dept_no NUMBER, Emp_count OUT NUMBER)
RETURN NUMBER
IS
V_sum NUMBER;

SELECT SUM(sal), count(*) INTO V_sum, emp_count
FROM emp WHERE deptno=dept_no;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=get_salary(30, v_num);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人 数：'||v_num);
END;

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=get_salary(emp_count => v_num, dept_no => 30);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);
END;