oracle学习笔记

1、decode函数：
SELECT checkup_type, DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。

函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。

当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。

区别于SQL的其它函数，DECODE函数还能识别和操作空值.
其具体的语法格式如下：DECODE(input_value,value,result[,value,result…][,default_result]);
其中：input_value 试图处理的数值。

DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果value 是一组成序偶的数值。

如果输入数值与之匹配成功，则相应的结果将被返回。

对应一个空的返回值，可以使用关键字NULL于之对应result 是一组成序偶的结果值default_result 未能与任何一序偶匹配成功时，函数返回的默认值下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。

2、nvl函数的用法：
如果你某个字段为空，但是你想让这个字段显示0nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如：1，2，3……
NULL指的是空值，或者非法值。

NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。

注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。

expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
3、oracle的查询必须是select ... from ...成对出现：
查询单行的时候可以用dual代替，dual表在系统中只有一行；
例如：获取系统时间；
select sysdate from dual
4、oracle查询结果多行用逗号拼接：
SELECT WM_CONCAT(GOODSTYPENAME) FROM TB_SYS_PRODUCT_FORBIDGOODSTYPE fib LEFT JOIN TB_SYS_GOODSTYPE ty ON fib.goodstypeid=ty.goodstypeid
WHERE fib.productid=t.productid
5、oracle递归获取所有子或者父节点：
pid为子级的父级id的字段名称
从Root往树末梢递归：获取子级
select * from temp
start with id=3 /*父级的id*/
connect by prior id = pid /*pid 子级的父级id的字段名称*/
从末梢往树ROOT递归：获取父级
select * from temp
start with id=3
connect by id = prior pid /*pid子级的父级id的字段名称*/
如果需要显示层次结构
select sys_connect_by_path(id,'/'),pid from temp
start with id=1
connect by prior id = pid
Rg:
SELECT * from tb_base_netpoint t
START WITH pointid=74852
CONNECT BY PRIOR pointid=t.parentnetpoint
6、oracle使用正则表达式：
替换掉大写字母：
SELECT REGEXP_REPLACE(printNo,'^[A-Z]*','') AS ISSUENO FROM TB_BASE_PRINTORDERLIST
Oracle 10g支持正则表达式的四个新函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。

特殊字符：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。

如果设置了RegExp 对象的Multiline 属性，则$ 也匹配'n' 或'r'。

'.' 匹配除换行符n之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'*' 匹配前面的子表达式零次或多次。

'+' 匹配前面的子表达式一次或多次。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出
现m次。

'|' 指明两项之间的一个选择。

例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

num 匹配num，其中num 是一个正整数。

对所获取的匹配的引用。

正则表达式的一个很有用的特点是可以保存子表达式以后使用，被称为Backreferencing. 允许复杂的替换能力
如调整一个模式到新的位置或者指示被代替的字符或者单词的位置. 被匹配的子表达式存储在临时缓冲区中，
缓冲区从左到右编号, 通过数字符号访问。

