Oracle tables and views

Oracle tables and View tables and views the most basic data objects in Oracle databases are tables and views. Other data objects include constraints, sequences, functions, stored procedures, packages, and triggers. Database Operations can basically be attributed to operations on Data Objects. Understanding and understanding Oracle database objects is a shortcut to learning Oracle. Tables and views in Oracle tables are the basic structure of data storage. ORACLE8 introduces partition tables and object tables, while ORACLE8i introduces temporary tables to make the table more powerful. A view is a logical expression of data in one or more tables. This article describes how to create and manage simple tables and views. You can manage a table as a data table with rows and columns. A table is a data structure in a relational database. Use the create table statement to CREATE a TABLE. When creating a TABLE, you must define the TABLE name, column, and column data type and size. For example: create table products (PROD_ID NUMBER (4), PROD_NAME VAECHAR2 (20), STOCK_QTY NUMBER (5, 3); then we CREATE a TABLE named products, the name of the TABLE followed by the keyword "create table" is defined, three columns are defined, and the data type and size of the column are specified. When creating a table, you can define the integrity constraints of the table, or define the integrity constraints of the column. The general constraints on the column are not null. We will discuss the constraints later. When creating or changing a table, you can give the table a default value. The default value is that when an added row is null, oracle considers this value as the default value. The following data dictionary view provides information about tables and columns :. DBA_TABLES. DBA_ALL_TABLES. USER_TABLES. USER_ALL_TABLES. ALL_TABLES. ALL_ALL_TABLES. DBA_TAB_COLUMNS. USER_TAB_COLUMNS. the naming rule table name of the ALL_TAB_COLUMNS table identifies a table. Therefore, you should try to describe the table in the table name. In oracle, the table name or column name can contain up to 30 strings. The table name should start with a letter. It can contain numbers, underscores, #, and $. You can use a query statement to create a table from another table. The data type and size of the columns in a table are determined by the query result. You can select all columns in other tables or select only some columns for this type of Table query. 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 Note that if the query involves the LONG data type, then create table .... as select .... will not work. After creating a table, you may need to modify the table, such as changing the column definition, changing the default value, adding new columns, and deleting columns. ORACLE uses the alter table statement to change the TABLE definition. 1. added the column Syntax: alter table [schema.]. table_name ADD column_definition example: alter table orders ADD order_date DATE; table alter for existing data rows, the value of the new column will be NULL. 2. Change the column Syntax: alter table [schema.] table_name MODIFY column_name new_attributes; example: alter table orders MODITY (quantity number (), status varchar2 (15); in this example, we modified the TABLE orders, increase the length of the STATUS column to 15, and reduce the QUANTITY column to 10, 3. Modify the column rules as follows :. the string data type can be added. The length of the column, the precision of the numeric data type column.. When the length of a column is reduced, the column should not contain any value. All data rows are NULL .. when changing the data type, the column value must be NULL .. decimal Digits can be increased or decreased, but cannot be reduced. 3. delete data columns to optimize the ORACLE database. The only way is to delete columns and recreate the database. There are many ways to delete a column in ORACLE8i. You can delete unused data columns or mark this column as unused data columns and then delete it. Syntax for deleting data columns: alter table [schema.] table_name DROP {COLUM column_names | (column_names)} [cascade constrains] note that when you delete a column, the index and integrity constraints of the column are also deleted. Note the keyword cascade constrains. If the column to be deleted is part of the multi-column constraint, this constraint is also deleted from other columns. If you are worried that it takes too much time to delete columns in a large database, you can mark them as unused data columns first. 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, or release the occupied disk space. However, unused data columns are not displayed in views and data dictionaries, and the name of the data column will be deleted. This name can be used for new data columns. Indexes, constraints, and statistics based on the data column will be deleted. The statement for deleting unused data columns is: alter table [schema.] table_name DROP {unused colum | column continue} It is very easy to delete a table and change the table name to delete a table, but it is irreversible. Syntax: drop table [schema.] table_name [cascade constraints] After deleting a TABLE, the indexes, triggers, permissions, and integrity CONSTRAINTS of 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 integrity CONSTRAINTS that reference the primary key or unique keywords, the drop table statement must contain the cascade constraints substring. The RENAME command is used to RENAME tables and other database objects. The ORACLE system automatically transfers the integrity constraints, indexes, and permissions of the old table to the new table. ORACLE also makes all database objects based on old tables, such as views, programs, and functions, invalid. Syntax: RENAME old_name TO new_name; example: SQL> RENAME orders TO purchase_orders; The TRUNCATE command of the TABLE RENAMED truncated TABLE is similar TO the DROP command, but it does not delete the entire data TABLE, therefore, indexes, integrity constraints, triggers, and permissions are not deleted. By default, some tables and views are released. If you do not want to release the tablespace, The TRUNCATE statement must contain the reuse storage Sub-string. The TRUNCATE command syntax is as follows: TRUNCATE {TABLE | CLUSTER} [schema.] name {DROP | reuse storage} example: SQL> TRUNCATE TABLE t1; TABLE truncate. A management view is a simplified description of data in one or more tables. You can view a view as a stored query or virtual table ). the query is only stored in the oracle Data dictionary, and the actual data is not stored in any other place. Therefore, creating a view does not consume any other space. A view can also hide complex queries, such as multi-table queries, but you can only see the view. A view can have different column names than its table-based column names. You can create a view that limits access by other users. Create view to CREATE a VIEW. You can CREATE one or more tables or other views to query a VIEW. The query cannot have a for update substring. In earlier ORACLE8i versions, order by substrings are not supported. In current versions, create view can have order by substrings. Example: SQL> CREATE VIEW TOP_EMP AS SELECT empno EMPLOYEE_ID, ename EMPLOYEE_NAME, salary FROM empWHERE salary> 2000 you can change the column name while creating the VIEW, add the name of the column to be named immediately after the view name. To redefine a view, you must include or replace substrings. SQL> CREATE VIEW TOP_EMP (EMPLOYEE_ID, EMPLOYEE_NAME, SALARY) AS SELECT empno, ename, salary FROM empWHERE salary> 2000 if the created VIEW contains errors normally, the view will not be created. However, if you want to CREATE a VIEW with errors, you must include the FORCE option in the create view statement. For example, create force view ORDER_STATUS as select * FROM PURCHASE_ORDERSWHERE STATUS = 'apppove'; SQL>/warning: View create with compilation errors creates a VIEW named ORDER_STATUS, however, the status of such a view is invalid. If the status changes in the future, the view can be re-compiled and its status becomes legal. Retrieving data from a view is basically the same as obtaining data from a table. You can use views in connections and subqueries, or use SQL functions, and the strings of all SELECT statements. Users who insert, update, and delete data can update, insert, and delete data through views under certain restrictions. If a view is connected to multiple tables, only one table can be updated in one time. All columns that can be updated can be found in the data dictionary USER_UPDATETABLE_COLUMNS. You can use the WITH substring in create view. With read only sub-string indicates that the created view is a READ-ONLY view and cannot be updated, inserted, or deleted. With check option indicates that insert and update operations can be performed, but the WHERE sub-string conditions must be met. This condition is the condition for creating a view WHERE clause. For example, in the preceding example, you have created a view TOP_EMP. In this view, you cannot insert data rows with salary less than 2000. Use the drop view command to delete a VIEW. The view definition is also deleted from the data dictionary, and the view-based permissions are also deleted. Other functions, views, and programs related to the view are considered invalid. Example: drop view TOP_EMP;