別紙： 組み込み関数対応表（Oracle-PostgreSQL）

[Pages:7] Oracle-PostgreSQL

Oracle ABS(n) ACOS(n) ASIN(n) ATAN(n) ATAN2(n,m) BITAND(expr1, expr2) CEIL(n) COS(n) COSH(n) EXP(n) FLOOR(n) LN(n) LOG(m, n) MOD(m, n) NANVL(n2, n1) POWER(m, n) REMAINDER(n2, n1) ROUND(n, m) SIGN(n) SIN(n) SINH(n) SQRT(n) TAN(n) TANH(n) TRUNC(n, m) WIDTH_BUCKET(expr,min_value, max_value, num_buckets) CHR(n) CONCAT(char1, char2) INITCAP(char)

n n n n n m expr1, expr2AND n n n e= 2.71828183 ... n n n m n m n n2n1n2 m n n2 n1 n m n n n n n n n m

n

LOWER(char)

LPAD(char1, n, char2)

char1 char2 n

LTRIM(char, set)

charset

NCHR(number)

number

NLS_INITCAP(char, 'nlsparam')

NLS_LOWER(char, 'nlsparam')

NLSSORT(char, 'nlsparam')

NLS_UPPER(char, 'NLS_param = param_value')

char

REGEXP_REPLACE(string, pattern[, replace[, pos[, occurrence[, match]]]]])

REPLACE

REGEXP_SUBSTR(source_char, pattern[, position[, occurrence[, match_param[, subexpr]]]])

SUBSTR

PostgreSQL

orafce

abs(n)

acos(n)

asin(n)

atan(n)

atan2(n, m)

&

orafcebitand

ceil(n)

cos(n)

(exp(n) + exp(-n)) / 2

orafcecosh

exp(n)

floor(n)

ln(n)

log(m, n)

mod(m, n)

nanvl (n2, n1)

power(m, n)

n1 - n2 * ROUND ( n1 / n2 )

round(n, m)

sign(n)

sin(n)

(exp(n)-exp(-n))/2

orafcesinh

sqrt(n)

tan(n)

(exp(n)-exp(-n))/(exp(n)+exp(-n))

orafcetanh

trunc(n, m)

width_bucket(op numeric, b1 numeric, b2numeric, coun t int)

chr(n)

concat(char1, char2)

initcap(char)

lower(char)

lpad(char1, n, char2)

ltrim(char, set)

? ? ? ?

?

regexp_replace(string text, pattern text, replacement text [, flags text])

regexp_matches(string text,pattern text [, flags tex t])

Oraclecharchar PostgreSQLcharactertext

PostgreSQL 2integernumeric

Oraclecharchar PostgreSQLcharactertext

1

 Oracle-PostgreSQL

REPLACE(char, search_string, replacement_string)

RPAD(char1, n, char2)

replacement_string search_string

replace(char, search_string, replacement_string)

char1 char2 n

rpad(char1, n, char2)

RTRIM(char, set)

char set char

rtrim(char, set)

SOUNDEX(char)

SUBSTR(char, m, n)

TRANSLATE(char, from, to) TREAT(expr AS type) TRIM([LEADING|TRAILING|BOTH] [ trim_character] FROM trim_source)

char char m n from to char

?

substr(char, m, n)

translate(char, from, to)

?

trim([leading | trailing | both] [characters] from string)

UPPER(char)

char

upper(char)

ASCII(char)

char 10

ascii(char)

INSTR(string, substring)

substring

strpos(string, substring)

LENGTH(char)

char

length(char)

REGEXP_COUNT (source_char, pattern [, position [, match_param]])

REGEXP_INSTR

?

REGEXP_INSTR ( string , pattern

[ , pos [, occurrence [, offset [,

INSTR

?

match]]]] )

NLS

NLS_CHARSET_DECL_LEN(bytecnt, csid)

NCHAR

?

NLS_CHARSET_ID(text)

NLS text NLS ID

?

NLS_CHARSET_NAME(n)

ID n NLS

?

ADD_MONTHS (date, integer)

dateinteger

: select date '2013-03-22' + interval '1 months'

CURRENT_DATE

current_datecurrent_timestamp

CURRENT_TIMESTAMP

TIMESTAMP WITH TIME ZONE

current_timestamp

