Comandos Oracle – Segunda Parte –

En esta segunda entrega de Comandos SQL de Oracle vamos a aprender a crear tablas, modificarlas, añadir restricciones (constraints) y comentarios y modificar sus columnas y tipos. Además aprenderemos a crear secuencias las cuales nos ayudaran a insertar valores de forma automática, crearemos y modificaremos claves primerias (PK) y claves foráneas (FK) para poder enlazar valores de diferentes tablas.

Para acceder a la primera parte haz clic en este enlace
Para acceder a la tercera parte haz clic en este enlace

Para acceder a la creación de funciones y procedimientos haz clic en este enlace

Ver tablas creadas:

{SELECT * FROM data_dictionary}

Dónde data_dictionary puede ser:

DBA_TABLES: Contiene todas las tablas de la base de datos
ALL_TABLES: Contiene todas las tablas accesibles por el usuario (las propias más las que tiene permisos sobre ellas)
USER_TABLES: Contiene totas las tablas del usuario
DBA_SEGMENTS: Contiene todos los segmentos de la base de datos, esto incluye tablas, indices y segmentos de rollback entre otros.

SELECT * FROM USER_TABLES;

ilumina2 photo

Insertar registros en una tabla: Caracteres y fechas entre comillas simples

{INSERT INTO tabla [] VALUES []}

INSERT INTO tab (dnum, depnam, hiredate, usr_actual)

VALUES (50, ‘DEVELOPMENT’, SYSDATE, USERID);

Insertar registros nulos:

INSERT INTO dept VALUES (70, ‘FINANCE’, NULL);

Creación de Scripts: Los scripts nos permiten crear sencillas aplicaciones para añadir datos de forma sencilla a nuestra base de datos.

{ACCEPT variable PROMPT ‘texto a mostrar’ INSERT INTO tabla [] VALUES[&variable]}

ACCEPT id PROMPT ‘Introduce el ID de usuario:’

ACCEPT last_name prompt ‘Introduce apellido del usuario:’

ACCEPT first_name prompt ‘Introduce nombre del usuario:’

INSERT INTO my_employee (id, LAST_NAME, FIRST_NAME, USERID)

VALUES (&id, ‘&last_name’, ‘&first_name’, substr(‘&first_name’,1,1) || substr(‘$last_name’,1,7));

Insertar registros de otra tabla:

{INSERT INTO tabla [] select(…)}

INSERT INTO managers(id, name, salary, hiredate)

SELECT empno, ename, sal, hiredate FROM emp WHERE job = ‘MANAGER’

Modificación de registros:

{UPDATE tabla SET column = value where…}

UPDATE emp SET deptno = 20 WHERE empno = 7782;

UPDATE emp SET (job,deptno) = (SELECT job, deptno from…)

Eliminar registros:

{DELETE FROM tabla WHERE condición}

DELETE FROM department WHERE dname = ‘DEVELOPMENT’

DELETE FROM employee WHERE deptno = (SELECT deptno from…)

Control transacciones:

COMMIT: Confirmar cambios realizados en la base de datos

ROLLBACK: Deshacer todos los cambios realizados en la base de datos

SAVEPOINT nombre: Crear punto de guardado

ROLLBACK TO nombre: Volver a punto de guardado

Creación de tablas:

{CREATE TABLE tabla (columna tipo(longitud), columna..)}

Tipos: NUMBER, VARCHAR2, DATE, NUMBER(dcimal,digit)

CREATE TABLE dept (deptno NUMBER(2) NOT NULL, name VARCHAR2(50));

Crear tabla a partir de otra (por subconsulta):

{CREATE TABLE tabla AS SELECT…}

CREATE TABLE departamento AS SELECT empno, ename “Nombre Departamento”, sal FROM dept;

Alter table (Modificar tabla)

-Añadir columnas:

{ALTER TABLE tabla ADD (columna1 tipo(), columna2 tipo()…);}

ALTER TABLE dept add (job VARCHAR2(9));

-Modificar columnas existentes:

{ALTER TABLE tabla MODIFY (col1 tipo() [DEFAULT expr]);}

ALTER TABLE empl modify (name VARCHAR2(50) DEFAULT ‘Nom’);

Nota: Solo se puede cambiar de tipo si el atributo está vacío. Solo se puede decrementar la precisión si no es inferior al contenido con longitud máxima.

Eliminar columna:

{ALTER TABLE tabla DROP COLUMN columna;}

ALTER TABLE dept DROP COLUMN dname;

ALTER TABLE dept DROP (dname, director,…);

Atributos únicos (no se puede repetir su valor):

ALTER TABLE dept

ADD(

    UNIQUE (nombre),

    UNIQUE (direccion)

  );

ALTER TABLE dept ADD UNIQUE(nombre, direccion,…) – -La combinación de las dos debe ser única

 Eliminación de restricción UNIQUE:

