Tipos de datos en Oracle

Los tipos de datos soportados por Oracle se agrupan en los siguientes conjuntos.

Los valores alfanuméricos van encerrados entre comilla simple: 'Alfanumérico' Los
valores numéricos son número simples: 123 Las fechas van encerradas entre comillas
simples: '1/12/2000' Los valores binarios no pueden ser representados (son fotos,
videos…)

Built-In
Codea Datatype Description

1 VARCHAR2(size) Variable-length character string having maximum length

size bytes. Maximum size is 4000, and minimum is 1. You
must specify size for VARCHAR2.

1 NVARCHAR2(size) Variable-length character string having maximum length

size characters or bytes, depending on the choice of
national character set. Maximum size is determined by the
number of bytes required to store each character, with an
upper limit of 4000 bytes. You must specify size for
NVARCHAR2.

2 NUMBER(p,s) Number having precision p and scale s. The precision p

can range from 1 to 38. The scale s can range from -84 to
127.

8 LONG Character data of variable length up to 2 gigabytes, or 231 -

1 bytes.
 Built-In
a
Code Datatype Description

12 DATE Valid date range from January 1, 4712 BC to December

31, 9999 AD.

23 RAW(size) Raw binary data of length size bytes. Maximum size is

2000 bytes. You must specify size for a RAW value.

24 LONG RAW Raw binary data of variable length up to 2 gigabytes.

69 ROWID Hexadecimal string representing the unique address of a

row in its table. This datatype is primarily for values
returned by the ROWID pseudocolumn.

208 UROWID [(size)] Hexadecimal string representing the logical address of a

row of an index-organized table. The optional size is the
size of a column of type UROWID. The maximum size and
default is 4000 bytes.

96 CHAR(size) Fixed-length character data of length size bytes. Maximum

size is 2000 bytes. Default and minimum size is 1 byte.

96 NCHAR(size) Fixed-length character data of length size characters or

bytes, depending on the choice of national character set.
Maximum size is determined by the number of bytes
required to store each character, with an upper limit of
2000 bytes. Default and minimum size is 1 character or 1
byte, depending on the character set.

112 CLOB A character large object containing single-byte characters.

Both fixed-width and variable-width character sets are
supported, both using the CHAR database character set.
Maximum size is 4 gigabytes.

112 NCLOB A character large object containing multibyte characters.

Both fixed-width and variable-width character sets are
supported, both using the NCHAR database character set.
Maximum size is 4 gigabytes. Stores national character set
data.

113 BLOB A binary large object. Maximum size is 4 gigabytes.

 Built-In
a
Code Datatype Description

114 BFILE Contains a locator to a large binary file stored outside the
database. Enables byte stream I/O access to external LOBs
residing on the database server. Maximum size is 4
gigabytes.

Tipo de dato CHAR(b)

Almacena cadenas de caracteres de longitud fija, desde 1 a 2.000 bytes de ocupación. El

número de caracteres que se pueden almacenar se rige según la siguiente fórmula.
nº caracteres = bytes / character set Para ASCII, el conjunto de caracteres
ocupa un byte, por lo que coincide el número de caracteres máximos con la ocupación del tipo
de dato. Si se introduce un valor de 10 caracteres en un campo de CHAR(100), se rellenará
con espacios las 90 posiciones restantes. Así la siguiente expresión es cierta:
'Hola pepe' = 'Hola pepe '
Si se intenta introducir un valor demasiado grande para el campo, se intentará eliminar los
espacios finales, y si cabe sin espacios, se introduce. Si aún así no cabe, se retorna un error.

Tipo de dato VARCHAR2(b)

Almacena cadenas de caracteres de longitud variable. Si se define una columna de longitud

100 bytes, y se introduce en ella un valor de 10 bytes, la columna ocupará 10 y no 100 como
hacía con el tipo de dato CHAR.

Tipo de dato NCHAR(b)

Almacena un valor alfanumérico de longitud fija con posibilidad de cambio de juego de

caracteres. Puede almacenar tanto caracteres ASCII, EBCDIC, UNICODE…

Tipo de dato NVARCHAR2(b)

Almacena un valor alfanumérico de longitud variable con posibilidad de cambio de juego de

caracteres. Puede almacenar tanto caracteres ASCII, EBCDIC, UNICODE…

Tipo de dato NUMBER(p,s)

Almacena valores numéricos en punto flotante que pueden ir desde 1.0 x 10 -130
hasta 9.9…(38
nueves)… 9 x 10 . El almacenamiento interno de los valores numéricos en notación científica:
125

Mantisa x 10exponente
La mantisa puede contener cualquier número, entero o decimal, positivo o negativo. El
exponente podrá contener cualquier número entero, positivo o negativo. El parámetro “p” indica
la precisión (número de dígitos contando los decimales) que contendrá el número como
máximo. Oracle garantiza los datos con precisiones de 1 a 38. El parámetro “s” indica la escala,
esto es, el máximo de dígitos decimales. Hay que tener en cuenta que una columna definida
NUMBER(10,5), podrá contener como máximo cualquier número siempre y cuando el número
de dígitos enteros más el número de dígitos decimales no supere 10 (y no 15). La escala puede
ir de -84 a 127. Para definir número enteros, se puede omitir el parámetro s o bien poner un 0
en su lugar. Se puede especificar una escala negativa, esto lo que hace es redondear el
número indicado a las posiciones indicadas en la escala. Por ejemplo un número definido como
NUMBER(5,-2), redondeará siempre a centenas. Así si intentamos introducir el valor 1355, en
realidad se almacenará 1400.

Tipo de dato FLOAT(b)

Almacena un número en punto decimal sin restricción de dígitos decimales. El parámetro b

indica la precisión binaria máxima que puede moverse en el rango 1 a 126. Si se emite el
defecto será 126. Una columna FLOAT(126) es equivalente a una columna NUMBER(38),
aunque la diferencia está en que la columna NUMBER no podrá contener decimales y la
columna FLOAT si y en con cualquier escala.

Tipo de dato DATE

Almacena un valor de fecha y hora.

Para un tipo de dato DATE, Oracle almacena internamente los siguiente datos: ƒ Siglo ƒ Año ƒ
Mes ƒ Día ƒ Hora ƒ Minuto ƒ Segundo
El formato por defecto de las
fechas es: 'DD-MON-YYY'
Esto es: Dos dígitos para el día Las tres primeras del mes (depende del idioma instalado).
Cuatro dígitos para el año.
Por ejemplo: '1-JAN-2001' ó '2-DEC-1943'
Este formato puede ser alterado en cualquier momento.
Internamente un fecha se almacena como el número de días desde cierto punto de inicio (por
ejemplo el año 0). Esto permite que las fechas puedan ser tratadas en operaciones aritméticas
normales:
'1-JAN-2001' + 10 = '11-JAN-2001' '1-JAN-2000' - 1 = '31-DEC-1999' '10-MAY-2000' - '1-
MAY-2000' = 9
Tipos de datos binarios

Permiten almacenar información en formato "crudo", valores binarios tal y como se almacenan
en el disco duro o como residen en memoria. Estas columnas se pueden utilizar tanto para
almacenar grandes cantidades de datos (hasta 4Gb.), como para almacenar directamente
cualquier tipo de fichero (ejecutables, sonidos, videos, fotos, documentos Word, librerías…) o
para transportar datos de una base de datos a otra, ya que el formato binario es el único
formato común entre cualquier sistema informático.

Tipo de dato ROWID

Representa una dirección de la base de datos, ocupada por una única fila. El ROWID de una
fila es un identificador único para una fila dentro de una base de datos. No hay dos filas con el
mismo ROWID. Este tipo de dato sirve para guardar punteros a filas concretas. El ROWID se
compone de:
-Número de datafile donde se almacena la fila (se pueden ver en DBA_DATA_FILES) -
Dirección del bloque donde está la fila -Posición dentro del bloque
Siempre que queramos obtener una fila de la firma más rápida posible, debemos hacerlo a
través de su ROWID. Un uso típico suele ser obtener un listado de ROWIDs con un SELECT, y
después acceder a cada una de las filas directamente con la condición del ROWID.
Categorías de ordenes SQL.

Lenguaje de Manipulación de Datos (DML): SELECT, INSERT, UPDATE,

DELETE

Permiten cambiar o consultar los datos contenidos en una tabla de la base de datos.

Lenguaje de definición de Datos (DDL): DROP, CREATE, ALTER, GRANT,

REVOKE.

Permiten crear, borrar o modificar la estructura de un objeto. Las ordenes que cambian
los permisos relativos a los objetos también pertenecen al DDL.

Control de transacciones: COMMIT, ROLLBACK.

Garantizan la consistencia de los datos, organizando las ordenes SQL en transacciones

lógicas, que se completan o fallan como una unidad.

Control de sesión: ALTER SESSION.

Cambian las opciones de una conexión determinada de la Base de Datos.

Control de sistema: ALTER SYSTEM.

Cambian las opciones que afectan a la base de datos completa.

SQL Pseudocolumns and Functions

SQL recognizes the pseudocolumns in Table 4-4, which return specific data items:

Table 4-4 Pseudocolumns and Internal Datatypes

Pseudocolumn Internal Datatype

CURRVAL NUMBER

LEVEL NUMBER

NEXTVAL NUMBER

ROWID ROWID

ROWNUM NUMBER
Pseudocolumns are not actual columns in a table. However, pseudocolumns are treated
like columns, so their values must be SELECTed from a table. Sometimes it is
convenient to select pseudocolumn values from a dummy table.
In addition, SQL recognizes the functions without parameters in Table 4-5, which also
return specific data items:

Table 4-5 Functions and Internal Datatypes

Function Internal Datatype

SYSDATE DATE

UID NUMBER

USER VARCHAR2

You can refer to SQL pseudocolumns and functions in SELECT, INSERT, UPDATE,
and DELETE statements. In the following example, you use SYSDATE to compute the
number of months since an employee was hired:
EXEC SQL SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE)
INTO :MONTHS-OF-SERVICE
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END EXEC.

Brief descriptions of the SQL pseudocolumns and functions follow. For details, see the
Oracle8i SQL Reference.
CURRVAL returns the current number in a specified sequence. Before you can
reference CURRVAL, you must use NEXTVAL to generate a sequence number.
LEVEL returns the level number of a node in a tree structure. The root is level 1,
children of the root are level 2, grandchildren are level 3, and so on.
LEVEL is used in the SELECT CONNECT BY statement to incorporate some or all the
rows of a table into a tree structure. In an ORDER BY or GROUP BY clause, LEVEL
segregates the data at each level in the tree.
You specify the direction in which the query walks the tree (down from the root or up
from the branches) with the PRIOR operator. In the START WITH clause, you specify
a condition that identifies the root of the tree.
NEXTVAL returns the next number in a specified sequence. After creating a sequence,
you can use it to generate unique sequence numbers for transaction processing. In the
following example, you use the sequence named partno to assign part numbers:
EXEC SQL INSERT INTO PARTS
VALUES ([Link], :DESCRIPTION, :QUANTITY, :PRICE
END EXEC.

If a transaction generates a sequence number, the sequence is incremented when you

commit or rollback the transaction. A reference to NEXTVAL stores the current
sequence number in CURRVAL.
ROWNUM returns a number indicating the sequence in which a row was selected from
a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM
of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs
are assigned to the selected rows before the sort is done.
You can use ROWNUM to limit the number of rows returned by a SELECT statement.
Also, you can use ROWNUM in an UPDATE statement to assign unique values to each
row in a table. Using ROWNUM in the WHERE clause does not stop the processing of
a SELECT statement; it just limits the number of rows retrieved. The only meaningful
use of ROWNUM in a WHERE clause is
... WHERE ROWNUM < constant END-EXEC.

because the value of ROWNUM increases only when a row is retrieved. The following
search condition can never be met because the first four rows are not retrieved:
... WHERE ROWNUM = 5 END-EXEC.

SYSDATE returns the current date and time.

UID returns the unique ID number assigned to an Oracle user.
USER returns the username of the current Oracle user.
 Lenguaje estructurado de consultas SQL (1)

SQL es un conjunto de sentencias u órdenes que todos los programas y usuarios deben utilizar
para acceder a bases de datos Oracle. No hay otra manera de comunicarse con Oracle si no es
a través de SQL. Dado que SQL es un estándar, todas las bases de datos comerciales de la
actualidad utilizan SQL como puente de comunicación entre la base de datos y el usuario.

Historia

SQL nació como a partir de una publicación de 1970 escrita por E.F. Cood, y titulada “A
relational model of data for large shared data banks” (El modelo de datos relacionales para
grandes bancos de datos compartidos). IBM utilizó el modelo planteado por Codd para
desarrollar un lenguaje capaz de soportar el recién nacido modelo relacional y así apareció
SEQUEL (Structured English QUEry Language). SEQUEL más tarde se convirtió en SQL
(Structured Query Language) que continuó pronunciándose en inglés como su predecesor:
SEQUEL. En 1979, una desconocida empresa llamada Relational Software, sacó por sorpresa
al mercado la primera implementación comercial de SQL. Relational Software más tarde pasó a
llamarse Oracle. Después de 20 años, SQL todavía es (y será) siendo el estándar en lenguajes
de acceso a base de datos relacionales.
En 1992, ANSI e ISO (organizaciones que se encargan de establecer estándares de todo tipo),
completaron la estandarización de SQL y se definió un conjunto de sentencias básicas que
debía tener toda implementación para ser llamada estándar. Este SQL se le denominó ANSI-
SQL o SQL92.
Hoy en día todas las bases de datos comerciales cumplen el estándar ANSI, aunque cada
fabricante añade sus mejoras al lenguaje SQL.

SQL como lenguaje estructurado

En realidad SQL no es un lenguaje en si, como podría ser un lenguaje de programación de 3ª

generación (C, Pascal…), sino que en un sublenguaje orientado a acceso y manipulación de
base de datos relacionales. Con SQL como única herramienta sólo podemos acceder a las
bases de datos, pero no tenemos las estructuras típicas de un lenguaje de programación. Una
buena analogía podría ser un sistema operativo. El interfaz de comandos de un SO nos da todo
lo que necesitamos para acceder al sistema de ficheros, pero sólo podemos hacer eso, acceder
a ficheros.
SQL actúa de la misma manera, nos da todo lo que necesitamos para acceder a bases de
datos, pero no podemos hacer más.
Se dice que SQL es estructurado porque trabaja con conjuntos de resultados (result set)
abstractos como unidades completas. Un conjunto de resultados es el esquema básico de una
tabla: N filas x N columnas. Este esquema se trata como un todo y es la idea principal de SQL.
A la hora de recuperar un conjunto de resultados, éste se trata de la misma forma tenga el
número de filas que tenga (0-N) y tenga el número de columnas que tenga (1-N). Además SQL
es consistente, esto significa que los "estilos" de las distintas sentencias son uniformes, por lo
que el aprendizaje es rápido.

Operadores SQL

Ya hemos visto anteriormente qué tipos de datos se pueden utilizar en Oracle. Y siempre que
haya datos, habrá operaciones entre ellos, así que ahora se describirán qué operaciones y con
qué operadores se realizan:
Los operadores se pueden dividir en dos conjuntos: · Aritméticos: utilizan valores numéricos ·
Lógicos (o booleanos o de comparación): utilizan valores booleanos o lógicos. · Concatenación:
para unir cadenas de caracteres.
Operadores arítméticos Retornan un valor numérico: Operadores lógicos Retornan un valor
lógico (verdadero o falso)
(*) El operador LIKE sirve para hacer igualdades con comodines, al estilo * y ? de MS-DOS.

Símbo lo Significado Ejemplo

+ Operación suma 1+2

- Operación resta 1-2
* Operación multiplicación 1*2
/ Operador división 1/2
Símbolo Significado Ejemplo
= Igualdad 1=2

!= <> ^= Desigualdad 1 != 2 1 <> 2 1 ^= 2

> Mayor que 1>2

< Menor que 1<2
>= Mayor o igual que 1 >= 2
<= Menor o igual que 1 <= 2
IN (RS) Igual a algún elemento del result set. 1 IN (1,2)

a algún elemento del result set (derecha). Debe ser

10 >= ANY (1,2,3,10)
ANY SOME estar precedido por =, !=, <, <=, >, >= Hace un OR
[TRUE]
lógico entre todos los elementos.
 a todos los elementos del result set (derecha), Debe
10 <= ALL (1,2,3,10)
ALL ser estar precedido por =, !=, <, <=, >, >= Hace un
[TRUE]
AND lógico entre todos los elementos.

BEETWEEN x Operando de la izquierda entre x e y. Equivalente a 10 BETWEEN 1 AND

AND y op >= x AND op <= y 100

EXISTS( SELECT 1
EXISTS Si la retorna al menos una fila
FROM DUAL)

LIKE(*) Es como 'pepe' LIKE

'pe%'
IS NULL Si es nulo 1 IS NULL
IS NOT NULL Si es No nulo 1 IS NOT NULL

NOT EXISTS… NOT

NOT cond. Niega la condición posterios BETWEEN NOT IN
NOT =

cond AND cond Hace un AND lógico entre dos condiciones 1=1 AND 2 IS NULL

Cond OR cond Hace un OR lógico entre dos condiciones 1=1 OR 2 IS NULL

Existen los siguientes comodines: %: Conjunto de N caracteres (de 0 a ∞) _: Un solo carácter

Ejemplo: Las siguientes condiciones retornarán el valor “verdadero” (TRUE)
'significado LIKE 's_gn%fi%d_' 'pepe'
LIKE 'pep%' (los que empiecen por 'pep')
'pepote' LIKE 'pep%'
'pepote' LIKE 'pe%te' (los que empiecen por 'pe' y terminen por 'te')
'pedrote' LIKE 'pe%te'

Operador de concatenación Retornan una cadena de caracteres

Oracle puede hacer una conversión automática cuando se utilice este operador con
valores numéricos: 10 || 20 = '1020'
Este proceso de denomina CASTING y se puede aplicar en todos aquellos casos en que
se utiliza valores numéricos en puesto de valores alfanuméricos o incluso viceversa.
La ausencia de valor: NULL

Todo valor (sea del tipo que sea) puede contener el valor NULL que no es más que la ausencia
de valor. Así que cualquier columna (NUMBER, VARCHAR2, DATE…) puede contener el valor
NULL, con lo que se dice que la columna está a NULL. Una operación retorna NULL si
cualquiera de los operandos es NULL. Para comprobar si una valor es NULL se utiliza el
operador IS NULL o IS NOT NULL.

Lenguaje de manipulación de datos: DML

El DML (Data Manipulation Language) es el conjunto de sentencias que está orientadas a la

consulta, y manejo de datos de los objetos creados. El DML es un subconjunto muy pequeño
dentro de SQL, pero es el más importante, ya que su conocimiento y manejo con soltura es
imprescindible. Básicamente consta de cuatro sentencias: SELECT, INSERT, DELETE,
UPDATE.

Instrucción SELECT

La sentencia SELECT es la encargada de la recuperación (selección) de datos, con cualquier

tipo de condición, agrupación u ordenación. Una sentencia SELECT retorna un único conjunto
de resultados, por lo que podrá ser aplicada en cualquier lugar donde se espere un conjunto de
resultados.
La sintaxis básica es:
SELECT columnas FROM tablas WHERE condición GROUP BY columnas de
agrupación HAVING condición agrupada ORDER BY columnas de
ordenación;
Todas las cláusulas son opcionales excepto SELECT y FROM.
A continuación vamos a hacer una descripción breve de cada cláusula:
SELECT Se deben indicar las columnas que se desean mostrar en el resultado. Las distintas
columnas deben aparecer separadas por coma (","). Opcionalmente puede ser cualificadas
con el nombre de su tabla utilizando la sintaxis:
[Link]
Si se quieren introducir todas las columnas se podrá incluir el carácter *, o bien TABLA.* Existe
la posibilidad de sustituir los nombres de columnas por constantes (1, 'pepe' o '1-may-2000'),
expresiones, pseudocolumnas o funciones SQL.
A toda columna, constante, pseudocolumna o función SQL, se le puede cualificar con un
nombre adicional: COLUMNA NOMBRE, CONSTANTE NOMBRE, PSEUDOCOLUMNA
NOMBRE, FUNCION SQL NOMBRE
Si se incluye la cláusula DISTINCT después de SELECT, se suprimirán aquellas filas del
resultado que tenga igual valor que otras.
Así
SELECT C_CLIENTE FROM FACTURA;

Puede retornar 1, 3, 5, 5, 1, 7, 3, 2 y 9

Sin embargo, para el mismo caso:

SELECT DISTINCT C_CLIENTE FROM FACTURA;

Retornará (suprimiendo las repeticiones): 1, 3, 5, 7, 2 y 9

Ejemplos:

SELECT REFERENCIA REF, DESCRIPCION FROM FACTURA

SELECT [Link], DESCRIPCION FROM FACTURA

SELECT * FROM FACTURA

SELECT FACTURA.* FROM FACTURA

SELECT 1 UN_NUMERO_CTE, REFERENCIA FROM FACTURA

SELECT 1+1-3*5/5.4 RESULTADO FROM DUAL

SELECT ROWNUM, DESCRIPCION FROM FACTURA

SELECT TRUNC( '1-JAN-2001'+1, 'MON' ) FROM DUAL

SELECT DISTINCT * FROM FACTURA

SELECT DISTINCT DESCRIPCION, IMPORTE FROM FACTURA

SELECT REFERENCIA||DESCRIPCION FROM FACTURA

FROM se indican el(los) conjunto(s) de resultado(s) que interviene(n) en la consulta.

Normalmente se utilizan tablas, pero se admite cualquier tipo de conjunto (tabla, select,
vista…). Si apareciese más de una tabla, deben ir separadas por coma. Las tablas deben
existir y si no existiera alguna aparecería el siguiente error:
ORA-00942: table or view does not exist

Al igual que a las columnas, también se puede cualificar a las tablas

TABLA NOMBRE
 Oracle tiene definida una tabla especial, llamada DUAL, que se utiliza para consultar
valores que no dependen de ninguna tabla.
SELECT (1+1.1*3/5)-1-2 FROM DUAL;
Ejemplos:
FROM FACTURA FAC
FROM FACTURA FAC, CLIENTE CLI
FROM DUAL
FROM ( SELECT C_CLIENTE FROM FACTURA ) CLIENTE_FAC

WHERE Indica qué condiciones debe cumplirse para que una fila entre dentro del conjunto de
resultados retornado. Para construir las condiciones se podrán utilizar todos los
operadores lógicos vistos anteriormente. Es posible construir condiciones complejas
uniendo dos o más condiciones simples a través de los operadores lógicos AND y OR.
Ejemplos:
WHERE [Link] = 'AA3455'
WHERE FACTURA.C_CLIENTE IS NULL
WHERE C_CLIENTE BETWEEN '12' AND '20'
WHERE C_CLIENTE IS NULL AND REFERENCIA IN ('AA23344', 'BB23345')
WHERE C_CLIENTE != 55 OR REFERENCIA LIKE 'AA%5_'

GROUP BY La expresión GROUP BY se utiliza para agrupar valores que es necesario

procesar como un grupo. Por ejemplo, puede darse el caso de necesitar procesar todas
las facturas de cada cliente para ver su total, o para contarlas, o para incrementarles un
10%… Para estos casos se haría un SELECT agrupando por C_CLIENTE. Un SELECT
con GRUOP BY es equivalente a un SELECT DISTINCT, siempre y cuando en el SELECT
no aparezcan consultas sumarias (ver apartado Funciones SQL).
Trataremos con más profundidad este tipo de consultas en el apartado "Consultas
agrupadas".

HAVING Se utiliza para aplicar condiciones sobre agrupaciones. Sólo puede aparecer si se ha
incluido la cláusula GROUP BY. Trataremos con más profundidad este tipo de consultas
en el apartado "Consultas agrupadas".
ORDER BY Se utiliza para ordenar las filas del conjunto de resultados final. Dentro de esta
cláusula podrá aparecer cualquier expresión que pueda aparecer en el SELECT, es decir,
pueden aparecer columnas, pseudocolumnas, constantes (no tiene sentido, aunque está
permitido), expresiones y funciones SQL. Como característica adicional, se pueden incluir
números en la ordenación, que serán sustituidos por la columna correspondiente del
SELECT en el orden que indique el número.
 La ordenación es el último paso en la ejecución de una consulta SQL, y para ello Oracle
suele necesitar crear objetos temporales que son creados en el tablespace Temporal. Por
eso es recomendable hacer las ordenaciones del lado de cliente (siempre que sea
posible), ya que el servidor puede cargarse bastante si tiene que hacer, por ejemplo, 300
ordenaciones de tablas de 2 millones de registros. Después de cada columna de
ordenación se puede incluir una de las palabras reservadas ASC o DESC, para hacer
ordenaciones ASCendentes o DESCendentes. Por defecto, si no se pone nada se hará
ASC.
Ejemplos:
ORDER BY REFERENCIA ASC
ORDER BY REFERENCIA DESC, C_CLIENTE DES, IMPORTE ASC
ORDER BY C_CLIENTE
ORDER BY 1, C_CLIENTE, 2
ORDER BY TRUNC( '1-JAN-2001'+1, 'MON' )
ORDER BY 1.1+3-5/44.3 -- no tiene sentido ordenar por una cte.

Consultas agrupadas

Una consulta agrupada se utiliza para considerar los registros cuyos ciertos campos tienen el
mismo valor, y procesarlos de la misma manera, para contarlos, sumarlos, hacer la media…
Las consultas típicas son para contar los registros de cierto tipos, sumar los importes de cierto
cliente, etc. Por ejemplo, vamos a sacar el total del importe de las factura, por cliente:
SELECT C_CLIENTE, SUM(IMPORTE) FROM FACTURA GROUP BY C_CLIENTE;
Esto nos sumará (la función SUM suma su parámetro) los registro agrupando por cliente.
Internamente Oracle tiene que hacer una ordenación interna de los registros, según las
columnas incluidas en el GROUP BY, así que todo lo dicho para el ORDER BY (sobre la
sorbecarga del servidor) se puede aplicar para el GROUP BY.
Cuando en la cláusula SELECT no se incluyen funciones SQL (para más información ver el
apartado Funciones SQL), una consulta GROUP BY es equivalente a una consulta SELECT
DISTINCT.
Un error muy común cuando se construyen consultas agrupadas, es el siguiente:
ORA-00979: not a GROUP BY expression
Esto es debido al modo que tiene Oracle de analizar las consultas agrupadas: Lo que hace
es comprobar que todos las columnas incluidos en la cláusula SELECT fuera de funciones
sumarias, estén dentro de la cláusula GROUP BY, aunque pueden estar en cualquier orden y
en el GROUP BY pueden aparecer columnas que no estén en el SELECT. Si encuentra
alguna columna en el SELECT (que no esté dentro de una función sumaria) que no aparezca
en el GROUP BY, entonces nos retorna el error anterior. Si pensamos la situación, es lógico
que nos retorne un error, porque no podemos agrupar por la columna C_CLIENTE, si luego
queremos mostrar otras columnas que estén sin agrupar. O agrupamos por todo, o
mostramos sin agrupar, pero ambas a la vez no es posible.

Ejemplos
Simple Query Examples
The following statement selects rows from the emp table with the department number of
30:
SELECT * FROM emp WHERE deptno = 30;

The following statement selects the name, job, salary and department number of all
employees except sales people from department number 30:
SELECT ename, job, sal, deptno FROM emp
WHERE NOT (job = 'SALESMAN' AND deptno = 30);

The following statement selects from subqueries in the FROM clause and gives
departments' total employees and salaries as a decimal value of all the departments:
SELECT [Link] "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal
"%Salary"
FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM [Link]
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM [Link]) b ;
GROUP BY Examples
To return the minimum and maximum salaries for each department in the employee
table, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp
GROUP BY deptno;