DBTIMEZONE

?

EXTRACT (element FROM date)

extract(field from timestamp)

FROM_TZ(timestamp, time_zone_value)

TIMESTAMP WITH TIME ZONE

?

LAST_DAY(d)

d

last_date(date)

LOCALTIMESTAMP

TIMESTAMP localtimestamp

MONTHS_BETWEEN(d1, d2)

d1 d2

months_between(d1, d2)

NEW_TIME(d, z1, z2)

z1dz2

?

NEXT_DAY(d, char)

chard

next_day(date, text)

NUMTODSINTERVAL(n, 'char_expr')

n INTERVAL DAY TO SECOND

?

NUMTOYMINTERVAL(n, 'char_expr')

n INTERVAL YEAR TO MONTH

?

ROUND(d, fmt)

d fmt

round(date, text)

SESSIONTIMEZONE

?

SYS_EXTRACT_UTC(datetime_with_timezone) (UTC)

?

SYSDATE SYSTIMESTAMP TRUNC(d, fmt)

fmt

current_datecurrent_timestampclock_timestamp

current_timestampclock_timestamp

date_trunc(text, timestamp)

2

PostgreSQL 2integernumeric

Oraclecharchar PostgreSQLcharactertext

contribfuzzystrmatch

2 orafce

Oraclecharchar PostgreSQLcharactertext

orafceadd_months

orafcetrunc

 Oracle-PostgreSQL

TZ_OFFSET(char)

?

GREATEST(expr)

expr

greatest(expr)

LEAST(expr)

expr

least(expr)

ASCIISTR(char)

char

to_ascii(string text)

BIN_TO_NUM(expr)

?

CAST(expr AS type_name)

CAST ( expression AS type )

CHARTOROWID(char)

ROWID

?

COMPOSE(char)

CONVERT(char, dest_char_set, source_char_set)

DECOMPOSE(string)

Unicode Unicode

?

convert(string bytea,src_encoding name,dest_encoding name)

?

HEXTORAW(char)

16 char RAW

?

NUMTODSINTERVAL(n, 'char_expr')

n INTERVAL DAY TO SECOND

?

NUMTOYMINTERVAL(n, 'char_expr')

n INTERVAL YEAR TO MONTH

?

RAWTOHEX(raw)

raw16

?

RAWTONHEX(raw)

raw16TO_NCHAR(RAWTOHEX(raw))

?

ROWIDTOCHAR(rowid)

ROWID VARCHAR2

?

ROWIDTONCHAR(rowid)

ROWID NVARCHAR2

?

SCN_TO_TIMESTAMP(number)

(SCN System Change Number)

?

TIMESTAMP_TO_SCN(timestamp)

(SCN)

?

TO_BINARY_DOUBLE ( expr [, format [, nls_param]] )

?

TO_BINARY_FLOAT ( expr [, format [, nls_param]] )

?

TO_BLOB(raw_value)

LONG RAWRAWBLOB

?

TO_CHAR(nchar|clob|nclob)

NCHARNVARCHAR2CLOBNCLOB VARCHAR2

?

TO_CHAR(d, fmt)

DATE

d

fmt

VARCHAR2

to_char(d,

fmt)

TO_CHAR(n, fmt)

NUMBER

n

fmt

VARCHAR2

to_char(n,

fmt)

TO_CLOB(lob_column)

LOBNCLOBCLOB

?

TO_DATE(char, fmt)

CHARVARCHAR2charDATE to_date(char, fmt)

TO_DSINTERVAL(char)

CHARVARCHAR2NCHARNVARCHAR2INTERVAL DAY TO SECOND

?

TO_LOB(long_column)

LONGLONG RAW LOB

?

TO_MULTI_BYTE(char)

?

TO_NCHAR(char[, format[, nls_param]])

CHARVARCHAR2CLOBNCLOB

?

TO_NCHAR(date[, format[, nls_param]])

?

TO_NCHAR(n[, format[, nls_param ]])

?

TO_NCLOB(lob_column)

LOBCLOBNCLOB

?

TO_NUMBER(char, fmt)

fmtcharNUMBER

to_number(char, fmt)

TO_SINGLE_BYTE(char)

?

TO_TIMESTAMP(char, fmt)

CHARVARCHAR2NCHARNVARCHAR2TIMESTAMP

