Advanced Structured Query Language (SQL)/SQL*Plus

Oracle9i SQL*Plus

Tutorial

Author: DL

Last Updated: GS (02/11/10)

10.0 Variables in SQL

The define command allows you to create user variables in SQL.

The undefine command allows you to delete user variables.

Reminder: all SQL and SQL*Plus commands are case insensitive.

Examples

Try typing the following at the SQL> prompt.

DEFINE job_type = 'CLERK'

Essentially this define command creates a variable called job_type and assigns it the value 'CLERK' (a character string of type varchar2).

Define may also be used to display the values of a specific user variable.

To do this type DEF[INE] at the SQL> prompt to list all the current definitions.

Try (and observe)

SQL> def

SQL> def job_type

SQL> undefine job_type

SQL> def

10.1 More on Defining User Variables

The general structure of the command is

DEFINE variable_name = 'sometext'

E.g.

DEFINE salary = '2000

DEFINE job_type = CLERK'

Note: even if you type DEFINE salary = 2000 SQL*Plus will assign to the variable salary a character string. Oracle is rather 'relaxed' (sloppy?) about implicit data conversions.

Example

Enter the following at the SQL> prompt

(It is preferable to prepare a command file (script file) called, say, def1.sql and then use Start def1 or @def1

/* The SET COMMANDS WILL BE EXPLAINED LATER */

SET DOC OFF

SET VERIFY ON

DEFINE sal_check = '1000'

DEFINE job_type = 'CLERK'

/* We can now refer to these variables using &

NOTE THE USE OF THE QUOTES ' ' for &job_type. This is needed to tell Oracle that it really is a string. Yes, this is puzzling and a little inconsistent!

Select ename, sal, job

From emp

Where sal < TO_NUMBER(&sal_check)

And job = '&job_type'

It is always good practice to delete the definitions

if they are no longer needed. See below.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDEFINE sal_check

UNDEFINE job_type

And the output is :

old 3: Where sal < &sal_check

new 3: Where sal < 1000

old 4: And job = '&job_type'

new 4: And job = 'CLERK'

Press Return To Continue =>

EMPNO ENAME SAL JOB

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

7369 SMITH 800 CLERK

7900 JAMES 950 CLERK

Note that the display

old 3: Where sal < &sal_check

new 3: Where sal < 1000

old 4: And job = '&job_type'

new 4: And job = 'CLERK'

shows the how the substitution process works. N.B. The substitution of values for job_type and sal_check is carried out before the command is sent to the database engine.

N.B. You can disable the display of 'old' and 'new' values by using the command SET VERIFY OFF which is what you are now recommended to do. Note that set verify off is included in your login.sql file and will be the default.

Example

Enter the following at the SQL> prompt (or of course prepare a command file called, say, def2.sql and use Start def2 or @def2

DEFINE remun = 12*sal + NVL(comm, 0)'

Select ename,

job,

&remun 'Remuneration'

From emp

Order By &remun;

NB Here we have a more obvious simple text substitution for &renum.

And the output is :

ENAME JOB Remuneration

SMITH CLERK 9600

JAMES CLERK 11400

ADAMS CLERK 13200

WARD   SALESMAN 15500

MILLER CLERK 15600

MARTIN SALESMAN 16400

TURNER SALESMAN 18000

ALLEN SALESMAN 19500

CLARK MANAGER 29400

ETC.

Reminders

10.1.1 Displaying Current Values

Simply type DEFINE variable_name at the SQL> prompt

E.g.

DEFINE job_type (or just def job_type)

Typing DEFINE on its own will display the values of all currently define variables.

Note that Oracle defines a few standard variables when it is loaded.

10.1.2 The Undefine Command

To delete the value of a variable simple type

UNDEFINE variable_name

at the SQL> prompt

Note that you can easily re-define a variable by using the DEFINE command again and the new definition simply overwrites the old definition.

10.2 Substitution Variables

Substitution variables are a simple extension of the variables discussed above. SQL*Plus allows you to place user variables in your script to indicate where you want the user to supply values at run-time.

These substitution variables are identified by preceding the variable name with either a single or double ampersand
(i.e. & or &&). For example

Select hiredate, job, ename

From emp

Where empno = &Employee_Number;

Note the ampersand &. The variable Employee_Number has not been previously defined and so SQL*Plus will prompt the user to provide a value as shown below

Enter value for employee_number: 7788

Press Return To Continue =>

HIREDATE JOB ENAME

09-DEC-82 ANALYST SCOTT

A SQL script can have more than one substitution variable.

E.g. as before we can use

Select empno, ename, sal, job

From emp

Where sal < &sal_check

And job = '&job_type'

This time we shall be prompted for both sal_check and job_type.

Note the use of the single quotation marks around job_type. This is needed as job_type needs to be a character string. The output and prompts will look like

Enter value for sal_check: 1200

Enter value for job_type: CLERK

Press Return To Continue =>

EMPNO ENAME SAL JOB

7369 SMITH 800 CLERK

7876 ADAMS 1100 CLERK

7900 JAMES 950 CLERK

Note you could also use

Select empno, ename, sal, job

From emp

Where sal < &sal_check

And job = &job_type

and then type in 'CLERK' at the job_type response.

For more flexibility try

SELECT empno, ename, sal, job

From emp

WHERE sal < &sal_check

AND job = UPPER('&job_type')

This will allow you to enter something like cleRk at the prompt and all will work as required.

<aside>

For the ultimate in flexibility try

Select &cols

From &tabs

Where &conditions

Variations of this technique can be useful in accessing Oracle's Data Dictionary views and tables.

</aside>

10.2.1 Using Double-Ampersand Substitution Variables

Using a double ampersand in front of a substitution variable tells SQL*Plus to define that variable for the duration of the session (e.g. until you log off SQL*Plus). This is very useful when you need to reference a variable several times in the same script/command file as you don't wish to prompt for its value each time.

Example

Create and run the following command file

DESCRIBE &&Tab_Name

REM *** The old and new displays will NOT appear here

REM *** SQL*Plus only does this for queries

REM *** This command could be used to remind you

REM *** of the column definitions

Select &cols

From &Tab_Name;

N.B. Once the variable Tab_Name has been preceded by two ampersands subsequent references could use a single or double ampersand - both will use the previously defined value. Try running the above file 2 or 3 times in succession - you will only be prompted for cols after the first execution.

10.2.2 Invoking A Command File Containing Substitution Variables

Create a command file called job1.sql containing the following text

Select empno, ename

From emp

Where job = '&1';

Now execute the query using START (or @) followed by a parameter    E.g.

SQL>start job1 CLERK

and CLERK is then substituted for &1. (Something like parameter passing in Java and VB.NET.)

N.B. Commands containing substitution variables such as &1, &2 etc. must be invoked by using START (or @).

Try Start job1 SALESMAN

Exercise 7

Type in 12*sal + NVL(comm, 0) at the prompt that is generated by the query below and observe what happens.

Select deptno, &Remuneration What is this

From emp;

Write a query to display the number of employees in a department whose department number (deptno) is to be entered at run-time.

Write a query to display the salary, location and hiredate of all employees who were hired between two dates which are to entered at run time.

10.3 The ACCEPT Command

This command allows you to enter a value to be stored in a variable at run time. This is a more powerful way of creating substitution variables.
This variable can then be referenced in a SQL statement.

ACCEPT is often used in SQL command files.

The advantages of using ACCEPT (over substitution variables are:

with ACCEPT there is a limited form of type checking;

( N.B. NUMBER CHAR and DATE only .. see later for definitions)

you can arrange for more helpful prompts;

responses can be hidden.

The general syntax is of the form:

ACCEPT variable [DATA TYPE]

[FORMAT format_spec]

[DEFAULT default_value]

[PROMPT 'prompt text' | NOPROMPT]

[HIDE]

(where the [ ] brackets imply optionality and [a | b] means a OR b)

The data types allowed are only the generic NUMBER, CHAR and DATE

Example

Type in the following at the SQL> prompt

ACCEPT testsalary NUMBER PROMPT 'Enter Salary: '

Select *

From emp

Where sal > &testsalary;

Notes: testsalary is the name of the variable

NUMBER is its generic data type which will be checked at input.

SQL*Plus will prompt you with Enter Salary:

You can type in an appropriate number which will be then stored in the variable testsalary.

As before you can check which variables have been defined simply type define at the SQL> prompt or, specifically define testsalary

As before, to cancel this definition simply type undefine testsalary

Example

ACCEPT testsalary NUMBER FORMAT 9999.99 PROMPT 'Enter Salary: '

Example

Type in the following at the SQL> prompt

Accept testjob Char prompt 'Enter a job title: '

and then type in and execute the query

Select * From emp Where job = '&testjob';

Note the use of single quotes around &testjob (it is a character string).

ACCEPT testjob Char Format A6 Prompt 'Enter job title > '

Example

Type in the following at the SQL> prompt

Accept password char prompt 'Enter Password > ' Hide

This will not echo your input onto the screen. This is achieved by using the Hide option.

[The rest of Section 10 is for background reading]

10.4 Bind Variables

Unlike substitution variables bind variables are 'proper' variables, having both a data type and a size.

Bind variables provide a mechanism for returning data from a PL/SQL block back to SQL*Plus where it can be used for subsequent queries or passed into another PL/SQL block.

Example

Declare Bind Variables in your SQL*Plus script/command file

variable x number

When used inside a PL/SQL block, use a to preface the variable name

Here follows perhaps the simplest PL/SQL block.

This does exactly what you think it does!

execute x := 7729;

/* ----------- Now use in a SQL query ------------ */

Select ename, hiredate

From emp

Where empno = :x;

--- Now use in the following PL/SQL bloc ----- ----- ---- */

BEGIN

DBMS_OUTPUT.PUT_LINE(:x);

END;

NOTES:

Bind variables are 'lost' at the length of the SQL*Plus session.

DBMS_OUTPUT.PUT_LINE is a PL/SQL built-in procedure which can be used to display items (strictly a string of characters/ text) on the screen. See PL/SQL notes.

10.4.1 Declaring a Bind Variable

General syntax

VAR[IABLE] variable_name data_type

(i.e. variable can be abbreviated to var)

E.g. var x varchar2(10)

Variable Names

They must start with a letter and can be up to 30 characters long. The allowable characters include letters, digits, _ , £, $ .

Some of the 'Allowable Data Types'

NUMBER (but not NUMBER(a, b) or NUMBER(n))

CHAR(n)

VARCHAR2(n)

Checking Current Variables

Simply type VARIABLE or VARIABLE var_name at the SQL> prompt.

N.B. You cannot remove a variable once you've defined it without quitting the session, i.e. there is no equivalent of undefine

However you can replace it by simply using the variable command again: e.g. typing var x char(2) will replace any previous definition of x without warning.

10.5 Combining Bind and Substitution Variables

This is not straightforward as they were designed to carry out different tasks.

10.5.1 From Substitution to Bind

Example

REM *** This isn't a very useful example but illustrates the

REM *** many of the basic principles.

REM *** We define to substitution and two bind variables

ACCEPT sub_x CHAR FORMAT A10 PROMPT Enter a name >

DEFINE sub_y = 3000

VARIABLE bind_a VARCHAR2(10)

VARIABLE bind_b NUMBER

REM *** Now give the bind variables the values of the

REM *** substitution variables

EXECUTE :bind_a := &sub_x

EXECUTE :bind_b := &sub_y

REM *** Now carry out a simple query using the bind

REM *** variables(see also SQL*Forms later)

Select ename, sal, hiredate

From emp

Where ename = :bind_a

Or sal = :bind_b

REM *** and to finish off do some PL/SQL

EXECUTE DBMS_OUTPUT.PUT_LINE(:bind_a)

EXECUTE DBMS_OUTPUT.PUT_LINE(:bind_b)

REM *** Ah well, to really finish off .. back to SQL*Plus

PRINT bind_a

PRINT bind_b

NOTES

EXECUTE is a way of running a simple PL/SQL command. It provides the BEGIN END structure for you.

As the substitution variable is a string the reference to it must be contained in single quotes.

PRINT is a SQL*Plus command that can be used to display the values of bind variables.

(This next section contains some tricky material. You can omit it at the first reading)

10.5.2 From Bind to Substitution

Taking a value from a bind variable and placing it into a substitution variable is a more difficult task. The example below illustrates the technique. As you will see it is somewhat convoluted!

Example

SET VERIFY OFF

DEFINE sub_x = '' -- Define and Initialise the substitution variable

VARIABLE bind_y VARCHAR2(20) -- Define the bind variable

EXECUTE :bind_y := 'SMITH' -- Initialise the bind variable

COLUMN temp NEW_VALUE sub_x /* Arrange for the value of a column called temp to be assigned to sub_x */

SELECT :bind_y as temp FROM DUAL /* The Select statement will

return the value of :bind_y into the

alias temp and because of the

New_value option above this will

be stored in sub_x */

Select empno, ename From emp

Where ename = '&sub_x' /* And now to check to see if SMITH's

details are selected */

Prompt Hello &sub_x -- Just to confirm

SET VERIFY ON

Summary of Bind vs. Substitution

10.5.3 Displaying the Contents of a Bind Variable

This is done using the PRINT command which we have already met. A command such as

PRINT bind_variable

will display the value of the variable.

You can also use

PRINT :bind_variable

if you wish)

