PL/SQL:游标的灵活运用

-------------------游标的灵活运用1--------------------
在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN  - 游标是否打开，始终为FALSE

DECLARE
  v_empData   emp%ROWTYPE;--定义一个表示表中一行记录的变量
BEGIN
  SELECT *
    INTO v_empData FROM emp WHERE empno = -1;
  IF SQL%NOTFOUND THEN /*注意，这里不执行，以上自动引发select into预定义异常*/
    INSERT INTO emp (empno,ename) VALUES (-1,'Not found!');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN/*注意，这是真正执行的*/
    INSERT INTO emp (empno,ename) VALUES (-10,'Not found!');
END;
select * from emp;
：-10	Not found!						

DECLARE
  v_empno emp.EMPNO%type := '&empno';--8888
  v_ename emp.ENAME%Type := '&ename';--JUST
BEGIN
  UPDATE emp SET ENAME = v_ename WHERE empno = v_empno;
  IF SQL%NOTFOUND THEN--如果 DML 语句不影响任何行，则返回 True 
    DBMS_OUTPUT.PUT_LINE('编号未找到。');
  ELSE
    DBMS_OUTPUT.PUT_LINE('表已更新');
  END IF;
END;
OUTPUT:编号未找到。

BEGIN
  UPDATE emp SET empno = 8888 WHERE empno = 7900;
  IF SQL%FOUND THEN--如果 DML 语句影响任何行，则返回 True
    DBMS_OUTPUT.PUT_LINE('表已更新');
  END IF;
END;
OUTPUT:表已更新

select * from dept;
--练习
create  table 成绩表
 (学号 char(5) not null,
   课程号 char(3) not null,
   成绩 int);
insert into 成绩表 values('95001','001',95);
insert into 成绩表 values('95002','001',56);
insert into 成绩表 values('95003','002',70);
select * from 成绩表;

begin 
update 成绩表
set 成绩=60
where 课程号='001' and 成绩<60;
if SQL%FOUND THEN
   dbms_output.put_line('成绩已更新');
end if;
end;   

select * from emp;
declare
  v_rows number;
begin
  update emp set comm = 500 where deptno = 30;
  v_rows := SQL%ROWCOUNT;--返回 DML 语句影响的行数,这里赋值，put_line才能输出
  dbms_output.put_line('给部门30的' || v_rows || '个雇员每人加了1000元奖金');
end;

select * from emp order by deptno;

DECLARE 
	empid VARCHAR2(10);
	desig VARCHAR2(10);
     BEGIN
	empid:= '&Employeeid';
	SELECT designation INTO desig 
	FROM employee WHERE empno=empid;
     EXCEPTION
	WHEN NO_DATA_FOUND THEN --如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常
	  DBMS_OUTPUT.PUT_LINE('职员未找到');
     END;
     
DECLARE 
	empid VARCHAR2(10);
     BEGIN
	SELECT empno INTO empid FROM employee;
     EXCEPTION
	WHEN TOO_MANY_ROWS THEN--如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常
	  DBMS_OUTPUT.PUT_LINE('该查询提取多行');
     END;
-------------------游标的灵活运用2(显式游标)--------------------
DECLARE   
 CURSOR CUR_TEST IS --声明显式游标
             SELECT ECODE,ENAME
              FROM EMP;
      CUR CUR_TEST%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录
 BEGIN 
      --For 循环
      FOR CUR IN CUR_TEST LOOP
          --循环体
        DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);
      END LOOP;

      --Fetch 循环
      OPEN CUR_TEST;--必须要明确的打开和关闭游标
      LOOP 
        FETCH CUR_TEST INTO CUR;
        EXIT WHEN CUR_TEST%NOTFOUND;
        --循环体
        DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);
      END LOOP;
      CLOSE C_EMP;

      --While 循环
      OPEN CUR_TEST;--必须要明确的打开和关闭游标
        FETCH CUR_TEST INTO CUR;
        WHILE CUR_TEST%FOUND LOOP--循环体
          DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);

          FETCH CUR_TEST INTO CUR;
        END LOOP;
      CLOSE C_EMP;
	  END;
使用For循环的有什么好处？
使用for循环不需要关注游标是否打开或关闭。
for循环会自动将数据fetch到记录型变量。
for循环不需要关注何时要退出，也就是不需要写退出满足条件。遍历完成就会退出。

声明游标：CURSOR ..IS select..
打开游标：OPEN 
结果集控制：FETCH..INTO..
关闭游标：CLOSE 
DECLARE
  empid VARCHAR2(10);
BEGIN
  SELECT empno INTO empid FROM employee;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    --如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常
    DBMS_OUTPUT.PUT_LINE('该查询提取多行');
END;

DECLARE
  my_toy_price toys.toyprice%TYPE;
  CURSOR toy_cur IS
    SELECT toyprice FROM toys WHERE toyprice < 250;--声明游标
BEGIN
  OPEN toy_cur;--打开游标
  LOOP
    FETCH toy_cur INTO my_toy_price;--提取行
    EXIT WHEN toy_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('TOYPRICE=:玩具单价=：' || my_toy_price);
  END LOOP;
  CLOSE toy_cur;--关闭游标
END;

声明显式游标时可以带参数以提高灵活性
声明带参数的显式游标的语法如下：
	CURSOR <cursor_name>(<param_name> <param_type>)
     IS select_statement;

DECLARE
  desig    VARCHAR2(20);
  emp_code VARCHAR2(5);
  empnm    VARCHAR2(20);
  CURSOR emp_cur(desigparam VARCHAR2) IS
    SELECT empno, ename FROM employee WHERE designation = desig;
BEGIN
  desig := '&desig';
  OPEN emp_cur(desig);
  LOOP
    FETCH emp_cur
      INTO emp_code, empnm;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_code || ' ' || empnm);
  END LOOP;
  CLOSE emp_cur;
END;

--使用显式游标更新行
允许使用游标删除或更新活动集中的行
声明游标时必须使用 SELECT … FOR UPDATE语句

DECLARE
  new_price NUMBER;
  CURSOR cur_toy IS
    SELECT toyprice FROM toys WHERE toyprice < 100 FOR UPDATE OF toyprice;
BEGIN
  OPEN cur_toy;
  LOOP
    FETCH cur_toy
      INTO new_price;
    EXIT WHEN cur_toy%NOTFOUND;
    UPDATE toys SET toyprice = 1.1 * new_price WHERE CURRENT OF cur_toy;
  END LOOP;
  CLOSE cur_toy;
  COMMIT;
END;
--更新的语法
CURSOR < cursor_name > IS
  SELECT statement FOR UPDATE;
  
UPDATE < table_name >
   SET < set_clause >
 WHERE CURRENT OF < cursor_name >
      --删除的语法
       DELETE FROM < table_name >
 WHERE CURRENT OF < cursor_name >
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
--demo
1 %TYPE说明
为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，Oracle提供了%TYPE定义方式。当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个变量的数据类型时，就只能采用这种方法定义变量的数据类型。

2 %ROWTYPE说明
如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，比分别使用%TYPE来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。

   为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供%ROWTYPE定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

   一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。
————————————————
select * from dept;

declare
cursor mycur is
select * from dept order by deptno;
myreco dept%rowtype;
begin 
open mycur;
fetch mycur into myreco;
while mycur%found loop
  dbms_output.put_line(myreco.deptno || ' '|| myreco.loc);
  fetch mycur into myreco;
end loop;
close mycur;
end;

declare
  cursor mycur(myjob varchar2) is
    select * from emp where job = myjob;
begin
  for myreco in mycur('SALESMAN') loop
    dbms_output.put_line(myreco.ename);
  end loop;
end;

select * from emp;

declare
  dept_no  emp.deptno%type;
  emp_no   emp.empno%type;
  emp_name emp.ename%type;
  cursor emp_cur(deptparam number) is
    select empno, ename from emp where deptno = deptparam;
begin
  dept_no := &部门编号;--20
  open emp_cur(dept_no);
  loop
    fetch emp_cur
      into emp_no, emp_name;
    exit when emp_cur%notfound;
    dbms_output.put_line(emp_no || ' ' ||emp_name);
  end loop;
  close emp_cur;
end;

declare
  cursor mycur(dept_no integer) is
    select * from dept where deptno > dept_no for update;
begin
  for myreco in mycur(50) loop
    delete from dept where current of mycur;
  end loop;
end;

---修复非宽带移机场景
begin
  for rec in (SELECT t.key_ele_id
                FROM aiorder.COM_CONSUME_EX_579 t
               WHERE t.state = 'E'
                 and t.err_desc LIKE '%调用帐管资金反冲接口报错%') LOOP
  
    update aiorder.ordx_broad_pre_579 a
       set a.pay_way_state = '1',
           a.remarks       = '调用帐管资金反冲接口数据修复'
     where a.pay_way_state = '0'
       --and a.customer_order_id = 57900009310353;
  
    update aiorder.ord_user_ext_579 b
       set b.attr_value = '1'
     where b.attr_code = '101412'
       and b.attr_value = '0'
       --and b.cust_order_id = 57900009310353;
  
    update aiorder.COM_CONSUME_EX_579 t
       set t.redo_times = 1, t.state = 'C'
     WHERE t.state = 'E'
       --and t.key_ele_id = 57900009310353;
    commit;
  end loop;
end;

