PHP+Oracle项目笔记
来源：互联网 发布：知乎大神 编辑：程序博客网 时间：2024/05/22 15:45
业务需求决定数据操纵的复杂程度。一般情况下，我们将数据从数据库中取出到结果集中，再通过PHP等语言进行加工，生成我们需要的最终数据，然后输出显示出来。但是在Oracle数据库中，有时候出于业务需求的复杂，为了简化业务操作，不影响现有业务逻辑，这时候在数据库中直接进行针对各相关字段的数据计算显得尤为重要。当然，这种情况也仅适合于数据规模不是特别庞大的项目而言。如果是特大规模的海量数据操作，那就另当别论了。
本次项目中需要对游戏数据进行报表分析，经常我们的运营数据需要进行数据分析，有些比较稳定的数据是通过计算加工出来的，而不是数据库中的原始数据。在项目中，我们把“今日老用户联网次数/昨日联网用户数”的比值叫做“宏观留存率”，这时候就有必要运用到以下知识：
一、Oracle数据库关于取某条记录上一条和下一条记录
在oracle数据库中，我们利用统计分析函数lead 、lag 查询已有记录的相邻记录（上一条记录或一条记录）。这两个函数是偏移量函数，可以查出一条记录某字段的上一个值或者下一个值，配合over来使用。
oracle 统计分析函数 lead
语法结构：
lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)
参数说明：
value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
offset 偏移量，或者是相对偏移，表格离开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。 如果不提供这个参数，就是默认为1.
default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null
over 可以简单地翻译为“在……的基础之上”
query_partition_clause 分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。
Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc
lead是根据over的规则取下行记录，lag是根据over的规则取上行记录，两者可以从逻辑上互相取代。
STATISTICS_DATE NEW_USER REG_USER
2013-04-02 58 9
2013-04-02 1 0
2013-04-01 67 9
2013-03-31 60 11
2013-03-30 68 8
2013-03-29 66 9
2013-03-28 1 0
2013-03-28 74 14
2013-03-27 81 17
2013-03-27 1 0
2013-03-26 64 9
2013-03-25 1 0
2013-03-25 66 10
2013-03-24 1 0
2013-03-24 59 7
2013-03-23 50 11
2013-03-22 1 1
对于SQL语句：SELECT statistics_date,lead(new_user,1) over (order by statistics_date desc ) FROM pdt_stat_newuser_1133_i ORDER BY statistics_date desc
STATISTICS_DATE NEWFIELD
2013-04-02 1
2013-04-02 67
2013-04-01 60
2013-03-31 68
2013-03-30 66
2013-03-29 1
2013-03-28 74
2013-03-28 81
2013-03-27 1
2013-03-27 64
2013-03-26 1
2013-03-25 66
2013-03-25 1
2013-03-24 59
2013-03-24 50
2013-03-23 1
2013-03-22 55
二、Oracle中decode函数
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途 呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录 中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如:变量1=10，变量2=20，则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
DECODE(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n，缺省值)
DECODE(字段，比较1，值1，比较2，值2，.....，比较n，值n缺省值)
DECODE函数实例使用方法:
1、函数逻辑与格式
DECODE函数相当于一条件语句(IF).类似于其他编程语言中的if-then-else逻辑。它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.
其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);
其中：
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果
value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
result 是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时，函数返回的默认值
下面的例子说明了如何读取用户CHECKUP表中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;
2、函数实例
Oracle系统中就有许多数据字典是使用decode 思想设计的，比如记录会话信息的V$SESSION数据字典视图就是这样。我们从《Oracle8i/9i Reference》资料中了解到，当用户登录成功后在V$SESSION中就有该用户的相应记录，但用户所进行的命令操作在该视图中只记录命令的代码 （0—没有任何操作，2—Insert…），而不是具体的命令关键字。因此，我们需要了解当前各个用户的名字及他们所进行的操作时，要用下面命令才能得到 详细的结果：
select sid,serial#,username,
DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) cmmand
from v$session where username is not null;
3、DECODE实现表的转置
数据库中的表是由列和行构成的一个二维表。一般列在任何数据库中都是有限的数量，而行的变化较大，如果表很大，行的数量可能大上千万行。同一列的不同行可能有不同的值，而且不是预先定义的。
例:住房公积金报表置换实例：
1.各个单位在本地经办行进行开户，开户就是将单位的基本信息和职工信息的进行登记；
2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金，系统记录有每个职工的交缴明细并在每条记录上记录有经办行的代码；
3.每月、季、半年及年终都要求将经办行 变为“列”给出个月的明细报表：
经办行：城西区 城东区
月份：
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
。 。 。 。 。 。
原来的数据顺序是：
城西区2001.01 xxxxx1.xx
城东区2001.01 xxxxx2.xx
城西区2001.02 xxxxx3.xx
城东区2001.02 xxxxx4.xx
住房公积金系统记录职工的每月交缴名细的pay_lst表结构是：
bank_code varchar2(6)NOT NULL, -- 经办行代码
acc_no varchar2(15) not null, -- 单位代码(单位帐号)
emp_acc_no varchar2(20) not null, -- 职工帐号
tran_date date not null, -- 交缴日期
tran_val Number(7,2) not null, -- 交缴额
sys_date date default sysdate, --系统日期
oper_id varchar2(10) --操作员代码
这样的表结构，一般按照将经办行作为行(row)进行统计是很容易的，但是如果希望将经办行变为列(column)这样的格式来输出就有困难。如果用DECODE函数来处理则变得很简单：
我们创建一个视图来对目前的pay_lst表进行查询。将经办行代码变为一些具体的经办行名称即可：
CREATE OR REPLACE VIEW bank_date_lst AS
Select to_char(tran_date,’yyyy.mm’),
SUM( DECODE ( bank_code,’001’, tran_val,0 )) 城西区，
SUM( DECODE ( bank_code,’002’, tran_val,0 )) 城南区，
SUM( DECODE ( bank_code,’003’, tran_val,0 )) 城东区
FROM pay_lst
GROUP BY to_char(tran_date,’yyyy.mm’);
建立视图后，可直接对该视图进行查询就可按照列显示出结果。
三、和其他数据库系统类似，Oracle字符串连接使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。
比如执行下面的SQL语句：
SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee
WHERE FName IS NOT NULL
除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：
SELECT CONCAT('工号:',FNumber) FROM T_Employee
如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串，比如执行下面的SQL语句：
SELECT CONCAT('年龄:',FAge) FROM T_Employee
与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的：
SELECT CONCAT('工号为',FNumber,'的员工姓名为',FName) FROM T_Employee
WHERE FName IS NOT NULL
运行以后Oracle会报出下面的错误信息：
参数个数无效
如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用，上面的SQL可以如下改写：
SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM
T_Employee
WHERE FName IS NOT NULL
四、oracle中关于null排序的问题
在处理一般的数据记录中，对于数字类型的字段，在oracle的排序中，默认把null值做为大于任何数字的类型，当然对于varchar2类型的字段，默认也是该处理方式，但是客户要求排序的过程中，需要把null的字段默认排在前边（从小到大）。一般的
order by field asc/desc是无法解决的，这时可以使用可以利用以下方式：
1、oracle中可以对order by中对比较字段做设置的方式来实现：
如： ……order by NVL( filed,'-1')。
2、使用nulls first 或者nulls last 语法
若Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
若Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
相关oracle排序问题，可以参考文章《Oracle数据库order by排序之null值处理方法》http://blog.csdn.net/freshlover/article/details/8706739
五、Oracle数据计算保留两位小数
Oracle截取小数位，有两种方法，一个是TO_CHAR，一个是TRUNC函数，第三种方法ROUND()函数。三种方法在用法上有细微的差别，对比如下：
1、Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
2、TO_CHAR处理小数位
TO_CHAR(字段名,'999,999,999.99')
使用TO_CHAR的方式，有两个弊端，也是需要注意的地方：
（1）整数部分的9要写的足够多，否则会错误显示，如下：
错误：SELECT TO_CHAR(199999999.1256,'9,999.99') FROM DUAL --显示结果：#########
正确：SELECT TO_CHAR(199999999.1256,'999,999,999.99') FROM DUAL --显示结果：199,999,999.13
（2）对0这种情况要单独处理，当为0时，会显示为.00，而非0.00。如下：
错误：SELECT TO_CHAR(0,'9,99.99') FROM DUAL --显示结果：.00
正确：SELECT DECODE(0,0,'0.00',TO_CHAR(199999999.1256,'999,999,999.99')) FROM DUAL --显示结果：0.00
3、ROUND()函数
ROUND函数：四舍五入，ROUND( number, [ decimal_places ] )
描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。
SELECT ROUND( number, [ decimal_places ] ) FROM DUAL
参数:
number : 欲处理之数值
decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )
示例 :
select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
select round(123.456, 3) from dual; 回传 123.456
select round(-123.456, 2) from dual; 回传 -123.46
当然，保留两位小数的处理，在格式要求的不是很严格的条件下，也可以用round函数。
在保留小数这个问题上round函数有以下弊端：
①当为整数时，无论如何写，最终返回的都是整数，如下：
SELECT ROUND(199.000,2) FROM DUAL --199
②返回值中不会自带千分符，如下：
SELECT ROUND(199999.2356,2) FROM DUAL --199999.24
我的项目中，最终选择了使用DECODE与lead…over、TO_CHAR()函数。数据库语句如下：
SELECT thedate, DECODE(lead(conn_user,1) over (order by thedate desc),NULL,'0.00',TO_CHAR(conn_user_old/lead(conn_user,1) over (order by thedate desc)*100,'999,999,999.99')) AS percentage from
( SELECT thedate,subcoopid,SUM(new_user) AS new_user,SUM(reg_user) AS reg_user,conn_user,conn_time,conn_user_old,conn_times_old,dl_time,dl_user FROM ( SELECT medium.thedate AS thedate, n.edition AS edition, n.subcoopid AS subcoopid, NVL(SUM(n.new_user), 0) AS new_user, NVL(SUM(n.reg_user), 0) AS reg_user, NVL(SUM(c.conn_user), 0) AS conn_user, NVL(SUM(c.conn_time), 0) AS conn_time, NVL(SUM(c.conn_user_old), 0) AS conn_user_old, NVL(SUM(c.conn_times_old), 0) AS conn_times_old, NVL(SUM(r.dl_time), 0) AS dl_time, NVL(SUM(r.dl_user), 0) AS dl_user FROM (SELECT to_char(thedate, 'yyyy-mm-dd') AS thedate FROM tbl_sysdate t WHERE thedate >= to_date('2013-03-1', 'yyyy-mm-dd') AND thedate < to_date('2013-04-03', 'yyyy-mm-dd')) medium LEFT JOIN (SELECT statistics_date AS thedate, NVL(SUM(new_user), 0) AS new_user, NVL(SUM(reg_user), 0) AS reg_user, edition, subcoopid FROM pdt_stat_newuser_1133_i GROUP BY statistics_date,edition,subcoopid) n ON medium.thedate=n.thedate LEFT JOIN ( SELECT statistics_date AS thedate, NVL(SUM(conn_user), 0) AS conn_user, NVL(SUM(conn_time), 0) AS conn_time, NVL(SUM(conn_user_old), 0) AS conn_user_old, NVL(SUM(CONN_TIME_OLD), 0) AS conn_times_old FROM pdt_stat_conn_1133_i GROUP BY statistics_date) c ON medium.thedate=c.thedate LEFT JOIN (SELECT statistics_date AS thedate, NVL(SUM(dl_time), 0) AS dl_time, NVL(SUM(dl_user), 0) AS dl_user FROM pdt_stat_resdl_1133_i GROUP BY statistics_date) r ON medium.thedate=r.thedate WHERE 1=1 GROUP BY medium.thedate,n.edition,n.subcoopid )
GROUP BY thedate,subcoopid,conn_user,conn_time,conn_user_old,conn_times_old,dl_time,dl_user /*ORDER BY thedate desc */ ORDER BY thedate desc
)
本次项目中需要对游戏数据进行报表分析，经常我们的运营数据需要进行数据分析，有些比较稳定的数据是通过计算加工出来的，而不是数据库中的原始数据。在项目中，我们把“今日老用户联网次数/昨日联网用户数”的比值叫做“宏观留存率”，这时候就有必要运用到以下知识：
一、Oracle数据库关于取某条记录上一条和下一条记录
在oracle数据库中，我们利用统计分析函数lead 、lag 查询已有记录的相邻记录（上一条记录或一条记录）。这两个函数是偏移量函数，可以查出一条记录某字段的上一个值或者下一个值，配合over来使用。
oracle 统计分析函数 lead
语法结构：
lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)
参数说明：
value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
offset 偏移量，或者是相对偏移，表格离开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。 如果不提供这个参数，就是默认为1.
default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null
over 可以简单地翻译为“在……的基础之上”
query_partition_clause 分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。
Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc
lead是根据over的规则取下行记录，lag是根据over的规则取上行记录，两者可以从逻辑上互相取代。
STATISTICS_DATE NEW_USER REG_USER
2013-04-02 58 9
2013-04-02 1 0
2013-04-01 67 9
2013-03-31 60 11
2013-03-30 68 8
2013-03-29 66 9
2013-03-28 1 0
2013-03-28 74 14
2013-03-27 81 17
2013-03-27 1 0
2013-03-26 64 9
2013-03-25 1 0
2013-03-25 66 10
2013-03-24 1 0
2013-03-24 59 7
2013-03-23 50 11
2013-03-22 1 1
对于SQL语句：SELECT statistics_date,lead(new_user,1) over (order by statistics_date desc ) FROM pdt_stat_newuser_1133_i ORDER BY statistics_date desc
STATISTICS_DATE NEWFIELD
2013-04-02 1
2013-04-02 67
2013-04-01 60
2013-03-31 68
2013-03-30 66
2013-03-29 1
2013-03-28 74
2013-03-28 81
2013-03-27 1
2013-03-27 64
2013-03-26 1
2013-03-25 66
2013-03-25 1
2013-03-24 59
2013-03-24 50
2013-03-23 1
2013-03-22 55
二、Oracle中decode函数
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途 呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录 中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如:变量1=10，变量2=20，则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
DECODE(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n，缺省值)
DECODE(字段，比较1，值1，比较2，值2，.....，比较n，值n缺省值)
DECODE函数实例使用方法:
1、函数逻辑与格式
DECODE函数相当于一条件语句(IF).类似于其他编程语言中的if-then-else逻辑。它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.
其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);
其中：
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果
value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
result 是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时，函数返回的默认值
下面的例子说明了如何读取用户CHECKUP表中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;
2、函数实例
Oracle系统中就有许多数据字典是使用decode 思想设计的，比如记录会话信息的V$SESSION数据字典视图就是这样。我们从《Oracle8i/9i Reference》资料中了解到，当用户登录成功后在V$SESSION中就有该用户的相应记录，但用户所进行的命令操作在该视图中只记录命令的代码 （0—没有任何操作，2—Insert…），而不是具体的命令关键字。因此，我们需要了解当前各个用户的名字及他们所进行的操作时，要用下面命令才能得到 详细的结果：
select sid,serial#,username,
DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) cmmand
from v$session where username is not null;
3、DECODE实现表的转置
数据库中的表是由列和行构成的一个二维表。一般列在任何数据库中都是有限的数量，而行的变化较大，如果表很大，行的数量可能大上千万行。同一列的不同行可能有不同的值，而且不是预先定义的。
例:住房公积金报表置换实例：
1.各个单位在本地经办行进行开户，开户就是将单位的基本信息和职工信息的进行登记；
2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金，系统记录有每个职工的交缴明细并在每条记录上记录有经办行的代码；
3.每月、季、半年及年终都要求将经办行 变为“列”给出个月的明细报表：
经办行：城西区 城东区
月份：
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
。 。 。 。 。 。
原来的数据顺序是：
城西区2001.01 xxxxx1.xx
城东区2001.01 xxxxx2.xx
城西区2001.02 xxxxx3.xx
城东区2001.02 xxxxx4.xx
住房公积金系统记录职工的每月交缴名细的pay_lst表结构是：
bank_code varchar2(6)NOT NULL, -- 经办行代码
acc_no varchar2(15) not null, -- 单位代码(单位帐号)
emp_acc_no varchar2(20) not null, -- 职工帐号
tran_date date not null, -- 交缴日期
tran_val Number(7,2) not null, -- 交缴额
sys_date date default sysdate, --系统日期
oper_id varchar2(10) --操作员代码
这样的表结构，一般按照将经办行作为行(row)进行统计是很容易的，但是如果希望将经办行变为列(column)这样的格式来输出就有困难。如果用DECODE函数来处理则变得很简单：
我们创建一个视图来对目前的pay_lst表进行查询。将经办行代码变为一些具体的经办行名称即可：
CREATE OR REPLACE VIEW bank_date_lst AS
Select to_char(tran_date,’yyyy.mm’),
SUM( DECODE ( bank_code,’001’, tran_val,0 )) 城西区，
SUM( DECODE ( bank_code,’002’, tran_val,0 )) 城南区，
SUM( DECODE ( bank_code,’003’, tran_val,0 )) 城东区
FROM pay_lst
GROUP BY to_char(tran_date,’yyyy.mm’);
建立视图后，可直接对该视图进行查询就可按照列显示出结果。
三、和其他数据库系统类似，Oracle字符串连接使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。
比如执行下面的SQL语句：
SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee
WHERE FName IS NOT NULL
除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：
SELECT CONCAT('工号:',FNumber) FROM T_Employee
如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串，比如执行下面的SQL语句：
SELECT CONCAT('年龄:',FAge) FROM T_Employee
与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的：
SELECT CONCAT('工号为',FNumber,'的员工姓名为',FName) FROM T_Employee
WHERE FName IS NOT NULL
运行以后Oracle会报出下面的错误信息：
参数个数无效
如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用，上面的SQL可以如下改写：
SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM
T_Employee
WHERE FName IS NOT NULL
四、oracle中关于null排序的问题
在处理一般的数据记录中，对于数字类型的字段，在oracle的排序中，默认把null值做为大于任何数字的类型，当然对于varchar2类型的字段，默认也是该处理方式，但是客户要求排序的过程中，需要把null的字段默认排在前边（从小到大）。一般的
order by field asc/desc是无法解决的，这时可以使用可以利用以下方式：
1、oracle中可以对order by中对比较字段做设置的方式来实现：
如： ……order by NVL( filed,'-1')。
2、使用nulls first 或者nulls last 语法
若Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
若Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
相关oracle排序问题，可以参考文章《Oracle数据库order by排序之null值处理方法》http://blog.csdn.net/freshlover/article/details/8706739
五、Oracle数据计算保留两位小数
Oracle截取小数位，有两种方法，一个是TO_CHAR，一个是TRUNC函数，第三种方法ROUND()函数。三种方法在用法上有细微的差别，对比如下：
1、Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
2、TO_CHAR处理小数位
TO_CHAR(字段名,'999,999,999.99')
使用TO_CHAR的方式，有两个弊端，也是需要注意的地方：
（1）整数部分的9要写的足够多，否则会错误显示，如下：
错误：SELECT TO_CHAR(199999999.1256,'9,999.99') FROM DUAL --显示结果：#########
正确：SELECT TO_CHAR(199999999.1256,'999,999,999.99') FROM DUAL --显示结果：199,999,999.13
（2）对0这种情况要单独处理，当为0时，会显示为.00，而非0.00。如下：
错误：SELECT TO_CHAR(0,'9,99.99') FROM DUAL --显示结果：.00
正确：SELECT DECODE(0,0,'0.00',TO_CHAR(199999999.1256,'999,999,999.99')) FROM DUAL --显示结果：0.00
3、ROUND()函数
ROUND函数：四舍五入，ROUND( number, [ decimal_places ] )
描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。
SELECT ROUND( number, [ decimal_places ] ) FROM DUAL
参数:
number : 欲处理之数值
decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )
示例 :
select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
select round(123.456, 3) from dual; 回传 123.456
select round(-123.456, 2) from dual; 回传 -123.46
当然，保留两位小数的处理，在格式要求的不是很严格的条件下，也可以用round函数。
在保留小数这个问题上round函数有以下弊端：
①当为整数时，无论如何写，最终返回的都是整数，如下：
SELECT ROUND(199.000,2) FROM DUAL --199
②返回值中不会自带千分符，如下：
SELECT ROUND(199999.2356,2) FROM DUAL --199999.24
我的项目中，最终选择了使用DECODE与lead…over、TO_CHAR()函数。数据库语句如下：
SELECT thedate, DECODE(lead(conn_user,1) over (order by thedate desc),NULL,'0.00',TO_CHAR(conn_user_old/lead(conn_user,1) over (order by thedate desc)*100,'999,999,999.99')) AS percentage from
( SELECT thedate,subcoopid,SUM(new_user) AS new_user,SUM(reg_user) AS reg_user,conn_user,conn_time,conn_user_old,conn_times_old,dl_time,dl_user FROM ( SELECT medium.thedate AS thedate, n.edition AS edition, n.subcoopid AS subcoopid, NVL(SUM(n.new_user), 0) AS new_user, NVL(SUM(n.reg_user), 0) AS reg_user, NVL(SUM(c.conn_user), 0) AS conn_user, NVL(SUM(c.conn_time), 0) AS conn_time, NVL(SUM(c.conn_user_old), 0) AS conn_user_old, NVL(SUM(c.conn_times_old), 0) AS conn_times_old, NVL(SUM(r.dl_time), 0) AS dl_time, NVL(SUM(r.dl_user), 0) AS dl_user FROM (SELECT to_char(thedate, 'yyyy-mm-dd') AS thedate FROM tbl_sysdate t WHERE thedate >= to_date('2013-03-1', 'yyyy-mm-dd') AND thedate < to_date('2013-04-03', 'yyyy-mm-dd')) medium LEFT JOIN (SELECT statistics_date AS thedate, NVL(SUM(new_user), 0) AS new_user, NVL(SUM(reg_user), 0) AS reg_user, edition, subcoopid FROM pdt_stat_newuser_1133_i GROUP BY statistics_date,edition,subcoopid) n ON medium.thedate=n.thedate LEFT JOIN ( SELECT statistics_date AS thedate, NVL(SUM(conn_user), 0) AS conn_user, NVL(SUM(conn_time), 0) AS conn_time, NVL(SUM(conn_user_old), 0) AS conn_user_old, NVL(SUM(CONN_TIME_OLD), 0) AS conn_times_old FROM pdt_stat_conn_1133_i GROUP BY statistics_date) c ON medium.thedate=c.thedate LEFT JOIN (SELECT statistics_date AS thedate, NVL(SUM(dl_time), 0) AS dl_time, NVL(SUM(dl_user), 0) AS dl_user FROM pdt_stat_resdl_1133_i GROUP BY statistics_date) r ON medium.thedate=r.thedate WHERE 1=1 GROUP BY medium.thedate,n.edition,n.subcoopid )
GROUP BY thedate,subcoopid,conn_user,conn_time,conn_user_old,conn_times_old,dl_time,dl_user /*ORDER BY thedate desc */ ORDER BY thedate desc
)
- PHP+Oracle项目笔记
- php项目笔记（二）php部分
- php项目笔记（一）mysql准备
- [Openwrt 项目开发笔记]：PHP+Nginx安装（七）
- java项目，从Oracle迁移到达梦数据库笔记
- java项目，从Oracle迁移到达梦数据库笔记
- PHP + Oracle
- PHP项目
- PHP-笔记
- php笔记
- php笔记
- PHP笔记
- PHP笔记
- php笔记
- PHP 笔记
- php笔记
- PHP笔记
- PHP笔记
- 黑马程序员-反射
- 黑马程序员-注解
- 三角形类
- Web开发者职业生涯的11个阶段
- (转)【Android游戏开发之十】（优化处理）详细剖析Android Traceview 效率检视工具！分析程序运行速度！并讲解两种创建SDcard方式！
- PHP+Oracle项目笔记
- 发送消息，有字数限制的js监控代码
- c#多线程彻底学习
- 解决win7打开网络看不到局域网的其他电脑/计算机
- [转载]android读取sd卡文件的工具类
- uva10131 Is Bigger Smarter？
- JAVA中字符串转换成整型问题
- java解析xml文件
- Oracle 字符集的查看和修改
