Oracle數(shù)據(jù)庫中對null值的排序及mull與空字符串的區(qū)別
order by排序之null值處理方法
在對業(yè)務數(shù)據(jù)排序時候，發(fā)現(xiàn)有些字段的記錄是null值，這時排序便出現(xiàn)了有違我們使用習慣的數(shù)據(jù)大小順序問題。在Oracle中規(guī)定，在Order by排序時缺省認為null是最大值，所以如果是ASC升序則被排在最后，而DESC降序則排在最前。所以，為何分析數(shù)據(jù)的直觀性方便性，我們需要對null的記錄值進行相應處理。
這是四種oracle排序中NULL值處理的方法：
1、使用nvl函數(shù)
語法：Nvl(expr1, expr2)
若EXPR1是NULL,則返回EXPR2,否則返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;
nvl函數(shù)可以在輸入?yún)?shù)為空時轉換為一特定值，如
nvl(person_name,“未知”)表示若person_name字段值為空時返回“未知”，如不為空則返回person_name的字段值。
通過這個函數(shù)可以定制null的排序位置。
2、使用decode函數(shù)
decode函數(shù)比nvl函數(shù)更強大，同樣它也可以將輸入?yún)?shù)為空時轉換為一特定值，如
decode(person_name,null,“未知”, person_name)表示當person_name為空時返回“未知”，如不為空則返回person_name的字段值。
通過此函數(shù)也可以定制null的排序位置。
3、使用nulls first 或者nulls last 語法，最簡單常用的方法。
Nulls first和nulls last是Oracle Order by支持的語法
（1）若Order by 中指定了表達式Nulls first則表示null值的記錄將排在最前(不管是asc 還是 desc)
（2）若Order by 中指定了表達式Nulls last則表示null值的記錄將排在最后 (不管是asc 還是 desc)
使用方法舉例如下：
將nulls始終放在最前：
select * from tbl order by field nulls first
將nulls始終放在最后：
select * from tbl order by field desc nulls last
4、使用case 語法
Case語法是Oracle 9i后開始支持的，是一個比較靈活的語法，同樣在排序中也可以應用
如：
select *
from students
order by (case person_name
when null then
'未知'
else
person_name
end)
表示在person_name字段值為空時返回'未知'，如果不為空則返回person_name
通過case語法同樣可以定制null的排序位置。
項目實例：
!defined('PATH_ADMIN') && exit('Forbidden');
class mod_gcdownload
{
public static function get_gcdownload_datalist($start = 0,$rowsperpage = PAGE_ROWS, $datestart = '',$dateend = '',$ver = '',$coopid = '',$subcoopid = '',$sortfield = '', $sorttype = '', $pid = 123456789, $plat = 'abcdefg'){
$sql = '';
$condition = empty($datestart) ? " WHERE 1=1 " : " WHERE t.statistics_date >= '$datestart' AND t.statistics_date <= '$dateend'";
if($ver)
{
$condition .= " AND t.edition='$ver'";
}
if($coopid)
{
$condition .= " AND t.suco_coopid=$coopid";
}
if($subcoopid)
{
$condition .= " AND t.suco_subcoopid=$subcoopid";
}
if($sortfield && $sorttype){
$condition .= " ORDER BY t.{$sortfield} {$sorttype} NULLS LAST";
}elseif($sortfield){
$condition .= " ORDER BY t.{$sortfield} desc NULLS LAST";
}else{
$condition .= " ORDER BY t.statistics_date desc NULLS LAST";
}
$finish = $start + $rowsperpage;
$joinsqlcollection = "(SELECT tc.coop_name, tsc.suco_name, tsc.suco_coopid,tsc.suco_subcoopid, s.edition, s.new_user, d.one_user, d.three_user, d.seven_user, s.statistics_date FROM (((pdt_stat_newuser_{$pid}_{$plat} s LEFT JOIN pdt_days_dl_remain_{$pid}_{$plat} d ON s.statistics_date=d.new_date AND s.subcoopid=d.subcoopid AND s.edition=d.edition )LEFT JOIN tbl_subcooperator@JTUSER1.NET@JTINFO tsc ON s.subcoopid=tsc.suco_subcoopid) LEFT JOIN tbl_cooperator@JTUSER1.NET@JTINFO tc ON tsc.suco_coopid=tc.coop_id))";
$sql = "SELECT * FROM (SELECT tb_A.*, ROWNUM AS rn FROM (SELECT t.* FROM $joinsqlcollection t {$condition} ) tb_A WHERE ROWNUM <= {$finish} ) tb_B WHERE tb_B.rn>{$start} ";
$countsql = "SELECT COUNT(*) AS totalrows, SUM(t.new_user) AS totalnewusr,SUM(t.one_user) AS totaloneusr,SUM(t.three_user) AS totalthreeusr,SUM(t.seven_user) AS totalsevenusr FROM $joinsqlcollection t {$condition} ";
$db = oralceinit(1);
$stidquery = $db->query($sql,false);
$output = array();
while($row = $db->FetchArray($stidquery, $skip = 0, $maxrows = -1))
{
$output['data'][] = array_change_key_case($row,CASE_LOWER);
}
$count_stidquery = $db->query($countsql,false);
$row = $db->FetchArray($count_stidquery, $skip = 0, $maxrows = -1);
$output['total']= array_change_key_case($row,CASE_LOWER);
//echo "<br />".($sql)."<br />";
return $output;
}
}
Null與空字符串' '的區(qū)別
含義解釋：
問：什么是NULL？
答：在我們不知道具體有什么數(shù)據(jù)的時候，也即未知，可以用NULL，我們稱它為空，ORACLE中，含有空值的表列長度為零。
ORACLE允許任何一種數(shù)據(jù)類型的字段為空，除了以下兩種情況：
1、主鍵字段（primary key），
2、定義時已經(jīng)加了NOT NULL限制條件的字段
說明：
1、等價于沒有任何值、是未知數(shù)。
2、NULL與0、空字符串、空格都不同。
3、對空值做加、減、乘、除等運算操作，結果仍為空。
4、NULL的處理使用NVL函數(shù)。
5、比較時使用關鍵字用“is null”和“is not null”。
6、空值不能被索引，所以查詢時有些符合條件的數(shù)據(jù)可能查不出來，count(*)中，用nvl(列名,0)處理后再查。
7、排序時比其他數(shù)據(jù)都大（索引默認是降序排列，小→大），所以NULL值總是排在最后。
使用方法：
SQL> select 1 from dual where null=null;
沒有查到記錄
SQL> select 1 from dual where null='';
沒有查到記錄
SQL> select 1 from dual where ''='';
沒有查到記錄
SQL> select 1 from dual where null is null; 1 --------- 1 SQL> select 1 from dual where nvl(null,0)=nvl(null,0); 1 --------- 1
對空值做加、減、乘、除等運算操作，結果仍為空。
SQL> select 1+null from dual; SQL> select 1-null from dual; SQL> select 1*null from dual; SQL> select 1/null from dual;
查詢到一個記錄.
注：這個記錄就是SQL語句中的那個null
設置某些列為空值
update table1 set 列1=NULL where 列1 is not null;
現(xiàn)有一個商品銷售表sale，表結構為：
month char(6) --月份
sell number(10,2) --月銷售金額
create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
insert into sale(month) values('200009');（注意：這條記錄的sell值為空）
commit;
共輸入12條記錄
SQL> select * from sale where sell like '%'; MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000
查詢到11記錄.
結果說明：
查詢結果說明此SQL語句查詢不出列值為NULL的字段
此時需對字段為NULL的情況另外處理。
SQL> select * from sale where sell like '%' or sell is null; SQL> select * from sale where nvl(sell,0) like '%'; MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009
查詢到12記錄.
Oracle的空值就是這么的用法，我們最好熟悉它的約定，以防查出的結果不正確。
但對于char 和varchar2類型的數(shù)據(jù)庫字段中的null和空字符串是否有區(qū)別呢？
作一個測試：
create table test (a char(5),b char(5));
SQL> insert into test(a,b) values('1','1');
SQL> insert into test(a,b) values('2','2');
SQL> insert into test(a,b) values('3','');--按照上面的解釋，b字段有值的
SQL> insert into test(a) values('4');
SQL> select * from test;
A B
---------- ----------
1 1
2 2
3
4
SQL> select * from test where b='';
----按照上面的解釋，應該有一條記錄，但實際上沒有記錄
未選定行
SQL> select * from test where b is null;
----按照上面的解釋，應該有一跳記錄，但實際上有兩條記錄。
A B ---------- ---------- 3 4 SQL>update table test set b='' where a='2'; SQL> select * from test where b='';
未選定行
SQL> select * from test where b is null; A B ---------- ---------- 2 3 4
測試結果說明，對char和varchar2字段來說，''就是null；但對于where 條件后的'' 不是null。
對于缺省值，也是一樣的！
相關文章
這篇文章主要介紹了Oracle rac案例講解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
這篇文章主要介紹了Oracle進程占用CPU100%的問題分析及解決方法,文中通過代碼示例和圖文結合的方式給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下2024-08-08
Oracle根據(jù)逗號拆分字段內(nèi)容轉成多行的函數(shù)說明
在做系統(tǒng)時經(jīng)常會遇到在一個字段中,用逗號或其他符號分隔存儲多個信息,下面這篇文章主要給大家介紹了關于Oracle根據(jù)逗號拆分字段內(nèi)容轉成多行的函數(shù)說明,需要的朋友可以參考下2023-04-04
在ORACLE移動數(shù)據(jù)庫文件...2007-03-03
Oracle數(shù)據(jù)庫的字段約束創(chuàng)建和維護示例
本篇文章主要介紹了Oracle數(shù)據(jù)庫的字段約束創(chuàng)建和維護示例，可以創(chuàng)建，添加，刪除等約束，感興趣的小伙伴們可以參考一下。2017-04-04
Oracle數(shù)據(jù)庫丟失表排查思路實戰(zhàn)記錄
相信大家無論是開發(fā)、測試還是運維過程中,都可能會因為誤操作、連錯數(shù)據(jù)庫、用錯用戶、語句條件有誤等原因,導致錯誤刪除、錯誤更新等問題,這篇文章主要給大家介紹了關于Oracle數(shù)據(jù)庫丟失表排查思路的相關資料,需要的朋友可以參考下2022-06-06
最近在工作中遇到了一個問題,錯誤是Oracle報錯ORA-28001:口令已失效,下面這篇文章主要給大家介紹了關于Oracle報錯:ORA-28001:口令已失效的解決辦法,需要的朋友可以參考下2023-04-04
oracle數(shù)據(jù)庫id自增及生成uuid問題
這篇文章主要介紹了oracle數(shù)據(jù)庫id自增及生成uuid問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
基于ora2pg遷移Oracle19C到postgreSQL14的全過程
ora2pg是一個開源工具,可將Oracle數(shù)據(jù)庫模式轉換為PostgreSQL格式,支持導出數(shù)據(jù)庫絕大多數(shù)對象類型,本文就給大家介紹了基于ora2pg遷移Oracle19C到postgreSQL14的全過程,文中有詳細的代碼示例,需要的朋友可以參考下2023-11-11
Oracle如何批量將表中字段名全轉換為大寫(利用簡單存儲過程)
這篇文章主要給大家介紹了關于Oracle如何批量將表中字段名全轉換為大寫的相關資料，主要利用的就是一個簡單的存儲過程，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11