AntDB-Oracle Compatibility Developer's Manual P3–13
Published: 2023-09-01 05:36:40

#### CREATE TABLE AS

**Name**

CREATE TABLE AS -- define a new table based on the results of the query.

**Summary**

CREATE \[ GLOBAL TEMPORARY ] TABLE table\_name

\[ (column\_name \[, ...] ) ]

\[ ON COMMIT { PRESERVE ROWS \| DELETE ROWS } ]

\[ TABLESPACE tablespace ]

AS query

**Description**

CREATE TABLE AS creates a new table and populates the new table with the result set returned by the SELECT command. The new table columns have the same names and data types as the columns output by the SELECT command (unless the column names of the newly created table are explicitly given).

The CREATE TABLE AS command has some similarities to the Create View command, but the two commands actually do something completely different: CREATE TABLE AS creates a new table and runs the query only once, populating the new table with the contents of the query. The new table does not track changes to the contents of the source table, and the SELECT command that defines the view is always rerun whenever the view is queried.

**argument**

GLOBAL TEMPORARY

If this parameter is specified, then the created table is a temporary table. Please refer to CREATE TABLE command for details.

table\_name

The name of the data table to be created (which can be referenced in a schema-qualified manner).

column\_name

The name of the column in new table. If no column name is provided, then the column name is the name of the column in the query output.

query

Query statement (SELECT command). Please refer to the SELECT command for the description of the allowed syntax.

#### CREATE VIEW

**Name**

CREATE VIEW - define a new view.

**Summary**

CREATE \[ OR REPLACE ] VIEW name \[ ( column\_name \[, ...] ) ]

AS query

**Description**

The CREATE VIEW command defines a new query view. The view is not physically materialized, but each time you query the view, you are actually running the query statement in the view definition.

The CREATE OR REPLACE VIEW command functions similarly, but if a view with the same name already exists, the newly created view will replace the existing view with the newly created view.

If the schema name is specified (for example, CREATE VIEW myschema.myview ...) ), then the view is created in the specified schema. Otherwise it is created in the current mode. The name of the created view must be different from other views, tables, sequences or indexes in the same schema.

**argument**

name

The name of the created view (which can be referenced in a schema-qualified manner).

column\_name

The name of the created view column (optional). If this parameter is not used, then it is the name of the column in the query output that is used.

query

The query statement that provides columns and rows for the view (is the SELECT command).

For more information about valid queries, refer to the SELECT command.

**Note**

Currently, the view is read-only - the system does not allow insert, update or delete operations on the view. By creating rules on the view and overriding the insert, update, etc. operations on the view to the corresponding operations on other tables, you can get the effect of an updatable view.

Whether access to the tables referenced in the view is allowed or not is determined by the view owner's privileges. If a function is called directly in a query using a view, the function called in the view is treated the same way. Therefore, the user using the view must have permission to call all functions used by the view.

**Example**

Create a view that contains all employees in the department whose number is 30.

```
CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30; 
```