to_timestamp(text,

text)

TO_TIMESTAMP_TZ(char, fmt)

CHARVARCHAR2NCHARNVARCHAR2charTIMESTAMP WITH TIME ZONE

to_timestamp(text, text)

TO_YMINTERVAL(char)

CHARVARCHAR2NCHARNVARCHAR2INTERVAL YEAR TO MONTH

?

TRANSLATE(text USING CHAR_CS | NCHAR_CS)

text

?

3

LATIN1LATIN2LATIN9WIN1250

OraclePostgreSQL

 Oracle-PostgreSQL

UNISTR(string) BFILENAME('directory', 'filename') EMPTY_BLOB() | EMPTY_CLOB()

LOBBFILE LOB

DECODE(expr, search, result)

DUMP(expr)

ORA_HASH(expr[, max_bucket[, seed_value]]) VSIZE(expr)

search1result

case

expr VARCHAR2

expr

NULL

COALESCE (expr_list)

NULLexpr

coalesce(expr_list)

?

? ?

orafcedecode

?

? ?

LNNVL(condition)

FALSEUNKNOWNTRUETRUEFALSE lnnvl (boolean)

NULLIF(expr1, expr2)

expr1expr2NULLexpr1

NULLIF(value1, value2)

NVL(expr1, expr2)

expr1NULLexpr2NULLexpr1

coalesce(expr1, expr2)

NVL2(expr1, expr2, expr3)

expr1NULLexpr2NULLexpr3

coalesce(expr1, expr2, expr3)

AVG(expr)

expr

avg(expr)

COLLECT([DISTINCT | UNIQUE] column [ORDER BY expr])

?

CORR(expr1, expr2)

corr(expr1, expr2)

COUNT(expr)

count(expr)

COVAR_POP(expr1, expr2)

covar_pop(expr1, expr2)

COVAR_SAMP(expr2, expr2)

covar_samp(expr1, expr2)

CUME_DIST() OVER( ORDER_BY_clause)

cume_dist()

DENSE_RANK() OVER ( ORDER_BY_clause)

dense_rank()

FIRST

?

GROUP_ID

GROUP BY

?

GROUPING(expr)

ROLLUP CUBE GROUP BY SELECT

?

GROUPING_ID(expr[, expr]...)

GROUPING

?

LAST

?

LISTAGG (expr[, delimiter] ) WITHIN GROUP (order_by)

ORDER BY

?

MAX(expr)

expr

max(expr)

MEDIAN(expr)

?

MIN(expr)

expr

min(expr)

PERCENT_RANK() OVER (ORDER_BY_clause)

R R1 1

percent_rank()

PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr[DESC|ASC]) [OVER (query_partition_clause)]

?

PERCENTILE_DISC(expr) WITHIN GROUP

(ORDER BY expr[DESC|ASC])

?

[OVER (query_partition_clause)]

RANK() OVER (ORDER_BY_clause)

rank()

REGR_SLOPE

REGR_INTERCEPT

REGR_COUNT

REGR_R2

REGR_AVGX

?

REGR_AVGY

REGR_SXX

REGR_SYY

REGR_SXY

4

 Oracle-PostgreSQL

STATS_BINOMIAL_TEST

2

?

STATS_CROSSTAB

2

?

STATS_F_TEST

2

?

STATS_KS_TEST

2 Kolmogorov-Smirnov

?

STATS_MODE

?

STATS_MW_TEST

Mann-Whitney

?

STATS_ONE_WAY_ANOVA

?

STATS_T_TEST_*

t

?

STATS_WSR_TEST

?

STDDEV(expr)

expr

stddev(expr)

STDDEV_POP(expr)

stddev_pop(expr)

STDDEV_SAMP(expr)

stddev_samp(expr)

SUM(expr)

expr

sum(expr)

SYS_XMLAGG(expr[, fmt])

exprXMLXMLXML

xmlagg(expression)

VAR_POP(expr)

NULL

var_pop(expr)

VAR_SAMP(expr)

NULL

var_samp(expr)

VARIANCE(expr)

expr

variance(expr)

XMLAGG(XMLType_instance [ order_by_clause ])

XMLXML

xmlagg(expression)

LAG(value_expr, offset, default)

lag(value_expr, offset, default)

FIRST_VALUE(expr)

