Basic Concepts for Guerrilla Oracle

This chapter is from the book

Guerrilla Oracle®: The Succinct Windows Perspective

Learn More Buy

This chapter is from the book

This chapter is from the book 

Guerrilla Oracle®: The Succinct Windows Perspective

Learn More Buy

SQL Programming Languages

SQL

SQL was a product of the original RDBMS development efforts back in the 1970s. Since then, SQL has grown into a mature language that has three major categories—Data Definition Language (DDL), Data Manipulation Language (DML), and transaction control:

1. DDL contains verbs such as CREATE, ALTER, and DROP.

2. DML has SELECT, INSERT, UPDATE, and DELETE.

3. Transaction control is a subset of DML and contains the verbs SET TRANSACTION, SAVEPOINT, COMMIT, and ROLLBACK.

These are the expressions you would expect in a language. With DDL you will create. Then, using DML commands, you will insert and update your tables, and using the transaction control commands, you will either commit your modifications to the database, or do a rollback and not commit them.

Here's an example of SQL from the PERSON table. We want all the information for the person who has ID 0001. The SQL command is:

SELECT * from PERSON_TABLE
       where Person_id = 0001;

This query will return all the data (or the entire row) for the person with ID 0001, as shown here:

Suppose that you wanted to know just the names and phone numbers for everyone. Using the query

SELECT NAME, PHONE_NUMBER from PERSON_TABLE;

you would get the following information:

and so forth, until the end of the table was reached.

Table 3.1 briefly summarizes the SQL commands that have been introduced already, as well as some additional basic commands that will be introduced later. These commands are covered more fully in the Oracle documentation and in the many excellent reference books available.

Table 3.1. Basic SQL Commands

Now that you have a little taste for SQL, let's take a walk through some basic SQL syntax (Table 3.2). These are symbols and formats that you will be using for the rest of your career, and they form the foundation of your programming. When you perform a test against something, such as a letter, number, column, or literal, you will use these symbols.

Table 3.2. SQL Syntax

You will also constantly be comparing other values, looking for things in lists and so forth, so here is some more SQL syntax that you will have to digest. Suppose you have created users whose names you can't quite remember, but you know they're something like Neuman, or Sunman, or something similar. With SQL you can search on a column using the LIKE statement:

• To find any name, for example, that begins with Sun, use this command: Select NAME from PERSON_TABLE where name LIKE 'Sun%'

• To find a name that has man anywhere in it, use this command: Select NAME from PERSON_TABLE where name LIKE '%man%'

• To find a name that has, for example, two a's in it, use this command: Select NAME from PERSON_TABLE where name LIKE '%a%a%'

• If you know that the name you want has a z in the second position, you can get fancy by using an underscore. Each underscore stands for one space, so the following command would look for z in the second position of the name: Select NAME from PERSON_TABLE where name LIKE '_z%'

You will also find yourself using lists of things to compare values. For example, you may want all the students whose scores are not within the range of 70 to 90 for a certain exam, or for all exams. Or you may want to find the students who are from certain states—say, Alabama, Connecticut, and New York. Or you may want to exclude students from certain states, by using either the state name or letters of the alphabet. There is much flexibility with these commands. Here are the formats:

For numbers:

• Use the IN command when you want to check if something is in a list. For example, Score IN (90, 91, 92, 93) gives all scores that match anything in the list.

• Reverse the command to exclude a list. For example, Score NOT IN (90, 91, 92, 93) gives all scores that don't match anything in the list.

• To check for a range, use the BETWEEN command. For example, Score BETWEEN 90 and 95 gives all scores from 90 to 95.

• To exclude a range, use the NOT BETWEEN command. For example, Score NOT BETWEEN 90 and 95 excludes all scores above 95 and below 90.

For letters:

• Use the IN command when you want to check if something is in a list. For example, State IN ('AL', 'CT', 'NY') gives all states that match anything in the list contained within the parentheses.

• Reverse the command to exclude a list. For example, State NOT IN ('AL', 'CT', 'NY') gives all states that don't match the list.

• To check for a range, use the BETWEEN command. For example, State BETWEEN 'A' and 'D' gives all states between A and D.

