SAP HANA SQL参考手册（SPS05，第45版）

需积分: 9 24 浏览量更新于2024-07-24 收藏 954KB PDF 举报

HANA SQL REFERENCE是由SAP内部提供的文档，旨在为SAP HANA数据库的专业人士提供详细的SQL参考手册。该版本适用于咨询师、管理员、SAP硬件合作伙伴以及其他对HANA技术感兴趣的用户。此文档针对的是SAPHANA Appliance Software SPS05的第45次修订，发布日期为2012年12月19日。 HANA SQL Reference手册的主要目标是帮助读者深入了解HANA数据库的操作，包括查询语言、数据管理、性能优化以及与SAP其他组件（如SAP Library）的集成。版权信息表明，所有内容受SAP AG的严格版权保护，未经许可，禁止任何形式的复制或传播，且可能在未经通知的情况下进行更新。值得注意的是，文档中提到的部分软件产品，如Microsoft的Windows、Excel、Outlook、PowerPoint、Silverlight和Visual Studio，以及IBM的DB2、System i等，都是各自公司的注册商标。这反映了HANA在多平台环境下的兼容性和集成能力，同时提醒用户在引用这些商标时需遵守相应的授权协议。此外，文档还涵盖了SAP HANA与第三方技术的交互，如Microsoft的DB2 Connect、IBM的RACF（Resource Access Control Facility）以及与IBM的其他存储解决方案（如System Storage、Storwize、XIV和GPFS）的整合。对于操作系统如AIX、OS/2、OS/390、z/OS、zEnterprise等，也有所提及，说明HANA在支持多样化的IT基础设施中的角色。 HANA SQL REFERENCE是一个重要的参考资料，不仅为HANA的使用者提供了全面的SQL语法指南，还涵盖了与其他软件和技术的集成细节，是理解和操作SAP HANA数据库不可或缺的工具。对于任何希望深入学习或进行HANA系统管理的人来说，理解和掌握这份文档的内容至关重要。

maximum value is 2,147,483,647 for INTEGER.

BIGINT

The BIGINT data type stores a 64-bit signed integer. The minimum value is -

9,223,372,036,854,775,808 and the maximum value is 9,223,372,036,854,775,807 for BIGINT.

DECIMAL(precision, scale) or DEC(p,s)

DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. "p" specifies precision or the

number of total digits (the sum of whole digits and fractional digits). "s" denotes scale or the number

of fractional digits. For example, if a column is defined as DECIMAL(5, 4), the numbers 3.14, 3.1415,

3.141592 are stored in the column as 3.1400, 3.1415, 3.1416, respectively keeping the specified

precision(5) and scale(4).

The precision p, can range from 1 to 34. The scale can range from 0 to p. If the scale is not specified, it

defaults to 0.

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In

this case, precision and scale can vary within the range 1 to 34 for precision and -6,111 to 6,176 for

scale depending on the stored value.

Examples: 0.0000001234 (1234 x 10-10) has the precision 4 and the scale 10. 1.0000001234

(10000001234 x 10-10) has the precision 11 and scale 10. 1234000000 (1234x106) has the precision 4

and scale -6.

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In

this case, precision and scale can vary within the range described above, 1~34 for precision and -

6,111~6,176 for scale depending on the stored value.

SMALLDECIMAL

The SMALLDECIMAL is a floating-point decimal number. The precision and scale can vary within the

range, 1~16 for precision and -369~368 for scale depending on the stored value. SMALLDECIMAL is

supported only on column store.

DECIMAL and SMALLDECIMAL are floating-point types. For instance, a decimal column can store any

of 3.14, 3.1415, 3.141592 whilst maintaining their precision.

DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. For instance, 3.14, 3.1415,

3.141592 are stored in a decimal(5, 4) column as 3.1400, 3.1415, 3.1416, respectively keeping the

specified precision(5) and scale(4).

REAL

The REAL data type specifies a single-precision 32-bit floating-point number.

DOUBLE

The DOUBLE data type specifies a single-precision 64-bit floating-point number. The minimum value is

