Cursores en DBMS: definición, tipos, atributos, usos

El sistema de administración de bases de datos (DBMS) admite declaraciones de lenguaje de manipulación de datos como consultas de inserción, actualización y eliminación. Cada base de datos difiere sintácticamente pero la funcionalidad sigue siendo la misma en las operaciones mencionadas. En este artículo, veamos cómo realizar consultas de inserción, actualización y eliminación con el cursor.

Cursores

Cada vez que se ejecutan declaraciones DML, se crea un área de trabajo temporal en la memoria del sistema y se denomina cursor. Un cursor puede tener más de una fila, pero en cuanto al procesamiento, solo se tiene en cuenta 1 fila. Los cursores son muy útiles en todo tipo de bases de datos como Oracle, SQL Server, MySQL, etc. Se pueden usar bien con declaraciones DML como Actualizar, Insertar y Eliminar. Especialmente los cursores implícitos están ahí con estas operaciones. De vez en cuando cambia los valores y, por lo tanto, los valores de atributos de cursor implícitos deben asignarse en una variable local para su uso posterior. En PL/SQL, hay dos tipos diferentes de cursores disponibles.

• Cursores implícitos 
• Cursores explícitos

Cursores explícitos

Los programadores definen los cursores explícitos para tener más área de control en el área de contexto. Tiene que ser definido en la sección de declaración del Bloque PL/SQL. Por lo general, se define en una declaración SELECT y devuelve más de una fila como salida. Podemos iterar sobre las filas de datos y realizar las operaciones requeridas.

Pasos involucrados en la creación de cursores explícitos:

• Cursor Declaración para inicializar la memoria

CURSOR <cursorName> IS 
   SELECT <Required fields> FROM <tableName>;

• Cursor Apertura para asignar la memoria

OPEN <cursorName>; 

• Obtención del cursor para recuperar los datos

FETCH <cursorName> INTO <Respective columns>

• Cierre del cursor para liberar la memoria asignada

CLOSE <cursorName>;

Ejemplo:

DECLARE 
   empId employees.EMPLOYEEID%type; 
   empName employees.EMPLOYEENAME%type; 
   empCity employees.EMPLOYEECITY%type; 
   CURSOR c_employees is 
      SELECT EMPLOYEEID, EMPLOYEENAME, EMPLOYEECITY FROM employees; 
BEGIN 
   OPEN c_employees; 
   LOOP 
   FETCH c_employees into empId , empName , empCity; 
      EXIT WHEN c_employees %notfound; 
      dbms_output.put_line(empId || ' ' || empName || ' ' || empCity); 
   END LOOP; 
   CLOSE c_employees ; 
END; 
/

Producción:

Cursores implícitos

Para declaraciones DML, los cursores implícitos están disponibles en PL/SQL, es decir, no es necesario declarar el cursor, e incluso para las consultas que devuelven 1 fila, los cursores implícitos están disponibles. A través de los atributos del cursor, podemos rastrear la información sobre la ejecución de un cursor implícito.

Atributos de los cursores implícitos:

Los atributos de cursor implícitos proporcionan los resultados sobre la ejecución de INSERTAR, ACTUALIZAR y ELIMINAR. Tenemos diferentes atributos de Cursor como «%FOUND», «%ISOPEN», «%NOTFOUND» y %ROWCOUNT. El resultado de la instrucción SQL ejecutada más recientemente estará disponible en Cursor. Inicialmente, el valor del cursor será nulo.

Veamos los diferentes atributos del cursor uno por uno con respecto a las declaraciones DML. Entonces, creemos una tabla de muestra llamada «empleados» en Oracle:

CREATE TABLE employees
( EMPLOYEEID number(10) NOT NULL,  
  EMPLOYEENAME varchar2(50) NOT NULL,  
  EMPLOYEECITY varchar2(50)  
); 

Inserte los registros en la tabla «empleados»

INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (1,'XXX','CHENNAI');
INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (2,'XYZ','MUMBAI');
INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (3,'YYY','CALCUTTA');

Existencia de registros en tabla “empleados”

SELECT * FROM employees;

Atributo %ENCONTRADO

CREATE TABLE tempory_employee  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee WHERE employeeId = employeeNo ;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO tempory_employee  (employeeId,employeeName,employeeCity)  VALUES (2, 'ZZZ', 'Delhi');
  END IF;
END;
/

Ahora verifique los detalles presentes en la tabla tempory_employee

SELECT * FROM tempory_employee;

Producción:

Atributo %FOUND y ejemplo de operación de actualización

CREATE TABLE tempory_employee1  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee WHERE employeeId = employeeNo ;
  IF SQL%FOUND THEN  -- delete succeeded
     UPDATE employees SET employeeCity = 'Chandigarh' WHERE employeeId = 1;
  END IF;
END;
/

Salida de SELECT * FROM empleados:

Atributo %ENCONTRADO en la operación de actualización y luego realizando el ejemplo de operación de eliminación

CREATE TABLE tempory_employee2  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  UPDATE tempory_employee2 SET employeeCity = 'Gurgaon' WHERE employeeId = employeeNo;
  IF SQL%FOUND THEN  -- update succeeded
     DELETE FROM tempory_employee2 WHERE employeeId = 1 ;  -- Then delete a specific row
  END IF;
END;
/

Producción:

Atributo %ISOPEN:   para cursores implícitos, el resultado siempre es falso. El motivo es que Oracle se cierra inmediatamente después de ejecutar el resultado de DML. Por lo tanto el resultado es FALSO.

Atributo %NOTFOUND: Es justo lo contrario de %FOUND. %NOTFOUND es el opuesto lógico de %FOUND. %NOTFOUND da como resultado un valor VERDADERO para una instrucción INSERTAR, ACTUALIZAR o ELIMINAR que no afectó a ninguna fila. Por defecto, devuelve Falso.

Atributo %ROWCOUNT: %ROWCOUNT proporciona un número de filas afectadas por una instrucción INSERT, UPDATE o DELETE. Cuando no hay filas afectadas, %ROWCOUNT da como resultado 0, de lo contrario, devuelve el número de filas que se han eliminado.

CREATE TABLE tempory_employee3  AS SELECT * FROM employees;
DECLARE  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee3 WHERE employeeId = employeeNo ;
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;

Producción:

Los valores del atributo del cursor deben guardarse en una variable local y esas variables pueden usarse en usos futuros. La razón es que al realizar varias operaciones de base de datos en diferentes bloques, los valores de los atributos del cursor siguen cambiando y, por lo tanto, esto es muy necesario.

 El atributo %NOTFOUND se usa mejor solo con declaraciones DML pero no con la declaración SELECT INTO.