The bind variable is treated like a database column with the variable name being the default column heading. Thus you can use the Column (see Chapter 14) commands to tailor the output. E.g.

VAR x NUMBER

EXEC :x := 3445

COLUMN x FORMAT 9,999.00 HEADING Fancy That

PRINT x

which will produce the output

Fancy That

3,445.00

11.0 An Introduction To Report Writing Using SQL*Plus

11.1 Running SQL Queries: A Quick Review

SQL statements will (usually) be entered in multi-line command and will usually be prepared using a full screen text editor such as Notepad++.

Blank lines are not allowed in SQL command files

SQL*Plus does not support blank lines within SQL statements, even though the Oracle server itself has no trouble with them. For example:

CREATE TABLE dept

deptno NUMBER(2),
dname  VARCHAR2(20) NOT NULL,

loc    VARCHAR2(20) NOT NULL,
constraint pk_dept PRIMARY KEY (deptno)

will not be accepted.

A simple workaround is to use a comment facility such as 'double hyphen' --

CREATE TABLE dept

deptno NUMBER(2),
dname  VARCHAR2(20) NOT NULL,

loc    VARCHAR2(20) NOT NULL,
constraint pk_dept PRIMARY KEY (deptno)

Sometimes just using the 'commas' judiciously can have the same effect of 'spacing'.

