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.
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.99The 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
SQL> select * 2 from products 3 where unit_cost > 25 4 /
You can move to a specific line from the buffer by placing a line number after the l:
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.
SQL> l3 3* where unit_cost > 25
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_valueor
C/old_value/new_value
SQL> c/>/<
3* where unit_cost < 25
SQL> l
1 select * 2 from products 3* where unit_cost < 25
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
SQL> l
1 select * 2 from products 3* where unit_cost < 25Now, 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
SQL> DEL4 SQL> l
1 select * 2 from products 3* where unit_cost < 25Another 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_costTo 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 descNow 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 descSuppose 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.
DESC[RIBE] table_nameTake 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 DATEThe 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)
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 wrappedThe 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
SQL> select * 2 from products 3 where unit_cost < 25
SQL> save query1.sql
Created file query1.sql
SQL> get query1
1 select * 2 from products 3* where unit_cost < 25You 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.sqlFigure 20.1.
START filenameor
STA filenameor
@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.99Notice that when you use RUN to execute a query, the statement is echoed, or displayed on the screen.
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
Figure 20.2.
Spooling your output to a file.
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.
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 ONbefore 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
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
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.99You 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
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.
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>
SQL> clear col
columns cleared
SQL> clear break
breaks cleared
SQL> clear compute
computes cleared
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 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.
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.
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.
COMP[UTE] function OF column_or_alias ON column_or_row_or_reportSome of the more popular functions are
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
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 clearedNow 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 clearedThe 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 reportNow 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
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.
By now you should understand the basics of formatting columns, grouping data on the report, and performing computations on each group.
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.
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.
The next example starts by clearing the buffer:
SQL> clear buffer
buffer clearedThen 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".
SQL> save prod replace
Wrote file prodNow 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 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
COL[UMN] column_name NEW_VALUE new_nameYou call the values of variables by using the & character; for example:
&new_nameThe 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
For more information on variables in SQL, see Day 18, "PL/SQL: An Introduction," and Day 19.
SQL> desc dual;
Name Null? Type ------------------------------- -------- ---- DUMMY VARCHAR2(1)
SQL> select * 2 from dual;
D - XTake 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 -------- 4Pretty 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 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.
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.
SQL> select sysdate 2 from dual;
SYSDATE -------- 08-MAY-97When 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. |
SQL> col today for a20 SQL> select to_char(sysdate,'Mon dd, yyyy') today 2 from dual;
TODAY -------------------- May 08, 1997
SQL> col today hea 'TODAYs JULIAN DATE' for a20 SQL> select to_char(sysdate,'ddd') today 2 from dual;
TODAYs JULIAN DATE -------------------- 128
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 dualNow 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.
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
SQL> edit main.sql
SQL> @main
Figure 20.4.
Running SQL scripts from within an SQL script.
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
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
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.
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.