intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Oracle SQL Jumpstart with Examples- P8

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

Thêm vào BST
Báo xấu
82
lượt xem 11
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P8: 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- P8

  1. 320 15.2 Transaction Control Figure 15.3 A Transaction Is Set. your DML command to fail rather than wait if another user is updating the same row you try to update. Another common use is assigning large trans- actions to very large rollback segments. Figure 15.2 shows the syntax of the SET TRANSACTION command Let’s query an ARTIST row and start a read-only transaction using the following commands. SQL*Plus Worksheet displays “Transaction set.” in the lower pane. The result is shown in Figure 15.3. SELECT ARTIST_ID, NAME, ZIP FROM ARTIST WHERE NAME = 'Puddle of Mudd'; SET TRANSACTION READ ONLY; Now let’s try to change the zip code using the following script. UPDATE ARTIST SET ZIP='10099' WHERE NAME = 'Puddle of Mudd'; Figure 15.4 shows an error message. No changes can be made to the database inside a read-only transaction. In addition, a read-only transaction does not see changes to the database made by other users after the transac- tion starts. This might be useful when you are generating a set of reports that summarize data and must be consistent from beginning to end. For Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. 15.2 Transaction Control 321 Figure 15.4 Read-Only Transactions Prevent Database Changes. example, you run a summary of sales from the beginning of the year up to today and then (in the same transaction) run a detail report of sales activity. If users are updating the SALES table between your first and second reports, the two reports will not match. Use a read-only transaction to pre- serve the state of the database when you begin the first report. Note: Setting read-only transactions can cause serious concurrency issues for other users. Applications will not be able to respond properly when other users preserve data for exclusive use. This type of activity is inadvis- able because it could upset end users (your clients). The default transaction setting is READ WRITE, which allows changes and sees other users’ changes immediately after being committed. The current transaction can be completed using the COMMIT or ROLLBACK commands. Other options are transaction isolation levels, which can be set to SERI- ALIZABLE or READ COMMITTED. The default mode is ISOLATION LEVEL READ COMMITTED, where SQL will wait until any locks on data it wants to modify are released. Using the SET TRANSACTION ISO- LATION LEVEL SERIALIZABLE command, SQL commands handle locking differently. If a problem is encountered, the SERIALIZABLE option will cause a transaction to fail immediately without waiting. This can be useful in a batch job that runs overnight, where it is preferable to stop the entire batch job as opposed to risking the overnight job spilling over into daytime hours. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. 322 15.2 Transaction Control Note: Once again, be aware of conflict with concurrent applications and potentially upsetting clients. 15.2.3 The SAVEPOINT Command Another transaction-related command you may want to use is the SAVE- POINT command. The syntax is simply as follows, where the label implies a point within a transaction to undo changes back to: SAVEPOINT label; SAVEPOINT is useful when you are making many changes to the data- base and you want the ability to undo only part of the changes made. For example, you have inserted some testing rows into a table specifically to test an UPDATE command. You want to be able to undo the UPDATE com- mand while keeping the inserted rows. This way, you can repeat the UPDATE command. Demonstrating using the SAVEPOINT command, we can do the fol- lowing: Begin by updating a zip code and creating a target label (SAVE- POINT). Then make a different change to the same row already updated and query to see row changes. The result of the following script is shown in Figure 15.5. UPDATE ARTIST SET ZIP='10099' WHERE NAME = 'Puddle of Mudd'; SAVEPOINT AFTERUPDATE; UPDATE ARTIST SET NAME='Mud Puddle' WHERE NAME = 'Puddle of Mudd'; SELECT ARTIST_ID, NAME, ZIP FROM ARTIST WHERE NAME = 'Mud Puddle'; In the next script, we undo (rollback) the name change, done after the SAVEPOINT label, and query again. We see that the name change no longer exists, but the zip code is still changed. In other words, the first update is stored and the second is removed. The result of the following script is shown in Figure 15.6. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. 15.2 Transaction Control 323 Figure 15.5 Two Updates to the Same Row with a SAVEPOINT Label Between the Updates. ROLLBACK TO SAVEPOINT AFTERUPDATE; SELECT ARTIST_ID, NAME, ZIP FROM ARTIST WHERE NAME = 'Mud Puddle'; SELECT ARTIST_ID, NAME, ZIP FROM ARTIST WHERE NAME = 'Puddle of Mudd'; Finally, we can undo the remaining change from the first UPDATE command and end the transaction using a ROLLBACK command. The rest of this chapter deals with making changes to the database using DML commands to add, change, and remove data. We begin with the INSERT command. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 324 15.3 Adding Data (INSERT) Figure 15.6 Undo Changes Back to a SAVEPOINT Label. 15.3 Adding Data (INSERT) Adding new rows into a table is done with the INSERT command. The INSERT command can be used to add to a single table or multiple tables. The syntax of the single-table INSERT command is shown in Figure 15.7. You can insert one row into a table using expressions, individual subque- ries for each column, or a single subquery for all columns. For a single sub- query filling all columns, use a subquery that retrieves multiple rows instead of a list of literal values. We cover the multiple-table INSERT command shortly. The RETURNING portion of the INSERT, UPDATE, and DELETE statements is essentially PL/SQL (see Chapter 24) but is covered here as well for the sake of completeness. Note: Any literal value such as “hello” or the number 50,000 is an expres- sion. See Chapter 14 for more information on expressions. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. 15.3 Adding Data (INSERT) 325 Figure 15.7 Single Table INSERT Command Syntax. 15.3.1 Inserting One Row Let’s start with an easy example, adding a single row to the INSTRU- MENT table. INSERT INTO INSTRUMENT VALUES (INSTRUMENT_ID_SEQ.NEXTVAL ,(SELECT INSTRUMENT_ID FROM INSTRUMENT WHERE NAME = 'String') , 'Harp'); You do not need to list what value goes into which column if you list the values in the same order as columns appear in the table, and all table col- umns are filled. In this case, there are only three columns to worry about. The first column uses a sequence that generates a number that is used as the unique identifier for the instrument. See Chapter 22 for details on sequences. The NEXTVAL function always returns the next available value from a sequence. The second column finds the strings section in the INSTRUMENTS table, the same table. The third column adds a new instrument name. Here is an example in which you list the columns in a different order than they appear in the table, additionally omitting columns. INSERT INTO MUSICCD (MUSICCD_ID, TITLE, PLAYING_TIME) VALUES (MUSICCD_ID_SEQ.NEXTVAL, 'SPIDER-MAN','60:35'); Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. 326 15.3 Adding Data (INSERT) When you omit columns, Oracle Database 10g sets missing columns to null values except when a default value is defined for a column. In that case, Oracle fills the column with the default value. If you omit any non- nullable columns, which do not have a default value setting, then an error will result. 15.3.2 Inserting with a Subquery You can also insert a group of rows all at once using a subquery instead of a list of values. Each row returned by the subquery becomes a row inserted into the table. In this example, we create a table and insert rows using a subquery. CREATE TABLE TESTMUSICCD( TITLE VARCHAR2(32) , ARTIST_NAME VARCHAR2(32) NOT NULL , PRESSED_DATE DATE , ARTIST_COUNTRY VARCHAR2(32)); Now we use an INSERT statement to query the ARTIST and MUS- ICCD tables and load the resulting rows into the new table. INSERT INTO TESTMUSICCD SELECT DISTINCT M.TITLE, A.NAME, M.PRESSED_DATE , A.COUNTRY FROM ARTIST A , SONG S, CDTRACK T, MUSICCD M WHERE A.ARTIST_ID = S.ARTIST_ID AND S.SONG_ID = T.SONG_ID AND T.MUSICCD_ID = M.MUSICCD_ID; This INSERT command creates 13 rows at once. Figure 15.8 shows the new rows using the following simple query: SELECT * FROM TESTMUSICCD; The rows in the table have not yet been saved to the database. We could save them by executing a COMMIT command. And now that you have some data in a new table, you can experiment with updates and deletes. However, first let’s examine multiple-table inserts. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. 15.3 Adding Data (INSERT) 327 Figure 15.8 The New Rows Were Derived from a Subquery Join on Four Tables. 15.3.3 The Multiple-Table INSERT Command Figure 15.9 describes the syntax for the multiple-table form of the INSERT command. Now let’s look at an example, once again using the data warehouse SALES table as a basis. The following query shows a breakdown for the SALES table by retailer. Next, we use the SALES table to create three sepa- rate empty tables. Following that we insert rows into all of the three sepa- rate tables at once. The rows will originate from the SALES table, using a single multiple-table INSERT command, inserting into the three tables based on the retailer data in each row. The initial query is shown in Figure 15.10, showing the breakdown of the SALES table based on retailers (RETAILER_ID). SELECT (SELECT NAME FROM RETAILER WHERE RETAILER_ID = S.RETAILER_ID) "Retailer" , COUNT(S.RETAILER_ID) "Sales" FROM SALES S GROUP BY S.RETAILER_ID; Now we can create three empty tables from the SALES table. The WHERE clause using the ROWNUM < 1 condition is a simple method of copying the structure of the SALES table without copying any rows. See Top-N queries in Chapter 5. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. 328 15.3 Adding Data (INSERT) Figure 15.9 Multiple-Table INSERT Command Syntax. CREATE TABLE AMAZON AS SELECT * FROM SALES WHERE ROWNUM < 1; CREATE TABLE BANDN AS SELECT * FROM SALES WHERE ROWNUM < 1; CREATE TABLE CDSHOP AS SELECT * FROM SALES WHERE ROWNUM < 1; Figure 15.10 SALES Table Entries Are Distributed Among Three Different Retailers. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. 15.3 Adding Data (INSERT) 329 The following script is the multiple-table INSERT command, filling all three tables with the appropriate rows in the three new tables. In this case, an ELSE clause is not required, and the FIRST option can be used. INSERT FIRST WHEN RETAILER_ID = (SELECT RETAILER_ID FROM RETAILER WHERE NAME = 'Amazon') THEN INTO AMAZON WHEN RETAILER_ID = (SELECT RETAILER_ID FROM RETAILER WHERE NAME = 'Barnes and Noble') THEN INTO BANDN WHEN RETAILER_ID = (SELECT RETAILER_ID FROM RETAILER WHERE NAME = 'CD Shop') THEN INTO CDSHOP SELECT * FROM SALES; Figure 15.11 shows resulting table counts after the execution of the mul- tiple-table INSERT command, distributing sales entries to the three sepa- rate retailer tables. The correct row counts can be verified by comparing row counts between those shown in Figures 15.10 and 15.11. That covers the INSERT command and adding data. Let’s look at other DML commands, starting with the UPDATE command used to change existing data. Figure 15.11 SALES Table Entries Distributed into Three Separate Retailer Tables. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. 330 15.4 Changing Data (UPDATE) 15.4 Changing Data (UPDATE) The syntax for the UPDATE command is as shown in Figure 15.12. You can update all rows in the table by omitting the WHERE clause. List any or all column settings in the updated table after the SET keyword. Any subquery must be a single-row subquery. A subquery can be a corre- lated or regular subquery. Several UPDATE commands will be demon- strated in the next sections. Figure 15.12 UPDATE Command Syntax. Use the NULL keyword to set a column to a null value. Use the DEFAULT keyword to set a column to its default value (as defined in the table). 15.4.1 Updating One Row You find out that Jewel now lives in Brazil, so you update the row contain- ing Jewel’s data. Note that we are using the TESTMUSICCD table created in the previous section on the INSERT command. UPDATE TESTMUSICCD SET ARTIST_COUNTRY='Brazil' WHERE ARTIST_NAME = 'Jewel'; SQL*Plus Worksheet will reply, “1 row updated.” The same syntax can be used to update more than one row. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. 15.4 Changing Data (UPDATE) 331 15.4.2 Updating Many Rows There are three rows with the name and country of Sheryl Crow because there are three of her CDs in the table we created. Update all three at once, changing her country to Canada. UPDATE TESTMUSICCD SET ARTIST_COUNTRY='Canada' WHERE ARTIST_NAME = 'Sheryl Crow'; SQL*Plus Worksheet will reply, “3 rows updated.” Another method of updating data is to use subqueries. For example, let’s say you want to update ARTIST_COUNTRY column values in TEST- MUSICCD with data from the ARTIST table. You can use a correlated subquery to match the artist’s name between the ARTIST and TESTMUS- ICCD tables to find the country. The following query removes the changes to countries of residence for both Jewel and Sheryl Crow. UPDATE TESTMUSICCD T SET ARTIST_COUNTRY= (SELECT COUNTRY FROM ARTIST A WHERE A.NAME = T.ARTIST_NAME); SQL*Plus Worksheet will reply, “13 rows updated.” Note: Updated rows must comply with any constraints defined for a table. If one row does not comply, all rows updated by the statement are automat- ically rolled back. You can also update more than one column, whether you are updating one row or many rows. For example, change the title and the country with one update command. In the next example, we change the ARTIST_NAME column of each TESTMUSICCD table row to uppercase using a function, and change the PRESSED_DATE using a correlated sub- query that finds the most recent RECORDING_DATE from the songs on the CD (TESTMUSICCD table). You also use a WHERE clause in the UPDATE command so that you only update Sheryl Crow’s three rows. UPDATE TESTMUSICCD T SET ARTIST_NAME=UPPER(ARTIST_NAME), PRESSED_DATE = (SELECT MAX(RECORDING_DATE) Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. 332 15.4 Changing Data (UPDATE) FROM SONG S, CDTRACK C, MUSICCD M WHERE M.TITLE = T.TITLE AND M.MUSICCD_ID = C.MUSICCD_ID AND C.SONG_ID = S.SONG_ID) WHERE ARTIST_NAME = 'Sheryl Crow'; SQL*Plus Worksheet will reply, “3 rows updated.” Let’s illustrates several points about the UPDATE command: The data in the current row is available for use, so you can update a value using itself or other values in the current row. This refers to the correlating column alias called T.TITLE shown in the previous query, passed from the calling query to the subquery. The WHERE clause (in the UPDATE command) can reference col- umns that are updated, using the value before the update. You can use a mixture of literals, subqueries, and functions in the same UPDATE command. Figure 15.13 Sheryl Crow Is Now Uppercase as a Result of an UPDATE Command. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. 15.5 Deleting Data (DELETE) 333 Figure 15.13 shows the result of all changes made using the UPDATE command, using the following query against the TESTMUSICCD table. COLUMN ARTIST_NAME FORMAT A20; COLUMN ARTIST_COUNTRY FORMAT A10; SELECT ARTIST_NAME, ARTIST_COUNTRY, PRESSED_DATE FROM TESTMUSICCD; The rows updated in the table have not yet been saved to the database. They could be saved using the COMMIT command. Removing rows using the DELETE command is easier than inserting and updating rows. 15.5 Deleting Data (DELETE) The syntax for the DELETE command is as shown in Figure 15.14. As with the UPDATE command, use the WHERE clause to delete selected rows from a table, and omit the WHERE clause to delete all the rows in a table. Figure 15.14 DELETE Statement Syntax. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. 334 15.5 Deleting Data (DELETE) 15.5.1 Deleting One Row Using the WHERE clause, you can specify one row when deleting. In the TESTMUSICCD table, delete the row for the “C’mon, C’mon” CD by typing this DELETE command: DELETE FROM TESTMUSICCD WHERE TITLE = 'C''mon, C''mon'; SQL*Plus Worksheet will reply: “1 row deleted.” Notice the use of quotation marks in the title. The title has two single quotes in it where the data actually has a single quote. This is called a string escape sequence. Because Oracle Database 10g uses single quote marks to delimit literal values, you must indicate that the single quote in the middle is not a delimiter by typing two single quote marks together. Remember that two single quotes are not the same as one double quotation mark. 15.5.2 Deleting Many Rows Just like the UPDATE command, simply revising the WHERE clause to select more rows enables you to delete multiple rows in one command. For example, deleting all CDs by the Goo Goo Dolls can be accomplished using the following command: DELETE FROM TESTMUSICCD WHERE ARTIST_NAME = 'Goo Goo Dolls'; SQL*Plus Worksheet will reply: “2 rows deleted.” The following query will show that rows for the Goo Goo Dolls and for the CD named “C’mon, C’mon” are no longer in the table. Figure 15.15 shows the result. SELECT * FROM TESTMUSICCD; If a table is the parent of another table, such as the MUSICCD table, which is the parent to the CDTRACK table, you cannot delete a row in the MUSICCD table that has related child rows (CD tracks) in the CDTRACK table. You should remove the child rows first and the parent row last. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. 15.5 Deleting Data (DELETE) 335 Figure 15.15 Three Rows Were Deleted by Two DELETE Commands. Note: This is not always strictly true if CASCADE DELETE is used with constraints. See Chapter 1 for details on Referential Integrity and Chapter 20 for information on constraints. 15.5.3 Deleting All Rows You can delete all the rows in a table by leaving out the WHERE clause. The following command will delete all rows in the TESTMUSICCD table: DELETE FROM TESTMUSICCD; SQL*Plus Worksheet will reply, “8 rows deleted.” You could also finally remove the temporarily created table TESTMUS- ICCD by dropping it. DROP TABLE TESTMUSICCD; The final section in this chapter discusses the MERGE command, a new feature of Oracle Database 9i and much improved in Oracle Database 10g. Chapter 15 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. 336 15.6 Merging New and Old Data (MERGE) The MERGE command enables a combination insert and update to a table using a single DML command. 15.6 Merging New and Old Data (MERGE) There are some enhancements to the MERGE command between Oracle Database 9i and Oracle Database 10g. The purpose of the MERGE com- mand is to allow you to build on an already existing table’s rows. For exam- ple, you have a central database that tracks contact information for clients. Your salespeople have handheld palmtop units that they use to record con- tact information for new and existing clients. The palmtop’s client table has only half the data for existing customers, because that is all the salespeople need in the field. When salespeople return to the central office, they plug in their palmtops and dump the data about all their clients. The central com- puter must determine whether the client is new or already existing in the central database client table. Then, if it is new, a row is inserted. If it already exists, the existing row is updated, preserving the data in columns that are not provided in the palmtop record. In the past, a merging or migration process would have required an application program, perhaps even custom coding and scripting. Now, you can use the MERGE command to handle these issues. Figure 15.16 shows Figure 15.16 Merge Looks Complex but Has Familiar Components. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. 15.6 Merging New and Old Data (MERGE) 337 the syntax of the MERGE command, including updates for Oracle Data- base 10g. As you can see, there are two tables used in a MERGE command: (1) the target table, which receives the inserts and updates, and (2) the source table, which is used to determine whether to insert or update the target table. Usually, the source table provides the data to be inserted or updated in the target table, but you can also provide literal values, expressions, and so on. 15.6.1 How To Use MERGE Use the MERGE command when you need to handle ongoing inserts and updates into a table. For an easy example of the MERGE command, first create a new table that is an exact copy of the STUDIOTIME table, but contains only nine of the rows. This simulates a situation where a copy of the STUDIOTIME table was created at the end of the year 2000. CREATE TABLE HISTORY_STUDIOTIME AS SELECT * FROM STUDIOTIME WHERE SESSION_DATE
  19. 338 15.6 Merging New and Old Data (MERGE) Looking closely at the previous statement, observe these points: The target table (the one receiving rows) is the HISTORY_STUDIOTIME table. The source table (the one sending rows) is the STUDIOTIME table. The two tables are matched on the value of the STUDIOTIME_ID. When both tables contain a row with matching STUDIOTIME_ID, three columns in the HISTORY_STUDIOTIME table are updated with values from columns in the STUDIOTIME table. When only the STUDIOTIME table has a row, and there is no matching row in the HISTORY_STUDIOTIME table, a row is inserted into the HISTORY_STUDIOTIME table using values in the STUDIOTIME table’s row. The MERGE command can be very useful in situations that otherwise would require separate INSERT and UPDATE commands. Note: Rows in the target table that do not match those in the source table are not affected by a MERGE command. This chapter has covered the Data Manipulation Language (DML), comprising commands to change data in tables in an Oracle database. We have covered the INSERT, UPDATE, DELETE, and MERGE commands. The next chapter examines datatypes in detail. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. 16 Datatypes and Collections In this chapter: What are datatypes? What are simple datatypes? What are complex datatypes? What is a user-defined datatype? What are object datatypes? What types of object collection functionality exist? What are special Oracle datatypes? This chapter examines simple, complex, and object datatypes. Addition- ally, this chapter includes user-defined datatypes plus details of special object datatype functions. Object functions are included in this chapter because they are specific to object datatypes. Object functions do not belong with single-row functions in Chapter 9 or with grouping functions in Chapter 11. Like Chapter 7, this chapter contains some information found in other chapters, but it also contains new information. It is necessary to place this information in a single chapter in order to put everything in one place. Let’s begin with what could be termed simple datatypes. 16.1 Simple Datatypes I like to classify simple datatypes as those containing single scalar values, such as strings, numbers, and dates. Table 16.1 shows a summary of Oracle simple datatypes. 339 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD


ERROR:connection to 10.20.1.100:9315 failed (errno=111, msg=Connection refused)
ERROR:connection to 10.20.1.100:9315 failed (errno=111, msg=Connection refused)
THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2