The most basic of the Oracle Database Data Objects are tables and views, other constraints, sequences, functions, stored procedures, packages, triggers, and more. The operation of the database can be substantially incorporated into the operation of the data object, understanding and mastering the Oracle database object is a shortcut to learning Oracle.
Table and view
Oracle's table is the basic structure of the data store. Oracle8 introduces partition tables and object tables, Oracle8i introduces a temporary table, making the table more powerful. The view is a logical expression of data in one or more tables. This article we will discuss how to create and manage simple tables and views.
Management form
Table can be seen as a row and column electronic data table, which is a structure in which there is data in the relational database. Establish a table with CREATE TABLE statement, while establishing a table, you must define a table name, column, and column data type and size. E.g:
Create Table Products (PROD_ID NUMBER (4), PROD_NAME VAECHAR2 (20), STOCK_QTY NUMBER (5, 3));
In this way, we have established a table named Products, the keyword table, the table name, and then define three columns, and specifies the data type and size of the column.
At the same time you create a table, you can specify the integrity constraints of the table, or the integrity constraints can also be specified, and the ordinary constraints on the column are NOT NULL, and the discussion of constraints will be carried out later.
You can give a default value when establishing or changing the table. The default is when an increase is increasing, and the Oracle is considered to be the default value when an item is incremented.
The following data dictionary view provides information on the list of tables and tables:
...
Table naming rules
The table name identifies a table, so it should be described as much as possible in the table name, and the table name or column name can be up to 30 strings in Oracle. The table name should begin with letters, and you can include numbers, underscores, #, $, and so on in the table name.
Establish a table from other tables
You can use a query from a table based on one or more tables, the data type and size of the table are determined by the query result. Establishing a query for this form of tables You can select all columns in other tables or select only partial columns. Use keywords as a 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 the long data type, CREATE TABLE .... As SELECT ... will not work.
Change table definition
After the establishment, sometimes we may need to modify the table, such as changing the definition of the column, change the default, add new columns, delete columns, etc. Oracle uses the ALTER TABLE statement to change the definition
1, increase column
grammar:
Alter table [schema.] Table_name add column_definition
example:
Alter Table ORDERS Add Order_Date Date;
Table alter
For the already existing data line, the value of the new column will be NULL.
2, change the column
grammar:
ALTER TABLE [Schema.] Table_name Modify Column_name New_Attribute;
example:
ALTER TABLE ORDERS MODITY (Quantity Number (10, 3), STATUS VARCHAR2 (15)); In this example, we modified the table Orders, increase the length of the Status column to 15, and minus the Quantity column to 10, 3;
The rules for modifying columns are as follows:
You can increase the length of the column of the string data type, the accuracy of the digital data type column.
. When the length of the column is reduced, the column should not contain any values, all data rows are NULL.
When you change the data type, the value of the column must be NULL.
For decimal numbers, it can increase or decrease but cannot reduce his precision.
3, delete data columns
Optimizing the Oracle database, the only way 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 can indicate that the column is not used and then deleted.
Deleting the syntax of the data column is:
Alter table [schema.] Table_name Drop {Colum Column_Names | (Cascade Constrains]} [Cascade Constrains]
It should be noted that the index and integrity constraints of the column are also deleted at the same time as the column is deleted. Note Keyword Cascade Constrains, if the deleted column is part of the multi-column constraint, then this constraint is also deleted at the same time relative to other columns.
If users are worried that the delete column is spent too much in a large database, they can mark them as unused data columns, and the syntax that is not used is as follows:
Alter table [schema.] Table_name set unused {color column_names | (colorn_names)} [cascade constrains]
This statement is marked by one or more data columns as unused data columns, but does not delete data in the data column, nor free of the occupied disk space. However, unused data is not displayed in the view and data dictionary, and the name of the data column will be deleted, and the new data column can use this name. Based on the index, constraints, statistics, etc. of the data column, will be deleted.
Deleting unused data columns is:
Alter Table [Schema.] Table_name Drop {Unuse Colum | Column Continue}
Delete the table and change the table name
The delete table is very simple, but it is an irreversible behavior.
grammar:
DROP TABLE [SCHEMA] TABLE_NAME [CASCADE CONSTRAINTS] After deleting the table, the index, trigger, permissions, and integrity constraints on the table are also deleted. Oracle cannot delete a view, or other program unit, but Oracle will indicate that they are invalid. If the deleted table involves reference primary key or unique keyword integrity constraint, the DROP TABLE statement must contain the Cascade Constraints substring.
Change the table name
The rename command is used to rename the table and other database objects. The Oracle system will automatically transfer to the new table based on the integrity constraints, indexes, and permissions of the old table. Oracle simultaneously enables all old-based database objects, such as views, programs, functions, etc.
grammar:
Rename Old_name to new_name;
example:
SQL> Rename ORDERS to PURCHASE_ORDERS;
Table renamed
Truncated table
The truncate command is similar to the drop command, but he does not delete the entire data table, so the index, integrity constraint, trigger, permissions, etc. will not be deleted. The partial table and view space will be released by default. If the user does not want to release the table space, the TRUNCATE statement is included in the TRUNCATE statement. The TRUNCATE command syntax is as follows:
Truncate {Table | Cluster} [schema.] Name {drop | reuse storage} example:
SQL> TRUNCATE TABLE T1;
Table truncate.
Management view
The view is a simplified description of the data in one or more tables, and the user can view the view as a storage query or a virtual table. Queries are only stored in the Oracle data dictionary, and the actual data is not stored. In any other place, the establishment view does not need to consume other spaces. The view can also hide complex queries, such as multi-table queries, but users can only see views. The view can have a column name that is different from the column name of the table based on him. Users can establish a view that restricts other users access.
View view
The CREATE View command creates a view that the query defined the view can be built on one or more tables, or other views. The query does not have a for Update substring, and the ORDER BY substrs are not supported in the early Oracle8i versions. Now the Create View can have the Order By substring.
example:
SQL> CREATE VIEW TOP_EMP As SELECT Empno Employee_ID, ENAME Employee_name, Salary from Empwhere Salry> 2000
Users can change the column name while creating a view, and the method is to add the column name to be named immediately after the view name. Reproduction The view needs to include the or replace substring.
SQL> CREATE VIEW TOP_EMP (Employee_ID, Employee_name, Salary) AS Select Empno, ENAME, SALARY FROM EMPWHERE SALY> 2000
If the created view contains errors, the view will not be created. But if you need to create a view with an erroneous view, you must bring the Force option in the CREATE VIEW statement. Such as:
Create Force View Order_Status As SELECT * from Purchase_Orderswhere Status = 'AppPove';
SQL> /
Warning: View Create with Compiration Error
This will create a view called ORDER_STATUS, but the status of such a view is illegal, and if the state changes in the future, it can be recompiled, and its state is also legal.
Get data from the view
The data obtained from the view is basically basically basically available from the table, and the user can use the view in the connection and subquery, and the SQL function can be used, and the string of all SELECT statements.
Insert, update, delete data
Users can update, insert, and delete data through views under certain restrictions. If the view is connected multiple tables, only one table can be updated at a time. All enabled columns can be checked in the data dictionary user_updateTable_columns.
The user can use the with substring in the Create View. The with read Only substring represents the viewed view is a read-only view that cannot be updated, inserted, and deleted. The WITH CHECK OPTION indicates that insert and update operations, but should meet the conditions of WHERE skewers. This condition is the condition of creating view WHERE clauses, such as in the above example creates a view top_emp, in which the user cannot insert the Salary less than 2000 data line.
Delete view
Delete view Use the drop view command. At the same time, the view definition is deleted from the data dictionary. The view based on the view is also deleted, and other functions, views, programs, etc. involving the view will be considered illegal.
example:
Drop view top_emp;
