Oracle相关记录

Oracle使用的一些记录

时间处理

查询当前时间

SELECT SYSDATE FROM DUAL;

查询1天前和1天后的当前时间：

SELECT SYSDATE-1,SYSDATE+1 FROM DUAL;

查询上月的第一天：

SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD')  FROM DUAL;

查询两个时间相差的月数：

    SELECT MONTHS_BETWEEN(SYSDATE+30,SYSDATE) FROM DUAL;--（如果当月为30天，返回1，为31天返回0.967741935483871）

查询一年中所有的星期五：

SELECT TO_CHAR(t.d, 'yyyy-mm-dd'), to_char(t.d, 'day') from (select trunc(sysdate, 'YY') + rownum - 1 as d from dba_objects where rownum <= TO_CHAR(ADD_MONTHS(trunc(sysdate, 'YY'), 12) - 1, 'DDD')) t where to_char(t.d, 'd') = 6;
SELECT TO_CHAR(t.d, 'yyyy-mm-dd'), to_char(t.d, 'day') from (select trunc(sysdate, 'YY') + rownum - 1 as d from dba_objects where rownum <= TO_CHAR(LAST_DAY(ADD_MONTHS(trunc(sysdate, 'YY'),11)),'DDD')) t where to_char(t.d, 'd') = 6;

查询一年四个季度各有多少天：

select (case when jijie = 1 then  '第一季' when jijie = 2 then '第二季' when jijie = 3 then  '第三季'  else '第四季' end) 季度 ,tianshu 天数 from (select to_char(d, 'q') jijie, sum(a) tianshu from (select 1 a, trunc(sysdate, 'YY') + rownum - 1 as d from dba_objects where rownum <= TO_CHAR(LAST_DAY(ADD_MONTHS(trunc(sysdate, 'YY'), 11)), 'DDD'))  group by to_char(d, 'q'))

查询当年有多少个工作日(周一到周五)：

select count(d)  from(select trunc(sysdate, 'YY') + rownum - 1 as d  from dba_objects where rownum <= TO_CHAR(ADD_MONTHS(trunc(sysdate, 'YY'), 12) - 1, 'DDD')) where to_char(d,'d')<>1 and to_char(d,'d')<>7

执行update时卡死问题解决

进行项目开发时，为了测试项目中SQL执行效果，就用PL/SQL Developer执行了一下，没有commit就继续敲代码了，之后重新使用PL/SQL Developer时，可能是网络原因，PL/SQL Developer断开了与数据库连接并且卡死，便强制结束了。代码敲完后进行测试，项目执行后没效果，并且无响应，debug后发现jdbc执行SQL时没反应了，问了一下谷老师才找到原因。由于在PL/SQL Developer执行update时没有commit，oracle将该条记录锁住了。

可以通过以下办法解决：

先查询锁定记录

SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; 

然后删除锁定记录

ALTER system KILL session 'SID,serial#';

树查询

数据库表结构：

CREATE TABLE "MODEL"   
(  
"ID" NUMBER(5,0) NOT NULL ENABLE,   
"NAME" VARCHAR2(40) NOT NULL ENABLE,   
"PID" NUMBER(5,0) NOT NULL ENABLE  
) ; 

插入数据：

INSERT INTO "MODEL" VALUES ('1', '中国', '0');  
INSERT INTO "MODEL" VALUES ('11', '北京', '1');  
INSERT INTO "MODEL" VALUES ('12', '河北', '1');  
INSERT INTO "MODEL" VALUES ('13', '山西', '1');  
INSERT INTO "MODEL" VALUES ('14', '山东', '1');  
INSERT INTO "MODEL" VALUES ('131', '太原', '13');  
INSERT INTO "MODEL" VALUES ('132', '长治', '13');  
INSERT INTO "MODEL" VALUES ('133', '大同', '13');  
INSERT INTO "MODEL" VALUES ('135', '临汾', '13');  
INSERT INTO "MODEL" VALUES ('121', '石家庄', '12');  
INSERT INTO "MODEL" VALUES ('122', '保定', '12');  
INSERT INTO "MODEL" VALUES ('123', '邯郸', '12');  
INSERT INTO "MODEL" VALUES ('124', '唐山', '12');  
INSERT INTO "MODEL" VALUES ('111', '海淀', '11');  
INSERT INTO "MODEL" VALUES ('112', '朝阳', '11');  
INSERT INTO "MODEL" VALUES ('113', '昌平', '11');  
INSERT INTO "MODEL" VALUES ('1111', '中关村', '111');

下级查询上级

select c.id, c.name, level, sys_connect_by_path(c.name, '-')  
  from (select * from model) c  
 start with c.name = '中关村'  
connect by c.id = prior c.pid 

上级查询下级

select c.id, c.name, level, sys_connect_by_path(c.name, '-')  
  from (select * from model) c  
 start with c.name = '中国'  
connect by prior c.id =  c.pid  