📄 ch17.htm
字号:
CREATE SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE SYNONYM HISTORY FOR RYAN.HISTORY;</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL>
SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK ON</B>
SQL> <B>START PRIV_SYN.SQL</B>
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>With hardly any effort, BRANDON has synonyms for all tables owned by RYAN and
no longer needs to qualify the table names.
<H2><FONT COLOR="#000077">Creating Views on Your Tables</FONT></H2>
<P>If you want to create views on a group of tables, you could try something similar
to the following example:</P>
<H5>INPUT:</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 VIEWS.SQL</B>
SQL> <B>SELECT 'CREATE VIEW ' || TABLE_NAME || '_VIEW AS SELECT * FROM ' ||</B>
2 <B> TABLE_NAME || ';'</B>
3 <B>FROM CAT</B>
4 <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">CREATE VIEW ACCT_PAY_VIEW AS SELECT * FROM ACCT_PAY;
CREATE VIEW ACCT_REC_VIEW AS SELECT * FROM ACCT_REC;
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;
CREATE VIEW EMPLOYEES_VIEW AS SELECT * FROM EMPLOYEES;
CREATE VIEW HISTORY_VIEW AS SELECT * FROM HISTORY;
CREATE VIEW INVOICES_VIEW AS SELECT * FROM INVOICES;
CREATE VIEW ORDERS_VIEW AS SELECT * FROM ORDERS;
CREATE VIEW PRODUCTS_VIEW AS SELECT * FROM PRODUCTS;
CREATE VIEW PROJECTS_VIEW AS SELECT * FROM PROJECTS;
CREATE VIEW VENDORS_VIEW AS SELECT * FROM VENDORS;</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL><B> SET ECHO OFF</B>
SQL> <B>SET FEEDBACK ON</B>
SQL> <B>START VIEWS.SQL</B>
View Created.
View Created.
View Created.
View Created.
View Created.
View Created.
View Created.
View Created.
View Created.
View Created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The file <TT>views.sql</TT> was generated by the previous SQL statement. This
output file has become another SQL statement file and contains statements to create
views on all specified tables. After running <TT>views.sql</TT>, you can see that
the views have been created.
<H2><FONT COLOR="#000077">Truncating All Tables in a Schema</FONT></H2>
<P>Truncating tables is an event that occurs in a development environment. To effectively
develop and test data load routines and SQL statement performance, data is reloaded
frequently. This process identifies and exterminates bugs, and the application being
developed or tested is moved into a production environment.</P>
<P>The following example truncates all tables in a specified schema.</P>
<H5>INPUT:</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 TRUNC.SQL</B>
SQL> <B>SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';'</B>
2 <B>FROM ALL_TABLES</B>
3 <B> WHERE OWNER = 'RYAN'</B>
4 <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TRUNCATE TABLE ACCT_PAY;
TRUNCATE TABLE ACCT_REC;
TRUNCATE TABLE CUSTOMERS;
TRUNCATE TABLE EMPLOYEES;
TRUNCATE TABLE HISTORY;
TRUNCATE TABLE INVOICES;
TRUNCATE TABLE ORDERS;
TRUNCATE TABLE PRODUCTS;
TRUNCATE TABLE PROJECTS;
TRUNCATE TABLE VENDORS;
</FONT></PRE>
<P>Go ahead and run your script if you dare.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> SPOOL OFF</B>
SQL> <B>SET FEEDBACK ON</B>
SQL> <B>START TRUNC.SQL</B>
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
Table Truncated.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Truncating all tables owned by RYAN removes all the data from those tables. Table
truncation is easy. You can use this technique if you plan to repopulate your tables
with new data.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Before performing an operation such
as truncating tables in a schema, you should always have a good backup of the tables
you plan to truncate, even if you are sure that you will never need the data again.
(You will--somebody is sure to ask you to restore the old data.)
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Using SQL to Generate Shell Scripts</FONT></H2>
<P>You can also use SQL to generate other forms of scripts, such as shell scripts.
For example, an Oracle RDBMS server may be running in a UNIX environment, which is
typically much larger than a PC operating system environment. Therefore, UNIX requires
a more organized approach to file management. You can use SQL to easily manage the
database files by creating shell scripts.</P>
<P>The following scenario drops tablespaces in a database. Although tablespaces can
be dropped using SQL, the actual data files associated with these tablespaces must
be removed from the operating system separately.</P>
<P>The first step is to generate an SQL script to drop the tablespaces.</P>
<H5>INPUT:</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 DROP_TS.SQL</B>
SQL> <B>SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ' INCLUDING CONTENTS;'</B>
2 <B>FROM SYS.DBA_TABLESPACES</B>
3 <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">DROP TABLESPACE SYSTEM INCLUDING CONTENTS;
DROP TABLESPACE RBS INCLUDING CONTENTS;
DROP TABLESPACE TEMP INCLUDING CONTENTS;
DROP TABLESPACE TOOLS INCLUDING CONTENTS;
DROP TABLESPACE USERS INCLUDING CONTENTS;
</FONT></PRE>
<P>Next you need to generate a shell script to remove the data files from the operating
system after the tablespaces have been dropped.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SPOOL OFF</B>
SQL> <B>SPOOL RM_FILES.SH</B>
SQL> <B>SELECT 'RM -F ' || FILE_NAME</B>
2 <B>FROM SYS.DBA_DATA_FILES</B>
3 <B> /</B>
rm -f /disk01/orasys/db01/system0.dbf
rm -f /disk02/orasys/db01/rbs0.dbf
rm -f /disk03/orasys/db01/temp0.dbf
rm -f /disk04/orasys/db01/tools0.dbf
rm -f /disk05/orasys/db01/users0.dbf
SQL> spool off
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now that you have generated both scripts, you may run the script to drop the tablespaces
and then execute the operating system shell script to remove the appropriate data
files. You will also find many other ways to manage files and generate non-SQL scripts
using SQL.
<H2><FONT COLOR="#000077">Reverse Engineering Tables and Indexes</FONT></H2>
<P>Even though many CASE tools allow you to reverse-engineer tables and indexes,
you can always use straight SQL for this purpose. You can retrieve all the information
that you need from the data dictionary to rebuild tables and indexes, but doing so
effectively is difficult without the use of a procedural language, such as PL/SQL
or a shell script.</P>
<P>We usually use embedded SQL within a shell script. Procedural language functions
are needed to plug in the appropriate ingredients of syntax, such as commas. The
script must be smart enough to know which column is the last one, so as to not place
a comma after the last column. The script must also know where to place parentheses
and so on. Seek the tools that are available to regenerate objects from the data
dictionary, whether you use C, Perl, shell scripts, COBOL, or PL/SQL.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Generating statements directly from the database spares you the often tedious
job of coding SQL statements. Regardless of your job scope, using SQL statement generation
techniques frees you to work on other phases of your projects.</P>
<P>What you have learned today is basic, and though these examples use the Oracle
database, you can apply the concepts to any relational database. Be sure to check
your specific implementation for variations in syntax and data dictionary structure.
If you keep an open mind, you will continually find ways to generate SQL scripts,
from simple statements to complex high-level system management.
<H2><FONT COLOR="#000077">Q&A</FONT></H2>
<DL>
<DD><B>Q How do I decide when to issue statements manually and when to write SQL
to generate SQL?</B>
<P><B>A</B> Ask yourself these questions:</P>
<UL>
<LI>How often will I be issuing the statements in question?<BR>
<BR>
<LI>Will it take me longer to write the "mother" statement than it would
to issue each statement manually?<BR>
<BR>
</UL>
<DD><B>Q From which tables may I select to generate SQL statements?</B>
<P><B>A </B>You may select from any tables to which you have access, whether they
are tables that you own or tables that reside in the data dictionary. Also keep in
mind that you can select from any valid objects in your database, such as views or
snapshots.</P>
<P><B>Q Are there any limits to the statements that I can generate with SQL?</B></P>
<P><B>A</B> For the most part any statement that you can write manually can be generated
somehow using SQL. Check your implementation for specific options for spooling output
to a file and formatting the output the way you want it. Remember that you can always
modify the generated statements later because the output is spooled to a file.
</DL>
<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
<H3><FONT COLOR="#000077">Quiz</FONT></H3>
<DL>
<DD><B>1. </B>From which two sources can you generate SQL scripts?
<P><B>2.</B> Will the following SQL statement work? Will the generated output work?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SPOOL CNT.SQL</B>
SQL> <B>SELECT 'COUNT(*) FROM ' || TABLE_NAME || ';'</B>
2 <B>FROM CAT</B>
3 <B> /</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>3. </B>Will the following SQL statement work? Will the generated output work?
</DL>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL><B> SET FEEDBACK OFF</B>
SQL><B> SPOOL GRANT.SQL</B>
SQL> <B>SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'</B>
2 <B>FROM SYS.DBA_USERS</B>
3 <B>WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')</B>
4 <B>/</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>4.</B> Will the following SQL statement work? Will the generated output work?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET ECHO OFF</B>
SQL> <B>SET FEEDBACK OFF</B>
SQL> <B>SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'</B>
2 <B>FROM SYS.DBA_USERS</B>
3 <B> WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT)</B>
4 <B> /</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD><B>5. </B>True or False: It is best to set feedback <TT>ON</TT> when generating
SQL.
<P><B>6.</B> True or False: When generating SQL from SQL, always spool to a list
or log file for a record of what happened.</P>
<P><B>7. </B>True or False: Before generating SQL to truncate tables, you should
always make sure you have a good backup of the tables.</P>
<P><B>8.</B> What is the <TT>ED</TT> command?</P>
<P><B>9. </B>What does the <TT>SPOOL OFF</TT> command do?
</DL>
<H3><FONT COLOR="#000077">Exercises</FONT></H3>
<DL>
<DD><B>1. </B>Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statement
that will generate a series of <TT>GRANT</TT> statements to five new users: John,
Kevin, Ryan, Ron, and Chris. Use the column called <TT>USERNAME</TT>. Grant them
Select access to <TT>history_tbl</TT>.
<P><B>2.</B> Using the examples in this chapter as guidelines, create some SQL statements
that will generate SQL that you can use.
</DL>
<H1></H1>
<CENTER>
<P>
<HR>
<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><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -