PL/SQL 游标

一.游标引入和作用
在实际使用查询的过程中 例如 SELECT * FROM EMP; 一次会获取多行数据，这个数据可能是几百，几千甚至更多行
这样影响我们对数据的使用效率 就这样引入了一个能一次输出一行的数据库访问机制：游标
通过对游标遍历，保存数据库查询数据，方便下次使用找到需要的部分数据，这样的效率远高于SELECT 语句
优点；

缺点：由于游标也是一种指针便利类型的机制，当数据被全部读取时，效率较低

二.游标的使用步骤
1.游标的声明
2.打开游标
3.提取数据
4.关闭游标,释放资源

三.游标运用

--游标的简单运行

DECLARE
--CURSOR CUR; --声明了游标 但是没写数据类型
--报错 无主体的游标应声明返回值类型
CURSOR CUR IS SELECT * FROM EMP;
V_EMP EMP%ROWTYPE;
游标的输出方式：插入变量后 输出变量 
BEGIN
OPEN CUR;  
--FOR V_EMP IN CUR LOOP--位置怎么样才算对  V_EMP 相当于I CUR 表示循环范围
--Q3:
FOR 循环会自动打开 关闭游标
FOR循环还会自动 遍历 应该是开发者为了让FOR循环变得简单 
    FETCH CUR INTO V_EMP;
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
--END LOOP;
CLOSE CUR;
END;

1.游标的四种状态代码
%FOUND:是否返回数据 默认FALSE 若找到则变为TRUE
%NOTFOUND：是否返回数据  默认FALSE 若找不到则变TURE
%ISOPEN：游标是否打开 默认FALSE 若打开则变为TRUE
%ROWCOUNT：返回记录的行数，默认是0
--准备工作 B2C函数

create or replace function b2c(b boolean) return varchar2--为什么要写这个函数 不能直接输出吗
is
begin
  if b then
    return 'TRUE';
  else
    return 'FALSE';
  end if;
end;

2.隐式游标

DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
SELECT * INTO V_EMP FROM EMP WHERE ROWNUM<2;
    DBMS_OUTPUT.PUT_LINE(B2C(SQL%FOUND));--隐式游标的关键字是SQL
    DBMS_OUTPUT.PUT_LINE(B2C(SQL%NOTFOUND));--同样需要使用TF判断函数
END;

3.显示游标

DECLARE
CURSOR CUR IS SELECT * FROM EMP;
V_EMP EMP%ROWTYPE;
BEGIN
  
OPEN CUR; 
    FETCH CUR INTO V_EMP;
    DBMS_OUTPUT.PUT_LINE(B2C(CUR%FOUND));
    DBMS_OUTPUT.PUT_LINE(B2C(CUR%NOTFOUND));
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
CLOSE CUR;
END;
--Q4:当游标不断Fetch、遍历 直到指针指到最后一条数据 这时候再继续提取输出什么 为什么？
--A4:会输出上一条遍历的数据 为什么不输出NULL 原因可能是内部设定

4.游标传参

游标作为一种数据库访问机制，同时可以传递参数
DECLARE
CURSOR CUR(V_MGR NUMBER) IS SELECT * FROM EMP WHERE MGR=V_MGR;--Q4:为什么不能写数据类型大小：参数本来就不能写（）
V_EMP EMP%ROWTYPE;--这种传参方式属实搞不明白 有啥意义 就为了去带个条件这样查询？（地铁老人手机）
BEGIN
  OPEN CUR(&1);
  FETCH CUR INTO V_EMP;
  DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
  CLOSE CUR;
END;--我们以前学的基本都是自上而下运行 这个好像是先把声明语句执行后挂起了 去找V_MGR

四.游标循环
 

