[Oracle] 連線與資料搜尋

連線至資料庫

一開始 Oracle 安裝完成後，除了系統管理員(SYS、SYSTEM、SYSMAN) 之外，其他帳號都是被鎖住的，因此若是要使用其他帳號登入的話，必須先將帳號解除鎖定，以下示範將帳號「hr」解除鎖定：
SQL> ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY password;

在 Oracle 中，schema 的名稱跟 user 名稱是相同的

除了這個跟 SQL Server 與 MySQL 不太一樣，Oracle 也只有一個 database，而分類的方式就是利用 schema 的方式進行分類，然後再用與 schema 名稱相同的使用者名稱進行資料庫的連線。

SID 即為 Oracle database 名稱

在連線資料庫的時候，必須指定 SID，而 SID 指的就是 Oracle database 的名稱；而由於每個 Oracle 只有一個 database，因此 SID 在 Oracle 安裝時就會設定好，並且要記好。

Select + Regular Expression

範例一：搜尋 job_id 的值中包含 man 或是 mgr 且大小寫不拘的員工
<br />select first_name as FIRST, last_name as LAST, job_id as Job <br />from employees <br />where regexp_like(job_id, '(_m[an|gr])', 'i');<br />

詳細使用方式可以參考官方網站說明。

範例二：搜尋 last_name 的值中包含兩個母音字母(a、e、i、o、u)且大小且不拘的員工
<br />select first_name as FIRST, last_name as LAST <br />from employees <br />where regexp_like(last_name, '([aeiou])\1', 'i')<br />

其中「\1」代表第一個 pattern，也就是「([aeiou])」。(每個 pattern 都會由小括號包起來)

詳細使用方式可以參考官方網站說明。

範例三：將搜尋結果的電話格式由「nnn.nnn.nnnn」改為「(nnn) nnn-nnnn」

<br />select first_name as FIRST, last_name as LAST, phone_number as OLD_NUMBER, <br /> regexp_replace(phone_number, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') as NEW_NUMBER <br />from employees<br />where department_id = 90<br />

詳細使用方式可以參考官方網站說明。

範例四：搜尋地址的街道號碼(格式可能為「1234」、「123-34」或是「12-34-56」)

<br />select street_address, <br /> regexp_substr(street_address, '[[:digit:]-]+', 1, 1) as STREET_NUMBERS <br />from locations;<br />

其中 regexp_substr() 中的後兩個參數為 1，分別代表「從第一個字元開始搜尋」以及「符合搜尋條件幾次」，詳細使用方式可以參考官方網站說明。

範例五：判斷地址中有幾個空白字元

<br />select street_address, <br /> regexp_count(street_address, ' ', 1) as NUMBER_OF_SPACE <br />from locations;<br />

詳細使用方式可以參考官方網站說明。

範例六：取得地址中諦一個空白所出現的位置索引

<br />select street_address, <br /> regexp_instr(street_address, '[ ]+', 1, 1) as POSITION <br />from locations;<br />

其中 regexp_instr() 中的後兩個參數為 1，分別代表「從第一個字元開始搜尋」以及「符合搜尋條件幾次」，詳細使用方式可以參考官方網站說明。

Built-in & Aggregate Function

範例一：搜尋員工在職時間

<br />select e.FIRST_NAME || ' ' || e.LAST_NAME as NAME, <br /> trunc(months_between(j.END_DATE, j.START_DATE)) MONTHS_WORKED <br />from employees e, job_history j <br />where e.EMPLOYEE_ID = j.EMPLOYEE_ID <br />order by MONTHS_WORKED<br />

範例二：列出員工年資

<br />select first_name || ' ' || last_name as NAME, <br /> (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from hire_date)) as YEARS_EMPLOYED <br />from employees;<br />

範例三：列出目前系統時間

<br />select EXTRACT(YEAR from SYSTIMESTAMP) || '/' || EXTRACT(MONTH from SYSTIMESTAMP) || '/' || <br /> EXTRACT(DAY from SYSTIMESTAMP) || ' ' || EXTRACT(HOUR from SYSTIMESTAMP) || ':' || <br /> EXTRACT(MINUTE from SYSTIMESTAMP) || ':' || ROUND(EXTRACT(SECOND from SYSTIMESTAMP), 0) <br /> as SYSTEM_TIME_AND_DATE <br />from dual;<br />

