首页 > 代码库 > Oracle中的游标(光标)--来自Oracle赵强老师
Oracle中的游标(光标)--来自Oracle赵强老师
表、select语句、游标:返回结果都能是一个集合。
注意:游标的结果是一个集合。
--查询并打印员工的姓名和薪水 set serveroutput on /* 光标: 1. 光标的属性: %isopen %rowcount(返回的行数) %notfound %found 2. 默认情况下,一次性打开300个光标 SQL> show parameter cursor NAME TYPE VALUE ------------------------------------ ----------- --------------- cursor_sharing string EXACT cursor_space_for_time boolean FALSE open_cursors integer 300 session_cached_cursors integer 20 修改光标: alter system set open_cursors=400; */ declare --定义一个光标 cursor cemp is select ename,sal from emp; pename emp.ename%type;引用变量 psal emp.sal%type;引用变量 begin open cemp; loop --取一条记录 fetch cemp into pename,psal; --退出: fetch没有取到 exit when cemp%notfound; dbms_output.put_line(pename||'的薪水是'||psal); end loop; close cemp; end; /
--查询某个部门的员工姓名 set serveroutput on declare cursor cemp(dno number) is select ename from emp where deptno=dno; pename emp.ename%type; begin open cemp(20); loop fetch cemp into pename; exit when cemp%notfound; dbms_output.put_line(pename); end loop; close cemp; end; /
/* SQL语句 select to_char(hiredate,'YYYY') from emp --> 光标 --> 循环 --> 退出条件 变量: count80 number := 0; count81 number := 0; count82 number := 0; count87 number := 0; */ set serveroutput on declare cursor cemp is select to_char(hiredate,'YYYY') from emp; phiredate varchar2(4); count80 number := 0; count81 number := 0; count82 number := 0; count87 number := 0; begin open cemp; loop --取一个员工的年份 fetch cemp into phiredate; exit when cemp%notfound; --判断年份 if phiredate = '1980' then count80 := count80+1; elsif phiredate = '1981' then count81 := count81+1; elsif phiredate = '1982' then count82 := count82+1; else count87 := count87 + 1; end if; end loop; close cemp; --输出 dbms_output.put_line('Total:'||(count80+count81+count82+count87)); dbms_output.put_line('1980:'||count80); dbms_output.put_line('1981:'||count81); dbms_output.put_line('1982:'||count82); dbms_output.put_line('1987:'||count87); end; /
/* SQL语句: select empno,sal from emp order by sal --> 光标 --> 循环 --> 退出条件: 1. 总额> 5w 2. 所有人涨完 变量: 涨工资的人数: countEmp number := 0; 涨后的工资总额: salTotal number; 1. select sum(sal) into salTotal from emp; 2. 涨后 = 涨前 + sal * 0.1 练习: 工资总额不能超过5w */ set serveroutput on declare cursor cemp is select empno,sal from emp order by sal; pempno emp.empno%type; psal emp.sal%type; --涨工资的人数: countEmp number := 0; --涨后的工资总额: salTotal number; begin --得到工资初始值 select sum(sal) into salTotal from emp; open cemp; loop --1. 总额> 5w exit when salTotal > 50000; --取一个员工 fetch cemp into pempno,psal; --2. 所有人涨完 exit when cemp%notfound; --涨工资 update emp set sal=sal*1.1 where empno=pempno; --人数+1 countEmp := countEmp +1; --总额 salTotal := salTotal + psal * 0.1; end loop; close cemp; commit; dbms_output.put_line('人数:'||countEmp||' 涨后的工资总额:'||salTotal); end; /
/* SQL语句: 部门: select deptno from dept --> 光标 --> 循环 --> 退出条件 部门中员工的薪水: select sal from emp where deptno=?? --> 带参数的光标 --> 循环 --> 退出条件 变量: 每个段的人数: count1 number; count2 number; count3 number; 部门的工资总额: salTotal number; 1. 累加 2. select sum(sal) into salTotal from emp where deptno=?? */ set serveroutput on declare --部门 cursor cdept is select deptno from dept; pdeptno dept.deptno%type; --部门中员工的薪水 cursor cemp(dno number) is select sal from emp where deptno=dno; psal emp.sal%type; --每个段的人数: count1 number; count2 number; count3 number; --部门的工资总额: salTotal number; begin open cdept; loop --取一个部门 fetch cdept into pdeptno; exit when cdept%notfound; --初始化 count1:=0; count2:=0; count3:=0; --部门的工资总额 select sum(sal) into salTotal from emp where deptno=pdeptno; --部门中员工的薪水 open cemp(pdeptno); loop -- 取一个员工的薪水 fetch cemp into psal; exit when cemp%notfound; --判断薪水 if psal < 3000 then count1:=count1+1; elsif psal>=3000 and psal< 6000 then count2:=count2+1; else count3:=count3+1; end if; end loop; close cemp; --保存当前部门 insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0)); end loop; close cdept; commit; dbms_output.put_line('完成'); end; /
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。