Un cursor es un área de memoria en la que se almacena el resultado de una sentencia SELECT. Es la manera en la que PL/SQL maneja las filas que devuelve una consulta. PL/SQL no puede realizar consultas con las mismas sentencias de SQL, necesita hacer uso de cursores bien sea para retornar una única fila o varias.
Cursores explícitos
Se pueden crear los cursores de forma explícita para devolver una o varias filas si se quiere un control preciso. Siempre de debe crear un cursor explícito para las consultas que devuelven más de una fila.
En el siguiente ejemplo vamos a hacer un SELECT que devuelva cuatro campos de una tabla donde el campo employee_id no sea nulo. Sería así en SQL como podrás ver es muy fácil:
SELECT employee_id, first_name, last_name, hire_date FROM oehr_employees WHERE employee_id IS NOT NULL;
Ahora para hacer exactamente los mismo en PL/SQL podemos ver que es mucho más complicado. En primer lugar declaramos las variables en las que vamos a guardar los resultados del SELECT, luego declaramos el cursor y por último en el propio cuerpo del programa, ahí está toda la sintaxis de donde se abre el cursor, luego metemos la información en las variables y por último hay que cerrar el cursor.
DECLARE l_employee_id OEHR_EMPLOYEES.employee_id%TYPE; l_first_name OEHR_EMPLOYEES.first_name%TYPE; l_last_name OEHR_EMPLOYEES.last_name%TYPE; l_hire_date OEHR_EMPLOYEES.hire_date%TYPE; -- declaramos el cursor CURSOR c1 IS SELECT employee_id, first_name, last_name, hire_date FROM oehr_employees WHERE employee_id IS NOT NULL; BEGIN OPEN c1; -- se abre el cursor LOOP --abrimos un loop que recorrerá toda la consulta FETCH c1 INTO l_employee_id, l_first_name, l_last_name, l_hire_date; -- se guarda los datos en las variables DBMS_OUTPUT.put_line('id: '||l_employee_id|| ' Nombre: '||l_first_name||' Apellido: '|| l_last_name||' Fecha contratación '|| l_hire_date); EXIT WHEN c1%notfound OR c1%notfound IS NULL; END LOOP; --cerramos el loop CLOSE c1; --cierre del cursor END;
Resultado:
id: 100 Nombre: steven Apellido: King Fecha contratación 01/31/2008
id: 101 Nombre: neena Apellido: Kochhar Fecha contratación 05/07/2010
id: 102 Nombre: lex Apellido: De Haan Fecha contratación 08/29/2013
id: 103 Nombre: alexander Apellido: Hunold Fecha contratación 08/19/2010
Statement processed.
0.01 seconds
Atributos del cursor
Los atributos del cursor devuelven información sobre la ejecución de las sentencias DML y DDL, como las sentencias INSERT, UPDATE, DELETE, SELECT INTO, COMMIT o ROLLBACK.
Los atributos de cursor devuelven información relevante sobre la sentencia SQL que se acaba de ejecutar. Para hacer uso de los atributos se debe añadir el nombre del cursor explícito al principio del atributo. Por ejemplo, cursortabla%FOUND.
Tratemos uno a uno los atributos de los cursores: After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
Atributo %FOUND: después de abrir un cursor , pero antes de la primera obtención, %FOUND devuelve NULL. Por el contrario, devuelve TRUE si la última búsqueda devolvió una fila, o FALSE si la última búsqueda no ha devuelto ninguna fila.
Atributo %ISOPEN: si un cursor está abierto, entonces %ISOPEN devuelve TRUE ; en caso contrario, %ISOPEN será FALSE.
Atributo %NOTFOUND: si el último fetch devolvió una fila, entonces %NOTFOUND regresará FALSE. Si la última búsqueda no ha devuelto ninguna fila, %NOTFOUND devuelve TRUE. como habrás notado %NOTFOUND es el opuesto a %FOUND.
Atributo %ROWCOUNT: inmediatamente después de abierto un cursor, %ROWCOUNT devuelve 0 antes de la primera búsqueda. Luego devuelve el número de filas obtenidas hasta el momento.
Cursores implícitos
Si se va a crear una consulta que retorna una única fila se puede usar los cursores implícitos.
En el próximo ejemplo usaremos un cursor implícito en el que únicamente podremos devolver una sola fila, si es resultado de la consulta regresa más de una fila, la aplicación dará un error:
DECLARE l_employee_id OEHR_EMPLOYEES.employee_id%TYPE; l_first_name OEHR_EMPLOYEES.first_name%TYPE; l_last_name OEHR_EMPLOYEES.last_name%TYPE; l_hire_date OEHR_EMPLOYEES.hire_date%TYPE; BEGIN SELECT employee_id, first_name, last_name, hire_date INTO l_employee_id, l_first_name, l_last_name, l_hire_date FROM oehr_employees WHERE employee_id = 100; DBMS_OUTPUT.put_line('id: '||l_employee_id|| ' Nombre: '||l_first_name||' Apellido: '|| l_last_name||' Fecha contratación '|| l_hire_date); END;
Resultado:
id: 100 Nombre: steven Apellido: King Fecha contratación 01/31/2008
Statement processed.
0.01 seconds