Tipos de Transacciones SQL

Objetivos:

• Familiarizarse con los diferentes tipos de Transacciones SQL de Oracle.

• Clasificar las Sentencias por Tipo de Transacción.
• Entender como funcionan.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________

Transacciones SQL en Oracle.

Una transacción se puede definir como:

• Una unidad de trabajo compuesta por diversas tareas, cuyo resultado final debe ser que se ejecuten todas o ninguna de ellas.^[1]

• Un conjunto de sentencias SQL que se ejecutan en una Base de Datos como una única operación, confirmándose o deshaciéndose todo el conjunto de sentencias SQL.^[2]

Oracle SQL cumple los estándares aceptados por la industria. Oracle Corporation asegura la futura compatibilidad con los estándares en evolución al implicar de forma activa a personal clave en los comités de normalización SQL. Los comités aceptados por la industria son ANSI (Instituto Nacional Americano de Normalización) e ISO (Organización Internacional de Normalización). Tanto ANSI como ISO aceptan SQL como lenguaje estándar para las Bases de Datos relacionales.

_____________________________________________________________________________________
Lenguaje de Manipulación de Datos(DML).

DML(Data Manipulation Language): Son todas las operaciones básicas que realizamos en una Base de Datos las cuales afectan los Datos. Desde consultar, agregar, actualizar/modificar y eliminar registros en una Tabla.

Nota: Estas sentencias no ejecutan un COMMIT implícito (no se confirma la transacción actual).

• CALL: Esta sentencia se usa para invocar/ejecutar procedimientos o funciones independientes, así como también procedimientos/funciones definidos dentro de tipos(types) o paquetes(packages).

Sintaxis:
CALL function_name;
---

• EXPLAIN PLAN: Este comando determina el plan de ejecución que la Base de Datos Oracle permite para ejecutar una sentencia SQL Especifica. Esta sentencia (por defecto) inserta un registro en la tabla: PLAN_TABLE; dicho registro describe los pasos del plan de ejecución, así como también costo de ejecución de la sentencia.

Nota: Puedes especificar otra tabla en la cual quieres insertar el registro; La tabla que especifiques debe tener los mismos campos y estructura de PLAN_TABLE.

Sintaxis:
EXPLAIN PLAN [SET STATEMENT_ID = 'text']
[INTO [schema .] table [@ dblink]] FOR statement;

En la Sintaxis:

• SET STATEMENT_ID: Es el nombre que deseas especificar para esa sentencia, si se omite el valor seria nulo.

• [INTO [schema .] table [@ dblink]]: Es la tabla donde quieres insertar el registro.

• FOR statement: Es la sentencia por el cual se generará el plan de ejecución.

Ejemplo:

EXPLAIN PLAN

    SET STATEMENT_ID = 'Actualiza_Correo'

      FOR UPDATE employees e

      SET email =

                (SELECT

                      first_name||'_'||last_name||'@MagicPLSQL.COM'

                 FROM employees

                 WHERE LENGTH(first_name||'_'||last_name||'@MagicPLSQL.COM') < 26

                 AND employee_id = e.employee_id);

/*Creamos el plan de ejecución para el SCRIPT que  actualiza el campo email de tabla: employees; Los registros actualizados tendrán un formato: nombre_apellido@MagicPLSQL.COM, siempre y cuando esa cadena tenga una longitud máxima de 25 caracteres(Precision por defecto del campo email).*/

---

SELECT

      statement_id,

      operation,

      options,

      object_owner,

      object_type,

      object_name,

      cpu_cost,

      io_cost

FROM plan_table

WHERE statement_id = 'Actualiza_Correo';

/*Consultamos el tabla: PLAN_TABLE para verificar algunas de las propiedades del plan de ejecución para el SCRIPT anterior.*/

---OUTPUT: 

• LOCK TABLE: Esta sentencia permite loquear una o más tablas, particiones de tablas o subparticiones de tablas. La tabla en cuestión permanece bloqueada hasta que se ejecuta un COMMIT o ROLLBACK.

Nota: Aun estando la tabla bloqueada, otros usuarios pueden consultarla. Podemos decir que los Lectores(Readers) nunca bloquean a los Escritores(Writers) y los Escritores(Writers) nunca bloquean a los Lectores(Readers).

Sintaxis:
LOCK TABLE
   [ schema. ] { table | view }
   [ { PARTITION (partition)
     | SUBPARTITION (subpartition)
     }
   | @ dblink
   ]
     [, [ schema. ] { table | view }
        [ { PARTITION (partition)
          | SUBPARTITION (subpartition)
          }
        | @ dblink
        ]
     ]...
   IN lock_mode MODE
   [ NOWAIT ];
En la Sintaxis:

•lock_mode: puede recibir estos parámetros:

Parámetro	Descripción
ROW SHARE	Permite el acceso concurrente a la tabla, pero no permite que otros usuarios bloqueen la tabla de forma exclusiva.
ROW EXCLUSIVE	Permite el acceso concurrente a la tabla, pero no permite que otros usuarios bloqueen la tabla de forma exclusiva ni de forma compartida(SHARE)
SHARE UPDATE	Permite el acceso concurrente a la tabla, pero no permite que otros usuarios bloqueen la tabla de forma exclusiva.
SHARE	Permite el acceso concurrente a la tabla, pero no permite que otros usuarios actualicen el tipo de bloqueo en la tabla.
SHARE ROW EXCLUSIVE	Permite ver los registros de la tabla, pero no permite que otros usuarios actualicen la tabla o que la bloqueen en modo compartido(SHARE).
EXCLUSIVE	Permite consultas en la tabla, pero no otra actividad.

Ejemplos:
LOCK TABLE jobs
IN EXCLUSIVE MODE NOWAIT;
/*Este sentencia bloquea la tabla jobs de forma que solo permite que otros usuarios realicen consultas en ella.*/
---
LOCK TABLE departments
IN SHARE MODE NOWAIT;
/*Aquí bloqueamos la tabla departments de forma que otros usuarios no puedan alterar el bloqueo a ella.*/
---

• SELECT:  <<Detalles Aquí>>

• INSERT:  <<Detalles Aquí>>

• UPDATE: <<Detalles Aquí>>

• DELETE: <<Detalles Aquí>>

• MERGE: <<Detalles Aquí>>

_____________________________________________________________________________________

Lenguaje de Definición de Datos(DDL).

Las sentencias DDL(Data Definition Language): Permiten crear, alterar o eliminar objetos de esquemas; Analizar información en una tabla, índice o cluster; Establecer opciones de auditoria y agregar comentarios al Diccionario de Datos.

Nota: Oracle Server ejecuta un COMMIT implícito  antes y después de cada instrucción DDL, por lo cual se confirma la transacción actual.

• ASSOCIATE STATISTICS: Utilice la sentencia ASSOCIATE STATISTICS para asociar un tipo de estadísticas (o estadísticas por defecto) que contiene funciones relacionadas con la recopilación de estadísticas, la selectividad, o el costo de una o más columnas, funciones independientes, paquetes, tipos, índices de dominio, o indextypes.

Consulte la vista del diccionario de datos USER_ASSOCIATIONS para obtener una lista de todas las asociaciones de tipo de estadísticas actuales.

Sintaxis:

ASSOCIATE STATISTICS WITH

  { column_association | function_association }

  [ storage_table_clause ];

• DISASSOCIATE STATISTICS: Utilice la sentencia se DISASSOCIATE STATISTICS para desasociar estadísticas predeterminadas o un tipo de estadísticas de columnas, funciones independientes, paquetes, tipos, índices de dominio, o tipos de índices.

Sintaxis:
DISASSOCIATE STATISTICS FROM
   { COLUMNS [ schema. ]table.column
               [, [ schema. ]table.column ]...
   | FUNCTIONS [ schema. ]function
                 [, [ schema. ]function ]...
   | PACKAGES [ schema. ]package
                [, [ schema. ]package ]...
   | TYPES [ schema. ]type
             [, [ schema. ]type ]...
   | INDEXES [ schema. ]index
               [, [ schema. ]index ]...
   | INDEXTYPES [ schema. ]indextype
                  [, [ schema. ]indextype ]...
   }
   [ FORCE ] ;

• ANALYZE: Nos permite recopilar este tipo de estadísticas:

Recoger o eliminar información sobre un índice o partición de índice, una mesa o tabla de particiones, tabla de índice organizado, clúster o atributo de objeto escalar.

