Book Home Perl for System AdministrationSearch this book

D.6. SQL Stragglers

Before we close this tutorial section, there are a few more advanced SQL topics you may encounter in your travels.

D.6.1. Views

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

D.6.2. Cursors

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

D.6.3. Stored Procedures

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".



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.