At this point you should have enough information to be able to create tables and execute queries against your database. You should experiment with this until you are comfortable with the Oracle environment and SQL.
Oracle also has a number of additions to SQL called SQL*Plus. Many of the SQL*Plus commands fall into the category of report writer -- they allow the easy generation of nicely formatted and useful reports.
The following sections describe some of the SQL*Plus commands and features.
You can create more complex queries by combining SQL with other SQL*Plus commands into larger scripts or programs. Very complex reports can be generated with very little physical effort. In SQL*Plus you can set up a number of parameters to control how output is generated. To see all the current settings, issue the following SQL*Plus command:
SQL> show all
arraysize 15
autocommit OFF
autoprint 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 80
lno 7
long 80
longchunksize 80
maxdata 60000
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
pause is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 702030001
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 0
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 "SMITH"
verify ON
wrap : lines will be wrapped
Example output of the SHOW ALL command.
You can see that there are many output options that you can change to suit your needs. Following are some of the more important and most-frequently used:
Option Meaning
set linesize val width of page (default val is 80)
set pagesize val length of page
ttitle 'text' report title - title is printed at the top-centre of
every page - the date and page number are printed at the
top of every page (before the title) - the '|' (vertical
bar) is the default line-break character used to split up
titles across multiple lines - can use left or right
modifiers to align text along margins
btitle 'text' bottom title (footer) - places specified text at the
centre-bottom of every page - can use left or right
modifiers to align text along margins
spool filename causes output of SQL script to be sent to the specified
filename rather than the default output device (usually
the screen)
spool off terminate the spool command
At times you will need to format the output generated by your scripts beyond the defaults specified in the table/column definitions. That is, you might want to print out nicely formatted and aligned columns and so forth. You do this through the COLUMN command:
column <column> format <format> heading '<heading>'
Format for the COLUMN command.
The <column> is simply the name of the column you are formatting. The <format> is the actual format of the output you want, and the <heading> is the the heading you want above the column. By default all the columns are formatted according to their defined sizes and the headings are the regular column headings.
You can format alphabetic information with the 'a' format command. For example, to reserve 15 spaces for alphabetic data, you might issue a command similar to the following:
column car_name format a15
Example of COLUMN specifying an alphabetic field width of 15 characters.
Important Note:
SQL*Plus commands do not have to end with a ';' character but no error is caused by doing so. All standard SQL commands must end with a ';'.
Numeric data is just as easy to format. To print a dollar value, you might use the following format specifier:
column car_price format $99,999.99 heading 'Cost'
Example of formatting numeric data.
which will reserve 10 spaces for a numeric value that will be formatted with a '$' and a comma if required.
In the above example an alternatate heading was also specified. When the CAR_PRICE column of information is printed, the title for that column will be "Cost". If no heading is specified, the column name is used as the default column title.
Furthermore, you can pad numeric fields with other numbers:
column car_fuel_displacement_cm3 format 00,000
Will reserve 6 spaces for numbers. If a number is not printed in any of those spaces, a 0 will be printed instead. One of those spaces is also reserved for a comma.
It is worthwhile to experiment with the formatting codes. They are not very difficult to implement and can make reports look substantially better than the default values.
You can cause columns to be broken apart according to some condition with the BREAK command. This is useful for printing column subtotals and so forth.
The next few examples make use of various tables that contain fictional information on spacecraft from various SF novels. The information is not that important, but the examples of break logic are.
The following SQL script:
rem ::: Produce a table showing the:
rem ::: engine alignment
rem ::: spacecraft name
rem ::: number of marines aboard
rem ::: spacecraft speed
rem ::: for ships with less than 5 engines.
set pagesize 25;
ttitle 'Spacecraft Engine Alignment|(for vessles with fewer than 5 engines)';
btitle right '- sc_query4.sql -';
compute sum of sc_marines_number on sc_speed
column sc_engine_alignment heading 'Alignment' format a9;column sc_name heading 'Vessle Name' format a15;
column sc_speed heading 'Max V' format 999
column sc_marines_number heading 'Marines' format 00000
spool result.tab
select sc_engine_alignment,
sc_marines_number,
spacecraft_engine.sc_name,
spacecraft_speed.sc_speed
from
spacecraft_engine, spacecraft_speed, spacecraft_marines
where
spacecraft_engine.sc_engine_number < 5
and
spacecraft_engine.sc_name = spacecraft_speed.sc_name
and
spacecraft_engine.sc_name = spacecraft_marines.sc_name
order by spacecraft_speed.sc_speed;
spool off
exit
Example SQL scriptfile.
produced the following output:
Mon Apr 29 page 1
Spacecraft Engine Alignment
(for vessles with fewer than 5 engines)
Alignment Marines Vessle Name Max V
--------- ------- --------------- -----
Y 00300 Valhala 3
N 00300 Klattu 3
Y 00250 Miroc IV 4
Y 00012 Maxima 4
Y 00050 Arcadia 5
- sc_query4.sql -
When performing any kind of break logic it is important that the column you wish to break on is ordered somehow. In the above example, the "Max V" (SC_SPEED) column is sorted into ascending order. If the column that you break on is not sorted, your output may not look nice since breaks will appear whenever the value changes.
The next output is produced by the same script with the addition of the following BREAK command:
break on sc_speed
Example of the BREAK ON command.
SC_SPEED is the column name that is output under the heading "Max V". The BREAK command causes the following output. Notice the column "Max V":
Mon Apr 29 page 1
Spacecraft Engine Alignment
(for vessles with fewer than 5 engines)
Alignment Marines Vessle Name Max V
--------- ------- --------------- -----
Y 00300 Valhala 3
N 00300 Klattu
Y 00250 Miroc IV 4
Y 00012 Maxima
Y 00050 Arcadia 5
- sc_query4.sql -
Example output produced with the BREAK command.
Only one "Max V" is displayed for a given value. When the value changes the new value is displayed once and all the subsequent same-values are not displayed.
We can make the output a bit more readable by also issuing the following BREAK/SKIP command:
break on sc_speed skip 1
Example of the SKIP command.
which causes 1 line to be skipped after each break:
Mon Apr 29 page 1
Spacecraft Engine Alignment
(for vessles with fewer than 5 engines)
Alignment Marines Vessle Name Max V
--------- ------- --------------- -----
Y 00300 Valhala 3
N 00300 Klattu
Y 00250 Miroc IV 4
Y 00012 Maxima
Y 00050 Arcadia 5
- sc_query4.sql -
Example output caused by the SKIP command.
Besides making output look nicer, you can also print out totals and other information at breaks. For example, to print out the total number of marines for each class of ship, we would issue the following COMPUTE SUM command in the script:
compute sum of sc_marines_number on sc_speed
Example of the COMPUTE SUM command.
This would cause sum totals of the SC_MARINES column to be calculated at every break specified. The sums are printed at the appropriate break points. If no break points are specified with a BREAK command, then the COMPUTE SUM command will not produce any output.
Mon Apr 29 page 1
Spacecraft Engine Alignment
(for vessles with fewer than 5 engines)
Alignment Marines Vessle Name Max V
--------- ------- --------------- -----
Y 00300 Valhala 3
N 00300 Klattu
------- *****
00600 sum
Y 00250 Miroc IV 4
Y 00012 Maxima
------- *****
00262 sum
Y 00050 Arcadia 5
------- *****
00050 sum
- sc_query4.sql -
You can use a similar command to print report totals as well. However, if you wish to print totals at different break points, you need to change the break selection as well The following COMPUTE command was added to the script:
compute sum of sc_marines_number on report
Example of the COMPUTE command to print a total at the end of the report.
and the BREAK command was changed to reflect this:
break on report skip 1 on sc_speed skip 1
Example of breaking on multiple conditions.
Notice that the BREAK command can have multiple ON conditions. In this case, it is breaking ON REPORT as well as breaking ON SC_SPEED. The cumulative effect is that an additional total is printed at the end of the report:
Mon Apr 29 page 1
Spacecraft Engine Alignment
(for vessles with fewer than 5 engines)
Alignment Marines Vessle Name Max V
--------- ------- --------------- -----
Y 00300 Valhala 3
N 00300 Klattu
------- *****
00600 sum
Y 00250 Miroc IV 4
Y 00012 Maxima
------- *****
00262 sum
Y 00050 Arcadia 5
------- *****
00050 sum
-------
sum 00912
- sc_query4.sql -
Following is the complete listing of the script that generated the abouve output:
rem ::: Produce a table showing the:
rem ::: engine alignment
rem ::: spacecraft name
rem ::: spacecraft speed
rem ::: for ships with less than 5 engines.
rem set headsep !
set pagesize 25;
ttitle 'Spacecraft Engine Alignment|(for vessles with fewer than 5 engines)';
btitle right '- sc_query4.sql -';
compute sum of sc_marines_number on sc_speed;
compute sum of sc_marines_number on report
break on report skip 1 on sc_speed skip 1
column sc_engine_alignment heading 'Alignment' format a9;
column sc_name heading 'Vessle Name' format a15;
column sc_speed heading 'Max V' format 999
column sc_marines_number heading 'Marines' format 00000
spool result.tab
select sc_engine_alignment,
sc_marines_number,
spacecraft_engine.sc_name,
spacecraft_speed.sc_speed
from
spacecraft_engine, spacecraft_speed, spacecraft_marines
where
spacecraft_engine.sc_engine_number < 5
and
spacecraft_engine.sc_name = spacecraft_speed.sc_name
and
spacecraft_engine.sc_name = spacecraft_marines.sc_name
order by spacecraft_speed.sc_speed;
spool off
exit
Scriptfile "sc_query4.sql".