Oracle PL/SQL/Report Column Page/Column Number Format

Материал из SQL эксперт
Перейти к: навигация, поиск

Add dollar signs to the output

   <source lang="sql">

-- clear columns

SQL> SQL> -- create demo table SQL> create table TestTable(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    MyName             VARCHAR2(10 BYTE),
 4    MyDate             DATE,
 5    MyNumber           Number(8,2)
 6  )
 7  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.1 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.1 SQL> SQL> -- Add dollar signs to the output SQL> SQL> COLUMN MyNumber FORMAT $990.99; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 $12.12 1 Alison 22-JUN-97 -$12.12 1 Alison 13-MAY-98 $22.10 1 Alison 24-JUN-99 -$2.12 1 Alison 15-APR-00 $2.10 SQL> SQL> SQL> COLUMN MyNumber FORMAT $990.99; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 $12.12 1 Alison 22-JUN-97 -$12.12 1 Alison 13-MAY-98 $22.10 1 Alison 24-JUN-99 -$2.12 1 Alison 15-APR-00 $2.10 SQL> SQL> column mynumber clear; SQL> SQL> CLEAR COLUMNS; columns cleared SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>

      </source>
   
  


COLUMN Number FORMAT: 999

   <source lang="sql">

SQL> -- create demo table SQL> create table TestTable(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    MyName             VARCHAR2(10 BYTE),
 4    MyDate             DATE,
 5    MyNumber           Number(8,2)
 6  )
 7  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.10 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.10 SQL> SQL> SQL> SQL> -- Format MyNumber field to make it shorter SQL> SQL> COLUMN MyNumber FORMAT 999; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12 1 Alison 22-JUN-97 -12 1 Alison 13-MAY-98 22 1 Alison 24-JUN-99 -2 1 Alison 15-APR-00 2 SQL> SQL> column mynumber clear; SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>

      </source>
   
  


COLUMN Number FORMAT: 999,999

   <source lang="sql">

SQL> -- create demo table SQL> create table TestTable(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    MyName             VARCHAR2(10 BYTE),
 4    MyDate             DATE,
 5    MyNumber           Number(8,2)
 6  )
 7  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.1 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.1 SQL> SQL> SQL> -- If there are decimals or if commas are desired: SQL> SQL> COLUMN MyNumber FORMAT 999,999; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12 1 Alison 22-JUN-97 -12 1 Alison 13-MAY-98 22 1 Alison 24-JUN-99 -2 1 Alison 15-APR-00 2 SQL> SQL> COLUMN MyNumber FORMAT 99999.99; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.10 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.10 SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


If the column format is too small

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table TestTable(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    MyName             VARCHAR2(10 BYTE),
 4    MyDate             DATE,
 5    MyNumber           Number(8,2)
 6  )
 7  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.1 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.1 SQL> SQL> -- If the column format is too small: SQL> SQL> COLUMN MyNumber FORMAT 99; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12 1 Alison 22-JUN-97 -12 1 Alison 13-MAY-98 22 1 Alison 24-JUN-99 -2 1 Alison 15-APR-00 2 SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


Number format cannot be too short

   <source lang="sql">

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION


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

01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer 02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester 03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester 04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager 05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester 06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester 07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager 08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> --Column format:Dollar symbol, with 2 digits to the right of the decimal point and 2 digits to the left SQL> SQL> SQL> COLUMN salary FORMAT $9.99 SQL> SELECT salary FROM employee; SALARY


8 rows selected. SQL> column salary clear; SQL> SELECT salary FROM employee;

   SALARY

  1234.56
  6661.78
  6544.78
  2344.78
  2334.78
  4322.78
  7897.78
  1232.78

8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>

      </source>
   
  


Set number format to "$9,999.99"

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


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

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


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

08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- set number format to "$9,999.99" SQL> column Salary format $9,999.99; SQL> select Salary from Employee;

   SALARY

$1,234.56
$6,661.78
$6,544.78
$2,344.78
$2,334.78
$4,322.78
$7,897.78
   SALARY

$1,232.78

8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>

      </source>
   
  


To avoid having "naked" decimal points

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table TestTable(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    MyName             VARCHAR2(10 BYTE),
 4    MyDate             DATE,
 5    MyNumber           Number(8,2)
 6  )
 7  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.1 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.1 SQL> SQL> SQL> -- To avoid having "naked" decimal points SQL> SQL> COLUMN MyNumber FORMAT 990.99; SQL> SQL> SELECT * FROM TestTable; ID MYNAME MYDATE MYNUMBER


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

1 Alison 11-JUL-96 12.12 1 Alison 22-JUN-97 -12.12 1 Alison 13-MAY-98 22.10 1 Alison 24-JUN-99 -2.12 1 Alison 15-APR-00 2.10 SQL> SQL> column mynumber clear; SQL> SQL> SQL> drop table TestTable;

      </source>