CREATE TABLE dept

deptno NUMBER(2)

dname  VARCHAR2(20) NOT NULL

loc    VARCHAR2(20) NOT NULL

constraint pk_dept PRIMARY KEY (deptno)

The same applies to simple SQL queries. The following will work but simply having a blank line after sal, will not,

Select  ename,
sal,

deptno
From emp

Command Terminators

SQL statements can be terminated by:

a semi-colon (;), which is usually placed at the end of the final line of the statement;

a forward slash character (/) but this must be on a separate line on its own and must be the first character in that line;

a blank line - which simply causes the file to be stored in the SQL buffer ready for execution.

E.g.

INSERT INTO Dept (deptno, dname, loc)
VALUES (50, 'TEST', 'TEST')

or

INSERT INTO Dept (deptno, dname, loc)
VALUES (51, 'TEST', 'TEST');

11.2 The PROMPT Command

Although there is a PROMPT option within the ACCEPT command the PROMPT command can be used on its own to provide a user with useful additional guidance about what to enter.

The following example is indicative of the feedback that can be given to the user. The Prompt command used on its own is useful to provide a blank line to aid the overall layout.

PROMPT *** Choose a table name such as Emp or Dept
PROMPT
ACCEPT tab_name CHAR PROMPT 'Enter Table Name > '
PROMPT
PROMPT This is about the *** &&tab_name table ***
PROMPT
DESCRIBE &&tab_name
PROMPT
PROMPT * These are the columns of the &&tab_name table *
PROMPT
UNDEFINE tab_name

Note that after ac ACCEPT command we can use either & or && and the same value will be used throughout the session.

11.3 Placing Comments in your code

As you will recall there are three ways that this can be done:

using REM[ARK]

using the double hyphen --

using /* . */

11.3.1 Points To Note

REMARK commands can only be used at the start of a line and cannot span more than one line without the use of the continuation character -

REMARK is a SQL*Plus command and cannot be used within a SQL 'query'.

The /* . */ delimiters are a feature of many programming languages and they allow comments to span several lines.
When you use this feature as follows (i.e. with the delimiters on separate lines)
/* ----- ----- --------- ----- ----
Now for some comments.
This is an extended comment.
----- ----- --------- ----- ----- */
and these comments are outside a query then the comments are echoed to the screen.
To suppress these comments you use the SET DOC OFF command.

The double hyphen commands can be used anywhere in a script file but cannot span more than one line.

In each case (i.e. REM, /* , -- ) leave at least one space before typing your comments.

SQL*Plus does not check for substitution variables embedded in a comment EXCEPT when those comments are embedded within a query (or a PL/SQL block).

SQL*Plus does not check SQL commands. This is done by the database software.

Example

This is just a demo of some of the above points. Note that using &x here has no effect.

REM *** This is just a demonstration of what can be done - *** with REM and the continuation character
-- This comment cannot span more than one line
REM *** and in the SQL query .. no blank lines!

Select  ename, sal, deptno -- but usefully comments can go here
From    emp
Where   /* Here is a comment explaining the
the conditions that follow */
sal < ( Select max(sal) -- Hello &y Don't use & here!
From emp
Where job = 'CLERK')

11.4 Reports in SQL*Plus

SQL*Plus is a primitive but very effective reporting tool and it is always available to an Oracle programmer / consultant whereas the more sophisticated reporting tools that can be used for generating complex reports, with a full range of formatting and output capabilities, are not.

Generating a report, of course, needs a simple methodology. For example: (some of these are optional)

Formulate the query (or queries);

Format the columns;

Add Headers and Footers;

Add totals, subtotals, averages etc.;

Format the Page, add page breaks etc.;

Print/ display the output.

This section covers some of the features available in SQL*Plus. All the SQL*Plus commands are available at the SQL> prompt. Indeed you have already met some of the features of SQL*Plus. For example:

Describe

Start or @

Get

Define

Accept

Save

Spool

c (to invoke the Line Editor)

Rem

You have also installed a LOGIN.SQL file which controls the SQL environment by using the SQL*Plus set commands (see later).

Recall that SQL*Plus commands are

case insensitive single line commands (but you can extend them over more than one line if you wish) and

do not have to end with a semi-colon (

In most cases the best way to use SQL*Plus commands is to include them in SQL*Plus command file.

Parameters that you set up using SQL*Plus commands stay in force until you log off from SQL or change them or clear them.

Example (Read the whole page first before executing the file

Prepare the following file using your usual text editor. Call it plus1.sql It provides some simple illustrations of the Column and Heading commands. (Note that the text following the Heading command is case sensitive.)

In this example the SQL*Plus commands have been typed in bold.

Test the above SQL command file by using START plus1. It is essential that you get this to work. Once you are comfortable with what you think it is doing - experiment. More details of the Column, Heading and Format commands are given below.

11.5 Column Commands

These establish display options for a column. E.g.

Column Ename Format A11 Heading Salespeople

Format A11 refers to the data type of column Tax (A for alphanumeric in this case) and sets the width for display to 11 characters. See below for more details on the options available.

Heading 'Salespeople' effectively replaces the default heading (which would be Ename) with Salespeople.
If you wish the heading to contain a space the place the heading text in single quotes. E.g. 'Sales Staff'. In fact it is good practice to always include them.

The options that can follow Column, such as Format and Heading , can appear in any order.

To determine the current settings type column column_name (e.f. column ename) or column column_alias. To view all the options simply type column.

To clear a column setting simply type Column column_name Clear.

To reset all the columns to their default settings use Clear Columns.

Resetting all columns is good practice and you are encouraged to do so by including this command in your command files. See the example above.

To have a heading covering more than one line use | as follows
Column sal Heading Monthly|Salary

11.6 The Format Options

An alphanumeric n characters wide

for numeric fields e.g. format 999999 for a field 6 digits wide

use to enforce leading zeroes e.g. format 0999 (or format 0 for one digit) to ensure that zeroes are printed rather than blanks when the value to be displayed evaluates to zero

use to insert a decimal point e.g. format 9999.99

use to insert a comma e.g. format 999,999

EEEE use to invoke scientific notation e.g. format 99.99EEEE

B use to display zero values as blanks e.g. format B999

11.6.1 Errors

If after execution a column displays ##### then it probably implies that the (formatting ) width is too small for the data.

A display of a % means the wrong data type has been used.

11.7 Some Options Available in the Column Command.

Some of the other COLUMN options are given below. Experiment

11.8 A Detailed Example Using SQL*Plus Commands

Create the following text file and save it in your Scripts sub directory as PLUS2.SQL. N.B A .sql file can contain several independent SQL scripts. Each one must finish with a semi-colon or (and this is safer) have a / as the only character on the following line.

TEST THIS CAREFULLY.
CHECK THE OUTPUT CAREFULLY

EXPERIMENT

See overleaf.

Empl Y-T-D

Dept Job No. SAL Commission Total Income

010 PRESIDENT 7839 £5,000.00 No Comm £60,000.00

030 MANAGER 7698 £2,850.00 No Comm £34,200.00

010 MANAGER 7782 £2,450.00 No Comm £29,400.00

020 MANAGER 7566 £2,975.00 No Comm £35,700.00

020 ANALYST 7902 £3,000.00 No Comm £36,000.00

020 CLERK 7369 £800.00 No Comm £9,600.00

030 SALESMAN 7499 £1,600.00 £300.00 £19,500.00

030 SALESMAN 7521 £1,250.00 £500.00 £15,500.00

030 SALESMAN 7654 £1,250.00 £1,400.00 £16,400.00

020 ANALYST 7788 £3,000.00 No Comm £36,000.00

030 SALESMAN 7844 £1,500.00 £0.00 £18,000.00

020 CLERK 7876 £1,100.00 No Comm £13,200.00

030 CLERK 7900 £950.00 No Comm £11,400.00

010 CLERK 7934 £1,300.00 No Comm £15,600.00

Notes

1. The output in the Y-T-D/Commission column is a little ragged. A simple way to adjust this is to edit the corresponding Column format specification so that the text and the numbers line up. Amend PLUS2.SQL by changing the Comm specification to

Column Comm Format 99,990.99 -

Heading Y-T-D|Commission -

Null ' No Comm'

How many spaces do you need to add to the string in the Null option? Experiment. Note that you have to use quotes to ensure that the spaces are printed.

The use of the format mask L99,999.99 has increased the amount of space needed for the display. This annoying feature can be side-stepped by using

substr(to_char(sal,'L99,999.99'),8) Salary

in the main query and removing the column formatting options.

TEST the above command file THOROUGHLY . then test it again, then again - and experiment!!

N.B. The SELECT clause can contain column names, column aliases, columns with a table prefix, expressions and literal strings.

These can determine the column name specified in the SQL*Plus COLUMN command. So, for example :

if you use sal, emp.sal or e.sal in the select command then use Column sal in the SQL*Plus command file.

if you use a column alias (e.g. select sal*12 annsal) then you must use this alias in the corresponding Column command in the SQL*Plus command file i.e. use Column annsal etc.

<Aside1>

The use of the 'L' in L99,999.00 will invoke the £ sign in the display (as shown above) as long as you have executed the command
alter session set NLS_CURRENCY = '£';

Note, you have already done so if you have activated login.sql correctly.

</Aside1>

<Aside2>

If you use Empno||'-'||Ename in the select command then use

Column Empno||'-'||Ename in the Sql*Plus File.

</Aside2>

Exercise

Check on the effect of the SQL*Plus command CLEAR SCREEN (or clear screen - the command is case insensitive).

(Typing it at the SQL> prompt may give you a hint!)

11.9 Set Commands

They assign a value to a system variable (see below).

They can be used in a LOGIN.SQL file to make certain values the defaults for a session (check your own LOGIN.SQL file sometime

To determine the current default values use the Show command at the SQL> prompt (e.g. Show Pagesize, Show All

They can also be placed in your SQL*Plus command files. See examples given below.

Some of the more useful possibilities are given below. Use HELP to get a full list of options together with brief explanations. Feel free to experiment.

Exercise 8

Use the supplied documentation 'SQLPlus - User Guide and Reference.pdf' to:

determine what the commands in the supplied login.sql file actually do;

investigate the semantics for the following options for the Set command options

Heading, Wrap, Numformat, Sqlprompt, Sqlprefix, Autocommit.

11.10 The TTITLE and BTITLE Commands

These are used to give titles to the reports; they are similar in one sense to the headers and footers you have in Word Processing files.

E.G. 

TTITLE 'Weekly Report'

BTITLE 'Summary - over the page'

Using the above format then, by default, the Ttitle command:

displays the current date in the top left hand corner of each page;

displays the page number in the top right hand corner of each page;

centres the text (e.g. 'Weekly Report') on the next line down.

Note: if you tailor the TTITLE command in any way then you do not get any automatic page number, date and centring. But see below .

The BTITLE command prints the text centred at the bottom of each page.

For both commands the '|' character can be used to split text over more than one line. Examples will follow.

Typing TTITLE or BTITLE at the SQL> prompt simply displays their current values.

Exercise 9

Make the following additions to your plus2.sql file and then test it.

REM ***** To test the TITLE commands ****

Ttitle 'Company Report|Produced By The Personnel Department'

Btitle 'CONFIDENTIAL'

Ttitle off

Btitle off

Clear columns

Rem *** Title and Column definitions now reset ****

11.11 Tailored Titles And Footnotes

The TTITLE and BTITLE commands can include a number of clauses enabling the appearance of the title to be specified in more detail

The general structure is

TTITLE [printspec [text | variable] ] [OFF | ON]

(Note : items in [ ] brackets are optional entries whilst items separated by a | are alternatives)

Some of the clauses that could appear in printspec are :-

LEFT

RIGHT,

CENTER are used to align the text (Note spelling of CENTER)

COL n which starts printing at position n of the current line

SKIP n skips to the start of a new line n times. If n is omitted then it is assumed to be 0.

FORMAT CHAR specifies the format model of the data items that are to follow it. It is similar to the Format command used with Column

Some of the standard variables that can be used are:

SQL.PNO used to generate current page number;

SQL.USER used to generate current user name.

Example

Try out the following. Save as plus3.sql

TTITLE LEFT FORMAT 099 'Page :' SQL.PNO -

RIGHT 'Produced by : Accounting 'SKIP 2 -

RIGHT '----- ----- ----------------' SKIP 2

REMARK Note use of the continuation character -

BTITLE CENTER 'End of Report' SKIP -

CENTER '----- ----- -----'

Select Ename, Job, Sal, Comm

From Emp

Where Comm is not null;

TTITLE OFF

BTITLE OFF

REM Note use of Ttitle Off and Btitle Off: this is good practice.

11.12 Writing A Basic Report: Outline Steps

Construct and test the select statements using your usual editor. Save and Test.

Add your formatting commands (Column, Heading, Ttitle etc.)

Add totals, subtotals etc. (see later)

Add comments/remarks to help in documentation/maintenance. Note the use of REMARK, /* ..*/ and -- .

Add any page formatting commands you need

Add the Clear commands (see later for more examples).

Print/Display

There is no definitive style for the layout of SQL and SQL*Plus commands. But, whatever style you choose be consistent

The use of the TAB key will often enhance the layout.

Some authors prefer to use lower case for all commands whilst others use a mix of lower case, upper case and initial capital letters.

When you decide on a strategy then you may wish to distinguish between commands (e.g. SELECT), functions (e.g. To_char), tables (e.g. Emp) and column names (e.g. ename).

Consider

SELECT Initcap(ename) A,

sal*12 + Nvl(Comm,0) B,

To_char(hiredate,'MM,YY') C,

mgr D

FROM Emp

ORDER BY ename;

This style is NOT obligatory and will not be enforced in these notes either.

Exercise 10

Create and test the following. Call it plus4.sql. Check and print out the output.

Set echo off

Remark *** Set up the SQL*Plus environment ***

Set Pagesize 24

Set Feedback Off

Remark *** Format the Columns ***

Column a Format A10 Heading Department

Column b Format A9 Heading Job

Column c Format 9999 Heading Emp|No.

Column d Format A8 Heading Name

Column e Format A5 Heading Hire|Date

Column f Format B99,999.99 Heading Monthly|Salary

Column g Format 99,990.99 Heading Annual|Comm

Column h Format 999,999.99 Heading Total

Remark *** Setting up Titles ***

Ttitle 'E M P L O Y E E R E P O R T'

Btitle 'Confidential'

Remark *** The Query ***

SELECT dname a, -- select the department name

job b, -- and the job

empno c, -- etc. etc. Don't over do this!!

ename d,

To_char(hiredate, 'MM/YY') e,

sal f,

comm g,

sal * 12 + Nvl(comm, 0) h -- use Nvl because..

FROM Emp p, Dept d

WHERE p.deptno = d.deptno -- comments

ORDER BY dname, job

Remark *** Reset the environment ***

Clear columns

Ttitle Off

Btitle Off

Set Feedback on

Set Pagesize 20

Set Echo on

The output is given on the next page.

Tue Sep 30   page 1

E M P L O Y E E R E P O R T

Emp Hire Monthly Annual

Department Job No. Name Date Salary Comm Total

ACCOUNTING CLERK 7934 MILLER 01/82 1,300.00 15,600.00

ACCOUNTING MANAGER 7782 CLARK 06/81 2,450.00 29,400.00

ACCOUNTING PRESIDENT 7839 KING 11/81 5,000.00 60,000.00

RESEARCH ANALYST 7902 FORD 12/81 3,000.00 36,000.00

RESEARCH ANALYST 7788 SCOTT 12/82 3,000.00 36,000.00

RESEARCH CLERK 7369 SMITH 12/80 800.00 9,600.00

RESEARCH CLERK 7876 ADAMS 01/83 1,100.00 13,200.00

RESEARCH MANAGER 7566 JONES 04/81 2,975.00 35,700.00

SALES CLERK 7900 JAMES 12/81 950.00 11,400.00

SALES MANAGER 7698 BLAKE 05/81 2,850.00 34,200.00

SALES SALESMAN 7499 ALLEN 02/81 1,600.00 300.00 19,500.00

SALES SALESMAN 7521 WARD 02/81 1,250.00 500.00 15,500.00

SALES SALESMAN 7844 TURNER 09/81 1,500.00 0.00 18,000.00

SALES SALESMAN 7654 MARTIN 09/81 1,250.00 1,400.00 16,400.00

Confidential

11.13 The Break Command

The rows of a report may be broken up into sections by using the BREAK command. This command can also (by default) suppress the display of duplicate values in the column used in the Break command.

N.B. You must have deptno as one of the selected columns for the 'breaking' effect to work.   If you don't wish to see deptno displayed when the command is executed then add the command Column deptno noprint before the query. Also add, say, Skip 1 at the end of the Break command - else you will not see any effect if you do not display deptno

Example

Try this and observe. Save as plus5.sql

Break On deptno

SELECT deptno, job, empno, sal

FROM Emp

ORDER BY deptno

The output is of the form . (see next page)

DEPTNO JOB EMPNO SAL

10 PRESIDENT 7839 5000

MANAGER 7782 2450

CLERK 7934 1300

20 MANAGER 7566 2975

CLERK 7369 800

ANALYST 7902 3000

ANALYST 7788 3000

CLERK 7876 1100

30 MANAGER 7698 2850

SALESMAN 7499 1600

SALESMAN 7521 1250

CLERK 7900 950

SALESMAN 7844 1500

SALESMAN 7654 1250

14 rows selected.

Since a break will occur each time the column value changes you must use the
Order By clause otherwise the report will be split into meaningless sections.

N.B. There can only be ONE Break command active at any one time.

Thus if you require multiple breaks then they all have to be specified in the same Break command.

You should always list the break columns in order of importance, i.e. major breaks first.

Amend plus5.sql as shown below and note the output

Break On deptno On job On sal

Select deptno, job, empno, sal

From Emp

Order By deptno, job, sal

N.B. Note the sequencing of the columns in the Break command and the Order by clause must match.

The general syntax of the Break command allows for an action to be specified after each On column_name option. The actions could be one of:

Skip Page effectively issues a form-feed when the value in a column changes

Skip n skips n lines when a value changes

Duplicate duplicates values - the default is Noduplicate

Try the following amendments to plus5.sql

Break On deptno skip page -

On job skip 1 -

On sal skip 1

Select deptno, job, empno, sal

From Emp

Order By deptno, job, sal

The output from this command file is given below. Check and note what is happening. It may not be that obvious. Try it again without the skip commands.

DEPTNO JOB EMPNO SAL

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

10 CLERK 7934 1300

MANAGER 7782 2450

PRESIDENT 7839 5000

DEPTNO JOB EMPNO SAL

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

20 ANALYST 7788 3000

7902

CLERK 7369 800

7876 1100

MANAGER 7566 2975

DEPTNO JOB EMPNO SAL

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

30 CLERK 7900 950

MANAGER 7698 2850

SALESMAN 7521 1250

7654

7844 1500

7499 1600

14 rows selected.

N.B.

To remove breaks use the Clear Breaks command.

To display the current Break settings use the Break command

If column aliases have been used in the Select clause then the Break command MUST use these aliases.

e.g.

Break On d On j On sal

Select deptno d, job j, empno, sal

From Emp

Order By deptno, job, sal;

Clear Breaks

NB: if you use table aliases in your query then any associated column command must also use that alias.

e.g. column e.deptno heading, Etc.

Select e.deptno,

From Emp e, Dept d ,

BUT any associated Break command must only use deptno rather than e.deptno.

e.g. column e.deptno format

Break On deptno

Select e.deptno, .

From Emp e, Dept d , .

11.14 The Compute Command

The Compute command instructs SQL*Plus to perform calculations. These are summary calculations (sums, averages etc.) and are controlled by the Break command.

The general structure of the Compute command is (note her that | means 'or')

Compute clauses Of Columns|Expressions

On Break_columns|Report|Row

The clauses that can be used include

Unlike the Break command which only appears once per command file there may be many Compute commands although it is often easier to specify all the computes required in one command. E.g.

Compute Sum Avg Of Sal Comm On deptno Report

(i.e. you are 'summarising Sal and Comm based on groups identified by different values of deptno (and on the complete report)).

This will find the sum and average of the values stored in the Sal and Comm columns printing them out for each department (as long as there was a Break on Deptno active) and for the 'complete selection' (as long as there is a Break On Report active).

[Aside: What would happen if we replace Comm in the above command with Nvl(Comm, 0)? Try it.]

N.B.

To clear Compute settings simply type Clear Computes

To display current settings type Compute

As an example, consider the following SQL*Plus command file.

set pagesize 30

column sal format L99,999

break on deptno skip 1 on report

compute sum of sal on deptno

Select deptno, job, empno, sal

From emp

Order By deptno

clear columns

clear computes

clear breaks

which has the output given overleaf. Note the use of the 'L' as a formatting character and then check your login.sql file to check why this has worked.

Output overleaf

DEPTNO JOB EMPNO SAL

10 PRESIDENT 7839 £5,000

MANAGER 7782 £2,450

CLERK 7934 £1,300

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

sum  £8,750

20 MANAGER 7566 £2,975

CLERK 7369 £800

ANALYST 7902 £3,000

ANALYST 7788 £3,000

CLERK 7876 £1,100

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

sum £10,875

30 MANAGER 7698 £2,850

SALESMAN 7499 £1,600

SALESMAN 7521 £1,250

CLERK 7900 £950

SALESMAN 7844 £1,500

SALESMAN 7654 £1,250

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

sum  £9,400

14 rows selected.

Enter the above command and experiment. What if we chose not to display the deptno column? Find out!

Exercise 11

Amend plus2.sql to read as follows and save it as plus6.sql.

Set Echo Off

Set Pagesize 24

Set Feedback Off

Column Deptno Format 099 Heading ' Dept'

/* Why the spaces in front of Dept ?

See Label commands below */

Column Job Format A9 Heading ' Job' Justify Right

Column Empno Format 9999 Heading Emp|No.

Column Sal Format 99,999.99 Heading Monthly|Salary

Column Comm Format 99,990.99 Heading Y-T-D|Commission Null ' No Comm'

Column Totinc Format 999,999.99 Heading 'Total Income'

Ttitle 'COMPANY REPORT|Produced by Personnel Dept'

Btitle 'Company Confidential'

Break On Deptno Skip 1 On Job On Report

And now for a variation on a theme - the LABEL option

Compute Sum Label 'Dept Total: ' Of Sal On Deptno

Compute Avg Label 'Dept Average: ' Of Sal On Deptno

Compute Sum Label 'Overall Tot: ' Of Sal On Report

Compute Avg Label 'Overall Avg: ' Of Sal On Report

Define CommN = 'Nvl(Comm, 0)'

SELECT deptno,

job,

empno,

sal,

comm,

sal*12 + &CommN Totinc

FROM Emp

ORDER BY Deptno, Job

Clear Columns

Clear Breaks

Ttitle Off

Btitle Off

Set Pagesize 20

Set Feedback On

Set Echo On

Exercise 12

Reminder: If column aliases have been used in the Select clause then the Column, Break and Compute commands must also use these aliases.

(i)  Amend PLUS4.SQL to produce the following output. Save as Plus4a.sql

(ii)    Adjust your solution to cope with the display of a £ sign in the appropriate places

(iii)  Now amend your solution to only display the details corresponding to a particular department. The department number is to be entered at run time by using the Accept command.

11.15 Master Detail Formatting :The 'New_Value' Option

Suppose we wish to create a report that displays a manager's numbers at the top of a page and have the employees reporting to that manager on the same page - and that is repeated for each manager (i.e. someone who has staff reporting to them).

This is an example of what is termed a Master Detail report.

Another example would be to have a department name at the head of each page followed by a list of employees, and their details, which work for that department.

Surprisingly there is no elegant way of achieving this in SQL*Plus, however it can be done using user variables and the NEW_VALUE option of the column command.

The usual format is

COLUMN Column_Name NEW_VALUE user_variable

Column_name must be a legal column name of one of the tables involved in the subsequent query and user_variable has the same properties as if it was created using define, accept or & except that it is not referenced by invoking &user_variable and all we need to do is use user_variable

In this case we could use:

Column Mgr New_Value u_Mgr Noprint

The Noprint option suppresses the printing of the Mgr column in any Select statement. This doesn't matter as we are contriving to have the manager's number printed in the page title.

The above command has some similarity with a conventional statement of the form

u_Mgr := value of Mgr

but PL/SQL does not support this syntax.

However, in this case the value of u_Mgr is updated every time the query retrieves a new row of data from the database.

Enter the following and observe. Save it as nv1.sql.

Column Mgr New_Value u_Mgr Noprint

Ttitle Left 'Manager: ' u_Mgr Skip 2

Break On Mgr Skip Page

Select Mgr, Ename, Sal, Deptno

From Emp

Where Mgr In (7698, 7839)

Order By Mgr

The output should look something like this

Manager : 7698

ENAME SAL DEPTNO

ALLEN 1600 30

WARD 1250 30

TURNER 1500 30

MARTIN 1250 30

JAMES 950 30

Press Return To Continue =>

Manager : 7839

ENAME SAL DEPTNO

JONES 2975 20

BLAKE 2850 30

CLARK 2450 10

8 rows selected.

Note that the Mgr column has not been displayed.

Of course we can improve the layout using further features of Ttitle and Btitle

Exercise 13

Construct a command file that will display the name of the department at the head of each page followed by the name, salary and hiredate for each employee in each department.

Mimic the following layout as closely as possible.

Department Summary Report

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

DEPARTMENT : ACCOUNTING Page 1

ENAME SAL HIREDATE

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

CLARK 2450 09-JUN-81

KING 5000 17-NOV-81

MILLER 1300 23-JAN-82

End Of Report

Press Return To Continue =>

Department Summary Report

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

DEPARTMENT : RESEARCH Page 2

ENAME SAL HIREDATE

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

SMITH 800 17-DEC-80

ADAMS 1100 12-JAN-83

FORD 3000 03-DEC-81

SCOTT 3000 09-DEC-82

JONES 2975 02-APR-81

End Of Report

Press Return To Continue =>

Department Summary Report

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

DEPARTMENT : SALES Page 3

ENAME SAL HIREDATE

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

ALLEN 1600 20-FEB-81

BLAKE 2850 01-MAY-81

MARTIN 1250 28-SEP-81

JAMES 950 03-DEC-81

TURNER 1500 08-SEP-81

WARD 1250 22-FEB-81

End Of Report

14 rows selected.

11.16 Dates in Headers

It is very common practice to put the date in the header of a report. It is so obvious and so common it is quite surprising that it isn't simpler to do!

Again we have to rely on new_value

Create the following command file, calling in date1.sql and experiment. Suggested improvements will follow.

Printdate is the variable storing the value of Sysdate

Column Sysdate New_Value Printdate

And now we update Sysdate by 'fetching' it ..

Select Sysdate From Dual

Ttitle Left ' ~~~ HERE IS A REPORT ~~~ ' -

Right 'Date: ' Printdate Skip 2

Select Ename,

Hiredate

From Emp

Where Deptno = 30

Ttitle Off

Clear Columns

The output also displays the output from the

Select Sysdate from dual

command.

Can we suppress this? Read on!

Can we output the date in a different format? Of course. Amend date1.sql as suggested below and save it as date2.sql.

(see overleaf)

Set Echo Off -- Why?

Set Linesize 50 -- Why?

Set Termout Off -- Why?

Set Pause Off -- Why?

/* Today is an alias used in the Select clause of the

query used to, effectively retrieve sysdate.

Column Today New_Value Printdate

REM *** continued . .

Select To_Char(Sysdate, 'fmMonth DD, YYYY') Today

From Dual

Set Pause On

Set Termout On

Ttitle Left 'Date: ' Printdate -

Right Format 09 'Page:' sql.pno Skip 2 -

Center 'A report with reformatted date' Skip 1 -

Center '----- ----- --------- ----- ------' Skip 2

Select Ename,

Hiredate

From Emp

Where Deptno = 30

Ttitle Off

Clear Columns

Set Echo On

And the output will look something like

Date: September 15, 1999 Page: 01

A report with reformatted date

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

ENAME HIREDATE

ALLEN 20-FEB-81

WARD 22-FEB-81

MARTIN 28-SEP-81

BLAKE 01-MAY-81

TURNER 08-SEP-81

JAMES 03-DEC-81

11.17 Other uses of New_Value

Enter the command file given below and observe. What is happening?

Parameter Passing form one query to another? How else would you achieve the same effect without using new_value

Set Echo Off

Set Verify Off -- Why?

Set Pause Off

Column j Format A11 Heading 'Job With|Max Avg Sal'

Column   max_avg_sal New_Value max

Set Termout Off

Select max(avg(sal)) max_avg_sal

From emp

Where job != 'PRESIDENT'

Group By job

Set Termout On

select job j

from emp

group by job

having avg(sal) = &max

Clear Columns

Set Verify On

Set Pause On

Set Echo On

and the output looks like

Job With

Max Avg Sal

ANALYST

11.18 Typical Structure Of A SQL*Plus Command File

Rem *** Set up the environment ***

Set .

Col

Ttitle

Btitle

Break

Compute

Remark *** The query or queries ***

Select

Remark *** Re-set the environment ***

Clear Computes

Clear Breaks

BTitle Off

Ttitle Off

Clear Columns

Remark *** and undo the Set commands ***

11.19 Abbreviations

Most SQL*Plus commands, unlike SQL commands, can be abbreviated. We have deliberately not used any abbreviations in the above notes. In any case the abbreviations do not always aid clarity.

Some examples are given below.

11.20 User Interaction In SQL*Plus

(A brief re-visit)

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a command file.

Example 1

Create the following command file. Call it param.sql

Set echo off

Select *

From Emp

Where Job = '&1'

And Sal = &2

Now run it by typing

Start param CLERK 800

at the SQL> prompt.

CLERK (note the capitals) and 800 are the parameters that will be substituted for &1 and &2 respectively.

Of course you should also use the commands Prompt and Accept to communicate with the user. See previous notes.

Example 2

Set up the following command file, call it p1.sql and test it.

N.B. Note also how the variable used in the Accept command can also be used in the Ttitle command. It can be used without the preceding &.

Prompt Enter a Title of up to 30 characters

Accept Mytitle Prompt 'Title >> '

Ttitle Center &Mytitle Skip 2

Select * From dept;

Exercise 14

Find out about the commands REPHEADER and amend one of the reports you have already written to include the use of these commands.

11.21 Branching in SQL*Plus

SQL*Plus has no IF statement which can be rather annoying for a scripting language. There are some approaches to overcome this oversight (or, of course, use PL/SQL).

Example 1

Create a command file called update_sal.sql containing the following text.

ACCEPT s_update_confirm CHAR

PROMPT 'Do you wish to update (Y/N)? > '

UPDATE Emp

Set sal = sal + 100

Where Upper('&s_update_confirm') = 'Y';

It provides a primitive but reasonably effective 'do this if . ' option

Example 2

We can improve matters a shade. Amend update_sal.sql as follows.

ACCEPT s_update_confirm CHAR PROMPT 'Do you wish to update (Y/N)? > '

SET HEADING OFF

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

Select 'Please Type in Y or N'

From Dual

Where Upper('&s_update_confirm') Not In ('Y', 'N')

Or '&s_update_confirm' Is Null

SET FEEDBACK ON

SET PAGESIZE 1

SET HEADING ON

UPDATE Emp

Set sal = sal + 100

Where Upper('&s_update_confirm') = 'Y'

SET VERIFY ON

If you run this with incorrect input you will see something like

Do you wish to update (Y/N)? > b

Please Type in Y or N

0 rows updated.

Simple Branching using SQL

Example 1

Use the following text to create a file called (say) br1.sql.

Then invoke @br1 (or start br1) at the SQL prompt.

This only works properly if you have already prepared the files report1.sql, report2.sql and report3.sql

To test set report1.sql to contain

Select * from emp;

Set report2.sql to contain

Select * from dept;

Set report3.sql to contain

Select * from salgrade;

PROMPT

PROMPT 1 - Report One

PROMPT 2 - Report Two

PROMPT 2 - Report Three

PROMPT

ACCEPT choice char Prompt 'Enter your choice (1,2,3) > '

@report&choice

Example 2

Use the following text to create a file called (say) br2.sql.

Then invoke @br2 (or start br2) at the SQL prompt.

SET DOC OFF

CLEAR SCREEN

The function decode is covered in the next section

It performs the same functionality as a 'case' statement.

This allows you to trap an error in the input.

Prepare the file Error.sql first to include

PROMPT Incorrect Input

DEFINE padding = ''

PROMPT

PROMPT &padding 1 - Report One

PROMPT &padding 2 - Report Two

PROMPT &padding 3 - Report Three

PROMPT

ACCEPT choice CHAR Prompt ' Enter your choice (1,2,3) > '

SET TERMOUT OFF

SET PAUSE OFF

COLUMN entered_value NEW_VALUE Run_this

Select Decode('&choice',

'1', 'Report1.sql',

'2', 'Report2.sql',

'3', 'Report3.sql',

'Error.sql') entered_value

From Dual

SET TERMOUT ON

SET PAUSE ON

@&Run_this

11.23 Looping In SQL*Plus

Looping is not really supported in SQL*Plus so the advice is to use PL/SQL , except in the special case when you want user interaction.

Example

Create a file called finished.sql containing only the line

PROMPT That s all folks

Now create the following file and call it loopdemo.sql

SET DOC OFF

SET VERIFY OFF

Note that &1 in the first select clause will prompt you for the name of a table.

You could pass it a value by using a call such as

@loopdemo EMP

Select * from &1 -- input parameter

PROMPT

ACCEPT x CHAR PROMPT 'Next Table? >'

COL alias NOPRINT NEW_VALUE next_table

SET TERM OFF

SET PAUSE OFF

select decode('&x','','finished.sql',

'loopdemo.sql '||'&x') alias

from dual

Note the space here

SET TERM ON

SET PAUSE ON

/* and now run the command file 'chosen' by the decode command */

@&next_table

Now at the SQL> prompt type, say, @loopdemo EMP

11.24 Odds and Ends

SQL*Plus has a vast array of additional facilities. You are encouraged to tio use the SQL*Plus Reference file or countless websites.

E.g. what does the following achieve?

12.0 The Decode and Case Functions

Decode and Case are powerful SQL functions that allow a measure of conditional processing to be carried out very much like the traditional
if . then . else facilities in a high level programming language

12.1 Decode

The general structure is

DECODE ( column or expression,

search1, result1,

search2, result2,

etc. etc.,

default)

The column (or expression) is compared to each search value in turn and returns the corresponding result if there is a match

If no match is found, the DECODE function returns the default value.

If the default value is omitted then NULL is returned for unmatched values (never do this!)

Note

search must be of the same data type as column or expression

and all the result options must be of the same data type (but not necessarily of the same data type as search

12.2 Examples of the use of Decode

Work through all of these carefully.

Column A search value Result

Test each one in turn and satisfy yourselves that the output is what you would expect.

Select ename, job, sal Old_salary,

Decode(job, 'ANALYST', sal*1.1,

'CLERK', sal*1.2,

'MANAGER', sal*0.8,

sal) New_salary

From emp;

Default value