Esta es la octava parte de una serie de artículos que muestran los conceptos básicos de SQL. En este artículo, echamos un vistazo a algunas de las variaciones de la instrucción INSERT.
- Preparar
- COMMIT y ROLLBACK
- INSERT Básico
- INSERT into View
- INSERT … SELECT
- Sub-Consultas Escacalar
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».
En este artículo, también vamos a necesitar la siguiente copia de la tabla EMPLOYEES. Observe que hemos hecho obligatorias las columnas EMPLOYEE_NAME y JOB. La columna EMPLOYEE_ID es implícitamente obligatoria, como resultado de ser la clave principal.
-- DROP TABLE employees2 PURGE; CREATE TABLE employees2 ( employee_id NUMBER(4) CONSTRAINT employees2_pk PRIMARY KEY, employee_name VARCHAR2(10) NOT NULL, job VARCHAR2(9) NOT NULL, manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) );
COMMIT y ROLLBACK
Todos los cambios en el lenguaje de manipulación de datos (DML) se realizan como parte de una transacción. No son permanentes hasta que se confirman mediante la declaración COMMIT. Una vez comprometido, la única forma de revertir un cambio es emitir una nueva declaración DML para alterar los datos. Se pueden agrupar varios extractos para formar una sola transacción.
Los comandos del lenguaje de definición de datos (DDL) realizan una confirmación implícita, que también confirma todos los cambios de DML pendientes en la sesión actual.
Si decide que no desea mantener algunos cambios sin confirmar, puede deshacerse de ellos usando la instrucción ROLLBACK. Muchos de los ejemplos de este artículo emitirán declaraciones ROLLBACK después de la prueba, para revertir los datos a su estado original.
NOTA: Algunas herramientas y lenguajes de programación se comprometen automáticamente de forma predeterminada, por lo que emiten automáticamente una declaración COMMIT después de cada declaración DML que procesan. No dejes que esto te engañe haciéndote pensar que es un comportamiento predeterminado. No lo es.
INSERT básica
La versión más simple de la instrucción INSERT no especifica las columnas que se insertarán. Se supone que los valores se especifican en el orden de columna correcto y que no se han omitido valores de columna. Como resultado, esto es peligroso en el código de la aplicación, ya que agregar una columna a la tabla romperá la declaración.
INSERT INTO employees2 VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20 1 row selected. SQL> ROLLBACK;
Especificar una lista de columnas es mucho más seguro ya que la declaración no se romperá si se agregan columnas opcionales a la tabla.
INSERT INTO employees2 (employee_id, employee_name, job, manager_id, hiredate, salary, commission, department_id) VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20 1 row selected. SQL> ROLLBACK;
Una lista de columnas explícita también le permite eliminar columnas opcionales de la declaración e intercambiar las posiciones de las columnas en la declaración. En el siguiente ejemplo, las columnas MANAGER_ID, COMMISSION y DEPARTMENT_ID se han eliminado y la columna EMPLOYEE_ID se ha movido al final de la lista de columnas y la lista de valores.
INSERT INTO employees2 (employee_name, job, hiredate, salary, employee_id) VALUES ('JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800,8888); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20 1 row selected. SQL> ROLLBACK;
Recuerde, no se permite perder columnas obligatorias, a menos que tenga valores predeterminados de columna o activadores que las completen. En el siguiente ejemplo se omite la columna JOB, que es una columna obligatoria en la tabla EMPLOYEES2.
INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id) VALUES (8888,'JONES',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id) * ERROR at line 1: ORA-01400: cannot insert NULL into ("TEST"."EMPLOYEES2"."JOB") SQL>
INSERT en View
Es posible insertar en la tabla base asociada con una vista. Hay algunas restricciones asociadas con esto, pero están un poco fuera del alcance de un artículo de nivel principiante. En el siguiente ejemplo, creamos una vista simple en la tabla EMPLOYEES2 y luego la insertamos.
CREATE OR REPLACE VIEW employees2_v AS SELECT * FROM employees2; INSERT INTO employees2_v VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20 1 row selected. SQL> ROLLBACK;
No lo verá muy a menudo, pero también puede insertarlo en vistas en línea. Esto se puede usar para controlar la lista de columnas para la inserción, en lugar de usar una lista de columnas explícita.
INSERT INTO (SELECT employee_id, employee_name, job, hiredate, salary FROM employees2) VALUES (8888,'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20 1 row selected. SQL> ROLLBACK;
INSERT … SELECT
Es posible crear varias filas en una sola instrucción INSERT utilizando la sintaxis INSERT … SELECT. El siguiente ejemplo copia todos los datos de la tabla EMPLOYEES en la tabla EMPLOYEES2.
-- Asegurese de que la table este vacia. TRUNCATE TABLE employees2; INSERT INTO employees2 SELECT * FROM employees; COMMIT; SELECT * FROM employees2 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>
La lista de columnas de la instrucción INSERT debe coincidir con la lista SELECT de la consulta.
TRUNCATE TABLE employees2; INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary) SELECT employee_id, employee_name, job, hiredate, salary FROM employees; COMMIT; SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 7369 SMITH CLERK 17-DEC-1980 00:00:00 800 7499 ALLEN SALESMAN 20-FEB-1981 00:00:00 1600 7521 WARD SALESMAN 22-FEB-1981 00:00:00 1250 7566 JONES MANAGER 02-APR-1981 00:00:00 2975 7654 MARTIN SALESMAN 28-SEP-1981 00:00:00 1250 7698 BLAKE MANAGER 01-MAY-1981 00:00:00 2850 7782 CLARK MANAGER 09-JUN-1981 00:00:00 2450 7788 SCOTT ANALYST 19-APR-1987 00:00:00 3000 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000 7844 TURNER SALESMAN 08-SEP-1981 00:00:00 1500 7876 ADAMS CLERK 23-MAY-1987 00:00:00 1100 7900 JAMES CLERK 03-DEC-1981 00:00:00 950 7902 FORD ANALYST 03-DEC-1981 00:00:00 3000 7934 MILLER CLERK 23-JAN-1982 00:00:00 1300 14 rows selected. SQL>
Las reglas normales se aplican a la parte de consulta de la declaración, por lo que puede filtrar los datos o unirse a otras tablas para recopilar los datos.
TRUNCATE TABLE employees2; INSERT INTO employees2 SELECT * FROM employees WHERE department_id = 20; COMMIT; SELECT * FROM employees2 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 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20 7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20 7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20 5 rows selected. SQL>
Sub-Consulta Escalar
Las subconsultas escalares se pueden utilizar para obtener un valor para una instrucción INSERT. En el siguiente ejemplo, la columna SALARIO se completa con una subconsulta escalar, que devuelve el SALARIO máximo de la tabla EMPLEADOS y le agrega 1000.
TRUNCATE TABLE employees2; INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary) VALUES (8888, 'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),(SELECT MAX(salary)+1000 FROM employees)); SELECT * FROM employees2 ORDER BY employee_id; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID ----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- 8888 JONES CLERK 17-DEC-1980 00:00:00 6000 1 row selected. SQL>
Pingback: Introducción a SQL - DBandTech.com