ORACLE常用結構和函數使用方法總結

ORACLE常用結構和函數使用方法總結

在學習oracle的過程中，遇到一些常用的函數或結構，下面做一個簡單的總結。

　　With用法：

　　The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.

WITH <alias_one> AS

  (subquery_sql_statement),

     <alias_two> AS

  (sql_statement_from_alias_one)

SELECT <column_name_list>

FROM <alias_one>, <alias_two>

WHERE <join_condition>;

　　例子：

with

  sql1 as (select to_char(a) s_name from test_tempa),

  sql2 as (select to_char(b) s_name from test_tempb where not exists ( select s_name from sql1 where rownum=1))

  select * from sql1

  union all  select * from sql2

　　Decode用法：

　　Decode函數與一系列嵌套的 IF-THEN-ELSE語句相似。base_exp與compare1,compare2等等依次進行比較。如果base_exp和 第i 個compare項比對，就傳回第i 個對應的value 。如果base_exp與任何的compare值都不比對，則傳回default;

decode(條件,值1,傳回值1,值2,傳回值2,...值n,傳回值n,預設值

SELECT   DECODE(Status,0,'未預定'1,'已預定',2,'已入庫')  AS status

FROM   Stocks  

　　//分析:

　　當Status=0時,将"未預定"指派

　　當Status=1時,将"已預定"指派

　　當Status=2時,将"已入庫"指派

　　NVL()函數:

　　NVL(ARG,VALUE)達标如果前面的ARG值為NULL那麼傳回的值為後面的VALUE

NVL(eExpression1, eExpression2)

 　　eExpression1 和 eExpression2 可以是任意一種資料類型;

　　DECODE與NVL結合使用:

DECODE(NVL(ChangeNum,''),'','-',ChangeNum)  

　　首先判斷ChangeNum是否為空.若為空賦空值[NVL函數]

　　其次,判斷取出的字段是否為空,如果是則賦'-'

　　最後的預設處理是ChangeNum。

　　LPAD和RPAD的用法:

lpad( string1, padded_length, [ pad_string ] )

　　其中string1是需要粘貼字元的字元串

　　padded_length 是傳回的字元串的數量，如果這個數量比原字元串的長度要短，lpad函數将會把字元串截取成padded_length;pad_string是個可選參數，如果這個參數未寫，lpad函數将會在string1的左邊粘貼空格。

select lpad('tech',2) from dual

傳回值：t

eselect lpad('tech', 8, '0') from dual

傳回值：0000tech

　　TRUNC用法:

TRUNC(for dates)

 　　TRUNC函數為指定元素而截去的日期值。

　　其具體的文法格式:TRUNC(date[,fmt])

　　date 一個日期值

　　fmt 日期格式，該日期将由指定的元素格式所截去。忽略它則由最近的日期截去

TRUNC（TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)）      

=’24-Nov-1999 12:00:00 am’  

TRUNC（TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)）   

 =’24-Nov-1999 08:00:00 am’  

　　另一種用法

TRUNC(for number)

 　　TRUNC函數傳回處理後的數值，其工作機制與ROUND函數極為類似，隻是該函數不對指定小數前或後的部分做相應舍入選擇處理，而統統截去。

　　其具體的文法格式：TRUNC(number[,decimals])

　　number 待做截取處理的數值

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80