Validar la estructura de un índice o partición de índice, una mesa o tabla de particiones, tabla de índice organizado, clúster o referencia de objeto (REF).

Identificar las filas migrados y encadenados de una mesa o clúster.

Sintaxis:
ANALYZE
  { TABLE [ schema. ]table
      [ PARTITION (partition)
      | SUBPARTITION (subpartition)
      ]
  | INDEX [ schema. ]index
      [ PARTITION (partition)
      | SUBPARTITION (subpartition)
      ]
  | CLUSTER [ schema. ]cluster
  }
  { validation_clauses
  | LIST CHAINED ROWS [ into_clause ]
  | DELETE [ SYSTEM ] STATISTICS
  | compute_statistics_clause
  | estimate_statistics_clause
  };

• AUDIT: Con esta sentencia puede:
 • Realizar un seguimiento de las sentencias SQL en las sesiones de usuarios futuras. Puede hacer un seguimiento de la aparición de una sentencia SQL específica o de todas las sentencias SQL autorizadas por un privilegio del sistema en particular. Es posible realizar operaciones de auditoría sobre las sentencias SQL de sesiones posteriores, no para las sesiones actuales.

•  Realizar un seguimiento de las operaciones en un objeto de esquema específico. Puede realizar estos seguimientos sobre los objetos de esquema de las sesiones actuales, así como para las sesiones posteriores.

• NOAUDIT: Utilice la sentencia NOAUDIT para detener las operaciones de auditoría previamente habilitados por la sentencia AUDIT.

La declaración NOAUDIT debe tener la misma sintaxis que la declaración de auditoría anterior.

• RENAME: Utilice la instrucción RENAME para cambiar el nombre de una tabla, vista, secuencia, o un sinónimo privado.

Oracle transfiere automáticamente las restricciones de integridad, los índices y las subvenciones en el objeto antiguo al nuevo objeto.

Oracle invalida todos los objetos que dependen del objeto cambiado de nombre, tales como vistas, sinónimos y procedimientos almacenados y funciones que hacen referencia a una tabla de renombrado.

Sintaxis:
RENAME old_name
   TO new_name ;

Ejemplos:
CREATE TABLE primer_nombre
(
  columna1 NUMBER(3),
  columna2 VARCHAR(5)
);
/*Creamos la tabla primer_nombre.*/
---
RENAME primer_nombre
TO segundo_nombre;
/*Con esta sentencia la renombramos a segundo_nombre.*/
---

• COMMENT: Utilice la sentencia COMMENT para añadir un comentario sobre una tabla, vista, vista materializada, o columna en el Diccionario de Datos.

Para eliminar a un comentario de la Base de Datos, se debe especificar la cadena vacía: ''.

Sintaxis:
COMMENT ON
  { TABLE [ schema. ]
    { table | view }
  | COLUMN [ schema. ]
    { table. | view. | materialized_view. } column
  | OPERATOR [ schema. ] operator
  | INDEXTYPE [ schema. ] indextype
  | MATERIALIZED VIEW materialized_view
  }
  IS string;

Ejemplos:
COMMENT ON
    COLUMN hr.departments.department_name
    IS 'Almacena el nombre del departamento.';
/*Agregamos un comentario para describir en campo department_name de la table departments del esquema hr.*/
---
COMMENT ON
    COLUMN hr.departments.department_name
    IS '';
/*Aquí eliminamos el anterior comentario.*/
---

• CREATE:  <<Detalles Aquí>>

• ALTER:  <<Detalles Aquí>>

• TRUNCATE:  <<Detalles Aquí>>

• PURGE:  <<Detalles Aquí>>

• DROP:  <<Detalles Aquí>>

• FLASHBACK:  <<Detalles Aquí>>

_____________________________________________________________________________________
Transacciones de Control.
Las Transacciones de Control administran los cambios hechos por las sentencias DML. Al controlar y manejar las sentencias DML, permiten al usuario mantener la integridad de los datos.


• COMMIT:  <<Detalles Aquí>>
• ROLLBACK:  <<Detalles Aquí>>
• SAVEPOINT:  <<Detalles Aquí>>

• SET TRANSACTION: Utilice la instrucción SET TRANSACTION para establecer la transacción actual como de sólo lectura o de lectura / escritura, establecer su nivel de aislamiento, o asignarlo a un segmento de cancelación especificado.

