数据库学习10-游标

1.游标；
  1.1 游标是什么；
      查询出来的结果集的指针；
  1.2 游标的分类；
      1.2.1 静态游标
           a. 隐式游标 %rowcount,%found,%nofound,%isopen
           b. 显式游标 
              --声明部分
              declare
                 cursor cur_name is
                   select ... from ...;
              begin
                 --打开游标
                 open cur_name;
                 loop
                    fetch cur_name into 变量; --遍历
                    exit when cur_name%notfound;
                    --打印 变量
                    
                 end loop;
                 close cur_name;


             (查询、删除和更新(for update [of] current of);参数;for...in...语句)
      12.2 游标变量

/*
  pl/sql基本语法回顾
*/
DECLARE
  --声明部分
  LOOP
    EXIT WHEN 条件表达式;
  END LOOP;
  
  WHILE 条件表达式 LOOP
   
  END LOOP;
  
  FOR i IN 1..10 LOOP
   
  END LOOP;
BEGIN
  --可执行部分
EXCEPTION 
  -- 异常处理
END;

/*
  1. 根据员工编号打印：ename(员工姓名)、dname(部门名称)

   要求分别使用三种方法
       a、rowtype (提示：视图%rowtype)
       b、record
       c、type
*/
--c、type
DECLARE 
  a emp.ename%TYPE;  --员工名字
  b dept.dname%TYPE; --部门名称
BEGIN
  SELECT ename,dname INTO a,b 
     FROM emp e,dept d
      WHERE e.deptno=d.deptno
         AND e.empno=&uuu;
  --打印       
  dbms_output.put_line('员工名字：'||a||',部门名称：'||b);
END;
-- a、rowtype (提示：视图%rowtype)
CREATE VIEW v_emp_dept 
   AS
  SELECT ename,dname 
   FROM emp t1,dept t2
    WHERE t1.deptno=t2.deptno
  WITH READ ONLY; --只读视图
 
DECLARE
  t v_emp_dept%ROWTYPE;
BEGIN
  SELECT ename,dname INTO t 
     FROM emp e,dept d
      WHERE e.deptno=d.deptno
         AND e.empno=&uuu;
  
  dbms_output.put_line('员工名字：'||t.ename||',部门名称：'||t.dname);
END;
--b、record
DECLARE
--数据类型ty
 TYPE ty IS RECORD 
 (
   c1 emp.ename%TYPE,
   c2 dept.dname%TYPE
 );
 var ty;
BEGIN
  SELECT ename,dname INTO var 
     FROM emp e,dept d
      WHERE e.deptno=d.deptno
         AND e.empno=&uuu;
  
  dbms_output.put_line('员工名字：'||var.c1||',部门名称：'||var.c2);
END;

/*
  4. 添加员工，工号使用序列seq_emp。
   然后打印该员工的最新工号。
*/
CREATE SEQUENCE seq_emp;
DECLARE
  e_id NUMBER;
BEGIN
  SELECT seq_emp.NEXTVAL INTO e_id FROM dual;
  INSERT INTO emp(empno) VALUES(e_id);
  dbms_output.put_line('最新工号是：'||e_id);
  COMMIT;
END;
SELECT * FROM emp;
/*
  7. 根据工号查询员工姓名，工作和工资。
    如果员工不存在，则提示之。
*/
--预定义异常 no_data_found
DECLARE
  e_name emp.ename%TYPE;
  e_job emp.job%TYPE;
  e_sal emp.sal%TYPE;
BEGIN
  SELECT ename,job,sal INTO 
        e_name,e_job,e_sal
    FROM emp 
      WHERE empno=&i;
  dbms_output.put_line('员工的名字是：'||e_name||'薪水是：'||e_sal);
EXCEPTION 
  WHEN no_data_found THEN
    raise_application_error(-20222,'该员工不存在');
END;
/*
 10.添加100000员工以下信息:empno,ename,sal,job,hiredate

   要求：使用两种方法，并对比两种方法的执行效率。
   a、静态sql，使用拼接字符串
   b、动态sql，使用绑定变量

   说明：原emp表的empno长度为4，
   所以需要使用 'alter table 表名 modify 字段 数据类型' 来扩大字段。
*/
 DROP TABLE emp2;
 CREATE TABLE emp2 
  AS SELECT * FROM emp;
 SELECT COUNT(*) FROM emp2;
 ALTER TABLE emp2 MODIFY empno NUMBER(10);
TRUNCATE TABLE emp2;--清空数据
--静态的 sql
BEGIN
  FOR i IN 9000..109000 LOOP
     INSERT INTO emp2(empno,ename,sal,job,hiredate)
       VALUES(i,i,2000,'CLERK',SYSDATE); 
  END LOOP;
  COMMIT;
END;
--动态sql
BEGIN
  FOR i IN 9000..109000 LOOP
  EXECUTE IMMEDIATE  
    'INSERT INTO emp2(empno,ename,sal,job,hiredate)
     VALUES(:1,:2,:3,:4,:5)' 
   USING i,i,'2000','CLERK',SYSDATE;
  END LOOP;
  COMMIT;
END;

/*
  游标: 指向结果集的指针
    分类：
      1.静态游标 
         1.1隐式游标
         1.2显示游标
      2.游标变量
*/
--通过属性了解隐式游标 
--  %rowcount,%found,%notfound,
--  隐式游标里 %isopen属性总是false
SELECT * FROM emp;
BEGIN
   UPDATE emp SET sal=1000 
     WHERE deptno=10;
   dbms_output.put_line('sql语句影响了'||SQL%ROWCOUNT||'条数据');
   ROLLBACK;
END;

DECLARE
 e emp%ROWTYPE;
BEGIN
  SELECT * INTO e FROM emp WHERE empno=&i;
  IF SQL%FOUND THEN
    dbms_output.put_line('找到数据');
  END IF;
END;

BEGIN
  UPDATE emp SET sal=1000 WHERE empno=100;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('-----');
  END IF; 
  ROLLBACK;
END;

--显示游标  
DECLARE
  CURSOR c IS   --声明一个游标c
    SELECT ename,sal FROM emp;
  a VARCHAR2(10);
  b NUMBER; 
BEGIN
  --打开
    OPEN c;
    LOOP 
      FETCH c INTO a,b; --遍历
      EXIT WHEN c%NOTFOUND;
        dbms_output.put_line(a||','||b);
    END LOOP;
  CLOSE c;--关闭
END;
--用游标打印所有员工的工作岗位
DECLARE  
  CURSOR c IS
    SELECT job FROM emp;
  j emp.job%TYPE;
BEGIN
  OPEN c;
   LOOP
     FETCH c INTO j;
     EXIT WHEN c%NOTFOUND;
     dbms_output.put_line(j);
   END LOOP;
  CLOSE c;
END;
--某一个工作岗位的员工
-- exit when 设置退出循环条件
DECLARE
  CURSOR c(j VARCHAR) IS 
    SELECT * FROM emp WHERE job=j;
  e emp%ROWTYPE;
BEGIN
  OPEN c('MANAGER');
    LOOP 
      FETCH c INTO e;
      EXIT WHEN c%NOTFOUND;
      dbms_output.put_line(e.ename||'  '||e.job);
    END LOOP;
  CLOSE c;
END;
--while循环
DECLARE
  CURSOR c(j VARCHAR) IS 
    SELECT * FROM emp WHERE job=j;
  e emp%ROWTYPE;
BEGIN
  OPEN c('MANAGER');
  FETCH c INTO e;
    WHILE c%FOUND LOOP 
      dbms_output.put_line(e.ename||'  '||e.job);
      FETCH c INTO e;
    END LOOP;
  CLOSE c;
END;
--for 循环 不需要打开或关闭游标
DECLARE
  CURSOR c(j VARCHAR) IS 
    SELECT * FROM emp WHERE job=j;
BEGIN
  FOR e IN c('MANAGER') LOOP
     dbms_output.put_line(e.ename||'  '||e.job);
  END LOOP;
END;
--给部门30所有人加工资10
DECLARE
  CURSOR c IS
    SELECT * FROM emp WHERE deptno=30;
BEGIN
  FOR e IN c LOOP
     dbms_output.put_line(e.ename||'之前的工资：'||e.sal);
     UPDATE emp SET sal = e.sal+10
       WHERE empno=e.empno;
      dbms_output.put_line(e.ename||'之后的工资：'||(e.sal+10));
  END LOOP;
  ROLLBACK;
END;
--更新游标
DECLARE
  CURSOR c IS
    SELECT ename,sal,comm,hiredate FROM emp
      WHERE deptno=30 FOR UPDATE OF sal;
BEGIN
  FOR e IN c LOOP
    dbms_output.put_line(e.ename||'  前 '||e.sal);
    UPDATE emp SET sal = e.sal+10 
      WHERE CURRENT OF c;
    dbms_output.put_line(e.ename||'  后 '||(e.sal+10));
  END LOOP;
  COMMIT;
END;

--游标变量
DECLARE
  TYPE ct IS REF CURSOR;
  c ct;
  e emp%ROWTYPE;
BEGIN
  OPEN c FOR 'select * from emp';
   FETCH c INTO e;
   WHILE c%FOUND LOOP
     dbms_output.put_line(e.ename);
     FETCH c INTO e;
   END LOOP;
  CLOSE c; 
END;