下面的例子列出了把名字aa bb cc 变成
cc, bb, aa.
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
REGEXP_REPLACE('ELLENHILDISMIT
cc, bb, aa
'' 转义符。

字符簇：
[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级
转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
| “或”操作
7、oracle替换字母：
替换所有字母：
REGEXP_REPLACE(issueno, '^[A-Z]*', '')
8、oracle新建存储过程：
create or replace procedure Test_Get_User(Iuserid in NUMBER,tt out varchar) is
begin
select username into tt from tb_sys_user where userid=Iuserid;
--dbms_output.put_line('tt='||tt); --打印结果
end Test_Get_User;
9、PLSQL添加新链接：
1、<客户端目录>
/network/admin，在admin目录中建立tnsnames.ora文件，
内容大致如下：demo1 = #oracle服务名
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521)) #oracle服务器地址与端口) (CONNECT_DATA = (SERVICE_NAME = demo1) ) )
2、客户端配置：配置和移植工具——Net Manager——服务命名；
10、substr的用法：
TSG.SCHEDULEORDERNO要截取的字符串，从3开始截取,字符串的下标从1开始;
SUBSTR(TSG.SCHEDULEORDERNO, 3)
11、遍历所有数据：
for lr in(
SELECT *
from TB_FIN_CREDITORDER
WHERE 1=1
AND to_date(lr.startdate)=to_date(SYSDATE)
AND state=8
)
loop
end loop;
跳出循环：
exit when result=500;
12、C#调用执行sql插入数据返回序列号：
string str_Sql = @"begin insert into testtab(name) values('test'); select TESTTABSEQ.Currval into :ID from dual; end;";
ORAC.OracleCommand cmd= new ORAC.OracleCommand(str_Sql,this.oracleConnection1);
ORAC.OracleParameter parm = new ORAC.OracleParameter("ID",ORAC.OracleType.Number);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
if(this.oracleConnection1.State == System.Data.ConnectionState.Closed)
{
this.oracleConnection1.Open();
}
cmd.ExecuteNonQuery();
this.textBox1.Text = cmd.Parameters[0].Value.ToString();
13、查询表的所有分区：
查询表的所有分区，表名必须大写
select * from user_tab_partitions t where t.table_name = 'ABC' ;
14、查找字符串位置：
INSTR（string,subString,position,ocurrence）查找字符串位置
解释：string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串
For example:
INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为14 '
15、单据编号生成sql：
单据编号加上前缀，加上每天日期，每天从1开始生成，
SELECT 'DD' || (NVL(SUBSTR(MAX(TSG.SCHEDULEORDERNO), 3),
TO_CHAR(SYSDATE, 'yyyymmdd') || '00000') + 1)
FROM TB_SALE_SCHEDULEORDER TSG
WHERE TSG.SCHEDULEORDERNO LIKE
'DD' || TO_CHAR(SYSDATE, 'yyyymmdd') || '%'
16、定义变量并输出：
declare v_exit NUMBER;
begin
v_exit:=0;
FOR i IN 0..100 LOOP
v_exit:=v_exit+i;
END LOOP;
dbms_output.put_line(v_exit);
end;
17、提取的列值为NULL：
问题：提取的列值为NULL
解决：有些列的值为NULL，用NVL（）转换；
18、调试存储过程：
存储过程右键--Test；
按F9；
19、运行存储过程：
--运行存储过程,有返回值
declare
v_a NUMBER;
v_b VARCHAR2(500);
begin
TEST_UPDATE_CUSTOMERCREDIT(v_a,v_b);
dbms_output.put_line(v_a);
dbms_output.put_line(v_b);
end;
20、整除：
整除，不要四舍五入
：
TRUNC(5/3)
----------
1
21、新建作业：
在DBMS_Jobs右键--新建：
运行：作业右键--Run
可以select * from user_jobs;查到job的id；
然后exec dbms_job.remove(21);
commit;就删掉了
select * from user_jobs;
select * from dba_users;
oracle中创建作业
/*创建作业任务*/
DECLARE
jobno NUMBER;
BEGIN
--每15分钟执行一次
DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',SYSDATE,'SYSDATE+15/1440');
--定时晚上11：00执行
DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',TRUNC(SYSDATE),'TRUNC(SYSDATE)+1380/1440');
--每天执行一次
DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',TRUNC(SYSDATE),'TRUNC(SYSDATE)+1');
--提交
COMMIT;
END;
--查看任务编号
SELECT * FROM USER_JOBS;
BEGIN
DBMS_JOB.run(任务编号);
END;
--删除JOB
BEGIN
DBMS_JOB.remove(任务编号);
END;
--终止任务
BEGIN
DBMS_JOB.broken(任务编号,FALSE);
COMMIT;
END;
---------------------------------------------------------------------------------------------------------------------------------------------------------------
一、创建一个存储过程
create or replace procedure MY_Procedure
is
--这里用到了游标
cursor cur is
select * from users where length(name) > 10 ;
begin
for user in cur
loop
insert into users_temp values(user.id,user.passwd);
end loop;
end;
二、创建作业
variable job_num number;
begin
dbms_job.submit(:job_num,'MY_Procedure;',sysdate,'sysdate+1');
commit;
end;
三、运行作业
begin
dbms_job.run(:job_num);
end;
四、查询作业
select priv_user,job,what from user_jobs;
五、删除作业
begin
dbms_job.remove(:job_num);
end;
六、时间
Sysdate＋1/1440 每1分钟执行一次
Sysdate＋5/1440 每5分钟执行一次
trunc(sysdate+1)+2/24 下一天的2点（凌晨）执行一次
'SYSDATE + 7' 最后一次执行的7天之后执行
'SYSDATE + 1/48' 每半个小时执行一次
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' 每个礼拜一的下午3点
'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3),''THURSDAY'')' 每个季度的第一个星期四
22、日期说明：
1、to_date(SYSDATE)得到系统今天的日期：2014/01/16
2、to_date(updatetime)得到的是日期：2014/01/16（updatetime是数据库的字段）
3、前3天的日期：to_date(SYSDATE-3)
4、获取年、月：
select to_char(paremStartSettlement,'yyyy') from dual ;
select to_char(paremStartSettlement,'mm') from dual ;
5、
/*每个月第一天*/;
select to_char(add_months(last_day(sysdate)+1,0),'yyyy/MM/dd') FirstDay FROM dual
;
/*每个月最后一天*/;
select to_char(add_months(last_day(sysdate),1),'yyyy/MM/dd') FirstDay FROM dual
6、只获取时间：
select to_char(SPR.PLANARRIVETIME,'hh24:mi:ss') AS PREARRIVETIME from dd
;
7、增加时分秒：
对当前日期增加一个小时：
SQL> select sysdate, sysdate+numtodsinterval(1,’hour’) from dual ;
2010-10-14 21:38:19 2010-10-14 22:38:19
对当前日期增加50分种
SQL> select sysdate, sysdate+numtodsinterval(50,’minute’) from dual ;
2010-10-14 21:39:12 2010-10-14 22:29:12
对当前日期增加45秒
SQL> select sysdate, sysdate+numtodsinterval(45,’second’) from dual ;
大于某天：
t.createtime>to_date('2013/12/03','yyyy-MM-dd')
查询某一天：
to_date(T.CREATETIME)=TO_DATE('2014/01/03', 'yyyy/MM/dd')
在某段时间内:
select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
返回系统日期,输出25-12月-09
select sysdate from dual;
转换的格式：
表示year 的：y 表示年的最后一位、
yy 表示年的最后2位、
yyy 表示年的最后3位、
yyyy 用4位数表示年
表示month的：mm 用2位数字表示月、
mon 用简写形式，比如11月或者nov 、
month 用全称，比如11月或者november
表示day的：dd 表示当月第几天、
ddd 表示当年第几天、
dy 当周第几天，简写，比如星期五或者fri 、
day 当周第几天，全称，比如星期五或者friday
表示hour的：hh 2位数表示小时12进制、
hh24 2位数表示小时24小时
表示minute的：mi 2位数表示分钟
表示second的：ss 2位数表示秒60进制
表示季度的：q 一位数表示季度（1-4）
另外还有ww 用来表示当年第几周w用来表示当月第几周。

