一．游标
1）--do while循环
Declare
Cursor c is select * from emp;
V_emp c%rowtype;
              Begin
                     Open c;
                     loop
                            Fetch c into v_emp;
                            Exit when (c%notfound);
                            Dbms_output.put_line(v_emp.ename); ......

一．存储过程
1）--创建存储过程
Create or replace procedure p
Is
         Cursor c is
                Select * from emp for update;
Begin
         For v_emp in c loop
                If (v_emp.deptno = 10) then
                       Update emp set sal = sal +10 where current of c;
                Elsif (v_emp.deptno = 20) then
                       Update emp set sal = sal +20 where current of c;
          &n ......

一．常见问题
1）求部门中哪些人的薪水最高
Select ename, sal from emp
Join (select max(sal) max_sal, deptno from emp group by deptno) t
On (emp.sal = t.max_sal and emp.deptno = t.deptno);
2）求部门平均薪水的等级
Select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
Join salgrade s on (t.avg_sal between s.lowsal and s.highsal);
3）求部门平均的薪水等级
Select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s on (emp.sal between s. lowsal and s.highsal)) t
Group by deptno;
4）求雇员中有那些人是经理人
Select ename from emp where empno in (select distinct mgr from emp);
5）不准用组函数，求薪水的最高值（面试题）
Select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
6）求平均薪水最高的部门的部门编号
Select deptno from (select avg(sal) avgsal, deptno from emp group by deptno)
Where avgsal = (select max(avgsal) from (select avg(sal) avgsal, d ......

一．表结构
Create table emp
(
       empno number(4) primary key,         --雇员编号
       ename varchar2(10),                               
       job varchar2(9),
       mgr number(4),                                        --经理
       hiredate date,
       sal number(7, 2),                       &n ......

今天新同事用ibatis执行一个脚本时报如上错误，而相关的语句在pl/sql中执行却没有问题，但我检查他的语句时没有问题，相关的参数赋值都没问题，最后发现的语句后面留了个“;”，总算解决了问题，特此记录下。 ......

加法
select sysdate,add_months(sysdate,12) from dual;         --加1年
select sysdate,add_months(sysdate,1) from dual;         --加1月
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual;    --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;    --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
减法
select sysdate,add_months(sysdate,-12) from dual;         --减1年
select sysdate,add_months(sysdate,-1) from dual;         --减1月
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual;    ......