DEPTNO MIN(SAL) MAX(SAL)

---------- ---------- ----------
10 1300 5000
20 800 3000
30 950 2850

To return the minimum and maximum salaries for the clerks in each department, issue
the following statement:
SELECT deptno, MIN(sal), MAX (sal)
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno;

DEPTNO MIN(SAL) MAX(SAL)

---------- ---------- ----------
10 1300 1300
20 800 1100
30 950 950

HAVING Example
To return the minimum and maximum salaries for the clerks in each department whose
lowest salary is below $1,000, issue the next statement:
SELECT deptno, MIN(sal), MAX (sal)
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno
HAVING MIN(sal) < 1000;

DEPTNO MIN(SAL) MAX(SAL)

---------- ---------- ----------
20 800 1100
30 950 950

ORDER BY Examples
To select all salesmen's records from emp, and order the results by commission in
descending order, issue the following statement:
SELECT *
FROM emp
WHERE job = 'SALESMAN'
ORDER BY comm DESC;

To select the employees from emp ordered first by ascending department number and
then by descending salary, issue the following statement:
SELECT ename, deptno, sal
FROM emp
ORDER BY deptno ASC, sal DESC;

To select the same information as the previous SELECT and use the positional ORDER BY
notation, issue the following statement:
SELECT ename, deptno, sal
FROM emp
ORDER BY 2 ASC, 3 DESC;

FOR UPDATE Examples

The following statement locks rows in the emp table with clerks located in New York
and locks rows in the dept table with departments in New York that have clerks:
SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND [Link] = [Link]
AND loc = 'NEW YORK'
FOR UPDATE;

The following statement locks only those rows in the emp table with clerks located in
New York. No rows are locked in the dept table:
SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND [Link] = [Link]
AND loc = 'NEW YORK'
FOR UPDATE OF [Link];

Equijoin Examples
This equijoin returns the name and job of each employee and the number and name of
the department in which the employee works:
SELECT ename, job, [Link], dname
FROM emp, dept
WHERE [Link] = [Link];

ENAME JOB DEPTNO DNAME

---------- --------- ---------- --------------
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
FORD ANALYST 20 RESEARCH
SCOTT ANALYST 20 RESEARCH
JONES MANAGER 20 RESEARCH
ALLEN SALESMAN 30 SALES
BLAKE MANAGER 30 SALES
MARTIN SALESMAN 30 SALES
JAMES CLERK 30 SALES
TURNER SALESMAN 30 SALES
WARD SALESMAN 30 SALES

You must use a join to return this data because employee names and jobs are stored in a
different table than department names. Oracle combines rows of the two tables
according to this join condition:
[Link] = [Link]

The following equijoin returns the name, job, department number, and department name
of all clerks:
SELECT ename, job, [Link], dname
FROM emp, dept
WHERE [Link] = [Link]
 AND job = 'CLERK';

ENAME JOB DEPTNO DNAME

---------- --------- ---------- --------------
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES

This query is identical to the preceding example, except that it uses an additional
where_clause condition to return only rows with a job value of 'CLERK'.

Subquery Examples
To determine who works in Taylor's department, issue the following statement:
SELECT ename, deptno
FROM emp
WHERE deptno =
(SELECT deptno
FROM emp
WHERE ename = 'TAYLOR');

To give all employees in the emp table a 10% raise if they have not already been issued a
bonus (if they do not appear in the bonus table), issue the following statement:
UPDATE emp
SET sal = sal * 1.1
WHERE empno NOT IN (SELECT empno FROM bonus);

To create a duplicate of the dept table named newdept, issue the following statement:
CREATE TABLE newdept (deptno, dname, loc)
AS SELECT deptno, dname, loc FROM dept;

Self Join Example

The following query uses a self join to return the name of each employee along with the
name of the employee's manager:
SELECT [Link]||' works for '||[Link]
"Employees and their Managers"
FROM emp e1, emp e2 WHERE [Link] = [Link];

Employees and their Managers

-------------------------------
BLAKE works for KING
CLARK works for KING
JONES works for KING
FORD works for JONES
SMITH works for FORD
ALLEN works for BLAKE
WARD works for BLAKE
MARTIN works for BLAKE
SCOTT works for JONES
TURNER works for BLAKE
ADAMS works for SCOTT
JAMES works for BLAKE
MILLER works for CLARK
The join condition for this query uses the aliases e1 and e2 for the emp table:
[Link] = [Link]