-1.79769 x 10308 and the maximum value is 1.79769x10308 . The smallest positive DOUBLE value is

2.2207x10-308 and the largest negative DOUBLE value is -2.2207x10-308.

FLOAT(n)

The FLOAT(n) data type specifies a 32-bit or 64-bit real number, where n specifies the number of

significant bits and can range between 1 and 53.

When you use the FLOAT(n) data type, if n is smaller than 25, it becomes a 32-bit REAL data type. If n

is greater than or equal to 25, it then becomes a 64-bit DOUBLE data type. If n is not declared, it

becomes a 64-bit double data type by default.

Character String Types

The character string data types are used to store values that contain character strings. While VARCHAR data

types contain ASCII character strings, NVARCHAR are used for storing Unicode character strings.

VARCHAR

The VARCHAR(n) data type specifies a variable-length ASCII character string, where n indicates the

maximum length and is an integer between 1 and 5000.

NVARCHAR

The NVARCHAR(n) data type specifies a variable-length Unicode character set string, where n

indicates the maximum length and is an integer between 1 and 5000.

ALPHANUM

The ALPHANUM(n) data type specifies a variable-length character string which contains alpha-

numeric characters, where n indicates the maximum length and is an integer between 1 and 127.

SAP HANA Database - SQL Reference Manual

16 2012-12-19

SHORTTEXT

The SHORTTEXT(n) data type specifies veriable-length character string which supports textsearch-

features and stringsearch-features. This is not a standalone sql-type. Selecting a SHORTTEXT(n)-

column yields a column of type NVARCHAR(n).

<shorttext_type> ::= SHORTTEXT '(' int_const ')' <elem_list_shorttext>

<elem_list_shorttext> ::= <elem_shorttext> [... ',' <elem_shorttext>]

<elem_shorttext> ::= <fulltext_elem> | SYNC[HRONOUS]

Binary Types

Binary types are used to store bytes of binary data.

VARBINARY

The VARBINARY(n) data type is used to store binary data of a specified maximum length in bytes,

where n indicates the maximum length and is an integer between 1 and 5000.

Large Object (LOB) Types

LOB (large objects) data types, CLOB, NCLOB and BLOB, are used to store a large amount of data such as text

documents and images. The maximum size of an LOB is 2 GB.

BLOB

The BLOB data type is used to store large binary data.

CLOB

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

NCLOB

The NCLOB data type is used to store a large Unicode character object.

TEXT

The TEXT data type specifies which supports textsearch-features. This is not a standalone sql-type.

Selecting a TEXT-column yields a column of type NCLOB.

<text_type> ::= TEXT <opt_fulltext_elem_list_text>

<opt_fulltext_elem_list_text> ::=

<fulltext_elem_text> [... ',' <fulltext_elem_text>]

<fulltext_elem_text> ::= <fulltext_elem>

