Conceptos Esenciales y Administración Práctica de Bases de Datos SQL y PL/SQL

Fundamentos de Bases de Datos (BD)

Una Base de Datos (BD) es un conjunto de datos de un mismo contexto, almacenados sistemáticamente para un uso ordenado e inteligente. Utiliza claves, relaciones y tipos que permiten mantener la homogeneidad de la colección de datos (integridad referencial).

Lenguajes de Operación en Bases de Datos

En las bases de datos existen dos tipos de lenguajes de operación:

• DML (Data Manipulation Language): Lenguaje de manipulación de datos (Ejemplos: INSERT, UPDATE, DELETE).
• DDL (Data Definition Language): Lenguaje de definición de datos (Ejemplos: CREATE, ALTER, DROP).

Estructura Lógica y Física

Las unidades básicas de una BD son el Tablespace y el Datafile.

En la estructura de una base de datos existe al menos una tabla y una columna.

Constraints (Restricciones)

Los Constraints permiten manejar la integridad de la BD:

• Referencial: Relaciones entre tablas.
• Dominio/Columna: Restricciones a nivel de columna.
• Identificación/Tabla: Restricciones a nivel de tabla.

El Catálogo es una BD que permite almacenar objetos y metadatos dentro de la misma BD.

Administración de Usuarios y Seguridad

Autenticación de Contraseña

Existen varios tipos de autenticación, incluyendo: usuario global y contraseña por Sistema Operativo (S.O.).

Comandos Básicos de Administración (DDL/DCL)

A continuación, se presentan comandos fundamentales para la gestión de usuarios y privilegios:

• CREATE USER *nombre* IDENTIFIED BY 'clave': Creación de usuario y clave.
• GRANT CREATE SESSION TO *usuario*: Otorga el privilegio para conectar a la BD.
• REVOKE CREATE SESSION FROM *usuario*: Revoca el privilegio de conexión.
• ALTER USER *nombre* IDENTIFIED BY *nueva_clave*
• ALTER USER *nombre* ACCOUNT LOCK;: Bloquear cuenta.
• ALTER USER *nombre* ACCOUNT UNLOCK;: Desbloquear cuenta.
• GRANT ALL PRIVILEGES TO *usuario*: Otorga todos los privilegios.
• GRANT ROLE *nombre_rol* TO *usuario*: Otorga un rol, permitiendo asignar un grupo de privilegios al usuario.

Crear un usuario con todos los privilegios para guardar datos y crear objetos:

CREATE USER *nombre* IDENTIFIED BY *clave*
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON data;

Gestión de Roles y Privilegios Específicos

CREATE ROLE *nombre_rol*;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
CREATE PROCEDURE, ALTER SYNONYM, DROP TABLE, DROP VIEW, DROP PROCEDURE, DROP SYNONYM
TO conn;

GRANT programador TO *usuario*;

DROP USER *usuario* CASCADE;

Gestión de Tablespaces

Un Tablespace es una división lógica de almacenamiento en la BD. Actúa como un puente entre el Sistema Operativo (S.O.) y la Base de Datos. Una BD está formada por varias unidades lógicas llamadas tablespaces. Cuando se crea una BD, se debe crear al menos un tablespace, denominado SYSTEM.

Comandos DDL para Tablespaces

Ejemplos prácticos:

• Crear un Tablespace:

  CREATE TABLESPACE prueba DATAFILE '/users/oradata/orcl/prueba01.dbt' SIZE 100M;

• Aumentar tamaño (añadir un nuevo datafile):

  ALTER TABLESPACE prueba ADD DATAFILE '/users/oradata/orcl/prueba02.dbt' SIZE 50M;

• Aumentar tamaño (redimensionar datafile existente):

  ALTER DATAFILE 'users/oradata/orcl/prueba01.dbf' RESIZE 50M;

• Borrar Tablespace:

  DROP TABLESPACE prueba;

Estados y Disponibilidad

Un tablespace puede estar en dos estados principales:

• ONLINE: Significa que está disponible para operar con él.
• OFFLINE: Quiere decir que no se puede utilizar.

