oracle 笔记 游标

 --游标：是Oracle系统在内存中开辟的一个工作区，可以存放select查询结果 --隐式的游标（SQL游标）：不需要open、fetch、close等操作， DECLARE   v_deptno NUMBER:=30; BEGIN   DELETE FROM emp WHERE deptno=v_deptno;   dbms_output.put_line(SQL%ROWCOUNT||'行记录被删除。');    END; SELECT * FROM emp;  --显式的游标：open打开游标、fetch提取游标数据、close关闭游标等操作 --循环打印部门表中的部门号和部门名称(1.使用标量变量接收数据） DECLARE   --定义一个游标   CURSOR dept_cursor IS          SELECT deptno,dname FROM dept;   v_deptno dept.deptno%TYPE;   v_dname dept.dname%TYPE; BEGIN   --游标使用之前，需要手动打开   OPEN dept_cursor;   LOOP     --提取数据     FETCH dept_cursor INTO v_deptno,v_dname;     EXIT WHEN dept_cursor%NOTFOUND;     dbms_output.put_line('部门号:'||v_deptno);     dbms_output.put_line('部门名称:'||v_dname);     dbms_output.put_line('==================');   END LOOP;   --关闭游标   CLOSE dept_cursor; END;   --循环打印部门表中的部门号和部门名称(2.使用记录变量接收数据） DECLARE   --定义一个游标   CURSOR dept_cursor IS          SELECT * FROM dept;      dept_record dept%ROWTYPE; BEGIN     --游标使用之前，需要手动打开   IF NOT dept_cursor%ISOPEN THEN           OPEN dept_cursor;   END IF;   LOOP     --提取数据     FETCH dept_cursor INTO dept_record;     EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;     dbms_output.put_line('部门号:'||dept_record.deptno);     dbms_output.put_line('部门名称:'||dept_record.dname);     dbms_output.put_line('==================');   END LOOP;   --关闭游标   CLOSE dept_cursor; END;  --3.使用集合变量接收数据 DECLARE    CURSOR dept_cursor IS          SELECT deptno,dname,loc FROM dept;   TYPE dept_table_type IS TABLE OF dept_cursor%ROWTYPE INDEX BY BINARY_INTEGER;   dept_table dept_table_type;   idx NUMBER;--存储下标 BEGIN    OPEN dept_cursor;   LOOP     idx:=dept_cursor%ROWCOUNT+1;     FETCH dept_cursor INTO dept_table(idx);          EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;          dbms_output.put_line('部门号：'||dept_table(idx).deptno);     dbms_output.put_line('部门名称：'||dept_table(idx).dname);     dbms_output.put_line('部门地址：'||dept_table(idx).loc);          dbms_output.put_line('==================');   END LOOP; END;  --4.带参数的游标 DECLARE   --定义一个游标   CURSOR dept_cursor(v_deptno NUMBER DEFAULT 30) IS          SELECT deptno,dname,loc FROM dept WHERE deptno<v_deptno;      dept_record dept%ROWTYPE;          BEGIN     --OPEN dept_cursor;   OPEN dept_cursor(v_deptno=>&deptno);   LOOP     --提取数据     FETCH dept_cursor INTO dept_record;     EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;     dbms_output.put_line('部门号:'||dept_record.deptno);     dbms_output.put_line('部门名称:'||dept_record.dname);     dbms_output.put_line('部门地址：'||dept_record.loc);       dbms_output.put_line('==================');   END LOOP;   --关闭游标   CLOSE dept_cursor; END;  --游标for循环1（自动执行游标的open、fetch、close） DECLARE   CURSOR dept_cursor IS          SELECT deptno,dname,loc FROM dept; BEGIN   FOR dept_record IN dept_cursor  LOOP       dbms_output.put_line('部门号:'||dept_record.deptno);       dbms_output.put_line('部门名称:'||dept_record.dname);       dbms_output.put_line('部门地址：'||dept_record.loc);         dbms_output.put_line('==================');   END LOOP; END;   --游标for循环2(简化写法） DECLARE BEGIN   FOR dept_record IN (SELECT deptno,dname,loc FROM dept)  LOOP       dbms_output.put_line('部门号:'||dept_record.deptno);       dbms_output.put_line('部门名称:'||dept_record.dname);       dbms_output.put_line('部门地址：'||dept_record.loc);         dbms_output.put_line('==================');   END LOOP; END;  --使用游标修改数据 --修改指定部门的员工的最低工资为1500 --NOWAIT:如果当前数据行已经被其他回话锁定，open将立即返回一个oracle错误 DECLARE   v_deptno emp.deptno%TYPE:=&p_deptno;   CURSOR emp_cursor IS          SELECT empno,sal FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT; BEGIN   FOR emp_record IN emp_cursor LOOP     IF emp_record.sal<1500 THEN       dbms_output.put_line('职工号：'||emp_record.empno);       dbms_output.put_line('工资：'||emp_record.sal);       dbms_output.put_line('==================');       --修改数据(游标中当前行：WHERE CURRENT OF emp_cursor，需要在查询时提供FOR UPDATE)       --UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;       DELETE FROM emp WHERE CURRENT OF emp_cursor;     END IF;   END LOOP;   COMMIT; END;  SELECT * FROM emp;    --异常 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;   DECLARE  CURSOR dept_cursor IS         SELECT  deptno,dname,loc FROM dept; TYPE dept_table_type IS TABLE OF dept_cursor%ROWTYPE INDEX BY BINARY_INTEGER; dept_table dept_table_type; idx NUMBER;--存储下标 BEGIN   OPEN dept_cursor;   LOOP     idx:=dept_cursor%ROWCOUNT+1;     FETCH dept_cursor INTO dept_table(idx);    EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;         dbms_output.put_line('部门号：'||dept_table(idx).deptno);     dbms_output.put_line('部门名称：'||dept_table(idx).dname);     dbms_output.put_line('部门地址：'||dept_table(idx).loc);          dbms_output.put_line('==================');   END LOOP; END;           --使用游标修改数据 --修改指定部门的员工的最低工资为1500 --NOWAIT:如果当前数据行已经被其他回话锁定，open将立即返回一个oracle错误 DECLARE   v_deptno emp.deptno%TYPE:=&p_deptno;   CURSOR emp_cursor IS          SELECT empno,sal FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT; BEGIN   FOR emp_record IN emp_cursor LOOP     IF emp_record.sal<1500 THEN       dbms_output.put_line('职工号：'||emp_record.empno);       dbms_output.put_line('工资：'||emp_record.sal);       dbms_output.put_line('==================');       --修改数据(游标中当前行：WHERE CURRENT OF emp_cursor，需要在查询时提供FOR UPDATE)       UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;      -- DELETE FROM emp WHERE CURRENT OF emp_cursor;     END IF;   END LOOP;   COMMIT; END;  SELECT * FROM emp;    --异常 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;         ---第四章练习  /* 使用游标获取部门表（dept）中的部门号deptno，部门名，传递部门号   到游标中，获取在此部门中工作的员工姓名，工作，参加工作时间，工资。    如 部门号10 部门名ACCOUNTING     张三      SALES     1999.1.1               3000     ...*/   --1DECLARE  CURSOR cursor_test IS    SELECT deptno,dname FROM dept;    v_deptno dept.deptno%TYPE;     v_dname dept.dname%TYPE;     dept_record  dept%ROWTYPE;     emp_record   emp%ROWTYPE;  ename_record emp.ename%TYPE;  CURSOR cursor_2(v_deptno1 dept.deptno%TYPE) IS   SELECT *  FROM emp WHERE deptno=v_deptno1;BEGIN   OPEN cursor_test;       LOOP     FETCH cursor_test INTO v_deptno,v_dname;        EXIT WHEN cursor_test%NOTFOUND;     dbms_output.put_line('部门号'||v_deptno);     dbms_output.put_line('部门名称'||v_dname);     OPEN cursor_2(v_deptno);     LOOP      FETCH cursor_2 INTO emp_record;     EXIT WHEN cursor_2%NOTFOUND;     dbms_output.put_line('雇员名称：'||emp_record.ename);     dbms_output.put_line('薪水：'||emp_record.sal);     dbms_output.put_line('入职时间：'||emp_record.hiredate);     END LOOP;     CLOSE cursor_2;     END LOOP;      CLOSE cursor_test;  END;   SELECT * FROM dept   SELECT * FROM copy_emp;   DROP TABLE copy_emp;      /*   2.游标处理中使用for update和where current of 在表中修改字段starts的值，sal字段中每一个1000，一个‘*’，如sal为3500，四舍五入，starts打印3个'*'建表语句如下：*/CREATE TABLE copy_emp  as select * from emp;   alter table copy_emp add starts varchar(20);   SELECT * FROM copy_emp  DECLARE v_empno emp.empno%TYPE:=&p_empno;   CURSOR emp_cursor IS          SELECT sal FROM copy_emp WHERE empno=v_empno ; BEGIN   FOR emp_record IN emp_cursor LOOP     IF FLOORdTHEN       UPDATE copy_emp SET starts ='*' WHERE empno=v_empno;           END IF;   END LOOP;   COMMIT; END;   DECLARE v_empno emp.empno%TYPE:=&p_empno;   CURSOR emp_cursor IS          SELECT sal FROM copy_emp WHERE empno=v_empno FOR UPDATE NOWAIT; BEGIN   FOR emp_record IN emp_cursor LOOP     IF FLOOR(emp_record.sal/1000)=1 THEN       UPDATE copy_emp SET starts ='*'  WHERE CURRENT OF emp_cursor;           END IF;   END LOOP;   COMMIT; END;   /*  3.定义游标，查询emp表，如果工资大于2000，  并且参加工作时间在82年之前，在屏幕上显示员工的名字，  工资和参加工作时间  */ DECLARE  CURSOR emp_cursor1 IS         SELECT ename,sal,to_number(EXTRACT(YEAR FROM hiredate)) FROM emp ;    v_ename emp.ename%TYPE ;   v_sal emp.sal%TYPE ;   v_hiredate NUMBER ; BEGIN  OPEN emp_cursor1;    LOOP    FETCH emp_cursor1 INTO  v_ename,v_sal,v_hiredate;    EXIT WHEN emp_cursor1%NOTFOUND;    IF v_sal>1000 AND v_hiredate<1982 THEN     dbms_output.put_line('雇员名称：'||v_ename);     ELSE      dbms_output.put_line('!!!!!');      END IF;    END LOOP;     CLOSE emp_cursor1; END;  SELECT * FROM copy_emp SELECT * FROM emp FOR UPDATE UPDATE emp SET sal    SELECT ename,sal,to_number(EXTRACT(YEAR FROM hiredate)) FROM emp;ROLLBACK;