• To exclude a range, use the NOT BETWEEN command. For example, State NOT BETWEEN 'AA' and 'DZ' excludes all states between A and D. (Note: The IN command ignores NULLS!)

Finally, you can use AND and OR just as you would think:

• Select Name from PERSON_TABLE where NAME LIKE 'Man%' AND STATE = 'CT';

• Select Name from PERSON_TABLE where NAME LIKE 'Man%' OR STATE = 'CT';

SQL*Plus

Oracle's extensions to SQL are called SQL*Plus. In general, the SQL*Plus commands are very useful when you are writing queries and generating reports. They give you control over headings, page breaks, totals, and other reporting format issues.

Here's an example of creating a simple SQL*Plus report to list all the phone numbers for everyone in the PERSON table:

spool c:\CHAPTER_3_REPORT
set echo off
set pagesize 55
set linesize 132
set newpage 0
ttitle left   'All Employees and Phone Numbers' -
      right  'Pg: ' format 999 sql.pno  skip 1 -
      center 'Guerrilla Oracle University' skip 1 -
      center 'As of &&date' skip 2
column PERSON_ID  format 999999999 Heading 'Person ID'
column Name      format a20 Heading 'Name'
column Phone     format a12 Heading 'Phone Number'
break on PERSON_ID skip 2 -

SELECT  PERSON_TABLE.PERSON_ID,  NAME, PHONE_NUMBER
                      from PERSON_TABLE,
TEMP_PERSON_TABLE
WHERE PERSON_TABLE.PERSON_ID = TEMP_PERSON_TABLE.PERSON_ID
order by 1;
spool off;

And here's what the report looks like:

All Employees and Phone Numbers
	Pg: 1
Guerrilla Oracle University As of 04-APR-02
Person ID	Name	Phone Number
111111111	Harry Gould	611-432-5543
222222222	Nancy Stankov	611-531-9987
333333333	Damian Stern	611-769-3211
444444444	John Turin	514-899-5567

Just to give you a quick synopsis, when this report runs, it prompts for the date; that's the &&date field. It then gets every record in PERSON_TABLE and TEMP_PERSON_TABLE and produces a simple listing with the column names of Person ID, Name, and Phone Number. (I'm about to show you why and how to create TEMP_PERSON_TABLE, so don't worry that you've missed something.)

PL/SQL

In addition to SQL*Plus, Oracle has developed PL/SQL (Procedural Language/SQL), which supports more traditional programming, such as loops and IF..THEN statements. When you write PL/SQL statements, you will be creating blocks. These blocks generally have three distinct sections:

1. DECLARE. In this section you define any variables and cursors that you will use in the block. (I will discuss cursors later in this chapter.)

2. BEGIN. This section contains your commands, such as loops and IF..THEN statements.

3. EXCEPTION. As the name implies, this is where you put any exception handling you may want.

In addition to including these three sections, you must complete the block with an END command or you will have errors. Here's a simple example from our PERSON table:

DECLARE
   Per_ID              NUMBER (9)
   Per_Name            VARCHAR2(20)
   Per_Birth           Date
   Per_Address         VARCHAR2(20)
   Per_Phone           VARCHAR2(10)

   BEGIN
   Per_ID           := 000000123
   Per_Birth        :=  '06091950'
   Per_Name         := 'Jerry Dubois'
   Per_Address      := '54 Durham Rd'
   Per_Phone        := '7543228769'

   INSERT INTO PERSON_TABLE    VALUES
   (Per_ID, Per_Birth, Per_NAME, Per_ADDRESS, Per_PHONE);

   EXCEPTION
   When DUP_VALUE_ON_INDEX
   Then . . . create a short report with the error and Student ID that
   caused the error

   END;
   .
   

In this example, the data for the row in PERSON_TABLE is declared as variables in the DECLARE section, values are given to these variables in the BEGIN section, and a simple INSERT is done to add the new row to the table. We have added an EXCEPTION section to check for duplicates, and if one is found, a quick listing is produced and processing ends. Be aware that once your program goes into the exception-handling routines, it does not return to the program. If you wanted to process more records, you would have to trap the errors by checking for them within the executable code, using a series of IF statements.

