HANA SQL reference and Oracle comparison 03/26 Update SLTechnology News&Howtos

HANA SQL reference and Oracle comparison

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Shulou(Shulou.com)06/02 Report--

HANA SQL reference and Oracle comparison

In this part, we mainly refer to "SAP_HANA Database SQL reference Manual" and "ORACLE sql reference Manual", and compare the sql mode, built-in functions and keywords that we use every day.

1. Commonly used SQL writing method

1.1. Recursive query

SQL usage:

Recursive query is used to query the data table of the parent-child node structure to form a tree-structured data set, which is often used in menu data sets and report structure data.

ORACLE

HANA

Select * from

Tf_rpt_hn_stat_bshzqk t

Where t.deptdistribuid = '10262200'

Start with t.deptroomid= '10000000'

Connect by prior t.dept_id=t.dept_mng_id

Ord er siblings by t.detp_id

Select * from "tf_rpt_hn_stat_bshzqk" t

Where t.deptdistribuid = '10262200'

Start with t.deptroomid= '10000000'

Connect by prior t.dept_id=t.dept_mng_id

Ord er by t.detp_id

Comparative description:

Start with of Oracle and hana... The connect by prior statement is consistent, and the conditional statement is also a recursive query followed by where filtering

In addition to the above, there are no level and siblings keywords in HANA, and there is no additional extension like oracle.

SQL usage:

The vertical to horizontal query is used to convert the data of the vertical columns in the data table into horizontal display.

ORACLE

HANA

Select t.issue

T.dept_id

T.obj_add_id

Max (decode (t.zbdm, 'FDCYXQK_0001')

T.zbz, null)) A

Max (decode (t.zbdm, 'FDCYXQK_0002')

T.zbz, null)) B

Max (decode (t.zbdm, 'FDCYXQK_0003')

T.zbz, null) C

From tf_rpt_item_data_fdsc t

Group by t.issue,t.dept_id,t.obj_add_id

Select t.issue

T.dept_id

T.obj_add_id

Max (map (t.zbdm, 'FDCYXQK_0001')

T.zbz, null)) A

Max (map (t.zbdm, 'FDCYXQK_0002')

T.zbz, null)) B

Max (map (t.zbdm, 'FDCYXQK_0003')

T.zbz, null) C

From "tf_rpt_item_data_fdsc" t

Group by t.issue,t.dept_id,t.obj_add_id

Comparative description:

In HANA, there is no decode function, and the same function is the map function

1.2. Vertical to horizontal query

1.3. Nullif

SQL usage:

It is generally used to set the divisor condition. When the divisor is 0, the result is null.

ORACLE

HANA

Select 100 / nullif (, 0)

From dua l t

Select 100 / nullif (0jin0)

From DUMMY

Comparative description:

In ORACLE and HANA, nullif has the same function, except that HANA has no dual table, only DUMMY table

1.4. Nvl (oracle), ifnull (hana)

SQL usage:

Returns the specified value when the parameter value is null

ORACLE

HANA

Select nvl (aaa,0)

From tab t

Select nvl (aaa,0)

From tab t

Comparative description:

Nvl in ifnull;oracle in hana

1.5. Delete

SQL usage:

Delete data

ORACLE

HANA

Delete from T1 a where exists (select 1 from T2 b where a.id = b.id)

Comparative description:

HANA's DELETE statement is similar to ORACLE in that the FROM clause supports only one table (that is, the table from which records need to be deleted). If the set of records that a table needs to delete is related to other tables, the association needs to be implemented in the WHERE condition.

1.6. With clause

SQL usage:

Used to define virtual tables to facilitate the readability of multi-layer nested statements

ORACLE

HANA

With bb as (

Select t.* from a t)

Select * from bb

There is an error in the grammar of with clause writing.

Alternative writing

Select * from (select * from "STS". "TF_RBAC_DEPARTMENT") bb

Comparative description:

The with clause function is not supported in HANA

1.7. Update table

SQL usage:

Data in update data tabl

ORACLE

HANA

Update "STS". "TF_RBAC_DEPARTMENT" t

