Oracle数据库中游标，异常的简单应用

• 使用游标获取部门表（ dept ）中的部门号 deptno ，部门名，传递部门号到游标中，获取在此部门中工作的员工姓名，工作，参加工作时间，工资。

• 如 部门号    10                 部门名 ACCOUNTING

•     张三      SALES     1999.1.1               3000

CREATE TABLE copy_emp  as select * from emp;
alter table copy_emp add starts varchar(20);
SELECT * FROM copy_emp;
DECLARE 
       CURSOR copy_emp_cursor IS
       SELECT DECODE(TRUNC(sal/1000),0,'*',1,'*',2,'**',3,'***',4,'****',5,'*****') e FROM copy_emp FOR UPDATE NOWAIT;
       
BEGIN
       FOR i IN copy_emp_cursor LOOP
         
         UPDATE copy_emp SET starts=i.e WHERE CURRENT OF copy_emp_cursor;
       END LOOP;
       COMMIT;
END;

•游标处理中使用forupdate和where current of

•在表中修改字段starts的值，sal字段中每一个1000，一个‘*’，如sal为3500，四舍五入，starts打印3个'*'

•建表语句如下：

•CREATE TABLE copy_emp  as select * from emp;

•alter table copy_empadd starts varchar(20);

DECLARE 
   CURSOR dept_cursor(v_dno NUMBER) IS 
   SELECT d.deptno,d.dname,e.ename,e.sal,e.hiredate FROM dept d,emp e WHERE d.deptno=e.deptno AND d.deptno=v_dno;
   --声明变量接受游标数据
   v_dno dept.deptno%TYPE;
   v_dname dept.dname%TYPE;
   v_name emp.ename%TYPE;
   v_sal emp.sal%TYPE;
   v_hiredate emp.hiredate%TYPE;
BEGIN
  OPEN dept_cursor(v_dno=>&dep);
  LOOP
    FETCH dept_cursor INTO v_dno,v_dname,v_name,v_sal,v_hiredate;
    EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%Rowcount>3;
    dbms_output.put_line('部门号:'||v_dno);
    dbms_output.put_line('部门名称:'||v_dname);
    dbms_output.put_line('姓名：'||v_name);  
    dbms_output.put_line('薪水：'||v_sal); 
    dbms_output.put_line('入职年限：'||v_hiredate); 
    dbms_output.put_line('==================');
  END LOOP;
  CLOSE dept_cursor;
END;

定义游标，查询 emp 表，如果工资大于 2000 ，并且参加工作时间在 82 年之前，在屏幕上显示员工的名字，工资和参加工作时间

DECLARE
   
   CURSOR emp_cursor IS
          SELECT ename,sal,hiredate FROM emp;
 BEGIN
   FOR emp_record IN emp_cursor LOOP
     IF emp_record.sal<2000 AND emp_record.hiredate<to_date('1982-1-1','yyyy-MM-dd') THEN
       dbms_output.put_line('职工姓名：'||emp_record.ename);
       dbms_output.put_line('工资：'||emp_record.sal);
       dbms_output.put_line('工资：'||emp_record.hiredate);
       dbms_output.put_line('==================');
     END IF;
   END LOOP;
 END;

• 在一个块中分别显示薪水低于 2000 ，在 2000 与 4000 之间，以

• 及大于 4000 的雇员名 .( 请使用游标参数 )

DECLARE
   CURSOR emp_cursor IS//声明游标
          SELECT ename,sal FROM emp;
 BEGIN
   FOR emp_record IN emp_cursor LOOP
     IF emp_record.sal<2000 THEN
       dbms_output.put_line('薪水小于2000');
       dbms_output.put_line('姓名：'||emp_record.ename);
       dbms_output.put_line('==================');
      ELSIF emp_record.sal BETWEEN 2000 AND 4000 THEN
       dbms_output.put_line('薪水大于2000小于4000');
       dbms_output.put_line('姓名：'||emp_record.ename);
       dbms_output.put_line('==================');
      ELSE 
       dbms_output.put_line('薪水大于4000');
       dbms_output.put_line('姓名：'||emp_record.ename);
       dbms_output.put_line('==================');
     END IF;
   END LOOP;
   COMMIT;
 END;

• 汇总每个部门每个职位员工的最高及平均工资，包括显示部门名称

• 和职位名称，将数据存储到 Test 表中。要求使用游标

CREATE TABLE TEST(//新建了一张临时表存放数据
       dname VARCHAR2(14),
       job VARCHAR2(9),
       maxsal NUMBER(7,2),
       avgsal NUMBER(7,2)
);
SELECT * FROM TEST;
DECLARE 
   CURSOR dept_cursor IS 
   SELECT p.dname,p.job ,MAX (p.sal) MAX,avg(p.sal) AVG FROM 
   (SELECT d.dname,e.job,e.sal FROM dept d,emp e WHERE d.deptno=e.deptno) p GROUP BY p.dname,p.job;

   --声明变量接受游标数据
   v_dname dept.dname%TYPE;
   v_job emp.job%TYPE;
   v_max emp.sal%TYPE;
   v_avg emp.sal%TYPE;
BEGIN
  OPEN dept_cursor;
  LOOP
    FETCH dept_cursor INTO v_dname,v_job,v_max,v_avg;
    EXIT WHEN dept_cursor%NOTFOUND;
    INSERT INTO TEST VALUES(v_dname,v_job,v_max,v_avg);
  END LOOP;
  COMMIT;
  CLOSE dept_cursor;
END;

异常及自定义异常的应用

 --异常
 DECLARE
    v_empno emp.empno%TYPE:=&empno;
    
 BEGIN
   UPDATE emp SET sal=sal+1000 WHERE empno=v_empno;
   COMMIT;
   
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('指定的员工不存在');
     WHEN OTHERS THEN
          dbms_output.put_line('产生其他异常');
 END;
 
 --自定义异常
  DECLARE
    v_empno emp.empno%TYPE:=&empno;
    noresult EXCEPTION;
 BEGIN
   UPDATE emp SET sal=sal+1000 WHERE empno=v_empno;
   IF(SQL%NOTFOUND) THEN
      RAISE noresult;
   ELSE
      COMMIT;
   END IF;
   
   EXCEPTION 
     WHEN noresult THEN
          dbms_output.put_line('指定的员工不存在');
     WHEN OTHERS THEN
          dbms_output.put_line('产生其他异常');
 END;