java SSM第三章学习内容（oracle普通游标，对象游标，参数游标，修改游标，优化的存储过程，数据库笔试题）
二十：普通游标
当数据大于100万的时候使用游标
例1：
declare
cursor cur_aa is //声明游标
select wage from teachers;
gongzi teachers.wage%type;
begin
open cur_aa; //打开游标
loop
fetch cur_aa into gongzi; //赋值给变量gongzi
exit when cur_aa%NOTFOUND; //判断是否有，如没有进行退出
DBMS_OUTPUT.PUT_LINE(gongzi);
end loop;
close cur_aa;
end;
例2:用while
declare
cursor curaa is
select wage from teachers;
gz teachers.wage%type;
begin
open curaa;
fetch curaa into gz;
while(curaa%FOUND)
loop
DBMS_OUTPUT.PUT_LINE(gz);
fetch curaa into gz;
end loop;
close curaa;
end;
二十一：对象游标
自动开自动关，不用手动关
declare
cursor cur_aa2 is
select * from teachers; //对象是*
begin
for tea in cur_aa2
loop
DOPL(tea.name||tea.wage);
end loop;
end;
二十二：参数游标
例1：如语句有参数
declare
cursor cur_aa3(bt teacher.title%type) is
select wage from teachers where title=bt;
gongzi teachers.wage%type;
begin
open cur_aa('教授');
loop
fetch cur_aa into gongzi;
exit when cur_aa%NOTFOUND;
DOPL(gongzi);
end loop;
close cur_aa;
end;
例2：参数为对象游标
(1.loop)
declare
cursor cur_aa4(zhicheng teachers.title%type)
is select * from teachers
where title=zhicheng;
begin
for tea in cur_aa('教授') //tea是对象
loop
DOPL(tea.name);
DOPL(tea.wage);
end loop;
end;
(2 whlie循环)
declare
cursor curaa(tt teachers.title%type)
is select wage from teachers where title=tt;
gz teachers.wage%type;
begin
open curaa('教授');
fetch curaa into gz;
while(curaa%FOUND)
loop
DBMS_OUTPUT.PUT_LINE(gz);
fetch curaa into gz;
end loop;
close curaa;
end;
二十三：修改游标
1.数据量大于100万要用
2.教授*1.2,副教授*1.1，其他*5%
设置了游标可以直接找职称然后修改工资
declare
cursor cur_aa6 is
select title from teachers for update;
zhicheng teachers.title%type;
begin
open cur_aa6;
loop
fetch cur_aa6 into zhicheng;
exit when cur_aa6%notfound;
case zhicheng
when '教授' then update teachers set wage=wage*1.2 where current of cur_aa6; //current 游标所在行的wage
when '副教授' then update teachers set wage=wage*1.1 where current of cur_aa6;
else update teachers set wage=wage*1.05 where current of cur_aa6;
end case;
end loop;
commit;
close cur_aa6;
dbms_output.put_line('修改成功');
end;
二十四：优化的存储过程（30条原则）
1.连表查询数据小的放在后面
2.where条件最后的要数据量少的
3.少用*，全部查询
4.少访问数据库
5.尽量设置访问记录(set ARRAYSIZE 200;)一次查询获得多少条
6.出现交集或则差集有利于提交效率
7.定时删除重复记录
8.删除truncate代替delete
9.确定没错多用commit;
10.where替换having
挖掘数据外，少用from
多用多行子查询
多用any,all
11.减少对表的查询
12.使用表别名
13.使用内部函数提高效率
14.DEPTNO IN 换成 EXISTS
15.第三方优化工具
16.索引(超过一年半载会失效)要重新使用维护
17.EXISTS替代DISTINCT
18. sqlserver大写,orcle小写
19.java中少用+拼接sql语句
20.避免索引使用计算
21.索引>=替代>
22.UNION 替代or
23.用IN替代or
24.索引遇到is null,is not null,not的时候失效
25.一表创建多个索引分开建
26.UNION-ALL替代UNION
27.避免改变索引列的类型
28.不等于3用!=3
29.检索数据超过30%表中记录，索引失效
30.优化GROUP BY
数据库笔试题目
1 列出emp表中各部门的部门号，最高工资，最低工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;
2 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where job = 'CLERK' group by deptno;
3 对于emp中最低工资小于1000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp as b
where job='CLERK' and 1000>(select min(sal) from emp as a where a.deptno=b.deptno) group by b.deptno
4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select deptno as 部门号,ename as 姓名,sal as 工资 from emp order by deptno desc,sal asc
5 写出对上题的另一解决方法
（请补充）
6 列出'张三'所在部门中每个员工的姓名与部门号
select ename,deptno from emp where deptno = (select deptno from emp where ename = '张三')
7 列出每个员工的姓名，工作，部门号，部门名
select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno
8 列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job='CLERK'
9 对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
select a.ename as 姓名,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.empno
10 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select dname as 部门名,dept.deptno as 部门号,ename as 员工名,job as 工作 from dept,emp
where dept.deptno *= emp.deptno and job = 'CLERK'
11 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) order by a.deptno
12 对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
select count(a.sal) as 员工数,a.deptno as 部门号 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) group by a.deptno order by a.deptno
13 对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序
select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp as a
where (select count(c.empno) from emp as c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1
group by a.deptno order by a.deptno
14 对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as 人数 from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>5