oracle与sql的区别（常用函数）

许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle SQL Server DB )的应用程序的重要性 它可以让客户们选择自己习惯的平台 一般来说 软件开发人员都能够识别出他们的负责数据库维护的客户 和必须使用现有平台和个性化的客户

关于Oracle和SQL Server之间的区别 已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别 在这篇文章里面 我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别 并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法 与此同时 我不会再论讨对于应用程序来说 两个平台之间显而易见的区别 例如表的分割和索引

定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库 想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的 这是一家志愿者成员的组织(用私人基金运转) 他们在有关设备和程序等广泛领域内开发了国家承认的标准 在数据库领域 ANSI定义了编写SQL命令的标准 假设命令可以运行在任何的数据库上 而不需要更改命令的语法

ODBC是开放数据库连接(ODBC)接口 微软定义的 它可以让应用程序访问数据库管理系统(DBMS)中的数据 使用SQL作为访问数据的标准 ODBC允许最大的互联性 这意味着一个单个的应用程序可以访问不同的数据库管理系统 然后 应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上 OLE DB是ODBC的继承者 是一组允许例如基于VB C++ Access等任何可以连接到类似SQL Server Oracle DB MySQL等后台的 前台 的软件组件 在许多种情况下 OLE DB组件都比原有的ODBC提供了好得多的性能

JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库 SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间 进行不依赖于数据库的连接的行业标准 JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口 真实世界中的通用接口不幸的是 并不是所有数据库级别的命令都是ANSI 每个数据库平台都有自己的扩展功能 对于ANSI或者通用接口 一般来说都代表着几本功能 因此也可能意味着丧失了性能方面的竞争力 对于小型数据库和小型应用程序来说 要维护对数据库的通用访问是简单的 但是当数据库和/或应用程序变得越来越大 越来越复杂 你就不得不向代码中添加功能

SQL Server和Oracle的常用函数对比

数学函数

绝对值

S:select abs( ) value

O:select abs( ) value from dual

取整(大) S:select ceiling( ) value O:select ceil( ) value from dual

取整（小）

S:select floor( ) value O:select floor( ) value from dual

取整（截取）

S:select cast( as int) value O:select trunc( ) value from dual

四舍五入S:select round( ) value O:select round( ) value from dual

e为底的幂S:select Exp( ) value O:select Exp( ) value from dual

取e为底的对数S:select log( ) value O:select ln( ) value from dual;

取 为底对数

S:select log ( ) value

O:select log( ) value from dual;

取平方

S:select SQUARE( ) value

O:select power( ) value from dual

取平方根

S:select SQRT( ) value

O:select SQRT( ) value from dual

求任意数为底的幂S:select power( ) value O:select power( ) value from dual

取随机数

S:select rand() value

O:select sys dbms_random value( ) value from dual;

取符号

S:select sign( ) value

O:select sign( ) value from dual

三角函数相关

圆周率S:SELECT PI() value

O:不知道

sin cos tan 参数都以弧度为单位

例如 select sin(PI()/ ) value得到 （SQLServer）

Asin Acos Atan Atan 返回弧度

弧度角度互换(SQLServer Oracle不知道)

DEGREES 弧度 〉角度

RADIANS 角度 〉弧度

数值间比较

求集合最大值

S:select max(value) value from

(select value

union

select value

union

select value

union

select value)a

O:select greatest( ) value from dual

求集合最小值

S:select min(value) value from

(select value

union

select value

union

select value

union

select value)a

O:select least( ) value from dual

如何处理null值(F 中的null以 代替) S:select F IsNull(F ) value from Tbl O:select F nvl(F ) value from Tbl

字符串函数

求字符序号

S:select ascii( a ) value

O:select ascii( a ) value from dual

从序号求字符

S:select char( ) value

O:select chr( ) value from dual

连接

S:select + + value

O:select CONCAT( )|| value from dual

子串位置 返回 S:select CHARINDEX( s sdsq ) value O:select INSTR( sdsq s ) value from dual

