SQL y SQL*Plus Comandos conceptos de bases de datos
Página Principal Temas Básicos PL/SQL

 

SQL y SQL*Plus

SQL (Structured Query Language) es el lenguaje estructurado de consultas que se usa para acceder a los datos en una base de datos (BD) relacional. El lenguaje SQL es estándar para cualquier BD relacional. En el caso de Oracle, se incluyen comandos y funciones adicionales y por eso se le llama SQL*Plus.

Terminología de BD

CONCEPTO

DESCRIPCIÓN

Tabla

Es la estructura básica de almacenamiento en una BD relacional. Consiste de una o más columnas y cero o más filas.

Fila

Es el equivalente a un registro en un sistema de archivos. Las filas se componen de columnas.

Columna

Es un elemento de una fila que contiene un dato. Equivale a un campo en un registro.

Llave primaria (PK)

Es la columna o conjunto de ellas que identifica de forma única a una fila dentro de la tabla.

Llave foránea (FK)

Es la columna o conjunto de columnas que sirve de relación con la llave primaria de otra tabla.

Vistas

Estructuras dinámicas "virtuales" que se comportan en forma similar a una tabla (para efectos de consulta) y que se crean con un select.

Transacción

Conjunto de instrucciones que se ejecutan todas o ninguna. La transaccionalidad permite mantener consistente la información en una BD.

Comandos

SELECT retorna filas de una o más tablas en la base de datos.

SELECT < columnas >

FROM < tablas >

WHERE < condición >

HAVING < condición de grupos >

GROUP BY < grupos >

ORDER BY < ordenamiento >

;

INSERT permite agregar filas a una tabla

INSERT INTO < tabla > < columnas >

VALUES < valores para columnas >

< comando select >

;

UPDATE permite modificar el valor de una o más columnas de una o más filas.

UPDATE < tabla >

SET < columna > = < valor > < , >

WHERE < condición >

;

DELETE borra filas de una tabla.

DELETE FROM < tabla > WHERE < condición >;

Operaciones de grupo

En un select se pueden utilizar varias funciones que agrupan datos y presentan los resultados como un valor de una columna. Las más comunes son SUM, COUNT, MAX, MIN.

Ejemplo:

SELECT MAX( f_ingreso )

FROM empleado

;

Otras funciones

Existen otras funciones que se aplican a columnas simples y que aportan mucha funcionalidad. Ellas son NVL, DISTINCT y DECODE.

NVL ( columna, valor ) Retorna el valor, cuando la columna tiene valor nulo.

DISTINCT descarta los valores repetidos en una consulta (select)

DECODE ( columna, valor1, resultado1, valor2, resultado2,..., resultado_dflt) Evalúa la columna. Si el valor de ella es el valor1, retorna el resultado1, si el valor de columna es el valor2, retorna el resultado2, etc. Si el valor de la columna no es igual a ningún valorn, retorna el resultado_dflt.

Comandos DDL

CREATE para crear un objeto en la BD. Depende del objeto, así cambia la sintaxis.

ALTER para modificar la estructura o propiedades de un objeto

DROP para eliminar un objeto de la BD

Otros comandos importantes

COMMIT Confirma la aplicación definitiva de una transacción

ROLLBACK Deshace los cambios realizados en una transacción que no hayan sido confirmados por un COMMIT.

SAVEPOINT Son marcas de proceso durante una transacción. Se puede devolver hasta un savepoint, sin tener que deshacer toda la transacción.

GRANT Otorga algún permiso o derecho de acceso sobre un objeto en la BD.

REVOKE Elimina un permiso o derecho sobre un objeto en la BD.

EJEMPLO:

SQL> CREATE TABLE DEPARTAMENTO (

DEPTO_ID NUMBER(5) NOT NULL,

NOMBRE VARCHAR2(30))

;

