Oracle PL\SQL 操作（三）Oracle函數

1.系統變數函數

（1）SYSDATE

該函數返回當前的日期和時間。返回的是Oracle伺服器的當前日期和時間。

select sysdate from dual; insert into purchase values (‘Small Widget’,’SH’,sysdate, 10); insert into purchase values (‘Meduem Wodget’,’SH’,sysdate-15, 15);

查看最近30天的所有銷售記錄，使用如下命令：

select * from purchase where purchase_date between (sysdate-30) and sysdate; 

（2）USER

查看使用者名稱。

select user from dual; 

（3）USERENV

查看使用者環境的各種資料。

select userenv(‘TERMINAL’) FROM dual; 

2.數值函數

（1）ROUND 四捨五入函數

ROUND(數值，保留位元）

select round(3.1415,3) from deul; select product_name,round(product_price,0) price from product; 

（2）TRUNC 從數中截去小數部分

TRUNC(數值，截斷小數點n位後的數）

select trunc(3.145159,3) from dual; select trunc(123456.45,-1) from dual; select trunc(123456.45) from dual; select product_name,trunc(product_price) price from product;

3.文本函數

（1）UPPER、LOWER和INITCAP

這三個函數更改提供給它們的文體的大小寫。

select upper(product_name) from product; select lower(product_name) from product; select initcap(product_name) from product; 

函數INITCAP能夠整理雜亂的文本，如下：

select initcap(‘this TEXT hAd UNpredictABLE caSE’) from dual; 

（2）LENGTH

求資料庫列中的資料所佔的長度。

select product_name,length(product_name) name_length from product order by product_name; 

（3）SUBSTR

取子串，格式為：

SUBSTR（源字串，起始位置，子串長度）；

create table item_test(item_id char(20),item_desc char(25)); insert into item_test values(‘LA-101’,’Can, Small’); insert into item_test values(‘LA-102’,’Bottle, Small’); insert into item_test values(‘LA-103’,’Bottle, Large’); 

取編號：

select substr(item_id,4,3) item_num,item_desc from item_test; 

（4）INSTR

確定子串在字串中的位置，格式如下：

INSTR(源字串，要尋找的字串，尋找起始位置）

select instr(‘this is line one’,’line’,1) from dual; 

其傳回值為子串在源字串中從起始位置開始第一次出現的位置。上面例子的傳回值為9。

select item_desc , instr(item_desc,’,’,1) from item_test; 

（5）LTRIM、RTRIM和TRIM

去除字串左邊的空格、去除字串右邊的空格、去除字串左右兩邊的空格。

select ltrim(‘ abc def ‘) from dual; 

4.日期函數

（1）SYSDATE和TRUNC

這兩個函數前面已經出現過，即取Oracle伺服器系統的日期和截掉小數部分的功能。觀察以下操作：

create table test_date (name varchar2(20), p_date date); insert into test_date values(‘name1’,sysdate); select * from test_date; select * from test_date where p_date=’25-10月-05‘; select * from test_date where trunc(p_date)= ’25-10月-05‘; 

Oracle系統中用SYSDATE取得的不僅包含日期而且還包含的有時間資訊，時間資訊實際上就是表示儒略日資料中的小數部分。

（2）ADD_MONTHS

該函數返回一個具有與所提供日期相差月份的日期，函數中給出了未來或以前的月份數。文法如下：

ADD_MONTHS（起始日期，增減月數） select add_months(’26-10月-05‘,2) from dual; select add_months(’26-10月-05‘,-2) from dual; 

（3）LAST_DAY

返回包含給定日期的那個月的最後一天。文法為：

LAST_DAY(日期）

select last_day(’21-2月-80‘) from dual; 

（4）MONTHS_BETWEEN

返回兩個日期間的月份。文法為：

MONTHS_BETWEEN（較晚日期，較早日期）

select months_between(’12-10月-05‘,’12-9月-03‘) from dual; 

5.資料轉換函式

（1）TO_CHAR

該函數將日期、時間和數值轉換為文本。它的主要價值是提供對日期、時間和數的顯示控制；該函數的用法非常靈活，使用較複雜，在此我們僅對經常使用的轉換舉例說明。

1）格式化日期和時間值

TO_CHAR（日期資料，格式編碼）

select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) from dual; select to_char(sysdate,’HH24:MI:SS’) from dual; select to_char(sysdate,‘YYYY’)||‘年’|| to_char(sysdate,'MM')||'月'|| to_char(sysdate,'DD')||'日' 日期 from dual 

2）格式化數值

select to_char(5764.12345,'99,999.9999') from dual; select to_char(5764.12345,‘09,999.9999') from dual; 

（2）TO_DATE

將文本轉換為實際的Oracle日期/時間值。格式：

TO_DATE(文本，日期格式）

select to_date(‘2005-10-10 11’,’YYYY-MM-DD HH24’) from dual; insert into item_test values( ‘name-x’, to_date(‘2005-10-25’,’YYYY-MM-DD’) ) ; 

6.其它函數

NVL函數完成一個簡單但有用的功能。任何時候給它一個空值，它都返回一個你所選擇的值。格式如下：

NAL(輸入值，如果輸入值為空白要返回的值)

select nvl(null,’new_value’) from dual; select name,nvl(p_date,sysdate) from test_date ; 

注意：函數中‘輸入值’和‘如果輸入值為空白要返回的值’的類型必須一致。

 

7.常用的分組函數

（1）SUM

該函數累加值並返回總數。

select sum(quantity) from purchase; select sum(sal) from emp; 

(2)COUNT

該函數對記錄進行統計。

select count(*) from emp; 

這個命令有缺點，一般不應該使用。如果一個表有數十萬條記錄，強制進行全表掃描將會使計數結果的返回非常慢，會降低其他業務工作的處理效率。因些COUNT函數對指定進行單列統計效果會好得多。

select count(ename) from emp; select count(1) from emp; 

（3）AVG

該函數返回指定列中值的平均數。使用這個函數必須給出相應的列名，且相應列應為數實值型別。

select avg(sal) from emp; 

（4）MIN

該函數返回作為參數給出的某列中的最小值。

select min(sal) from emp; 

（5）MAX 用法與MIN相似

select max(sal) from emp; 

8.利用GROUP BY子句分組資料

完成下列操作：

create table tb1( name varchar2(10),empno number(3),sal number(4) ); insert into tb1 values(‘name-a’,1,1200); insert into tb1 values(‘name-b’,1,1500); insert into tb1 values(‘name-c’,1,2100); insert into tb1 values(‘name-d’,2,900); insert into tb1 values(‘name-e’,2,1300); select empno,sum(sal) from tb1 group by empno; select empno,avg(sal),min(sal),max(sal),count(empno) from tb1 group by empno; 

9.利用HAVING子句過濾分組的行

select empno, sum(sal), avg(sal), min(sal), max(sal), count(empno) from test group by empno having sum(sal)>2500