| [SYNC[HRONOUS]

| [ASYNC[HRONOUS] FLUSH [QUEUE]

EVERY <n> MINUTES [[OR] AFTER <m> DOCUMENTS] ]

Syntax-rules common to TEXT and SHORTTEXT

<fulltext_elem> ::= LANGUAGE COLUMN <column_name>

| LANGUAGE DETECTION '(' <str_const_list> ')'

| MIME TYPE COLUMN <column_name>

| FUZZY SEARCH INDEX [ON|OFF]

| PHRASE INDEX RATIO [ON|OFF]

| CONFIGURATION <str_const>

| SEARCH ONLY [ON|OFF]

| FAST PREPROCESS [ON|OFF]

LOB types are provided for storing and retrieving such large data. LOB types support the following

operations.

The length() function returns the LOB length in bytes.

LIKE can be used to search LOB columns.

The LOB types have the following restrictions:

LOB columns cannot appear in ORDER BY or GROUP BY clauses.

LOB columns cannot appear in FROM clauses as a join predicate.

LOB columns cannot appear in WHERE clauses as a predicate except LIKE, CONTAINS, =, or <>.

LOB columns cannot appear in SELECT clauses as an aggregate function argument.

LOB columns cannot appear in SELECT DISTINCT clauses.

LOB columns cannot be used in set operations such as EXCEPT. UNION ALL is an exception.

LOB columns cannot be used as a primary key.

SAP HANA Database - SQL Reference Manual

17 2012-12-19

LOB columns cannot be used in CREATE INDEX statements.

LOB columns cannot be used in statistics update statements.

Mapping between SQL Data Type and Column Store Data Type

SQL Type Column Store Type

Intege r Types TINYINT, SMALLINT, INT CS_INT

BIGINT CS_FIXED(18,0)

Approxima te Type s REAL CS_FLOAT

DOUBLE CS_DOUBLE

FLOAT CS_DOUBLE

FLOAT(p) CS_FLOAT, CS_DOUBLE

Decima l Types DECIMAL CS_DECIMAL_FLOAT

DECIMAL(p,s ) CS_FIXED(p-s ,s )

SMALLDECIMAL CS_SDFLOAT

Cha racter Types VARCHAR CS_STRING

NVARCHAR CS_STRI NG

CLOB, NCLOB CS_STRI NG

ALPHANUM CS_ALPHANUM

Bina ry Type s BLOB CS_RAW

VARBINARY CS_RAW

Da te /Ti me Types DATE CS_DAYDATE

TIME CS_SECONDTIME

TIMESTAMP CS_LONGDATE

SECONDDATE CS_SECONDDATE

Data Type Conversion

This section describes the data type conversion allowed in SAP HANA Database.

Explicit type conversion

The type of an expression result, for example a field reference, a function on fields, or literals can be

converted using the following functions: CAST, TO_ALPHANUM, TO_BIGINT, TO_VARBINARY,

TO_BLOB, TO_CLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_INTEGER, TO_INT,

TO_NCLOB, TO_NVARCHAR, TO_REAL, TO_SECONDDATE, TO_SMALLINT, TO_TINYINT, TO_TIME,

TO_TIMESTAMP, TO_VARCHAR.

Implicit type conversion

When a given set of operand/argument types does not match what an operator/function expects, a

type conversion is carried out by the SAP HANA Database. This conversion only occurs if a relevant

conversion is available and if it makes the operation/function executable. For instance, a comparison

of BIGINT and VARCHAR is performed by implicitly converting VARCHAR to BIGINT. The entire explicit

conversions can be used for implicit conversion except for the TIME and TIMESTAMP data types. TIME

and TIMESTAMP can be converted to each other using TO_TIME(TIMESTAMP) and

TO_TIMESTAMP(TIME).

Examples

Table 8: Implicit Type conversion Examples

Input Expression Transformed Expression with Implicit Conversion

BIGINT > VARCHAR BIGINT > BIGINT(VARCHAR)

BIGINT > DECIMAL DECIMAL(BIGINT) > DECIMAL

TIMESTAMP > DATE TIMESTAMP > TIMESTAMP(DATE)

DATE > TI ME Error beca use there i s no convers i on a vai l a bl e be tween DATE and TIME

In the tables below,

Boxes with "OK" means data type conversions are allowed without any checks.

Boxes with "CHK" means the data type can be converted if the data is valid for the target type.

Boxes with "-" indicates that data type conversion is not allowed.

SAP HANA Database - SQL Reference Manual

18 2012-12-19

剩余192页未读，继续阅读

fish139175

粉丝: 0
资源: 1

SAP HANA SQL参考手册（SPS05，第45版）

SAP HANA SQL Script Reference

SAP_HANA_SQL_and_System_Views_Reference_en

SAP HANA SQL and System Views Reference

Hana CDS Reference

SAP_HANA_SQL_Reference_Guide_en.pdf

SAP_HANA_SQL_Script_Reference_en.pdf

SAP HANA SQL Script编程指南

SAP HANA SQL与系统视图参考指南

SAPHANA SQL参考指南：数据类型与操作

SAP HANA SQL脚本参考：存储过程与数据类型详解

最新资源