05Oracle第五课

SQLCODE：函数，返回错误的代码 2、错误消息：

SQLERRM：函数，返回错误消息文本

--输入员工编号、姓名、工资和部门号，添加新员工 --体会程序健壮性的重要性 --异常是处理运行时错误的 --通过自定义异常，处理工资上限 declare

v_empno emp.empno%type; v_ename emp.ename%type; v_sal emp.sal%type; v_deptno emp.deptno%type; e_fk exception;

pragma exception_init(e_fk,-2291); e_sal exception;

pragma exception_init(e_sal,-20001); begin v_empno := &empno;

v_ename := &ename; v_sal := &sal;

v_deptno := &deptno; if v_sal > 5000 then raise_application_error(-20001,'工资超限'); end if;

insert into emp(empno,ename,sal,deptno) values(v_empno,v_ename,v_sal,v_deptno);

commit; exception when dup_val_on_index then

-- -1

dbms_output.put_line('已经存在'||v_empno||'号员工了'); when e_fk then

dbms_output.put_line(v_deptno||'号部门还没有创建，请更换新的部门号'); -- -2291 when e_sal then -- -20001 dbms_output.put_line('工资不能超过5000'); when others then dbms_output.put_line(SQLERRM);

-- 其他所有的

end;

完整范例： declare v_empno v_ename v_deptn

emp.empno%type; emp.ename%type;

e_notnull exception;

e_fk exception;

e_hiredate exception; e_operation exception;

pragma exception_init(e_notnull,-1400); pragma exception_init(e_fk,-2291);

pragma exception_init(e_hiredate,-20001);

pragma exception_init(e_operation,-20002); begin

v_empno:=&empno; v_ename:=&ename; v_deptno:=&deptno;

v_hiredate := to_date('&hiredate','YYYY-MM-DD'); if to_char(sysdate,'day') = '星期三' then

raise_application_error(-20002,'非法操作时间'); end if;

if v_hiredate > sysdate then raise_application_error(-20001,'非法雇佣日期'); end if;

insert into emp(empno,ename,hiredate,deptno) values(v_empno,v_ename,v_hiredate,v_deptno); if SQL%found then

dbms_output.put_line('OK'); commit; else

dbms_output.put_line('ERROR'); end if;

exception when dup_val_on_index then --捕获预定义异常 dbms_output.put_line('此员工编号已经存在');

when e_notnull then

dbms_output.put_line('员工名不能为空'); when e_fk then

dbms_output.put_line('部门号不存在');

when e_hiredate then

dbms_output.put_line('雇佣日期不能大于当前时间'); when others then

dbms_output.put_line('发生了如下错误:'||SQLERRM); end; /

异常的传播规律：

在一个程序中可能发生错误的地方： 1、执行部分

如果在执行部分发生错误或者显式引发了异常，首先确定当前块是否有相应的 异常处理程序，如果有则异常处理程序处理此异常，如果没有，就把这个异常 传播到此块的父块，如果没有父块，则传播到调用环境，一旦传播到调用环境 如果调用环境没有处理，则导致程序非正常的终止运行。 2、声明部分

如果在声明部分发生错误或者显式引发了异常，则立即将此异常传播到父块， 即使本块有相应的处理程序，也不起作用。如果没有父块，则传播到调用环境 一旦传播到调用环境，如果调用环境没有处理，则导致程序非正常的终止运行。 3、异常处理

如果在异常处理部分发生错误或者显式引发了异常，则立即传播到块外，且一 次只能引发一次异常

练习题

第一部分：

1、PL/SQL块的基本结构

2、下面定义常量和变量的方法哪个是正确的?不正确的请说明原因。

1、v_ename varchar2(10);

2、v_sal,v_comm number(6,2); 3、v_sal number(6,2) not null;

4、c_tax constant number(6,2) default 0.17; 5、%sal number(6,2);

6、v_comm emp.comm%type;

3、请根据结果确定以下变量的数据类型 1. v1:=1; 2. v2:=null;

3. v3:=(v_comm is null);

4. v4:=SYSDATE; 5. v5:='hello world'; 6. v6:=SYSDATE-hiredate; 4、请阅读下面的语句：

declare v1 number(6); v2 number(6); begin