SQL> CREATE TABLE EMPLEADO(

EMPLEADO_ID NUMBER(10) NOT NULL,

NOMBRE VARCHAR2(30),

F_INGRESO DATE,

DEPTO_ID NUMBER(5) NOT NULL)

;

INSERT INTO DEPARTAMENTO VALUES(1,’ADMINISTRATIVO’);

1 row created.

SQL> SELECT * FROM DEPARTAMENTO;

DEPTO_ID NOMBRE

--------- ------------------------------

1 ADMINISTRATIVO

2 VENTAS

3 INFORMATICA

SQL> SELECT * FROM EMPLEADO;

EMPLEADO_ID NOMBRE F_INGRES DEPTO_ID

----------- ------------------------------ -------- ---------

1 JUAN 01/04/98 1

2 MARIA 23/01/00 3

3 CARLOS 29/04/96 1

4 ANA 23/11/99 1

5 PEDRO 13/08/97 2

SELECT EMPLEADO.EMPLEADO_ID EMPLEADO,

EMPLEADO.NOMBRE NOMBRE_EMP,

DEPARTAMENTO.NOMBRE DEPARTAMENTO,

F_INGRESO FECHA_INGRESO

FROM EMPLEADO, DEPARTAMENTO

WHERE EMPLEADO.DEPTO_ID = DEPARTAMENTO.DEPTO_ID

AND F_INGRESO > '01-01-1998'

ORDER BY EMPLEADO.NOMBRE

EMPLEADO NOMBRE_EMP DEPARTAMENTO FECHA_IN

--------- --------------- ------------------------------ --------

4 ANA ADMINISTRATIVO 23/11/99

1 JUAN ADMINISTRATIVO 01/04/98

2 MARIA INFORMATICA 23/01/00

SELECT EMPLEADO_ID EMPLEADO,

EMPLEADO.NOMBRE NOMBRE_EMP,

F_INGRESO FECHA_INGRESO

FROM EMPLEADO, DEPARTAMENTO

WHERE EMPLEADO.DEPTO_ID = DEPARTAMENTO.DEPTO_ID

AND DEPARTAMENTO.NOMBRE = 'ADMINISTRATIVO'

ORDER BY 2

EMPLEADO NOMBRE_EMP FECHA_IN

--------- ------------------------------ --------

4 ANA 23/11/99

3 CARLOS 29/04/96

1 JUAN 01/04/98

SELECT EMPLEADO_ID EMPLEADO,

EMPLEADO.NOMBRE NOMBRE_EMP,

F_INGRESO FECHA_INGRESO

FROM EMPLEADO, DEPARTAMENTO

WHERE EMPLEADO.DEPTO_ID = DEPARTAMENTO.DEPTO_ID

AND DEPARTAMENTO.NOMBRE = 'ADMINISTRATIVO'

ORDER BY 3

EMPLEADO NOMBRE_EMP FECHA_IN

--------- ------------------------------ --------

3 CARLOS 29/04/96

1 JUAN 01/04/98

4 ANA 23/11/99

INSERT INTO EMPLEADO

VALUES(6,'GUILLERMO','16-08-1993',3);

1 row created.

SQL> SELECT * FROM EMPLEADO

2 ORDER BY F_INGRESO DESC;

EMPLEADO_ID NOMBRE F_INGRES DEPTO_ID

----------- ------------------------------ -------- ---------

2 MARIA 23/01/00 3

4 ANA 23/11/99 1

1 JUAN 01/04/98 1

5 PEDRO 13/08/97 2

3 CARLOS 29/04/96 1

6 GUILLERMO 16/08/93 3

UPDATE EMPLEADO

SET DEPTO_ID = 2

WHERE NOMBRE LIKE '%A%'

4 rows updated.

SQL> SELECT * FROM EMPLEADO;

EMPLEADO_ID NOMBRE F_INGRES DEPTO_ID

----------- ------------------------------ -------- ---------

1 JUAN 01/04/98 2

2 MARIA 23/01/00 2

