Caso Práctico de las Funciones INSTR y SUBSTR

Sintaxis de la función INSTR:

INSTR( cadena , sub_cadena [, posicion_desde [ , ocurrencias_numero ] ] )

Donde:

Cadena: Cadena de caracteres donde se buscará la sub-cadena.

Sub_cadena: Sub-cadena que se desea buscar.

Posicion_desde: Posición a partir de donde se quiere buscar.

Ocurrencias_numero: Número de ocurrencia que se desea encontrar su posición.

Sintaxis de la función SUBSTR:

SUBSTR(cadena,inicio,longitud) 

Donde:

Cadena: Cadena de caracteres

Inicio: Posición Inicial, por defecto 1. Si el valor es negativo, por ejemplo -1, la posición inicial es la longitud de la cadena -1. Si ponemos 0, Oracle pondrá 1.

Longitud: Longitud del texto a extraer.

CASO PRACTICO: Si tenemos la siguiente columna, de la tabla Clientes:

Nombres_Clientes

Javier Serapio

Christian Hernan Salome Donaide

Y queremos extraer el segundo nombre de la columna "Nombres_Clientes", la consulta sería la siguiente:

SELECT SUBSTR(NOMBRES_CLIENTES, INSTR(NOMBRES_CLIENTES, ' ')+1) FROM CLIENTES

El resultado para el ejemplo sería: "Serapio", "Hernan" y "Donaide"

Sentencia MERGE en Oracle

Lo que permite la sentencia SQL MERGE dependiendo de una condición lógica, actualizar
registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple.

Las ventajas de la sentencia SQL MERGE son claras:

• Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.
• La sentencia puede paralelizarse de forma transparente.
• Se evita la necesidad de realizar actualizaciones múltiples.
• Es especialmente útil para realizar operaciones en masa (Por ejemplo en aplicaciones data warehousing).
• El rendimiento de la base de datos mejora ya que se necesitan menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.

A continuación vamos a ver con un caso práctico la aplicación de MERGE:

• Estructura de la tabla HR.EMPLOYEES

          EMPLOYEE_ID NUMBER PRIMARY KEY

          FIRST_NAME VARCHAR2(30)

          LAST_NAME VARCHAR2(30)

          HIRE_DATE DATE

• Estructura de la tabla HR.NEW_EMPLOYEES

          EMPLOYEE_ID NUMBER PRIMARY KEY

          NAME VARCHAR2(65)

• Entonces si queremos insertar datos en la tabla HR.NEW_EMPLOYEES con datos provenientes de la tabla HR.EMPLOYEES, utilizando la sentencia MERGE, sería de la siguiente forma:

          MERGE INTO HR.NEW_EMPLOYEES C

          USING HR.EMPLOYEE E

          ON (C.EMPLOYEE_ID=E.EMPLOYEE_ID)

          WHEN MATCHED THEN 

          UPDATE SET

          C.NAME=E.FIRST_NAME||','||E.LAST_NAME

          WHEN NOT MATCHED THEN

          INSERT VALUES (E.EMPLOYEE_ID,E.FIRST_NAME||','||E.LAST_NAME)

Oracle/PLSQL: ORA-02292 - Restricción de integridad violada - Registro secundario encontrado

A continuación vamos a mostrar con un caso práctico algunos posibles escenarios en los que se presentaría el error ORA-02292 y tres formas para evitarlo:

1. Vamos a tener dos tablas HR.PROVEEDOR (Tabla padre) y HR.PRODUCTO (Tabla hija)

CREATE TABLE HR.PROVEEDOR

( COD_PROV NUMERIC(10) NOT NULL,

  NOM_PROV varchar2(50) NOT NULL,

  NOM_CONT varchar2(50),

  CONSTRAINT PROVEEDOR_PK PRIMARY KEY (COD_PROV)

);

CREATE TABLE HR.PRODUCTO

( COD_PROD NUMERIC(10) NOT NULL,

  COD_PROV NUMERIC(10) NOT NULL,

  CONSTRAINT PROVEEDOR_FK

  FOREIGN KEY (COD_PROV)

  REFERENCES PROVEEDOR (COD_PROV)

);

2. Insertamos datos en las tablas:

INSERT INTO HR.PROVEEDOR (COD_PROV, NOM_PROV, NOM_CONT) VALUES (1000, 'MICROSOFT', 'DEAN VALDIVIA');

INSERT INTO HR.PRODUCTO (COD_PROD, COD_PROV)  VALUES (50000, 1000);

3. Si intentamos eliminar un registro:

delete from hr.proveedor where cod_prov = 1000; --- Nos aparecerá el mensaje de error ORA-02292

4. Si queremos deshabilitar las restricciones, con las siguientes consultas podemos ver el detalle:

select owner, constraint_name, constraint_type, table_name, status from dba_constraints; ó

select owner, constraint_name, constraint_type, table_name, status from user_constraints;

Para deshabilitar una restricción: ALTER TABLE table_name disable CONSTRAINT constraint_name;

Para habilitar una restricción: ALTER TABLE table_name enable CONSTRAINT constraint_name;

5. Para el ejemplo, si intentamos deshabilitar la Primary Key nos aparecerá el siguiente error: 

ORA-02297: cannot disable constraint (HR.PROVEEDOR_PK) - dependencies exist

Esto es porque en la tabla hija existe una Foreign Key se esta haciendo referencia al campo COD_PROV de la tabla HR.PROVEEDOR.

De igual forma si queremos eliminar la tabla que contiene la Primary Key nos aparecerá el siguiente error: ORA-02449: unique/primary keys in table referenced by foreign keys

6. Entonces para poder eliminar el registro COD_PROV = 1000, tenemos tres opciones:

6.1. Deshabilitar la Foreign Key

alter table hr.producto disable constraint proveedor_fk;

6.2. La Foreign Key tenga la opción ON DELETE CASCADE

• Eliminamos la Foreign Key existente: alter table hr.producto drop constraint proveedor_fk;
• Luego agregamos la Foreign Key con la opción "on delete cascade": alter table hr.producto add constraint proveedor_fk foreign key (cod_prov) references hr.proveedor(cod_prov) on delete cascade;

Ahora si eliminamos el registro: delete from hr.proveedor where cod_prov=1000, y automáticamente borrará el registro de la tabla padre e hijo.

6.3. La tercera posibilidad sería eliminar cod_prov=1000 en la tabla hija y luego en la tabla padre.

ADICIONALMENTE

Si optamos por deshabilitar todas las restricciones de un esquema de base de datos Oracle, podemos hacerlo con una de las tres formas siguientes:

Forma 1:

begin

for i in (select constraint_name, table_name from user_constraints) LOOP

execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';

end loop;

end;

/

Forma 2:

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

Forma 3:

BEGIN

  FOR c IN

  (SELECT c.owner, c.table_name, c.constraint_name

   FROM user_constraints c, user_tables t

   WHERE c.table_name = t.table_name

   AND c.status = 'ENABLED'

   ORDER BY c.constraint_type DESC)

  LOOP

    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);

  END LOOP;

END;

/