Now you probably want to know about cursors. Think of a cursor as a holding tank for a query. It is where the results of a single row returned from a query are kept. Once you have the data from a row, you can manipulate the row, use the data to update other tables, and so forth.

Suppose that once a week you have to produce data showing all the hours that students and staff have spent on various courses. You are not concerned about the exact courses, rather just the total hours.

In the programming world, there are a couple of considerations. First, in general you do not store data in a table when that data can be calculated from other columns. So, you would not store a column called Total Hours in the PERSON table because you already have a column called Hours in the COURSE table, right? To get the total hours for a person, or all hours for a certain time period, you would just query the COURSE table.

Sometimes, however, some information might be needed by several programs, or the information might be exported to another system. Suppose that one department wanted you to provide the total hours so that they could chart staff development trends. Another department might want the same information to predict training requirements. Another might want to calculate training costs. They all want the same basic information, but in different reports, and perhaps at different times.

Here's where it gets interesting. If it makes no difference when the data is pulled, you can simply use the same code in various programs. However, if it does make a difference when the data is pulled—for example, suppose they all want the data at 9:00 AM on Friday—then you may want to pull the data and put it into a temporary table. This is often done when statistical reporting is necessary and timelines are crucial. By temporary table, I mean a working table that is refreshed periodically. The next example will show you how to use a cursor to update TEMP_PERSON_TABLE with the total hours for each person. Once TEMP_PERSON_TABLE has been updated, other scripts can use the data for reporting, exporting, or anything else.

TEMP_PERSON_TABLE has three columns: TEMP_PERSON_ID, TOTAL_HOURS, and DATE. The date is the current system date.

As usual, I'll attempt to show you several things at once. To start, take a look at the two tables we'll be working with. We're starting with 20 entries in COURSE_TABLE and 4 entries in PERSON_TABLE:

SQL> select person_id, 
hours from course_table
  2 order by person_id;

SQL> select person_id, 
name from person_table;

Now follow this logic. We want a temporary work table that will have just the total hours for each person (temporary in the sense that it is only valid at a certain point in time).

What we have to do is first clean out anything in the temporary work table, create it with entries for the four employees in the PERSON table, and then calculate the total hours for each employee and update the employee record. We'll do all this using two scripts, where one script will drop, create, and load the person IDs into TEMP_PERSON_TABLE, and then call a second script that will run a cursor to load the table with the hours.

Here's our first script:

/*  This script is used in this chapter and has two parts.
    In the first part, we show you how to drop and then create
    TEMP_PERSON_TABLE, followed by loading the table with data.

    Part two runs a script that updates TEMP_PERSON_TABLE
    with the total hours per person.

*/

/*  First DROP the table just in case it exists  */     


   DROP TABLE TEMP_PERSON_TABLE;
   COMMIT;
   /*  Now CREATE the working table   */       

   CREATE TABLE TEMP_PERSON_TABLE   (
   PERSON_ID        NUMBER(12),
   HOURS            NUMBER(9,2),
   RUN_DATE         DATE
   )
   ;

   /* Now insert a row for every person in PERSON_TABLE  

   Take a close look at how this is being done. Notice that all
   we want are PERSON_ID instances in the table, so we use some
   fancy SQL to take the person IDs from PERSON_TABLE.

   */

   INSERT INTO TEMP_PERSON_TABLE

   (PERSON_ID) (SELECT PERSON_ID FROM PERSON_TABLE)

   ;


   /*  Finally, run the cursor script to update the total hours for
   each person.

   NOTE - and this is important - that we run this script from the
   C: drive.
   You can move this script anywhere; just change the location
   in the line below.

   */

   @C:\CHAPTER3_SCRIPTS\CHAPTER3_CURSOR_SCRIPT.SQL      
   

First drop the table.

Then create it.

Now insert the person_IDs from the PERSON table.

This command runs another script.

Once TEMP_PERSON_TABLE has become available, the second script runs, and here is where the cursor logic is used:

/* ——————————————————————————————————
--
--  This script is an example of using PL/SQL to update rows
--  in one table from data in another table.
--
--  It is used in this chapter and is the second part of the
--  script that drops and creates the TEMP_PERSON_TABLE.
--
--  Included are programming examples of the dbms_output command,
--  creating a cursor, processing first and last records, and
--  updating TEMP_PERSON_TABLE after calculating all the hours
--  for a given person ID.
--
--  There are several ways this could have been done. I wanted
--  to show you the power of PL/SQL, and I hope that you will
--  now go to some of the many excellent reference books
--  available.
--
——————————————————————————————————
*/
spool c:\cursor.lst        

   set echo on;
   set serveroutput on;    
   set buffer (1000000);   

   DECLARE                

   XCOUNTER         NUMBER(1);
   OLD_PERSON_ID   NUMBER(9);
   NEW_PERSON_ID   NUMBER(9);
   XHOURS          NUMBER(6,2);
   IN_HOURS        NUMBER(6,2);

   CURSOR                 
   COURSE_TAKEN_CURSOR     IS
   SELECT * from COURSE_TABLE         Order by PERSON_ID;

   COURSE_DATA  COURSE_TAKEN_CURSOR%ROWTYPE;
   BEGIN                   
   OPEN COURSE_TAKEN_CURSOR;   


   XCOUNTER := 0;

   XHOURS := 0;

   LOOP                    



   FETCH COURSE_TAKEN_CURSOR INTO COURSE_DATA;    
   EXIT WHEN COURSE_TAKEN_CURSOR%NOTFOUND;
   NEW_PERSON_ID := COURSE_DATA.PERSON_ID;


   --dbms_output.put_line('Fetch person is  ' || course_data.person_id);  


   --dbms_output.put_line('Fetch hours  is  ' || course_data.hours);


   IF XCOUNTER = 0          
   Then XCOUNTER := 1;
   Old_Person_ID := Course_DATA.person_ID;
   New_Person_ID := Course_DATA.person_id;
   ELSE
   XCOUNTER := 2;
   END IF;

   IF XCOUNTER = 1
   THEN
   GOTO get_the_hours;
   END IF;

   IF NEW_PERSON_ID = OLD_PERSON_ID
   THEN
   GOTO  get_the_hours;
   END IF;   

   UPDATE TEMP_PERSON_TABLE
   SET HOURS = XHOURS,
   RUN_DATE = SYSDATE
   Where
   TEMP_PERSON_TABLE.PERSON_ID =  OLD_PERSON_ID;


   XHOURS := 0;      
   OLD_PERSON_ID := NEW_PERSON_ID;


   <<GET_THE_HOURS>>        
   XHOURS := XHOURS + COURSE_DATA.HOURS;

   END LOOP;     
   UPDATE  TEMP_PERSON_TABLE
   SET HOURS = XHOURS,
   RUN_DATE = SYSDATE
   Where
   TEMP_PERSON_TABLE.PERSON_ID =  OLD_PERSON_ID;
   CLOSE COURSE_TAKEN_CURSOR;
   END;
   .
   /      
   spool off;    

   --------------------------------
   

Point to a file for your output.

Sometimes when you code you have to do some debugging. The dbms_output shown below is a good tool. To enable it, you must first set the server output on.

Increasing the buffer gives you more space for the dbms_output lines.

The DECLARE section has the variables . . .

. . . and the CURSOR definition. Here the cursor is called COURSE_TAKEN_ CURSOR, and rows are retrieved from COURSE_TABLE and put into a holding area called COURSE_DATA, which has been defined as ROWTYPE data from the cursor. All the columns in the row go into the COURSE_DATA line, one row at a time. The forthcoming FETCH command actually gets the rows. (Yes, you can have more than one cursor in the DECLARE section.) Read on.

The area where the actual processing takes place starts with BEGIN. The word BEGIN is mandatory.

The first step in processing is to open the cursor and initialize variables.

LOOP means that we will now start processing rowsof data using all the logic between LOOP and END LOOP.

The FETCH command retrieves a row of data from the table. Notice that we have an EXIT command. EXIT tells the system to exit outof the processing loop when there aren't any more records. %NOTFOUND is a system-generated attribute of cursors.¹

