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. Tables and views Oracle are the basic structure of data storage. 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. The management table table can be regarded as a row and column electronic data table, which is a structure 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)); then we have established a table named product, keyword crete table, tightening the table name, then defined Three columns, and the data type and size of the column are specified. 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 views provide information tables and table columns:......... DBA_TABLES DBA_ALL_TABLES USER_TABLES USER_ALL_TABLES ALL_TABLES ALL_ALL_TABLES DBA_TAB_COLUMNS USER_TAB_COLUMNS naming table name identifies a table ALL_TAB_COLUMNS table, it should be as described in the table name Table, Oracle's table name or column name can up to 30 strings. The table name should begin with letters, and you can include numbers, underscores, #, $, and so on in the table name. The establishment of a table from other tables can be determined using a query from a table based on one or more tables, the data type and size of the table are determined by the query. 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 EMPLOEETABLE CREATEDSQL> CREATE TABLE Y AS SELECT * from x where no = 2 Need to note that if the query is involved in the LONG data type, Create Table .... as select ... will not Will work. Changing Table Definitions After the setup, 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, increasing column grammar:
ALTER TABLE [Schema.] Table_name Add Column_Definition Example:
Alter Table ORDERS ADD ORDER_DATE DATE; TABLE ALTER For the existing data line, the new column will be NULL. 2, more transformed grammar:
ALTER TABLE [Schema.] Table_name Modify Column_name New_Attributes; Example:
ALTER TABLE ORDERS MODITY (Quantity Number (10, 3), Status Varchar2 (15)); In this example, we modify table orders, increase the length of the Status column to 15, reduce the Quantity column to 10, 3; modified column The rules are as follows: You can increase the length of the column of the string data type, the accuracy of the digital data type column. If the length of the column is reduced, the column should not contain any value, all data rows are null. When the data type is changed, the value of the column must be null. For decimal numbers, it can increase or decrease but not to reduce his Accuracy. 3. Delete the data column Optimize the Oracle database, the only way is to delete columns, 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. The syntax of the delete data column is: Alter Table [Schema.] Table_name drop {color column_names | (column_names)} [Cascade constrains] To note that the index and integrity constraints of the column are also deleted at the same time when 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 will be marked by one or more data columns as unused data columns, but does not delete data in the data column, and do not release Used 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 {unused colum | column contractue} Delete table and change the table name 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 rename command to be renamed to 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 truncate Truncate command is similar to the drop command, but he is not deleting the entire data table, so 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}: SQL> TRUNCATE TABLE T1; TABLE TRUNCATE. Management view view is a simplified description of data in one or more tables, users can view the view as A Stored 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, so the establishment view does not 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. Create a view creation view command to create a view, a query that defines a 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 User You can change the column name while creating a view, and then add the column name to name 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 EMPWHER SALY> 2000 If the view is included in the created error, 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_ORDERSWHER STATUS = 'AppPove'; SQL> / Warning: View Create With Compiration ErroS This will create a view called Order_Status, but the status of such a view is illegal, if the state Variation can be recompiled, and its state is also legitimate. Obtaining data from the view From the view to obtain data from the view, the user can use the view in the connection and subquery, or the SQL function, and strings of all SELECT statements. Insert, update, delete data users can update, insert, and delete data by view 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;
