PL/SQL详解之游标

游标：指向查询结果集的指针，指向哪一行，提取哪一行的数据(PLSQL的游标默认指向结果集的第一行)

游标的四个属性
游标变量 %found: 当最近一次读入记录成功时返回true
游标变量 %notfound:同上 相反
游标变量 %isopen:判断游标是否已经打开

游标变量 %rowcount:返回已从游标中读取的记录数

隐示游标：固定名称sql
游标的四个属性
SQL%FOUND:如果操作有影响行，就为true，否则为false
SQL%NOTFOUND :求反
SQL%ISOPEN:在隐示游标中，取值永远为false
SQL%ROWCOUNT:操作影响的行数

必须在事务结束之前读取游标属性，只能读取最近的一次DML操作的游标状态。

使用游标分为4个步骤
1，声明游标 cursor 游标变量 is 查询语句
2，打开游标 open 游标变量（不能重复打开游标）
3，提取数据 fetch 游标变量 into 变量1，变量2,.
4，关闭游标释放系统资源 close 游标变量

(1)查询输出所有员工的编号，姓名，工资|
DECLARE
    v_empid employees.employee_id%TYPE;
    v_name  employees.last_name%TYPE;         
    v_salary employees.salary%TYPE;  

    CURSOR  emp_cursor IS
    SELECT  employee_id,last_name,salary
    FROM    employees
    WHERE   department_id = 90;
BEGIN
    OPEN    emp_cursor;
    FETCH   emp_cursor INTO v_empid ,v_name, v_salary;
    dbms_output.put_line('编号'|| v_empid);
    dbms_output.put_line('姓名'|| v_name);
    dbms_output.put_line('工资'|| v_salary);
    CLOSE emp_cursor;
END;

(2)查询输出所有员工的编号，姓名，工资 

DECLARE
    v_empid employees.employee_id%TYPE;
    v_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE; 
    CURSOR emp_cursor IS
    SELECT employee_id,last_name,salary
    FROM employees
    WHERE department_id = 90;
BEGIN
    OPEN emp_cursor;
    FETCH  emp_cursor INTO v_empid ,v_name, v_salary;
    WHILE emp_cursor%FOUND LOOP
    dbms_output.put_line('编号'|| v_empid);
    dbms_output.put_line('姓名'|| v_name);
    dbms_output.put_line('工资'|| v_salary);
    dbms_output.put_line('-------------------');
    FETCH  emp_cursor INTO v_empid ,v_name, v_salary;
    END LOOP;
    CLOSE emp_cursor;
END;

(3)查询输出所有员工编号，姓名，工资（记录类型）
DECLARE
    e employees%ROWTYPE;
    CURSOR emp_cursor IS
    SELECT *
    FROM employees;
BEGIN
     OPEN   emp_cursor;
      FETCH emp_cursor INTO e;
      WHILE emp_cursor%FOUND LOOP
      dbms_output.put_line('编号' || e.employee_id);
      dbms_output.put_line('姓名' || e.last_name);
      dbms_output.put_line('工资' || e.salary);
      dbms_output.put_line('职务' || e.job_id);
      dbms_output.put_line('入职日期' || e.hire_date);
      dbms_output.put_line('-----------------------');
      FETCH emp_cursor INTO e;
      END LOOP;
      CLOSE emp_cursor;
END;

(4)查询输出所有员工编号，姓名，工资（PLSQL表类型）
DECLARE
     TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
     e emp_table_type;
    CURSOR emp_cursor IS
    SELECT * FROM employees;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor BULK COLLECT
    INTO e;
    FOR i IN 1 .. e.count LOOP
    dbms_output.put_line('编号' || e(i).employee_id);
    dbms_output.put_line('姓名' || e(i).last_name);
    dbms_output.put_line('工资' || e(i).salary);
    dbms_output.put_line('----------------------------');
    END LOOP;
    CLOSE emp_cursor;
END;

(5)查询输出所有员工编号，姓名，工资（通过limit控制提取的数据量）

DECLARE
  TYPE emp_table_type IS TABLE OF employees%ROWTYPE
  INDEX BY BINARY_INTEGER;
  e emp_table_type;
  CURSOR emp_cursor IS
  SELECT * FROM employees;
BEGIN 
   OPEN emp_cursor;
   FETCH emp_cursor BULK COLLECT INTO e LIMIT 6 ;
   FOR i IN 1..e.count LOOP
    dbms_output.put_line('编号' || e(i).employee_id);
    dbms_output.put_line('姓名' || e(i).last_name);
    dbms_output.put_line('工资' || e(i).salary);
    dbms_output.put_line('----------------------------');
     END LOOP;
CLOSE emp_cursor;
END;

(6)查询输出某个部门的员工的编号，姓名，工资（参数化游标）
DECLARE
  v_empid employees.employee_id%TYPE;
  v_name employees.last_name%TYPE;
  v_salary employees.salary%TYPE;
  CURSOR emp_cursor(p_deptid employees.employee_id%TYPE) IS
  SELECT employee_id,last_name,salary
  FROM employees
 WHERE department_id = p_deptid; 
