根據函式每次處理的物件是一行還是多行，分為單行函式和多行函式，如果每次處理一行，是單行函式，如果每次處理是多行，就是多行函式，本章主要講單行函式，包括字元函式，數字函式及日期函式等。

1、字元函式

字元函式分兩類：

大小寫轉換的函式
對字元操作的函式

（1）大小寫轉換的函式

例子：查詢名字為Higgins的人員資訊

SQL> select employee_id,last_name,department_id from employees where last_name='higgins';

no rows selected

由於單引號內是區分大小寫的，故與Higgins不匹配，沒有結果。

使用lower函式進行轉換就可以匹配了。

SQL> select employee_id,last_name,department_id from employees where lower(last_name)='higgins';

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID

----------- ------------------------- -------------

205 Higgins 110

（2）對字元操作的函式

例子：從人員表中查詢工作編號第4個字母到最後為REP的人員，將他們的姓名顯示在一列中，還要顯示名字的長度以及名字中字母a的位置

SQL> select employee_id,concat(first_name,last_name) name,job_id,length(last_name),instr(last_name,'a') "Contains 'a'?" from employees where substr(job_id,4)='REP';

EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?

----------- --------------------------------------------- ---------- ----------------- -------------

202 PatFay MK_REP 3 2

2、數字函式

例子：使用round函式，四捨五入

SQL> select round(45.923,2),round(45.923,0),round(45.923,-1) from dual;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

--------------- --------------- ----------------

45.92 46 50

這裡引入了一個dual表，他是一個單行單列的虛擬表，基本上oracle引入dual為的就是符合語法，主要用來選擇系統變數或求一個表示式的值。

例子：使用trunc函式，去掉指定的位數

SQL> select trunc(45.923,2),trunc(45.923),trunc(45.923,-1) from dual;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)

--------------- ------------- ----------------

45.92 45 40

例子：使用mod函式，求模

SQL> select last_name,salary,mod(salary,5000) from employees where job_id='SA_REP';

LAST_NAME SALARY MOD(SALARY,5000)

------------------------- ---------- ----------------

Tucker 10000 0

3、日期函式

Oracle資料庫預設儲存世紀、年月日、時分秒，預設的日期格式是DD-MON-RR，RR表示的是世紀年，可以在21世紀儲存20世紀的日期，在20世紀儲存21世紀的日期。

（1）伺服器時間sysdate

例子：取伺服器時間

SQL> select sysdate from dual;

SYSDATE

------------

21-OCT-15

這裡看到取出來的伺服器時間只有日期，沒有時間，需要修改一下引數

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

-------------------

2015-10-21 22:44:16

（2）日期的算術運算

一個日期增加一個數字或者減去一個數字，結果為一個日期
兩個日期相減結果為之間的天數
透過除以24的方式為一天加上小時，如果是分鐘，就再除以60

例子：查詢員工至今入職多少周

SQL> select last_name,(sysdate-hire_date)/7 as weeks from employees where department_id=90;

LAST_NAME WEEKS

------------------------- ----------

King 644.278679

（3）日期函式

例子：檢視下一個星期天是幾號

SQL> select next_day(sysdate,1) from dual;

NEXT_DAY(SYS

------------

25-OCT-15

SQL> select next_day(sysdate,'sunday') from dual;

NEXT_DAY(SYS

------------

25-OCT-15

假定sysdate='25-JUL-03'

例子：對15號這一天使用round函式

SQL> select round(to_date('2014-04-15 23:59:59','yyyy-mm-dd hh24:mi:ss'),'month') from dual;

ROUND(TO_DAT

------------

01-APR-14

4、轉換函式

資料型別轉換有2種：

隱式轉換
顯示轉換

隱式轉換可以是從字元轉換成數字或者日期，也可以從數字或者日期轉換成字元。

顯示轉換使用to_char函式將數字或者日期轉換成字元，使用to_number函式將字元轉換成數字，使用to_date函式將字元轉換成日期。

（1）使用to_char函式將日期轉換成字元

格式串規則：

格式串需要使用單引號引起來
格式串大小寫敏感
可以使用任何有效的日期格式元素
可以使用fm消除前面的空格和前導0
格式串和前面的日期用逗號分割

日期元素格式：

時間元素格式：

可以使用帶雙引號的字串

拼寫成序數

例子：將員工的入職時間格式化顯示

SQL> select last_name,to_char(hire_date,'fmDD Month YYYY') as hiredate from employees;

LAST_NAME HIREDATE

------------------------- ---------------------

OConnell 21 June 2007

Mavris 7 June 2002

（2）使用to_char函式將數字轉換成字元

數字格式元素：

例子：將薪水轉換成字元顯示，單位是美元

SQL> select to_char(salary,'$99,999.00') salary from employees where last_name='Ernst';

SALARY

-----------

$6,000.00

例子：將薪水轉換成字元顯示，單位是￥，這裡和客戶端的作業系統的語言有關，我們Linux的語言是英文，我們Windows的中文，要顯示中文的貨幣符號，我們使用Windows的cmd連線過來

C:\Users\shilei>sqlplus hr/hr@192.168.230.139:1521/stone

SQL> select to_char(salary,'L99,999.00') from employees where employee_id=100;

TO_CHAR(SALARY,'L99,

--------------------

￥24,000.00

（3）字元轉換成數字和日期

例子：將16進位制'1e'轉換成10進位制

SQL> select to_number('1e','xx') from dual;

TO_NUMBER('1E','XX')

--------------------

30

例子：將字串轉換成日期，即使這個字串中間有空格也可以

SQL> select to_date('2014- 04-27','yyyy-mm-dd') from dual;

TO_DATE('201

------------

27-APR-14

如果格式串前面加上fx，表示精確匹配位數，有空格就不能進行轉換了

SQL> select to_date('2014- 04-27','fxyyyy-mm-dd') from dual;

select to_date('2014- 04-27','fxyyyy-mm-dd') from dual

*

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

去掉空格就可以了

SQL> select to_date('2014-04-27','fxyyyy-mm-dd') from dual;

TO_DATE('201

------------

27-APR-14

5、RR日期格式

使用RR日期格式就是使用兩位數字來表示世紀年，可以在21世紀儲存20世紀的日期，在20世紀儲存21世紀的日期。

例子：使用RR格式，獲取入職時間小於2006年的人員

SQL> select last_name,to_char(hire_date,'DD-Mon-YYYY') from employees where hire_date<to_date('01-jan-06','dd-mon-rr');

LAST_NAME TO_CHAR(HIRE_D</to_date('01-jan-06','dd-mon-rr');

------------------------- --------------

Whalen 17-Sep-2003

例子：對比yy和rr格式

SQL> select to_char(to_date('27-apr-88','dd-mon-yy'),'yyyy-mm-dd') from dual;

TO_CHAR(TO

----------

2088-04-27

SQL> select to_char(to_date('27-apr-88','dd-mon-rr'),'yyyy-mm-dd') from dual;

TO_CHAR(TO

----------

1988-04-27

6、巢狀函式

單行函式可以巢狀層數不限，從最裡面的函式計算起。

例子：取名字前8個字元並與'_US'拼接，大寫顯示

SQL> select last_name,upper(concat(substr(last_name,1,8),'_US')) from employees where department_id=60;

LAST_NAME UPPER(CONCAT(SUBSTR

------------------------- -------------------

Hunold HUNOLD_US

7、處理null的函式

NVL (expr1, expr2)：如果第一個引數為空那麼顯示第二個引數的值，如果第一個引數的值不為空，則顯示第一個引數本來的值。
NVL2 (expr1, expr2, expr3)：如果第一個引數為空那麼顯示第三個引數的值，如果第一個引數的值不為空，則顯示第二個引數的值。
NULLIF (expr1, expr2)：如果exp1和exp2相等則返回空(NULL)，否則返回第一個值。
COALESCE (expr1, expr2, ..., exprn)：如果第一個引數為空，則看第二個引數是否是空，不為空則顯示第二個引數，如果第二個引數是空再看第三個引數是否為空，不為空則顯示第三個引數，依次類推。

以上函式引數的資料型別必須匹配

例子：使用nvl函式將提成為空的轉換成提成為0

SQL> select last_name,salary,nvl(commission_pct,0),(salary*12)+(salary*12*nvl(commission_pct,0)) as sal from employees;

LAST_NAME SALARY NVL(COMMISSION_PCT,0) SAL

------------------------- ---------- --------------------- ----------

OConnell 2600 0 31200

例子：使用nvl2函式，如果提成為null，顯示SAL，如果提成不為null，則顯示SAL+COMM

SQL> select last_name,salary,commission_pct,nvl2(commission_pct,'SAL+COMM','SAL') income from employees where department_id in (50,80);

LAST_NAME SALARY COMMISSION_PCT INCOME

------------------------- ---------- -------------- --------

Vargas 2500 SAL

Russell 14000 .4 SAL+COMM

例子：使用nullif函式，如果姓和名的長度相等，則返回null，不相等，則返回姓的長度

SQL> select first_name,length(first_name) "expr1",last_name,length(last_name) "expr2",nullif(length(first_name),length(last_name)) result from employees;

FIRST_NAME expr1 LAST_NAME expr2 RESULT

-------------------- ---------- ------------------------- ---------- ----------

Kelly 5 Chung 5

Karen 5 Colmenares 10 5

例子：使用coalesce函式，如果manager_id為null，則顯示commission_pct的值，如果commission_pct也為null，則顯示-1

SQL> select last_name,coalesce(manager_id,commission_pct,-1) comm from employees order by commission_pct;

LAST_NAME COMM

------------------------- ----------

Lee 147

King -1

8、條件表示式

Oracle有2種方式實現條件表示式

case語句
decode函式

（1）case語句

需要注意：

尋找when的優先順序：從上到下
再多的when，也只有一個出口，即其中有一個滿足了expr就馬上退出case
不能把return_expr和else_expr指定為null，而且，expr、comparison_expr和return_expr的資料型別必須相同

例子：根據不同的job_id加不同比例的薪水

SQL> select last_name,job_id,salary,

2 case job_id when 'IT_PROG' then 1.10*salary

3 when 'ST_CLERK' then 1.15*salary

4 when 'SA_REP' then 1.20*salary

5 else salary

6 end "revised_salary"

7 from employees;

LAST_NAME JOB_ID SALARY revised_salary

------------------------- ---------- ---------- --------------

OConnell SH_CLERK 2600 2600

（2）decode函式

例子：根據不同的job_id加不同比例的薪水

SQL> select last_name,job_id,salary,

2 decode(job_id,'IT_PROG',1.10*salary,

3 'ST_CLERK',1.15*salary,

4 'SA_REP',1.20*salary,

5 salary)

6 as revised_salary

7 from employees;

LAST_NAME JOB_ID SALARY REVISED_SALARY

------------------------- ---------- ---------- --------------

OConnell SH_CLERK 2600 2600

例子：顯示部門編號為80的人員的稅率

SQL> select last_name,salary,

2 decode(trunc(salary/2000,0),

3 0,0.00,

4 1,0.09,

5 2,0.20,

6 3,0.30,

7 4,0.40,

8 5,0.42,

9 6,0.44,

10 0.45) as tax_rate

11 from employees

12 where department_id=80;

LAST_NAME SALARY TAX_RATE

------------------------- ---------- ----------

Russell 14000 .45

這章主要講了數字函式，字元函式，日期函式極其之間的轉換，以及對空的處理，和條件表示式。

9、相關習題

（1）View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions？ (Choose two.)

A.WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
B.WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
C.WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
D.WHERE order_date IN ( TO_DATE('Oct 21 2003','Mon DD YYYY'), TO_CHA('NOV 21 2003','Mon DD YYYY') )

答案：AB

（2）View the Exhibit and examine the description of the ORDERS table. Evaluate the following SQL statement: SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';Which statement is true regarding the execution of this SQL statement ?

A.It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within doublequotation marks.

B.It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.

C.It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.

D.It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.

答案：C

（3）View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees；The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query?

A.The query would execute to give the desired output.
B.The query would not execute because date functions cannot be nested.
C.The query would execute but the output would give review dates that are Sundays.
D.The query would not execute because the NEXT_DAY function accepts a string as argument.

答案：C

（4）Given below is a list of functions and the tasks performed by using these functions, in random order. Function Usage 1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b) Used to remove heading or trailing or both characters from the character string 3) DECODE c) Pads the character value right-justified to a total width of n character positions 4) TRIM d) Used to return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value ；Which option correctly matches the function names with their usage ?
A.1-c, 2-b, 3-e, 4-a, 5-d
B.1-e, 2-b, 3-c, 4-a, 5-d
C.1-e, 2-a, 3-c, 4-d, 5-b
D.1-c, 2-a, 3-e, 4-b, 5-d

答案：D

（5）Which three statements are true regarding single-row functions？(Choose three.)
A.They can accept only one argument.
B.They can be nested up to only two levels.
C.They can return multiple values of more than one data type.
D.They can be used in SELECT, WHERE, and ORDER BY clauses.
E.They can modify the data type of the argument that is referenced.
F.They can accept a column name, expression, variable name, or a user-supplied constant as arguments.

答案：DEF

（6）View the Exhibit and examine the description of the EMPLOYEES table. You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. Which SQL statement would you execute to get the desired output？

A.SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES；
B.SELECT first_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES；
C.SELECT first_name, salary, salary*12+ NVL(salary, 0)*commission_pct "Total" FROM EMPLOYEES；
D.SELECT first_name, salary, salary*12+(salary*NVL2(commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES；

答案：B

OCP課程5：SQL之使用單行函式

相關文章