兄弟连_宋红康_Oracle10_视图.pptVIP

Creating a View (continued) The example on the slide creates a view that contains the employee number, last name, and salary for each employee in department 80. You can display the structure of the view by using the iSQL*Plus DESCRIBE command. Guidelines for creating a view: The subquery that defines a view can contain complex SELECT syntax, including joins, groups, and subqueries. The subquery that defines the view cannot contain an ORDER BY clause. The ORDER BY clause is specified when you retrieve data from the view. If you do not specify a constraint name for a view created with the WITH CHECK OPTION, the system assigns a default name in the format SYS_Cn. You can use the OR REPLACE option to change the definition of the view without dropping and re-creating it or regranting object privileges previously granted on it. Creating a View (continued) You can control the column names by including column aliases within the subquery. The example on the slide creates a view containing the employee number (EMPLOYEE_ID) with the alias ID_NUMBER, name (LAST_NAME) with the alias NAME, and annual salary (SALARY) with the alias ANN_SALARY for every employee in department 50. As an alternative, you can use an alias after the CREATE statement and prior to the SELECT subquery. The number of aliases listed must match the number of expressions selected in the subquery. CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY) AS SELECT employee_id, last_name, salary*12 FROM employees WHERE department_id = 50; View created. Instructor Note Let students know about materialized views or snapshots. The terms snapshot and materialized view are synonymous. Both refer to a table that contains the results of a query of one or more tables, each of which may be located on the same or on a remote database. The tables in the query are called master tables or detail tables. The databases containing the master tables are called the master