問題
我想要查詢資料庫的Schema資訊時，以下列語法查詢，卻查不到資料。為什麼？
select * from sys.ALL_TAB_COLUMNS where owner = 'hr'
原因
原來，Oracle 是有分大小寫的。此與 SQL Server 的預設值不同。為了查詢，必須改成大寫才能找到資料。
select * from sys.ALL_TAB_COLUMNS where owner = 'HR'
不分大小寫
查詢時，如何不區分大小寫呢？例如在一個新聞上稿的資料庫中，查詢一篇文章內有 SQL Server 這個字串時，就必須不區分大小寫。
原來 Oracle 有兩個設定，NLS_COMP 與 NLS_SORT
NLS_COMP
這個設定是用來字串比對用的，適用於 where 子句中。
可設定的值
- BINARY : 所有比對是根據其字元的 binary 值來比對。所以一定會分大小寫。
- ANSI：遵守 NLS_SORT 的設定。這是10g R2 以前的設定方式。10g R2 以後仍在，只是為了向前相容性。
- LINGUISTIC：遵守 NLS_SORT 的設定。這是 10g R2 後的設定方式。
設定方式
任選一方式來設定：
- 環境變數：SET(export) NLS_COMP=<binary|linguistic>
- 在連線的 session中 內設定：ALTER SESSION SET NLS_COMP=<binary|linguistic>;
NLS_SORT
NLS_SORT 設定 order by 子句的排序方式。
可設定的值
- BINARY : 所有比對是根據其字元的 binary 值來排序。
- V$NLS_VALID_VALUES : All sorting is done in accordance to a named linguistic definition. This means that Oracle will sort in accordance to a particular locale (GERMAN, POLISH, FRENCH, etc.).These valid named definitions may be obtained by querying the V$NLS_VALID_VALUES where parameter = `SORT'. Just be aware that all the values in this view also have a hybrid definition by adding a suffix of _CI (case insensitivity
設定方式
- 環境變數：SET(export) NLS_SORT=<valid_value>
- 在連線的 session中 內設定：ALTER SESSION SET NLS_SORT=<valid_value>;
範例
不區分大小寫
alter session set NLS_COMP=LINGUISTIC; --遵照 NLS_SORT 的設定，即下面的不區分大小寫 alter session set NLS_SORT=BINARY_CI; -- 不區分大小寫 select * from sys.ALL_TAB_COLUMNS where owner = 'Hr'執行結果如下
參考
沒有留言:
張貼留言