 | Appendix D: The Fifteen-Minute SQL Tutorial |  |
Before we close this tutorial section, there are a few more advanced SQL topics you may encounter in your travels.
Some SQL servers allow you to create different views of a table. Views are like magic permanent SELECT queries. Once you create a view using a special SELECT query, the results of your query stick around and behave like their own table. Views can be queried like any other table. Modifications to a view, with a few restrictions, are propagated back to the original table or tables.
Note I said tables. Here's where the magic of views comes in: a view on a table can be created that consists of a join between that table and another. This view behaves as one large virtual table. Changes to this view are propagated back to the original tables that are part of the join that created the view.
A view can also be created with a new column consisting of calculations performed between other columns in that table, almost like a spreadsheet. Views are useful for more mundane purposes also, like query simplification (i.e., may be able to select fewer columns) and data restructuring (i.e., table users sees a view of the data that doesn't change, even if other columns in the underlying table structure are modified).
Here's a view creation example that demonstrates query simplification:
USE sysadm CREATE VIEW ipaddr_view AS SELECT name, ipaddr FROM hosts
Now we can use a very simple query to get back just the information we need:
USE sysadm SELECT * FROM ipaddr_view
The result of this query is:
name ipaddr ------------------------------ --------------- shimmer 192.168.1.11 bendir 192.168.1.3 sander 192.168.1.55 sulawesi 192.168.1.12
Like tables, views are dropped using a form of the DROP command:
USE sysadm DROP VIEW ipaddr_view
In all of the queries we've seen above, we've asked the server to hand us back all of the results once the query has completed. Sometimes it is preferable to receive the answer to a query one line at a time. This is most often the case when embedding SQL queries in other programs. If your query returns tens of thousands of lines, chances are pretty good that you'll want to process the results one line at a time, rather than storing them all in memory for later use. Most SQL programming in Perl uses this line-at-a-time method. Here's a small native-SQL program that demonstrates cursor use on a Sybase or MS-SQL Server:
USE sysadm
-- declare our variables
DECLARE @hostname character(30)
DECLARE @ip character(15)
-- declare our cursor
DECLARE hosts_curs CURSOR FOR SELECT name,ipaddr FROM hosts
-- open this cursor
OPEN hosts_curs
-- iterate over table, fetching rows one at a time,
-- until we receive an error
FETCH hosts_curs INTO @hostname,@ip
WHILE (@@fetch_status = 0)
BEGIN
PRINT "----"
PRINT @hostname
PRINT @ip
FETCH hosts_curs INTO @hostname,@ip
END
-- close the cursor (not strictly necessary when followed
-- by a DEALLOCATE)
CLOSE hosts_curs
-- undefine cursor def
DEALLOCATE hosts_curs
This produces the following output:
---- shimmer 192.168.1.11 ---- bendir 192.168.1.3 ---- sander 192.168.1.55 ---- sulawesi 192.168.1.12
Most database systems allow you to upload SQL code to the server where it is stored in an optimized, post-parsed form for faster execution. These uploads are known as stored procedures. Stored procedures are often a critical component of SQL for administrators because large parts of server administration for some servers rely on them. For example, to change the owner of the sysadm database in Sybase, you might do this:
USE sysadm sp_changedbowner "jay"
See Chapter 7, "SQL Database Administration" for examples of calling stored procedures. Now that you've seen the basics of SQL, you're ready to tackle Chapter 7, "SQL Database Administration".
|  | |
| D.5. Relating Tables to Each Other |  | E. The Twenty-Minute SNMP Tutorial |
Copyright © 2001 O'Reilly & Associates. All rights reserved.