PLSQL：静态游标和动态游标

转载

减肥的胖小鱼 2023-06-30 11:15:27 博主文章分类：PLSQL

文章标签 游标 文章分类 Oracle 数据库

一、游标的定义

==游标==是++一种从多条数据的结果集中每次提取一行数据的机制。游标可以充当指针，可以便利结果集中所有的行，但是一次只能取一行值。++ 游标提供了在逐行的基础上操作表中数据的方法，游标的结果集是由select语句指定的。

1.静态游标和动态游标的区别？

==静态游标==的++结果集是固定的，中间不能改变。++ ==动态游标==的++结果集是不固定的，每次打开都可以更换结果集。++

2.显示游标和隐式游标的区别？

==显示游标==的结果集是++在declare部分声明的，中途不能改变。++ ==隐式游标==的++结果集不用声明，且游标无需打开、获取、关闭，这些系统会自动完成++。

3.游标的四个属性

==cur_name%isopen==：判断游标是否打开（对,错） --布尔型； ==cur_name%found==：判断游标的指针是否有值（对，错）–布尔型； ==cur_name%notfound==：判断游标的指针是否没值（对,错）–布尔型； ==cur_name%rowcoun==：返回游标的指针指过的行数 --数值。

4.游标的循环

==普通循环==：%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

/** while循环*/
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;

5.静态游标

5.1显式游标–带参数

–打印某个部门某中职位的员工信息

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;

5.2静态游标–隐式游标

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

5.3 隐式游标的四个属性

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;

6.动态游标

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;

6.1动态游标 --强类型

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

REATE 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;

6.2动态游标 --弱类型

弱类型游标没有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;

7.游标+for循环

7.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;

7.2、不带游标名

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

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

上一篇：Oracle:Synonyms和Squences【同义词和序列】

下一篇：PLSQL:异常和异常传播

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