3 CARLOS 29/04/96 2

4 ANA 23/11/99 2

5 PEDRO 13/08/97 2

6 GUILLERMO 16/08/93 3

6 rows selected.

UPDATE EMPLEADO

SET DEPTO_ID = 1

WHERE NOMBRE LIKE '%AN%'

;

2 rows updated.

SQL> SELECT * FROM EMPLEADO;

EMPLEADO_ID NOMBRE F_INGRES DEPTO_ID

----------- ------------------------------ -------- ---------

1 JUAN 01/04/98 1

2 MARIA 23/01/00 2

3 CARLOS 29/04/96 2

4 ANA 23/11/99 1

5 PEDRO 13/08/97 2

6 GUILLERMO 16/08/93 3

6 rows selected.

DELETE FROM EMPLEADO WHERE NOMBRE IN ('JUAN','PEDRO');

2 rows deleted.

PL/SQL

PL/SQL es una extensión de SQL que aporta las características de un lenguaje de programación. Con SQL se procesan todas las filas en grupo. Si se consulta, el RDBMS retorna todas las filas seleccionadas a un mismo tiempo y no se puede ir procesando una a una. Igual pasa con los borrados o las modificaciones. En SQL no se tienen ciclos, IF’s y otros comandos tradicionales de un lenguaje de programación. Con PL/SQL se solventan estas limitaciones.

PL/SQL trabaja en bloques. Los bloques se pueden anidar y se respeta la jerarquía de declaración de variables. Un bloque se compone de:

DECLARE

BEGIN

EXCEPTION

END

Ejemplo de bloque de PL/SQL

DECLARE

CURSOR detalles IS

SELECT dmo_unidades unidades,

dmo_acl_art_referencia referencia,

dmo_rrd rrd

FROM re_det_movimientos_tb

WHERE dmo_mov_emp_empresa = :new.mov_emp_empresa

AND dmo_mov_cli_cliente = :new.mov_cli_cliente

AND dmo_mov_movimiento = :new.mov_movimiento

;

accion_v re_tipos_movimiento_tb.tmo_accion%TYPE;

movimiento_v re_movimientos_tb.mov_movimiento%TYPE;

rrd_v re_movimientos_tb.mov_rrd%TYPE;

BEGIN

/*********************************************************

* Nombre: RE_MOV_BU

* Tipo: Trigger de before update

* Proposito:

* Aplicar los movimientos, según cambio de estado

********************************************************

* 8 de setiembre de 2000

********************************************************/

IF ( :new.mov_estado != :old.mov_estado ) THEN

IF ( :new.mov_estado = 'A' and

:old.mov_estado = 'R' ) THEN

BEGIN

SELECT tmo_accion

INTO accion_v

FROM re_tipos_movimiento_tb

WHERE tmo_tipo_movimiento = :new.mov_tmo_tipo_movimiento

;

EXCEPTION

WHEN NO_DATA_FOUND THEN

raise_application_error(-20001,'No encontró tipo de mov');

WHEN OTHERS THEN

raise_application_error(-20001,'RE_MOV_BU:'||sqlerrm);

END;

/* *******************************************

Ejecuta la acción dependiendo del tipo

de movimiento correspondiente.

******************************************* */

IF ( accion_v = 'A' ) THEN

FOR f IN detalles LOOP

UPDATE re_referencias_cliente_tb

SET acl_saldo_unidades = NVL(acl_saldo_unidades,0) +

f.unidades

WHERE acl_cli_cliente = :new.mov_cli_cliente

AND acl_art_referencia = f.referencia

;

IF ( SQL%ROWCOUNT = 0 ) THEN

RAISE_APPLICATION_ERROR(-20001,

'No encontró la referencia'||

f.referencia || ' para el cliente '||

to_char(:new.mov_cli_cliente));

END IF;

INSERT INTO re_saldos_rrd_tb(

sal_rrd,

sal_emp_empresa,

sal_cli_cliente,

sal_art_referencia,

sal_unidades,

sal_saldo_unidades)