BEGIN
   OPEN emp_cursor(60);
   LOOP 
   FETCH emp_cursor  INTO v_empid,v_name,v_salary ;
   EXIT WHEN emp_cursor%NOTFOUND;
   dbms_output.put_line('编号：'||v_empid||'姓名：'||v_name||'工资：'||v_salary);
   END LOOP;
  CLOSE emp_cursor;
  dbms_output.put_line('----------------------------');
  OPEN emp_cursor(50);
  LOOP
    FETCH emp_cursor  INTO v_empid,v_name,v_salary ;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line('编号：'||v_empid||'姓名：'||v_name||'工资：'||v_salary);
    END LOOP;
    CLOSE emp_cursor;
END;

(7)查询输出所有的员工编号，姓名，工资(游标的for循环)
DECLARE
    CURSOR emp_cursor IS
    SELECT * 
    FROM employees;
BEGIN 
  FOR e IN emp_cursor LOOP
    dbms_output.put_line('编号：'||e.employee_id||'姓名：'||e.last_name||'工资：'||e.salary);
  END LOOP;
END;
(8)查询输出所有的员工编号，姓名，工资（带参数）

DECLARE
    CURSOR emp_cursor(p_depid employees.employee_id%TYPE)IS
    SELECT * 
    FROM employees
    WHERE department_id=p_depid;
BEGIN 
  FOR e IN emp_cursor(50) LOOP
    dbms_output.put_line('编号：'||e.employee_id||'姓名：'||e.last_name||'工资：'||e.salary);
  END LOOP;
END;

(9)查询输出所有的员工编号，姓名，工资（最精简的写法）
DECLARE
BEGIN 
  FOR e IN (SELECT * FROM employees) LOOP
    dbms_output.put_line('编号：'||e.employee_id||'姓名：'||e.last_name||'工资：'||e.salary);
  END LOOP;
END;

(10)输出每个部门的部门编号，部门名称以及这个部门的下属员工的编号，姓名，工资

DECLARE
      CURSOR dept_cursor IS
      SELECT * FROM departments; 
      CURSOR emp_cursor(p_deptid NUMBER) IS
      SELECT * FROM employees
      WHERE department_id = p_deptid;
BEGIN 
     FOR d IN dept_cursor LOOP
     dbms_output.put_line(d.department_id|| '  '||d.department_name);
     FOR e IN emp_cursor(d.department_id) LOOP
     dbms_output.put_line('   '||e.employee_id||', '||e.last_name|| ', '|| e.salary);
     END LOOP;
     END LOOP;
END;
(11)用户输入一个任意部门的编号，更新这个部门的员工工资
     如果有员工更新，输出‘更新成果，有XX个员工被更新’
     如果没有员工更新，输出‘这个部门不存在，没有任何员工被更新’
DECLARE
      v_deptid NUMBER :=&input;
BEGIN
  UPDATE employees
  SET salary = salary +1
  WHERE department_id = v_deptid;
  IF SQL%FOUND THEN
    dbms_output.put_line('更新成！有'||SQL%ROWCOUNT ||'个员工被更新');
    ELSE
    dbms_output.put_line('这个部门不存在，没有任何员工被更新');
    END IF;
    COMMIT;
END;

(12)通过游标去更新员工的工资，如果低于5000块，则把工资改为5000
DECLARE
    CURSOR emp_cursor IS
    SELECT * FROM new_emp FOR UPDATE;
BEGIN
  FOR e IN emp_cursor LOOP
    IF e.salary<8300 THEN
      UPDATE new_emp SET salary =8300 WHERE CURRENT OF emp_cursor;
    END IF;
  END LOOP;
END;
(13)通过游标删除工资=5000的员工
DECLARE
    CURSOR emp_cursor IS
    SELECT * FROM new_emp FOR UPDATE;
BEGIN
  FOR e IN emp_cursor LOOP
    IF e.salary = 6000 THEN
      DELETE FROM new_emp WHERE CURRENT OF emp_cursor;
      END IF;
  END LOOP;
END;

(14)用户输入一个字母，输入E，输出所有员工姓名，如果输出D，输出所有部门名称（游标变量（动态游标））
DECLARE
      v_cmd CHAR(1):='&input';
     v_name VARCHAR2(50);
     //声明自定义的游标变量类型
     TYPE c_type IS REF CURSOR;
    声明变量
    c c_type;
    c SYS_REFCURSOR;--相当于 c_type is ref cursor
BEGIN
  IF v_cmd = 'E' THEN
     dbms_output.put_line('员工姓名');
      OPEN c FOR
      SELECT last_name FROM employees;
  ELSIF v_cmd = 'D' THEN
     dbms_output.put_line('部门名称');
     O PEN c FOR
      SELECT department_name FROM departments;
  ELSE
    dbms_output.put_line('请正确输入');
    RETURN;
    END IF;
  LOOP
    FETCH  c
     INTO  v_name;
     EXIT  WHEN c%NOTFOUND;
     dbms_output.put_line(v_name);
   END ;