?? ch17.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 17 -- Using SQL to Generate SQL Statements</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="../ch16/ch16.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch18/ch18.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 17 -<BR>Using SQL to Generate SQL Statements</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Today you learn the concepts behind generating one or more SQL statements froma query. By the end of the day you should understand the following:<UL> <LI>The benefits of generating SQL statements from a query <P> <LI>How to make the output from a query appear in the form of another SQL statement <P> <LI>How to use the data dictionary, database tables, or both to form SQL statements</UL><H2><FONT COLOR="#000077">The Purpose of Using SQL to Generate SQL Statements</FONT></H2><P>Generating SQL from another SQL statement simply means writing an SQL statementwhose output forms another SQL statement or command. Until now, all the SQL statementsthat you have learned to write either do something, such as manipulate the data ina table, one row at a time, or produce some kind of report from a query. Today youlearn how to write a query whose output forms another query or SQL statement.</P><P>Why you would ever need to produce an SQL statement from a query? Initially, itis a matter of simplicity and efficiency. You may never need<B> </B>to produce anSQL statement, but without ever doing so you would be ignoring one of SQL's mostpowerful features, one that too many people do not realize exists.</P><P>Generating SQL is rarely mandatory because you can manually create and issue allSQL statements, although the process can be tedious in certain situations. On thesame note generating SQL statements may be necessary when you have a tight deadline.For example, suppose your boss wants to grant access on a new table to all 90 usersin the marketing department (and you want to get home for dinner). Because some usersof this database do not work in marketing, you cannot simply grant access on thetable to public. When you have multiple groups of users with different types of access,you may want to enforce role security, which is a built-in method for controllinguser access to data. In this situation you can create an SQL statement that generates<TT>GRANT</TT> statements to all individuals in the marketing department; that is,it grants each individual the appropriate role(s).</P><P>You will find many situations in which it is advantageous to produce an SQL statementas output to another statement. For example, you might need to execute many similarSQL statements as a group or you might need to regenerate DDL from the data dictionary.When producing SQL as output from another statement, you will always get the datafor your output from either the data dictionary or the schema tables in the database.Figure 17.1 illustrates this procedure.</P><P>As you can see in Figure 17.1, a <TT>SELECT</TT> statement can be issued to thedatabase, drawing its output results either from the data dictionary or from applicationtables in the database. Your statement can arrange the retrieved data into one ormore SQL statements. For instance, if one row is returned, you will have generatedone SQL statement. If 100 rows are returned from your statement, then you will havegenerated 100 SQL statements. When you successfully generate SQL code from the database,you can run that code against the database, which may perform a series of queriesor database actions.</P><P>The remainder of the day is devoted to examples that show you how to produce outputin the form of SQL statements. Most of your information will come from the data dictionary,so you may want to review yesterday's material. (See Day 16, "Using Views toRetrieve Useful Information from the Data Dictionary.")</P><P><A NAME="01"></A><A HREF="01.htm"><B>Figure 17.1.</B></A></P><P><I>The process of generating SQL from the database.</I></P><BLOCKQUOTE> <P><HR><BR> <FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Today's examples use Personal Oracle7. As always, you should apply the concepts discussed today to the syntax of your specific database implementation. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Miscellaneous SQL*Plus Commands</FONT></H2><P>Today's examples use a few new commands. These commands, known as SQL*Plus commands,are specific to Personal Oracle7 and control the format of your output results. (SeeDay 20, "SQL*Plus.") SQL*Plus commands are issued at the <TT>SQL></TT>prompt, or they can be used in a file.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Although these commands are specific to Oracle, similar commands are available in other implementations, for example, Transact-SQL. (Also see Day 19, "Transact-SQL: An Introduction.") <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">set echo on/off</FONT></H3><P>When you <TT>set echo on</TT>, you will see your SQL statements as they execute.<TT>Set echo off</TT> means that you do not want to see your SQL statements as theyexecute--you just want to see the output.</P><PRE><FONT COLOR="#0066FF">SET ECHO [ ON | OFF ]</FONT></PRE><H3><FONT COLOR="#000077">set feedback on/off</FONT></H3><P>Feedback is the row count of your output. For instance, if you executed a <TT>SELECT</TT>statement that returned 30 rows of data, your feedback would be</P><PRE><FONT COLOR="#0066FF">30 rows selected.</FONT></PRE><P><TT>SET FEEDBACK ON</TT> displays the row count; <TT>SET FEEDBACK OFF</TT> eliminatesthe row count from your output.</P><PRE><FONT COLOR="#0066FF">SET FEEDBACK [ ON | OFF ]</FONT></PRE><H3><FONT COLOR="#000077">set heading on/off</FONT></H3><P>The headings being referred to here are the column headings in the output of a<TT>SELECT</TT> statement, such as <TT>LAST_NAME</TT> or <TT>CUSTOMER_ID</TT>. <TT>SETHEADING ON</TT>, which is the default, displays the column headings of your dataas a part of the output. <TT>SET HEADING OFF</TT>, of course, eliminates the columnheadings from your output.</P><PRE><FONT COLOR="#0066FF">SET HEADING [ ON | OFF ]</FONT></PRE><H3><FONT COLOR="#000077">spool filename/off</FONT></H3><P>Spooling is the process of directing the results of your query to a file. In orderto open a spool file, you enter</P><PRE><FONT COLOR="#0066FF"><B>spool</B> filename</FONT></PRE><P>To close your spool file, you would type</P><PRE><FONT COLOR="#0066FF"><B>spool off</B></FONT></PRE><H4><FONT COLOR="#000077">start <TT>filename</TT></FONT></H4><P>Most SQL commands that we have covered so far have been issued at the <TT>SQL></TT>prompt. Another method for issuing SQL statements is to create and then execute afile. In SQL*Plus the command to execute an SQL file is <TT>START FILENAME</TT>.</P><PRE><FONT COLOR="#0066FF">START FILENAME</FONT></PRE><H4><FONT COLOR="#000077">ed <TT>filename</TT></FONT></H4><P><TT>ED</TT> is a Personal Oracle7 command that opens a file (existing or file).When you open a file with <TT>ed</TT>, you are using a full-screen editor, whichis often easier than trying to type a lengthy SQL statement at the <TT>SQL></TT>prompt. You will use this command to modify the contents of your spool file. Youwill find that you use this command often when generating SQL script because youmay have to modify the contents of the file for customization. However, you can achievemost customization through SQL*Plus commands.</P><P><FONT COLOR="#0066FF"><TT>ED FILENAME</TT></FONT><H2><FONT COLOR="#000077">Counting the Rows in All Tables</FONT></H2><P>The first example shows you how to edit your spool file to remove irrelevant linesin your generated code, thus allowing your SQL statement to run without being tarnishedwith syntax errors.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Take note of the editing technique used in this example because we will not show the step in the rest of today's examples. We assume that you know the basic syntax of SQL statements by now. In addition, you may choose to edit your spool file in various ways. <HR></BLOCKQUOTE><P>Start by recalling the function to count all rows in a table: <TT>COUNT(*)</TT>.You already know how to select a count on all rows in a single table. For example:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SELECT COUNT(*)FROM TBL1;</FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">COUNT(*)-------- 29</FONT></PRE><P>That technique is handy, but suppose you want to get a row count on all tablesthat you own or that are in your schema. For example, here's a list of the tablesyou own:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT * FROM CAT;</B>TABLE_NAME TABLE_TYPE------------------------------ -----------ACCT_PAY TABLEACCT_REC TABLECUSTOMERS TABLEEMPLOYEES TABLEHISTORY TABLEINVOICES TABLEORDERS TABLEPRODUCTS TABLEPROJECTS TABLEVENDORS TABLE10 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>If you want to get a row count on all your tables, you could manually issue the<TT>COUNT(*)</TT> statement on each table. The feedback would be</P><PRE><FONT COLOR="#0066FF">10 rows selected.</FONT></PRE><P>The following <TT>SELECT</TT> statement creates more <TT>SELECT</TT> statementsto obtain a row count on all the preceding tables.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>SQL> <B>SET FEEDBACK OFF</B>SQL> <B>SET HEADING OFF</B>SQL> <B>SPOOL CNT.SQL</B>SQL> <B>SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'</B> 2 <B>FROM CAT</B> 3 <B>/</B>SELECT COUNT(*) FROM ACCT_PAY;SELECT COUNT(*) FROM ACCT_REC;SELECT COUNT(*) FROM CUSTOMERS;SELECT COUNT(*) FROM EMPLOYEES;SELECT COUNT(*) FROM HISTORY;SELECT COUNT(*) FROM INVOICES;SELECT COUNT(*) FROM ORDERS;SELECT COUNT(*) FROM PRODUCTS;SELECT COUNT(*) FROM PROJECTS;select count(*) FROM VENDORS;</FONT></PRE><H5>ANALYSIS:</H5><P>The first action in the preceding example is to use some SQL*Plus commands. Setting<TT>echo off</TT>, <TT>feedback off</TT>, and <TT>heading off</TT> condenses theoutput to what is actually being selected. Remember, the output is not being usedas a report, but rather as an SQL statement that is ready to be executed. The nextstep is to use the <TT>SPOOL</TT> command to direct the output to a file, which isspecified as <TT>cnt.sql</TT>. The final step is to issue the <TT>SELECT</TT> statement,which will produce output in the form of another statement. Notice the use of singlequotation marks to select a literal string. The combination of single quotation marksand the concatenation (<TT>||</TT>) allows you to combine actual data and literalstrings to form another SQL statement. This example selects its data from the datadictionary. The command <TT>SPOOL OFF</TT> closes the spool file.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Always edit your output file before running it to eliminate syntax discrepancies and to further customize the file that you have created. <HR></P></BLOCKQUOTE><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>SQL><B> ED CNT.SQL</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> SELECT 'SELECT COUNT(*) FROM '||TABLE_NAME||';' 2 FROM CAT;SELECT COUNT(*) FROM ACCT_PAY;SELECT COUNT(*) FROM ACCT_REC;SELECT COUNT(*) FROM CUSTOMERS;SELECT COUNT(*) FROM EMPLOYEES;SELECT COUNT(*) FROM HISTORY;SELECT COUNT(*) FROM INVOICES;SELECT COUNT(*) FROM ORDERS;SELECT COUNT(*) FROM PRODUCTS;SELECT COUNT(*) FROM PROJECTS;SELECT COUNT(*) FROM VENDORS;SQL> SPOOL OFF</FONT></PRE><H5>ANALYSIS:</H5><P>The command <TT>SPOOL OFF</TT> closes the spool file. Then the <TT>ED</TT> commandedits the file. At this point you are inside the file that you created. You shouldremove unnecessary lines from the file, such as the <TT>SELECT</TT> statement, whichwas used to achieve the results, and the <TT>SPOOL OFF</TT> at the end of the file.</P><P>Here is how your file should look after the edit. Notice that each line is a validSQL statement.</P><PRE><FONT COLOR="#0066FF">SELECT COUNT(*) FROM ACCT_PAY;SELECT COUNT(*) FROM ACCT_REC;SELECT COUNT(*) FROM CUSTOMERS;SELECT COUNT(*) FROM EMPLOYEES;SELECT COUNT(*) FROM HISTORY;SELECT COUNT(*) FROM INVOICES;SELECT COUNT(*) FROM ORDERS;SELECT COUNT(*) FROM PRODUCTS;SELECT COUNT(*) FROM PROJECTS;SELECT COUNT(*) FROM VENDORS;</FONT></PRE><P>Now, execute the file:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO ON</B>SQL> <B>SET HEADING ON</B>SQL> <B>START CNT.SQL</B>SQL> SELECT COUNT(*) FROM ACCT_PAY; COUNT(*)--------- 7SQL> SELECT COUNT(*) FROM ACCT_REC; COUNT(*)--------- 9SQL> SELECT COUNT(*) FROM CUSTOMERS; COUNT(*)--------- 5SQL> SELECT COUNT(*) FROM EMPLOYEES; COUNT(*)--------- 10SQL> SELECT COUNT(*) FROM HISTORY; COUNT(*)--------- 26SQL> SELECT COUNT(*) FROM INVOICES; COUNT(*)--------- 0SQL> SELECT COUNT(*) FROM ORDERS; COUNT(*)--------- 0SQL> SELECT COUNT(*) FROM PRODUCTS; COUNT(*)--------- 10SQL> SELECT COUNT(*) FROM PROJECTS; COUNT(*)--------- 16SQL> SELECT COUNT(*) FROM VENDORS; COUNT(*)--------- 22SQL></FONT></PRE><H5>ANALYSIS:</H5><P><TT>Set echo on</TT> enables you to see each statement that was executed. <TT>Setheading on</TT> displays the column heading <TT>COUNT(*)</TT> for each <TT>SELECT</TT>statement. If you had included</P><PRE><FONT COLOR="#0066FF"><B>set feedback on</B></FONT></PRE><P>then</P><PRE><FONT COLOR="#0066FF">1 row selected. </FONT></PRE><P>would have been displayed after each count. This example executed the SQL scriptby using the SQL*Plus <TT>START</TT> command. However, what if you were dealing with50 tables instead of just 10?</P>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -