① oracle_plsql函數大全
1.ascii('A') 返回與指定的字元對應的十進制數
2.chr(65) 給出整數,返回對應的字元;
3. concat('010-','88888888') 連接字元串
4.initcap('smith') 返回字元串並將字元串的第一個字母變為大寫;
5.instr(C1,C2,I,J) 在字元串C1中搜索字元串C2,返回發現指定的字元的位置;
I搜索的開始位置,默認為1。 J出現的位置,默認為1。
6.length(name) 返回字元串的長度;
7. lower('AaBbCcDd') 返回字元串,並將所有的字元小寫
8. upper('AaBbCcDd') upper from al;返回字元串,並將所有的字元大寫
9.rpad和lpad(粘貼字元) lpad(rpad('gao',10,'*'),17,'*') 得*******gao*******
10.ltrim和rtrim和trim 刪除字元兩邊出現的空格。
11. substr (string,start,count)取子字元串,從start開始,取count個
12. replace ('string','s1','s2') string希望被替換的字元或變數,s1被替換的字元串
s2要替換的字元串
13. soundex 返回一個與給定的字元串讀音相同的字元串
14.TRIM('s' from 'string') 剪掉前面和後面的字元
15. abs(100) 返回指定值的絕對值
16. acos(-1) 給出反餘弦的值
17. asin(0.5) 給出反正弦的值
18. atan(1) 返回一個數字的反正切值
19. ceil(3.1415927) 返回大於或等於給出數字的最小整數
20. cos(-3.1415927) 返回一個給定數字的餘弦
21. cosh(20) 返回一個數字反餘弦值
22. exp(2) 返回一個數字e的n次方根
23. floor(2345.67) 對給定的數字取整數
24. ln(1) 返回一個數字的對數值
25.log(n1,n2) 返回一個以n1為底n2的對數
26.mod(n1,n2) 返回一個n1除以n2的余數
27.power(n1,n2) 返回n1的n2次方根
28. round和trunc 按照指定的精度進行舍入
29.sign(n) 取數字n的符號,大於0返回1,小於0返回-1,等於0返回0
30. sin(1.57079) 返回一個數字的正弦值
31. sin(20) 返回雙曲正弦的值
32.sqrt(n) 返回數字n的根
33. tan(n) 返回數字n的正切值
34. tanh(n) 返回數字n的雙曲正切值
35. trunc (124.16666,2) 按照指定的精度截取一個數
36. add_months 增加或減去月份
37. last_day(sysdate) 返回日期的最後一天
38. months_between (date2,date1) 給出date2-date1的月份
39. new_time (date,'this','that') 給出在this時區=other時區的日期和時間
40. next_day (date,'day') 給出日期date和星期x之後計算下一個星期的日期
41. sysdate 用來得到系統的當前日期
42. chartorowid 將字元數據類型轉換為ROWID類型
43. convert (c,dset,sset)
44. hextoraw 將一個十六進制構成的字元串轉換為二進制
45. rawtohext 將一個二進制構成的字元串轉換為十六進
46. rowidtochar 將ROWID數據類型轉換為字元類型
47. to_char (date,'format')
48. to_date(string,'format') 將字元串轉化為ORACLE中的一個日期
49. to_multi_byte('高') 將字元串中的單位元組字元轉化為多位元組字元
50. to_number('1999') 將給出的字元轉換為數字
51. bfilename (dir,file)
52. convert('x','desc','source') 將x欄位或變數的源source轉換為desc
53. mp(s,fmt,start,length)
54. empty_blob()和empty_clob() 這兩個函數都是用來對大數據類型欄位進行初始化操
作的函數
55. greatest('AA','AB','AC') 返回一組表達式中的最大值,即比較字元的編碼大小.
56. least('啊','安','天') 返回一組表達式中的最小值
57. uid 返回標識當前用戶的唯一整數(user_id=uid)
58. user 返回當前用戶的名字
59. userevn 返回當前用戶環境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看當前用戶是否是DBA如果是則返回true
60.avg(DISTINCT|ALL) all表示對所有的值求平均值,distinct只對不同的值求平均值
61.max(DISTINCT|ALL) 求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次
62.min(DISTINCT|ALL) 求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
63. stddev (distinct|all) 求標准差,DISTINCT表示只對不同的值求標准差
64. variance (DISTINCT|ALL) 求協方差
65. group by主要用來對一組數進行統計
66. having 對分組統計再加限制條件
67.ORDER BY 用於對查詢到的結果進行排序輸出
② oracle 合並查詢 事務 sql函數小知識學習
表查詢：
合並查詢：使用union關鍵字，可將滿足條件的重復行去掉。
復制代碼
代碼如下:
select
ename,sal,job
from
emp
where
sal
>
2500
union
select
ename,sal,job
from
emp
where
job
=
'MANAGER';
而union
all用法和union相似，但是不會取消重復行。
intersect
用來取兩個結果的交集。
minus用來取兩個結果的差集。
使員工scott的崗位，工資，補助與SMITH員工一樣。（使用子查詢修改數據）
復制代碼
代碼如下:
update
emp
set
(job,sal,comm)=(select
job,sal,comm
from
emp
where
ename
=
'SMITH')
where
ename
=
'SCOTT';
事務：
設置保存點
savepoint
a
取消部分事務
roll
back
to
a
取消全部事務
rollback
設置為只讀事務，用於統計某一刻之前的信息，而在統計過程中，可能還有訪問，影響統計，所以，統計之前，設為只讀事務，這樣就保存此刻之前的結果，而之後的修改，將不會顯示出來，設為只讀事務的語句為：
復制代碼
代碼如下:
set
transaction
read
only;
設置之後會顯示事務處理集。
sql函數：
將顯示內容以小寫形式顯示，使用lower函數，比如
復制代碼
代碼如下:
select
lower(ename),sal
from
emp;
顯示內容以大寫形式顯示，使用upper函數.還有length函數和substr函數。
復制代碼
代碼如下:
select
*
from
emp
where
length(ename)=5;
select
substr(ename,1,3)
from
emp;
substr表示從第一個取，取3個。
以首字母大寫的方式顯示所有員工的姓名。
將員工的姓名首字母大寫
復制代碼
代碼如下:
select
upper(sub(ename,1,1)
from
emp;
將第一個字母之後的字母以小寫的形式表示
復制代碼
代碼如下:
<PRE
class=sql
name="code">select
lower(substr(ename,2,length(ename)-1))
from
emp;</PRE><BR>
然後將兩個結果合並，則得到了要顯示的內容:
<PRE></PRE>
<PRE
class=sql
name="code"
sizcache="0"
sizset="11"><PRE
class=sql
name="code">select
upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))
as
name
from
emp;</PRE><BR>
替換函數replace
<PRE></PRE>
<PRE
class=sql
name="code"
sizcache="0"
sizset="14"><PRE
class=sql
name="code">select
replace(ename,'A','我')
from
emp;</PRE><BR>
<BR>
<BR>
<PRE></PRE>
</PRE></PRE>
③ oracle sql 管道函數是什麼
oracle管道函數是一類特殊的函數，關鍵字PIPELINED表明這是一個oracle管道函數，oracle管道函數返回值類型必須為集合。
例子：
create or replace function f_pipeline_test
return MsgType
PIPELINED
as
begin
for i in 1 .. 10
loop
pipe row( 'Iteration ' || i || ' at ' || systimestamp );
dbms_lock.sleep(1);
end loop;
pipe row( 'All done!' );
return;
end;
/
④ oracle sql中count、case函數運用
count
表示的是計數，也就是說記錄的條數,通常和分組函數一起使用。
sql：select
userId
,
count(*)
from
tablename
group
by
userId。
case表示的是多條件判斷。
sql：select
ename,
case
when
sal<1000
then
'lower'
when
sal>1001
and
sal<2000
then
'modest'
when
sal>2001
and
sal<4000
then
'high'
else
'too
high'
end
from
emp;
以上語句就是一個簡單的判斷工資等級的一個case用法。
⑤ oracle sql中count、case函數運用
selecta.lastname,ISNULL(COUNT(1),0)
fromhrmresourcea,workflow_currentoperatorbwherea.id=b.useridgroupbya.lastnameorderby2desc
改成這個
⑥ oracle里的哪個函數等於sql的year函數急
sql中的year函數即取日期類型年份，在oracle中可用to_char函數。
sql中取當前時間的年份：
selectyear(getdate())
結果：
⑦ 求oracle sql函數清單以及使用說明
登錄這個網站可以查詢到你要的詳細清單
docs.oracle.com
下面是部分函數清單及簡要說明，僅供參考
Oracle SQL Functions
Functions:
ABS(n) Absolute value of number
ACOS(n) arc cosine of n
ADD_MONTHS(date,num_months)
Returns date + num_months
ASCII(char) Converts char into a decimal ascii code
ASIN(n) arc sine of n.
ATAN(n) arc tangent of n.
ATAN2(n.m) arc tangent of n and m.
AVG([DISTINCT]n)
Averge value of 'n' ignoring NULLs
BETWEEN value AND value
Where 'x' between 25 AND 100
BFILENAME('directory','filename')
Get the BFILE locator associated with a physical LOB binary file.
CASE Group the data into sub-sets.
CEIL(n) Round n up to next whole number.
CHARTOROWID(char) Converts a Char into a rowid value.
CHR(n) Character with value n
CONCAT(s1,s2) Concatenate string1 and string2
CONVERT(char_to_convert, new_char_set, old_char_set)
Convert a string from one character set to another.
COS(n) Cosine of number
COSH(n) Hyperbolic Cosine of number
COUNT(*) Count the no of rows returned
COUNT([DISTINCT] expr)
Count the no of rows returned by expr
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DECODE IF x THEN return y ELSE return z
DENSE_RANK Calculate the rank of a value in a group
DEREF(e) Return the object reference of argument e.
DUMP(expr,fmt[,start,length])
Convert to dec/hex/oct and display char set
EMPTY_BLOB Return an empty LOB locator (use to empty a column or variable)
EMPTY_CLOB Return an empty LOB locator (use to empty a column or variable)
EXISTS Return TRUE if a subquery returns at least one row
EXP(n) Exponential (e to 'n'th power)
EXTRACT Extract part of a DATE (Year,Month,Day,Second,etc)
FLOOR(n) Round n down to the next whole number.
GREATEST(expression, expression...)
Returns the largest in a list of expressions.
GROUPING Grouping for superaggregate rows=NULL
(see GROUP BY ROLLUP/CUBE)
HEXTORAW(char) Convert char containing hex digits to a raw value.
IN (list of comma separated values)
Effectively a shorthand for ['x' = y OR 'x' = z...] i.e.
Where 'x' IN ('sales','marketing','recruitment')
INITCAP(char) String with Initial Capitals
INSTR(str, chars[,s[,n]])
Find the 'n'th occurence of 'chars' in 'str'
Starting at position 's'
n and s default to 1
INSTRB (str, chars[,s[,n]])
The same as INSTR, except that 's' and the return value are expressed in bytes,
use for double-byte char sets.
IS NULL Check for NULL (empty) values (Select * from demo Where x IS NULL;)
IS NOT NULL Check for items that contain a value (Select * from demo Where x IS NOT NULL;)
LAST_DAY(date)Returns the last day of month in Date
LEAST(expression, expression...)
Returns the smallest in a list of expressions
LENGTH(char) Returns the number of characters in char
LENGTHB(char) Returns the number of bytes in char (use for double-byte char sets)
LIKE wildcard/value
Wildcards are [% = any chars] [ _ = any one char]
Where 'x' LIKE 'smith%' [will find 'Smithson']
Where 'x' LIKE 'smith_' [will find 'Smithy']
LN(n) Natural Log of n, where n>0
LOG(b,n) log of n, base b
LOWER(char) Returns character string in lowercase
LPAD(char, n[,PadChar])
Left Pad char with n spaces [or PadChars]
LTRIM(char[,set])
Left Trim char - remove leading spaces [or char set]
MAKE_REF(table,key)
Create a REF to a row of an OBJECT view/table
MAX([DISTINCT] expr)
Maximum value returned by expr
MIN([DISTINCT] expr)
Minimum value returned by expr
MOD(x,y) Remainder of x divided by y
MONTHS_BETWEEN(end_date, start_date)
Number of months between the 2 dates (integer)
NEW_TIME(date, zone1, zone2)
Convert between GMT and US time zones (but not CET)
NEXT_DAY(date,day_of_week)
'12-OCT-01','Monday' will return the next Mon after 12 Oct
NLS_CHARSET_DECL_LEN (bytecount,charset)
Returns the declaration width (no of chars) of an NCHAR column
NLS_CHARSET_ID(varchars)
Returns the char set ID given a charset name
NLS_CHARSET_NAME(charset_id)
Returns the char set name given a charset id
NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])
Returns char in Initial Caps, using an NLS sort_sequence
either the session default or specified directly
NLS_LOWER(char[,'NLS_SORT = sort_sequence'])
Returns char in lower case, using an NLS sort_sequence
either the session default or specified directly
NLSSORT(char[,'NLS_SORT = sort_sequence'])
Return the string of bytes used to sort char, using an NLS sort_sequence
either the session default or specified directly
NLS_UPPER(char[,'NLS_SORT = sort_sequence'])
Returns char in UPPER case, using an NLS sort_sequence
either the session default or specified directly
NVL(expression, value_if_null)
If expression is null, returns value_if_null; if expression is not null, returns expression.
The arguments can have any datatype (Oracle will perform implicit conversion where needed).
PERCENT_RANK Calculate the percent rank of a value in a group.
POWER(m,n) m raised to the nth power
RANK Calculate the rank of a value in a group
RAWTOHEX(raw) Convert raw to a character value containing its hex equivalent
REF(table_alias)
Returns a REF value for an object instance (bound to the variable or row.)
The table alias (correlation variable) is associated with
one row of an object table or an object view in an SQL statement.
REFTOHEX(ref) Convert ref (object type) to a char value containing its hex equivalent.
REPLACE(char, search_str[, replace_str])
ANSI alternative to decode() Replace every occurrence of search_str
with replace_str, replace_str defaults to null.
ROUND(n,d) n rounded to d decimal places (d defaults to 0)
ROUND(date,fmt)
date rounded to fmt
ROWIDTOCHAR(rowid)
Convert a rowid value to VARCHAR2
ROW_NUMBER Assign a unique number to each row of results.
RPAD(char, n[,PadChar])
Right Pad char with n spaces [or PadChars]
RTRIM(char[,set])
Right Trim char - remove trailing spaces [or char set]
SIGN(n) positive = 1, zero = 0, negative = -1
SIN(n) Sine of n in Radians.
SINH(n) Hyperbolic Sine of n in Radians.
SOUNDEX(char) Returns a char value representing the sound of the words.
SQRT(n) Square Root (returns NULL for negative no's)
STDDEV([DISTINCT] n)
Standard deviation of n.
SUBSTR(char, s[,l])
A substring of char, starting at character s, length l.
SUBSTRB(char, s[,l])
A substring of char, starting at character s, length l
The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes,
use for double-byte char sets.
SUM([DISTINCT] n)
Sum of values of n, ignoring NULLs
SYS_CONTEXT('namespace','attribute_name')
Examine the package associated with the context namespace
Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR
NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER
CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID, CURRENT_SCHEMA
note: CURRENT_USER may be different from SESSION_USER within a stored procere
(e.g an invoker-rights procere).
SYS_CONTEXT ('USERENV','IP_ADDRESS')
SYS_GUID() Returns a globally unique identifier (16 byte RAW value)
SYSDATE The current system date & time
TAN(n) Tangent of n in Radians
TANH(n) Hyperbolic tangent of n in Radians
TO_BLOB(Raw_col) Convert LONG RAW and RAW values to BLOB
TO_CHAR Convert to a character String
TO_CLOB Convert character or NCLOB values to the database character set.
TO_DATE Convert to date value
TO_LOB(long) Convert LONG values to CLOB or NCLOB values
or convert LONG RAW values to BLOB values
TO_MULTI_BYTE(char)
Convert single-byte char to multi-byte char
TO_NCHAR(expr) Convert a TEXT expression, date, or number to NTEXT in a specified format.
Mostly used to format output data.
TO_NCLOB Convert any character string (including LOBs) to the national character set.
TO_NUMBER Convert to numeric format
TO_SINGLE_BYTE(char)
Convert multi-byte char to single-byte character.
TO_TIME Convert to time value
TO_TIME_TZ Convert to time zone
TO_TIMESTAMP Convert to timestamp
TO_TIMESTAMP_TZ
TO_YMINTERVAL Convert a character string to an INTERVAL YEAR TO MONTH type
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str
unlike REPLACE() if replace_str is NULL the function returns NULL
TRANSLATE (text USING charset)
Convert text into a specific character set
Use this instead of CONVERT() if either the input or output datatype
is NCHAR or NVARCHAR2.
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)
Return trim_source as a VARCHAR2 with leading/trailing items removed
trim_char defaults to a space ' ' but may be numeric or char 'A'
TRUNC(i,d) Truncate i to d decimal places (d defaults to 0)
TRUNC(date,fmt) Truncate Date to nearest fmt.
UID User id (a unique number)
UPPER(char) Return characters in uppercase
USER Return the current Username
USERENV('option')
Can return any of the options: ENTRYID, SESSIONID,
TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO
VALUE(correlation_variable)
Return the object instance for a row of an object table
as associated with the correlation_variable (table alias)
VARIANCE([DISTINCT] n)
Variance of n, ignoring NULLs
VSIZE(expr) Value Size, returns the number of bytes used by each row of expr.
Examples
Return the left 4 characters from the column prod_code, like a left() function in other languages:
SQL> select substr(prod_code,1,4) from sales;
Return the right 3 characters from the column prod_code, like a right() function in other languages:
SQL> select substr(prod_code,-3) from sales;
Return the leftmost 2 digits of idnum:
SQL> select substr(to_char(idnum),1,2) from mytable;
This page is not an exhaustive list of all the functions available - to find a complete list of functions for a particular release of Oracle see docs.oracle.com or run this query:
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';
⑧ 求ORACLE SQL下的日期函數
1. 日期和字元轉換函數用法（to_date,to_char）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from al; //日期轉化為字元串
select to_char(sysdate,'yyyy') as nowYear from al; //獲取時間的年
select to_char(sysdate,'mm') as nowMonth from al; //獲取時間的月
select to_char(sysdate,'dd') as nowDay from al; //獲取時間的日
select to_char(sysdate,'hh24') as nowHour from al; //獲取時間的時
select to_char(sysdate,'mi') as nowMinute from al; //獲取時間的分
select to_char(sysdate,'ss') as nowSecond from al; //獲取時間的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from al// 2.
select to_char( to_date(222,'J'),'Jsp') from al
顯示Two Hundred Twenty-Two 3.求某天是星期幾
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from al;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from al;
monday
設置日期語言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以這樣
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 4. 兩個日期間的天數
select floor(sysdate - to_date('20020405','yyyymmdd')) from al; 5. 時間為null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from al;
注意要用TO_DATE(null) 6.月份差
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那麼12月31號中午12點之後和12月1號的12點之前是不包含在這個范圍之內的。
所以，當時間需要精確的時候，覺得to_char還是必要的
7. 日期格式沖突問題
輸入的格式要看你安裝的ORACLE字元集的類型, 比如: US7ASCII, date格式的類型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中寫
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from al;
注意我這只是舉了NLS_DATE_LANGUAGE，當然還有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS 8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not in ( '1', '7' )
查找2002-02-28至2002-02-01間除星期一和七的天數
在前後分別調用DBMS_UTILITY.GET_TIME, 讓後將結果相減(得到的是1/100秒, 而不是毫秒). 9. 查找月份
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
10. Next_day的用法
Next_day(date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D 11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一條記錄的TIME 與最後一行是一樣的
可以建立一個函數來處理這個問題
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hh:mi:ss') from all_objects;
12.獲得小時數
extract()找出日期或間隔值的欄位值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from al;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from al;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
13.年月日的處理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from (
select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
from emp
)
) 14.處理月份天數不定的辦法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from al 16.找出今年的天數
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from al 閏年的處理方法
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
如果是28就不是閏年 17.yyyy與rrrr的區別
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001 18.不同時區的處理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from al; 19.5秒鍾一個間隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from al 2002-11-1 9:55:00 35786
SSSSS表示5位秒數 20.一年的第幾天
select TO_CHAR(SYSDATE,'DDD'),sysdate from al
310 2002-11-6 10:03:51 21.計算小時,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from al
)
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
//
floor((date2-date1) /365) 作為年
floor((date2-date1, 365) /30) 作為月
d(mod(date2-date1, 365), 30)作為日. 23.next_day函數 返回下個星期的日期,day為1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是從當前開始下一個星期五。後面的數字是從星期日開始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
---------------------------------------------------------------
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dal
日期 返回的是天 然後 轉換為ss
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from al 25,trunc[截斷到最接近的日期,單位為天] ,返回的是日期類型
select sysdate S1,
trunc(sysdate) S2, //返回當前日期,無時分秒
trunc(sysdate,'year') YEAR, //返回當前年的1月1日,無時分秒
trunc(sysdate,'month') MONTH , //返回當前月的1日,無時分秒
trunc(sysdate,'day') DAY //返回當前星期的星期天,無時分秒
from al 26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from al 27.計算時間差
注:oracle時間差是以天數為單位,所以換算成年月,日
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from al //時間差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from al //時間差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from al //時間差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from al //時間差-時
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from al //時間差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from al //時間差-秒 28.更新時間
注:oracle時間加減是以天數為單位,設改變數為n,所以換算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from al //改變時間-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from al //改變時間-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from al //改變時間-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from al //改變時間-時
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from al //改變時間-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from al //改變時間-秒 29.查找月的第一天,最後一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM al;