Outer Join Examples

This query uses an outer join to extend the results of the Equijoin example above:
SELECT ename, job, [Link], dname
FROM emp, dept
WHERE [Link] (+) = [Link];

ENAME JOB DEPTNO DNAME

---------- --------- ---------- --------------
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
FORD ANALYST 20 RESEARCH
SCOTT ANALYST 20 RESEARCH
JONES MANAGER 20 RESEARCH
ALLEN SALESMAN 30 SALES
BLAKE MANAGER 30 SALES
MARTIN SALESMAN 30 SALES
JAMES CLERK 30 SALES
TURNER SALESMAN 30 SALES
WARD SALESMAN 30 SALES
40 OPERATIONS

In this outer join, Oracle returns a row containing the operations department even though
no employees work in this department. Oracle returns NULL in the ename and job
columns for this row. The join query in this example selects only departments that have
employees.
The following query uses an outer join to extend the results of the preceding example:
SELECT ename, job, [Link], dname
FROM emp, dept
WHERE [Link] (+) = [Link]
AND job (+) = 'CLERK';

ENAME JOB DEPTNO DNAME

---------- --------- ---------- --------------
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
40 OPERATIONS

In this outer join, Oracle returns a row containing the operations department even though
no clerks work in this department. The (+) operator on the job column ensures that rows
for which the job column is NULL are also returned. If this (+) were omitted, the row
containing the operations department would not be returned because its job value is not
'CLERK'.
This example shows four outer join queries on the customers, orders, lineitems, and parts
tables. These tables are shown here:
SELECT custno, custname
FROM customers;

CUSTNO CUSTNAME
---------- --------------------
 1 Angelic Co.
2 Believable Co.
3 Cables R Us

SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE"

FROM orders;

ORDERNO CUSTNO ORDERDATE

---------- ---------- -----------
9001 1 OCT-13-1998
9002 2 OCT-13-1998
9003 1 OCT-20-1998
9004 1 OCT-27-1998
9005 2 OCT-31-1998

SELECT orderno, lineno, partno, quantity FROM lineitems;

ORDERNO LINENO PARTNO QUANTITY

---------- ---------- ---------- ----------
9001 1 101 15
9001 2 102 10
9002 1 101 25
9002 2 103 50
9003 1 101 15
9004 1 102 10
9004 2 103 20

SELECT partno, partname FROM parts;

PARTNO PARTNAME
------ --------
101 X-Ray Screen
102 Yellow Bag
103 Zoot Suit

The customer Cables R Us has placed no orders, and order number 9005 has no line
items.
The following outer join returns all customers and the dates they placed orders. The (+)
operator ensures that customers who placed no orders are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE"
FROM customers, orders
WHERE [Link] = [Link] (+);

CUSTNAME ORDERDATE
-------------------- --------------
Angelic Co. OCT-13-1993
Angelic Co. OCT-20-1993
Angelic Co. OCT-27-1993
Believable Co. OCT-13-1993
Believable Co. OCT-31-1993
Cables R Us

The following outer join builds on the result of the previous one by adding the lineitems
table to the FROM clause, columns from this table to the select list, and a join condition
joining this table to the orders table to the where_clause. This query joins the results of the
previous query to the lineitems table and returns all customers, the dates they placed
orders, and the part number and quantity of each part they ordered. The first (+)
operator serves the same purpose as in the previous query. The second (+) operator
ensures that orders with no line items are also returned:
SELECT custname,
TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE",
partno,
quantity
FROM customers, orders, lineitems
WHERE [Link] = [Link] (+)
AND [Link] = [Link] (+);

CUSTNAME ORDERDATE PARTNO QUANTITY

-------------------- -------------- ---------- ----------
Angelic Co. OCT-13-1993 101 15
Angelic Co. OCT-13-1993 102 10
Angelic Co. OCT-20-1993 101 15
Angelic Co. OCT-27-1993 102 10
Angelic Co. OCT-27-1993 103 20
Believable Co. OCT-13-1993 101 25
Believable Co. OCT-13-1993 103 50
Believable Co. OCT-31-1993
Cables R Us

The following outer join builds on the result of the previous one by adding the parts table
to the FROM clause, the partname column from this table to the select list, and a join
condition joining this table to the lineitems table to the where_clause. This query joins the
results of the previous query to the parts table to return all customers, the dates they
placed orders, and the quantity and name of each part they ordered. The first two (+)
operators serve the same purposes as in the previous query. The third (+) operator
ensures that rows with NULL part numbers are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE",
quantity, partname
FROM customers, orders, lineitems, parts
WHERE [Link] = [Link] (+)
AND [Link] = [Link] (+)
AND [Link] = [Link] (+);

CUSTNAME ORDERDATE QUANTITY PARTNAME

-------------------- -------------- ---------- ------------
Angelic Co. OCT-13-1993 15 X-Ray Screen
Angelic Co. OCT-13-1993 10 Yellow Bag
Angelic Co. OCT-20-1993 15 X-Ray Screen
Angelic Co. OCT-27-1993 10 Yellow Bag
Angelic Co. OCT-27-1993 20 Zoot Suit
Believable Co. OCT-13-1993 25 X-Ray Screen
Believable Co. OCT-13-1993 50 Zoot Suit
Believable Co. OCT-31-1993
Cables R Us
Correlated Subquery Examples
The following examples show the general syntax of a correlated subquery:
SELECT select_list
FROM table1 t_alias1
WHERE expr operator
(SELECT column_list
FROM table2 t_alias2
WHERE t_alias1.column
operator t_alias2.column);
UPDATE table1 t_alias1
SET column =
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
DELETE FROM table1 t_alias1
WHERE column operator
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);

The following statement returns data about employees whose salaries exceed their
department average. The following statement assigns an alias to emp, the table
containing the salary information, and then uses the alias in a correlated subquery:
SELECT deptno, ename, sal
FROM emp x
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE [Link] = deptno)
ORDER BY deptno;

For each row of the emp table, the parent query uses the correlated subquery to compute
the average salary for members of the same department. The correlated subquery
performs the following steps for each row of the emp table:
1. The deptno of the row is determined.
2. The deptno is then used to evaluate the parent query.
3. If that row's salary is greater than the average salary for that row's department,
then the row is returned.
The subquery is evaluated once for each row of the emp table.
DUAL Table Example
The following statement returns the current date:
SELECT SYSDATE FROM DUAL;

You could select SYSDATE from the emp table, but Oracle would return 14 rows of the
same SYSDATE, one for every row of the emp table. Selecting from DUAL is more
convenient.
Sequence Examples
The following statement increments the zseq sequence and returns the new value:
SELECT [Link]
FROM dual;

The following statement selects the current value of zseq:

SELECT [Link]
FROM dual;
Update Examples

Simple Examples

The following statement gives null commissions to all employees with the job trainee:
UPDATE emp SET comm = NULL
WHERE job = 'TRAINEE';

The following statement promotes jones to manager of Department 20 with a $1,000

raise (assuming there is only one jones):
UPDATE emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20
WHERE ename = 'JONES';

The following statement increases the balance of bank account number 5001 in the
accounts table on a remote database accessible through the database link boston:
UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001;
Complex Example

This example shows the following syntactic constructs of the UPDATE statement:

Both forms of the set_clause together in a single statement

A correlated subquery

A where_clause to limit the updated rows

UPDATE emp a
SET deptno = (SELECT deptno FROM dept WHERE loc = 'BOSTON'),
(sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b
WHERE [Link] = [Link])
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS'
OR loc = 'DETROIT');

The above UPDATE statement performs the following operations:

 Updates only those employees who work in Dallas or Detroit
 Sets deptno for these employees to the deptno of Boston
 Sets each employee's salary to 1.1 times the average salary of their department
 Sets each employee's commission to 1.5 times the average commission of their
department

SET VALUE Example

The following statement updates a row of object table table1 by selecting a row from
another object table table2:
UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE [Link] = [Link])
WHERE [Link] = 10;

The subquery uses the value object reference function in its expression.
INSERT Examples

Inserting Values Examples

The following statement inserts a row into the dept table:

INSERT INTO dept
VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');

The following statement inserts a row with six columns into the emp table. One of these
columns is assigned NULL and another is assigned a number in scientific notation:
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

The following statement has the same effect as the preceding example, but uses a
subquery in the DML_query_expression_clause:
INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

Inserting Values with a Subquery Example

The following statement copies managers and presidents or employees whose

commission exceeds 25% of their salary into the bonus table:
INSERT INTO bonus
SELECT ename, job, sal, comm
FROM emp
WHERE comm > 0.25 * sal
OR job IN ('PRESIDENT', 'MANAGER');

Inserting Sequence Values Example

The following statement inserts a new row containing the next value of the employee
sequence into the emp table:
INSERT INTO emp
VALUES ([Link], 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20);
Delete Examples

The following statement deletes all rows from a table named temp_assign.
DELETE FROM temp_assign;

The following statement deletes from the emp table all sales staff who made less than
$100 commission last month:
DELETE FROM emp
WHERE JOB = 'SALESMAN'
AND COMM < 100;

The following statement has the same effect as the preceding example, but uses a
subquery:
DELETE FROM (select * from emp)
WHERE JOB = 'SALESMAN'
AND COMM < 100;