SQLSQL 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:
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:
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:
For letters:
Finally, you can use AND and OR just as you would think:
SQL*PlusOracle'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:
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/SQLIn 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:
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 :
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
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; --------------------------------
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:
|