?? 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="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch16.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch18.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch18.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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 from
a 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 statement
whose output forms another SQL statement or command. Until now, all the SQL statements
that you have learned to write either do something, such as manipulate the data in
a table, one row at a time, or produce some kind of report from a query. Today you
learn 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, it
is a matter of simplicity and efficiency. You may never need<B> </B>to produce an
SQL statement, but without ever doing so you would be ignoring one of SQL's most
powerful features, one that too many people do not realize exists.</P>
<P>Generating SQL is rarely mandatory because you can manually create and issue all
SQL statements, although the process can be tedious in certain situations. On the
same 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 users
in the marketing department (and you want to get home for dinner). Because some users
of this database do not work in marketing, you cannot simply grant access on the
table 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 controlling
user 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 statement
as output to another statement. For example, you might need to execute many similar
SQL 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 data
for 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 the
database, drawing its output results either from the data dictionary or from application
tables in the database. Your statement can arrange the retrieved data into one or
more SQL statements. For instance, if one row is returned, you will have generated
one SQL statement. If 100 rows are returned from your statement, then you will have
generated 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 queries
or database actions.</P>
<P>The remainder of the day is devoted to examples that show you how to produce output
in 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 to
Retrieve Useful Information from the Data Dictionary.")</P>
<P><A NAME="01"></A><A HREF="01-3.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/01-3.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. (See
Day 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 they
execute--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> eliminates
the 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>SET
HEADING ON</TT>, which is the default, displays the column headings of your data
as a part of the output. <TT>SET HEADING OFF</TT>, of course, eliminates the column
headings 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 order
to 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 a
file. 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, which
is 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. You
will find that you use this command often when generating SQL script because you
may have to modify the contents of the file for customization. However, you can achieve
most 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 lines
in your generated code, thus allowing your SQL statement to run without being tarnished
with 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 tables
that you own or that are in your schema. For example, here's a list of the tables
you own:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM CAT;</B>
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ACCT_PAY TABLE
ACCT_REC TABLE
CUSTOMERS TABLE
EMPLOYEES TABLE
HISTORY TABLE
INVOICES TABLE
ORDERS TABLE
PRODUCTS TABLE
PROJECTS TABLE
VENDORS TABLE
10 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> statements
to 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 the
output to what is actually being selected. Remember, the output is not being used
as a report, but rather as an SQL statement that is ready to be executed. The next
step is to use the <TT>SPOOL</TT> command to direct the output to a file, which is
specified 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 single
quotation marks to select a literal string. The combination of single quotation marks
and the concatenation (<TT>||</TT>) allows you to combine actual data and literal
strings to form another SQL statement. This example selects its data from the data
dictionary. 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> command
edits the file. At this point you are inside the file that you created. You should
remove unnecessary lines from the file, such as the <TT>SELECT</TT> statement, which
was 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 valid
SQL 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(*)
---------
7
SQL> SELECT COUNT(*) FROM ACCT_REC;
COUNT(*)
---------
9
SQL> SELECT COUNT(*) FROM CUSTOMERS;
COUNT(*)
---------
5
SQL> SELECT COUNT(*) FROM EMPLOYEES;
COUNT(*)
---------
10
SQL> SELECT COUNT(*) FROM HISTORY;
COUNT(*)
---------
26
SQL> SELECT COUNT(*) FROM INVOICES;
COUNT(*)
---------
0
SQL> SELECT COUNT(*) FROM ORDERS;
COUNT(*)
---------
0
SQL> SELECT COUNT(*) FROM PRODUCTS;
COUNT(*)
---------
10
SQL> SELECT COUNT(*) FROM PROJECTS;
COUNT(*)
---------
16
SQL> SELECT COUNT(*) FROM VENDORS;
COUNT(*)
---------
22
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>Set echo on</TT> enables you to see each statement that was executed. <TT>Set
heading 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 script
by using the SQL*Plus <TT>START</TT> command. However, what if you were dealing with
50 tables instead of just 10?</P>
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -