Oracle基础语法练习

Oracle中序列可以按照自己的意愿自主增长，可以用于很多情况，比如java向Oracle插入数据时乱序时加入Sequence可以让表中数据自主排序。如下代码是生成一个按照自己意愿增长的序列

CREATE SEQUENCE seqTest
INCREMENT BY 2 -- 每次加几个
START WITH 99 -- 从99开始计数
MAXvalue 10000-- 设置最大值
minvalue 99 -- 设置最小值
CYCLE -- 一直累加，不循环
ORDER -- 顺序
CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续
--也可以设置为NOCACHE，一般项目中都会设置缓存

如果希望能够查询当前的序列在多少可以用如下语句

select seqtest.currval,seqtest.nextval from dual;--currval为当前序列的值
--nextval为增加sequence的值，然后显示sequence的值
--注意：在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错 

当然如果向更改当前序列的一些属性可以使用alter语句，例如更改步进值可以使用，并且让下一次显示999可以使用如下代码

alter sequence seqtest increment by 900;--更改为每次增长900
select seqtest.currval,seqtest.nextval from dual;--显示当前序列的值

需要注意的是，Oracle中序列的开始值不能被更改，如果需要让序列从多少重新开始计数的解决方法是：先将原先的序列删除，再重新创建一个新的序列，如需要让序列重新从99开始并且只显示奇数，可以使用如下代码

drop sequence seqtest;
CREATE SEQUENCE seqTest
INCREMENT BY 2 
START WITH 99
MAXvalue 10000
minvalue 99
CYCLE 
ORDER
CACHE 10;
select seqtest.currval,seqtest.nextval from dual;

此外遇到了一个实际问题：

建立表：

职员管理表：职员编号 (PK) ，职员姓名，职员性别，工作年限，备考

职员业绩表：职员编号 (PK) ，工作 日期 (PK) ，工作小时数，加班小时数

职员休假表：职员编号 (PK) ，职员休假小时数，职员剩余休假小时数

要求操作

查询出 7,8,9 月份加班最多的前三名职员

         显示项目：职员姓名，职员性别，工作月份，加班小时数，职员剩余休假小时数

 

要求操作是找出加班时间最长的三名员工，思路是7月找出3个时间最长的，89月依次类推，将9个数据依次比较，找出3个月中加班时间最长的三个。最开始是想在一句select语句中解决，但是发现group by语句经常出现不是单独分组之类的错误，并且由于Oracle中的选出前三用的rownum和order by会产生冲突。最后决定先只从业绩表中找出加班月份最长的员工，将数据放进一个临时表中，再用临时表和另外两张表联查得到最后的结果。代码如下：

select * from (
select * from (
select sum(p.overtime) as s ,pid,to_char(7)
from per p
where to_char(p.dat,'MM') = '07'
group by p.pid
union
select sum(overtime),pid,to_char(8)
from per p
where to_char(p.dat,'MM') = '08'
group by pid
union
select sum(overtime),pid,to_char(9)
from per p
where to_char(p.dat,'MM') = '09'
group by pid)
order by s desc）
where rownum <=3;

以上可以将需求的三个员工的员工id找出并且得到他们的加班月份和加班时间。然后最开始想使用临时表的方式将数据放进去，但发现临时表很难删除，需要删除别的东西并且删除会话以后才能删除，用起来较为麻烦。于是决定创建实表存放数据，分别的语法如下

create table tem_table as SELECT * FROM 表名;
--创建一个新表放入查询的内容；



create global temporary table tem_table1 
on commit preserve rows 
as SELECT * FROM 表名;
--在当前会话中查询的结果会在临时表中存储数据，会话结束后数据会清空；

--删除临时表步骤：
SELECT sid, serial# 
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;
--查询当前会话的sid，serial#值；

alter system kill session 'sid, serial#';
--杀死当前会话；

drop table tem_table1;
--删除临时表；

此外，创表的语法如下

create table mangement (
pid varchar(10),
name varchar(10),
sex varchar(10),
years int,
remark varchar(10)
primary(pid)
)

create table per (
pid varchar(10),
dat timestamp,
workhour varchar(10),
overtime varchar(10),
primary key(pid,dat)
)

create table vacation(
pid varchar(10),
vacahour varchar(10),
resthour varchar(10),
primary key(pid)
)

如果需要修改某些数据也是使用alter语句，当然直接在Oracle中操作也可以，我也更喜欢这种操作，但是如果需要在Java中操作数据库，这些语句也是必不可少的。