BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  Oracle Tips by Burleson

Number Functions

Number functions allow you to present a number in a manner that is useful to the reader.  You must always be careful with converting numbers because you are only converting the presentation.  The number is in the database is still the same. 

round (n,d)

The round  function rounds a number n to the specified decimal d.  The decimal d can be positive, negative or zero.  If the decimal is positive, then the number is rounded to that many decimal points.  The number five rounds up.  If d is zero, then the number is rounded to no decimal points.  If d is negative, then the number will have no decimal points and it will be rounded to the d digits to the left of the decimal point.

SELECT
  ROUND( 1234.345, 2),
  ROUND( 1234.345, 0),
  ROUND( 1234.345, -2)
FROM
  dual;

ROUND(1234.345,2) ROUND(1234.345,0) ROUND(1234.345,-2)
----------------- ----------------- ------------------
          1234.35              1234               1200 

1 row selected. 

The boss now wants to know the retail price of the books we publish, rounded to the nearest dollar.  This is found in the book table. 

SELECT
  RPAD(TRIM(INITCAP(book_title)),40,'.') Title,
  ROUND(book_retail_price,0) Bucks
FROM
  book;

TITLE                                         BUCKS
---------------------------------------- ----------
Windows Success.........................         35
Piano Greats............................         33
Dos For Dummies.........................         20
The Zen Of Auto Repair..................        100
Zero Loss Finance.......................         22
Operations Research Theory..............         45
Non Violins In The Workplace............         12
Unix For Experts........................         39
Pay No Taxes And Go To Jail.............         11
The Fall Of Microsoft...................         20
Writers Market..........................         23
Managing Stress.........................         40
Bears Are People Too....................         35
Reduce Spending The Republican Way......         28
The Willow Weeps No More................         30
Oracle9i Sql Tuning.....................         50
Was George Washington Feeble?...........         25
Cooking Light...........................         25
Never Eat Meat..........................         11
How To Housebreak Your Horse............         30

20 rows selected.

trunc (n,d)

The trunc  or truncate function simply drops the digits without rounding.  The decimal d can again be positive, negative or zero.  If the number truncated is five or higher, it is still dropped without rounding the next digit up.

SELECT
  TRUNC( 1234.345, 2),
  TRUNC( 1234.345, 0),
  TRUNC( 1234.345, -2)
FROM
  dual;  

TRUNC(1234.345,2) TRUNC(1234.345,0) TRUNC(1234.345,-2)
----------------- ----------------- ------------------
          1234.34              1234               1200

1 row selected.

Notice the difference between trunc and round.

TRUNC(1234.345,2) TRUNC(1234.345,0) TRUNC(1234.345,-2)
----------------- ----------------- ------------------
          1234.34              1234               1200

ROUND(1234.345,2) ROUND(1234.345,0) ROUND(1234.345,-2)
---------------- ----------------- ------------------          1234.35              1234               1200

Like the character functions, there are more numeric functions that are mostly used by programmers such as sin, cos, tan, ceil, floor, etc. 


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.