Today you will learn about SQL*Plus, the SQL interface for Oracle's RDBMS. By the end of Day 20, you will understand the following elements of SQL*Plus:
We are presenting SQL*Plus today because of Oracle's dominance in the relational database market and because of the power and flexibility SQL*Plus offers to the database user. SQL*Plus resembles Transact-SQL (see Day 19, "Transact-SQL: An Introduction") in many ways. Both implementations comply with the ANSI SQL standard for the most part, which is still the skeleton of any implementation.
SQL*Plus commands can enhance an SQL session and improve the format of queries from the database. SQL*Plus can also format reports, much like a dedicated report writer. SQL*Plus supplements both standard SQL and PL/SQL and helps relational database programmers gather data that is in a desirable format.
The SQL*Plus buffer is an area that stores commands that are specific to your particular SQL session. These commands include the most recently executed SQL statement and commands that you have used to customize your SQL session, such as formatting commands and variable assignments. This buffer is like a short-term memory. Here are some of the most common SQL buffer commands:
We begin with a simple SQL statement:
SQL> select * 2 from products 3 where unit_cost > 25;
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99
The LIST command lists the most recently executed SQL statement in the buffer. The output will simply be the displayed statement.
SQL> list 1 select * 2 from products 3* where unit_cost > 25
Notice that each line is numbered. Line numbers are important in the buffer; they act as pointers that enable you to modify specific lines of your statement using the SQL*PLUS buffer. The SQL*Plus buffer is not a full screen editor; after you hit Enter, you cannot use the cursor to move up a line, as shown in the following example.
SQL> select * 2 from products 3 where unit_cost > 25 4 /
NOTE: As with SQL commands, you may issue SQL*Plus commands in either uppercase or lowercase.
TIP: You can abbreviate most SQL*Plus commands; for example, LIST can be abbreviated as l.
You can move to a specific line from the buffer by placing a line number after the l:
SQL> l3 3* where unit_cost > 25
Notice the asterisk after the line number 3. This asterisk denotes the current line number. Pay close attention to the placement of the asterisk in today's examples. Whenever a line is marked by the asterisk, you can make changes to that line.
Because you know that your current line is 3, you are free to make changes. The syntax for the CHANGE command is as follows:
CHANGE/old_value/new_value
or
C/old_value/new_value
SQL> c/>/<
3* where unit_cost < 25
SQL> l
1 select * 2 from products 3* where unit_cost < 25
The greater than sign (>) has been changed to less than (<) on line 3. Notice after the change was made that the newly modified line was displayed. If you issue the LIST command or l, you can see the full statement. Now execute the statement:
SQL> /
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99
The forward slash at the SQL> prompt executes any statement that is in the buffer.
SQL> l
1 select * 2 from products 3* where unit_cost < 25
Now, you can add a line to your statement by typing a new line number at the SQL> prompt and entering text. After you make the addition, get a full statement listing. Here's an example:
SQL> 4 order by unit_cost SQL> 1
1 select * 2 from products 3 where unit_cost < 25 4* order by unit_cost
Deleting a line is easier than adding a line. Simply type DEL 4 at the SQL> prompt to delete line 4. Now get another statement listing to verify that the line is gone.
SQL> DEL4 SQL> l
1 select * 2 from products 3* where unit_cost < 25
Another way to add one or more lines to your statement is to use the INPUT command. As you can see in the preceding list, the current line number is 3. At the prompt type input and then press Enter. Now you can begin typing text. Each time you press Enter, another line will be created. If you press Enter twice, you will obtain another SQL> prompt. Now if you display a statement listing, as in the following example, you can see that line 4 has been added.
SQL> input 4i and product_id = 'P01' 5i SQL> l
1 select * 2 from products 3 where unit_cost < 25 4 and product_id = 'P01' 5* order by unit_cost
To append text to the current line, issue the APPEND command followed by the text. Compare the output in the preceding example--the current line number is 5--to the following example.
SQL> append desc
5* order by unit_cost desc
Now get a full listing of your statement:
SQL> l
1 select * 2 from products 3 where unit_cost < 25 4 and product_id = 'P01' 5* order by unit_cost desc
Suppose you want to wipe the slate clean. You can clear the contents of the SQL*Plus buffer by issuing the command CLEAR BUFFER. As you will see later, you can also use the CLEAR command to clear specific settings from the buffer, such as column formatting information and computes on a report.
SQL> clear buffer
buffer cleared
SQL> l
No lines in SQL buffer.
Obviously, you won't be able to retrieve anything from an empty buffer. You aren't a master yet, but you should be able to maneuver with ease by manipulating your commands in the buffer.
The handy DESCRIBE command enables you to view the structure of a table quickly without having to create a query against the data dictionary.
DESC[RIBE] table_name
Take a look at the two tables you will be using throughout the day.
SQL> describe orders
Name Null? Type ------------------------------- -------- ---- ORDER_NUM NOT NULL NUMBER(2) CUSTOMER NOT NULL VARCHAR2(30) PRODUCT_ID NOT NULL CHAR(3) PRODUCT_QTY NOT NULL NUMBER(5) DELIVERY_DATE DATE
The following statement uses the abbreviation DESC instead of DESCRIBE:
SQL> desc products
Name Null? Type ------------------------------- -------- ---- PRODUCT_ID NOT NULL VARCHAR2(3) PRODUCT_NAME NOT NULL VARCHAR2(30) UNIT_COST NOT NULL NUMBER(8,2)
DESC displays each column name, which columns must contain data (NULL/NOT NULL), and the data type for each column. If you are writing many queries, you will find that few days go by without using this command. Over a long time, this command can save you many hours of programming time. Without DESCRIBE you would have to search through project documentation or even database manuals containing lists of data dictionary tables to get this information.
The SHOW command displays the session's current settings, from formatting commands to who you are. SHOW ALL displays all settings. This discussion covers some of the most common settings.
SQL> show all
appinfo is ON and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF and is the 1st few characters of the next SELECT statement closecursor OFF colsep " " cmdsep OFF compatibility version NATIVE concat "." (hex 2e) copycommit 0 copytypecheck is ON crt "" define "&" (hex 26) echo OFF editfile "afiedt.buf" embedded OFF escape OFF feedback ON for 6 or more rows flagger OFF flush ON heading ON headsep "|" (hex 7c) linesize 100 lno 6 long 80 longchunksize 80 maxdata 60000 newpage 1 null "" numformat "" numwidth 9 pagesize 24 pause is OFF pno 1 recsep WRAP recsepchar " " (hex 20) release 703020200 repheader OFF and is NULL repfooter OFF and is NULL serveroutput OFF showmode OFF spool OFF sqlcase MIXED sqlcode 1007 sqlcontinue "> " sqlnumber ON sqlprefix "#" (hex 23) sqlprompt "SQL> " sqlterminator ";" (hex 3b) suffix "SQL" tab ON termout ON time OFF timing OFF trimout ON trimspool OFF ttitle OFF and is the 1st few characters of the next SELECT statement underline "-" (hex 2d) user is "RYAN" verify ON wrap : lines will be wrapped
The SHOW command displays a specific setting entered by the user. Suppose you have access to multiple database user IDs and you want to see how you are logged on. You can issue the following command:
SQL> show user
user is "RYAN"
To see the current line size of output, you would type:
SQL> show linesize
linesize 100
Various commands enable you to manipulate files in SQL*Plus. These commands include creating a file, editing the file using a full-screen editor as opposed to using the SQL*Plus buffer, and redirecting output to a file. You also need to know how to execute an SQL file after it is created.
The SAVE command saves the contents of the SQL statement in the buffer to a file whose name you specify. For example:
SQL> select * 2 from products 3 where unit_cost < 25
SQL> save query1.sql
Created file query1.sql
After a file has been saved, you can use the GET command to list the file. GET is very similar to the LIST command. Just remember that GET deals with statements that have been saved to files, whereas LIST deals with the statement that is stored in the buffer.
SQL> get query1
1 select * 2 from products 3* where unit_cost < 25
You can use the EDIT command either to create a new file or to edit an existing file. When issuing this command, you are taken into a full-screen editor, more than likely Notepad in Windows. You will find that it is usually easier to modify a file with EDIT than through the buffer, particularly if you are dealing with a large or complex statement. Figure 20.1 shows an example of the EDIT command.
SQL> edit query1.sql
Figure 20.1.
Editing a file in SQL*Plus.
Now that you know how to create and edit an SQL file, the command to execute it is simple. It can take one of the following forms:
START filename
or
STA filename
or
@filename
TIP: Commands are not case sensitive.
SQL> start query1.sql
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99
NOTE: You do not have to specify the file extension .sql to start a file from SQL*Plus. The database assumes that the file you are executing has this extension. Similarly, when you are creating a file from the SQL> prompt or use SAVE, GET, or EDIT, you do not have to include the extension if it is .sql.
SQL> @query1
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99
SQL> run query1
1 select * 2 from products 3* where unit_cost < 25 PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99
Notice that when you use RUN to execute a query, the statement is echoed, or displayed on the screen.
Viewing the output of your query on the screen is very convenient, but what if you want to save the results for future reference or you want to print the file? The SPOOL command allows you to send your output to a specified file. If the file does not exist, it will be created. If the file exists, it will be overwritten, as shown in Figure 20.2.
SQL> spool prod.lst SQL> select * 2 from products;
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99 7 rows selected.
SQL> spool off SQL> edit prod.lst
The output in Figure 20.2 is an SQL*Plus file. You must use the SPOOL OFF command to stop spooling to a file. When you exit SQL*Plus, SPOOL OFF is automatic. But if you do not exit and you continue to work in SQL*Plus, everything you do will be spooled to your file until you issue the command SPOOL OFF.
Figure 20.2.
Spooling your output to a file.
SET commands in Oracle change SQL*Plus session settings. By using these commands, you can customize your SQL working environment and invoke options to make your output results more presentable. You can control many of the SET commands by turning an option on or off.
To see how the SET commands work, perform a simple select:
SQL> select * 2 from products;
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99 7 rows selected.
The last line of output
7 rows selected.
is called feedback, which is an SQL setting that can be modified. The settings have defaults, and in this case the default for FEEDBACK is on. If you wanted, you could type
SET FEEDBACK ON
before issuing your select statement. Now suppose that you do not want to see the feedback, as happens to be the case with some reports, particularly summarized reports with computations.
SQL> set feedback off SQL> select * 2 from products;
PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99
SET FEEDBACK OFF turns off the feedback display.
In some cases you may want to suppress the column headings from being displayed on a report. This setting is called HEADING, which can also be set ON or OFF.
SQL> set heading off SQL> /
P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99
The column headings have been eliminated from the output. Only the actual data is displayed.
You can change a wide array of settings to manipulate how your output is displayed. One option, LINESIZE, allows you to specify the length of each line of your output. A small line size will more than likely cause your output to wrap; increasing the line size may be necessary to suppress wrapping of a line that exceeds the default 80 characters. Unless you are using wide computer paper (11 x 14), you may want to landscape print your report if you are using a line size greater than 80. The following example shows the use of LINESIZE.
SQL> set linesize 40 SQL> /
P01 MICKEY MOUSE LAMP
29.95
P02 NO 2 PENCILS - 20 PACK
1.99
P03 COFFEE MUG
6.95
P04 FAR SIDE CALENDAR
10.5
P05 NATURE CALENDAR
12.99
P06 SQL COMMAND REFERENCE
29.99
P07 BLACK LEATHER BRIEFCASE
99.99
You can also adjust the size of each page of your output by using the setting PAGESIZE. If you are simply viewing your output on screen, the best setting for PAGESIZE is 23, which eliminates multiple page breaks per screen. In the following example PAGESIZE is set to a low number to show you what happens on each page break.
SQL> set linesize 80 SQL> set heading on SQL> set pagesize 7 SQL> /
PRO PRODUCT_NAME UNIT_COST -- ------------------------------ -------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 PRO PRODUCT_NAME UNIT_COST -- ------------------------------ -------- P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99
Using the setting of PAGESIZE 7, the maximum number of lines that may appear on a single page is seven. New column headings will print automatically at the start of each new page.
The TIME setting displays the current time as part of your SQL> prompt.
SQL> set time on
08:52:02 SQL>
These were just a few of the SET options, but they are all manipulated in basically the same way. As you saw from the vast list of SET commands in the earlier output from the SHOW ALL statement, you have many options when customizing your SQL*Plus session. Experiment with each option and see what you like best. You will probably keep the default for many options, but you may find yourself changing other options frequently based on different scenarios.
When you log out of SQL*Plus, all of your session settings are cleared. When you log back in, your settings will have to be reinitialized if they are not the defaults unless you are using a login.sql file. This file is automatically executed when you sign on to SQL*Plus. This initialization file is similar to the autoexec.bat file on your PC or your .profile in a UNIX Korn Shell environment.
In Personal Oracle7 you can use the EDIT command to create your Login.sql file, as shown in Figure 20.3.
Figure 20.3.
Your Login.sql file.
When you log on to SQL*Plus, here is what you will see:
SQL*Plus: Release 3.3.2.0.2 - Production on Sun May 11 20:37:58 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter password: **** Connected to: Personal Oracle7 Release 7.3.2.2.0 - Production Release With the distributed and replication options PL/SQL Release 2.3.2.0.0 - Production 'HELLO! ------- HELLO ! 20:38:02 SQL>
In SQL*Plus, settings are cleared by logging off, or exiting SQL*Plus. Some of your settings may also be cleared by using the CLEAR command, as shown in the following examples.
SQL> clear col
columns cleared
SQL> clear break
breaks cleared
SQL> clear compute
computes cleared
SQL*Plus also has commands that enable you to arrange your output in almost any format. This section covers the basic formatting commands for report titles, column headings and formats, and giving a column a "new value."
TTITLE and BTITLE enable you to create titles on your reports. Previous days covered queries and output, but with SQL*Plus you can convert simple output into presentable reports. The TTITLE command places a title at the top of each page of your output or report. BTITLE places a title at the bottom of each page of your report. Many options are available with each of these commands, but today's presentation covers the essentials. Here is the basic syntax of TTITLE and BTITLE:
TTITLE [center|left|right] 'text' [&variable] [skip n] BTITLE [center|left|right] 'text' [&variable] [skip n]
SQL> ttitle 'A LIST OF PRODUCTS' SQL> btitle 'THAT IS ALL' SQL> set pagesize 15 SQL> /
Wed May 07
page 1
A LIST OF PRODUCTS
PRO PRODUCT_NAME UNIT_COST
-- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
THAT IS ALL
7 rows selected.
The title appears at the top of the page and at the bottom. Many people use the bottom title for signature blocks to verify or make changes to data on the report. Also, in the top title the date and page number are part of the title.
Formatting columns refers to the columns that are to be displayed or the columns that are listed after the SELECT in an SQL statement. The COLUMN, HEADING, and FORMAT commands rename column headings and control the way the data appears on the report.
The COL[UMN] command is usually used with either the HEADING command or the FORMAT command. COLUMN defines the column that you wish to format. The column that you are defining must appear exactly as it is typed in the SELECT statement. You may use a column alias instead of the full column name to identify a column with this command.
When using the HEADING command, you must use the COLUMN command to identify the column on which to place the heading.
When using the FORMAT command, you must use the COLUMN command to identify the column you wish to format.
The basic syntax for using all three commands follows. Note that the HEADING and FORMAT commands are optional. In the FORMAT syntax, you must use an a if the data has a character format or use 0s and 9s to specify number data types. Decimals may also be used with numeric values. The number to the right of the a is the total width that you wish to allow for the specified column.
COL[UMN] column_name HEA[DING] "new_heading" FOR[MAT] [a1|99.99]
The simple SELECT statement that follows shows the formatting of a column. The specified column is of NUMBER data type, and we want to display the number in a decimal format with a dollar sign.
SQL> column unit_cost heading "PRICE" format $99.99 SQL> select product_name, unit_cost 2 from products;
PRODUCT_NAME PRICE ------------------------------ ------- MICKEY MOUSE LAMP $29.95 NO 2 PENCILS - 20 PACK $1.99 COFFEE MUG $6.95 FAR SIDE CALENDAR $10.50 NATURE CALENDAR $12.99 SQL COMMAND REFERENCE $29.99 BLACK LEATHER BRIEFCASE $99.99
7 rows selected.
Because we used the format 99.99, the maximum number that will be displayed is 99.99.
Now try abbreviating the commands. Here's something neat you can do with the HEADING command:
SQL> col unit_cost hea "UNIT|COST" for $09.99 SQL> select product_name, unit_cost 2 from products;
PRODUCT_NAME UNIT COST ---------------------------- --------- MICKEY MOUSE LAMP $29.95 NO 2 PENCILS - 20 PACK $01.99 COFFEE MUG $06.95 FAR SIDE CALENDAR $10.50 NATURE CALENDAR $12.99 SQL COMMAND REFERENCE $29.99 BLACK LEATHER BRIEFCASE $99.99 7 rows selected.
The pipe sign (|) in the HEADING command forces the following text of the column heading to be printed on the next line. You may use multiple pipe signs. The technique is handy when the width of your report starts to push the limits of the maximum available line size. The format of the unit cost column is now 09.99. The maximum number displayed is still 99.99, but now a 0 will precede all numbers less than 10. You may prefer this format because it makes the dollar amounts appear uniform.
What would a report be without summaries and computations? Let's just say that you would have one frustrated programmer. Certain commands in SQL*Plus allow you to break up your report into one or more types of groups and perform summaries or computations on each group. BREAK is a little different from SQL's standard group functions, such as COUNT( ) and SUM( ). These functions are used with report and group summaries to provide a more complete report.
The BREAK ON command breaks returned rows of data from an SQL statement into one or more groups. If you break on a customer's name, then by default the customer's name will be printed only the first time it is returned and left blank with each row of data with the corresponding name. Here is the very basic syntax of the BREAK ON command:
BRE[AK] [ON column1 ON column2...][SKIP n|PAGE][DUP|NODUP]
You may also break on REPORT and ROW. Breaking on REPORT performs computations on the report as a whole, whereas breaking on ROW performs computations on each group of rows.
The SKIP option allows you to skip a number of lines or a page on each group. DUP or NODUP suggests whether you want duplicates to be printed in each group. The default is NODUP.
Here is an example:
SQL> col unit_cost head 'UNIT|COST' for $09.99 SQL> break on customer SQL> select o.customer, p.product_name, p.unit_cost 2 from orders o, 3 products p 4 where o.product_id = p.product_id 5 order by customer;
CUSTOMER PRODUCT_NAME UNIT COST
------------------------------ ---------------------------- ---------
JONES and SONS MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $01.99
COFFEE MUG $06.95
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $01.99
SQL COMMAND REFERENCE $29.99
BLACK LEATHER BRIEFCASE $99.99
FAR SIDE CALENDAR $10.50
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $29.95
BLACK LEATHER BRIEFCASE $99.99
BLACK LEATHER BRIEFCASE $99.99
NO 2 PENCILS - 20 PACK $01.99
NO 2 PENCILS - 20 PACK $01.99
13 rows selected.
Each unique customer is printed only once. This report is much easier to read than one in which duplicate customer names are printed. You must order your results in the same order as the column(s) on which you are breaking for the BREAK command to work.
The COMPUTE command is used with the BREAK ON command. COMPUTE allows you to perform various computations on each group of data and/or on the entire report.
COMP[UTE] function OF column_or_alias ON column_or_row_or_report
Some of the more popular functions are
Suppose you want to create a report that lists the information from the PRODUCTS table and computes the average product cost on the report.
SQL> break on report SQL> compute avg of unit_cost on report SQL> select * 2 from products;
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.50
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
---------
avg 27.48
You can obtain the information you want by breaking on REPORT and then computing the avg of the unit_cost on REPORT.
Remember the CLEAR command? Now clear the last compute from the buffer and start again--but this time you want to compute the amount of money spent by each customer. Because you do not want to see the average any longer, you should also clear the computes.
SQL> clear compute
computes cleared
Now clear the last BREAK. (You don't really have to clear the BREAK in this case because you still intend to break on report.)
SQL> clear break
breaks cleared
The next step is to reenter the breaks and computes the way you want them now. You will also have to reformat the column unit_cost to accommodate a larger number because you are computing a sum of the unit_cost on the report. You need to allow room for the grand total that uses the same format as the column on which it is being figured. So you need to add another place to the left of the decimal.
SQL> col unit_cost hea 'UNIT|COST' for $099.99 SQL> break on report on customer skip 1 SQL> compute sum of unit_cost on customer SQL> compute sum of unit_cost on report
Now list the last SQL statement from the buffer.
SQL> l
1 select o.customer, p.product_name, p.unit_cost 2 from orders o, 3 products p 4 where o.product_id = p.product_id 5* order by customer
Now that you have verified that this statement is the one you want, you can execute it:
SQL> /
UNIT
CUSTOMER PRODUCT_NAME COST
------------------------------ ------------------------------ --------
JONES and SONS MICKEY MOUSE LAMP $029.95
NO 2 PENCILS - 20 PACK $001.99
COFFEE MUG $006.95
****************************** --------
sum $038.89
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $029.95
NO 2 PENCILS - 20 PACK $001.99
SQL COMMAND REFERENCE $029.99
BLACK LEATHER BRIEFCASE $099.99
FAR SIDE CALENDAR $010.50
****************************** --------
sum $172.42
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $029.95
BLACK LEATHER BRIEFCASE $099.99
BLACK LEATHER BRIEFCASE $099.99
NO 2 PENCILS - 20 PACK $001.99
NO 2 PENCILS - 20 PACK $001.99
****************************** --------
UNIT
CUSTOMER PRODUCT_NAME COST
----------------------------- ------------------------------ --------
sum $233.91
--------
sum $445.22
13 rows selected.
This example computed the total amount that each customer spent and also calculated a grand total for all customers.
By now you should understand the basics of formatting columns, grouping data on the report, and performing computations on each group.
Without actually getting into a procedural language, you can still define variables in your SQL statement. You can use special options in SQL*Plus (covered in this section) to accept input from the user to pass parameters into your SQL program.
An ampersand (&) is the character that calls a value for a variable within an SQL script. If the variable has not previously been defined, the user will be prompted to enter a value.
SQL> select * 2 from &TBL 3 / Enter value for tbl: products
The user entered the value "products."
old 2: from &TBL new 2: from products PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99 7 rows selected.
The value products was substituted in the place of &TBL in this "interactive query."
You can use DEFINE to assign values to variables within an SQL script file. If you define your variables within the script, users are not prompted to enter a value for the variable at runtime, as they are if you use the &. The next example issues the same SELECT statement as the preceding example, but this time the value of TBL is defined within the script.
SQL> define TBL=products SQL> select * 2 from &TBL;
old 2: from &TBL new 2: from products PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99 7 rows selected.
Both queries achieved the same result. The next section describes another way to prompt users for script parameters.
ACCEPT enables the user to enter a value to fill a variable at script runtime. ACCEPT does the same thing as the & with no DEFINE but is a little more controlled. ACCEPT also allows you to issue user-friendly prompts.
The next example starts by clearing the buffer:
SQL> clear buffer
buffer cleared
Then it uses an INPUT command to enter the new SQL statement into the buffer. If you started to type your statement without issuing the INPUT command first, you would be prompted to enter the value for newtitle first. Alternatively, you could go straight into a new file and write your statement.
SQL> input 1 accept newtitle prompt 'Enter Title for Report: ' 2 ttitle center newtitle 3 select * 4 from products 5 SQL> save prod
File "prod.sql" already exists. Use another name or "SAVE filename REPLACE".
Whoops...the file prod.sql already exists. Let's say that you need the old prod.sql and do not care to overwrite it. You will have to use the replace option to save the statement in the buffer to prod.sql. Notice the use of PROMPT in the preceding statement. PROMPT displays text to the screen that tells the user exactly what to enter.
SQL> save prod replace
Wrote file prod
Now you can use the START command to execute the file.
SQL> start prod Enter Title for Report: A LIST OF PRODUCTS
A LIST OF PRODUCTS PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE MUG 6.95 P04 FAR SIDE CALENDAR 10.5 P05 NATURE CALENDAR 12.99 P06 SQL COMMAND REFERENCE 29.99 P07 BLACK LEATHER BRIEFCASE 99.99 7 rows selected.
The text that you entered becomes the current title of the report.
The next example shows how you can use substitution variables anywhere in a statement:
SQL> input 1 accept prod_id prompt 'Enter PRODUCT ID to Search for: ' 2 select * 3 from products 4 where product_id = '&prod_id' 5 SQL> save prod1
Created file prod1
SQL> start prod1 Enter PRODUCT ID to Search for: P01
old 3: where product_id = '&prod_id'
new 3: where product_id = 'P01'
A LIST OF PRODUCTS
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
You can use variables to meet many needs--for example, to name the file to which to spool your output or to specify an expression in the ORDER BY clause. One of the ways to use substitution variables is to enter reporting dates in the WHERE clause for transactional quality assurance reports. If your query is designed to retrieve information on one particular individual at a time, you may want to add a substitution variable to be compared with the SSN column of a table.
The NEW_VALUE command passes the value of a selected column into an undefined variable of your choice. The syntax is as follows:
COL[UMN] column_name NEW_VALUE new_name
You call the values of variables by using the & character; for example:
&new_name
The COLUMN command must be used with NEW_VALUE.
Notice how the & and COLUMN command are used together in the next SQL*Plus file. The GET command gets the file.
SQL> get prod1
line 5 truncated. 1 ttitle left 'Report for Product: &prod_title' skip 2 2 col product_name new_value prod_title 3 select product_name, unit_cost 4 from products 5* where product_name = 'COFFEE MUG'
SQL> @prod1
Report for Product: COFFEE MUG PRODUCT_NAME UNIT_COST ------------------------------ ---------- COFFEE MUG 6.95
The value for the column PRODUCT_NAME was passed into the variable prod_title by means of new_value. The value of the variable prod_title was then called in the TTITLE.
For more information on variables in SQL, see Day 18, "PL/SQL: An Introduction," and Day 19.
The DUAL table is a dummy table that exists in every Oracle database. This table is composed of one column called DUMMY whose only row of data is the value X. The DUAL table is available to all database users and can be used for general purposes, such as performing arithmetic (where it can serve as a calculator) or manipulating the format of the SYSDATE.
SQL> desc dual;
Name Null? Type ------------------------------- -------- ---- DUMMY VARCHAR2(1)
SQL> select * 2 from dual;
D - X
Take a look at a couple of examples using the DUAL table:
SQL> select sysdate 2 from dual;
SYSDATE -------- 08-MAY-97
SQL> select 2 * 2 2 from dual;
2*2
--------
4
Pretty simple. The first statement selected SYSDATE from the DUAL table and got today's date. The second example shows how to multiply in the DUAL table. Our answer for 2 * 2 is 4.
The DECODE function is one of the most powerful commands in SQL*Plus--and perhaps the most powerful. The standard language of SQL lacks procedural functions that are contained in languages such as COBOL and C.
The DECODE statement is similar to an IF...THEN statement in a procedural programming language. Where flexibility is required for complex reporting needs, DECODE is often able to fill the gap between SQL and the functions of a procedural language.
DECODE(column1, value1, output1, value2, output2, output3)
The syntax example performs the DECODE function on column1. If column1 has a value of value1, then display output1 instead of the column's current value. If column1 has a value of value2, then display output2 instead of the column's current value. If column1 has a value of anything other than value1 or value2, then display output3 instead of the column's current value.
How about some examples? First, perform a simple select on a new table:
SQL> select * from states;
ST -- IN FL KY IL OH CA NY 7 rows selected.
Now use the DECODE command:
SQL> select decode(state,'IN','INDIANA','OTHER') state 2 from states;
STATE ------ INDIANA OTHER OTHER OTHER OTHER OTHER OTHER 7 rows selected.
Only one row met the condition where the value of state was IN, so only that one row was displayed as INDIANA. The other states took the default and therefore were displayed as OTHER.
The next example provides output strings for each value in the table. Just in case your table has states that are not in your DECODE list, you should still enter a default value of 'OTHER'.
SQL> select decode(state,'IN','INDIANA', 2 'FL','FLORIDA', 3 'KY','KENTUCKY', 4 'IL','ILLINOIS', 5 'OH','OHIO', 6 'CA','CALIFORNIA', 7 'NY','NEW YORK','OTHER') 8 from states;
DECODE(STATE) ---------- INDIANA FLORIDA KENTUCKY ILLINOIS OHIO CALIFORNIA NEW YORK 7 rows selected.
That was too easy. The next example introduces the PAY table. This table shows more of the power that is contained within DECODE.
SQL> col hour_rate hea "HOURLY|RATE" for 99.00 SQL> col date_last_raise hea "LAST|RAISE" SQL> select name, hour_rate, date_last_raise 2 from pay;
HOURLY LAST NAME RATE RAISE -------------------- ------ -------- JOHN 12.60 01-JAN-96 JEFF 8.50 17-MAR-97 RON 9.35 01-OCT-96 RYAN 7.00 15-MAY-96 BRYAN 11.00 01-JUN-96 MARY 17.50 01-JAN-96 ELAINE 14.20 01-FEB-97 7 rows selected.
Are you ready? It is time to give every individual in the PAY table a pay raise. If the year of an individual's last raise is 1996, calculate a 10 percent raise. If the year of the individual's last raise is 1997, calculate a 20 percent raise. In addition, display the percent raise for each individual in either situation.
SQL> col new_pay hea 'NEW PAY' for 99.00 SQL> col hour_rate hea 'HOURLY|RATE' for 99.00 SQL> col date_last_raise hea 'LAST|RAISE' SQL> select name, hour_rate, date_last_raise, 2 decode(substr(date_last_raise,8,2),'96',hour_rate * 1.2, 3 '97',hour_rate * 1.1) new_pay, 4 decode(substr(date_last_raise,8,2),'96','20%', 5 '97','10%',null) increase 6 from pay;
HOURLY LAST NAME RATE RAISE NEW PAY INC -------------------- ------ --------- ------- --- JOHN 12.60 01-JAN-96 15.12 20% JEFF 8.50 17-MAR-97 9.35 10% RON 9.35 01-OCT-96 11.22 20% RYAN 7.00 15-MAY-96 8.40 20% BRYAN 11.00 01-JUN-96 13.20 20% MARY 17.50 01-JAN-96 21.00 20% ELAINE 14.20 01-FEB-97 15.62 10% 7 rows selected.
According to the output, everyone will be receiving a 20 percent pay increase except Jeff and Elaine, who have already received one raise this year.
If you want to add a touch of class to the way dates are displayed, then you can use the TO_CHAR function to change the "date picture." This example starts by obtaining today's date:
SQL> select sysdate 2 from dual;
SYSDATE -------- 08-MAY-97
When converting a date to a character string, you use the TO_CHAR function with the following syntax:
TO_CHAR(sysdate,'date picture')
date picture is how you want the date to look. Some of the most common parts of the date picture are as follows: Month The current month spelled out.
| Mon | The current month abbreviated. |
| Day | The current day of the week. |
| mm | The number of the current month. |
| yy | The last two numbers of the current year. |
| dd | The current day of the month. |
| yyyy | The current year. |
| ddd | The current day of the year since January 1. |
| hh | The current hour of the day. |
| mi | The current minute of the hour. |
| ss | The current seconds of the minute. |
| a.m. | Displays a.m. or p.m. |
The date picture may also contain commas and literal strings as long as the string is enclosed by double quotation marks "".
SQL> col today for a20 SQL> select to_char(sysdate,'Mon dd, yyyy') today 2 from dual;
TODAY -------------------- May 08, 1997
Notice how we used the COLUMN command on the alias today.
SQL> col today hea 'TODAYs JULIAN DATE' for a20 SQL> select to_char(sysdate,'ddd') today 2 from dual;
TODAYs JULIAN DATE -------------------- 128
Some companies prefer to express the Julian date with the two-digit year preceding the three-digit day. Your date picture could also look like this: 'yyddd'.
Assume that you wrote a little script and saved it as day. The next example gets the file, looks at it, and executes it to retrieve various pieces of converted date information.
SQL> get day
line 10 truncated. 1 set echo on 2 col day for a10 3 col today for a25 4 col year for a25 5 col time for a15 6 select to_char(sysdate,'Day') day, 7 to_char(sysdate,'Mon dd, yyyy') today, 8 to_char(sysdate,'Year') year, 9 to_char(sysdate,'hh:mi:ss a.m.') time 10* from dual
Now you can run the script:
SQL> @day
SQL> set echo on SQL> col day for a10 SQL> col today for a25 SQL> col year for a25 SQL> col time for a15 SQL> select to_char(sysdate,'Day') day, 2 to_char(sysdate,'Mon dd, yyyy') today, 3 to_char(sysdate,'Year') year, 4 to_char(sysdate,'hh:mi:ss a.m.') time 5 from dual; DAY TODAY YEAR TIME ---------- ------------------------ ----------------------- ------------ Thursday May 08, 1997 Nineteen Ninety-Seven 04:10:43 p.m.
In this example the entire statement was shown before it ran because ECHO was set to ON. In addition, sysdate was broken into four columns and the date was converted into four formats.
The TO_DATE function enables you to convert text into a date format. The syntax is basically the same as TO_CHAR.
TO_DATE(expression,'date_picture')
Try a couple of examples:
SQL> select to_date('19970501','yyyymmdd') "NEW DATE"
2 from dual;
NEW DATE -------- 01-MAY-97
SQL> select to_date('05/01/97','mm"/"dd"/"yy') "NEW DATE"
2 from dual;
NEW DATE -------- 01-MAY-97
Notice the use of double quotation marks to represent a literal string.
An SQL script file can include anything that you can type into the SQL buffer at the SQL> prompt, even commands that execute another SQL script. Yes, you can start an SQL script from within another SQL script. Figure 20.4 shows a script file that was created using the EDIT command. The file contains multiple SQL statements as well as commands to run other SQL scripts.
SQL> edit main.sql
SQL> @main
By starting main.sql, you will be executing each SQL command that is contained within the script. Query1 through query5 will also be executed, in that order, as shown in Figure 20.4.
Figure 20.4.
Running SQL scripts from within an SQL script.
SQL*Plus gives you three ways to place comments in your file:
Study the following example:
SQL> input 1 REMARK this is a comment 2 -- this is a comment too 3 REM 4 -- SET COMMANDS 5 set echo on 6 set feedback on 7 -- SQL STATEMENT 8 select * 9 from products 10 SQL>
To see how comments look in an SQL script file, type the following:
SQL> edit query10
Now let's have some fun. By taking the concepts that you have learned today, as well as what you learned earlier, you can now create some fancy reports. Suppose that you have a script named report1.sql. Start it, sit back, and observe.
SQL> @report1
SQL> set echo on
SQL> set pagesize 50
SQL> set feedback off
SQL> set newpage 0
SQL> col product_name hea 'PRODUCT|NAME' for a20 trunc
SQL> col unit_cost hea 'UNIT|COST' for $99.99
SQL> col product_qty hea 'QTY' for 999
SQL> col total for $99,999.99
SQL> spool report
SQL> compute sum of total on customer
SQL> compute sum of total on report
SQL> break on report on customer skip 1
SQL> select o.customer, p.product_name, p.unit_cost,
2 o.product_qty, (p.unit_cost * o.product_qty) total
3 from orders o,
4 products p
5 where o.product_id = p.product_id
6 order by customer
7 /
CUSTOMER PRODUCT UNIT QTY TOTAL
NAME COST
--------------------------- --------------------- ------ ----- ----------
JONES and SONS MICKEY MOUSE LAMP $29.95 50 $1,497.50
NO 2 PENCILS - 20 PA $1.99 10 $19.90
COFFEE MUG $6.95 10 $69.50
****************************** ----------
sum $1,586.90
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $29.95 5 $149.75
NO 2 PENCILS - 20 PA $1.99 15 $29.85
SQL COMMAND REFERENC $29.99 10 $299.90
BLACK LEATHER BRIEFC $99.99 1 $99.99
FAR SIDE CALENDAR $10.50 22 $231.00
****************************** ----------
sum $810.49
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $29.95 1 $29.95
BLACK LEATHER BRIEFC $99.99 5 $499.95
BLACK LEATHER BRIEFC $99.99 1 $99.99
NO 2 PENCILS - 20 PA $1.99 10 $19.90
NO 2 PENCILS - 20 PA $1.99 10 $19.90
****************************** ----------
sum $669.69
---------- sum $3,067.08 SQL> Input truncated to 9 characters spool off
Several things are taking place in this script. If you look at the actual SQL statement, you can see that it is selecting a data from two tables and performing an arithmetic function as well. The statement joins the two tables in the WHERE clause and is ordered by the customer's name. Those are the basics. In addition, SQL*Plus commands format the data the way we want to see it. These commands break the report into groups, making computations on each group and making a computation on the report as a whole.
Day 20 explains Oracle's extension to the standard language of SQL. These commands are only a fraction of what is available to you in SQL*Plus. If you use Oracle's products, check your database documentation, take the knowledge that you have learned here, and explore the endless possibilities that lie before you. You will find that you can accomplish almost any reporting task using SQL*Plus rather than by resorting to a procedural programming language. If you are not using Oracle products, use what you have learned today to improve the ways you retrieve data in your implementation. Most major implementations have extensions, or enhancements, to the accepted standard language of SQL.
A If your requirements for reports are simple, straight SQL is fine. But you can reduce the time you spend on reports by using SQL*Plus. And you can be sure that the person who needs your reports will always want more information.
Q How can I select SYSDATE from the DUAL table if it is not a column?
A You can select SYSDATE from DUAL or any other valid table because SYSDATE is a pseudocolumn.
Q When using the DECODE command, can I use a DECODE within another DECODE?
A Yes, you can DECODE within a DECODE. In SQL you can perform functions on other functions to achieve the desired results.
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."
2. Can your SQL script prompt a user for a parameter and execute the SQL statement using the entered parameter?
3. If you are creating a summarized report on entries in a CUSTOMER table, how would you group your data for your report?
4. Are there limitations to what you can have in your LOGIN.SQL file?
5. True or False: The DECODE function is the equivalent of a loop in a procedural programming language.
6. True or False: If you spool the output of your query to an existing file, your output will be appended to that file.
2. Suppose today is Monday, May 12, 1998. Write a query that will produce the following output:
Today is Monday, May 12 1998
1 select * 2 from orders 3 where customer_id = '001' 4* order by customer_id;
Now append DESC to the ORDER BY clause.
© Copyright, Macmillan Computer Publishing. All rights reserved.