24小时制下的时间范围：00：00：00-23：59：59
12小时制下的时间范围：1：00：00-12：59：59
数字格式: 9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符
补充：
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual;
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual;
当前时间减去7天的时间
select sysdate - interval '7' day from dual;
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual;
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual;
时间间隔乘以一个数字
select sysdate,sysdate - 8*interval '7' hour from dual;
含义解释：
Dual伪列
Dual 是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select语句块中。

不同系统可能返回日期的格式不一样。

返回当前连接的用户：select user from dual;
常用的时间格式在oracle中有yyyy-mm-dd hh24:mi:ss 而在Java中有些区别为yyyy-MM-dd HH:mm:ss 这点还是经常容易模糊的。

相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。

那在oracle中应该怎么来写sql语句呢，这个时候Oracle 的日期函数会给我们很多帮助。

常用日期型函数
1。

Sysdate 当前日期和时间
SQL> Select sysdate from dual;
SYSDATE
----------
21-6月-05
2。

Last_day 本月最后一天
SQL> Select last_day(sysdate) from dual;
LAST_DAY(S
----------
30-6月-05
3。

Add_months(d,n) 当前日期d后推n个月
用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)
SQL> Select add_months(sysdate,2) from dual;
ADD_MONTHS
----------
21-8月-05
4。

Months_between(f,s) 日期f和s间相差月数
SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD'))
----------------------------------------------------------
-4.6966741
5。

NEXT_DAY(d, day_of_week)
返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。

参数"day_of_week"必须为该星期中的某一天。

SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;
NEXT_DAY(T
----------
26-6月-05
6。

current_date()返回当前会话时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03
SQL> alter session set time_zone='-11:00' 2 /
会话已更改。

SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午-11:00
7。

current_timestamp()以timestamp with time zone数据类型返回当前会话时区中的当前日期
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-6月-05 10.13.08.220589 上午+08:00
8。

dbtimezone()返回时区
SQL> select dbtimezone from dual;
DBTIME
------
-08:00
9。

extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
6
SQL> select extract(year from add_months(sysdate,36)) " Years" from dual;
Years
----------
2008
10。

localtimestamp()返回会话中的日期和时间
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
21-6月-05 10.18.15.855652 上午
常用日期数据格式（该段为摘抄）
Y或YY或YYY 年的最后一位，两位或三位Select to_char(sysdate,’YYY’) from dual；002表示2002年
SYEAR或YEAR SYEAR使公元前的年份前加一负号Select to_char(sysdate,’SYEAR’) from dual；-1112表示公元前111 2年
Q 季度，1～3月为第一季度Select to_char(sysdate,’Q’) from dual；2表示第二季度①MM 月份数Select to_char(sysdate,’MM’) from dual；12表示12月
RM 月份的罗马表示Select to_char(sysdate,’RM’) from dual；IV表示4月
Month 用9个字符长度表示的月份名Select to_char(sysdate,’Month’) from dual；May后跟6个空格表示5月
WW 当年第几周Select to_char(sysdate,’WW’) from dual；24表示2002年6月13日为第24周
W 本月第几周Select to_char(sysdate,’W’) from dual；2002年10月1日为第1周
DDD 当年第几, 1月1日为001，2月1日为032 Select to_char(sysdate,’DDD’) from dual；363 2002年1 2月2 9日为第363天
DD 当月第几天Select to_char(sysdate,’DD’) from dual；04 10月4日为第4天
D 周内第几天Select to_char(sysdate,’D’) from dual；5 2002年3月14日为星期一
DY 周内第几天缩写Select to_char(sysdate,’DY’) from dual；SUN 2002年3月24日为星期天
HH或HH12 12进制小时数Select to_char(sysdate,’HH’) from dual；02 午夜2点过8分为02
HH24 24小时制Select to_char(sysdate,’HH24’) from dual；14 下午2点08分为14
MI 分钟数(0～59) Select to_char(sysdate,’MI’) from dual；17下午4点17分
SS 秒数(0～59) Select to_char(sysdate,’SS’) from dual；22 11点3分22秒
提示注意不要将MM格式用于分钟(分钟应该使用MI)。

MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。

现在给出一些实践后的用法：
1。

上月末天：
SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from
dual;
LASTDAY
----------
2005-05-31
2。

上月今天
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
PRETODAY
----------
2005-05-21
3.上月首天
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
FIRSTDAY
----------
2005-05-01
4.按照每周进行统计
SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
TO
--
25
5。

按照每月进行统计
SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
TO
--
06
6。

按照每季度进行统计
SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
T
-
2
7。

按照每年进行统计
SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
TO_C
----
2005
8.要找到某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。

9.oracle中时间运算
内容如下：
1、oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可
4、进行时间进制转换时注意加括号，否则会出问题
SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
会话已更改。

SQL> set serverout on
SQL> declare
2 DateValue date;
3 begin
4 select sysdate into DateValue from dual;
5 dbms_output.put_line('源时间:'||to_char(DateValue));
6 dbms_output.put_line('源时间减1天:'||to_char(DateValue-1));
7 dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24));
8 dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60)));
9 dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
10 end;
11 /
源时间:2003-12-29 11:53:41
源时间减1天:2003-12-28 11:53:41
源时间减1天1小时:2003-12-28 10:53:41
源时间减1天1小时1分:2003-12-28 10:52:41
源时间减1天1小时1分1秒:2003-12-28 10:52:40
PL/SQL 过程已成功完成。

在Oracle中实现时间相加处理
-- 名称：Add_Times
-- 功能：返回d1与NewTime相加以后的结果，实现时间的相加
-- 说明：对于NewTime中的日期不予考虑
-- 日期：2004-12-07
-- 版本：1.0
-- 作者：Kevin
create or replace function Add_Times(d1 in date,NewTime in date) return date
is
hh number;
mm number;
ss number;
hours number;
dResult date;
begin
-- 下面依次取出时、分、秒
select to_number(to_char(NewTime,'HH24')) into hh from dual;
select to_number(to_char(NewTime,'MI')) into mm from dual;
select to_number(to_char(NewTime,'SS')) into ss from dual;
-- 换算出NewTime中小时总和，在一天的百分几
hours := (hh + (mm / 60) + (ss / 3600))/ 24;
-- 得出时间相加后的结果
select d1 + hours into dResult from dual;
return(dResult);
end Add_Times;
-- 测试用例
-- select Add_Times(sysdate,to_date('2004-12-06 03:23:00','YYYY-MM-DD HH24:MI:SS')) from dual
在Oracle9i中计算时间差
计算时间差是Oracle DATA数据类型的一个常见问题。

Oracle支持日期计算，你可以创建诸如“日期1－日期2”这样的表达式来计算这两个日期之间的时间差。

一旦你发现了时间差异，你可以使用简单的技巧来以天、小时、分钟或者秒为单位来计算时间差。

为了得到数据差，你必须选择合适的时间度量单位，这样就可以进行数据格式隐藏。

使用完善复杂的转换函数来转换日期是一个诱惑，但是你会发现这不是最好的解决方法。

round(to_number(end-date-start_date))- 消逝的时间（以天为单位）
round(to_number(end-date-start_date)*24)- 消逝的时间（以小时为单位）
round(to_number(end-date-start_date)*1440)- 消逝的时间（以分钟为单位）
显示时间差的默认模式是什么？为了找到这个问题的答案，让我们进行一个简单的SQL *Plus查询。

SQL> select sysdate-(sysdate-3) from dual;
SYSDATE-(SYSDATE-3)
-------------------
3
这里，我们看到了Oracle使用天来作为消逝时间的单位，所以我们可以很容易的使用转换函数来把它转换成小时或者分钟。

然而，当分钟数不是一个整数时，我们就会遇到放置小数点的问题。

Select
(sysdate-(sysdate-3.111))*1440
from
dual;
(SYSDATE-(SYSDATE-3.111))*1440
------------------------------
4479.83333
当然，我们可以用ROUND函数（即取整函数）来解决这个问题，但是要记住我们必须首先把DATE数据类型转换成NUMBER数据类型。

Select
round(to_number(sysdate-(sysdate-3.111))*1440)
from
dual;
ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
----------------------------------------------
4480
我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。

在这个例子里，我们有一个离线（logoff）系统级触发机制来计算已经开始的会话时间并把它放
入一个Oracle STATSPACK USER_LOG扩展表格之中。