Sintaxis:
SET TRANSACTION
   { { READ { ONLY | WRITE }
     | ISOLATION LEVEL
       { SERIALIZABLE | READ COMMITTED }
     | USE ROLLBACK SEGMENT rollback_segment
     } [ NAME string ]
   | NAME string
   };

• SET CONSTRAINT: Utilice la sentencia SET CONSTRAINTS para especificar, para una transacción particular, si una restricción diferible se comprueba después de cada sentencia DML (IMMEDIATE) o cuando se confirma(COMMIT) la transacción (DEFERRED). Se puede utilizar esta sentencia para establecer el modo de obtener una lista de nombres de restricciones o para todas las limitaciones.

La duración del modo SET CONSTRAINT es equivalente a la duración de la actual transacción o hasta que otra sentencia SET CONSTRAINT es ejecutada.

Sintaxis:
SET { CONSTRAINT | CONSTRAINTS }
    { constraint [, constraint ]...
    | ALL
    }
    { IMMEDIATE | DEFERRED };
_____________________________________________________________________________________
Lenguaje de Control de Datos(DCL).
Las sentencias DCL(Data Control Language):  Controlan el nivel de acceso que tienen los usuarios en los objetos de Base de Datos.

Nota: Al igual que en las sentencias DDL, Oracle Server ejecuta un COMMIT implícito  antes y después de cada sentencia DCL.

GRANT:  <<Detalles Aquí>>
REVOKE:  <<Detalles Aquí>>
_____________________________________________________________________________________
Control de Secciones.
Las sentencias de Control de Secciones permiten administrar de manera dinámica las propiedades de Sección para el usuario actual (Conectado a la Base de Datos). Estas sentencias no ejecutan un COMMIT implícito.

• ALTER SESSION:

Ejemplo:
SELECT *
FROM NLS_SESSION_PARAMETERS;
/*Consultamos NLS_SESSION_PARAMETERS para verificar los valores configurados en las propiedades de fecha de nuestra Sección.*/
--OUTPUT:

SELECT
      INITCAP(TO_CHAR(SYSDATE, 'fmDAY DD "de" MONTH, "año" YYYY')) AS "Fecha Actual"
FROM dual;
/*El parámetro NLS_LANGUAGE tenia el valor en Ingles, por eso notamos que los componentes de fecha se muestran en ese idioma.*/
--OUTPUT:

ALTER SESSION
    SET NLS_LANGUAGE = 'SPANISH';
/*Cambiamos la propiedad NLS_LANGUAGE a Español y ejecutamos las dos consultas anteriores, notar la diferencia.*/
---OUTPUT:

• SET ROLE: Cuando un usuario inicia sesión en una Base de Datos Oracle, la Base de Datos permite todos los privilegios concedidos explícitamente al usuario y todos los privilegios de los roles predeterminados del usuario. Durante la sesión, el usuario o una aplicación puede utilizar la sentencia SET ROLE cualquier número de veces para activar o desactivar las funciones habilitadas actualmente para la sesión.

Sintaxis:
SET ROLE
   { role [ IDENTIFIED BY password ]
     [, role [ IDENTIFIED BY password ] ]...
   | ALL [ EXCEPT role [, role ]... ]
   | NONE
   };
_____________________________________________________________________________________
Control del Sistema.
Las sentencias de Control del Sistema (ALTER SYSTEM) nos ayudan a administrar las propiedades/parámetros de inicialización de una instancia de Base de Datos Oracle de forma dinámica. Al usar estas sentencias debe especificar el alcance(SCOPE) para así ejecutar los cambios en el archivo de parámetros del servidor.

Nota: Estas sentencias no ejecutan un COMMIT implícito.

• ALTER SYSTEM:

Ejemplo:

SELECT *

FROM V$PARAMETER;

/*Consultamos la Vista: V$PARAMETER para verificar algunos parámetros del Sistema.*/

---OUTPUT:

ALTER SYSTEM

    SET NLS_DATE_LANGUAGE = 'SPANISH'

                                                              SCOPE=SPFILE;

/*Con este sentencia cambiamos el valor del parámetro NLS_DATE_LANGUAGE  a Español para toda la instancia.*/

_____________________________________________________________________________________

Fuentes: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1, docs.oracle.com/cd...