SQL語法 | PL/SQL | Function | TRIGGER | StoreProc | DBA

2015年7月14日 星期二

NVL , NVL2 ,COALESCE ,DECODE用法

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
COALESCE (eXPression_1, expression_2, ...,expression_n)
列表中第一個非空的表達式是函數的返回值,假如所有的表達式都是空值,最終將返回一個空值。 
NVL進階版函式 
select Coalesce('C','B','A') from dual ==> C 
select Coalesce(null,'B','A') from dual ==> B 
select Coalesce(null,null,'A') from dual ==> A

DECODE用法:
DECODE(value,if1,then1,if2,then2,if3,then3,……,else),表示如果value 等于if1?,DECODE函數返回then1,……

select decode( x , 1 , ‘x is 1 ‘, 2 , ‘x is 2 ‘, ‘others’) from dual
當x等於1時,則返回‘x is 1’。
當x等於2時,則返回‘x is 2’。
否則,返回others’。
在需要比較2個值的時候,我們可以配合SIGN()函數一起使用。
SELECT DECODE( SIGN(5 -6), 1 ‘Is Positive’, -1, ‘Is Nagative’, ‘Is Zero’)
同樣,也可以用CASE實現:
SELECT CASE SIGN(5 – 6)
WHEN 1 THEN ‘Is Positive’
WHEN-1 THEN ‘Is Nagative’
ELSE’Is Zero’ END
FROM DUAL
另外,大家還可以在Order by中使用Decode。

2015年6月11日 星期四

我查询出50条纪录,我只想要最前面的5条纪录(按某个字段排好序的)?


select * from (select * from tbname order by colname)
where rownum<6 u="">

oracle没有top的用法。
为什么只能rownum<
如果用到rownum > 就出错?

rownum是随着结果集生成的,一旦生成,就不会变化了;
同时,生成的结果是依次递加的,没有1就永远不会有2! 

rownum 是在 查询集合产生的过程中产生的伪列,并且如果where条件中存在 rownum 条件的话,则: 
1: 假如 判定条件是常量,则: 
只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的, 大于一个数也是没有结果的 
即 当出现一个 rownum 不满足条件的时候则 查询结束
如果要中间的纪录的话要怎样才行?
select * from
(select *,rownum num from
(select * from tbname order by colname) a
where rownum<10 b="" u="">

where num>4;

select * from(select * from table_name order by col_name) where rownum<6 u="">

select * from (select rownum rm,a.* from table_name a order by col_name where rownumm;

-- 我們想把最先進入公司的5個人找出來
SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY hiredate;
或
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 5;

-- 我們想把最先進入公司的6~10個人找出來!!!!!!!!!!!!!!
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 10 
MINUS
SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)
   WHERE ROWNUM <= 5;

2010年3月19日 星期五

ORA-02069: global_names parameter must be set to TRUE for this operation


透過 db link insert,update remote db ,有些語法要注意
insert statement 不要再 call function

2010年1月6日 星期三

Oracle 匯出/匯入 exp/imp

Import (回復):
開啟命令提示字元,
輸入 "imp system/用戶密碼@資料庫名稱" →
詢問從哪個檔案回復 →
詢問陣列緩衝區的大小 →
詢問「是否僅列出匯入檔案的內容」(若選擇 "no",會逐步詢問以下的相關資料,否則會跳過一些步驟) →
告知「物件已存在,建立物件的錯誤被略過不予以處理」(預設值為 "no",則系統碰到錯誤就會顯示錯誤訊息) →
詢問「是否匯入整個匯出檔」(若選 "yes" 則會將原先所有 Export 的資料全部匯入;若選 "no" 則會詢問是要匯入哪位 User 的 Schema、哪一個 Table)
→ 自動開始回復


imp system@DB_NAME  file=imp_File_name.dmp  fromuser  =sa touser=sa ignore=Y log=imp_log_name.log buffer=81920000


exp user/pwd file=test.dmp log=test.log owner=test

(13) 資料備份 - 使用 Export、Import:
● 有關 Oracle 資料備份的方式有很多種,還可分為:離線式、線上式、熱備份、冷備份,本文僅挑重點敘述。欲知詳細步驟及各種備份方式的特點請自行參考相關書籍。
● 此項 EXP 備份工具,較適合備份「部份 Table 或全部 Table」、「部份 Object 或所有 Object」,亦可備份「整個資料庫」。此工具將資料 Dump 出來是以 Binary 的方式,因此無法用記事本開啟檔案。此外,須具備足夠權限才能 Export 別人 Schema 的 Object,且有少數系統內建的 Schema 亦無法以此方式 Export。
Export (備份):
開啟命令提示字元,輸入 "exp system/用戶密碼@資料庫名稱" → 詢問陣列緩衝區的大小(值的大小僅會影響備份速度) → 匯出檔案名稱(如:backup1.dmp,預設最後會將檔案匯出在 C 槽底下) → 詢問匯出類型(分為:1.整個資料庫 2.使用者 3.表格) → 接下來選項大部份都可選 "yes"(例如是否要壓縮) → 自動開始備份

Import (回復):
開啟命令提示字元,輸入 "imp system/用戶密碼@資料庫名稱" → 詢問從哪個檔案回復 → 詢問陣列緩衝區的大小 → 詢問「是否僅列出匯入檔案的內容」(若選擇 "no",會逐步詢問以下的相關資料,否則會跳過一些步驟) → 告知「物件已存在,建立物件的錯誤被略過不予以處理」(預設值為 "no",則系統碰到錯誤就會顯示錯誤訊息) → 詢問「是否匯入整個匯出檔」(若選 "yes" 則會將原先所有 Export 的資料全部匯入;若選 "no" 則會詢問是要匯入哪位 User 的 Schema、哪一個 Table) → 自動開始回復

● 此項 Export、Import 的備份及回復功能,必須在資料庫啟動時才能操作。版工試過可在有使用者連線時備份和回復,且結束時亦顯示未發生任何錯誤。但建議實務上還是儘量選在使用者 離線時做備份,以避免影響運作績效或對用戶作業造成干擾,甚至造成資料的不一致或毀損。

2009年12月28日 星期一

關於Windows Oracle「ORA-12638 Credential retrieval failed」

1. 發生狀況:在資料庫(Server:Oracle 10g / Windows)進行抄寫時,出現「ORA-12638:Crendential retrieval failed(證明資料擷取失敗)」錯誤訊息。

2.說明:

Client端無法登入到遠端Server時,就會出現該狀況。

3.可能原因:
Server有開放OS認證模式,已知在Windows機器上,容易導致client端抄寫失敗
。

4.解決:
(1)登入到抄寫的Server(也就是提供資料的Oracle 10g機器)上的Windows作業系統。
(2)編輯「%Oracle_HOME%\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora」檔案,做如下方框內的修正:
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

#SQLNET.AUTHENTICATION_SERVICES = (NTS) --> 註解這一行,取消OS認證!
NAMES.DIRECTORY_PATH= (TNSNAMES)

(3)修改完成後存檔離開。離開後,可以再試試看DB抄寫,應該要能正常運作!