VALUES(

f.rrd,

:new.mov_emp_empresa,

:new.mov_cli_cliente,

f.referencia,

f.unidades,

f.unidades)

;

END LOOP;

ELSIF ( accion_v = 'D' ) THEN

FOR f IN detalles LOOP

UPDATE re_referencias_cliente_tb

SET acl_saldo_unidades = NVL(acl_saldo_unidades,0) –

f.unidades

WHERE acl_cli_cliente = :new.mov_cli_cliente

AND acl_art_referencia = f.referencia

;

IF ( SQL%ROWCOUNT = 0 ) THEN

RAISE_APPLICATION_ERROR(-20001,'No encontró referencia’||

f.referencia || ' para el cliente '||

to_char(:new.mov_cli_cliente));

END IF;

UPDATE re_saldos_rrd_tb

SET sal_saldo_unidades = sal_saldo_unidades - f.unidades

WHERE sal_rrd = f.rrd

AND sal_emp_empresa = :new.mov_emp_empresa

AND sal_cli_cliente = :new.mov_cli_cliente

AND sal_art_referencia = f.referencia

;

IF ( SQL%ROWCOUNT = 0 ) THEN

RAISE_APPLICATION_ERROR(-20001,

'No encontraron saldos para RRD <'||f.rrd||'-'||

to_char(:new.mov_emp_empresa)||

'-'||to_char(:new.mov_cli_cliente)||'-'||

to_char(:new.mov_movimiento)||'>');

END IF;

END LOOP;

END IF;

ELSIF ( (:new.mov_estado = 'A' AND :old.mov_estado != 'R') OR

(:new.mov_estado = 'R') OR

(:old.mov_estado = 'N') ) THEN

RAISE_APPLICATION_ERROR(-20001,

'Invalido cambio de estado en el movimiento');

END IF;

END IF;

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20001,'RE_MOV_BU:'||sqlerrm);

END;

PRACTICA

  1. Cree una tabla llamada LIBROS_TB con las siguientes columnas: libro_id, nombre, tema, autor, editorial, año, paginas. Cree una tabla llamada AUTORES_TB con autor_id, nombre, nacionalidad.
  2. Las tablas que usted creó deben contener los siguientes datos:
  3. Libros:

    1,SANCHO PANZA, NOVELA, MANUEL CERVANTES, CASTILLA, 2000, 250

    2,CIEN AÑOS DE COMPAÑÍA, NOVELA, GARCIA MARCOS, CLB, 1975, 343

    3,ORACLE 8, INFORMATICA, J. LOCK, ORACLE PRESS, 1999, 875

    4,EL FUTBOL, DEPORTES, MARA DONA, ARGENTINA, 1986, 109

    5,VOLAR EN LA CANCHA, DEPORTES, MARA DONA, ARGENTINA, 1990, 120

    Autores:

    1,MANUEL CERVANTES, ESPAÑOL

    2,GARCIA MARCOS, COLOMBIANO

    3,J. LOCK, INGLES

    4.MARADONA, ARGENTINO

    Escriba un ejemplo de insert para libros y otro para autores.

  4. Construya los comandos para
    1. Seleccionar el libro_id y el nombre de los libros que tengan más de 200 páginas.
    2. Seleccionar el libro_id, el nombre del libro, el autor y la nacionalidad del autor, para los libros de editoriales que tengan la letra A y después la C en su nombre de editorial.
    3. Seleccionar la suma total de páginas de todos los libros de la BD
    4. Seleccionar el nombre del autor y la cantidad total de libros de cada uno.
    5. Seleccionar los distintos temas (sin repetirlos) de los libros.
  5. Borre todos los libros que tienen más de tres años de escritos a partir de la fecha de hoy.
  6.  

    Página Principal Temas Básicos PL/SQL

     

Trance Convexo

1