別紙: 組み込み関数対応表(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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.