PS. 搜尋 DUAL table 會被罵 dummy 喔! Orz

範例四： 字串格式化 01 (原本格式範例：「01-7月 -98」)

<br />select first_name || ' ' || last_name as NAME, <br /> TO_CHAR(hire_date, 'FMMonth DD YYYY') DATE_STARTED <br />from employees;<br />

範例五：字串格式化 02 (原本格式範例：「01-7月 -98」)

<br />select first_name || ' ' || last_name as NAME, <br /> TO_CHAR(hire_date, 'DS') as SHORT_DATE, <br /> TO_CHAR(hire_date, 'DL') as LONG_DATE <br />from employees;<br />

範例六：字串格式化 03 (原本格式範例：「2600」)

<br />select first_name || ' ' || last_name as NAME, TO_CHAR(salary, '$99,999.99') as SALARY <br />from employees;<br /><br />

範例七：將字串轉為數字並進行運算

<br />select first_name || ' ' || last_name as NAME, <br /> TO_NUMBER('300') + salary as PROPOSED_SALARY <br />from employees <br />where SUBSTR(job_id, 4, 5) = 'CLERK';<br />

範例八：同時使用多個 aggregation function

<br />select job_id as JOB, <br /> COUNT(*) as "#", <br /> MIN(salary) as MINIMUM, <br /> ROUND(AVG(salary), 0) as AVERAGE, <br /> MEDIAN(salary) as MEDIAN, MAX(salary) as MAXIMUM, <br /> ROUND(STDDEV(salary)) AS STD_DEV <br />from employees <br />group by job_id <br />order by job_id;<br />

許多 function 都已經內建於 Oracle 中，例如：ROUND、TRUNC、LOWER、UPPER、INITCAP、LTRIM….等等，都可以直接拿來使用。

當然這些 function 的數量很多，很難一一介紹，如果想知道 Oracle 提供了哪些 function 以及要如何使用，可以參考官方網站的文件。

範例九：在 job_id 有 CLERK 字眼的群組中，取得 salary 為 3000 的排名以及多少百分比

<br />select RANK(3000) WITHIN GROUP(order by salary desc) as RANK, <br /> ROUND(100 * (PERCENT_RANK(3000) WITHIN GROUP(order by salary desc)), 0) as PERCENTILE <br />from employees <br />where job_id like '%CLERK';<br />

範例十：查詢員工與獎金相關資料(使用 NVL 與 NVL2)
<br />select first_name || ' ' || last_name as NAME, <br /> NVL(commission_pct * 100, 0) as COMM_RATE, <br /> NVL2(commission_pct, ROUND(salary * 12 + commission_pct * 300000, 2), salary * 12) as WITH_300K_SALES <br />from employees <br />where job_id like '%_M%' and department_id = 80;<br />

範例十一：使用 CASE 函數進行年資調整
<br />select first_name || ' ' || last_name as NAME, <br /> hire_date as DATE_STARTED, <br /> salary as CURRENT_PAY, <br /> CASE <br /> WHEN hire_date > TO_DATE('01-1月-90') THEN TRUNC(salary * 1.15, 0) <br /> WHEN hire_date > TO_DATE('01-1月-95') THEN TRUNC(salary * 1.10, 0) <br /> WHEN hire_date > TO_DATE('01-1月-00') THEN TRUNC(salary * 1.05, 0) <br /> ELSE salary <br /> END as PROPOSED_SALARY<br />from employees;<br />

CASE 的詳細使用方式可以參考官方網站說明。

範例十二：使用 DECODE 函數進行資料比對與條件判斷

<br />select first_name || ' ' || last_name as NAME, <br /> job_id as JOB, <br /> salary as CURRENT_PAY, <br /> DECODE(job_id, <br /> 'PU_CLERK', salary * 1.10, <br /> 'SH_CLERK', salary * 1.15, <br /> 'ST_CLERK', salary * 1.20, <br /> salary) as PROPOSED_SALARY <br />from employees;<br />

DECODE 的詳細使用方式可以參考官方網站說明。

若要進一步瞭解 Oracle 提供的 Aggregation Function 有哪些，以及詳細的使用方式，可以參考官方網站的文件說明；而 Expression 的部份，可以參考此處。