Oracle Database tables and views

oracle| View | data | Database Oracle database Data objects are the most basic tables and views, others are constraints, sequences, functions, stored procedures, packages, triggers, and so on. The operation of the database can be basically attributed to the operation of the data object, understanding and mastering Oracle Database Objects is a shortcut to learn Oracle.

Tables and views

Tables in Oracle are the basic structure of data storage. ORACLE8 introduced partitioned tables and object tables, Oracle8i introduced temporary tables to make the tables more powerful. A view is a logical expression of data in one or more tables. In this article, we'll discuss how to create and manage simple tables and views.

Manage tables

Tables can be considered as rows and columns of spreadsheets, which are structures that have data in relational databases. Creating a table with the CREATE TABLE statement, you must define the table name, the column, and the data type and size of the column, while creating the table. For example:

CREATE TABLE Products
(prod_id number (4),
Prod_name VAECHAR2 (20),
Stock_qty Number (5,3)
);
So we set up a table named products, the name of the table immediately following the keyword CREATE TABLE, and then three columns are defined, and the data type and size of the column are specified.

While creating a table you can specify a table's integrity constraints, or you can specify a column's integrity constraints, the normal constraint on the column is not NULL, and the discussion of the constraint we'll do later.

When you create or change a table, you can give the table a default value. The default value is that Oracle considers the value to be the default value when the row is increased and an item value in the increased row is null.

The following data dictionary views provide information about the columns of tables and tables:

. Dba_tables
. Dba_all_tables
. User_tables
. User_all_tables
. All_tables
. All_all_tables
. Dba_tab_columns
. User_tab_columns
. All_tab_columns

Naming rules for tables

The table name identifies a table, so you should describe the table as much as possible in the table name, and the table or column name in Oracle can be up to 30 strings long. The table name should start with a letter and can contain numbers, underscores, #, $, and so on in the table name.

Create a table from another table

You can use a query to create a table from one or more tables, and the data type and size of the table's columns are determined by the query results. Queries that create tables of this form can select all columns in other tables or select only a subset of the columns. Use the keyword as in the CREATE TABLE statement, for example:

Sql>create TABLE EMP as SELECT * FROM employee

TABLE CREATED

Sql> CREATE TABLE Y as SELECT * from X WHERE no=2
It should be noted that if the query involves a Long data type, create TABLE .... As SELECT .... will not work.

Change Table Definition

After creating the table, sometimes we may need to modify the table, such as changing the definition of the column, changing the default value, adding new columns, deleting columns, and so on. Oracle uses ALTER TABLE statements to change the definition of a table

1, add Column

Grammar:

ALTER TABLE [schema.] table_name ADD column_definition
Cases:

ALTER TABLE orders ADD order_date date;

TABLE ALTER
For data rows that already exist, the value of the new column will be null.

2, change the column

Grammar:

ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;
Cases:

ALTER TABLE orders modity (quantity number (10,3), status varchar2 (15));
In this example we modified the table orders to increase the length of the status column to 15, reducing the quantity column to 10, 3;

The rules for modifying columns are as follows:

. You can increase the length of a column in a string data type, and the precision of a numeric data type column.

. When you reduce the length of a column, the column should contain no values, and all data rows are null.

. When you change a data type, the value of the column must be null.

. For decimal digits, you can increase or decrease but not reduce his precision.

3. Delete data columns

The only way to optimize an Oracle database is to delete columns and re-establish the database. There are many ways to delete columns in oracle8i, you can delete unused data columns or you can label the column as unused and delete it.

The syntax for deleting a data column is:

ALTER TABLE [schema.] table_name DROP {Colum Column_names | (Column_names)} [CASCADE constrains]
Note that the index and integrity constraints on the column are also deleted when the column is deleted. Note the keyword Cascade constrains, if the deleted column is part of a multiple-column constraint, then the constraint is also deleted relative to the other columns.

If users are concerned that deleting a column in a large database takes too much time, they can mark them as unused data columns, and the syntax for marking unused data columns is as follows:

ALTER TABLE [schema.] table_name SET unused {Colum Column_names | (Column_names)} [CASCADE constrains]
This statement marks one or more data columns as unused data columns, but does not delete the data in the data column, nor does it release the disk space occupied. However, unused data columns are not displayed in the view and data dictionaries, and the name of the data column is deleted, and the new data column can use that name. indexes, constraints, statistics, and so on, based on the data column, are deleted.

The statements that delete unused data columns are:

ALTER TABLE [schema.] table_name DROP {Unused Colum | COLUMN CONTINUE}
deleting tables and changing table names

Deleting a table is very simple, but it is an irreversible behavior.

Grammar:

DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]
When you delete a table, the indexes, triggers, permissions, and integrity constraints on the table are also deleted. Oracle cannot delete views, or other program units, but Oracle will mark them as invalid. If the deleted table involves an integrity constraint referencing a primary key or a unique keyword, the DROP TABLE statement must contain the CASCADE constraints substring.

Change table name

The rename command is used to rename tables and other database objects. The Oracle system automatically transfers the integrity constraints, indexes, and permissions based on the old tables to the new table. Oracle also makes it illegal for all database objects based on old tables, such as views, programs, functions, and so on.

Grammar:

RENAME Old_name to New_name;
Cases:

sql> RENAME orders to Purchase_orders;

TABLE renamed

Cut short Table

The truncate command is similar to the drop command, but it does not delete the entire datasheet, so indexes, integrity constraints, triggers, permissions, and so on, are not deleted. Some tables and view spaces are freed by default, and the TRUNCATE statement contains reuse storage substrings if the user does not want to free the table space. The truncate command syntax is as follows:

TRUNCATE {table| CLUSTER} [schema.] Name {drop| Reuse STORAGE}
Cases:

sql> TRUNCATE TABLE t1;

TABLE truncate.
Manage views

A view is a simplified description of the data in one or more tables, and the user can view it as either a stored query (stored query) or a virtual table. The query is stored only in the Oracle data dictionary, and the actual data is not stored anywhere else, So building a view doesn't consume other space. Views can also hide complex queries, such as multiple table queries, but users can see only views. A view can have a column name that differs from the column name of the table that he is based on. Users can establish views that restrict access to other users.

Building a View

The CREATE VIEW command creates a view, and queries that define the view can be built on one or more tables, or on other views. The query cannot have a for update substring, and the order by substring is not supported in the earlier version of Oracle8i, and the CREATE view can now have a sequence by substring in the current version.

Cases:

Sql> CREATE VIEW Top_emp as
SELECT empno Employee_id,ename employee_name,salary
From EMP
WHERE Salary >2000
Users can change the name of a column while creating a view by adding the name of the column to be named immediately after the view name. Redefining the view requires that the or replace substring be included.

sql> CREATE VIEW top_emp
(Employee_id,employee_name,salary) as
SELECT empno, ename, salary
From EMP
WHERE Salary >2000
If you create a view that contains errors under normal circumstances, the view will not be created. But if you need to create a view with errors, you must bring the force option in the CREATE VIEW statement. Such as:

CREATE FORCE VIEW Order_status as
SELECT * from Purchase_orders
WHERE status= ' Apppove ';

sql>/

Warning:view Create with compilation errors
This creates a view named Order_status, but the state of such a view is illegal and can be recompiled if the state changes later, and its state becomes legal.

Getting data from a view

Getting data from a view is basically the same as getting data from a table, which allows users to use views in connections and subqueries, as well as SQL functions and strings for all SELECT statements.

Inserting, updating, and deleting data

Users can update, insert, and delete data through the view under certain restrictions. If the view is connected to multiple tables, only one table can be updated at a time. All columns that can be updated can be found in the user_updatetable_columns of the data dictionary.

The user can use the with substring in CREATE view. With the Read only substring indicates that the created view is a read-only view and cannot be updated, inserted, or deleted. The WITH CHECK option indicates that you can insert and update operations, but you should satisfy the conditions of the where substring. This condition is the condition for creating the view where clause, such as in the above example where the user creates a view top_emp in which the user cannot insert a row of data that is less than 2000 salary.

Delete View

Deletes a view using the drop view command. The view definition is also removed from the data dictionary, and the view-based permissions are deleted as well, and other functions, views, programs, etc. that pertain to the view are considered illegal.

Cases:

DROP VIEW top_emp;