AntDB-Oracle Compatibility Developer's Manual P3–1
Published: 2023-08-30 07:18:09

This chapter introduces the set of SQL language compatible with Oracle in AntDB, including SQL syntax, commands, data types, functions and so on. They can be used in both AntDB and Oracle.

This chapter consists of the following sections:

● Preliminary discussion of AntDB SQL syntax and language components.

● Discussion of data types.

● Overview of SQL commands.

● Discussion of built-in functions.

### **SQL Syntax**

This section introduces the basic syntax of the SQL language, which is the basis for an in-depth understanding of how to define and modify data content using the SQL language in later chapters.

#### Lexical Structure

The SQL language consists of a sequence of commands. A command consists of multiple symbols, ending with a semicolon. We end the command when we have finished typing all the characters. The specified syntax about which symbols can be used in a command is determined by these commands.

A symbol can be a keyword, an identifier, an identifier caused by parentheses, text (or constants), or a specific character. These symbols are usually separated by spaces (e.g., spaces, tabs, line breaks), but are not required if there is no semantic ambiguity (usually a specific character connected to other types of symbols).

In addition to this, it is common to encounter comments in the SQL statements that are entered. Comments are not symbols, and comments can be equated to spaces.

For example, the following is a syntactically valid SQL statement.

```
SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');
```

This is a sequence of 3 commands, one command per line. (Usually this is not required, and in fact it is possible to hold multiple commands in a single line, or split the commands into different lines)

SQL syntax is not entirely consistent with what symbols are used to identify commands, what are operators or parameters. The first few symbols in a command are usually the command name, so in the above example we are usually talking about a SELECT, UPDATE and INSERT command. But for example, the UPDATE command requires a SET symbol to appear at the specified location. For a different form of the INSERT command, it would require that a VALUES notation appear only at the specified place to complete the input of this command. We will describe the precise syntax rules for each command in Section 2.3.

#### Identifiers and keywords

The symbols that appear in the examples above, such as SELECT, UPDATE, or VALUES, are actually examples of keywords. This means that some words have a fixed meaning in the SQL language. The symbols MY\_TABLE and A are examples about identifiers. Depending on their use in commands, these symbols indicate the name of a data table, column or other database object. Therefore, some times they are referred to as "names". Keywords and identifiers have the same syntactic structure, which means that it is not possible to identify a symbol as an identifier or a keyword without knowing the language used.

SQL language identifiers and keywords must start with a letter (a-z or A-Z), while the following characters can be letters, underscores, numbers (0-9), $ symbols or numeric symbols (#).

Identifiers and keywords are not case-sensitive, so

```
UPDATE MY_TABLE SET A = 5;
```

is equivalent to

```
uPDaTE my_TabLE SeT a = 5; 
```

The usual writing convention is to capitalize the keyword and lowercase the name, e.g.:

```
UPDATE my_table SET a = 5; 
```

The second type of identifier consists of: a separating identifier or a quoting identifier - is a sequence of characters caused by double quotation marks. A separator identifier can only be an identifier, not a keyword. So, we can use "select" for a column or data table with the name "select". However, if select is not enclosed in quotes, it can only be used as a keyword, in which case it will raise an error if used to represent a data table name and column name.

An example of using the quote identifier is as follows: 

```
UPDATE "my_table" SET "a" = 5; 
```

The quote identifier can contain any character except the character with the code 0. (We can write 2 double quotes to include a double quote.) This allows creating table or column names that contain spaces and \& symbols. However, the name length limit still applies.

If an identifier is enclosed in double quotes, it will be case sensitive, but identifiers that are not enclosed in quotes will be treated as lower case. For example, AntDB considers the identifiers FOO, foo, and "foo" to be the same, but for "Foo" and "FOO", they are different when compared to each other or to the three identifiers above. (In AntDB, names not enclosed in quotes are automatically lowercase, which is not compatible with Oracle, where names not enclosed in quotes are actually capitalized. So foo is equivalent to "FOO" in Oracle, not to "foo". If you want to write a concise application, it is recommended that you either quote all names or never do so). However, in AntDB, column names that are not enclosed in double quotes are displayed in upper case in select queries, in line with Oracle.

The example of case is as follows

```
create table TEST1(ID int, NAME varchar(5));
insert into test1(name) values('Tom');
select * from test1;

create table TEST2("ID" int, "NAME" varchar(5));
insert into TEST2("NAME") values('Tom');
select * from test2;

create table "TesT3"(ID int, NAME varchar(5));
insert into "TesT3" (NAME) values('Tom');
select * from "TesT3";
```

The same result of the select statement query above is as follows.

```
 ID | NAME 
----+------
    | Tom
(1 row)
```