模糊子串的位置 返回 参数去掉中间%则返回 // 本文转自 C++Builder 研究 i= &d=dwn rn S:select patindex( %d%q% sdsfasdqe ) value O:oracle没发现 但是instr可以通过第四个参数控制出现次数select INSTR( sdsfasdqe sd ) value from dual 返回

求子串S:select substring( abcd ) value O:select substr( abcd ) value from dual

子串代替 返回aijklmnef S:SELECT STUFF( abcdef ijklmn ) value O:SELECT Replace( abcdef bcd ijklmn ) value from dual

子串全部替换

S:Replace

O:select Translate( fasdbfasegas fa 我 ) value from dual

长度S:len datalength

O:length

大小写转换 lower upper

单词首字母大写

S:没发现

O:select INITCAP( abcd dsaf df ) value from dual

左补空格（LPAD的第一个参数为空格则同space函数）

S:select space( )+ abcd value

O:select LPAD( abcd ) value from dual

右补空格（RPAD的第一个参数为空格则同space函数）

S:select abcd +space( ) value

O:select RPAD( abcd ) value from dual

删除空格S:ltrim rtrim O:ltrim rtrim trim

重复字符串S:select REPLICATE( abcd ) value

O:没发现

发音相似性比较(这两个单词返回值一样 发音相同) S:SELECT SOUNDEX ( Smith ) SOUNDEX ( Smythe ) O:SELECT SOUNDEX ( Smith ) SOUNDEX ( Smythe ) from dual SQLServer中用SELECT DIFFERENCE( Smithers Smythers ) 比较soundex的差返回 为同音 最高

日期函数

系统时间

S:select getdate() value

O:select sysdate value from dual

前后几日

直接与整数相加减

求日期S:select convert(char( ) getdate() ) value

O:select trunc(sysdate) value from dual

select to_char(sysdate yyyy mm dd ) value from dual

求时间S:select convert(char( ) getdate() ) value O:select to_char(sysdate hh :mm:ss ) value from dual

取日期时间的其他部分

S:DATEPART和DATENAME函数（第一个参数决定）

O:to_char函数第二个参数决定

参数 下表需要补充

year yy yyyy quarter qq q (季度) month mm m (m O无效) dayofyear dy y (O表星期) day dd d (d O无效) week wk ww (wk O无效)

weekday dw (O不清楚)

Hour hh hh hh (hh hh S无效) minute mi n (n O无效) second ss s (s O无效)

millisecond ms (O无效)

当月最后一天

S:不知道

O:select LAST_DAY(sysdate) value from dual

本星期的某一天（比如星期日）

S:不知道

O:SELECT Next_day(sysdate ) vaule FROM DUAL;

字符串转时间

S:可以直接转或者select cast( as datetime) value

O:SELECT To_date( : : yyyy mm dd hh mi ss ) vaule FROM DUAL;

求两日期某一部分的差（比如秒）

S:select datediff(ss getdate() getdate()+ ) value O:直接用两个日期相减（比如d d = ）

SELECT (d d ) vaule FROM DUAL;

根据差值求新的日期（比如分钟）

S:select dateadd(mi getdate()) value

O:SELECT sysdate+ / / vaule FROM DUAL;

求不同时区时间

S:不知道

O:SELECT New_time(sysdate ydt gmt ) vaule FROM DUAL;

时区参数 北京在东 区应该是Ydt

AST ADT大西洋标准时间

BST BDT白令海标准时间

CST CDT中部标准时间

EST EDT东部标准时间

GMT格林尼治标准时间

HST HDT阿拉斯加—夏威夷标准时间

MST MDT山区标准时间

NST纽芬兰标准时间

PST PDT太平洋标准时间

YST YDT YUKON标准时间

Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:

函数Oracle Microsoft SQL Server

把字符转换为ASCII ASCII ASCII

字串连接CONCAT (expressiοn + expressiοn)

把ASCII转换为字符CHR CHAR

返回字符串中的开始字符（左起）INSTR CHARINDEX

把字符转换为小写LOWER LOWER

