Objetivos
- Tener un concepto claro sobre las pseudocolumnas de Oracle.
- Conocer los diferentes tipos.
- Ver las ventajas que ofrecen.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
Descripción
Las pseudocolumnas se comportan como columnas reales dentro de una tabla, aunque en realidad no lo son. Pueden considerarse elementos de datos de propósito especial que se utilizan en sentencias SQL como si formaran parte de la tabla, pero cuyos valores no se almacenan físicamente en ella. En esencia, una pseudocolumna es un valor generado por Oracle que puede utilizarse en el mismo contexto que una columna convencional, sin estar guardado en disco.
Una pseudocolumna también puede compararse con una función sin argumentos. Sin embargo, mientras que las funciones sin argumentos suelen devolver el mismo valor para todas las filas del conjunto de resultados, las pseudocolumnas generalmente retornan un valor distinto para cada fila.
Es importante tener presente que, aunque es posible consultar los valores de una pseudocolumna, no se pueden realizar operaciones DML sobre ellas, tales como INSERT, UPDATE o DELETE.
Tipos de Pseudocolumnas
En Oracle existen un considerable número de pseudocolumnas las cuales podrían clasificarse de acuerdo al contexto, a continuación presentamos un lista parcial de ellas:
Pseudocolumnas de Secuencias
Una secuencia es un objeto de esquema que puede generar valores secuenciales únicos. Estos valores se utilizan a menudo para generar claves primarias y únicas. Puede consultar valores de secuencia en sentencias SQL (En cualquier versión Oracle) y PL/SQL (11g en adelante) con estas pseudocolumnas:
- NEXTVAL: Incrementa la secuencia y devuelve el siguiente valor.
- CURRVAL: Devuelve el valor actual de una secuencia.
Ejemplos:
CREATE SEQUENCE sec_prueba
START WITH 1
INCREMENT BY 1
NOCYCLE;Creamos la secuencia sec_prueba para a continuación usar las pseudocolumnas NEXTVAL y CURRVAL.
SELECT sec_prueba.NEXTVAL
FROM dual;
Realizamos una simple consulta en la cual usamos la pseudocolumna NEXTVAL.
SELECT sec_prueba.CURRVAL
FROM dual;
Realizamos una simple consulta en la cual usamos la pseudocolumna CURRVAL.
SET SERVEROUTPUT ON
DECLARE
v_seq NUMBER;
v_date DATE;
BEGIN
v_seq := sec_prueba.NEXTVAL;
DBMS_OUTPUT.PUT_LINE('El valor generado es: '||v_seq);
DBMS_OUTPUT.PUT_LINE('Valor actual: '||sec_prueba.CURRVAL);
END;
Este ejemplo muestra que es posible usar las pseudocolumnas NEXTVAL y CURRVAL en un bloque de PL/SQL (de 11g en adelante).
Pseudocolumnas de Versión de Consulta
Las pseudocolumnas de versión de consulta son válidas sólo en consultas de flashback de Oracle. A continuación una lista de ellas:
- VERSIONS_STARTTIME: Devuelve el TIMESTAMP de la primera versión de las filas devueltas por la consulta.
- VERSIONS_STARTSCN: Devuelve el SCN de la primera versión de las filas devueltas por la consulta.
- VERSIONS_ENDTIME: Devuelve el TIMESTAMP de la última versión de las filas devueltas por la consulta.
- VERSIONS_ENDSCN: Devuelve el SCN de la última versión de las filas devueltas por la consulta.
- VERSIONS_XID: para cada versión de cada fila, devuelve el ID (un número RAW) de la transacción que creó esa versión de fila.
- VERSIONS_OPERATION: Para cada versión de cada fila, devuelve un carácter que representa la operación que causó esa versión de fila. Los valores devueltos son I (para una operación de inserción), U (para una operación de actualización) o D (para una operación de borrado).
Ejemplos:
INSERT INTO hr.employees
(
employee_id,
last_name,
email,
hire_date,
job_id
)
VALUES
(
1111,
'Aponte',
'aponte@',
SYSDATE,
'IT_PROG'
);
COMMIT;Insertamos un nuevo registro en la tabla employees.
SELECT
VERSIONS_STARTTIME,
VERSIONS_STARTSCN,
VERSIONS_ENDTIME,
VERSIONS_ENDSCN,
VERSIONS_XID,
VERSIONS_OPERATION,
employee_id,
last_name,
email
FROM hr.employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 1111;
Consultamos el nuevo registro insertado en la tabla employees.
UPDATE hr.employees
SET last_name = 'Castillo'
WHERE employee_id = 1111;
COMMIT;
Pseudocolumna ROWID
Para cada fila de la base de datos, la pseudocolumna ROWID devuelve la dirección de la fila. Los valores ROWID de Oracle contienen la información necesaria para localizar una fila.
Normalmente, un valor ROWID identifica de forma única una fila en la Base de Datos. Sin embargo, las filas en tablas diferentes que se almacenan juntas en el mismo clúster pueden tener el mismo ROWID.
Los valores de la pseudocolumna ROWID tienen el tipo de datos ROWID o UROWID.
Entre los usos mas importantes de los ROWID podemos mencionar:
- Son la forma más rápida de acceder a una sola fila.
- Pueden mostrarle cómo se almacenan las filas de una tabla.
- Son identificadores únicos para filas en una tabla.
- No debe utilizar ROWID como clave principal de una tabla. Por ejemplo, si elimina y vuelve a insertar una fila con las utilidades de importación y exportación (Import and Export utilities), puede que su ROWID cambie. Si elimina una fila, Oracle puede reasignar su ROWID a una nueva fila insertada posteriormente.
Nota: si usas Toad for Oracle y ejecutas una consulta con ROWID en la lista de columnas del SELECT, puedes editar los valores en los campos de la tabla de forma manual en el Data Grid (ejemplo mas adelante).
Ejemplo:
DECLARE
v_max_emp NUMBER;
CURSOR c_max_emp IS
SELECT MAX(employee_id)
FROM hr.employees;
BEGIN
OPEN c_max_emp;
FETCH c_max_emp INTO v_max_emp;
CLOSE c_max_emp;
FOR i IN 1..5 LOOP
v_max_emp := v_max_emp+i;
INSERT INTO hr.employees
(
employee_id,
last_name,
email,
hire_date,
job_id
)
VALUES
(
v_max_emp,
'Emp '||i,
'correo'||i||'@',
SYSDATE,
'IT_PROG'
);
END LOOP;
COMMIT;
END;Con este Bloque de PL/SQL procedemos a insertar 5 registros mas en la tabla employees.
SELECT
''||ROWID||'' AS ROW_ID,
employee_id,
last_name,
email
FROM hr.employees
WHERE employee_id > 1111;
Consultamos los empleados insertados en el ejemplo anterior para así notar cada ROWID.
SELECT ROWID,e.*
FROM hr.employees e
WHERE e.employee_id = 1111;
En esta consulta editamos el campo salary del registro con el employee_id = 1111. Esto lo permite en Toad al incluir el ROWID en la lista de columnas del SELECT.
Pseudocolumna ROWNUM
Para cada fila devuelta por una consulta, la pseudocolumna ROWNUM asigna un número que refleja el orden en que Oracle recupera las filas desde una tabla o desde un conjunto de resultados generado por una unión. La primera fila seleccionada recibe el valor ROWNUM = 1, la segunda ROWNUM = 2, y así sucesivamente, según el orden en que las filas son procesadas durante la ejecución de la consulta.
Ejemplos:
SELECT
ROWNUM,
employee_id,
last_name,
email
FROM hr.employees
WHERE employee_id > 1110;
Consultamos los registros que hemos insertado recientemente; Notar como se muestra el ROWNUM de cada linea.
SELECT
ROWNUM,
employee_id,
last_name,
email
FROM hr.employees
WHERE employee_id > 1110
AND ROWNUM < 4;
Este ejemplo muestra que es posible usar la pseudocolumna ROWNUM en la cláusula WHERE de una consulta.
Pseudocolumnas de consultas jerárquicas
Para más información acerca de las consultas Consultas Jerárquicas de Oracle hacer click aquí.
Pseudocolumna LEVEL
Para cada fila devuelta por una consulta jerárquica, la pseudocolumna LEVEL devuelve 1 para una fila raíz, 2 para un hijo de una raíz, y así sucesivamente. Una fila raíz es la fila más alta dentro de un árbol invertido. Una fila secundaria es cualquier fila no raíz. Una fila principal es cualquier fila que tiene hijos. Una hilera de hojas es cualquier hilera sin hijos.
Tenga en cuenta que es posible usar la pseudocolumna LEVEL en consultas normales, no solo en consultas jerárquicas. A continuación un ejemplo:
SELECT
LEVEL,
CASE
WHEN LEVEL > 4 THEN 'ALTO'
WHEN LEVEL < 3 THEN 'BAJO'
ELSE 'MEDIO'
END AS consideración
FROM dual
CONNECT BY LEVEL <= 5
ORDER BY LEVEL DESC;