v1:=100; v2:=200;

declare

v1 number(6); v3 number(6);

begin v1:=110;

v2:=210;

v3:=300; end; v3:=400; end;

执行了以上的代码后，请确定子块和主块的变量结果

第二部分：

1、编写PL/SQL块，使用DBMS_output显示表emp中所有人员的平均sal的值，并显示所有人员的总工资。 显示格式为：

员工的平均工资：$1134.20 所有员工的总工资：$12000.00

2、编写PL/SQL块，使用SQL*PLUS的替代变量输入数字数据，然后使用DBMS_OUTPUT 以本地货币格式显示结果。格式如下：

输入number的值：1200 本地货币格式： RMB1,500.00

3、编写PL/SQL块，使用SQL*PLUS的替代变量输入日期数据，然后使用DBMS_OUTPUT 输出10月后对应的日期，以及5年10个月之前对应的日期。格式：

输入date的值：2003-10-10

5年10个月之前对应的日期：10-12月-97 10月后对应的日期：10-8月-04

4、编写PL/SQL块，使用SQL*PLUS的替代变量输入字符串数据，然后使用DBMS_OUTPUT 输出其大写、小写，以及首字母大写其他字母小写的格式。格式如下：

输入String的值：my china 大写格式：MY CHINA 小写格式：my china

首字母大写：My China 5、编写PL/SQL块，使用SQL*PLUS的替代变量输入各种数据，实现向emp表添加新的数据， 其格式为： 输入 员工编号 的值：1234

输入 员工名 的值：tom 输入 工资 的值：1200 输入 部门编号 的值：20 插入新数据成功！

6、编写PL/SQL块，使用SQL*PLUS的替代变量输入员工的名字和工资的修改数据，实现向emp表中指定的员工更新工资。格式如下： 输入 员工名 的值：tom 输入 更改值 的值：-100

数据修改成功！

第三部分

1、完成一下3个练习题，注意功能逐步完善。

a、使用替代变量输入数据，实现向emp表插入数据。（直接插入，无需考虑其他因素） b、使用替代变量输入数据，实现向emp表插入数据，并在插入前检查是不是违反主键 约束，如果违反则什么都不做。

c、使用替代变量输入数据，实现向emp表插入数据，并在插入前检查是不是违反主键

约束，如果违反则提示用户“已经存在相同编号的用户”。

2、编写一个程序段，输入员工编号，依据以下的条件给指定的员工更改工资

ANALYST CLERK MANAGER PRESIDENT SALESMAN

13% 7%

12% 14% 20%

a、使用IF语句实现。

b、使用case语句实现。

3、首先建立临时表temp，然后编写PL/SQL块为该表插入数据1-20，其中排除5，10，14，20，建立表的语句

create table temp(cola int);

4、建立表TOP，然后编写PL/SQL块，将emp表中工资最高的前N个（使用替代变量）雇员插入TOP表。 异常处理：

1、raise语句放在PL/SQL程序块的哪一部分？

2、在异常和Oracle错误建立关联的时候，应该在哪个部分完成？

3、使用rasie_application_error的目的是什么？ 4、假定在EMP表上定义了check约束，要求雇员工资不能高于6000.为了处理工资超过6000的错误，应该使用什么样的异常?

5、编写PL/SQL块，使用SQL*Plus替代变量输入员工号，显示员工的部门名称，工作岗位。并处理NO_DATA_FOUND的异常。如果不存在此员工，则显示消息“请检查员工号，并输入正确的员工号”

6、编写PL/SQL块，使用SQL*Plus替代变量输入部门号，删除该部门的信息，并处理可能的错误： a、如果能成功删除部门，则显示消息“成功删除部门...”

b、如果部门不存在，则显示消息“部门不存在”

c、如果违反了约束，则显示“此部门有员工不能被删除”

7、编写PL/SQL程序块，使用SQL*Plus替代变量输入员工号，姓名、岗位和部门号，利用异常实现，CLERK岗位的员工不能在10部门。

8、利用PL/SQL实现数据库分页emp表，select * from emp

输入：一页显示几行（页大小），第几页（页数）

如果输入的页数大于最大页数，要提示用户，他可以输入的页码范围。