1.loop循环
DECLARE
CURSOR CUR(V_MGR NUMBER) IS SELECT * FROM EMP WHERE MGR=V_MGR;
V_EMP EMP%ROWTYPE;
BEGIN
  OPEN CUR(&MGR);
  LOOP
    EXIT WHEN CUR%NOTFOUND;--如果要这么写 当notfound为true时终止循环 为了不多输出重复，
                            --除非每次在循环结束后再判断一次是否为true
                            --用IF语句就可以实现
    FETCH CUR INTO V_EMP;
    IF CUR%NOTFOUND=FALSE THEN
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    END IF;
  END LOOP;
  CLOSE CUR;
END;


DECLARE
CURSOR CUR(V_MGR NUMBER) IS SELECT * FROM EMP WHERE MGR=V_MGR;
V_EMP EMP%ROWTYPE;
BEGIN
  OPEN CUR(&MGR);
  LOOP
    EXIT WHEN CUR%FOUND=FALSE;--如果要这么写 当notfound为true时终止循环 为了不多输出重复，
                            --除非每次在循环结束后再判断一次是否为true
                            --用IF语句就可以实现(FOUND 一样的)
    FETCH CUR INTO V_EMP;
    IF CUR%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    END IF;
  END LOOP;
  CLOSE CUR;
END;

2.WHILE 循环

DECLARE
CURSOR CUR(V_MGR NUMBER) IS SELECT * FROM EMP WHERE MGR=V_MGR;
V_EMP EMP%ROWTYPE;
BEGIN
  OPEN CUR(&MGR);
WHILE (B2C(CUR%NOTFOUND)='FALSE') LOOP--在WHILE循环这里 不能直接判断CUR%NOTFOUND=FALSE 
    FETCH CUR INTO V_EMP;--WHILE循环似乎在读到第一个FALSE的时候就不进入判断了 直接不循环
    IF CUR%NOTFOUND=FALSE THEN--所以要写成B2C表达式判断布尔类型
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);--IF没有被影响
    END IF;
  END LOOP;
  CLOSE CUR;
END;


3.FOR 循环

DECLARE
CURSOR CUR(V_MGR NUMBER) IS SELECT * FROM EMP WHERE MGR=V_MGR;
V_EMP EMP%ROWTYPE; 
BEGIN
  FOR V_EMP IN CUR(&MGR) LOOP
    IF CUR%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    END IF;
  END LOOP;
END;

五.游标其它

1.RETURN--强类型
--在之前报过一个错，当不声明游标的数据类型时
--报错 无主体的游标应声明返回值类型
--即RETURN
DECLARE
CURSOR CUR RETURN EMP%ROWTYPE IS SELECT * FROM EMP;
--一旦使用RETURN 就必须让CUR和这个返回值类型相同
V_EMP EMP%ROWTYPE;
BEGIN
  OPEN CUR;
  FETCH CUR INTO V_EMP;
  DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
  CLOSE CUR;
END;

2.FOR UPDATE
--WHERE CURRENT OF CUR
--将当前行更新
DECLARE
CURSOR CUR IS SELECT * FROM EMP FOR UPDATE;--关键字
V_EMP EMP%ROWTYPE;
BEGIN
  OPEN CUR;
  FETCH CUR INTO V_EMP;
  UPDATE EMP SET ENAME='AAA' WHERE CURRENT OF CUR;
  CLOSE CUR;
END;

3.动态游标
--之前在学PL/SQL变量类型时，有一个自定义类型记录变量
--TYPE T_NAME IS RECORD(); 先自定义
--V_NAME TNAME; 再传值
--在这里也是相似的 不过不再用RECORD 而是 REF
DECLARE
TYPE T_CUR IS REF CURSOR;
V_EMP EMP%ROWTYPE;
CUR T_CUR;
BEGIN
  --何为动态 ''单引号即为动态
  OPEN CUR FOR 'SELECT * FROM EMP';--第二种初始化赋值的方式
  --动态代表什么意思？
  --动态语句是因为在储存过程中，由于参数的不确定性，所执行SQL语句也不同
  FETCH CUR INTO V_EMP;
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
   CLOSE CUR;