The dbms_output.put_line( ) command prints whateveryou put in the parentheses. It isa good debugging tool.

XCOUNTER indicates when the first record is read. You have to do some special processing with the first record, in this case move the person_ID into both the old and new person_ID variables. You need these variables to know when the new row retrieved does not have the same person_ID as the previous row because the COURSE table will have multiple entries for each person. XCOUNTER is set to1 for the first row processed. All other rows will set XCOUNTER to 2.

We're giving you quite a bit with this UPDATE section. The important thing to understand is that what we're doing is updating the PERSON_TABLE rows with the total hours for each person.

After the row has been updated, set the variables back to zero.

GET_THE_HOURS is a "paragraph" that GOTO referred to earlier.

You must end with these commands. If you use a cursor, you have to close it. The same is true if you use a loop. And you need the END; and the period. Notice the final UPDATE section, which handles the last row. Remember that we told the system to exit when there weren't any more records? If we just exit, we won't do the final update for the last row we processed.

The final slash ("/") tells the SQL editor to go ahead and run the script. Without the final slash, the script would be loaded into memory but it would not execute.

Finally it is good practice to close your spool file.

The preceding example contains a lot of code. This example has shown how to use one script to call another, how to drop, create, and load a table, and how to use a cursor. At first look, the scripts might appear complicated and somewhat unique. And they are a little strange to anyone who is starting to learn PL/SQL, so don't be dismayed. You're not expected to get it all at once. Rather, as I have said, take this example, get yourself a good book on SQL, PL/SQL, and SQL*Plus, and start coding.

I suggest that you start with this basic script, add another CURSOR section and some exception processing, and then take a look at creating procedures from your SQL and PL/SQL scripts. There's an enormous amount to learn, but just take it one step at a time and you'll be successful!

These scripts are on the CD that accompanies this book, so copy them and make your modifications. What you want to see is the successful message at the end:

.
.
.
29                THEN
30                  GOTO  get_the_hours;
31          END IF;
32          UPDATE  TEMP_PERSON_TABLE
33                 SET TOTAL_HOURS = XHOURS,
34                 RUN_DATE = SYSDATE
35             Where temp_person_table.TEMP_PERSON_ID =
36                 OLD_PERSON_ID;
37             XHOURS := 0;
38              Old_Person_Id := New_Person_Id;
39               New_Person_Id := 0;
40     <<GET_THE_HOURS>>
41          XHOURS := XHOURS + COURSE_DATA.HOURS;
42     END LOOP;
43     CLOSE COURSE_TAKEN_CURSOR;
44*    END;

PL/SQL procedure successfully completed.
   

This example is meant to show you how English-like and intuitive SQL, SQL*Plus, and PL/SQL are. In coming chapters we will cover additional SQL*Plus and PL/SQL commands, but just cursorily so that you can start programming immediately.

We're not quite finished with this chapter. There are two more major topics—Oracle Forms and Oracle Reports—to cover, and I'll do so in fast, guerrilla fashion. You will be using these two Oracle tools in your career. I'll also give a quick overview of a couple of other important Oracle modules: Web DB and Data Warehousing.

There are a few other important concepts you should also know:

• View. A view is a customized presentation of a table or tables. Usually it does not contain all the columns in the parent tables. In the PERSON table example, a view might contain the Name and Total Hours. Because a view has its own name, the actual table names are hidden from the users.

• Synonym. A synonym is another name for a table, view, or other object. The idea is to create short, easily remembered names, or to hide the actual name, such as Payroll Table, from the end user.

• Sequence. This powerful Oracle tool automatically assigns the next unique numeric value to a column. This is a great feature when you want sequential numbers for a column. You specify the starting number and range, and then use NEXT.VAL when adding data. This will be clear in Chapter 9, where data is loaded into tables.

• Database triggers. Database triggers are activities that happen automatically when something happens to a table. For example, if a student charges a meal in the cafeteria, a trigger will cause the available balance in her account to be reduced. A trigger is really code that you create and is “fired” when a particular event happens. You'll see examples of triggers in Chapters 13 and 14.