En este capítulo, discutiremos los cursores en PL / SQL. Oracle crea un área de memoria, conocida como área de contexto, para procesar una declaración SQL, que contiene toda la información necesaria para procesar la declaración; por ejemplo, el número de filas procesadas, etc.
A cursor es un puntero a esta área de contexto. PL / SQL controla el área de contexto a través de un cursor. Un cursor contiene las filas (una o más) devueltas por una instrucción SQL. El conjunto de filas que contiene el cursor se conoce como conjunto activo.
Puede nombrar un cursor para que se pueda hacer referencia a él en un programa para obtener y procesar las filas devueltas por la instrucción SQL, una a la vez. Hay dos tipos de cursores:
- Cursores implícitos
- Cursores explícitos
Cursores implícitos
Oracle crea automáticamente cursores implícitos cada vez que se ejecuta una instrucción SQL, cuando no hay un cursor explícito para la instrucción. Los programadores no pueden controlar los cursores implícitos y la información que contienen.
Siempre que se emite una instrucción DML (INSERT, UPDATE y DELETE), se asocia un cursor implícito con esta instrucción. Para las operaciones INSERT, el cursor contiene los datos que deben insertarse. Para las operaciones UPDATE y DELETE, el cursor identifica las filas que se verían afectadas.
En PL / SQL, puede hacer referencia al cursor implícito más reciente como el Cursor SQL, que siempre tiene atributos como % ENCONTRADO,% ESTADO ABIERTO,% NO ENCONTRADO, y %NÚMERO DE FILAS. El cursor SQL tiene atributos adicionales, % BULK_ROWCOUNT y % BULK_EXCEPTIONS, diseñado para su uso con el PARA TODOS declaración. La siguiente tabla proporciona la descripción de los atributos más utilizados:
S. No | Atributo y descripción |
---|---|
1 |
%FUNDAR Devuelve VERDADERO si una instrucción INSERT, UPDATE o DELETE afectó a una o más filas o una instrucción SELECT INTO devolvió una o más filas. De lo contrario, devuelve FALSE. |
2 |
%EXTRAVIADO El opuesto lógico de% FOUND. Devuelve VERDADERO si una instrucción INSERT, UPDATE o DELETE no afectó filas, o una instrucción SELECT INTO no devolvió filas. De lo contrario, devuelve FALSE. |
3 |
%ESTA ABIERTO Siempre devuelve FALSE para cursores implícitos, porque Oracle cierra el cursor SQL automáticamente después de ejecutar su declaración SQL asociada. |
4 |
%NÚMERO DE FILAS Devuelve el número de filas afectadas por una instrucción INSERT, UPDATE o DELETE, o devuelto por una instrucción SELECT INTO. |
Se accederá a cualquier atributo de cursor SQL como sql% nombre_atributo como se muestra a continuación en el ejemplo.
Ejemplo
Usaremos la tabla CLIENTES que habíamos creado y usado en los capítulos anteriores.
Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+
El siguiente programa actualizará la tabla y aumentará el salario de cada cliente en 500 y utilizará el SQL% ROWCOUNT atributo para determinar el número de filas afectadas –
DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 500; IF sql%notfound THEN dbms_output.put_line('no customers selected'); ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ' customers selected '); END IF; END; /
Cuando el código anterior se ejecuta en el indicador de SQL, produce el siguiente resultado:
6 customers selected PL/SQL procedure successfully completed.
Si comprueba los registros en la tabla de clientes, encontrará que las filas se han actualizado:
Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2500.00 | | 2 | Khilan | 25 | Delhi | 2000.00 | | 3 | kaushik | 23 | Kota | 2500.00 | | 4 | Chaitali | 25 | Mumbai | 7000.00 | | 5 | Hardik | 27 | Bhopal | 9000.00 | | 6 | Komal | 22 | MP | 5000.00 | +----+----------+-----+-----------+----------+
Cursores explícitos
Los cursores explícitos son cursores definidos por el programador para obtener más control sobre el área de contexto. Se debe definir un cursor explícito en la sección de declaración del bloque PL / SQL. Se crea en una instrucción SELECT que devuelve más de una fila.
La sintaxis para crear un cursor explícito es:
CURSOR cursor_name IS select_statement;
Trabajar con un cursor explícito incluye los siguientes pasos:
- Declarar el cursor para inicializar la memoria
- Abrir el cursor para asignar la memoria
- Obteniendo el cursor para recuperar los datos
- Cerrar el cursor para liberar la memoria asignada
Declarar el cursor
Declarar el cursor define el cursor con un nombre y la instrucción SELECT asociada. Por ejemplo
CURSOR c_customers IS SELECT id, name, address FROM customers;
Abrir el cursor
Abrir el cursor asigna la memoria para el cursor y lo prepara para recuperar las filas devueltas por la instrucción SQL en él. Por ejemplo, abriremos el cursor definido anteriormente de la siguiente manera:
OPEN c_customers;
Obteniendo el cursor
Obtener el cursor implica acceder a una fila a la vez. Por ejemplo, buscaremos filas del cursor abierto anteriormente de la siguiente manera:
FETCH c_customers INTO c_id, c_name, c_addr;
Cerrar el cursor
Cerrar el cursor significa liberar la memoria asignada. Por ejemplo, cerraremos el cursor abierto anteriormente de la siguiente manera:
CLOSE c_customers;
Ejemplo
A continuación se muestra un ejemplo completo para ilustrar los conceptos de cursores explícitos & minua;
DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; /
Cuando el código anterior se ejecuta en el indicador de SQL, produce el siguiente resultado:
1 Ramesh Ahmedabad 2 Khilan Delhi 3 kaushik Kota 4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal MP PL/SQL procedure successfully completed.