However, if the view is eventually compiled without errors, wildcards in the defining query are expanded. Views created with errors do not have wildcards expanded. Oracle stores the defining query of the dept view as: SELECT "DEPTNO", "DNAME", "LOC" FROM pt The column names in an expanded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.Īs an example, assume that the dept view is created as follows: CREATE VIEW dept AS SELECT * FROM pt The resulting query is stored in the data dictionary any subqueries are left intact. When a view is created, Oracle expands any wildcard (*) in a top-level view query into a column list.
Expansion of Defining Queries at View Creation Time See "Updating a Join View" for further discussion. The following statement creates the division1_staff view that joins data from the emp and dept tables: CREATE VIEW division1_staff ASĪn updatable join view is a join view where UPDATE, INSERT, and DELETE operations are allowed. You can also create views that specify more than one base table or view in the FROM clause. Oracle9i SQL Reference for detailed syntax, restriction, and authorization information relating to creating and maintaining views If no WITH clause is specified, the view, with some restrictions, is inherently updatable. The view could optionally have been constructed specifying the WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. However, the following INSERT statement returns an error because it attempts to insert a row for department number 30, which cannot be selected using the sales_staff view: INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30) For example, the following INSERT statement successfully inserts a row into the emp table by means of the sales_staff view, which contains all rows with department number 10: INSERT INTO sales_staff VALUES (7584, 'OSTER', 10)
DEFINITION OF SEQUENCE SYNONYM UPDATE
Furthermore, the CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. The query that defines the sales_staff view references only rows in department 10. WITH CHECK OPTION CONSTRAINT sales_staff_cnst The following statement creates a view on a subset of data in the emp table: CREATE VIEW sales_staff AS As with all subqueries, the query that defines a view cannot contain the FOR UPDATE clause. Each view is defined by a query that references tables, materialized views, or other views. You can create views using the CREATE VIEW statement.
If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.For example, if the owner of the view has only the INSERT privilege for Scott's emp table, the view can only be used to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows. Also, the functionality of the view is dependent on the privileges of the view's owner. The owner cannot have obtained these privileges through roles. The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition.You can acquire these privileges explicitly or through a role.
To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.
To create a view, you must meet the following requirements: This section describes aspects of managing views, and contains the following topics: You can think of a view as a "stored query" or a "virtual table." You can use views in most places where a table can be used. Viewing Information About Views, Synonyms, and SequencesĪ view is a tailored presentation of the data contained in one or more tables (or other views), and takes the output of a query and treats it as a table.This chapter describes the management of views, sequences, and synonyms and contains the following topics:
0 kommentar(er)