ALTER TABLE dept DROP UNIQUE(nombre)

ALTER TABLE dept DROP UNIQUE(nombre, dirección,…)  – -Si se crea UNIQUE compleja (varios atributos), se debe eliminar por completo

Notificar columna como no usada:

{ALTER TABLE tabla SET UNUSED (columna)}

ALTER TABLE dept SET UNUSED (loc);

Eliminar columnas marcadas como no usadas:

{ALTER TABLE tabla DROP UNUSED COLUMNS;}

ALTER TABLE dept DROP UNUSED COLUMNS;

Trabajo con claves primarias:

ALTER TABLE tabla ADD PRIMARY KEY (col1, col2…);

ALTER TABLE dept DROP PRIMARY KEY [CASCADE]; – – CASCADE si hay foraneas apuntando a ella

ALTER TABLE dept DISABLE PRIMARY KEY [CASCADE];

ALTER TABLE dept ENABLE PRIMARY KEY;

Trabajo con claves foráneas:

ALTER TABLE compra ADD(

    FOREIGN KEY(farmacia) REFERENCES farmacia(idfarmacia),

    FOREIGN KEY(medicamento) REFERENCES medicamentos(idmedicamento)

  )

DROP CONSTRAINT fk_compra_farmacia. –NO se puede con DROP FOREIGN KEY(atrib). Se debe especificar el nombre de la fk. Para ello, es necesario crearlas con CONSTRAINT tal y como se muestra a continuación

Trabajo con CONSTRAINTS:

ALTER TABLE compra

ADD(

CONSTRAINT fk_compra_farmacia FOREIGN KEY (farmacia) REFERENCES farmacias(idfarmacia),

CONSTRAINT fk_compra_medicamento FOREIGN KEY (medicamento) REFERENCES medicamentos(idmedicamento)

)

ALTER TABLE compra DROP CONSTRAINT fk_compra_farmacia;

ALTER TABLE compra DISABLE CONSTRAINT pk_farmacias;

ALTER TABLE compra ENABLE CONSTRAINT pk_farmacias;

DROP CONSTRAINT fk_compra_farmacia.

Renombrar columnas:

ALTER TABLE table RENAME COLUMN col_inicial to col_final;

Modificar nombre de objeto (tabla, vista…):

{RENAME tabla/vista… TO tabla/vista…}

RENAME dept TO departmento;

Eliminar tabla:

{DROP TABLE table;}

DROP TABLE dept;

Eliminar todos los registros (TRUNCAR):

{TRUNCATE TABLE tabla;}

TRUNCATE TABLE dept;

Añadir comentarios a una tabla:

{COMMENT ON TABLE/COLUMN table/columna IS ‘comentario’;}

COMMENT ON TABLE emp IS ‘Employee Infromation’

COMMENT ON COLUMN TABLE.column IS ‘comentario’

COMMENT ON COLUMN emp.ename IS ‘Nombre trabajador’

Constraints (restricciones)

Definición de constraints

{CREATE TABLE tabla

(columna tipo() [default expr] CONSTRAINT [nombre] tipo (columna));}

-Nivel de tabla:

CREATE TABLE emp(

(empno NUMBER(4),

ename VARCAHR2(10),

deptno NUMBER(2),

CONSTRAINT emp_empno_pk

PRIMARY KEY (empono));

– Nivel de columna:

CREATE TABLE emp(

(empno NUMBER(4) [CONSTRAINT emp_empno_nn] NOT NULL,

ename VARCAHR2(10),

deptno NUMBER(2) NOT NULL);

Constraints (tipos y situación):

NOT NULL (Nivel de columna)

CREATE TABLE emp(

(empno NUMBER(4) [CONSTRAINT emp_empno_nn] NOT NULL,

ename VARCAHR2(10),

deptno NUMBER(2) NOT NULL);

UNIQUE (Columna o tabla)

CREATE TABLE dept

(dname VARCHAR(14),

CONSTRAINT dept_dname_uk UNIQUE(dname));

PRIMARY KEY (Columna o tabla) -> PK lleva implícito UNIQUE

CREATE TABLE dept

(deptno NUMBER(2),

CONSTRAINT dept_deptno_pk PRIMERY KEY (deptno));

FOREIGN KEY (Columna o tabla)

CREATE TABLE emp

(ename VARCAHR2(10) NOT NULL,

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

REFERENCES dept (deptno));

CHECK -Condicion- (Columna o tabla)

CREATE TABLE emp

(deptno NUMBER(2),

CONSTRAINT emp_deptno_ck CHECK (deptno between 10 and 99));

Añadir CONSTRAINT (restricción) a tabla existente:

{ALTER TABLE tabla add/modify [CONSTRAINT CONSTRAINT] type (col);}

Podemos añadir restricción NOT NULL con MODIFY

Podemos añadir o borrar CONSTRAINTs pero NO modificar. Para modificar

eliminar y volver a crear

ALTER TABLE emp add CONSTRAINT emp_mgr_fk

FOREING KEY(mgr) REFERENCES emp(empno);

Eliminación de restricciones (CONSTRAINTs):

{ALTER TABLE tabla DROP CONSTRAINT CONSTRAINT;}

ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk;

Borrar CONSTRAINT PK y (FK de tabla dependiente de otra usando-CASCADE-)

{ALTER TABLE tabla DROP primary key CASCADE;}

ALTER TABLE dept DROP primary key cascade;

Eliminación de restricciones en cascada: Es usado para eliminar PK u otras con dependencias

{ALTER TABLE tabla DROP (constraint) cascade CONSTRAINT;}

ALTER TABLE dept DROP (deptno) cascade CONSTRAINTS;

Desactivar constraint:

{ALTER TABLE tabla DISABLE CONSTRAINT constraint [CASCADE];}

ALTER TABLE emp disable CONSTRAINT emp_empno_pk CASCADE;

Activar constraints:

{ALTER TABLE tabla ENABLE CONSTRAINT CONSTRAINT;}

ALTER TABLE emp ENABLE CONSTRAINT emp_empno_pk;

Creación de vistas:

{CREATE VIEW vista AS SELECT…}

[with check option CONSTRAINT CONSTRAINT]

[with read only]

CREATE view empvu10 as select empno “nº empleado”,

job from emp where deptno=10;

Modificación de vistas:

{CREATE OR REPLACE VIEW vista [(colname, colname, colname)] as select…}

Nota: Los alias de columna se puede poner en SELECT o en CREATE

CREATE or replace view empvu10

(employee_number, employee_name, job_title)

as select empno, ename, job from emp where deptno = 10;

Creación vistas complejas con funciones de grupo para ver 2 tablas:

{CREATE view dept_sum_vu (name, minsal, maxsal, avgsal)

as select d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

from emp e, dept d where e.deptno = d.deptno group by d.dname}

With check option:

CREATE or replace view vista as select…

with check option CONSTRAINT CONSTRAINT

With read only:

CREATE or replace view vista as select…

with read only;

Nota: En este caso no podemos quitar, insertar o modificar la vista

Eliminación de vistas:

{DROP view vista;}

Creación de vistas inline:

{SELECT … FROM tabla a(SELECT…) b where…}

SELECT tablaA.ename, tablaA.sal, tablaA.deptno, tablaB.maxsal

from emp tablaA, (select deptno, max(sal) from emp group by deptno) tablaB

where tablaA.deptno = tablaB.deptno and tablaA.sal < tablaB.maxsal

Obtener los N primeros/últimos datos de una tabla (Top-N):

{SELECT ROWNUM [as alias] FROM (SELECT… FROM tabla WHERE… ORDER BY Top-NCol)

where ROWNUM <= N}

SELECT ROWNUM as RANKING, ename, sal

from (select ename, sal from emp order by sal desc)

where rownum <=3;

Creación de secuencias:

CREATE/ALTER sequence nombre_secuencia

increment by n

start with n (no en ALTER)

maxvalue n

minvalue n

cycle (continua generando despues de max)

cache n (memoria oracle);

Insertar valores de una secuencia en una tabla:

INSERT INTO tabla (valorActual, valorSiguiente)

VALUES (secuencia.currval, secuencia.nextval)

Eliminar secuencias:

{DROP sequence secuencia;}

Crear índices:

{CREATE index indice on tabla (col, col+10, upper(ename)…)}

Eliminar índices:

{DROP index índice}

Crear sinonimos:

{CREATE [public] SYNONYM sinonimo FOR objecto}

CREATE public synonym dept for sis228.dept

Eliminar sinónimos:

{DROP SYNONYM sinónimo}

Entradas relacionadas:

Para acceder a la primera parte haz clic en este enlace
Para acceder a la tercera parte haz clic en este enlace

Anuncios

7 Responses to Comandos Oracle – Segunda Parte –

  1. Edison says:

    Buenazo, mejor ke muchos guias rapidas, muy directo, de lo mejorcito.

  2. junior says:

    concuerdo con el primer comentario, muy bueno y al grano.

  3. Pingback: Instalación Oracle 11g en Linux « Aitor Rigada

  4. Pingback: Instalación Oracle 10g en Linux « Aitor Rigada

  5. Pingback: Procedimientos y funciones en Oracle « Aitor Rigada

  6. I¡¦m now not positive exactly where you might be getting your info, but excellent subject. I needs to spend some time locating out a lot far more or working out a lot more. Thanks for amazing details I was looking for this information for my mission.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: