字符函數

字符函數參數全部是字符類作為參數，字符函數返回值有的是varchar2類型的值，有的是number類型的值，返回number類型時，沒有定義任何精度和刻度范圍

表3-1-1字符函數

在電子商務系統中，對于用戶的聯系方式，特別是固定電話，在數據庫中區號和電話號碼是分別用兩列存貯的，因為這樣方便查詢。但是展現信息是需要區號和電話作為一個整體。此時必須使用 字符串拼接函數CONCAT

示例3.3：

SQL> SELECT CONCAT(‘010-’,’88888888’) || ‘轉23’ 電話號碼 from dual;

連接010、88888888、轉23這3個字符串 

字符串連接可以有兩種方法，一是使用CONCAT函數，二是使用||字符串連接符。

美國人的名字中每個單詞的首字母必須大寫顯示，這是英語的習慣。下面要顯示邁克爾杰克遜人名，需要將每個單詞首字符大寫

示例3.4：

SQL> select initcap(‘michael jackson’) as name  from dual;

有些時候，我們可能需要在一長串字符中找某個字符串，并且有可能需要找到第幾次出現的位置，這時可以使用instr函數。

在oracle traning字符串中找ra字符，從第一個字符開始，查找第二次出現的位置示例3.5：

SQL> select instr(‘oracle training’,’ra’,1,2) instring from dual;

INSTR一共有4個參數，第一個是被查找的字符串，第二個是要查找的字符串，我們要查找ra字符串，第三個參數是一個數字，表示從哪個位置開始找，此參數可選，如果省略默認為1。第四個參數表示要查找第幾次出現的ra，此參數可選，如果省略默認為1。

在英語信息中，有時我們可能需要可能首字符大寫、全大寫和全小寫的形式向客戶展示我們的信息，這時我們可以使用initcap,upper,lower函數。

示例3.6：

SQL> select initcap(ename), upper(ename), lower(ename) from emp;

上面的例子分別以首字符大寫、全大寫和全小寫的形式顯示emp表中的姓名(ename)字段

電子商務中，我們有時需要在源字符串的基礎上在其前后增加若干個特殊字符，已達到某一個固定的長度。這時可以使用lpad,rpad函數

示例3.7：

SQL> select lpad(rpad(‘gao’,10,’#’),17,’*’) from dual;

上面的示例將gao字符串右邊增加若干個#使總長度達到10，然后再左邊增加若干個*，使總長度達到17

RPAD 是在列的右邊粘貼字符包括有3個參數，第一個是原字符串gao，第二個參數是增加后達到10個字符，第三個參數表示要加#字符。

示例3.8： 

SQL> select ltrim(rtrim(‘’****gao qian jing    ,’ ’),’*’) from dual;

上面的示例刪除了”****gao qian jing   “字符串左邊的*，再刪除右邊的空格

取字符串的子串在實際的業務中用的非常多，如取身份證的某幾位得到某人的出生年月日，再如獲得一個移動電話的后8位等等。

示例3.9：

SQL> select substr(‘13088888888’,3,8) from dual;

上面的示例從13088888888中取子字符串,從3開始,取8個

將字符串中的子串替換成新的值在業務中也有使用，比如我們將信息中的一些不雅的詞語替換成****，這時我們可以使用replace函數

示例3.10：

SQL> select replace(‘he love you’,’he’,’I’) from dual;

上面的示例替換he love you字符串中的he為i

用戶輸入時用戶名或密碼時有可能輸入空格，我們可以使用trim刪除兩端的空格

示例3.11：

SQL> select trim(‘   tech   ‘) from dual;  

trim('字符1' from '字符串2')  分別從字符2串的兩邊開始，刪除指定的字符1

SQL>select trim(‘ ‘ from ‘    tech       ‘)  from dual;

刪除000123頭部的0字符

SQL> select trim(leading ‘0’ from ‘000123’)  from dual;

刪除Tech1尾部的1字符

SQL>select trim(trailing ‘1’ from ‘Tech1’) from dual;

刪除123Tech111兩端的1字符

SQL>select trim (both  ‘1’ from ‘123tech111’) from dual;

注意：leading表示從字符串的頭開始刪除。Trailing表示從字符串的尾部開始刪除。Borth表示從字符串的兩邊刪除。

當然也可以簡化成

select ltrim('****gao qian jing------','*') from dual

數學函數

數學函數以number類型作為參數返回number類型。

表3-1-2數學函數

天花板函數示例3.12：

SQL> select ceil(3.1415927) from dual;

地板函數 示例3.13：

 select floor(2345.67) from dual;

示例3.14： 

SQL> select round(124.1666,-2) , round(124.1666,2) from dual;

SQL> select trunc(124.1666,-2) , trunc(124.16666,2) from dual;

注意：Round函數進行四舍五入，trunc函數進行截取。第二個參數為正時，表示從小數位計算。第二個參數為負時，表示從整數位計算。

日期函數

日期函數以DATE類型為參數。除了MONTHS_BETWEEN函數，它返回NUMBER類型，它返回DATE或日期時間類型。日期函數常用的格式模型如表3-1-1所示

表3-1-3   格式模型

表3-1-4  日期函數

示例3.15:

SQL> select sysdate, to_char(sysdate,’dd-mm-yyyy day’) from dual;

有時我們想按照自己的格式顯示今天幾號星期幾，可以采用以上寫法

trunc(date,fmt)按照給出的要求將日期截斷,如果fmt='mi'表示保留分,截斷秒

想得到某個日期前后若干月是哪一天，可以使用ADD_MONTHS函數增加或減去月份

示例3.16:

SQL> select to_char(add_months(to_date('1999-12','yyyy-mm'),2),'yyyy-mm') from dual;

在人員管理系統中，我們可能需要得到某兩個日期相差多少個月，或某日期和今天相差月份，可以使用MONTHS_BETWEEN函數

示例3.17：

SQL> select months_between('9-12月-1999','19-3月-1999') mon_between from dual;

轉換函數：

轉換函數是將PL/SQL數據由一種數據類型轉到另一種數據類型,例如to_date to_char tonumber

PL/SQL將會通過對轉換函數的隱式調用進行自動轉換。但是，隱式調用轉換函數時無法對使用的格式指定符進行控制，這時我們最好使用顯式調用轉換函數。

SQL> select * from test;

       AGE

----------

         1

SQL> select * from test where age='1';

表3-1-5 轉換函數

將今天按照某種特殊的格式顯示，可以使用to_char

示例18：

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

表3-1-6  to_date函數格式模板

示例19：

SQL> select * from emp where hiredate> to_date(‘1990-12-2’,’yyyy-mm-dd’);

SQL> select  to_date(‘3月 7，1999’,’Month DD,yyyy) from dual;

SQL>select  to_char(hiredate,’yyyy-MM-dd day’) from emp where rownum<10;

將字符串轉化為ORACLE中的一個日期

保存到數據庫中的數據可能是字符串，但是我們可以將其轉換為數字再參與數學運算，這時使用to_number函數。如年份可能保存成字符，計算1999年后是哪一年

示例20：

SQL> select to_number(‘1999’) + 10  year  from dual;

其他函數

用戶登錄成功后，我們可能要記錄當前登錄用戶的姓名和id，這時我們要使用UID,USER函數

示例20:

SQL> select uid from dual;

SQL> select username,user_id from user_users where user_id = uid;

顯示當前用戶編號，當然我們可以把這個編號存入自己定義的日志表中。

在數據庫中，經常出現某個字段值為空。這時，我們希望字段值為空時，顯示某個值，否則顯示自身。例如獎金字段(comm)的值為null,返回0，不為null顯示comm.

示例21：

SQL>select empno,ename,hiredate,sal,nvl(comm,0) ,sal+nvl(comm,0) from emp;

除了nvl外，還可以使用nvl2函數。Nvl2有3個參數，如果參數1不為NULL, 返回第二個參數；為NULL，返回第三個參數。

示例22：

SQL> SELECT COMM,NVL2(COMM,COMM,0) FROM EMP;

獎金字段COMM不為NULL，返回COMM；為NULL，返回0。

3.7 聚合函數

聚合函數將多條記錄聚合成一條記錄。聚合函數有AVG,MAX,MIN,COUNT,SUM

示例23：創建示例表和示例數據

SQL> create table test(xm varchar(8),sal number(7,2));

語句已處理。

SQL>  insert into table3 values(‘gao’,1111.11);

SQL>  insert into table3 values(‘gao’,1111.11);

SQL>  insert into table3 values(‘zhu’,5555.55);

SQL> commit;

得到不同的sal的平均值，如果出現相同的sal值，如1111.11出現兩次，只算一次。

SQL> select avg(distinct sal) from test;

得到所有的sal的平均值，如果出現相同的sal值，如1111.11出現兩次，算兩次。

SQL> select avg(all sal) from gao.test;

意：如果什么都不寫，默認是all

3.8  Oracle分析函數Oracle從8.1.6開始提供分析函數，分析函數用于計算基于組的某種聚合值，它和聚合函數的不同之處是對于每個組返回多行，而聚合函數對于每個組只返回一行。  常用的分析函數如下所列: row_number() over (partition by ... order by ...) rank() over (partition by ... order by ...) dense_rank() over (partition by ... order by ...)

count() over (partition by ... order by ...) max() over (partition by ... order by ...) min() over (partition by ... order by ...) sum() over (partition by ... order by ...) avg() over (partition by ... order by ...)

OVER子句前面必須是排名函數或者是聚合函數。

注意：下面例子中使用的表來自Oracle自帶的HR用戶下的表，如果沒有安裝該用戶，可以在SYS用戶下運行 $ORACLE_HOME/demo/schema/human_resources/hr_main.sql來創建。 

開窗函數的的理解： 開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化

舉例如下： over（order by salary） 按照salary排序進行累計，order by是個默認的開窗函數 over（partition by deptno）按照部門分區 over（order by salary range between 50 preceding and 150 following） 每行對應的數據窗口是之前行幅度值不超過50，之后行幅度值不超過150 over（order by salary rows between 50 preceding and 150 following） 每行對應的數據窗口是之前50行，之后150行 over（order by salary rows between unbounded preceding and unbounded following） 每行對應的數據窗口是從第一行到最后一行

等效： over（order by salary range between unbounded preceding and unbounded following） 先查詢 后開窗 然后再統計

示例3.24：

下面的例子中列c_mavg計算員工表中每個員工的平均薪水報告，按照manager_id分組

SQL>select manager_id, last_name, hire_date, salary,

Avg(salary) over (partition by manager_id) as c_mavg from employees;

示例3.25：下面的例子中列c_mavg計算員工表中每個員工的平均薪水報告，該平均值由當前員工和與之具有相同經理的前一個和后一個三者的平均數得來；

SQL>SELECT manager_id, last_name, hire_date, salary,

AVG(salary) OVER (PARTITION BY manager_id order by hire_date rows BETWEEN 1 PRECEDING and 3 FOLLOWING ) AS c_mavg

FROM employees;

示例3.27：下面例子中dept_max返回當前行所在部門的最大薪水值 SQL>SELECT department_id, last_name, salary, MAX(salary) OVER (PARTITION BY department_id) AS dept_max

FROM employees WHERE department_id in (10,20,30); 

實例3.28：下面例子中dept_min返回當前行所在部門的最小薪水值 SQL>SELECT department_id, last_name, salary, MIN(salary) OVER (PARTITION BY department_id) AS dept_min FROM employees WHERE department_id in (10,20,30); 

示例3.29 . DENSE_RANK 功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。密集的序列返回的時沒有間隔的數

下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與RANK函數的區別）

SQL>SELECT d.department_id , e.last_name, e.salary, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id IN ('60', '90'); 

示例3.30 RANK 功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序， 然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。 有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨后跳躍。若兩行序數為1， 則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。

下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與DENSE_RANK函數的區別） SQL>SELECT d.department_id , e.last_name, e.salary, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id IN ('60', '90'); 

示例3.31  ROW_NUMBER 功能描述：返回有序組中一行的偏移量，從而可用于按特定標準排序的行號。

下例返回每個員工再在每個部門中按員工號排序后的順序號 SQL>SELECT department_id, last_name, employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id FROM employees WHERE department_id < 50; 

示例3.32. SUM 功能描述：該函數計算組中表達式的累積和。

下例計算同一經理下員工的薪水累積值 SQL>SELECT manager_id, last_name, salary, SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees WHERE manager_id in (101,103,108); 

count分析函數對一組內發生的事情進行累積計數，如果指定*或一些非空常數，count將對所有行計數，如果指定一個表達式，count返回表達式非空賦值的計數，當有相同值出現時，這些相等的值都會被納入被計算的值；可以使用DISTINCT來記錄去掉一組中完全 相同的數據后出現的行數。 

示例3.26：下面例子中計算每個員工在按薪水排序中當前行附近薪水在[n-50,n+150]之間的行數，n表示當前行的薪水 例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行 沒有，所以count計數值cnt3為2（包括自己當前行）；cnt2值相當于小于等于當前行的SALARY值的所有行數

SQL>SELECT last_name, salary, COUNT(*) OVER () AS cnt1, COUNT(*) OVER (ORDER BY salary) AS cnt2, COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3 FROM employees; 

示例3.33: RATIO_TO_REPORT 功能描述：該函數計算expression/(sum(expression))的值，它給出相對于總數的百分比，即當前行對sum(expression)的貢獻。

下例計算每個員工的工資占該類員工總工資的百分比 SQL>SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees  WHERE job_id = 'PU_CLERK'; 

任務實訓部分

1、 假設學校環境如下：

一個系有若干個專業，每個專業一年只招一個班，每個班有若干個學生?，F要建立關于系、學生、班級的數據庫，關系模式如下：

班級表class（班號classid，專業名subject，系名deptname，入學年份entertime，人數num）

學生表student（學號studentid，姓名name，年齡age，班號classid）

系department（系號departmentid，系名deptname）

要求用sql語句完成如下功能：  

（1）建表，并實現如下要求

A：每個表根據實際定義好主外鍵關系

B：系表中編號字段利用數據庫自動化增長值實現

C：每個班級的人數不超過30人

D：學生年齡介于15到30之間

E：學生姓名不能為空，系名不能重復

（2）輸入部分測試數據

department表：

1  數學

2 計算機

3 化學

4 中文

5 經濟學

class表：

101 軟件  計算機  1995  20

102 微電子  計算機  1996  30

111  無機化學  化學  1995   29

112 高分子化學  化學 1996  25

121  統計  數學  1995   20

131 現代語言  中文  1996  20

141 國際貿易  經濟學  1997  30

142 國際金融  經濟學   1998   14

student表：

8101 張三 18  101

8102 錢四 16  121

8103 王玲 17  131

8104 李飛  19  102

8105 王五  20  141

（3）完成下列查詢：

A：列出所有人數大于等于28的系的編號和名稱

B：列出所有開設超過兩個專業的系的名字

C：顯示每個學生的學號、姓名、專業、系名信息

2、oracle函數的使用

針對scott.emp表，實現如下查詢操作：

（1） 把員工姓名和工作連接在一起，中間用“--”分隔顯示。

（2） 分別顯示工姓名的前三個字符和第四個字符后的內容

（3） 顯示字母T在員工姓名中第一次和第二次出現的位置

（4） 顯示12年前參加工作的員工信息

（5） 查詢在當月倒數第三天參加工作的員工信息

（6） 顯示每個員工的工作天數

（7） 按照每月30天，計算每個員工的日薪金

（8） 按照年和月的格式顯示員工參加工作的時間（如  SMITH  1980  12）

（9） 查詢在1987年2月到5月參加工作的員工信息（包括2月和5月）

（10） 顯示每個員工的津貼信息，沒有津貼就顯示0

（11） 分別顯示員工的總人數和津貼不為空的員工人數

（12） 顯示部門最低工資大于900的部門和最低工資

（13） 顯示每個部門工資在1400以上的所有員工的工資總額

（14） 顯示部門名稱以及該部門的員工總數，沒有員工的以0顯示

（15） 顯示每個部門中最高工資的員工信息

（16） 計算每個部門的員工工資排名

（17） 計算每個部門的員工工資占整個部門的工資比例

（18） 計算同一部門員工的薪水累積值

3、員工培訓系統的案例

Employee員工表：

EID       Name     Department     Job    Email    passWord

    10001      李明      SBB           EG

10003      李四      LUCK        ITM

11045      胡斐      SBB           EG

10044      張三      MTD         ETN

10023      王剛      MMM        ETN

Training 培訓記錄表：

CourseId    EID      Course     Grade     orders

1          10001     T-SQL     60

3          11045     java       71

2          10003     oracle     59

1          10003     T-SQL     90

3          10044     java       78

2          10001     oracle      69

2          10023     oracle      70

3          20001     Java       69

3          10078     Java       58

完成如下sql語句：

（1）建立兩個表的表結構，自行分析，根據需要設置主鍵

（2）用sql語句進行冊書數據的添加

（3）列出所有員工參加培訓的情況，要求顯示：EID，Name，department，course，grade用一條sql語句完成

（4）列出未參加培訓的員工信息，顯示格式如上

（5）列出所有各課成績最高的員工信息，顯示格式如上

（6）把所有表2有但表1沒有的員工編號插入表1中，一條語句完成

（7）統計各部門的人數

（8）統計各部門中姓李的人數

鞏固練習

1．Oracle返回“Hello World”的函數是：（）

A.UPPER

B.LOWER

C. INITCAP

D.CONCAT

1. 下面哪個分析函數返回連續的重復的行編號（）

A.ROW_NUMBER

B.RANK

C.DENSE_RANK

2. SELECT ROUND (164.1666,-2) FROM DUAL返回值（）

A．100

B．150

C．200

D．160

4．Oracle分析函數的特征有（）

A．可以帶over(partition by..order ..)

B．分析函數用于計算基于組的某種聚合值

C．它和聚合函數的不同之處是對于每個組返回多行，而聚合函數對于每個組只返回一行。

D．over在每組中定義了一個滑動窗口