Objetivos:
• Distinguir entre Cursores Implícitos y Explícitos.
• Discutir las razones para utilizar los cursores explícitos.
• Declarar y controlar cursores explícitos.
• Utilizar bucles simples y cursores FOR LOOP para obtener datos.
• Declarar y utilizar cursores con parámetros.
• Bloquear filas con la cláusula FOR UPDATE.
• Hacer referencia a la fila actual con la cláusula WHERE CURRENT OF.
• Distinguir entre Cursores Implícitos y Explícitos.
• Discutir las razones para utilizar los cursores explícitos.
• Declarar y controlar cursores explícitos.
• Utilizar bucles simples y cursores FOR LOOP para obtener datos.
• Declarar y utilizar cursores con parámetros.
• Bloquear filas con la cláusula FOR UPDATE.
• Hacer referencia a la fila actual con la cláusula WHERE CURRENT OF.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
Los Cursores.
Los Cursores.
El servidor Oracle utiliza áreas de trabajo (llamadas zonas privadas SQL) al ejecutar sentencias SQL para almacenar y procesar información. Puede utilizar los cursores explícitos para nombrar una zona privada SQL y acceder a su información almacenada.
El servidor Oracle abre implícitamente un cursor para procesar cada instrucción SQL que no este asociada con un cursor declarado explícitamente.
Tipo |
Descripción
|
Implícito |
Son declarados por PL/SQL de forma implícita para cada DML o sentencia SELECT.
|
Explícito
|
Son ideales para consultas que devuelven varias filas; son declarados y gestionados por el programador, y se manipulan a través de sentencias específicas en sentencias ejecutables del bloque.
|
Se declaran cursores explícitos en PL/SQL cuando se tiene una instrucción SELECT que devuelve varias filas, ya que con el cursor es posible procesar cada fila devuelta.
Se conoce como el active set(conjunto activo) al conjunto de filas devueltas por una consulta de múltiples filas. Su tamaño es el número de filas que coinciden con su criterio de búsqueda.
Funciones de los Cursores Explícitos.
• Puede realizar el procesamiento fila por fila más allá de la primera fila devuelta por una consulta.
• Mantiene un registro de la fila que se está procesando actualmente.
• Permite al programador controlar manualmente los cursores explícitos en el bloque PL/SQL.
Manejando Cursores Explícitos.
Ahora que tiene una comprensión conceptual de los cursores, revise los pasos para utilizarlos.
1. En la parte declarativa de un bloque PL/SQL, declare el cursor, asignele un nombre y defina la estructura de su consulta.
2. Abra el cursor.
La sentencia OPEN ejecuta la consulta y vincula las variables que se hayan referenciado. Las filas identificadas por la consulta(active set) ahora están disponibles para el FETCH.
3. Obtener(FETCH) los datos del cursor.
En el diagrama de flujo anterior: Luego de cada búsqueda(FETCH), se prueba la existencia de filas en el cursor. Si no hay más filas para procesar, se debe cerrar el cursor.
4. Cierre el cursor.
La sentencia CLOSE libera el active set de filas. Ahora es posible volver a abrir el cursor para establecer un nuevo active set.
Detalles:
1. La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el cursor en la primera fila.
2. La sentencia FETCH recupera la fila actual y avanza el cursor a la siguiente hasta que no haya más filas o hasta que cierta condición se cumpla.
3. La sentencia CLOSE libera el cursor.
Sintaxis:
CURSOR cursor_name IS
select_statement;
En la Sintaxis:
• cursor_name: Es un identificador PL/SQL.
• select_statement: Es una sentencia SELECT sin la cláusula INTO.
En un cursor el active set está determinado por el SELECT al momento de su declaración. En PL/SQL es obligatorio tener una cláusula INTO al usar una sentencia SELECT. Sin embargo, tenga en cuenta que la sentencia SELECT en la declaración de cursor no puede tener dicha cláusula. Esto se debe a que sólo se está definiendo el cursor en la parte declarativa, no recuperando su filas.
Notas:
• No incluya la cláusula INTO en la declaración del cursor, ya que está se usa más adelante en la sentencia FETCH.
• Si desea que las filas sean procesadas en algún orden especifico, utilice la cláusula ORDER BY en la consulta.
• El cursor puede ser cualquier sentencia SELECT válida, incluyendo JOINs, UNIONs, subconsultas, y así sucesivamente.
Ejemplos:
DECLARE
v_dept NUMBER := 30;
CURSOR c_emps IS
SELECT employee_id,
last_name,
salary,
job_id
FROM employees
WHERE department_id = v_dept;
---
CURSOR c_dept_sal IS
SELECT d.department_name,
SUM(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
/*El ejemplo muestra la declaración de dos cursores: v_dept y c_dept_sal; c_emps consulta algunas informaciones de los empleados del departamento que contenga la variable v_dept, que en este caso es 30; c_dept_sal consulta la suma de salarios de los empleados de cada departamento.*/
---
Sentencia OPEN.
La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el puntero en la primera fila del cursor. La sentencia OPEN se incluye en la sección ejecutable del bloque PL/SQL.
OPEN es una sentencia ejecutable que realiza las siguientes operaciones:
1. Dinámicamente asigna la memoria para un área de contexto.
2. Analiza la sentencia SELECT.
3. Vincula las variables de entrada (establece los valores de las variables de entrada mediante la obtención de sus direcciones de memoria).
4. Identifica el conjunto activo (el conjunto de filas que satisfacen los criterios de búsqueda). Las filas en el active set no son asignadas a variables cuando se ejecuta la sentencia OPEN. Más bien, la sentencia FETCH es la que hace ese trabajo.
5. Sitúa el puntero a la primera fila del active set.
Nota: Si una consulta no devuelve ninguna fila cuando se abre el cursor, PL/SQL no lanza una excepción. Puede averiguar el número de filas devueltas por un cursor explícito mediante el uso del atributo <cursor_name>%ROWCOUNT.
Sentencia FETCH.
La sentencia FETCH recupera las filas del cursor de una en una. Después de cada FETCH, el cursor avanza a la siguiente fila del active set. Puede utilizar el atributo %NOTFOUND para determinar si todo el active set se ha recuperado.
La sentencia FETCH realiza las siguientes operaciones:
1. Lee y asigna los datos de la fila actual a las variables de PL/SQL.
2. Avanza el puntero a la siguiente fila del active set.
El número de variables en la cláusula INTO de la sentencia FETCH debe ser igual al número de columnas en la sentencia SELECT del cursor; asegúrese de que los tipos de datos sean compatibles y que estén en el orden correcto. Alternativamente, también puede definir un récord para el cursor y referenciarlo en la cláusula INTO del FETCH.
Sentencia CLOSE.
La sentencia CLOSE desactiva el cursor, libera el área de contexto, y "in-define" el active set. Es recomendable siempre cerrar el cursor después de completar el proceso. Si es necesario, puede volver a abrir el cursor, pero tenga en cuenta que un cursor puede volver a abrir solamente si está cerrado. Si intenta obtener datos de un cursor cerrado, se produce la excepción: INVALID_CURSOR.
Nota: Aunque es posible terminar el bloque PL/SQL sin cerrar los cursores, es recomendable hacer hábito de cerrar cualquier cursor que se declara explícitamente, ya que esto libera recursos. Hay un límite máximo en el número de cursores abiertos por sesión, lo cual está determinado por el parámetro OPEN_CURSORS en el archivo de parámetros de Base de Datos. (Por defecto OPEN_CURSORS = 50).
Ejemplos:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name
FROM employees
WHERE employee_id = 111;
--
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN c_emp_cursor; --abre el cursor
FETCH c_emp_cursor INTO v_empno, v_lname; --asigna los valores a las variables.
CLOSE c_emp_cursor; --cierra el cursor.
--
DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname);
END;
/*Este es un ejemplo simple de un cursor, en el cual se define un cursor que extrae el código y apellido de un empleado; notar como son asignados los datos extraídos a las variables previamente declaradas.*/
---
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name
FROM employees
WHERE department_id = 30;
--
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN c_emp_cursor; --abre el cursor
LOOP
FETCH c_emp_cursor INTO v_empno, v_lname; --asigna los valores a las variables.
EXIT WHEN c_emp_cursor%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname);
END LOOP;
CLOSE c_emp_cursor; --cierra el cursor.
END;
/*Este ejemplo muestra como usar un cursor que retorne mas de una fila de resultados; notar como se itera el cursor con la ayuda de un LOOP simple.*/
---
Cursores y Records.
Ya ha visto que se puede definir records con la estructura de columnas de una tabla. También puede definir un records basado en la lista de columnas en el SELECT de un cursor explícito. Esto es conveniente para procesar las filas del active set, ya que solo tiene que especificar el nombre del record en la cláusula INTO de la sentencia FETCH, en lugar de un numero x de variable. Esto también asegura que los valores de las filas se cargaran directamente en los campos correspondientes del record.
Ejemplo:
____________________________________________________________________________________
Cursores FOR LOOP.
Un Cursor FOR LOOP procesa las filas de un cursor explícito. Se puede considerar como un atajo, esto porque se abre el cursor, una fila es extraída(FETCH) por cada iteración del LOOP, el LOOP termina cuando se procesa la última fila, y el cursor se cierra automáticamente. El LOOP en sí se termina automáticamente al final de la iteración, donde la última fila se recupera.
Sintaxis:
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
En la sintaxis:
• record_name: Es el nombre del record declarado implícitamente.
• cursor_name: Es el nombre del cursor declarado previamente.
Directrices:
• No declare el record que usara en el LOOP; ya que se declara de forma implícita.
• Puede usar los atributos de cursor durante el LOOP si es necesario.
• Suministrar los parámetros del cursor, si se requiere, entre paréntesis a continuación del nombre del cursor en la sentencia FOR.
Ejemplo:
--
DBMS_OUTPUT.PUT_LINE('Nombre: '||i.last_name||', Salario: '||i.salary||
', Departamento: '||i.department_id);
END LOOP;
____________________________________________________________________________________
Atributos de los Cursores Explícitos.
Al igual que con los cursores implícitos, hay cuatro atributos que proveen información referente al estado de un cursor. Una vez adjunto al nombre del cursor, estos atributos devuelven información útil acerca de la ejecución del cursor.
Nota: No puede hacer referencia a los atributos de un cursor directamente en una sentencia SQL.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_details IS
SELECT
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario,
d.department_name AS departamento,
j.job_title AS empleo,
m.first_name||' '||m.last_name AS supervisor,
l.city AS ciudad
FROM employees e, employees m, departments d, locations l, jobs j
WHERE NVL(e.manager_id, 0) = m.employee_id
AND e.job_id = j.job_id
AND e.department_id = d.department_id
AND d.location_id = l.location_id;
--
v_emp_details_rec c_emp_details%ROWTYPE;
BEGIN
IF NOT c_emp_details%ISOPEN THEN
OPEN c_emp_details;
DBMS_OUTPUT.PUT_LINE('Cursor Abierto');
END IF;
--
LOOP
FETCH c_emp_details INTO v_emp_details_rec;
EXIT WHEN c_emp_details%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE('FETCH #: '||c_emp_details%ROWCOUNT);
--
DBMS_OUTPUT.PUT_LINE(
'Nombre: '||v_emp_details_rec.nombre||CHR(10)||
'Salario: '||v_emp_details_rec.salario||CHR(10)||
'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
'Empleo: '||v_emp_details_rec.empleo||CHR(10)||
'Supervisor: '||v_emp_details_rec.supervisor||CHR(10)||
'Ciudad: '||v_emp_details_rec.ciudad
);
END LOOP;
--
IF c_emp_details%ISOPEN THEN
CLOSE c_emp_details;
DBMS_OUTPUT.PUT_LINE('Cursor Cerrado');
END IF;
END;
Manejando Cursores Explícitos.
Ahora que tiene una comprensión conceptual de los cursores, revise los pasos para utilizarlos.
2. Abra el cursor.
La sentencia OPEN ejecuta la consulta y vincula las variables que se hayan referenciado. Las filas identificadas por la consulta(active set) ahora están disponibles para el FETCH.
3. Obtener(FETCH) los datos del cursor.
En el diagrama de flujo anterior: Luego de cada búsqueda(FETCH), se prueba la existencia de filas en el cursor. Si no hay más filas para procesar, se debe cerrar el cursor.
4. Cierre el cursor.
La sentencia CLOSE libera el active set de filas. Ahora es posible volver a abrir el cursor para establecer un nuevo active set.
Detalles:
1. La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el cursor en la primera fila.
2. La sentencia FETCH recupera la fila actual y avanza el cursor a la siguiente hasta que no haya más filas o hasta que cierta condición se cumpla.
3. La sentencia CLOSE libera el cursor.
Sintaxis:
CURSOR cursor_name IS
select_statement;
En la Sintaxis:
• cursor_name: Es un identificador PL/SQL.
• select_statement: Es una sentencia SELECT sin la cláusula INTO.
En un cursor el active set está determinado por el SELECT al momento de su declaración. En PL/SQL es obligatorio tener una cláusula INTO al usar una sentencia SELECT. Sin embargo, tenga en cuenta que la sentencia SELECT en la declaración de cursor no puede tener dicha cláusula. Esto se debe a que sólo se está definiendo el cursor en la parte declarativa, no recuperando su filas.
Notas:
• No incluya la cláusula INTO en la declaración del cursor, ya que está se usa más adelante en la sentencia FETCH.
• Si desea que las filas sean procesadas en algún orden especifico, utilice la cláusula ORDER BY en la consulta.
• El cursor puede ser cualquier sentencia SELECT válida, incluyendo JOINs, UNIONs, subconsultas, y así sucesivamente.
Ejemplos:
DECLARE
v_dept NUMBER := 30;
CURSOR c_emps IS
SELECT employee_id,
last_name,
salary,
job_id
FROM employees
WHERE department_id = v_dept;
---
CURSOR c_dept_sal IS
SELECT d.department_name,
SUM(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
/*El ejemplo muestra la declaración de dos cursores: v_dept y c_dept_sal; c_emps consulta algunas informaciones de los empleados del departamento que contenga la variable v_dept, que en este caso es 30; c_dept_sal consulta la suma de salarios de los empleados de cada departamento.*/
---
Sentencia OPEN.
La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el puntero en la primera fila del cursor. La sentencia OPEN se incluye en la sección ejecutable del bloque PL/SQL.
OPEN es una sentencia ejecutable que realiza las siguientes operaciones:
1. Dinámicamente asigna la memoria para un área de contexto.
2. Analiza la sentencia SELECT.
3. Vincula las variables de entrada (establece los valores de las variables de entrada mediante la obtención de sus direcciones de memoria).
4. Identifica el conjunto activo (el conjunto de filas que satisfacen los criterios de búsqueda). Las filas en el active set no son asignadas a variables cuando se ejecuta la sentencia OPEN. Más bien, la sentencia FETCH es la que hace ese trabajo.
5. Sitúa el puntero a la primera fila del active set.
Nota: Si una consulta no devuelve ninguna fila cuando se abre el cursor, PL/SQL no lanza una excepción. Puede averiguar el número de filas devueltas por un cursor explícito mediante el uso del atributo <cursor_name>%ROWCOUNT.
Sentencia FETCH.
La sentencia FETCH recupera las filas del cursor de una en una. Después de cada FETCH, el cursor avanza a la siguiente fila del active set. Puede utilizar el atributo %NOTFOUND para determinar si todo el active set se ha recuperado.
La sentencia FETCH realiza las siguientes operaciones:
1. Lee y asigna los datos de la fila actual a las variables de PL/SQL.
2. Avanza el puntero a la siguiente fila del active set.
El número de variables en la cláusula INTO de la sentencia FETCH debe ser igual al número de columnas en la sentencia SELECT del cursor; asegúrese de que los tipos de datos sean compatibles y que estén en el orden correcto. Alternativamente, también puede definir un récord para el cursor y referenciarlo en la cláusula INTO del FETCH.
Sentencia CLOSE.
La sentencia CLOSE desactiva el cursor, libera el área de contexto, y "in-define" el active set. Es recomendable siempre cerrar el cursor después de completar el proceso. Si es necesario, puede volver a abrir el cursor, pero tenga en cuenta que un cursor puede volver a abrir solamente si está cerrado. Si intenta obtener datos de un cursor cerrado, se produce la excepción: INVALID_CURSOR.
Nota: Aunque es posible terminar el bloque PL/SQL sin cerrar los cursores, es recomendable hacer hábito de cerrar cualquier cursor que se declara explícitamente, ya que esto libera recursos. Hay un límite máximo en el número de cursores abiertos por sesión, lo cual está determinado por el parámetro OPEN_CURSORS en el archivo de parámetros de Base de Datos. (Por defecto OPEN_CURSORS = 50).
Ejemplos:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name
FROM employees
WHERE employee_id = 111;
--
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN c_emp_cursor; --abre el cursor
FETCH c_emp_cursor INTO v_empno, v_lname; --asigna los valores a las variables.
CLOSE c_emp_cursor; --cierra el cursor.
--
DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname);
END;
/*Este es un ejemplo simple de un cursor, en el cual se define un cursor que extrae el código y apellido de un empleado; notar como son asignados los datos extraídos a las variables previamente declaradas.*/
---
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name
FROM employees
WHERE department_id = 30;
--
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN c_emp_cursor; --abre el cursor
LOOP
FETCH c_emp_cursor INTO v_empno, v_lname; --asigna los valores a las variables.
EXIT WHEN c_emp_cursor%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname);
END LOOP;
CLOSE c_emp_cursor; --cierra el cursor.
END;
/*Este ejemplo muestra como usar un cursor que retorne mas de una fila de resultados; notar como se itera el cursor con la ayuda de un LOOP simple.*/
---
Cursores y Records.
Ya ha visto que se puede definir records con la estructura de columnas de una tabla. También puede definir un records basado en la lista de columnas en el SELECT de un cursor explícito. Esto es conveniente para procesar las filas del active set, ya que solo tiene que especificar el nombre del record en la cláusula INTO de la sentencia FETCH, en lugar de un numero x de variable. Esto también asegura que los valores de las filas se cargaran directamente en los campos correspondientes del record.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name,
salary,
department_id,
manager_id
FROM employees
WHERE department_id = 30;
--
v_emp_rec c_emp_cursor%ROWTYPE;
BEGIN
OPEN c_emp_cursor; --abre el cursor
LOOP
FETCH c_emp_cursor INTO v_emp_rec; --asigna los valores a las variables.
EXIT WHEN c_emp_cursor%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE('Nombre: '||v_emp_rec.last_name||', Salario: '||v_emp_rec.salary||
', Departamento: '||v_emp_rec.department_id);
END LOOP;
CLOSE c_emp_cursor; --cierra el cursor.
END;
/*Modificamos el último ejemplo agregandole mas columnas; notar como el record basado en el cursor simplifica el código, evitando tener que declarar tantas variables como columnas en el SELECT.*/____________________________________________________________________________________
Cursores FOR LOOP.
Un Cursor FOR LOOP procesa las filas de un cursor explícito. Se puede considerar como un atajo, esto porque se abre el cursor, una fila es extraída(FETCH) por cada iteración del LOOP, el LOOP termina cuando se procesa la última fila, y el cursor se cierra automáticamente. El LOOP en sí se termina automáticamente al final de la iteración, donde la última fila se recupera.
Sintaxis:
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
En la sintaxis:
• record_name: Es el nombre del record declarado implícitamente.
• cursor_name: Es el nombre del cursor declarado previamente.
Directrices:
• No declare el record que usara en el LOOP; ya que se declara de forma implícita.
• Puede usar los atributos de cursor durante el LOOP si es necesario.
• Suministrar los parámetros del cursor, si se requiere, entre paréntesis a continuación del nombre del cursor en la sentencia FOR.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor IS
SELECT
employee_id,
last_name,
salary,
department_id,
manager_id
FROM employees
WHERE department_id = 30;
--
v_emp_rec c_emp_cursor%ROWTYPE;
BEGIN
FOR i IN c_emp_cursor LOOP--
DBMS_OUTPUT.PUT_LINE('Nombre: '||i.last_name||', Salario: '||i.salary||
', Departamento: '||i.department_id);
END LOOP;
END;
/*El mismo ejemplo ahora usando un FOR LOOP; Notar como se simplifica el código aun mas ya que no hay necesidad de abrir, hacer FETCH ni cerrar el cursor.*/____________________________________________________________________________________
Atributos de los Cursores Explícitos.
Atributo
|
Tipo
|
Descripción
|
%ISOPEN
|
Boolean
|
Retorna TRUE si el cursor está abierto.
|
%NOTFOUND
|
Boolean
|
Retorna TRUE si el último FETCH no trajo una fila.
|
%FOUND
|
Boolean
|
Retorna TRUE si el último FETCH trajo una fila; opuesto a %NOTFOUND.
|
%ROWCOUNT
|
Number
|
Cantidad de filas que han sido extraídas(FETCH).
|
Nota: No puede hacer referencia a los atributos de un cursor directamente en una sentencia SQL.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_details IS
SELECT
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario,
d.department_name AS departamento,
j.job_title AS empleo,
m.first_name||' '||m.last_name AS supervisor,
l.city AS ciudad
FROM employees e, employees m, departments d, locations l, jobs j
WHERE NVL(e.manager_id, 0) = m.employee_id
AND e.job_id = j.job_id
AND e.department_id = d.department_id
AND d.location_id = l.location_id;
--
v_emp_details_rec c_emp_details%ROWTYPE;
BEGIN
IF NOT c_emp_details%ISOPEN THEN
OPEN c_emp_details;
DBMS_OUTPUT.PUT_LINE('Cursor Abierto');
END IF;
--
LOOP
FETCH c_emp_details INTO v_emp_details_rec;
EXIT WHEN c_emp_details%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE('FETCH #: '||c_emp_details%ROWCOUNT);
--
DBMS_OUTPUT.PUT_LINE(
'Nombre: '||v_emp_details_rec.nombre||CHR(10)||
'Salario: '||v_emp_details_rec.salario||CHR(10)||
'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
'Empleo: '||v_emp_details_rec.empleo||CHR(10)||
'Supervisor: '||v_emp_details_rec.supervisor||CHR(10)||
'Ciudad: '||v_emp_details_rec.ciudad
);
END LOOP;
--
IF c_emp_details%ISOPEN THEN
CLOSE c_emp_details;
DBMS_OUTPUT.PUT_LINE('Cursor Cerrado');
END IF;
END;
/*En el ejemplo creamos un cursor que contiene informaciones de los empleados y un record basado en ese cursor; notar el uso de los atributos del cursor en la sección de ejecución del bloque.*/
---
Cursores FOR LOOP con Subconsultas.
La diferencia entre un cursor FOR LOOP con una subconsulta y un cursor FOR LOOP se encuentra en la declaración de cursor. Si desea usar un cursor FOR LOOP con una subconsulta, no es necesario declarar el cursor en la parte declarativa. Usted tiene que proporcionar la sentencia SELECT que determina el active set en el mismo LOOP.
Nota: No se puede hacer referencia a los atributos del cursor FOR LOOP con una subconsulta, esto debido a que se desconoce el nombre del mismo.
Ejemplo:
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (
SELECT
employee_id,
last_name
FROM employees
WHERE department_id = 30
)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id||' '||emp_record.last_name);
END LOOP;
END;
/*En el ejemplo un cursor FOR LOOP con una subconsulta; notar como este bloque no necesita la seccion declarativa.*/
---
Cursores Con Parámetros.
Puede pasar parámetros a un cursor. Esto significa que puede abrir y cerrar un cursor varias veces y retornar un active set diferente en cada ocasión. Para cada ejecución, el cursor es cerrado y abierto de nuevo con un conjunto de valores diferentes.
Los tipos de datos de los parámetros son los mismos que para las variables escalares, con la diferencia de que al parámetro no se le especifica la precisión(tamaño). Los nombres de los parámetros son para referencia en la consulta del cursor.
Sintaxis:
CURSOR cursor_name[(parameter_name datatype, ...)]
IS
select_statement;
--
OPEN cursor_name(parameter_value,.....);
--
FOR record_name IN cursor_name(parameter_value,.....) LOOP
En la sintaxis:
La notación de parámetro no ofrece mejor funcionalidad; simplemente le permite especificar valores de entrada de forma fácil y clara. Esto es particularmente útil cuando se hace referencia repetidamente del mismo cursor.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_details(p_emp_id NUMBER) IS
SELECT
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario,
d.department_name AS departamento,
l.city AS ciudad
FROM employees e, departments d, locations l
WHERE e.employee_id = p_emp_id
AND e.department_id = d.department_id
AND d.location_id = l.location_id;
--
CURSOR c_job_dept_details(p_dept_id NUMBER, p_job_id VARCHAR2) IS
SELECT
d.department_name AS departamento,
j.job_title AS empleo,
COUNT(e.employee_id) AS emp_count,
SUM(e.salary) AS total_sal
FROM employees e, departments d, jobs j
WHERE d.department_id = p_dept_id
AND j.job_id = p_job_id
AND e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY d.department_name, j.job_title;
--
v_emp_details_rec c_emp_details%ROWTYPE;
BEGIN
OPEN c_emp_details(121);
FETCH c_emp_details INTO v_emp_details_rec;
CLOSE c_emp_details;
--
DBMS_OUTPUT.PUT_LINE('Primer Cursor');
DBMS_OUTPUT.PUT_LINE('Empleado: '||v_emp_details_rec.nombre||CHR(10)||
'Salario: '||v_emp_details_rec.salario||CHR(10)||
'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
'Ciudad: '||v_emp_details_rec.ciudad);
--
DBMS_OUTPUT.PUT_LINE('Segundo Cursor');
--
FOR i IN c_job_dept_details(50,'ST_MAN') LOOP
--
DBMS_OUTPUT.PUT_LINE('Departamento: '||i.departamento||CHR(10)||
'Empleo: '||i.empleo||CHR(10)||
'Cantidad de Empleados: '||i.emp_count||CHR(10)||
'Salario Total: '||i.total_sal);
END LOOP;
END;
/*El ejemplo muestra el uso de dos cursores con parámetro, el primero recibe el numero de empleado y retorna ciertas informaciones del mismo; el segundo recibe el código de departamento y el código de empleo y retorna la cantidad de empleados y la suma de salarios. Tenga en cuenta que estos cursores pueden ser usados repetidamente en el mismo bloque con parámetros distintos para así generar diferentes resultados.*/
____________________________________________________________________________________
Referenciar y Bloquear la Actual Fila del Cursor.
Cláusula FOR UPDATE.
Nota: No se puede hacer referencia a los atributos del cursor FOR LOOP con una subconsulta, esto debido a que se desconoce el nombre del mismo.
Ejemplo:
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (
SELECT
employee_id,
last_name
FROM employees
WHERE department_id = 30
)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id||' '||emp_record.last_name);
END LOOP;
END;
/*En el ejemplo un cursor FOR LOOP con una subconsulta; notar como este bloque no necesita la seccion declarativa.*/
---
Cursores Con Parámetros.
Puede pasar parámetros a un cursor. Esto significa que puede abrir y cerrar un cursor varias veces y retornar un active set diferente en cada ocasión. Para cada ejecución, el cursor es cerrado y abierto de nuevo con un conjunto de valores diferentes.
Los tipos de datos de los parámetros son los mismos que para las variables escalares, con la diferencia de que al parámetro no se le especifica la precisión(tamaño). Los nombres de los parámetros son para referencia en la consulta del cursor.
Sintaxis:
CURSOR cursor_name[(parameter_name datatype, ...)]
IS
select_statement;
--
OPEN cursor_name(parameter_value,.....);
--
FOR record_name IN cursor_name(parameter_value,.....) LOOP
En la sintaxis:
cursor_name
|
Es un identificador del cursor.
|
parameter_name
|
Es el nombre del parámetro.
|
datatype
|
Es el tipo de dato del parámetro.
|
select_statement
|
Es la sentencia SELECT.
|
La notación de parámetro no ofrece mejor funcionalidad; simplemente le permite especificar valores de entrada de forma fácil y clara. Esto es particularmente útil cuando se hace referencia repetidamente del mismo cursor.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_details(p_emp_id NUMBER) IS
SELECT
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario,
d.department_name AS departamento,
l.city AS ciudad
FROM employees e, departments d, locations l
WHERE e.employee_id = p_emp_id
AND e.department_id = d.department_id
AND d.location_id = l.location_id;
--
CURSOR c_job_dept_details(p_dept_id NUMBER, p_job_id VARCHAR2) IS
SELECT
d.department_name AS departamento,
j.job_title AS empleo,
COUNT(e.employee_id) AS emp_count,
SUM(e.salary) AS total_sal
FROM employees e, departments d, jobs j
WHERE d.department_id = p_dept_id
AND j.job_id = p_job_id
AND e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY d.department_name, j.job_title;
--
v_emp_details_rec c_emp_details%ROWTYPE;
BEGIN
OPEN c_emp_details(121);
FETCH c_emp_details INTO v_emp_details_rec;
CLOSE c_emp_details;
--
DBMS_OUTPUT.PUT_LINE('Primer Cursor');
DBMS_OUTPUT.PUT_LINE('Empleado: '||v_emp_details_rec.nombre||CHR(10)||
'Salario: '||v_emp_details_rec.salario||CHR(10)||
'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
'Ciudad: '||v_emp_details_rec.ciudad);
--
DBMS_OUTPUT.PUT_LINE('Segundo Cursor');
--
FOR i IN c_job_dept_details(50,'ST_MAN') LOOP
--
DBMS_OUTPUT.PUT_LINE('Departamento: '||i.departamento||CHR(10)||
'Empleo: '||i.empleo||CHR(10)||
'Cantidad de Empleados: '||i.emp_count||CHR(10)||
'Salario Total: '||i.total_sal);
END LOOP;
END;
/*El ejemplo muestra el uso de dos cursores con parámetro, el primero recibe el numero de empleado y retorna ciertas informaciones del mismo; el segundo recibe el código de departamento y el código de empleo y retorna la cantidad de empleados y la suma de salarios. Tenga en cuenta que estos cursores pueden ser usados repetidamente en el mismo bloque con parámetros distintos para así generar diferentes resultados.*/
____________________________________________________________________________________
Referenciar y Bloquear la Actual Fila del Cursor.
Cláusula FOR UPDATE.
Si hay varias sesiones en una Base de Datos, existe la posibilidad de que las filas de una tabla en particular se actualizan después de haber abierto un cursor. De modo que usted ve los datos actualizados sólo cuando vuelve a abrir el cursor. Por lo tanto, es mejor bloquear las filas antes de actualizarlas o eliminarlas. Es posible bloquear las filas con la cláusula FOR UPDATE en la consulta del cursor.
Sintaxis:
SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT | WAIT n];
En la sintaxis:
column_reference
|
Es una columna de la tabla en la que se realiza la consulta (también se puede utilizar una lista de columnas).
|
NOWAIT
|
Devuelve un error de Oracle si las filas están bloqueadas por otra sesión.
|
La cláusula FOR UPDATE es la última en una sentencia SELECT, incluso después de ORDER BY (si existe). Cuando se desea consultar varias tablas, puede utilizar la cláusula FOR UPDATE para confinar el bloqueo de filas en tablas particulares. FOR UPDATE OF col_name(s) bloquea filas sólo en las tablas que contienen col_name(s).
La sentencia SELECT ... FOR UPDATE identifica las filas que se van a actualizar o eliminar y, a continuación, bloquea cada fila del active set. Esto es útil cuando se desea realizar una actualización en los valores existentes en una fila. En ese caso, se debe asegurar de que la fila no sea cambiada por otra sesión antes de la actualización.
La palabra clave NOWAIT(opcional) le indica al Servidor Oracle no esperar si las filas seleccionadas han sido bloqueadas por otro usuario. En caso de que hayan sido bloqueadas, el control vuelve inmediatamente a tu programa de modo que pueda hacer otro trabajo antes de volver a intentar adquirir el bloqueo. Si se omite la palabra clave NOWAIT, el servidor Oracle espera hasta que las filas estén disponibles.
Ejemplo:
DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80
FOR UPDATE OF salary NOWAIT;
Si el servidor de Oracle no puede adquirir el bloqueo de las filas que necesita en una operación SELECT FOR UPDATE, se espera indefinidamente. Utilizar NOWAIT para manejar este tipo de situaciones. Si las filas están bloqueados por otra sesión y se ha especificado NOWAIT, al abrir el cursor se produce un error. Se puede utilizar WAIT en lugar de NOWAIT, y si usa WAIT puede especificar el número de segundos de espera. Si luego de haber transcurrido los n segundos las filas continúan bloqueadas, se devuelve un error.
No es obligatorio especificar una columna en la cláusula FOR UPDATE OF, pero se recomienda para una mejor legibilidad y mantenimiento.
Cláusula WHERE CURRENT OF.
• Usar cursores para actualizar o eliminar la fila actual.
• Incluir la cláusula FOR UPDATE en la consulta de cursor para primero bloquear la fila.
• Utilice la cláusula WHERE CURRENT OF para hacer referencia a la fila actual de un cursor explícito.
La cláusula WHERE CURRENT OF se utiliza en conjunto con la cláusula FOR UPDATE para referirse a la fila actual de un cursor explícito. La cláusula WHERE CURRENT OF se utiliza en las sentencias UPDATE o DELETE; primero es necesario especificar la cláusula FOR UPDATE en la declaración del cursor. Se puede utilizar la combinación de actualización y supresión de un fila actual de la tabla de Base de Datos correspondiente. Esto le permite aplicar las actualizaciones y eliminar la fila que en cuestión, sin la necesidad de hacer referencia explícita del ID de la misma.
Sintaxis:
UPDATE table_name
SET column_name = ...
WHERE CURRENT OF cursor_name;
--
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
Ejemplo:
DECLARE
CURSOR c_emp IS
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE OF salary WAIT 180;
v_emp_rec c_emp%ROWTYPE;
BEGIN
/*Este SCRIPT tratara de bloquear para luego actualizar el salario del registro con el employee_id = 100 de la tabla employees, en caso de ya estar bloqueado por otro usuario, el bloque esperaría máximo 3 minutos para que se libere dicho registro, de no ser bloqueado en este tiempo un error se produciría.*/
CURSOR c_emp IS
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE OF salary WAIT 180;
v_emp_rec c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_emp_rec;
FETCH c_emp INTO v_emp_rec;
--
UPDATE employees
SET salary = 30000
WHERE CURRENT OF c_emp;
COMMIT;
--
CLOSE c_emp;
END;/*Este SCRIPT tratara de bloquear para luego actualizar el salario del registro con el employee_id = 100 de la tabla employees, en caso de ya estar bloqueado por otro usuario, el bloque esperaría máximo 3 minutos para que se libere dicho registro, de no ser bloqueado en este tiempo un error se produciría.*/
