AntDB-Oracle Compatibility Developer’s Manual P3–21
Published: 2023-09-05 03:26:13

#### SAVEPOINT

**Name**

SAVEPOINT -- defines a new save point in the current transaction

**Summary**

SAVEPOINT savepoint\_name

**Description**

The SAVEPOINT command creates a new savepoint in the current transaction.

A savepoint is a specific token inside a thing that allows commands executed after the savepoint is created to roll back to the location where the savepoint was located, restoring the state of the transaction at the time the savepoint was created.

**argument**

savepoint\_name

The name of the savepoint.

**Note**

Use the ROLLBACK TO SAVEPOINT command to roll back to a savepoint location.

We can only create savepoints inside transaction blocks. Multiple savepoints can be created inside things.

When the new savepoint name is the same as the previously created savepoint name, the original savepoint is maintained even though only the most recently created savepoint is used when the rollback operation is performed.

Currently, the SAVEPOINT command is not supported inside the SPL program.

**Example**

Create a save point and roll back all commands executed after the save point:

```
BEGIN;
INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
SAVEPOINT emps;
INSERT INTO jobhist VALUES (9001,'17-SEP-07',NULL,'CLERK',800,NULL,50,'New Hire');
INSERT INTO jobhist VALUES (9002,'20-SEP-07',NULL,'CLERK',700,NULL,50,'New Hire');
ROLLBACK TO depts;
COMMIT;
```
The above transaction commits a data to table dept, but rolls back the insertion of records to tables emp, and jobhist.

#### SELECT

**Name**

SELECT -- fetch a row from a table or view.

**Overview**

SELECT \[ optimizer\_hint ] \[ ALL \| DISTINCT ]

\* \| expression \[ AS output\_name ] \[, ...]

FROM from\_item \[, ...]

\[ WHERE condition ]

\[ \[ START WITH start\_expression ]

CONNECT BY { PRIOR parent\_expr = child\_expr \|

child\_expr = PRIOR parent\_expr }

\[ ORDER SIBLINGS BY expression \[ ASC \| DESC ] \[, ...] ]

]

\[ GROUP BY expression \[, ...] \[ LEVEL ] ]

\[ HAVING condition \[, ...] ]

\[ { UNION \[ ALL ] \| INTERSECT \| MINUS } select ]

\[ ORDER BY expression \[ ASC \| DESC ] \[, ...] ]

\[ FOR UPDATE ]

From\_item is one of the following options:

table\_name \[ alias ]

( select ) alias

from\_item \[ NATURAL ] join\_type from\_item

\[ ON join\_condition \| USING ( join\_column \[, ...] ) ]

**Description**

The SELECT command fetches rows from one or more tables. The command is processed as follows.

1. First count all the members in the FROM list. (Each member in the FROM list is a real or virtual table.) If more than one member is specified in the FROM list, then those members perform a join operation. (See the FROM clause below)

2. If the WHERE clause is specified, then all rows that do not satisfy the query criteria will not appear in the output. (See the WHERE clause below)

3. If the GROUP BY clause is specified, then the output rows will be grouped according to one or more values. If the HAVING clause is used, only the groups that meet the specified criteria are displayed (see the GROUP BY and HAVING clauses section below)

4. If the UNION, INTERSECT, and MINUS operators are used, then the output of multiple SELECT commands is combined into a single result set. The UNION operator returns the records in one or all result sets. INTERSECT returns the rows that are present in all result sets. The operator MINUS returns the rows that appear in the first result set, but not the rows that appear in the second result set. In all three cases, there are no duplicate rows. When the UNION operator is used. If the keyword ALL is specified, duplicate records are not removed (see the UNION, INTERSECT, and MINUS clauses for more information).

5. The actual output records are calculated by using the SELECT output expression for each record (see the SELECT list below).

6. The CONNECT BY clause is used to query data that has a hierarchical relationship. Such data is parented between rows (see the CONNECT BY clause).

7. If the ORDER BY clause is specified, then the returned rows are sorted in the specified order. If the ORDER BY clause is not used, then the order of the returned records is the order in which the system produces them as fast as possible (see the ORDER BY clause below).

8. The DISTINCT clause removes duplicate rows from the result set. If ALL is specified (which is the default), then all rows are returned, which includes duplicates (see the DISTINCT clause below).

9. The FOR UPDATE clause causes the SELECT statement to lock the selected rows of rows to avoid simultaneous modification of those rows by other transactions. (See the FOR UPDATE clause below).

The user must have SELECT permission on the table to read its data, and if the FOR UPDATE clause is used, it also requires UPDATE permission.

##### FROM clause

The FROM clause specifies one or more data tables for the SELECT command. If multiple data tables are specified, the result set is the Cartesian product of all data tables. It is common to use query conditions to limit the rows returned by the query to reduce the result set of the Cartesian product.

The FROM clause contains the following members:

table\_name

The name of an existing table of view (which can be referenced in a schema-qualified manner）.

alias

A name that is substituted for the real table name after the FROM clause. Pseudonyms are used to abbreviate table names or to eliminate unclear table names when self-joining (this is due to the fact that the same table needs to be scanned multiple times). When a pseudonym is provided, it will completely imply the real name of the table or function. For example, for the statement FROM foo as f, the SELECT command will actually refer to f after the FROM clause, not foo.

select

In the FROM clause, another level of SELECT statement can appear. The output of this level SELECT command can be treated as a temporary table created during the run of a single SELECT command. Note that this level of SELECT command must be enclosed in parentheses and a pseudonym should be provided for the output of this statement.

join\_type

join\_type is one of the following types of joins.

\[ INNNER ] JOIN

LEFT \[ OUTER ] JOIN

RIGHT \[ OUTER ] JOIN

FULL \[ OUTER ] JOIN

CROSS JOIN

For join types INNER and OUTER, we must specify a join condition, i.e. NATUREL, ON join\_condition or USING (join\_column \[, ...] ). See the explanation below for details. For CROSS JOIN, these clauses do not appear.

The purpose of the JOIN clause is to combine the two tables or views that follow the FROM clause in a query. If necessary, we can use parentheses to determine the order of nesting. Without parentheses, the order of JOINS nesting is left to right. In any case, the JOIN clause has a higher binding level than a table or view separated by commas after a FROM.

The result of both CROSS JOIN and INNER JOIN operations is a simple Cartesian product. Its result set is the same as that obtained by a join operation performed on two tables or views in the top-level FROM clause, but the join conditions are restricted. CROSS JOIN is equivalent to INNER JOIN ON(TRUE), so that certain rows are not deleted using qualifying conditions. These join types are for notational convenience only and will have no meaning if they do not work with the FROM and WHERE clauses.

LEFT OUTER JOIN returns all the rows in the eligible Cartesian product (for example, all the combined rows that pass the join condition), plus a copy of the rows in the left-hand table that do not match the rows in the table to the right of the clause. By inserting null values for the columns in the table on the right side of the clause, the rows of records on the left side extend the full width of the joined table.

In contrast to LEFT OUTER JOIN, RIGHT OUTER JOIN returns all the rows that pass the join condition, plus the rows in the table on the right side of the clause that do not match, which is just a convenience of notation. A RIGHT OUTER JOIN can be converted to a LEFT OUTER JOIN by converting the table to the left of the clause to the table to the right.

FULL OUTER JOIN returns all the rows that match the join conditions, plus the rows in the table on the left side of the clause that do not match (extended to the right side with a null value) and plus the rows in the table on the right side of the clause that do not match (extended to the left side with a null value).

ON join\_condition

join\_condition is an expression that produces a Boolean type return value (similar to the WHERE clause) to specify which rows in the join match the join condition.

USING (join\_column \[, ...] )

Here is one form of clause, USING (a, b, ... ), is ON left\_table.a = right\_table.a AND left\_table.b = right\_table.b .... The USING clause is an abbreviation for The USING clause means that only one of each pair of equal columns can be included in the join output, not all of them.

NATURAL

NATURAL is shorthand for USING list, used to mention that all columns in two tables have the same name.