Set (addr,sort) = ('11111', 100)

Update "STS". "TF_RBAC_DEPARTMENT" t

Set (addr,sort) = ('11111', 100)

Comparative description:

Update datasheet statement is consistent

1.8. Multi-table association update

SQL usage:

Data in update data tabl

ORACLE

HANA

Update TF_RPT_AJ_M_JZJCSJB t

Set (t. Tcrq, t. Zjycdxsj) = (select T1. Tbbssj, t1. Tjsj

From TF_RPT_HN_STAT_BSHZQK t1

Where t. Issue = T1. Issue and t. Dept_id = T1. Dept_id and t1. Rpt_id = 'HAY01')

Where exists (select 1

From TF_RPT_HN_STAT_BSHZQK t1

Where t. Issue = T1. Issue and t. Dept_id = T1. Dept_id and t1. Rpt_id = 'HAY01'

)

Update "TB". "TF_RPT_AJ_M_JZJCSJB" t

Set (t.tcrqment.zjycdxsj) = (t1.tbbssjPowert1.tjsj)

From "TB". "TF_RPT_HN_STAT_BSHZQK" T1

Where t.issue=t1.issue

And t.dept_id=t1.dept_id

And t1.rptroomid = 'HAY01'

Comparative description:

In hana, if the set of records that a table needs to update is related to other tables, you need to specify the related table in the FROM clause and add the association condition in the WHERE clause

For oracle, multi-table association update is more complex, and oracle does not support from clause, that is, update from syntax is not supported.

1.9. Insert update

SQL usage:

Update existing records and insert new records

ORACLE

HANA

Merge into

UPSERT T (key, val) VALUES (1,9) WHERE KEY = 1

UPSERT T (key, val) VALUES (1,8) WITH PRIMARY KEY

Comparative description:

The merge into of Oracle has existed since 9i, and it can update the existing records. For new records, to avoid the inefficiency of condition judgment in proc, the relevant information can be baidu on its own.

Hana's upsert can update existing records. For new record inserts, all primary key fields must be included in the listed fields. It should be noted that upsert is not a standard sql statement and varies from database to database.

1.10. Multi-table association insert update

SQL usage:

Associate other datasheet data, update existing records, and insert new records

ORACLE

HANA

Merge into

UPSERT "TB". TF_RPT_AJ_M_JZJCSJB (issue,dept_id,jz_id,tcrq,zjycdxsj)

Select t1.issue,t1.dept_id, t.jz_id,t1.tbbssj,t1.tjsj

From "TB". "TF_RPT_HN_STAT_BSHZQK" T1 TFs, RPTs, AJs, JZJCSJB t

Where t.issue=t1.issue

And t.dept_id=t1.dept_id

And t1.rptroomid = 'HAY01'

Comparative description:

Hana's upsert can update existing records. For new record inserts, all primary key fields must be included in the listed fields. When multiple tables are associated, it is similar to insert syntax.

It should be noted that upsert is not a standard sql statement and varies from database to database.

1.11. Like search

SQL usage:

Fuzzy search with like

ORACLE

HANA

Select * from "STS". "tf_rpt_hn_stat_bshzqk" t

Where t.dept_id like '100cm'

Select * from "STS". "tf_rpt_hn_stat_bshzqk" t

Where t.dept_id like '100cm'

Comparative description:

Like datasheet statements are consistent and can be matched with% or _

1.12. Case when

SQL usage:

Using if in sql... Then... Else logic

ORACLE

HANA

Select t.jzlb

Case when t.jzlb = 'thermal power' then 'HD'

When t.jzlb = 'hydropower' then 'SD'

ELSE 'UNKOWN' END

From tf_rpt_dept_jzxx t

Select t.jzlb

Case when t.jzlb = 'thermal power' then 'HD'

When t.jzlb = 'hydropower' then 'SD'

ELSE 'UNKOWN' END

From "STS". "tf_rpt_dept_jzxx" t

Comparative description:

Case when uses the same as oracle

1.13. Multi-table association

SQL usage:

2 or more data table association queries

ORACLE

HANA

Select *

