oracle 游标

--异常处理
--select .. into .. 要求返回的结果有且只有一条
--1-未找到数据 no_data_found
--2-实际返回的行数操作请求的行数 too_many_rows
DECLARE
  myname emp.ename%TYPE;
BEGIN
  SELECT ename INTO myname FROM emp
  WHERE empno=1234;
  dbms_output.put_line(myname);
  EXCEPTION                       --之前是有可能出错的代码
  WHEN no_data_found THEN
     dbms_output.put_line('未找到数据');
  WHEN too_many_rows THEN
     dbms_output.put_line('实际返回的行数操作请求的行数');
  WHEN OTHERS THEN
     dbms_output.put_line('出错啦');
END;

--自定义异常
--判断某员工是否存在
DECLARE
  myExp EXCEPTION;--异常类型
  num INTEGER:=0;--用于记录查询出的员工个数
BEGIN
  SELECT COUNT(*) INTO num FROM emp WHERE empno=1234;
  IF num=0 THEN
    --引发自定义异常
    RAISE myExp;
  END IF;
  EXCEPTION--再次处理异常
  WHEN myExp THEN
     --dbms_output.put_line('不存在该员工');
     raise_application_error(-20000,'不存在该员工');
END;

--动态SQL
--1、使用动态SQL可以执行DDL语句（create，alter，drop）
DROP TABLE Test;
CREATE TABLE Test
(
       tid NUMBER,
       tname VARCHAR2(20)
);

DECLARE
   num INTEGER:=0;
BEGIN
   SELECT COUNT(*) INTO num FROM tabs WHERE table_name='TEST';
   IF num>0 THEN
      EXECUTE IMMEDIATE --表示执行动态SQL
      'DROP TABLE Test';
   ELSE
      EXECUTE IMMEDIATE
      'CREATE TABLE Test
      (
       tid NUMBER,
       tname VARCHAR2(20)
       )';
      
       EXECUTE IMMEDIATE
       'INSERT INTO Test VALUES(:1,:2)'
       USING 1,'李四';
   END IF;
END;
SELECT * FROM Test;
--2、在运行，动态处理SQL语句（构造条件）
DECLARE
  myename emp.ename%TYPE;
  myempno emp.empno%TYPE;
BEGIN
  myempno:=7369;
  EXECUTE IMMEDIATE
  'SELECT ename FROM emp WHERE empno=:1'
  INTO myename
  USING myempno;
  dbms_output.put_line(myename); 
END;

--游标：查询结果集的指针，编程的方式访问结果集中的数据
--分类：
--1、隐式游标：
  --DML语句自动应用隐式游标
  --隐式游标统一名称为SQL
  --获得最近的DML语句对数据库的影响
  --游标属性：
  %FOUND
  %NOTFOUND
  %ROWCOUNT
  %ISOPEN
 
BEGIN
  UPDATE empTest SET sal=sal-200 WHERE empno=4564;
  IF SQL%FOUND THEN
    dbms_output.put_line('更新成功，影响了'||SQL%ROWCOUNT||'条记录');
    COMMIT;
  END IF;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('更新失败');
    ROLLBACK;
  END IF;
END;

--显式游标：用来处理返回多行的结果集
--1、定义游标并指向结果集
--2、打开游标
--3、操作游标：循环提取行
--4、关闭游标

DECLARE
  CURSOR mycursor IS
  SELECT * FROM empTest;
  myemp emp%ROWTYPE;
BEGIN
  OPEN mycursor;
  --循环提取行
  LOOP
    FETCH mycursor INTO myemp;
    EXIT WHEN mycursor%NOTFOUND;
    dbms_output.put_line(mycursor%rowcount||myemp.ename);
  END LOOP;
  CLOSE mycursor;
END;

--for循环游标：简化游标操作
--自动打开，关闭，提取行
DECLARE
  CURSOR mycursor IS
  SELECT ename FROM empTest;
BEGIN
  FOR emprow IN mycursor
  LOOP
     dbms_output.put_line(emprow.ename);
  END LOOP;
END;

--简化
BEGIN
  FOR emprow IN (SELECT ename FROM empTest)
  LOOP
     dbms_output.put_line(emprow.ename);
  END LOOP;
END;

--带参数的游标，提高灵活性
--查询部门10的员工信息
DECLARE
  CURSOR mycursor(dno NUMBER) IS
  SELECT * FROM emp WHERE deptno=dno;
BEGIN
  FOR myemp IN mycursor('&dno')
  LOOP
    dbms_output.put_line(myemp.ename||myemp.deptno);
  END LOOP;
END;

--使用游标  更新数据或是删除数据
--1、可以锁定游标中的数据
--2、循环更新时，可以指定更新当前游标，与条件没有关系
--给每个员工加薪500
DECLARE
  CURSOR mycursor IS
  SELECT * FROM empTest FOR UPDATE;
BEGIN
  FOR myemp IN mycursor
  LOOP
      UPDATE empTest SET sal=sal+500 WHERE CURRENT OF mycursor;
  END LOOP;
END;



--REF游标：处理运行时确定结果集
DECLARE
  --游标类型
  TYPE mytype IS REF CURSOR;
  --游标变量
  mycursor mytype;
  tname VARCHAR2(10);
  myemp emp%ROWTYPE;
BEGIN
  tname:='&tname';
  OPEN mycursor FOR 'select * from '||tname;
  LOOP
    FETCH mycursor INTO myemp;
    EXIT WHEN mycursor%NOTFOUND;
    dbms_output.put_line(myemp.ename);
  END LOOP;
  CLOSE mycursor;
END;