END;

4.SYS_REFCURSOR--是一种游标类型
DECLARE
CUR SYS-REFCURSOR;
V_EMP EMP%ROWTYPE;
BEGIN
 OPEN CUR FOR SELECT * FROM EMP;
LOOP
FETCH CUR INTO V_EMP;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_lINR(V_EMP.ENAME);
END LOOP;
CLOSE CUR;
END;

六.问题

--1.按工号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。
--平均工资差函数
CREATE OR REPLACE FUNCTION MINUS_EMP(V_SAL NUMBER)
RETURN NUMBER
IS 
EMP_AS NUMBER(6);
EMP_SAL NUMBER(6);
BEGIN
  SELECT AVG(SAL) INTO EMP_AS FROM EMP ;
   EMP_SAL:=V_SAL-EMP_AS;
  RETURN EMP_SAL;
END;
--验证
DECLARE 
SAL NUMBER(10);
BEGIN
   SAL:=MINUS_EMP(100);
   DBMS_OUTPUT.PUT_LINE(SAL);
END;
--题目实现
SELECT ENAME,SAL,MINUS_EMP(SAL)  FROM EMP ORDER BY EMPNO ASC

DECLARE
CURSOR CUR IS SELECT ENAME,SAL,MINUS_EMP(SAL) MINUS_SAL FROM EMP ORDER BY EMPNO ASC;
BEGIN
  FOR V_EMP IN CUR LOOP--FOR 是 I TO VAR
    --LOOP 和 WHILE 是 CUR INTO VAR
       DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME||' '||V_EMP.SAL||' '||V_EMP.MINUS_SAL);
  END LOOP;
END;
--2.为所有雇员增加工资，工资在1000以内的增加30%，在1000-2000增加20%，2000以上增加10%。

DECLARE
CURSOR CUR IS SELECT * FROM EMP FOR UPDATE;
BEGIN
  FOR V_EMP IN CUR LOOP
    IF V_EMP.SAL<1000 THEN
    UPDATE EMP SET SAL=SAL*1.3 WHERE EMPNO=V_EMP.EMPNO;
    ELSIF V_EMP.SAL BETWEEN 1000 AND 2000 THEN
    UPDATE  EMP SET SAL=SAL*1.3 WHERE EMPNO=V_EMP.EMPNO;
      ELSE 
      UPDATE  EMP SET SAL=SAL*1.1 WHERE EMPNO=V_EMP.EMPNO;
       END IF;
  END LOOP;
END;
--3.统计输出部门名称、部门总人数、总工资及部门经理。
DECLARE
  CURSOR CUR IS
    SELECT D.DEPTNO,
           (SELECT DNAME FROM DEPT P WHERE P.DEPTNO = D.DEPTNO) DNAME,
           COUNT(E.DEPTNO) C,
           SUM(SAL) S      
      FROM DEPT D
      LEFT JOIN EMP E
        ON E.DEPTNO = D.DEPTNO
     WHERE E.DEPTNO = &DEPTNO
     GROUP BY D.DEPTNO;
     CURSOR CUR1(V_DEPTNO NUMBER) IS SELECT EMPNO FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO,EMPNO;
BEGIN
  FOR V_EMP IN CUR LOOP
    DBMS_OUTPUT.PUT_LINE(V_EMP.DNAME || V_EMP.C || V_EMP.S);
    FOR V IN CUR1(V_EMP.DEPTNO) LOOP
          DBMS_OUTPUT.PUT_LINE(V.EMPNO);
    END LOOP;
  END LOOP;
END;
--4.显示工资最高的前3名雇员的名称和工资。

DECLARE
CURSOR CUR IS SELECT E.*,ROWNUM FROM (SELECT * FROM EMP ORDER BY SAL DESC) E WHERE ROWNUM<4;
BEGIN
  FOR V_EMP IN CUR LOOP
  DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME||' '||V_EMP.SAL);
  END LOOP;
END;