plsql 游标

游标

游标是一种从多条数据的结果集中每次提取一行数据的机制
游标可以充当指针 可以便利结果集中所有的行
但是一次只能取一行值 ；

游标提供了在逐行的基础上操作表中数据的方法
游标的结果集是由select语句指定的；

静态游标：显示游标----->普通的
----->带参数的
隐式游标

动态游标：强类型
弱类型
动态游标

静态游标和动态游标的区别？
静态游标的结果集是固定的 中间不能改变
动态游标的结果集是不固定的 每次打开都可以更换结果集

显示游标和隐式游标的区别 ？
显示游标的结果集是在declare部分声明的 中途不能改变 ；
隐式游标的结果集不用声明 切游标无需打开 关闭 这些系统会自动完成；

游标的四个属性
cur_name%isopen 判断游标是否打开 （对,错） --布尔型
cur_name%found 判断游标的指针是否有值（对，错）–布尔型
cur_name%notfound 判断游标的指针是否没值（对,错）–布尔型
cur_name%rowcount 返回游标的指针指过的行数 --数值

-游标的循环 普通循环+%notfound
while循环+%found
游标+for循环

DECLARE 
CURSOR CUR_1 IS SELECT * FROM DEPT; --声明了一个静态的显示游标
V_DEPT DEPT%ROWTYPE;
begin
   OPEN CUR_1;
   LOOP
     FETCH CUR_1 INTO V_DEPT;
     EXIT WHEN CUR_1%NOTFOUND;--Y游标中没有数据直接退出
     DBMS_OUTPUT.put_line(V_DEPT.DEPTNO||CHR(32)||V_DEPT.DNAME||CHR(32)||V_DEPT.LOC);
   END LOOP;
   CLOSE CUR_1;
END;

普通循环+%notfound的步骤 OPEN->LOOP->FETCH->EXIT WHEN %NOTFOUND
->打印->END LOOP->CLOSE

DECLARE 
CURSOR CUR_1 IS SELECT * FROM DEPT; --声明了一个静态的显示游标
V_DEPT DEPT%ROWTYPE;
BEGIN 
  OPEN CUR_1; --开
  FETCH CUR_1 INTO V_DEPT;--先匹配一次在去判断
  WHILE CUR_1%FOUND 
    LOOP 
       DBMS_OUTPUT.put_line(V_DEPT.DEPTNO||CHR(32)||V_DEPT.DNAME||CHR(32)||V_DEPT.LOC);
       --先打印在匹配
       FETCH CUR_1 INTO V_DEPT;
     END LOOP;
    CLOSE CUR_1;
END;

WHILE+%found的步骤

OPEN->FETCH->WHILE%FOUND->LOOP
->先打印->FETCH->END LOOP->CLOSE;

–静态游标-显示–带参数
–打印某个部门某中职位的员工信息

DECLARE 
CURSOR CUR_1(V_DEPTNO NUMBER,V_JOB VARCHAR2) IS 
       SELECT * FROM EMP WHERE DEPTNO=V_DEPTNO AND JOB=V_JOB;
V_E EMP%ROWTYPE;
BEGIN 
  OPEN CUR_1(10,'MANAGER'); --开游标时对参数进行赋值
  LOOP
    FETCH CUR_1 INTO V_E;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E.ENAME||CHR(32)||V_E.JOB);
  END LOOP;
  CLOSE CUR_1;
  DBMS_OUTPUT.put_line('---------------------------------');
  
    OPEN CUR_1(20,'CLERK'); --重新打开游标时对参数进行赋值
  LOOP
    FETCH CUR_1 INTO V_E;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E.ENAME||CHR(32)||V_E.JOB);
  END LOOP;
  CLOSE CUR_1; 
end;

–静态游标–隐式游标
不用申明 不用打开 不用赋值 不用关闭
常见的隐私游标 DELETE /UPDATE/INSERT /SELECT INTO 单行赋值

隐式游标的四个属性
sql%isopen 判断游标是否打开 永远返回错
sql%found 判断游标的指针是否有值（对，错）最近一次是否有结果–布尔型
sql%notfound 判断游标的指针是否没值（对,错） 最近一次是否没有结果–布尔型
sql%rowcount 返回游标的指针指过的行数 返回最近一次的记录数 --数值

DECLARE 
V_E EMP%ROWTYPE;
BEGIN 
  UPDATE EMP_1 SET ENAME=LOWER(ENAME) WHERE DEPTNO=10;
  IF SQL%FOUND THEN 
    DBMS_OUTPUT.put_line('真的更新了');
    ELSIF SQL%NOTFOUND THEN 
      DBMS_OUTPUT.put_line('真的没更新了');
      end IF;
     DBMS_OUTPUT.put_line('更新了'||SQL%ROWCOUNT||'行');
     
DBMS_OUTPUT.put_line('=================================');

   DELETE FROM EMP_1 WHERE JOB='MANAGER';
     IF SQL%FOUND THEN 
    DBMS_OUTPUT.put_line('真的删了');
    ELSIF SQL%NOTFOUND THEN 
      DBMS_OUTPUT.put_line('真的没删了');
      end IF;
     DBMS_OUTPUT.put_line('删了'||SQL%ROWCOUNT||'行');
     
DBMS_OUTPUT.put_line('=================================');

     insert INTO EMP_1  SELECT * FROM EMP WHERE JOB='MANAGER';
        IF SQL%FOUND THEN 
               DBMS_OUTPUT.put_line('真的插了');
            ELSIF SQL%NOTFOUND THEN 
               DBMS_OUTPUT.put_line('真的没插了');
              end IF;
              DBMS_OUTPUT.put_line('插了'||SQL%ROWCOUNT||'行'); 
DBMS_OUTPUT.put_line('=================================');  

SELECT EMPNO INTO V_E.EMPNO FROM EMP WHERE ENAME='KING';
   IF SQL%FOUND THEN 
    DBMS_OUTPUT.put_line('真的FU了');
    ELSIF SQL%NOTFOUND THEN 
      DBMS_OUTPUT.put_line('真的没FU了');
      end IF;
     DBMS_OUTPUT.put_line('FU了'||SQL%ROWCOUNT||'行');
DBMS_OUTPUT.put_line('=================================');
   
   IF SQL%ISOPEN THEN 
      DBMS_OUTPUT.put_line('打开');
      ELSE 
         DBMS_OUTPUT.put_line('真的没打');
         END IF;
END;

–动态游标

DECLARE 
CUR_1 SYS_REFCURSOR;  --直接声明了一个动态游标 
V_D DATE;
V_C1 VARCHAR2(20);
V_C2 VARCHAR2(20);
BEGIN 
  --查询今年所有的星期五 
  OPEN CUR_1 FOR
   SELECT * FROM (   
   SELECT DATE'2020-12-31'+LEVEL LV FROM DUAL CONNECT BY LEVEL<=365)
   WHERE TO_CHAR(LV,'D')=6;
   
   LOOP
     FETCH CUR_1 INTO V_D;
     EXIT WHEN CUR_1%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(V_D);
   END LOOP;
   CLOSE CUR_1;
   
   ---------------------------
   --查询emp中每个人的姓名和经理的姓名 
   OPEN CUR_1 FOR SELECT A.ENAME,B.ENAME FROM EMP A,EMP B
                  WHERE A.MGR=B.EMPNO(+);
   LOOP
     FETCH CUR_1 INTO V_C1,V_C2;
     EXIT WHEN CUR_1%NOTFOUND;
     DBMS_OUTPUT.put_line(V_C1||' '||V_C2);
    END LOOP;
  CLOSE CUR_1;
END;

--把入职日期看成是生日 查询每个人活了多少年又几个月
DECLARE 
CUR_1 SYS_REFCURSOR;
V_E VARCHAR2(20);
V_N VARCHAR2(50);
BEGIN 
  OPEN CUR_1 FOR 
  SELECT ENAME,FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)||'年'||
         FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))||'月'
  FROM EMP;
  
  LOOP
    FETCH CUR_1 INTO V_E,V_N;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E||'活了'||V_N);
   END LOOP;
  CLOSE CUR_1;
END; 

–动态游标 --强类型

可以更换结果集  但是不能更换结果集的格式

CREATE TABLE EMP_A AS SELECT * FROM EMP;
CREATE TABLE EMP_B AS SELECT * FROM EMP;
CREATE TABLE EMP_C AS SELECT * FROM EMP;

DECLARE 
TYPE CUR_1_REF IS REF CURSOR RETURN EMP%ROWTYPE;--声明了一个强类型
cur_1 CUR_1_REF;--把声明的类型赋给变量 此时变量就成了强类型动态游标
v_E EMP%ROWTYPE;
BEGIN 
  DBMS_OUTPUT.put_line('EMP_A中的10部门的员工姓名和部门编号');
  OPEN CUR_1 FOR SELECT * FROM EMP_A WHERE DEPTNO=10;
  LOOP
    FETCH CUR_1 INTO V_E;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E.ENAME||' '||V_E.DEPTNO);
  END LOOP;
  CLOSE CUR_1;
  DBMS_OUTPUT.put_line('EMP_B中工作是销售的员工姓名和工作');
  OPEN CUR_1 FOR SELECT * FROM EMP_B WHERE JOB='SALESMAN';
    LOOP
    FETCH CUR_1 INTO V_E;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E.ENAME||' '||V_E.JOB);
  END LOOP;
  CLOSE CUR_1;
  DBMS_OUTPUT.put_line('EMP_C中1981年前入职的员工姓名和入职日期');
  OPEN CUR_1 FOR SELECT * FROM EMP_C WHERE TO_CHAR(HIREDATE,'YYYY')<1981;
    LOOP
    FETCH CUR_1 INTO V_E;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_E.ENAME||' '||V_E.HIREDATE);
  END LOOP;
  CLOSE CUR_1;
END;

–动态游标 --弱类型

弱类型游标没有return 中途不仅可以更换结果集 还可以更换格式

DECLARE 
TYPE CUR_1_REF IS REF CURSOR; --声明了一个类型
CUR_1 CUR_1_REF;--声明了一个弱类型动态游标 
V_N NUMBER;
V_C VARCHAR2(20);
BEGIN 
  DBMS_OUTPUT.put_line('每种职位的人数');
  OPEN CUR_1 FOR SELECT JOB,COUNT(1) FROM EMP GROUP BY JOB;
  LOOP
    FETCH CUR_1 INTO V_C,V_N;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_C||' '||V_N);
  END LOOP;
  CLOSE CUR_1;
  DBMS_OUTPUT.put_line('每个人的工资等级');
  OPEN CUR_1 FOR SELECT ENAME,GRADE FROM EMP E,SALGRADE S
                 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
  LOOP
    FETCH CUR_1 INTO V_C,V_N;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.put_line(V_C||' '||V_N);
  END LOOP;
  CLOSE CUR_1; 
END;

游标+for循环
1、带游标名
声明一个显示游标 不用打开 不用关闭 不用赋值
因为有名字 所以可以使用游标属性

DECLARE 
CURSOR CUR_1 IS SELECT * FROM EMP;
BEGIN 
  FOR I IN CUR_1 
    LOOP
      DBMS_OUTPUT.put_line(I.EMPNO||' '||I.ENAME);
      --EXIT WHEN CUR_1%ROWCOUNT=5;   --因为有名字 可以使用游标属性
    END LOOP;
END;

2、不带游标名
不用打开 不用赋值 不用声明 不用关闭
因为没有游标名字 所以不能使用游标属性

BEGIN 
  FOR I IN(SELECT * FROM EMP)
  LOOP
    DBMS_OUTPUT.put_line(I.EMPNO||' '||I.ENAME);
  END LOOP;
END;