把字符转换为大写UPPER UPPER

填充字符串的左边LPAD N/A

清除开始的空白LTRIM LTRIM

清除尾部的空白RTRIM RTRIM

字符串中的起始模式（pattern）INSTR PATINDEX

多次重复字符串RPAD REPLICATE

字符串的语音表示SOUNDEX SOUNDEX

重复空格的字串RPAD SPACE

从数字数据转换为字符数据TO_CHAR STR

子串SUBSTR SUBSTRING

替换字符REPLACE STUFF

将字串中的每个词首字母大写INITCAP N/A

翻译字符串TRANSLATE N/A

字符串长度LENGTH DATELENGTH or LEN

列表中最大的字符串GREATEST N/A

列表中最小的字符串LEAST N/A

lishixinzhi/Article/program/Oracle/201311/16561

登录这个网站可以查询到你要的详细清单

docsoraclecom

下面是部分函数清单及简要说明，仅供参考

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(nm) 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] ie

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 procedure

(eg an invoker-rights procedure)

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 docsoraclecom or run this query:

SELECT distinct object_name

FROM all_arguments

WHERE package_name = 'STANDARD';

rank() over（partition by sub_name order by score desc） 属于非连续排名（非连续排名就是指，有两个并列第一的话，它的排序是1,1,3，连续排名则为1,1,2） partition by是分组的意思 ，order by 是排序， desc 是降序，asc是升序 select scs_id,scs_name,sub_name,scscore, rank() over (order by score desc) 名次 from t_score sc where sub_name='Oracle' S_ID S_NAME SUB_NAME SCORE 名次 4 杨过 Oracle 7700 1 2 李四 Oracle 7700 1 3 张三丰 Oracle 000 3

自己写oracle function吧。俺写了一半不想写了。居然还没有悬赏分！！！

create or replace function Num2CN(num in varchar) return varchar is

Result varchar (2);

begin

case num

when '1' then

Result:='一';

when '2' then

Result:='二';

when '3' then

Result:='三';

when '4' then

Result:='四';

when '5' then

Result:='五';

when '6' then

Result:='六';

when '7' then

Result:='七';

when '8' then

Result:='八';

when '9' then

Result:='九';

when '0' then

Result:='零';

end case;

return(Result);

end Num2CN;

create or replace function getunit(num in number) return varchar is

Result varchar(1);

begin

case num

when '1' then

Result:='';

when '2' then

Result:='十';

when '3' then

Result:='百';

when '4' then

Result:='千';

end case;

return(Result);

end getunit;

create or replace function getBunit(num in number) return varchar is

Result varchar(1);

begin

if num>8 then

Result:='亿';

elsif num>4 then

Result:='万';

end if;

return(Result);

end getBunit;

CREATE OR REPLACE FUNCTION convertandoutput(str IN VARCHAR2) RETURN INTEGER IS

v_result INTEGER;

DECLARE

str_length number;

Bunit varchar(1);

unit varchar(1);

cn varchar(1);

tmp number;

BEGIN

str_length:=LENGTH(str);

tmp:=str_length;

while(str_length>0)

if str_length>8 and tmp=8 then

Bunit:=GETBUNIT(str_length);

v_result:=concat(v_result,Bunit);

elsif str_length>8 and tmp =4 then

Bunit:=GETBUNIT(str_length);

v_result:=concat(v_result,Bunit);

elsif tmp>0 then

cn:= NUM2CN(substr(str,1,1);

unit:=GETUNIT(tmp);

str:=substr(str,2);

v_result

tmp:=tmp-1;

end while;

IF str_length<5 THEN

dbms_outputput_line(Num2Cn(substr(str,1,1))||'千' );

RETURN 1;

EXCEPTION

WHEN OTHERS THEN

RETURN 0;

END isnumeric;

主处理没写完。。。

以上就是关于oracle与sql的区别（常用函数）全部的内容，包括:oracle与sql的区别（常用函数）、求oracle sql函数清单以及使用说明、oracle rank函数怎么用等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！