DECLARE
v_deptno emp.deptno%TYPE :=&deptno;
v_sal emp.sal%TYPE;
v_empno emp.empno%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE deptno= v_deptno;
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;
EXCEPTION
WHEN Dup_val_on_index THEN
DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_deptno||'的员工');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||v_deptno||SQLERRM); ---1422返回多行
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||v_deptno||SQLERRM); --100表示NO_DATA_FOUND
END;
select * from dept
DECLARE
v_deptno dept.deptno%TYPE :=&deptno;
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, 100);
/* -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); --sqlcode数据库返回的操作码 SQLERRM返回错误码信息 '程序运行错误!请使用游标'
END;
DECLARE
v_deptno dept.deptno%TYPE :=&deptno;
no_result EXCEPTION;
no_result1 EXCEPTION;
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
BEGIN
-- DELETE FROM dept WHERE deptno=v_deptno;
UPDATE emp SET sal=sal+100 WHERE deptno=v_deptno;
IF SQL%FOUND THEN
RAISE no_result;
elsIF SQL%NOTFOUND THEN
RAISE no_result1;
END IF;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'这是1'||SQLERRM);
WHEN no_result1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'这是2'||SQLERRM); --1表示用户没有定义
WHEN deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
Exception
when osi_general_error then
DBMS_OUTPUT.PUT_LINE(SQLCODE||'这是1'||SQLERRM);
when OTHERS THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
select* from emp
declare
v_empno emp.empno%type := &empno;
v_ename emp.ename%type;
begin
select ename into v_ename from emp
where empno = v_empno;
dbms_output.put_line(SQLCODE||'----'||v_ename); --0表示没有异常
exception
when others then
raise_application_error(-20001 , 'emp'||v_empno||' does not exists');
end;