--等竣工沉淀后根据取出的订单编号将订单表数据改回原来的
update aiorder.ordx_broad_pre_579 a set a.pay_way_state='0',a.remarks='调用帐管资金反冲接口数据修复' where a.pay_way_state='1' and a.customer_order_id=57900009310353;
update aiorder.ord_user_ext_h_579_201805 b set b.attr_value ='0' where b.attr_code='101412' and b.attr_value='1' and b.cust_order_id=57900009310353;

  
-------------------游标的灵活运用2(循环游标的应用/REF游标的应用)--------------------   
循环游标用于简化游标处理代码
当用户需要从游标中提取所有记录时使用
循环游标的语法如下： 
FOR <record_index> IN <cursor_name>
LOOP
	<executable statements>
END LOOP;

declare
  cursor c_dept is
    select deptno, dname from dept order by deptno;
  cursor c_emp(p_dept varchar2) is
    select ename, sal from emp where deptno = p_dept order by ename;
  v_salary emp.sal%type;
begin
  for r_dept in c_dept loop--循环游标
    dbms_output.put_line('Department:' || r_dept.deptno || '-' ||
                         r_dept.dname);
    v_salary := 0;
    for r_emp in c_emp(r_dept.deptno) loop
      dbms_output.put_line('Name:' || r_emp.ename || ' salary=' ||
                           r_emp.sal);
      v_salary := v_salary + r_emp.sal;
    end loop;
    dbms_output.put_line('total salary for dept:' || v_salary);
  end loop;
end;
REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
创建游标变量需要两个步骤：
声明 REF 游标类型
声明 REF 游标类型的变量
用于声明 REF 游标类型的语法为：
TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];

打开游标变量的语法如下：
	 OPEN cursor_name FOR select_statement;
声明强类型的 REF 游标
TYPE my_curtype IS REF CURSOR
  RETURN stud_det%ROWTYPE;
order_cur my_curtype; 
声明弱类型的 REF 游标
TYPE my_ctype IS REF CURSOR;
stud_cur my_ctype;

create table 学生表
(学号  char(5) not null primary key,
 姓名  varchar(10),
 性别  char(2),
 年龄  int
);
insert into 学生表 values('95001','李勇','男',20);
insert into 学生表 values('95002','刘晨','女',21);
insert into 学生表 values('95003','刘成名','男',18);
insert into 学生表 values('95006','王成','男',19);
select * from 学生表;

declare
  type cursor_type is ref cursor;
  stu_cursor cursor_type;
  v_stu      学生表%rowtype;--定义一个表示表中一行记录的变量
begin
  open stu_cursor for
    select * from 学生表 where 性别 = '男';
  loop
    fetch stu_cursor
      into v_stu;
    exit when stu_cursor%notfound;--当最近的一条sql语句没有涉及任何行的时候，则返回true。否则返回false
    dbms_output.put_line(v_stu.学号 || ' ' || v_stu.姓名 || ' ' || v_stu.性别 || ' ' || v_stu.年龄);
  end loop;
  close stu_cursor;
end;

declare
  type empcurtyp is ref cursor;
  type idlist is table of emp.empno%type;
  type namelist is table of emp.ename%type;
  type sallist is table of emp.sal%type;
  emp_cv empcurtyp;
  ids    idlist;
  names  namelist;
  sals   sallist;
  row_cn number;
begin
  open emp_cv for
    select empno, ename, sal from emp;
  fetch emp_cv bulk collect
    into ids, names, sals;
  close emp_cv;
  for i in ids.first .. ids.last loop
    dbms_output.put_line(ids(i) || ' ' || names(i) || ' ' || sals(i));
  end loop;
end;
游标变量的优点和限制
游标变量的功能强大，可以简化数据处理。
游标变量的优点有：
可从不同的 SELECT 语句中提取结果集
可以作为过程的参数进行传递
可以引用游标的所有属性
可以进行赋值运算
使用游标变量的限制：
不能在程序包中声明游标变量
FOR UPDATE子句不能与游标变量一起使用
不能使用比较运算符

Cursor与 Ref Cursor区别:
从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors可以动态打开。
 
Ref cursor根据逻辑动态打开；而游标cursor定义好了就无法修改了
ref cursor可以返回给客户端，cursor则不行。
cursor可以是全局的global ，ref cursor则必须定义在过程或函数中。
ref cursor可以在子程序间传递，cursor则不行。
cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常
          用在：向客户端返回结果集。

总结:
游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF 游标时，不需要将 SELECT 语句与 其关联 

select * from emp;

declare
  type emp_type is ref cursor;
  cur    emp_type;
  name   varchar2(20);
  salary number(7, 2);
begin
  open cur for 'select ename,sal from emp where job=:1'
    using 'SALESMAN';
  loop
    fetch cur
      into name, salary;
    exit when cur%notfound;
    dbms_output.put_line(name || ':' || salary);
  end loop;
  close cur;
end;