first_value(expr)

LAST_VALUE(expr)

last_value(expr)

LEAD(value_expr, offset, default)

lead(value_expr, offset, default)

NTH_VALUE (measure_expr, n) OVER (analytic_clause)

analytic_clausenmeasure_expr

?

NTILE(expr)

expr

ntile(expr)

RATIO_TO_REPORT(expr) OVER ()

?

ORDER_BY_clause 1

ROW_NUMBER() OVER (ORDER_BY_clause)

row_number()

CARDINALITY

?

COLLECT

?

POWERMULTISET

?

POWERMULTISET_BY_CARDINALITY

?

SET

?

SYS_CONNECT_BY_PATH

CONNECT BYchar

?

CLUSTER_ID

?

CLUSTER_PROBABILITY

?

CLUSTER_SET

?

FEATURE_ID

?

FEATURE_SET

VARRAY

?

FEATURE_VALUE

?

5

PREDICTION PREDICTION_BOUNDS PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET XML APPENDCHILDXML DELETEXML DEPTH EXISTSNODE

EXTRACTXML

EXTRACTVALUE INSERTCHILDXML INSERTCHILDXMLAFTER INSERTCHILDXMLBEFORE INSERTXMLAFTER INSERTXMLBEFORE PATH SYS_DBURIGEN SYS_XMLAGG

SYS_XMLGEN

UPDATEXML XMLAGG XMLCAST XMLCDATA XMLCOLATTVAL XMLCOMMENT XMLCONCAT XMLDIFF

XMLELEMENT

XMLEXISTS

XMLFOREST

XMLISVALID XMLPARSE XMLPI XMLQUERY

XMLROOT XMLSEQUENCE XMLSERIALIZE XMLTABLE

Oracle-PostgreSQL

?

LOWERUPPER2NUMBER

?

?

XML

?

?

VARRAY

?

XPathXML

?

XMLXPath

?

UNDER_PATH

?

XML

?

VARCHAR2XPathXMLXMLType

?

XMLTypeXPath

?

XPathXML

?

1

?

XPathXML

?

?

XPathXML

?

xpath(xpath, xml [, nsarray]) xpath_exists(xpath, xml [, nsarray])

DBUriTypeURL

?

exprXMLXMLXML

?

XMLXMLType

?

XMLTypeXPath XMLType

?

XMLXML

xmlagg(xml)

value_expressiondatatypeSQL

?

value_exprCDATA

?

XMLXMLcolumnname XML

?

value_exprXML

xmlcomment(text)

XMLType

xmlconcat(xml[,

...])

2XMLXdiffXML

?

identifierEVALNAME value_expr

xmlelement(name attname] [, ...

name [, xmlattributes(value ])] [, content, ...])

[AS

XQueryXQuery

XMLEXISTS(text PASSING [BY REF] xml [BY REF])

XMLXML

xmlforest(content [AS name] [, ...])

XMLType_instanceXML

?

value_exprXML

XMLPARSE ( { DOCUMENT | CONTENT } value)

identifiervalue_exprXML

xmlpi(name target [, content])

XQueryXQuery

?

XMLXML xmlroot(xml, version text | no value [, standalone

XML

yes|no|no value])

?

value_exprLOB

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

XQuery

?

6

 Oracle-PostgreSQL

XMLTRANSFORM

XMLTypeXSL

?

SYS_CONTEXT('namespace', 'attribute') namespace attribute

?

SYS_GUID()

16

?

SYS_TYPEID(object_type_value)

ID

?

UID

?

USER

user,current_user

USERENV(option)

?

DEREF expr expr

DEREF(expr)

REF

?

REF ID

MAKE_REF(table, key)

MAKE_REF REF

?

SQL REF

REF(correlation_variable)

?

REF

REFTOHEX(expr)

REFTOHEX expr 16

?

SQL VALUE

VALUE(correlation_variable)

?

CV

?

ITERATION_NUMBER

?

PRESENTNNV

cell_referencemodel_clausePRESENTNNVNULL expr1expr2

?

PRESENTV

cell_referencemodel_clauseexpr1 expr2

?

PREVIOUS

cell_reference

?

OLAP

CUBE_TABLE

SQL 2

?

DATAOBJ_TO_PARTITION

ID

?

7

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download