Oracle--游标
游标
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
就是（内存空间, 存储多行数据, 存储一个表格, 临时的存储数据, 减少与硬盘的访问, 提高效率）
变量：本质内存空间: 存储的一个数据
游标的分类:
1.隐式游标: Oracle 系统运行sql语句, 系统创建的游标
insert,update,delete, select into, 都会创建隐式游标, 隐式游标的名字: SQL
2.显示游标: 由用户创建的, 便于用户使用
游标四个属性: 语法格式: 游标名%属性名;
3.动态游标：创建游标的时候,没有绑定SQL语句, 在需要游标,才去绑定sql, 这个游标可以重复使用
/* 游标名%ROWCOUNT 整型 代表DML语句成功执行的行数 游标名%FOUND 布尔型 隐式:值为true表示插入，删除，更新，和单行查询操作成功, 是否找到数据, 有数据返回true, 没有false 游标名%NOTFOUND 布尔型 与SQL%FOUND相反 游标名%ISOPEN 布尔型 DML执行过程中为真，结束为false 打开了返回true, 没有打开, 返回false */
注意: 使用游标的时候, 先要打开, 使用完了, 必须关闭
隐式游标和显示游标都称为 静态游标 创建游标的时候, 绑定了sql语句
隐式游标
DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno = '&id'; --创建一个隐式游标, 隐式游标的名字:SQL IF SQL%FOUND THEN dbms_output.put_line('查询到数据,员工姓名：'||v_emp.ename); ELSE dbms_output.put_line('没有查询到数据'); END IF; END;
显示游标
用户创建, 查询返回多行的时候, 没办法存储到变量, 这个时候,我们需要使用游标来存储
语法： --1.创建游标 在declare中声明 -- 语法: cursor 游标名([参数列表]) is select 语句 -- 创建一个内存空间(游标), 通过select语句,查询到数据, 填充到这块空间中 -- 注意事项: 如果游标带参的, 这个参数的传值,在打开游标的时候传值 --2.打开游标 -- open 游标名([值]); --注意事件, 游标一旦打开,不能再重复打开 --3.提取数据 -- 语法: FETCH 游标名 INTO 变量名1【，变量名2….】 -- FETCH 游标名 INTO 记录变量(%rowtype) -- 注意: 1) fetch一次,只读一行, 一般使用循环来提取 -- 2) fetch 一定在游标打开之后才能读取数据 --4.关闭游标 -- 语法: close 游标名;
例：
--使用游标提取emp表中7788的姓名和岗位： declare --1.创建游标 cursor c_emp is select ename,job from emp where empno = 7788; --创建变量 v_ename emp.ename%type; v_job emp.job%type; begin --2.打开游标, 可以判断是否打开, 没有打开,打开 %isopen 判断游标是否打开 if c_emp%isopen then --打开 null; -- null表示不做任何操作, 让这个格式保留,可读性高 else --没有打开 open c_emp; --open c_emp; end if; --3.提取数据 fetch c_emp into v_ename,v_job; --4. 关闭游标 close c_emp; dbms_output.put_line(v_ename||v_job); end;
例：（带参数的游标） 推荐***
-- 根据部门来加薪, 如果你是10部门, +300, 加薪之后,工资不能超过5500 -- 如果你是20部门, + 400 不能超过 4000 如果你是30部门, + 500,不能超过3000 DECLARE -- 创建游标 cursor c_empcopy(v_deptno empcopy.deptno%type) is select * from empcopy where deptno = v_deptno; -- 接收查询出的一行数据 v_emp empcopy%rowtype; -- 新增后的工资 v_addSal empcopy.sal%type; BEGIN -- 打开游标 -- 如果游标已打开则不进行任何操作 if c_empcopy%isopen then null; else open c_empcopy('&请输入部门编号'); end if; -- 提取数据 loop -- 将查询出的表的每一行数据放在v_emp中 fetch c_empcopy into v_emp; -- 当c_empcopy没有数据了结束循环 exit when c_empcopy%notfound; -- 判断部门编号，添加相应工资 case when v_emp.deptno = 10 then -- 当部门编号为10 if (v_emp.sal+300 > 5500) then v_addSal := 5500; else v_addSal := v_emp.sal + 300; end if; when v_emp.deptno = 20 then if (v_emp.sal+400 > 4000) then v_addSal := 4000; else v_addSal := v_emp.sal + 400; end if; when v_emp.deptno = 30 then if (v_emp.sal+500 > 3000) then v_addSal := 3000; else v_addSal := v_emp.sal + 500; end if; end case; -- 修改表中的工资 update empcopy set sal = v_addSal where empno = v_emp.empno; end loop; -- 关闭游标 close c_empcopy; --提交: 把数据写入到表 commit; END;
执行完数据要点击commit提交事务
update,insert,delete 直接修改表吗? 没有直接修改数据库 Oracle 创建一个隐式游标, 修改的是隐式游标的数据
把这个修改的结果写入到表中, 提交: 手动的提交, 点击软件上 小绿勾, 提交, 小红叉的, 回滚, 刚刚的操作撤销
代码提交: commit (提交) rollback(回滚)
动态游标
创建游标的时候,没有绑定SQL语句, 在需要游标,才去绑定sql, 这个游标可以重复使用
强类型的动态游标: 声明这个游标,确定返回值类型, 很少使用
弱类型的动态游标: 声明这个游标,不需要确定返回值类型, 推荐它
强类型动态游标是指在声明变量时使用return关键字定义游标的返回值类型 弱类型动态游标是指在声明变量时不使用return关键字定义游标的返回类型? 一般动态游标有 REF CURSOR、REF CURSOR RETURN、***SYS_REFCURSOR。 （推荐） REF CURSOR RETURN 为强类型，REF CURSOR 为弱类型、SYS_REFCURSOR 为系统预定义（在STANDARD包中定义的）弱类型。
语法：
-- 步骤: /* 1. 声明一个游标类型 语法 TYPE 游标类型名 IS REF CURSOR ; 2. 声明1中定义的游标类型的变量(游标) 语法: 变量名 游标类型; 没有绑定sql语句 3. 打开游标, 在这一步, 绑定sql语句: 语法 open 游标名(游标变量) for select语句; 4. 获取数据, Fetch 游标名 into 变量 5. 关闭游标 close 游标名 */
例：查询部门编号为10的员工信息
使用动态游标
--使用动态游标: declare --1.声明一个动态游标类型 type MyRefCursorType is ref cursor; --2.声明该类型的游标变量 c_data MyRefCursorType; --行记录的变量 v_dept dept%rowtype; v_emp emp%rowtype; begin if c_data%isopen then null; else --打开游标, 并且绑定sql语句 open c_data for select * from dept; end if; --输出部门信息 loop fetch c_data into v_dept; exit when c_data%notfound; dbms_output.put_line(v_dept.deptno||', '||v_dept.dname||', '||v_dept.loc); end loop; --关闭游标: 把这个游标中的数据清空 close c_data; dbms_output.put_line('=================================='); --重复使用: 打开 -提取-关闭 if c_data%isopen then null; else --打开游标, 并且绑定sql语句 open c_data for select * from emp where deptno = 10; end if; --输出员工信息 loop fetch c_data into v_emp; exit when c_data%notfound; dbms_output.put_line(v_emp.empno||', '||v_emp.ename||', '||v_emp.job); end loop; close c_data; end;
使用系统动态游标 推荐***
--系统动态游标: SYS_REFCURSOR 就是一个动态游标类型 -- 打开 -提取 -关闭 declare --1.定义一个系统游标类型的变量 c_data SYS_REFCURSOR; --行记录的变量 v_dept dept%rowtype; v_emp emp%rowtype; begin if c_data%isopen then null; else --打开游标, 并且绑定sql语句 open c_data for select * from dept; end if; --输出部门信息 loop fetch c_data into v_dept; exit when c_data%notfound; dbms_output.put_line(v_dept.deptno||', '||v_dept.dname||', '||v_dept.loc); end loop; --关闭游标: 把这个游标中的数据清空 close c_data; dbms_output.put_line('=================================='); --重复使用: 打开 -提取-关闭 if c_data%isopen then null; else --打开游标, 并且绑定sql语句 open c_data for select * from emp where deptno = 10; end if; --输出员工信息 loop fetch c_data into v_emp; exit when c_data%notfound; dbms_output.put_line(v_emp.empno||', '||v_emp.ename||', '||v_emp.job); end loop; close c_data; end;
使用静态游标
--使用弱动态游标 --使用弱类型动态游标，完成查询所有的部门信息，再使用该游标查询部门编号为10的员工信息 --如果使用静态游标, 需要创建两个静态游标, 第一个游标,存储的部门信息 第二个游标: 部门编号为10的员工信息 --使用静态游标: declare --存放部门信息的游标 cursor c_dept is select * from dept; --存放部门编号为10的员工信息 cursor c_emp_10 is select * from emp where deptno = 10; --行记录的变量 v_dept dept%rowtype; v_emp emp%rowtype; begin --打印部门信息 if c_dept%isopen then null; else open c_dept; end if; if c_emp_10%isopen then null; else open c_emp_10; end if; --输出部门信息 loop fetch c_dept into v_dept; exit when c_dept%notfound; dbms_output.put_line(v_dept.deptno||', '||v_dept.dname||', '||v_dept.loc); end loop; --输出员工信息 loop fetch c_emp_10 into v_emp; exit when c_emp_10%notfound; dbms_output.put_line(v_emp.empno||', '||v_emp.ename||', '||v_emp.job); end loop; close c_dept; close c_emp_10; end;
自定义一个行类型 *
自定义一个行记录类型 -- 注意: 列名 与查询的列名一致 type myRecode is RECORD(ename emp.ename%type, empno emp.empno%type, job emp.job%type); --定义行记录变量 myEmp myRecode;
例：
-- 结果集变量或查询的返回类型不匹配 -- v_emp:行类型的结构 empno ename job mgr hiredate sal comm deptno -- c_data 游标的数据: ename, empno, job --记录类型, 人为的创建一个行记录, 表%rowtype 行记录的结构是表的结构 -- 创建行记录类型 语法: type 行记录类型名 is RECORD(列名1 数据类型1, 列名2 数据类型2,...) -- 声明这个行记录的变量 declare type myRecode is RECORD(ename emp.ename%type, empno emp.empno%type, job emp.job%type); --1.定义一个系统游标类型的变量 c_data SYS_REFCURSOR; --行记录的变量 v_dept dept%rowtype; --v_emp emp%rowtype; -- emp的行类型 --第二种解决方案: 自定义一个行记录类型 -- 注意: 列名 与查询的列名一致 --定义行记录变量 myEmp myRecode; begin if c_data%isopen then null; else --打开游标, 并且绑定sql语句 open c_data for select * from dept; end if; --输出部门信息 loop fetch c_data into v_dept; exit when c_data%notfound; dbms_output.put_line(v_dept.deptno||', '||v_dept.dname||', '||v_dept.loc); end loop; --关闭游标: 把这个游标中的数据清空 close c_data; dbms_output.put_line('=================================='); --重复使用: 打开 -提取-关闭 if c_data%isopen then null; else --打开游标, 并且绑定sql语句 -- 查询的部门列 open c_data for select ename,empno,job from emp where deptno = 10; end if; --输出员工信息 loop --提取数据 fetch c_data into myEmp; exit when c_data%notfound; dbms_output.put_line(myEmp.ename||', '||myEmp.empno||', '||myEmp.job); end loop; close c_data; end;
例题：
通过使用游标来显示dept表中的部门名称，及其相应的员工列表（提示：可以使用双重循环）
declare cursor c_depts is select * from dept; cursor c_emp_by_deptno(dno emp.deptno%type) is select ename from emp where deptno = dno; v_dept dept%rowtype; v_ename emp.ename%type; begin if c_depts%isopen then null; else open c_depts; end if; --获取部门信息 loop fetch c_depts into v_dept; exit when c_depts%notfound; --根据部门编号,查询这个部门的员工姓名, 打开c_emp_by_deptno游标 --打印部门 dbms_output.put_line(v_dept.dname||' 部门人员列表:'); if c_emp_by_deptno%isopen then null; else open c_emp_by_deptno(v_dept.deptno); end if; --获取指定部门的员工姓名 loop fetch c_emp_by_deptno into v_ename; exit when c_emp_by_deptno%notfound; dbms_output.put_line(' '||v_ename); end loop; --关闭c_emp_by_deptno游标 close c_emp_by_deptno; end loop; --关闭c_depts游标 close c_depts; end;
按照salgrade表中的标准，给员工加薪，1：5%，2：4%，3：3%，4：2%，5：1%， --并打印输出每个人，加薪前后的工资。
DECLARE CURSOR c_emp_grade is select ename,sal,s.grade from empcopy e inner join salgrade s on e.sal between s.losal and s.hisal; type mytable is RECORD(name empcopy.ename%type, sal empcopy.sal%type, grade salgrade.grade%type); mymoney mytable; v_sal empcopy.sal%type; BEGIN if c_emp_grade%isopen then null; else open c_emp_grade; end if; loop fetch c_emp_grade into mymoney; exit when c_emp_grade%notfound; if mymoney.grade = 1 then v_sal := mymoney.sal*(1+0.05); elsif mymoney.grade = 2 then v_sal := mymoney.sal*(1+0.04); elsif mymoney.grade = 3 then v_sal := mymoney.sal*(1+0.03); elsif mymoney.grade = 4 then v_sal := mymoney.sal*(1+0.02); elsif mymoney.grade = 5 then v_sal := mymoney.sal*(1+0.01); end if; update empcopy set sal = v_sal; DBMS_OUTPUT.PUT_LINE('姓名：'||mymoney.name||'原来工资：'|| mymoney.sal || '加薪前后的工资：'||v_sal); end loop; close c_emp_grade; commit; END;
