Oracle - Função Round

ROUND (number)

Syntax

round_number::=

Description of the illustration round_number.gif

Purpose

ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.

n can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The argument integer must be an integer. If you omit integer, then the function returns the same datatype as the numeric datatype of the argument. If you include integer, then the function returnsNUMBER.

For NUMBER values, the value n is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative). For BINARY_FLOAT andBINARY_DOUBLE values, the function rounds to the nearest even value. Please refer to the examples that follow.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Examples

The following example rounds a number to one decimal point:

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20 

The following examples illustrate the difference between rounding NUMBER and floating-point number values. NUMBER values are rounded up (for positive values), whereas floating-point numbers are rounded toward the nearest even value:

SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;

ROUND(1.5) ROUND(2.5)
---------- ----------
         2          3

SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

ROUND(1.5F) ROUND(2.5F)
----------- -----------
   2.0E+000    2.0E+000

Formatar valores no Oracle

SELECT TO_CHAR (<STRING>,1234) FROM DUAL

String	Descrição	Resultado
9	determina a largura de exibição	9999 = 1234
0	exibe zeros a esquerda	09999 = 01234
$	Exibe dolar ( UAUU! ) * qquer posicao fica na frente	$9999 = $1234
L	Exibe a moeda	L9999 = R$1234
D	Caractere decimal	9999D99 = 1234,00
.	Mostra uma virgula ou ponto	9999.99  = 1234.00
G	Separado de grupos	999G9 = 123.4
,	Ponto / virgula na posicao	999,9 = 123,4
MI	Sinal negativo à direita*	9999MI = 1234-*
PR	poe valores negativos entre <>*	9999PR = <1234>*
EEEE	informa no padrao cientifico	9999EEEE = 1E+03
U	Retorna um simbolo monetário fora da casinha	U9999 = Cr$1234
V	Multiplica por 10 x o numero de noves.	1234V9 = 12340
S	Informa o sinal	S9999 = +1234
B	Troca valores zero por vazio**	B9999 = ‘ ‘ **

* – use valores negativos ( ex: -1234 )
** – user um valor só com zeros ( ex: 0000 )

PL/SQL - Check Constraint

What is a check constraint?

A check constraint allows you to specify a condition on each row in a table.

Note:

• A check constraint can NOT be defined on a VIEW.
• The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
• A check constraint can NOT include a SUBQUERY.

A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For Example:

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_id
	CHECK (supplier_id BETWEEN 100 and 9999)
);

In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_name
	CHECK (supplier_name = upper(supplier_name))
);

In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.

Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For Example:

ALTER TABLE suppliers
add CONSTRAINT check_supplier_name
   CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

In this example, we've created a check constraint on the existing suppliers table called check_supplier_name. It ensures that the supplier_name field only contains the following values:  IBM, Microsoft, or NVIDIA.

Drop a Check Constraint

The syntax for dropping a check constraint is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For Example:

ALTER TABLE suppliers
drop CONSTRAINT check_supplier_id;

In this example, we're dropping a check constraint on the suppliers table called check_supplier_id.

Enable a Check Constraint

The syntax for enabling a check constraint is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For Example:

ALTER TABLE suppliers
enable CONSTRAINT check_supplier_id;

In this example, we're enabling a check constraint on the suppliers table called check_supplier_id.

Disable a Check Constraint

The syntax for disabling a check constraint is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For Example:

ALTER TABLE suppliers
disable CONSTRAINT check_supplier_id;

In this example, we're disabling a check constraint on the suppliers table called check_supplier_id.

Fonte: http://www.techonthenet.com/oracle/check.php