Oracle SRF & using dual table

之前學過用SELECT字串加上 || column 來組合查詢結果

SELECT 'Hi ! ' || ename AS HI

FROM emp

也可以用CONCAT來達成同樣的效果

SELECT CONCAT('Hi ! ', ename) AS HI

FROM emp

HI
Hi !KING
Hi !BLAKE
Hi !CLARK
Hi !JONES
Hi !SCOTT
Hi !FORD
Hi !SMITH
Hi !ALLEN
Hi !WARD
Hi !MARTIN

假如我們輸入以下query

SELECT UPPER('hello')

FROM emp

WHERE deptno = 20

這樣就只會有符合條件的資料會變成大寫HELLO

UPPER('HELLO')
HELLO
HELLO
HELLO
HELLO
HELLO

DUAL表

簡單來說，dual表就是oracle與數據字典自動創建的一張表，這張表是一個單行單列的表，這個表只有1列：DUMMY，數據類型為VERCHAR2(1)，dual表中只有一個數據'X', Oracle有內部邏輯保證dual表中永遠只有一條數據。dual表主要是用來選擇系統變量或是求一個表達式的值。

比如：

--求系統當前時間

SELECT sysdate FROM daul

--求系統當前時間，並按設定的格式顯示

select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') from dual;

--當計算器使用

select 1+2 from dual

除了upper把字都轉大寫以外當然也要有把字轉小寫的功能

這就要透過lower來完成

query:

select LOWER('hello')

from DUAL 

LOWER('HELLO')
hello

練習query

SELECT

    CONCAT(CONCAT(LOWER(ename), UPPER(' is the name')), CONCAT(Lower(' and their job is : '), UPPER(job))) AS sentence

FROM

    emp

WHERE

    deptno = 20

SENTENCE
jones IS THE NAME and their job is : MANAGER
scott IS THE NAME and their job is : ANALYST
ford IS THE NAME and their job is : ANALYST
smith IS THE NAME and their job is : CLERK
adams IS THE NAME and their job is : CLERK