From tf_rpt_dept_jzxx t,tf_rpt_hn_stat_bshzqk t1

Where t.dcdm=t1.dept_id

Select *

From "tf_rpt_dept_jzxx" t, "tf_rpt_hn_stat_bshzqk" T1

Where t.dcdm=t1.dept_id

Comparative description:

Write in the same way

1.14. External correlation

SQL usage:

External join query

ORACLE

HANA

Select *

From tf_rpt_dept_jzxx t

Left outer join tf_rpt_hn_stat_bshzqk t1

On t.dcdm=t1.dept_id

Where t1.issue = '320131100'

Select *

From "tf_rpt_dept_jzxx" t

Left outer join "tf_rpt_hn_stat_bshzqk" T1

On t.dcdm=t1.dept_id

Where t1.issue = '320131100'

Comparative description:

It is written in the same way, and it also shows that Oracle has a (+) external correlation, which is not recommended.

1.15. One day after the current time

SQL usage:

External join query

ORACLE

HANA

Select sysdate+1

From dual t

Select ADD_DAYS (CURRENT_TIMESTAMP, 1) from dummy

Comparative description:

In hana, date time cannot be added or subtracted from number, only through the date time function.

One month after the current time

SQL usage:

External join query

ORACLE

HANA

Select add_months (sysdate,1)

From dual t

Select ADD_ MONTH S (CURRENT_TIMESTAMP, 1) from dummy

Comparative description:

In hana, date time cannot be added or subtracted from number, only through the date time function.

1.16. Returns the first few records of the dataset

SQL usage:

Used to return the first few records of the sql query dataset

ORACLE

HANA

Select * from dual t

Where rownum=5

Select * from "TB". "TF_RPT_HN_STAT_BSHZQK" t

Where t.issue = '2013-12'

Limit 10 offset 5

Comparative description:

In oracle, the recordset is intercepted through its built-in rownum field; in hana, it is done through the limit clause

1.17. Globally unique identifier

SQL usage:

Returns a globally unique identifier, often used to write primary key values

ORACLE

HANA

Select sys_guid () from dual

Select SYSUUID from dummy

Comparative description:

In both Hana and oracle, a globally unique identifier is returned through function implementation, generating a 16-byte original value with 2 hexadecimal display bits per byte, which can be considered a 32-bit string.

The identifier is mainly used for the value of the primary key field of the data table, avoids the sequence value of the seq, and is useful for merging the data of the data table.

1.18. Calculate cumulative partition by

SQL usage:

Calculation and accumulation

ORACLE

HANA

Select t. Issue, t. Zbz, sum (zbz) over (partition by substr (t.) Issue, 1,5) order by t. Issue)

From tf_rpt_item_data_fdsc t

Where substr (t. Issue, 1,5) = '32013'

And t. Zbdm = 'FDCYXQK_0004'

And t. Dept_id = '10272700'

And t. Obj_add_id ='1'

Select t.issueover t.zbz, sum (zbz) over (partition by substr (t.issued1pr 5) order by t.issue)

From tf_rpt_item_data_fdsc t

Where substr (t.issue1) = '32013'

And t.zbdm = 'FDCYXQK_0004'

And t.deptroomid = '10272700'

And t. Objusted addroomid ='1'

Comparative description:

The cumulative sql of Hana is consistent with that of oracle, while other aggregate functions, such as max, min, avg, etc., all support partition by.

two。 Data type comparison

2.1. Date time type

2.1.1. DATE

ORACLE

HANA

DATE

Available dates range from January 1, BC 4712 to December 31, AC9999

The data contains century, year, month, day, hour, minute and second information, and the minimum time granularity is seconds.

The default format is "YYYY-MM-DD"

Time values range from 0001-01-01 to 9999-12-31

The DATE data type consists of year, month and day information, which represents a date value, and the minimum time granularity is day.

The default format of the DATA type is' YYYY-MM-DD'. YYYY stands for year, MM for month, and DD for day.

Comparative description:

Although there are DATE data types in both Oracle and Hana, it should be noted that in HANA, date only represents dates, that is, the data precision is days, which is very different from date in Oracle.

