Oracle SQL - NULL and NUMFORMAT

  1. HOME
  2. Oracle SQL
  3. SQL Plus
  4. SQL*Plus number width

Introduction

The following code demonstrates NULL and NUMFORMAT.

The NULL system variable makes all null values more visible.

The NUMFORMAT variable formats the layout of all numeric columns. 

set numwidth 5
set null " [N/A]"

select ename, mgr, comm
from   emp
where  deptno = 10;

set numformat 09999.99

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w w. j  ava  2s .  co m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800.1234 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600.5678, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250.98765, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975.07860, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250.0009987, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850.999876, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450.230987, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> set numwidth 5
SQL> set null " [N/A]"
SQL>
SQL> select ename, mgr, comm
  2  from   emp
  3  where  deptno = 10;

ENAME    |       MGR |      COMM
-------- | --------- | ---------
WARD     |  07006.00 |  00500.00
BLAKE    |  07009.00 |  [N/A]
CLARK    |  07009.00 |  [N/A]
KING     |  [N/A]    |  [N/A]
MARY     |  07007.00 |  [N/A]

SQL>
SQL> set numformat 09999.99
SQL>

Related Topic