- Preparar
- Tables
- View Inline
- La cláusula WITH
- Views
- Funciones de tabla canalizadas
Preparar
Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.
Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
Tables
Normalmente, la cláusula FROM enumera las tablas y las condiciones de combinación utilizadas en la consulta. Su forma más simple es cuando se accede a una única tabla, como se muestra a continuación. En este ejemplo, solicitamos los datos de todas las columnas de la tabla EMPLOYEES y solicitamos que los datos estén ordenados por la columna EMPLOYEE_ID. Dado que todos los datos provienen de una sola tabla, solo tenemos esa tabla referenciada en la cláusula FROM.
SELECT *
FROM employees
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
14 rows selected.
SQL>
El resultado anterior muestra todos los datos de todas las columnas de la tabla EMPLOYEES, como se esperaba.
En muchos casos, necesitamos obtener datos de varias tablas a la vez. Esto se hace mediante combinaciones. En el siguiente ejemplo, estamos usando una combinación de estilo ANSI entre las tablas EMPLOYEES y DEPARTMENTS. La combinación, o coincidencia, se realiza mediante la columna DEPARTMENT_ID en cada tabla. Cuando hacemos una combinación de estilo ANSI, todas las tablas y sus condiciones de combinación se enumeran en la cláusula FROM. Cuando ejecutamos la siguiente consulta, vemos el nombre de cada empleado (EMPPLOYEE.EMPLOYEE_NAME) y su nombre de departamento (DEPARTMENT.DEPARTMENT_NAME).
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_name;
EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS RESEARCH
ALLEN SALES
BLAKE SALES
CLARK ACCOUNTING
FORD RESEARCH
JAMES SALES
JONES RESEARCH
KING ACCOUNTING
MARTIN SALES
MILLER ACCOUNTING
SCOTT RESEARCH
SMITH RESEARCH
TURNER SALES
WARD SALES
14 rows selected.
SQL>
Muchos desarrolladores de Oracle prefieren utilizar la sintaxis que no es ANSI para las combinaciones. Se hace referencia a las tablas como una lista separada por comas en la cláusula FROM y las condiciones de combinación son predicados en la cláusula WHERE. La siguiente declaración es funcionalmente equivalente a la anterior.
SELECT e.employee_name, d.department_name FROM employees e, departments d WHERE d.department_id = e.department_id ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH ALLEN SALES BLAKE SALES CLARK ACCOUNTING FORD RESEARCH JAMES SALES JONES RESEARCH KING ACCOUNTING MARTIN SALES MILLER ACCOUNTING SCOTT RESEARCH SMITH RESEARCH TURNER SALES WARD SALES 14 rows selected. SQL>
Hay una serie de variaciones en las combinaciones, que se describen aquí.
Vistas en línea / View InLine
A menudo verá subconsultas en la cláusula FROM. Se conocen como vistas en línea. Una vez definidos y con un alias, se pueden unir y referenciar como una tabla normal. La siguiente declaración es funcionalmente equivalente a los dos ejemplos anteriores, pero la combinación se ha movido a una vista en línea.
SELECT ed.employee_name, ed.department_name
FROM (SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id) ed
ORDER BY ed.employee_name;
EMPLOYEE_N DEPARTMENT_NAM
---------- --------------
ADAMS RESEARCH
ALLEN SALES
BLAKE SALES
CLARK ACCOUNTING
FORD RESEARCH
JAMES SALES
JONES RESEARCH
KING ACCOUNTING
MARTIN SALES
MILLER ACCOUNTING
SCOTT RESEARCH
SMITH RESEARCH
TURNER SALES
WARD SALES
14 rows selected.
SQL>
Cláusula WITH
Una alternativa a una vista en línea es mover la subconsulta a la cláusula WITH. Luego podemos hacer referencia a esta consulta con nombre en la cláusula FROM de la instrucción SELECT principal. Esto se puede usar para hacer que una cláusula FROM muy complicada parezca mucho más simple.
WITH emp_dept_join AS ( SELECT e.employee_name, d.department_name FROM employees e, departments d WHERE d.department_id = e.department_id ) SELECT ed.employee_name, ed.department_name FROM emp_dept_join ed ORDER BY ed.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH ALLEN SALES BLAKE SALES CLARK ACCOUNTING FORD RESEARCH JAMES SALES JONES RESEARCH KING ACCOUNTING MARTIN SALES MILLER ACCOUNTING SCOTT RESEARCH SMITH RESEARCH TURNER SALES WARD SALES 14 rows selected. SQL>
La cláusula WITH se analiza con más detalle WITH Clause : Subquery Factoring in Oracle
(OJO)
Views
Otra alternativa es crear una vista convencional basada en la subconsulta.
CREATE OR REPLACE VIEW emp_dept_join_v AS SELECT e.employee_name, d.department_name FROM employees e, departments d WHERE d.department_id = e.department_id; La vista oculta parte de la complejidad de la consulta. Observe lo simple que se ve ahora la cláusula FROM. SELECT ed.employee_name, ed.department_name FROM emp_dept_join_v ed ORDER BY ed.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH ALLEN SALES BLAKE SALES CLARK ACCOUNTING FORD RESEARCH JAMES SALES JONES RESEARCH KING ACCOUNTING MARTIN SALES MILLER ACCOUNTING SCOTT RESEARCH SMITH RESEARCH TURNER SALES WARD SALES 14 rows selected. SQL>
Funciones de tabla canalizadas
El ejemplo de esta sección utiliza una función de tabla canalizada. No se preocupe demasiado por la funcionalidad en este momento. Es una técnica más avanzada, pero es bueno ver cómo se ve cuando se hace referencia a ella en la cláusula FROM. Primero tenemos que crear un tipo de colección y la propia función de tabla canalizada.
CREATE TYPE t_employee_name_tab AS TABLE OF VARCHAR2(10);
/
CREATE OR REPLACE FUNCTION get_employee_names
RETURN t_employee_name_tab PIPELINED
AS
BEGIN
FOR cur_rec IN (SELECT employee_name FROM employees) LOOP
PIPE ROW (cur_rec.employee_name);
END LOOP;
RETURN;
END;
/
Si ve algo como lo siguiente en una cláusula FROM, significa que se está utilizando una función de tabla o una función de tabla canalizada. Son tipos especiales de funciones que se pueden consultar como si fueran tablas.
SELECT e.column_value AS employee_name FROM TABLE(get_employee_names) e ORDER BY e.column_value; EMPLOYEE_N ---------- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD 14 rows selected. SQL>
Las funciones de tabla canalizadas se analizan con más profundidad aquí.