Update
perfstat.stats$user_log
set
elapsed_minutes =
round(to_number(logoff_time-logon_time)*1440)
where
user = user_id
and
elapsed_minutes is NULL;
查出任一年月所含的工作日
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
/*------------------------------------------------------------------------------------------
函数名称：Get_WorkingDays
中文名称：求某一年月中共有多少工作日
作者姓名: XINGPING
编写时间: 2004-05-22
输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405
返回值：整型值，包含的工作日数目。

算法描述：
1).列举出参数给出的年月中的每一天。

这里使用了一个表（ljrq是我的库中的一张表。

这个表可以是有权访问的、记录条数至少为31的任意一张表或视图）来构造出某年月的每一天。

2).用这些日期和一个已知星期几的日期相减(2001-12-30是星期天)，所得的差再对7求模。

如果所求年月在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.
3).过滤掉结果集中值为0和6的元素，然后求count,所得即为工作日数目。

-------------------------------------------------------------------------------------------------*/
Result INTEGER;
BEGIN
SELECT COUNT(*) INTO Result
FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday
FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq
FROM (SELECT substr(100+ROWNUM,2,2) dd
FROM ljrq z WHERE Rownum<=31
) t
WHERE to_date(ny||t.dd,'yyyymmdd')
BETWEEN to_date(ny,'yyyymm')
AND last_day(to_date(ny,'yyyymm'))
)q
) a
WHERE a.weekday NOT IN(0,6);
RETURN Result;
END Get_WorkingDays;
______________________________________
还有一个版本
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
/*-----------------------------------------------------------------------------------------
函数名称：Get_WorkingDays
中文名称：求某一年月中共有多少工作日
作者姓名: XINGPING
编写时间: 2004-05-23
输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405
返回值：整型值，包含的工作日数目。

算法描述：使用Last_day函数计算出参数所给年月共包含多少天，根据这个值来构造一个循环。

在这个循环中先求这个月的每一天与一个已知是星期天的日期(2001-12-30是星期天)的差，所得的差再对7求模。

如果所求日期在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模. 如过所得值不等于0和6（即不是星期六和星期天），则算一个工作日。

----------------------------------------------------------------------------------------*/
Result INTEGER := 0;
myts INTEGER; --所给年月的天数
scts INTEGER; --某天距2001-12-30所差的天数
rq DATE;
djt INTEGER := 1; --
BEGIN
myts := to_char(last_day(to_date(ny,'yyyymm')),'dd');
LOOP
rq := TO_date(ny||substr(100+djt,2),'yyyymmdd');
scts := rq - to_date('2001-12-30','yyyy-mm-dd');
IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN
Result := Result + 1;
END IF;
djt := djt + 1;
EXIT WHEN djt>myts;
END LOOP;
RETURN Result;
END Get_WorkingDays;
以上两个版本的比较
第一个版本一条SQL语句就可以得出结果，不需要编程就可以达到目的。

但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。

第二个版本需要编程，但不需要表或者视图。

这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。

这些节假日应该维护成一张表，然后通过查表来去除这些节假日。

23、ORACLE获取汉字首字母：
建立一个函数：
CREATE OR REPLACE FUNCTION F_PINYIN(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1..LENGTH(P_NAME) LOOP
V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
V_RETURN := V_RETURN || 'A';
ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
V_RETURN := V_RETURN || 'B';
ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
V_RETURN := V_RETURN || 'C';
ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
V_RETURN := V_RETURN || 'D';
ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
V_RETURN := V_RETURN || 'E';
ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
V_RETURN := V_RETURN || 'F';
ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
V_RETURN := V_RETURN || 'G';
ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
V_RETURN := V_RETURN || 'H';
ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
V_RETURN := V_RETURN || 'J';
ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
V_RETURN := V_RETURN || 'K';
ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
V_RETURN := V_RETURN || 'L';
ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
V_RETURN := V_RETURN || 'M';
ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
V_RETURN := V_RETURN || 'N';
ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
V_RETURN := V_RETURN || 'O';
ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN V_RETURN := V_RETURN || 'P';
ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN V_RETURN := V_RETURN || 'Q';
ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN V_RETURN := V_RETURN || 'R';
ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN V_RETURN := V_RETURN || 'S';
ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN V_RETURN := V_RETURN || 'T';
ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN V_RETURN := V_RETURN || 'W';
ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN V_RETURN := V_RETURN || 'X';
ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN V_RETURN := V_RETURN || 'Y';
ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN V_RETURN := V_RETURN || 'Z';
END IF;
END LOOP;
RETURN V_RETURN;
END;
例子：
SELECT F_PINYIN('函数已创建。

') FROM DUAL; 输出HSYCJ。