¿Para qué necesitamos el estado OFFLINE? Cuando el tablespace está en esta condición, no se podrán efectuar consultas, inserciones ni modificaciones sobre los objetos que contiene. Sin embargo, el resto de los objetos que se encuentran en otros tablespaces siguen estando accesibles. Si existen rollbacks activos, no se puede realizar la operación de OFFLINE.

Consulta de Estado y Rollbacks

Para buscar tablespaces activos:

SELECT tablespace_name, status FROM dba_tablespaces;

Para verificar segmentos de rollback activos:

SELECT rollback_segment nombre_del_segmento FROM *tabla_de_sistema* WHERE status = 'OFFLINE';

Estructura Interna y Objetos de Esquema

Un Segmento es la separación lógica entre diversos objetos que se pueden almacenar en un tablespace.

Componentes de la Estructura Interna de una BD

La estructura interna incluye:

• Tablas y Columnas
• Restricciones (Constraints)
• Usuarios y Esquemas
• Índices y Cluster
• Vistas y Secuencias
• Procedimientos, Funciones y Paquetes
• Disparadores (Triggers) y Sinónimos
• Privilegios y Roles
• Segmentos de Extensión y Bloques
• Segmentos de Rollback

Definición de Objetos

• Esquemas: Organizan lógicamente una base de datos.
• Secuencias: Es un objeto de un esquema que genera una serie de números enteros únicos.
• Vista: Es una relación virtual entre tablas y vistas de la BD, almacenada como objeto.
• Vista Materializada: Para que una vista pueda ser materializada, debe actualizarse mediante los datos subyacentes de la lista y debe actualizarse a través de los triggers (disparadores).

Programación y Objetos Avanzados (PL/SQL)

Comandos DDL para Objetos de Esquema

Sintaxis de creación de objetos comunes:

• Secuencia:

  CREATE SEQUENCE s_secuencia
  START WITH *valor_inicial*
  [INCREMENT BY *valor*]
  [MAXVALUE *valor*][MINVALUE *valor*];

• Inserción usando Secuencias:

  INSERT INTO empleado (cod_emp, codemp) -- Insertar fila en tabla empleado
  VALUES (s_secuencia.NEXTVAL, 'emp01');
  
  INSERT INTO salario (cod_emp, mes)
  VALUES (s_secuencia.CURRVAL, *valor_mes*);

• Sinónimo:

  CREATE SYNONYM sinonimo FOR *objeto*;

• Índice:

  CREATE INDEX indice ON *tabla* (*columna*) [ASC/DESC];

• Vista:

  CREATE OR REPLACE VIEW *nombre_vista* AS *sentencia_select*;

Introducción a PL/SQL

PL/SQL es un lenguaje de programación incluido en las bases de datos de Oracle. Permite construir bloques secuenciales, procedimientos, paquetes y funciones. Un bloque PL/SQL contiene tres secciones:

1. DECLARE: Declaraciones (Opcional).
2. BEGIN: El cuerpo del programa (Necesario).
3. EXCEPTION: Manejo de errores (Opcional).
4. END;

Procedimientos Almacenados

CREATE OR REPLACE PROCEDURE pr_nombre
(w_nombre VARCHAR, w_salario NUMBER, w_com NUMBER, w_jefe VARCHAR)
IS
BEGIN
  INSERT INTO empleados (cod_emp, nom_emp, salario, comision, cod_jefe)
  VALUES (s_secuencia.NEXTVAL, w_nombre, w_salario, w_com, w_jefe);
END pr_nombre;

Ejecución:

EXECUTE pr_nombre(25, 'contabilidad', '19-09-1967');

Funciones

CREATE OR REPLACE FUNCTION f_nombre
RETURN *tipo_dato*
IS
BEGIN
  -- Lógica de la función
END f_nombre;

Cursores

Los cursores permiten procesar filas de un conjunto de resultados una por una.

DECLARE
  CURSOR cursor_1 IS
    SELECT nombre, numero, salario FROM emp;
  w_nombre  emp.nombre%TYPE;
  w_numero  emp.numero%TYPE;
  w_salario emp.salario%TYPE;
BEGIN
  OPEN cursor_1;
  FETCH cursor_1 INTO w_nombre, w_numero, w_salario;
  CLOSE cursor_1;
END;