2.1.2. TIME

ORACLE

HANA

None

TIME

The TIME data type consists of hour, minute, and second information, which represents a time value.

The default format of the TIME type is' HH24:MI:SS'. HH24 represents hours from 0 to 24, MI represents minutes from 0 to 59, and SS represents seconds from 0 to 59.

Comparative description:

The time type in HANA represents time, while there is no corresponding type in Oracle

2.1.3. SECONDDATE

ORACLE

HANA

DATE

SECONDDATE

The TIME data type consists of hour, minute, and second information, which represents a time value.

The default format of the TIME type is' HH24:MI:SS'. HH24 represents hours from 0 to 24, MI represents minutes from 0 to 59, and SS represents seconds from 0 to 59.

Comparative description:

The SECONDDATE in HANA represents the date time, which is the same as the date in oracle, but the available time range is different.

2.1.4. Timestamp

ORACLE

HANA

TIMESTAMP

The TIMESTAMP data type consists of date and time information. Its default format is' YYYY-MM-DD HH24:MI:SS.FF 6'

Where the precision bits are 0 to 9

The TIMESTAMP data type consists of date and time information. The default format is' YYYY-MM-DD HH24:MI:SS.FF7'. FFn stands for seconds with decimals, where n represents the number of digits in the decimal part. The timestamp ranges from 0001-01-01 00 to 9999-12-31 23 to 59.9999999

Where the precision bits are from 1 to 7

Comparative description:

TIMESTAMP in ORACLE and HANA represents date and time, and decimal seconds for precision. The default precision of Oracle is 0.000001 seconds, and that of HANA is 0.0000001 seconds.

2.2. Numeric type

2.2.1. DECIMAL

ORACLE

HANA

NUMBER

DECIMAL

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from-84 to 127

The DECIMAL (p, s) data type specifies a fixed-point decimal with a precision of p decimal places s. Precision is the total number of significant digits, ranging from 1 to 34.

The decimal place is the number of digits from the decimal point to the least significant number, ranging from-6111 to 6176, which means that the number of decimal places specifies an exponential range of decimal places from 10-6111 to 10-6176. If no decimal places are specified, the default value is.

When the valid number of a number is to the right of the decimal point, the decimal place is positive; when the significant number is to the left of the decimal point, the decimal place is negative.

Comparative description:

The number in ORACLE is basically the same as the decimal in HANA, but they are different in precision and decimal places.

2.3. Character type

2.3.1. VARCHAR2

ORACLE

HANA

VARCHAR2

VARCHAR

The maximum length of character data stored in varchar2 is 4000 bytes.

Varchar2 normally takes up two bytes of all characters.

VARCHAR2 equates empty strings with null processing

VARCHAR2 characters are stored in several bytes, depending on the character set used by the database

The VARCHAR (n) data type defines a variable length ASCII string, where n represents the maximum length and is an integer value from 1 to 5000

Comparative description:

Varchar2 in ORACLE is basically the same as varchar in HANA, but there is a difference in maximum length.

2.3.2. NVARCHAR

ORACLE

HANA

NVARCHAR2

NVARCHAR

Nvarchar2 is used to store variable-length strings. The maximum value of size is 4000, while the minimum value is 1. The value represents the number of characters, not bytes.

The NVARCHAR (n) data type defines a variable length Unicode string, where n represents the maximum length and is an integer value from 1 to 5000

Comparative description:

Nvarchar2 in ORACLE is basically the same as nvarchar in HANA, but there is a difference in maximum length.

2.4. Large object type

2.4.1. BLOB

ORACLE

HANA

CLOB

Can be used to store unstructured binary data

The maximum length is 4GB

The BLOB data type is used to store large binary data

Maximum size is 2GB

Comparative description:

BLOB in ORACLE and HANA is basically the same, but there is a difference in maximum size.

2.4.2. CLOB

ORACLE

HANA

CLOB

Used to store character data corresponding to the character set defined by the database

The maximum length is 4GB

The CLOB data type is used to store large ASCII character data

Maximum size is 2GB

Comparative description:

CLOB in ORACLE and HANA is basically the same, but there is a difference in maximum size.

2.4.3. NCLOB

ORACLE

HANA

NCLOB

It is used to store fixed width multi-byte character data, and the maximum length of local language character set data is 4GB.

The NCLOB data type is used to store large Unicode character objects

Maximum size is 2GB

Comparative description:

NCLOB in ORACLE and HANA is basically the same, but there is a difference in maximum size.

2.4.4. TEXT

ORACLE

HANA

TEXT

The TEXT data type specifies that text search is supported, which is not a separate SQL type. Selecting a column of TEXT produces a column of type NCLOB

Comparative description:

TEXT is a unique data type of HANA to meet the needs of its unstructured data analysis. There is no corresponding data type in Oracle.

3. SQL function

3.1. Data type conversion function

3.1.1. CAST

ORACLE

HANA

CAST

CAST (expression AS data_type)

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

Used to convert parameters from one data type to another, or to test whether the conversion is feasible

Select CAST (111AS DATE) FROM DUAL

Execution error

Select CAST (111AS DATE) FROM DUMMY

Comparative description:

CAST has the same function in oracle and hana, but there is a difference in conversion types. For example, number cannot be converted to date in oracle, while hana can

3.1.2. TO_ALPHANUM

ORACLE

HANA

TO_ALPHANUM

TO_ALPHANUM (value)

Converts the given value to the ALPHANUM data type.

SELECT TO_ALPHANUM ('10') "to alphanum" FROM DUMMY

Comparative description:

ALPHANUM is a variable-length alphanumeric string, which is rarely used in practical applications, so the conversion function TO_ALPHANUM is also rarely used.

3.1.3. TO_BIGINT

ORACLE

HANA

TO_BIGINT

TO_BIGINT (value)

Convert value to BIGINT type

SELECT TO_BIGINT ('10') "to bigint" FROM DUMMY

Comparative description:

BIGINT is a 64-bit signed integer. Number type is generally used in Oracle, and there is no corresponding conversion function.

3.1.4. TO_BINARY

ORACLE

HANA

TO_BINARY

TO_BINARY (value)

Convert value to BINARY type

SELECT TO_BINARY ('abc') "to binary" FROM DUMMY

Comparative description:

BINARY is binary data, and Oracle has no corresponding conversion function.

3.1.5. TO_BLOB

ORACLE

HANA

TO_BLOB

TO_BLOB (value)

Convert value to BLOB type. Parameter values of LONG RAW or RAW type data are output BLOB values. LONG RAW and RAW types are equivalent to bin type data in other programming speech, that is, binary data.

Convert value to BLOB type. The parameter value must be a binary string, that is, a VARBINARY type

SELECT TO_BLOB (CAST ('abcde' AS RAW)) "to blob" FROM DUMMY

SELECT TO_BLOB (TO_BINARY ('abcde')) "to blob" FROM DUMMY

Comparative description:

There are TO_BLOB functions in both oracle and hana, and the conversion function is basically the same, but the TO_BLOB function in Oracle is only available in 11g, before 8i to 10g, only the TO_LOB function

4.2.6. TO_CHAR

ORACLE

HANA

TO_CHAR

TO_ CHAR (value)

TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.

TO_CHAR (datetime) converts date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt

TO_CHAR (number) converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits

Convert value to CHAR type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats

SELECT TO_CHAR (TO_DATE ('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY

Comparative description:

There are TO_CHAR functions in both oracle and hana, and the conversion function is basically the same. The difference is that the to_char of hana is converted to char, while the to_char of oracle is converted to varchar2, and the character encoding is based on the database character set.

4.2.7. TO_CLOB

ORACLE

HANA

TO_CLOB

TO_ C LOB (value)

TO_CLOB converts NCLOB values in a LOB column or other character strings to CLOB values. Char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Oracle executes this function by converting the underlying LOB data from the national character set to the database character set.

Convert value to CLOB type

SELECT TO_BLOB (CAST ('abcde' AS RAW)) "to blob" FROM DUMMY

SELECT TO_CLOB ('TO_CLOB converts the value to a CLOB data type') "to clob" FROM DUMMY

Comparative description:

There are TO_CLOB functions in both oracle and hana, and the conversion function is basically the same, but the TO_BLOB function in Oracle is only available in 10g. Before 8i to 9i, only the TO_LOB function

4.2.8. TO_DATE

ORACLE

HANA

TO_DATE

TO_DATE (char [, fmt [, 'nlsparam']])

TO_DATE (d [, format])

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is' J', for Julian, then char must be an integer

Convert the date string d to the DATE data type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats.

SELECT TO_DATE (

'The January 15, 1989, 11:00 A.M.'

'Month dd, YYYY, HH:MI A.M.'

'NLS_DATE_LANGUAGE = American')

FROM DUAL

SELECT TO_DATE ('2010-01-12,' YYYY-MM-DD') "to date" FROM DUMMY

Comparative description:

There are TO_DATE functions in both oracle and hana, but the conversion function is a little different. The to_date of hana can only be converted to date, that is, the date type of hana, which only contains the year, month and day, while the to_date of oracle is the date type converted to oracle, including the year, month, day, hour and second.

4.2.9. TO_DATS

ORACLE

HANA

TO_DATS

TO_DATS (d)

Convert the string d to an ABAP date string in the format "YYYYMMDD"

SELECT TO_DATS ('2010-01-12') "abap date" FROM DUMMY

Comparative description:

This conversion function is specific to HANA and is used to convert to ABAP date strings

4.2.10. TO_DECIMAL

ORACLE

HANA

TO_NUMBER

TO_DECIMAL

TO_NUMBER (char [, fmt [, 'nlsparam']])

TO_DECIMAL (value [, precision, scale])

TO_NUMBER converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype

Convert value to DECIMAL type.

SELECT TO_NUMBER ('- AusDollars100', 'L9G999D99'

'NLS_NUMERIC_CHARACTERS =',.''

NLS_CURRENCY =''AusDollars''') "Amount" FROM DUAL

SELECT TO_DECIMAL (7654321.888888, 10,3) "to decimal" FROM DUMMY

Comparative description:

HANA's to_decimal and ORACLE's to_number are basically the same in function, and both can convert strings to numeric types. The parameters of hana's to_decimal also include numeric precision and decimal places, while the parameters of Oracle's to_number include numeric format and nlsparam parameters.

4.2.11. TO_DOUBLE

ORACLE

HANA

TO_DOUBLE

TO_DOUBLE (value)

Converts value to the DOUBLE (double precision) data type.

SELECT 3*TO_DOUBLE ('15.12') "to double" FROM DUMMY

Comparative description:

4.2.12. TO_INT

ORACLE

HANA

TO_INT

TO_INTEGER (value)

Convert value to INTEGER type.

SELECT TO_INT ('10') "to int" FROM DUMMY

Comparative description:

4.2.13. TO_INTEGER

ORACLE

HANA

TO_INTEGER

TO_INT EGER (value)

Convert value to INTEGER type.

SELECT TO_INT EGER ('10') "to int eger" FROM DUMMY

Comparative description:

4.2.14. TO_NCHAR

ORACLE

HANA

TO_NCHAR

TO_NCHAR (value [, format])

Converts value to a NCHAR Unicode character type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats.

SELECT TO_NCHAR (TO_DATE ('2009-12-31'), 'YYYY/MM/DD') "to nchar" FROM DUMMY

Comparative description:

4.2.15. TO_NCLOB

ORACLE

HANA

TO_NCLOB

TO_NCLOB (lob_column | char)

TO_NCLOB (value)

TO_NCLOB converts CLOB values in a LOB column or other character strings to NCLOB values. Char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Oracle implements this function by converting the character set of the LOB column from the database character set to the national character set.

Converts value to the NCLOB data type.

INSERT INTO print_media (product_id, ad_id, ad_fltextn)

VALUES (3502, 31001

TO_NCLOB ('Placeholder for new product description'))

SELECT TO_NCLOB ('TO_NCLOB converts the value to a NCLOB data type') "to nclob" FROM DUMMY

Comparative description:

The function is basically the same, and the TO_NCLOB of Oracle also converts the character set of value to the local character set.

4.2.16. TO_NVARCHAR

ORACLE

HANA

TO_NVARCHAR

TO_NVARCHAR (value [, format])

Converts value to a NVARCHAR Unicode character type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats.

SELECT TO_NVARCHAR (TO_DATE ('2009 Accord 12Accord 31'),' YY-MM-DD') "to nchar" FROM DUMMY

Comparative description:

To _ nvarchar in Hana is the NVARCHAR type that converts value to unicode characters. The functions are all converted to strings, but the character set is different. The to_char function in Oracle uniformly implements this function, and the character set definition is realized through the nlsparam parameter.

4.2.17. TO_REAL

ORACLE

HANA

TO_REAL

TO_REAL (value)

Converts value to a real (single precision) data type.

SELECT 3*TO_REAL ('15.12') "to real" FROM DUMMY

Comparative description:

4.2.18. TO_SECONDDATE

ORACLE

HANA

TO_DATE

TO_SECONDDATE

TO_DATE (char [, fmt [, 'nlsparam']])

TO_REAL (value)

Convert value to SECONDDATE type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats

SELECT TO_DATE (

'The January 15, 1989, 11:00 A.M.'

'Month dd, YYYY, HH:MI A.M.'

'NLS_DATE_LANGUAGE = American')

FROM DUAL

SELECT TO_SECONDDATE ('2010-01-11 13 to seconddate 30 to seconddate FROM DUMMY', 'YYYY-MM-DD HH24:MI:SS')

Comparative description:

There are several date and time types in HANA, and they also correspond to three conversion functions. The conversion function to_seconddate is the closest to_date to Oracle. When you use it, pay attention to distinguish it.

4.2.19. TO_SMALLDECIMAL

ORACLE

HANA

TO_SMALLDECIMAL

TO_SMALLDECIMAL (value)

Convert value to SMALLDECIMAL type.

SELECT TO_SMALLDECIMAL (7654321.89) "to smalldecimal" FROM DUMMY

Comparative description:

4.2.20. TO_SMALLINT

ORACLE

HANA

TO_SMALLINT

TO_SMALL INT (value)

Convert value to SMALLINT type.

SELECT TO_SMALLINT ('10') "to smallint" FROM DUMMY

Comparative description:

4.2.21. TO_TIME

ORACLE

HANA

TO_TIME

TO_TIME (t [, format])

Converts the time string t to the TIME type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats

SELECT TO_TIME ('08:30 AM',' HH:MI AM') "to time" FROM DUMMY

Comparative description:

4.2.22. TO_TIMESTAMP

ORACLE

HANA

TO_TIMESTAMP

TO_TIMESTAMP (d [, format])

Converts the time string t to the TIMESTAMP type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats.

SELECT TO_TIMESTAMP ('2010-01-11 13 to timestamp 30 to timestamp FROM DUMMY', 'YYYY-MM-DD HH24:MI:SS')

Comparative description:

4.2.23. TO_TINYINT

ORACLE

HANA

TO_TINYINT

TO_TINYINT (value)

Convert value to TINYINT type.

SELECT TO_TINYINT ('10') "to tinyint" FROM DUMMY

Comparative description:

4.2.24. TO_VARCHAR

ORACLE

HANA

TO_CHAR

TO_VARCHAR

TO_ CHAR (value)

TO_VARCHAR (value [, format])

Converts the given value to the VARCHAR string type. If the format keyword is omitted, the transformation uses the date format model described in Date Formats.

SELECT TO_VARCHAR (TO_DATE ('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY

Comparative description:

The to_varchar in HANA is basically related to the to_char of Oracle, which converts digital and time-date data to varchar (varchar2).

4.3. Date-time function

4.3.1. ADD_DAYS

ORACLE

HANA

ADD_DAYS

ADD_DAYS (d, n)

Calculate the value of n days after the date d

SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) "add days" FROM DUMMY

Comparative description:

There is no corresponding function in Oracle, but in oracle, the date type can directly +-number to achieve the same function; in HANA, this function can be used for date and seconddate types, and date and seconddate cannot add or subtract number

4.3.2. ADD_MONTHS

ORACLE

HANA

ADD_MONTHS

ADD_MONTHS (d, n)

Returns the date of n months after the date d, if the date d is the end of the month, or if the number of days of the month in which the result date is located is less than the number of days of the date d, the result returns the month-end date of the result month.

Calculate the value of n months after the date d.

SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUAL

SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUMMY

Comparative description:

Hana has the same function as this function in oracle. It can be used in date and seconddate types in HANA.

4.3.3. ADD_SECONDS

ORACLE

HANA

ADD_SECONDS

ADD_SECONDS (t, n)

Calculate the value of n seconds after the time t.

SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23 add seconds 30 FROM DUMMY), 60mm 30)

Comparative description:

There is no corresponding function in Oracle, but in oracle, the date type can directly +-number to achieve the same function; in HANA, this function can be used for time, timestamp and seconddate types, and time, timestamp and seconddate cannot add or subtract number

4.3.4. ADD_YEARS

ORACLE

HANA

ADD_YEARS

ADD_YEARS (d, n)

Calculate the value of n years after the date d.

SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY

Comparative description:

4.3.5. CURRENT_DATE

ORACLE

HANA

SYSDATE

CURRENT_DATE

S ysdate

CURRENT_DATE

Returns the current local system date time.

Returns the current local system date.

SELECT SYSDATE FROM DUMMY

SELECT CURRENT_DATE "current date" FROM DUMMY

Comparative description:

The CURRENT_TIME of HANA is basically the same as the SYSDATE function of oracle, but this function of hana only returns the date

4.3.6. CURRENT_TIME

4.3.7. CURRENT_TIMESTAMP

4.3.8. CURRENT_UTCDATE

4.3.9. CURRENT_UTCTIME

4.3.10. CURRENT_UTCTIMESTAMP

4.3.11. DATNAME

4.3.12. DAYOFMONTH

4.3.13. DAYOFYEAR

4.3.14. DAYS_BETWEEN

4.3.15. EXTRACT

4.3.16. HOUR

4.3.17. ISOWEEK

4.3.18. LAST_DAY

4.3.19. LOCALTOUTC

4.3.20. MINUTE

4.3.21. MONTH

4.3.22. MONTHNAME

4.3.23. NEXT_DAY

4.3.24. NOW

4.3.25. QUARTER

4.3.26. SECOND

4.3.27. SECONDS_BETWEEN

4.3.28. UTCTOLOCAL

4.3.29. WEEK

4.3.30. WEEKDAY

4.3.31. YEAR

4.4. Numeric function

ABS

ACOS

ASIN

ATAN

ATAN2

BINTOHEX

BITAND

CEIL

COS

COSH

COT

EXP

FLOOR

GREATEST

HEXTOBIN

LEAST

LOG

MOD

POWER

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

UMINUS

4.5. String function

4.5.1. ASCII

4.5.2. CHAR

4.5.3. CONCAT

4.5.4. LCASE

4.5.5. LEFT

4.5.6. LENGTH

4.5.7. LOCATE

4.5.8. LOWER

4.5.9. LPAD

4.5.10. LTRIM

4.5.11. NCHAR

4.5.12. REPLACE

4.5.13. RIGHT

4.5.14. RPAD

4.5.15. RTRIM

4.5.16. SUBSTR_AFTER

4.5.17. SUBSTR_BEFORE

4.5.18. SUBSTRING

4.5.19. TRIM

4.5.20. UCASE

4.5.21. UNICODE

4.5.22. UPPER

4.6. Miscellaneous functions

4.6.1. COALESCE

4.6.2. CURRENT_CONNECTION

4.6.3. CURRENT_SCHEMA

4.6.4. CURRENT_USER

4.6.5. GROUPING_ID

4.6.6. IFNULL

4.6.7. MAP

4.6.8. NULLIF

4.6.9. SESSION_CONTEXT

4.6.10. SESSION_USER

4.6.11. SYSUUID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.