Oracle SQL Jumpstart with Examples- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

86
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P10: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P10

420 18.7 The Recycle Bin can also add a comment to the end of a line of code by placing the hyphens after all executable code. Single-Line Comments. Precede a line with the REM or REMARK keyword to mark that line as a comment. The following query demonstrates all three types of comments. See the result in Figure 18.28. REM This query looks for Artists with REM a letter "a" in their names. SELECT /* Ignore this line and this line and this line too */ NAME --this is the FROM clause FROM ARTIST WHERE NAME LIKE '%a%' --and this is the WHERE clause ; In a color graphic, the comments would be highlighted in red within SQL*Plus Worksheet. This makes them much easier to see. I have high- lighted commented sections by boxing them, as shown in Figure 18.28. The penultimate section in this chapter on tables will examine the recy- cle bin, which is newly introduced in Oracle Database 10g. 18.7 The Recycle Bin Oracle Database 10g introduces a recycle bin. This feature is certainly use- ful from the perspective of database administration, but it could cause problems with space and perhaps performance if the recycle bin is not regu- larly monitored and cleared. There are several changes to various DDL commands associated with the recycle bin as listed below. The syntax dia- gram in Figure 18.29 shows generally applicable recycle bin syntax. The DROP TABLE command now requires a PURGE option if an object is not to be retained in the recycle bin. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
18.8 Metadata Views 421 Figure 18.28 Commenting SQL Code. The new PURGE command is required to allow clearing the recycle bin. The Oracle Database 10g SQL Reference Manual states that the FLASHBACK TABLE command is used to recover a table from the recycle bin. This chapter concludes with a short section on database metadata as directly applicable to this chapter. 18.8 Metadata Views This section lists metadata views allowing access into the structural details of tables. Chapter 19 describes the basis and detail of Oracle Database metadata views. USER_TABLES. Table structural deﬁnitions. USER_TAB_COLS and USER_TAB_COLUMNS. Table column deﬁnitions where USER_TAB_COLS includes hidden columns. Chapter 18 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
422 18.8 Metadata Views Figure 18.29 Syntax for the Recycle Bin. USER_TAB_COMMENTS and USER_COL_COMMENTS. Comments on table columns. USER_UNUSED_COL_TABS. This view shows columns in tables marked as SET UNUSED and not physically dropped from tables. DBA_RECYCLEBIN and USER_RECYCLEBIN. These two metadata views represent all recycle bins for all users and the speciﬁc connected user recycle bin. The RECYCLEBIN view is often referred to and is simply a synonym for the USER_RECYCLEBIN view. See Chapter 22 for details on synonyms. USER_OBJECT_TABLES. Object type table structures. USER_TAB_PARTITIONS and USER_TAB_SUBPARTITIONS. Table partition and subpartition structures. USER_PART_TABLES. Table partitioning details of tables at the partition rather than the table level, as is the case for the USER_TAB_PARTITIONS and USER_TAB_SUBPARTITIONS views. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
18.9 Endnotes 423 Figure 18.30 Querying USER_TABLES and USER_TAB_COL UMNS. The script executed in Figure 18.30 matches all tables and table col- umns for the currently logged-in user. This gives an example of the power of metadata views. The script is included in Appendix B. That covers all we want to cover about tables at present. Chapter 20 cov- ers constraints and partially returns to the subject of tables. The next chap- ter looks at views. 18.9 Endnotes 1. Oracle Performance Tuning for 9i and 10g (ISBN: 1-55558-305-9) Chapter 18 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19 Views In this chapter: What is a view? What types of views are available? How do we use views? What are metadata views? There are various examples of views in other chapters. This chapter describes views in detail. A view is an overlay onto one or more other data sources. A data source can be a table, view, or multiples thereof. 19.1 What Is a View? Imagine that you are working in an insurance company, where part of your job duties are to help users who have trouble writing queries for reports. The users have had basic training in writing SQL queries, but they often get stuck when they must join many tables or use subqueries. If you could set up the join or subquery ahead of time, then the users would have no trou- ble adding to it to reﬁne their report requirements. This is one of the best reasons to create a view. Note: This approach can, however, be bad for performance because the entire query in the view will always be executed, whatever ﬁltering is placed on a query against a view.1 A view is a query that is stored in the database and executed to create a virtual table. A view is given a name, is owned by a schema, and is executed 425 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
426 19.2 Types and Uses of Views whenever a query or other SQL command uses the view. The tables refer- enced in the view’s query are called base tables. Views do not contain any data of their own, and therefore do not require storage. Views belong to a schema, and you can grant privileges such as SELECT, INSERT, UPDATE, and DELETE on views, even if the user does not have any privileges on the base table(s) used in the view. Views are most often used for security purposes and as an aid to query- ing the database; however, some views can be used to insert, update, and delete data in the underlying table. 19.2 Types and Uses of Views Here are some of the more common reasons for creating a view: Security. Create a view with a limited subset of the rows and/or col- umns in a table or tables and give the user permission to use the view, but not the base tables. Simplicity. Create a view that combines tables that have complex relationships so users writing queries do not need to understand the relationships. Complex Joins. Sometimes queries cannot be done without great difﬁculty unless you create a view in something like a temporary table ﬁrst. For example, you can create a view with a GROUP BY clause that summarizes data. You can join that summary data with other tables only by using a view. Materialized Views. This is not a view as such because the data in the view is physically stored in the materialized view, thus the term materi- alized. Materialized views are a little too specialized for this book. Regardless of why a view is created, it falls into one of three basic catego- ries or types of views: Simple View. A simple view contains a query on a single table. For example, a view that lists the names, addresses, and zip codes of all artists in the USA is a simple view because only the ARTIST table is queried in the view. Simple views can be used to narrow the focus or visible data window of a speciﬁc user from the entire table to a subset Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.3 CREATE VIEW Syntax 427 of the rows or a subset of the columns. The best explanation for this type of view is security where, for instance, different customers shar- ing the same database can only view their own data. With a few restrictions (examined later in this chapter), you can update the table on which the view is built by updating the view. You can also insert and delete rows in the base table through the view. Constraint View. A constraint view can be used to insert a new row into the underlying table as long as the row would be returned by the query, or the row exists for the view. For example, if the view only looks at ARTIST rows in the USA, you could not insert an ARTIST row where the artist is in France. The same rule applies to rows that are updated via the constraint view. Most constraint views are based on simple views, although certain complex views can also be used as constraint views. Constraint views are most often used as an easy way to enforce business rules in applications without the application developer doing any extra coding. Note: This approach applies views to ease of application coding rather than security. Views are possibly more applicable in client-server environments. Scalability issues may arise for large, very busy OLTP databases. Complex View. A complex view contains a query on more than one table. This type of view allows you to wrap complexities inside the view’s query so they are hidden from the user or application devel- oper. Complex views are most often used for simplifying end-user reporting by providing a table-like structure for users to query. For example, you could create a view that displays the CD title, artist name, and song title (which are found in three different tables). Complex views usually cannot be used to insert, update, or delete rows from the underlying tables. 19.3 CREATE VIEW Syntax Figure 19.1 shows the syntax of the CREATE VIEW statement. The same syntax applies to all types of views. The next three sections look at how to create each of the three types of views: simple, constraint, and complex. Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
428 19.3 CREATE VIEW Syntax Figure 19.1 CREATE VIEW Syntax Is Fairly Simple. 19.3.1 Creating Simple Views A simple view is the easiest type of view to create. CREATE VIEW USARTISTS AS SELECT ARTIST_ID, NAME, CITY, STATE_PROVINCE, ZIP FROM ARTIST WHERE COUNTRY = 'USA'; The view we have just created can be queried as if it were a table. When you execute a query on a view, the entire query contained within the view deﬁnition is executed to retrieve the columns and rows of the subquery, Then your query is applied to the view and the ﬁnal results are displayed. Query the view by executing these format commands and query. Figure 19.2 shows the result. COLUMN NAME FORMAT A20 COLUMN CITY FORMAT A15 COLUMN STATE_PROVINCE FORMAT A10 SELECT * FROM USARTISTS; You can use a view in a join as if it were another table. For example, you can list all U.S. artist names and their song titles with this query: SELECT NAME, TITLE FROM USARTISTS NATURAL JOIN SONG ORDER BY 1,2; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.3 CREATE VIEW Syntax 429 Figure 19.2 Querying a View Is Just Like Querying a Table. Now let’s take the simple view and add a constraint clause. The result will be a constraint view. 19.3.2 Creating Constraint Views A simple view usually allows you to update data in the underlying table through the view. You will examine this capability later in this chapter. There is a problem that sometimes crops up when using views to insert or update data: You can create a record that does not ﬁt the view’s query and therefore does not appear in the view. For example, imagine that you use the USARTISTS view to update the country from USA to Canada for one of the artists. You want to check the results, but querying the view no longer displays the record. It is as if the record disappeared after you updated it. Obviously, the record is in the table and simply is not displayed in the view. However, this fact may not be obvious to other users who are not familiar with the query that is used by the view. To prevent users from updating or inserting records not ﬁtting within the view, you create a constraint view. Another good reason to use a con- straint view is that it provides a form of security. Views are frequently used to limit a user’s access to certain rows and columns within the base table. The user should not be able to update rows not appearing in the view, but Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
430 19.3 CREATE VIEW Syntax without the constraint clause, this could happen and could be a violation of your business rules. Create a constraint view that looks like the simple view, except it includes the WITH CHECK OPTION clause, by running the following command: CREATE VIEW CONSTRAINT_USARTISTS AS SELECT ARTIST_ID, NAME, CITY, STATE_PROVINCE, ZIP, COUNTRY FROM ARTIST WHERE COUNTRY = 'USA' WITH CHECK OPTION CONSTRAINT AMERICANARTIST; You can leave out the “CONSTRAINT AMERICANARTIST” portion of the constraint clause. If you omit it, Oracle Database 10g will assign a system-generated name for the constraint. Next we insert a row for a Mexi- can artist: INSERT INTO CONSTRAINT_USARTISTS VALUES (ARTIST_ID_SEQ.NEXTVAL, 'Chrystal Perez', 'Mexico City', NULL, NULL, 'Mexico'); Figure 19.3 shows the result. The error message tells you that the row to be inserted has failed to comply with the WHERE clause of the view. You would get the same error if you tried to update one of the rows in the view with a country other than USA. Now, let’s look at some more interesting things you can do using com- plex views. 19.3.3 Creating Complex Views Complex views have more than one base table. Complex views include a wide variety of queries. Two common ones are views with joins and views with inline subqueries. 19.3.3.1 Views with Joins Let’s dive right in by creating a complex view that displays artist guest appearances and the instrument they played. CREATE VIEW INSTRUMENTS(ARTIST_NAME, INSTRUMENT) AS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.3 CREATE VIEW Syntax 431 Figure 19.3 Constraint Views Require Inserted Rows to Fit Inside the View. SELECT A.NAME, I.NAME FROM ARTIST A JOIN INSTRUMENTATION IA ON (IA.GUESTARTIST_ID = A.ARTIST_ID) JOIN INSTRUMENT I ON (IA.INSTRUMENT_ID = I.INSTRUMENT_ID); Now let’s look at the rows returned from the view issuing the following query. SELECT * FROM INSTRUMENTS; The view joins three tables and displays two columns of information. Notice the list of columns just after the view name. This is needed for the view because the two columns in the SELECT clause happen to have the same name. By listing different names for each of the two columns, the view can be created. Here is a view that summarizes an artist’s billing for studio time. CREATE VIEW ARTIST_MONTHLY_STATEMENT AS SELECT ARTIST_ID, NAME , TO_CHAR(DUE_DATE,'MON/YY') BILLING_MONTH , SUM(AMOUNT_CHARGED) DUE, SUM(AMOUNT_PAID) PAID , SUM(AMOUNT_CHARGED) - SUM(AMOUNT_PAID) BALANCE FROM ARTIST NATURAL JOIN STUDIOTIME GROUP BY ARTIST_ID, NAME, TO_CHAR(DUE_DATE,'MON/YY'); Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
432 19.3 CREATE VIEW Syntax This view shows how you can use grouping and functions in a view. In addition, notice that column aliases are used as a way to give the columns more appropriate names rather than using a column list in front of the query. Expressions, such as the columns with functions or group functions on them, must be given a valid name when the view is created. Imagine that you need to know which artists have balances over $500 for any month after 2000. The following query simpliﬁes the work required by selecting from a view: SELECT NAME, BALANCE, BILLING_MONTH FROM ARTIST_MONTHLY_STATEMENT WHERE BALANCE > 500 AND TO_DATE(BILLING_MONTH, 'MON/YY') > '31-DEC-2000' ORDER BY BALANCE DESC; Figure 19.4 shows the result of this query. The BILLING_MONTH column is converted to a date in the WHERE clause. This is needed because it was converted to a character ﬁeld in the view. If you did not con- vert it to a date, it would be compared as a character ﬁeld (alphabetically) when evaluating the WHERE clause. 19.3.3.2 Inline Subquery Views Another example of a complex view is one that contains a subquery. Sub- queries can be used in the SELECT, FROM, and WHERE clauses of a query. A view based on a query with a subquery in any of these SQL com- mand clause locations is valid. CREATE VIEW CD_SONGS AS SELECT M.MUSICCD_ID, M.TITLE, T.TRACK_SEQ_NO, (SELECT TITLE FROM SONG S WHERE T.SONG_ID = S.SONG_ID) SONG_TITLE FROM MUSICCD M JOIN CDTRACK T ON (M.MUSICCD_ID = T.MUSICCD_ID); The following script queries the view. Figure 19.5 shows the result. COLUMN TITLE FORMAT A25 COLUMN SONG_TITLE FORMAT A40 SELECT TITLE, TRACK_SEQ_NO, SONG_TITLE FROM CD_SONGS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.4 Changing and Dropping Views 433 Figure 19.4 A Complex Query Is Simpliﬁed with a View. WHERE TITLE='Soak Up the Sun' ORDER BY 1,2; 19.4 Changing and Dropping Views Syntax for changing and dropping views is as shown in Figure 19.6. Note that nearly all syntax for the ALTER VIEW command applies to con- straints. Constraints are covered in Chapter 20. What happens if you drop a table that is used in a view? The view becomes marked invalid and must be repaired. Sometimes you may have a new requirement from users that calls for a change in a view. Views are much easier to change than tables because they are generally nothing more than a stored query. Revise the stored query, and you have revised the view! To change a view, you revise the query and use the OR REPLACE option in the CREATE VIEW command, as in CREATE OR REPLACE VIEW. This assumes, of course, that constraint changes are not required. Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
434 19.4 Changing and Dropping Views Figure 19.5 Views Can Contain Subqueries of Their Own. The next view combines the SONG, CDTRACK, and MUSICCD tables to show the title of the CD along with details about each song on the CD. CREATE VIEW CD_DETAILS AS SELECT CD.TITLE CDTITLE, CD.PRESSED_DATE , CT.TRACK_SEQ_NO ,S.TITLE SONGTITLE, A.NAME , S.PLAYING_TIME FROM MUSICCD CD, CDTRACK CT, SONG S, ARTIST A WHERE CD.MUSICCD_ID = CT.MUSICCD_ID AND CT.SONG_ID = S.SONG_ID AND S.ARTIST_ID = A.ARTIST_ID ORDER BY 1, 3; This view has column aliases for the two TITLE columns (one in the MUSICCD table and the other in the SONG table); it has an ORDER BY clause; and it uses Oracle’s proprietary syntax for the join. Figure 19.6 ALTER VIEW and DROP VIEW Syntax. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.5 Working with Views 435 Imagine that the users who wanted this view for reporting asked you to add the playing time of the CD into the view. Revise the view by making changes requested to the original CREATE VIEW command and using CREATE OR REPLACE instead of CREATE. The following script high- lights changes: CREATE OR REPLACE VIEW CD_DETAILS AS SELECT CD.TITLE CDTITLE, CD.PRESSED_DATE , CD.PLAYING_TIME CD_TIME, CT.TRACK_SEQ_NO , S.TITLE SONGTITLE, A.NAME, S.PLAYING_TIME FROM MUSICCD CD, CDTRACK CT, SONG S, ARTIST A WHERE CD.MUSICCD_ID = CT.MUSICCD_ID AND CT.SONG_ID = S.SONG_ID AND S.ARTIST_ID = A.ARTIST_ID ORDER BY 1, 3; Note: You can create a brand-new view using CREATE OR REPLACE VIEW instead of CREATE VIEW. To drop a view, simply use the DROP VIEW command. The CD_DETAILS view can be dropped executing the following command: DROP VIEW CD_DETAILS; Dropping a view does not affect the base table or tables referenced by the view. 19.5 Working with Views Most views are used to query the base tables on which they are built. Some views are used to insert, update, and delete data in the base tables. The next sections show you how to query views and how to make changes to data using views. Following this section, we deal with Oracle Database metadata data dictionary views. 19.5.1 Querying a View A query on a view looks just like a query on a table. Behind the scenes, however, the Oracle Database 10g Optimizer merges the query that deﬁnes Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
436 19.5 Working with Views Figure 19.7 A Query Combined with the View’s Query. the view with the query that uses the view, into a single query. This query is parsed and stored in the shared SQL memory. Then the query is executed and the data retrieved. Figure 19.7 illustrates this activity. Let’s try some examples. Create the following view joining three tables to list a song and the artist, including any artists making guest appearances. CREATE VIEW ALLSONGS AS SELECT S.TITLE, A1.NAME ARTIST, GA.GUESTARTIST_ID FROM SONG S JOIN ARTIST A1 ON (S.ARTIST_ID = A1.ARTIST_ID) LEFT OUTER JOIN GUESTAPPEARANCE GA ON (S.SONG_ID = GA.SONG_ID); We would like to see the name of the artist making a guest appearance. The following query joins the view with the ARTIST table, which would become a very complex query without the view. The complexity is simply passed on to Oracle Database, potentially hurting performance in larger, busier environments. Figure 19.8 shows part of the result. SELECT V.ARTIST, V.TITLE, A.NAME GUEST FROM ALLSONGS V LEFT OUTER JOIN ARTIST A ON (V.GUESTARTIST_ID = A.ARTIST_ID) ORDER BY ARTIST; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.5 Working with Views 437 Figure 19.8 Joining Four Tables Is Easy When Three Are Joined in a View. 19.5.2 Views and DML Commands Although views have no data of their own, it is possible in certain cases to use views to modify data in the base table that is queried by the view. This can be very useful for tables for which the user does not have permission to access the base table but has access to a view. Oracle Database has rules that it tests against any view to determine whether it is inherently updatable. An inherently updatable view is one in which some or all of the view’s columns pass the test and can be used to update the base table. Some of the rules for simple views include the following: The view must not be created with the WITH READ ONLY clause. The view cannot contain GROUP BY, group functions, ORDER BY, or DISTINCT. The view cannot contain a subquery in the SELECT clause. The view must include the primary key and all NOT NULL col- umns, unless there are provisions (such as default values or a trigger) that plug values into the NOT NULL columns. Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
438 19.5 Working with Views The data dictionary table USER_UPDATABLE_COLUMNS lists each view and its columns, specifying whether the column can be refer- enced when updating, inserting, or deleting through the view. Execute this query to see which views are updatable, including their respective changeable columns. The query is shown in Figure 19.9, including the page size altered to show the CONSTRAINT_USARTISTS view and headings in the same image. COLUMN COLUMN_NAME FORMAT A20 SELECT TABLE_NAME, COLUMN_NAME, UPDATABLE, INSERTABLE , DELETABLE FROM USER_UPDATABLE_COLUMNS U WHERE EXISTS (SELECT VIEW_NAME FROM USER_VIEWS V WHERE U.TABLE_NAME = V.VIEW_NAME) ORDER BY 1, 2; Note: The complete syntax of the INSERT, UPDATE, and DELETE com- mands can be found in Chapter 15. The same syntax applies to both views and tables. Figure 19.9 The CONSTRAINT_ USARTISTS View Can Be Updated, Inserted into, and Deleted from. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.5 Working with Views 439 Now let’s continue and perform some inserts, updates, and deletes using the views USARTISTS and CONSTRAINT_USARTISTS created earlier in the chapter. Let’s say you have a new artist who will be using your studio to record her latest song. The following command will insert a row into the ARTIST table using the CONSTRAINT_USARTISTS view: INSERT INTO CONSTRAINT_USARTISTS VALUES (ARTIST_ID_SEQ.NEXTVAL, 'Judy Madrid', 'Madison' , 'WI','53887', 'USA'); When inserting or updating rows using a constraint view, like the CONSTRAINT_USARTISTS view, the new or modiﬁed row must still ﬁt within the view. In this case, because the WHERE clause of the view is WHERE COUNTRY='USA', that means the row’s COUNTRY column must be USA. Here is another important point about inserting data with a view: You can only insert values into the columns listed in the view. All other columns will be NULL or assigned a default value. In this example, the following columns will be NULL in the newly inserted row: STREET, POBOX, EMAIL, and INSTRUMENTS. Now try updating the row just inserted using the USARTISTS view: UPDATE USARTISTS SET ZIP = '53200' WHERE NAME = 'Judy Madrid'; Finally, delete the row using the CONSTRAINT_USARTISTS view: DELETE FROM CONSTRAINT_USARTISTS WHERE NAME = 'Judy Madrid'; Views that have columns made up of functions or other expressions can still be used to modify the base table. This view can illustrate that point: CREATE OR REPLACE VIEW SONG_VIEW AS SELECT SONG_ID, ARTIST_ID, TITLE, RECORDING_DATE , SUBSTR(PLAYING_TIME,1,1) MINUTES , SUBSTR(PLAYING_TIME,3) SECONDS FROM SONG; Chapter 19 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.