?? ch11.htm
字號(hào):
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 11 -- Controlling Transactions</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../ch10/ch10.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch12/ch12.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 11 -<BR>Controlling Transactions</FONT></H1></CENTER><P>You have spent the last 10 days learning virtually everything that you can dowith data within a relational database. For example, you know how to use the SQL<TT>SELECT</TT> statement to retrieve data from one or more tables based on a numberof conditions supplied by the user. You have also had a chance to use data modificationstatements such as <TT>INSERT</TT>, <TT>UPDATE</TT>, and <TT>DELETE</TT>. As of today,you have become an intermediate-level SQL and database user. If required, you couldbuild a database with its associated tables, each of which would contain severalfields of different data types. Using proper design techniques, you could leveragethe information contained within this database into a powerful application.<H2><FONT COLOR="#000077">Objectives</FONT></H2><P>If you are a casual user of SQL who occasionally needs to retrieve data from adatabase, the topics of the first 10 days provide most of the information you willneed. However, if you intend to (or are currently required to) develop a professionalapplication using any type of relational database, the advanced topics covered overthe next four days--transaction control, security, embedded SQL programming, anddatabase procedures--will help you a great deal. We begin with transaction control.By the end of the day, you will know the following:<UL> <LI>The basics of transaction control <P> <LI>How to finalize and or cancel a transaction <P> <LI>Some of the differences between Sybase and Oracle transactions</UL><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We used both Personal Oracle7 and Sybase's SQL Server to generate today's examples. Please see the documentation for your specific SQL implementation for any minor differences in syntax. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Transaction Control</FONT></H2><P>Transaction control, or transaction management, refers to the capability of arelational database management system to perform database transactions. Transactionsare units of work that must be done in a logical order and successfully as a groupor not at all. The term unit of work means that a transaction has a beginning andan end. If anything goes wrong during the transaction, the entire unit of work canbe canceled if desired. If everything looks good, the entire unit of work can besaved to the database.</P><P>In the coming months or years you will probably be implementing applications formultiple users to use across a network. Client/server environments are designed specificallyfor this purpose. Traditionally, a server (in this case, a database server) supportsmultiple network connections to it. As often happens with technology, this newfoundflexibility adds a new degree of complexity to the environment. Consider the bankingapplication described in the next few paragraphs.<H2><FONT COLOR="#000077">The Banking Application</FONT></H2><P>You are employed by First Federal Financial Bank to set up an application thathandles checking account transactions that consist of debits and credits to customers'checking accounts. You have set up a nice database, which has been tested and verifiedto work correctly. After calling up your application, you verify that when you take$20 out of the account, $20 actually disappears from the database. When you add $50.25to the checking account, this deposit shows up as expected. You proudly announceto your bosses that the system is ready to go, and several computers are set up ina local branch to begin work.</P><P>Within minutes, you notice a situation that you did not anticipate: As one telleris depositing a check, another teller is withdrawing money from the same account.Within minutes, many depositors' balances are incorrect because multiple users areupdating tables simultaneously. Unfortunately, these multiple updates are overwritingeach other. Shortly thereafter, your application is pulled offline for an overhaul.We will work through this problem with a database called <TT>CHECKING</TT>. Withinthis database are two tables, shown in Tables 11.1 and 11.2.<H4><FONT COLOR="#000077">Table 11.1. The CUSTOMERS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Name</B></TD> <TD ALIGN="LEFT"><B>Address</B></TD> <TD ALIGN="LEFT"><B>City</B></TD> <TD ALIGN="LEFT"><B>State</B></TD> <TD ALIGN="LEFT"><B>Zip</B></TD> <TD ALIGN="LEFT"><B>Customer_ID</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Bill Turner</TD> <TD ALIGN="LEFT">725 N. Deal Parkway</TD> <TD ALIGN="LEFT">Washington</TD> <TD ALIGN="LEFT">DC</TD> <TD ALIGN="LEFT">20085</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">John Keith</TD> <TD ALIGN="LEFT">1220 Via De Luna Dr.</TD> <TD ALIGN="LEFT">Jacksonville</TD> <TD ALIGN="LEFT">FL</TD> <TD ALIGN="LEFT">33581</TD> <TD ALIGN="LEFT">2</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Mary Rosenberg</TD> <TD ALIGN="LEFT">482 Wannamaker Avenue</TD> <TD ALIGN="LEFT">Williamsburg</TD> <TD ALIGN="LEFT">VA</TD> <TD ALIGN="LEFT">23478</TD> <TD ALIGN="LEFT">3</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">David Blanken</TD> <TD ALIGN="LEFT">405 N. Davis Highway</TD> <TD ALIGN="LEFT">Greenville</TD> <TD ALIGN="LEFT">SC</TD> <TD ALIGN="LEFT">29652</TD> <TD ALIGN="LEFT">4</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Rebecca Little</TD> <TD ALIGN="LEFT">7753 Woods Lane</TD> <TD ALIGN="LEFT">Houston</TD> <TD ALIGN="LEFT">TX</TD> <TD ALIGN="LEFT">38764</TD> <TD ALIGN="LEFT">5</TD> </TR></TABLE><H4><FONT COLOR="#000077">Table 11.2. The BALANCES table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Average_Bal</B></TD> <TD ALIGN="LEFT"><B>Curr_Bal</B></TD> <TD ALIGN="LEFT"><B>Account_ID</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1298.53</TD> <TD ALIGN="LEFT">854.22</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">5427.22</TD> <TD ALIGN="LEFT">6015.96</TD> <TD ALIGN="LEFT">2</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">211.25</TD> <TD ALIGN="LEFT">190.01</TD> <TD ALIGN="LEFT">3</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">73.79</TD> <TD ALIGN="LEFT">25.87</TD> <TD ALIGN="LEFT">4</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1285.90</TD> <TD ALIGN="LEFT">1473.75</TD> <TD ALIGN="LEFT">5</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1234.56</TD> <TD ALIGN="LEFT">1543.67</TD> <TD ALIGN="LEFT">6</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">345.25</TD> <TD ALIGN="LEFT">348.03</TD> <TD ALIGN="LEFT">7</TD> </TR></TABLE></P><P>Assume now that your application program performs a <TT>SELECT</TT> operationand retrieves the following data for Bill Turner:</P><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF">NAME: Bill TurnerADDRESS: 725 N. Deal ParkwayCITY: WashingtonSTATE: DCZIP: 20085CUSTOMER_ID: 1</FONT></PRE><P>While this information is being retrieved, another user with a connection to thisdatabase updates Bill Turner's address information:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>UPDATE CUSTOMERS SET Address = "11741 Kingstowne Road" WHERE Name = "Bill Turner";</B></FONT></PRE><P>As you can see, the information you retrieved earlier could be invalid if theupdate occurred during the middle of your <TT>SELECT</TT>. If your application firedoff a letter to be sent to Mr. Bill Turner, the address it used would be wrong. Obviously,if the letter has already been sent, you won't be able to change the address. However,if you had used a transaction, this data change could have been detected, and allyour other operations could have been rolled back.<H2><FONT COLOR="#000077">Beginning a Transaction</FONT></H2><P>Transactions are quite simple to implement. You will examine the syntax used toperform transactions using the Oracle RDBMS SQL syntax as well as the Sybase SQLServer SQL syntax.</P><P>All database systems that support transactions must have a way to explicitly tellthe system that a transaction is beginning. (Remember that a transaction is a logicalgrouping of work that has a beginning and an end.) Using Personal Oracle7, the syntaxlooks like this:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SET TRANSACTION {READ ONLY | USE ROLLBACK SEGMENT segment}</FONT></PRE><P>The SQL standard specifies that each database's SQL implementation must supportstatement-level read consistency; that is, data must stay consistent while one statementis executing. However, in many situations data must remain valid across a singleunit of work, not just within a single statement. Oracle enables the user to specifywhen the transaction will begin by using the <TT>SET TRANSACTION</TT> statement.If you wanted to examine Bill Turner's information and make sure that the data wasnot changed, you could do the following:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION READ ONLY;</B>SQL> <B>SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner';</B><B>---Do Other Operations---</B>SQL> <B>COMMIT;</B></FONT></PRE><P>We discuss the <TT>COMMIT</TT> statement later today. The <TT>SET TRANSACTIONREAD ONLY</TT> option enables you to effectively lock a set of records until thetransaction ends. You can use the <TT>READ ONLY</TT> option with the following commands:</P><P><TT>SELECT</TT></P><P><TT>LOCK TABLE</TT></P><P><TT>SET ROLE</TT></P><P><TT>ALTER SESSION</TT></P><P><TT>ALTER SYSTEM</TT></P><P><BR>The option <TT>USE ROLLBACK SEGMENT</TT> tells Oracle which database segment to usefor rollback storage space. This option is an Oracle extension to standard SQL syntax.Consult your Oracle documentation for more information on using segments to maintainyour database.</P><P>SQL Server's Transact-SQL language implements the <TT>BEGIN TRANSACTION</TT> commandwith the following syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">begin {transaction | tran} [transaction_name]</FONT></PRE><P>This implementation is a little different from the Oracle implementation. (Sybasedoes not allow you to specify the <TT>READ ONLY</TT> option.) However, Sybase doesallow you to give a transaction a name, as long as that transaction is the outermostof a set of nested transactions.</P><P>The following group of statements illustrates the use of nested transactions usingSybase's Transact-SQL language:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1><B> begin transaction new_account</B>2> <B>insert CUSTOMERS values ("Izetta Parsons", "1285 Pineapple Highway", "Greenville", "AL" 32854, 6)</B>3> <B>if exists(select * from CUSTOMERS where Name = "Izetta Parsons")</B>4> <B>begin</B>5> <B>begin transaction</B>6> <B>insert BALANCES values(1250.76, 1431.26, 8)</B>7> <B>end</B>8> <B>else</B>9> <B>rollback transaction</B>10> <B>if exists(select * from BALANCES where Account_ID = 8)</B>11> <B>begin</B>12> <B>begin transaction</B>13> <B>insert ACCOUNTS values(8, 6)</B>14> <B>end</B>15> <B>else</B>16> <B>rollback transaction</B>17> <B>if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)</B>18> <B>commit transaction</B>19><B> else</B>20> <B>rollback transaction</B>21> <B>go</B></FONT></PRE><P>For now, don't worry about the <TT>ROLLBACK TRANSACTION</TT> and <TT>COMMIT TRANSACTION</TT>statements. The important aspect of this example is the nested transaction--or atransaction within a transaction.</P><P>Notice that the original transaction (<TT>new_account</TT>) begins on line 1.After the first insert, you check to make sure the <TT>INSERT</TT> was executed properly.Another transaction begins on line 5. This transaction within a transaction is termeda <I>nested transaction</I>.</P><P>Other databases support the <TT>AUTOCOMMIT</TT> option. This option can be usedwith the <TT>SET</TT> command. For example:</P><PRE><FONT COLOR="#0066FF">SET AUTOCOMMIT [ON | OFF]</FONT></PRE><P>By default, the <TT>SET AUTOCOMMIT ON</TT> command is executed at startup. Ittells SQL to automatically commit all statements you execute. If you do not wantthese commands to be automatically executed, set the <TT>AUTOCOMMIT</TT> option tooff:</P><PRE><FONT COLOR="#0066FF">SET AUTOCOMMIT OFF</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Check your database system's documentation to determine how you would begin a transaction. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Finishing a Transaction</FONT></H2><P>The Oracle syntax to end a transaction is as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">COMMIT [WORK][ COMMENT 'text'| FORCE 'text' [, integer] ] ;</FONT></PRE><P>Here is the same command using Sybase syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">COMMIT (TRANSACTION | TRAN | WORK) (TRANSACTION_NAME)</FONT></PRE><P>The <TT>COMMIT</TT> command saves all changes made during a transaction. Executinga <TT>COMMIT</TT> statement before beginning a transaction ensures that no errorswere made and no previous transactions are left hanging.</P><P>The following example verifies that the <TT>COMMIT</TT> command can be used byitself without receiving an error back from the database system.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>COMMIT;</B>SQL> <B>SET TRANSACTION READ ONLY;</B>SQL> <B>SELECT * FROM CUSTOMERS</B> <B>WHERE NAME = 'Bill Turner';</B>---Do Other Operations---SQL> <B>COMMIT;</B></FONT></PRE><P>An Oracle SQL use of the <TT>COMMIT</TT> statement would look like this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION;</B>SQL> <B>INSERT INTO CUSTOMERS VALUES</B> <B>("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7);</B>SQL> <B>COMMIT;</B>SQL> <B>SELECT * FROM CUSTOMERS;</B></FONT></PRE><H4><FONT COLOR="#000077">The CUSTOMERS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Name</B></TD> <TD ALIGN="LEFT"><B>Address</B></TD> <TD ALIGN="LEFT"><B>City</B></TD> <TD ALIGN="LEFT"><B>State</B></TD> <TD ALIGN="LEFT"><B>Zip</B></TD> <TD ALIGN="LEFT"><B>Customer_ID</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Bill Turner</TD> <TD ALIGN="LEFT">725 N. Deal Parkway</TD> <TD ALIGN="LEFT">Washington</TD> <TD ALIGN="LEFT">DC</TD> <TD ALIGN="LEFT">20085</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">John Keith</TD> <TD ALIGN="LEFT">1220 Via De Luna Dr.</TD> <TD ALIGN="LEFT">Jacksonville</TD> <TD ALIGN="LEFT">FL</TD> <TD ALIGN="LEFT">33581</TD> <TD ALIGN="LEFT">2</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Mary Rosenberg</TD> <TD ALIGN="LEFT">482 Wannamaker Avenue</TD> <TD ALIGN="LEFT">Williamsburg</TD> <TD ALIGN="LEFT">VA</TD> <TD ALIGN="LEFT">23478</TD> <TD ALIGN="LEFT">3</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">David Blanken</TD> <TD ALIGN="LEFT">405 N. Davis Highway</TD> <TD ALIGN="LEFT">Greenville</TD>?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -