EJEMPLOS DE PL/SQL

Ejemplos de copia de funciones PL/SQL

Última actualización: 2021-03-17

Estos ejemplos muestran el DDL que se genera al copiar funciones PL/SQL entre bases de datos heterogéneas.

El DDL generado se muestra en la página Vista previa de DDL del asistente Pegar objetos de base de datos.

La Tabla 1 muestra ejemplos del DDL que se genera para copiar funciones PL/SQL de una base de datos de origen Oracle Database 11g en una base
de datos de destino DB2 Versión 9.7 para Linux, UNIX y Windows.

Definición de función PL/SQL de Oracle Database 11g

Tabla 1. Ejemplos de copia de funciones PL/SQL de Oracle Database 11g en una base de datos DB2 Versión 9.7 para Linux, UNIX y Windows

Definición de función PL/SQL de Oracle Database 11g DDL generado para la base de datos DB2 Versión 9.7
CREATE OR REPLACE FUNCTION func_authid2
CREATE FUNCTION func_authid2
RETURN NUMBER
RETURN NUMBER
AUTHID DEFINED
AUTHID DEFINED
AS
AS
x NUMBER(9,2);
x NUMBER(9,2);
BEGIN
BEGIN
x := 65400;
x := 65400;
RETURN x;
RETURN x;
END;
END func_authid2;
/
CREATE OR REPLACE FUNCTION func_cursr
CREATE FUNCTION func_cursr (
( p_job VARCHAR2 )
p_job VARCHAR2 )
RETURN SYS_REFCURSOR
RETURN SYS_REFCURSOR
IS
IS
emp_refcur SYS_REFCURSOR;
emp_refcur SYS_REFCURSOR;
BEGIN
BEGIN
OPEN emp_refcur FOR
OPEN emp_refcur FOR
SELECT empno, ename
SELECT empno, ename
FROM emp
FROM emp
WHERE job = p_job;
WHERE job = p_job;
RETURN emp_refcur;
RETURN emp_refcur;
END;
END func_cursr;
/
CREATE OR REPLACE FUNCTION func_cursr2
CREATE FUNCTION func_cursr2 (
( p_job VARCHAR2,
p_job VARCHAR2,
c SYS_REFCURSOR )
c SYS_REFCURSOR )
RETURN SYS_REFCURSOR
RETURN SYS_REFCURSOR
IS
IS
emp_refcur SYS_REFCURSOR;
emp_refcur SYS_REFCURSOR;
BEGIN
BEGIN
OPEN emp_refcur
OPEN emp_refcur
FOR SELECT empno, ename
FOR SELECT empno, ename
FROM emp
FROM emp
WHERE job = p_job;
WHERE job = p_job;
RETURN emp_refcur;
RETURN emp_refcur;
END;
END func_cursr2;
/
CREATE OR REPLACE FUNCTION func_inout CREATE FUNCTION func_inout (
( p_job IN VARCHAR2, p_job IN VARCHAR2,
p_job2 OUT VARCHAR2, p_job2 OUT VARCHAR2,
p_job3 IN OUT VARCHAR2 ) p_job3 IN OUT VARCHAR2 )
RETURN SYS_REFCURSOR RETURN SYS_REFCURSOR
IS IS
emp_refcur SYS_REFCURSOR; emp_refcur SYS_REFCURSOR;
BEGIN BEGIN
OPEN emp_refcur OPEN emp_refcur
FOR SELECT empno, ename FOR SELECT empno, ename
FROM emp FROM emp
WHERE job = p_job; WHERE job = p_job;
RETURN emp_refcur; RETURN emp_refcur;
END; END func_inout;
 Definición de función PL/SQL de Oracle Database 11g DDL generado para la base de datos DB2 Versión 9.7
/
CREATE OR REPLACE FUNCTION func_inout2
CREATE FUNCTION func_inout2 (
( p_job IN VARCHAR2,
p_job IN VARCHAR2,
p_job2 OUT NOCOPY VARCHAR2,
p_job2 OUT NOCOPY VARCHAR2,
p_job3 IN OUT VARCHAR2 )
p_job3 IN OUT VARCHAR2 )
RETURN SYS_REFCURSOR
RETURN SYS_REFCURSOR
IS
IS
emp_refcur SYS_REFCURSOR;
emp_refcur SYS_REFCURSOR;
BEGIN
BEGIN
OPEN emp_refcur
OPEN emp_refcur
FOR SELECT empno, ename
FOR SELECT empno, ename
FROM emp
FROM emp
WHERE job = p_job;
WHERE job = p_job;
RETURN emp_refcur;
RETURN emp_refcur;
END;
END func_inout2;
/
CREATE FUNCTION sal_ok2
CREATE FUNCTION sal_ok2 )
( salary NUMBER,
salary NUMBER,
title VARCHAR2 )
title VARCHAR2 )
RETURN BOOLEAN
RETURN BOOLEAN
IS
IS
min_sal NUMBER;
min_sal NUMBER;
max_sal NUMBER;
max_sal NUMBER;
-- AUTHID CURRENT_USER
-- AUTHID CURRENT_USER
-- PARALLEL_ENABLE
-- PARALLEL_ENABLE
BEGIN
BEGIN
SELECT losal, hisal
SELECT losal, hisal
INTO min_sal, max_sal
INTO min_sal, max_sal
FROM sals
FROM sals
WHERE job = title;
WHERE job = title;
RETURN (salary >= min_sal)
RETURN (salary >= min_sal)
AND (salary <= max_sal);
AND (salary <= max_sal);
END;
END sal_ok2;
/
Tabla 1. Ejemplos de copia de funciones PL/SQL de Oracle Database 11g en una base de datos DB2 Versión 9.7 para Linux, UNIX y Windows

La Tabla 2 muestra un ejemplo del DDL que se genera para copiar una función PL/SQL de una base de datos de origen DB2 Versión 9.7 para Linux,
UNIX y Windows en una base de datos de destino Oracle Database 11g.

Definición de función PL/SQL de la base de datos DB2 Versión 9.7 DDL generado para Oracle Database 11g
CREATE FUNCTION F047( CREATE FUNCTION F047
pID INTEGER, (pID INTEGER,
pFName CHAR, pFName CHAR,
pLNAME VARCHAR pLNAME VARCHAR
-- , pmyLONGVARCHAR LONG VARCHAR --, pmyLONGVARCHAR LONG VARCHAR
) )
RETURN CHAR RETURN CHAR
AS AS
BEGIN BEGIN
RETURN pFName; RETURN pFName;
END F047; END F047;
Tabla 2. Ejemplo de copia de una función PL/SQL de una base de datos DB2 Versión 9.7 para Linux, UNIX y Windows en Oracle Database 11g
Tema principal:
Ejemplos de copia de objetos de base de datos
Información relacionada:
Ejemplos de copia de objetos de base de datos
Terminologías en subprogramas PL/SQL
Antes de aprender sobre los subprogramas PL/SQL, discutiremos las diversas terminologías que forman parte de estos subprogramas. A
continuación se muestran las terminologías que vamos a discutir.

Parámetro
El parámetro es variable o marcador de posición de cualquier válido. tipo de datos PL/SQL a través del cual el subprograma PL/SQL intercambia
los valores con el código principal. Este parámetro permite dar entrada a los subprogramas y extraer de estos subprogramas.

 Estos parámetros deben definirse junto con los subprogramas en el momento de su creación.
 Estos parámetros se incluyen en la declaración de llamada de estos subprogramas para interactuar los valores con los subprogramas.
 El tipo de datos del parámetro en el subprograma y la declaración de llamada deben ser los mismos.
 El tamaño del tipo de datos no debe mencionarse en el momento de la declaración del parámetro, ya que el tamaño es dinámico para
este tipo.

Según su finalidad los parámetros se clasifican en

1. EN Parámetro
2. Parámetro de salida
3. Parámetro ENTRADA SALIDA

EN Parámetro

 Este parámetro se utiliza para dar entrada a los subprogramas.

 Es una variable de solo lectura dentro de los subprogramas. Sus valores no se pueden cambiar dentro del subprograma.
 En la declaración de llamada, estos parámetros pueden ser una variable, un valor literal o una expresión, por ejemplo, podría ser la
expresión aritmética como '5*8' o 'a/b' donde 'a' y 'b' son variables. .
 Por defecto, los parámetros son de tipo IN.
Parámetro de salida

 Este parámetro se utiliza para obtener resultados de los subprogramas.

 Es una variable de lectura-escritura dentro de los subprogramas. Sus valores se pueden cambiar dentro de los subprogramas.
 En la declaración de llamada, estos parámetros siempre deben ser una variable para contener el valor de los subprogramas actuales.

Parámetro ENTRADA SALIDA

 Este parámetro se utiliza tanto para dar entrada como para obtener salida de los subprogramas.
 Es una variable de lectura-escritura dentro de los subprogramas. Sus valores se pueden cambiar dentro de los subprogramas.
 En la declaración de llamada, estos parámetros siempre deben ser una variable para contener el valor de los subprogramas.

Este tipo de parámetro debe mencionarse al momento de crear los subprogramas.

DEVOLUCION
RETURN es la palabra clave que indica al compilador que cambie el control del subprograma a la declaración de llamada. En el subprograma
RETURN simplemente significa que el control necesita salir del subprograma. Una vez que el controlador encuentre la palabra clave RETURN en
el subprograma, se omitirá el código posterior.

Normalmente, el bloque padre o principal llamará a los subprogramas y luego el control shift desde esos bloques padres a los subprogramas
llamados. RETURN en el subprograma devolverá el control a su bloque principal. En el caso de funciones la sentencia RETURN también devuelve
el valor. El tipo de datos de este valor siempre se menciona en el momento de la declaración de la función. El tipo de datos puede ser cualquier
tipo de datos PL/SQL válido.

¿Qué es el procedimiento en PL/SQL?

A Procedimiento en PL/SQL es una unidad de subprograma que consta de un grupo de declaraciones PL/SQL que se pueden llamar por su
nombre. Cada procedimiento en PL/SQL tiene su propio nombre único mediante el cual se puede hacer referencia a él y llamarlo. Esta unidad
de subprograma en el Oracle database se almacena como un objeto de base de datos.
Nota: El subprograma no es más que un procedimiento y debe crearse manualmente según los requisitos. Una vez creados, se almacenarán
como objetos de base de datos.

A continuación se detallan las características de la unidad de subprograma de procedimiento en PL/SQL:

 Los procedimientos son bloques independientes de un programa que se pueden almacenar en el base de datos.
 La llamada a estos procedimientos PLSQL se puede realizar haciendo referencia a su nombre, para ejecutar las declaraciones PL/SQL.
 Se utiliza principalmente para ejecutar un proceso en PL/SQL.
 Puede tener bloques anidados o puede definirse y anidarse dentro de otros bloques o paquetes.
 Contiene parte de declaración (opcional), parte de ejecución y parte de manejo de excepciones (opcional).
 Los valores se pueden pasar a Oracle procedimiento o obtenido del procedimiento a través de parámetros.
 Estos parámetros deben incluirse en la declaración de llamada.
 Un procedimiento en SQL puede tener una declaración RETURN para devolver el control al bloque de llamada, pero no puede devolver
ningún valor a través de la declaración RETURN.
 Los procedimientos no se pueden llamar directamente desde declaraciones SELECT. Se pueden llamar desde otro bloque o mediante
palabra clave EXEC.

Sintaxis

CREATE OR REPLACE PROCEDURE

<procedure_name>
(
<parameterl IN/OUT <datatype>
..
.
)
[ IS | AS ]
 <declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

 CREATE PROCEDURE indica al compilador que cree un nuevo procedimiento en Oracle. La palabra clave 'OR REPLACE' indica a la
compilación que reemplace el procedimiento existente (si lo hay) por el actual.
 El nombre del procedimiento debe ser único.
 Se utilizará la palabra clave 'IS' cuando el procedimiento almacenado en Oracle está anidado en algunos otros bloques. Si el
procedimiento es independiente, se utilizará 'AS'. Aparte de este estándar de codificación, ambos tienen el mismo significado.

Ejemplo 1: crear un procedimiento y llamarlo usando EXEC

En este ejemplo, vamos a crear un Oracle procedimiento que toma el nombre como entrada e imprime el mensaje de bienvenida como salida.
Usaremos el comando EXEC para llamar al procedimiento.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)

IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);
Explicación del código:

 Línea de código 1: Creando el procedimiento con el nombre 'welcome_msg' y con un parámetro 'p_name' de tipo 'IN'.
 Línea de código 4: Imprimir el mensaje de bienvenida concatenando el nombre de entrada.
 El procedimiento se compila correctamente.
 Línea de código 7: Llamar al procedimiento mediante el comando EXEC con el parámetro 'Guru99'. Se ejecuta el procedimiento y se
imprime el mensaje "Bienvenido Guru99".

¿Qué es la función?
Functions es un subprograma PL/SQL independiente. Al igual que los procedimientos PL/SQL, las funciones tienen un nombre único mediante
el cual se puede hacer referencia a ellas. Estos se almacenan como objetos de base de datos PL/SQL. A continuación se muestran algunas de las
características de las funciones.

 Las funciones son un bloque independiente que se utiliza principalmente con fines de cálculo.
 La función utiliza la palabra clave RETURN para devolver el valor, y el tipo de datos de este se define en el momento de la creación.
 Una función debe devolver un valor o generar la excepción, es decir, la devolución es obligatoria en las funciones.
 La función sin declaraciones DML se puede llamar directamente en la consulta SELECT, mientras que la función con DML operación
sólo se puede llamar desde otros bloques PL/SQL.
 Puede tener bloques anidados o puede definirse y anidarse dentro de otros bloques o paquetes.
 Contiene parte de declaración (opcional), parte de ejecución y parte de manejo de excepciones (opcional).
 Los valores se pueden pasar a la función o recuperarse del procedimiento a través de los parámetros.
 Estos parámetros deben incluirse en la declaración de llamada.
 Una función PLSQL también puede devolver el valor a través de parámetros OUT además de usar RETURN.
 Dado que siempre devolverá el valor, al llamar a la declaración siempre acompaña con la asignación operator para completar las
variables.
Sintaxis

CREATE OR REPLACE FUNCTION

<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

 CREATE FUNCTION indica al compilador que cree una nueva función. La palabra clave 'O REEMPLAZAR' indica al compilador que
reemplace la función existente (si la hay) por la actual.
 El nombre de la función debe ser único.
 Se debe mencionar el tipo de datos RETURN.
 Se utilizará la palabra clave "IS" cuando el procedimiento esté anidado en otros bloques. Si el procedimiento es independiente, se
utilizará "AS". Aparte de este estándar de codificación, ambos tienen el mismo significado.

Ejemplo 1: crear una función y llamarla mediante un bloque anónimo

En este programa, vamos a crear una función que toma el nombre como entrada y devuelve el mensaje de bienvenida como salida. Usaremos
un bloque anónimo y una declaración de selección para llamar a la función.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN (‘Welcome ‘|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func (‘Guru99’);
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
Explicación del código:

 Línea de código 1: Creando el Oracle función con nombre 'welcome_msg_func' y con un parámetro 'p_name' de tipo 'IN'.
 Línea de código 2: declarar el tipo de retorno como VARCHAR2
 Línea de código 5: Devuelve el valor concatenado 'Bienvenido' y el valor del parámetro.
 Línea de código 8: Bloque anónimo para llamar a la función anterior.
 Línea de código 9: Declarar la variable con el mismo tipo de datos que el tipo de datos de retorno de la función.
 Línea de código 11: Llamar a la función y completar el valor de retorno de la variable 'lv_msg'.
 Línea de código 12: Imprimir el valor de la variable. El resultado que obtendrá aquí es "Bienvenido Guru99"
 Línea de código 14: Llamar a la misma función a través de la instrucción SELECT. El valor de retorno se dirige directamente a la salida
estándar.

Similitudes entre procedimiento y función

 Ambos pueden ser llamados desde otros bloques PL/SQL.

  Si la excepción planteada en el subprograma no se maneja en el subprograma manejo de excepciones sección, luego se propagará al
bloque de llamada.
 Ambos pueden tener tantos parámetros como sean necesarios.
 Ambos se tratan como objetos de base de datos en PL/SQL.

Procedimiento vs. Función: Diferencias clave

Procedimiento Función

Se utiliza principalmente para ejecutar cierto proceso. Se utiliza principalmente para realizar algún cálculo.

Se puede llamar a una función que no contiene declaraciones DML en

No se puede llamar a la declaración SELECT
declaración SELECT

Utilice el parámetro OUT para devolver el valor Utilice RETORNO para devolver el valor

No es obligatorio devolver el valor. Es obligatorio devolver el valor.

RETURN simplemente saldrá del control del subprograma. RETURN saldrá del control del subprograma y también devuelve el val

El tipo de datos devuelto no se especificará en el momento de la creación. El tipo de datos devuelto es obligatorio en el momento de la creación.

Funciones integradas en PL/SQL

PL / SQL contiene varias funciones integradas para trabajar con cadenas y tipos de datos de fecha. Aquí veremos las funciones más utilizadas y
su uso.

Funciones de conversión
Estas funciones integradas se utilizan para convertir un tipo de datos en otro tipo de datos.

Nombre de la función Uso Ejemplo

A_CHAR Convierte el otro tipo de datos en un tipo de datos de carácter. TO_CHAR(123);

TO_DATE (cadena, formato) Convierte la cadena dada a la fecha. La cadena debe coincidir con el formato. TO_DATE('2015-ENE-15', 'AAAA-MON-DD');

Salida: 1 / 15 / 2015
TO_NUMBER (texto, formato) Convierte el texto al tipo de número del formato dado. Seleccione TO_NUMBER('1234′,'9999') de d

Salida: 1234
Informat '9' denota el número de dígitos

Seleccione TO_NUMBER('1,234.45′,'9,999.9

Salida: 1234

Funciones de cadena
Estas son las funciones que se utilizan en el tipo de datos de carácter.

Nombre de la función Uso Ejemplo

INSTR(texto, cadena, inicio, Da la posición de un texto particular en la cadena dada. Seleccione INSTR('AEROPLANE'
aparición) dual
 texto – cadena principal Salida: 2
 cadena: texto que debe buscarse
 inicio – posición inicial de la búsqueda (opcional) Seleccione INSTR('AEROPLANE'
dual
Nombre de la función Uso Ejemplo

 acuerdo – aparición de la cadena buscada (opcional) Salida: 9 (2ºnd aparición de E)

SUBSTR (texto, inicio, longitud) Proporciona el valor de la subcadena de la cadena principal. seleccione substr('avión',1,7) de
Salida: aeropla
 texto – cadena principal
 inicio – posición inicial
 longitud: longitud que se va a subcadenar

SUPERIOR ( texto ) Devuelve la mayúscula del texto proporcionado. Seleccione superior('guru99') d

Salida: GURU99
BAJAR ( texto ) Devuelve las minúsculas del texto proporcionado. Seleccione inferior ("AerOpLane

Salida: avión
INITCAP (texto) Devuelve el texto dado con la letra inicial en mayúscula. Seleccione ('guru99') de doble

Salida: Gurú99

Seleccione ('mi historia') de dua

Salida: Mi historia
LONGITUD ( texto ) Devuelve la longitud de la cadena dada. Seleccione LONGITUD ('guru99'

Salida: 6
LPAD (texto, longitud, pad_char) Rellena la cadena en el lado izquierdo para la longitud dada (cadena total) con el Seleccione LPAD('guru99', 10, '$
carácter dado
Salida: $$$$gurú99
RPAD (texto, longitud, pad_char) Rellena la cadena en el lado derecho para la longitud dada (cadena total) con el Seleccione RPAD('guru99′,10,'-'
carácter dado
Salida: gurú99—-
LTRIM (texto) Recorta el espacio en blanco inicial del texto. Seleccione LTRIM('Guru99') de

Salida: Gurú99
Seleccione RTRIM('Guru99 ') de
RTRIM (texto) Recorta el espacio en blanco final del texto.
Salida; Gurú99

Funciones de fecha
Estas son funciones que se utilizan para manipular fechas.

Nombre de la función Uso Ejemplo

ADD_MONTHS (fecha, número de meses) Agrega los meses dados a la fecha. ADD_MONTH('2015-01-01',5);

Salida: 05 / 01 / 2015
FECHA DEL SISTEMA Devuelve la fecha y hora actuales del servidor. Seleccione SYSDATE de dual;

Salida: 10/4/2015 [Link]

TRUNC Redondeo de la variable de fecha al menor valor posible. seleccione sysdate, TRUNC(sysdate) de dual;

Salida: 10/4/2015 [Link] 10/4/2015

REDONDA Redondea la fecha a la nearest límite superior o inferior Seleccione sysdate, ROUND(sysdate) de dual

Salida: 10/4/2015 [Link] 10/5/2015

MESES_ENTRE Devuelve el número de meses entre dos fechas. Seleccione MONTHS_BETWEEN (sysdate+60, sysda

Salida: 2
Resumen
En este capítulo, hemos aprendido lo siguientewing.

 Cómo crear un Procedimiento y diferentes formas de llamarlo

 Cómo crear una función y diferentes formas de llamarla
 Similitudes y diferencias entre Procedimiento y Función
 Parámetros y terminologías comunes de RETURN en subprogramas PL/SQL
 Funciones integradas comunes en Oracle PL / SQL

Te podría gustar:

 Alcance variable PL/SQL y bloque exterior interior: estructura anidada

 Oracle PL/SQL FOR LOOP con ejemplo
 Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo
 Oracle PL/SQL BULK COLLECT: Ejemplo FORALL
 Transacción Autónoma en Oracle PL/SQL: confirmar, revertir
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)

¿Qué es el manejo de excepciones en PL/SQL?

Se produce una excepción cuando el motor PL/SQL encuentra una instrucción que no puede ejecutar debido a un error que ocurre en tiempo de
ejecución. Estos errores no se capturarán en el momento de la compilación y, por lo tanto, es necesario manejarlos solo en el tiempo de
ejecución.

Por ejemplo, si el motor PL/SQL recibe una instrucción para dividir cualquier número entre "0", entonces el motor PL/SQL la generará como una
excepción. La excepción solo la genera el motor PL/SQL en tiempo de ejecución.

Las excepciones impedirán que el programa siga ejecutándose, por lo que, para evitar dicha condición, es necesario capturarlas y manejarlas
por separado. Este proceso se denomina manejo de excepciones, en el que el programador maneja la excepción que puede ocurrir en el tiempo
de ejecución.

Sintaxis de manejo de excepciones

Las excepciones se manejan a nivel de bloque, es decir, una vez que ocurre alguna excepción en cualquier bloque, el control saldrá de la parte
de ejecución de ese bloque. Luego, la excepción se manejará en la parte de manejo de excepciones de ese bloque. Después de manejar la
excepción, no es posible reenviar el control a la sección de ejecución de ese bloque.

La siguiente sintaxis explica cómo detectar y manejar la excepción.

BEGIN
<execution block>
.
.
EXCEPTION
WHEN <exceptionl_name>
THEN
<Exception handling code for the “exception 1 _name’' >
WHEN OTHERS
THEN
<Default exception handling code for all exceptions >
END;
Explicación de sintaxis:

 En la sintaxis anterior, el bloque de manejo de excepciones contiene una serie de condiciones CUÁNDO para manejar la excepción.
 Cada condición WHEN va seguida del nombre de la excepción que se espera que se genere en tiempo de ejecución.
 Cuando se genera cualquier excepción en tiempo de ejecución, el motor PL/SQL buscará en la parte de manejo de excepciones esa
excepción en particular. Comenzará desde la primera cláusula 'CUÁNDO' y, secuencialmente, buscará.
 Si encontró el manejo de excepciones para la excepción que se generó, ejecutará esa parte del código de manejo en particular.
 Si ninguna de las cláusulas "CUANDO" está presente para la excepción que se ha generado, entonces el motor PL/SQL ejecutará la
parte "CUANDO OTROS" (si está presente). Esto es común a todas las excepciones.
 Después de ejecutar la excepción, el control parcial saldrá del bloque actual.
 Solo se puede ejecutar una parte de excepción para un bloque en tiempo de ejecución. Después de ejecutarlo, el controlador omitirá
la parte restante del manejo de excepciones y saldrá del bloque actual.

Nota: CUANDO OTROS siempre debe estar en la última posición de la secuencia. La parte de manejo de excepciones presente después de WHEN
OTHERS nunca se ejecutará ya que el control saldrá del bloque después de ejecutar WHEN OTHERS.

Tipos de excepción
Hay dos tipos de excepciones en Pl/SQL.

1. Excepciones predefinidas
2. Excepción definida por el usuario

Excepciones predefinidas
Oracle ha predefinido alguna excepción común. Estas excepciones tienen un nombre de excepción y un número de error únicos. Estas
excepciones ya están definidas en el paquete 'STANDARD' en Oracle. En el código, podemos usar directamente estos nombres de excepción
predefinidos para manejarlos.

A continuación se muestran las pocas excepciones predefinidas.

 Código de
Excepción Razón de excepción
error

ACCESO_INTO_NULL ORA-06530 Asignar un valor a los atributos de objetos no inicializados

CASO_NO_ENCONTRADO ORA-06592 Ninguna de las cláusulas 'WHEN' en la declaración CASE se cumple y no se especifica ninguna cláusula 'ELSE

COLLECTION_IS_NULL ORA-06531 Usar métodos de colección (excepto EXISTS) o acceder a atributos de colección en colecciones no inicializad

CURSOR_ALREADY_OPEN ORA-06511 Tratando de abrir un cursor que ya esta abierto

DUP_VAL_ON_INDEX ORA-00001 Almacenar un valor duplicado en una columna de base de datos que está restringida por un índice único

INVALID_CURSOR ORA-01001 Cursor ilegal operaciones como cerrar un cursor sin abrir

NÚMERO INVALIDO ORA-01722 La conversión de un carácter a un número falló debido a un carácter numérico no válido

DATOS NO ENCONTRADOS ORA-01403 Cuando la declaración 'SELECT' que contiene la cláusula INTO no recupera filas.

ROW_MISCATCH ORA-06504 Cuando el tipo de datos de la variable del cursor es incompatible con el tipo de retorno del cursor real

SUBSCRIPT_BEYOND_COUNT ORA-06533 Hacer referencia a la colección mediante un número de índice que es mayor que el tamaño de la colección

SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 Hacer referencia a la colección mediante un número de índice que está fuera del rango legal (por ejemplo: -1

TOO_MANY_ROWS ORA-01422 Cuando una declaración 'SELECT' con cláusula INTO devuelve más de una fila

Error aritmético o de restricción de tamaño (por ejemplo: asignar un valor a una variable que es mayor que e
VALOR_ERROR ORA-06502
la variable)

ZERO_DIVIDE ORA-01476 Dividir un número por '0'

Excepción definida por el usuario

In OracleAdemás de las excepciones predefinidas anteriormente, el programador puede crear su propia excepción y manejarlas. Se pueden
crear a nivel de subprograma en la parte de declaración. Estas excepciones son visibles sólo en ese subprograma. La excepción definida en la
especificación del paquete es una excepción pública y es visible dondequiera que se pueda acceder al paquete.

Sintaxis: A nivel de subprograma

DECLARE
<exception_name> EXCEPTION;
BEGIN
<Execution block>
EXCEPTION
WHEN <exception_name> THEN
<Handler>
END;

 En la sintaxis anterior, la variable "nombre_excepción" se define como tipo "EXCEPCIÓN".

 Esto se puede utilizar de forma similar a una excepción predefinida.

Sintaxis:En el nivel de especificación del paquete

CREATE PACKAGE <package_name>

IS
<exception_name> EXCEPTION;
.
.
END <package_name>;
  En la sintaxis anterior, la variable 'nombre_excepción' se define como tipo 'EXCEPCIÓN' en la especificación del paquete de .
 Esto se puede utilizar en la base de datos dondequiera que se pueda llamar al paquete 'nombre_paquete'.

Excepción de aumento de PL/SQL

Todas las excepciones predefinidas se generan implícitamente cada vez que se produce el error. Pero las excepciones definidas por el usuario
deben plantearse explícitamente. Esto se puede lograr utilizando la palabra clave "RAISE". Esto se puede utilizar de cualquiera de las formas
que se mencionan a continuación.

Si 'RAISE' se usa por separado en el programa, propagará la excepción ya generada al bloque principal. Solo se puede utilizar en el bloque de
excepción, como se muestra a continuación.

CREATE [ PROCEDURE | FUNCTION ]

AS
BEGIN
<Execution block>
EXCEPTION
WHEN <exception_name> THEN
<Handler>
RAISE;
END;
Explicación de sintaxis:

 En la sintaxis anterior, la palabra clave RAISE se utiliza en el bloque de manejo de excepciones.

 Siempre que el programa encuentra la excepción "nombre_excepción", la excepción se maneja y se completará normalmente
 Pero la palabra clave "RAISE" en la parte de manejo de excepciones propagará esta excepción particular al programa principal.

Nota: Al generar la excepción al bloque principal, la excepción que se genera también debe ser visible en el bloque principal; de lo contrario,
oracle arrojará un error.

 Podemos usar la palabra clave "RAISE" seguida del nombre de la excepción para generar esa excepción particular definida por el
usuario/predefinida. Esto se puede utilizar tanto en la parte de ejecución como en la parte de manejo de excepciones para generar la
excepción.
CREATE [ PROCEDURE | FUNCTION ]
AS
BEGIN
<Execution block>
RAISE <exception_name>
EXCEPTION
WHEN <exception_name> THEN
<Handler>
END;
Explicación de sintaxis:

 En la sintaxis anterior, la palabra clave RAISE se utiliza en la parte de ejecución seguida de la excepción "nombre_excepción".
 Esto generará esta excepción particular en el momento de la ejecución, y esto debe manejarse o plantearse más.

ejemplo 1: En este ejemplo, vamos a ver

 Cómo declarar la excepción

 Cómo plantear la excepción declarada y
 Cómo propagarlo al bloque principal.

DECLARE
Sample_exception EXCEPTION;
PROCEDURE nested_block
IS
BEGIN
Dbms_output.put_line(‘Inside nested block’);
Dbms_output.put_line(‘Raising sample_exception from nested block’);
RAISE sample_exception;
EXCEPTION
WHEN sample_exception THEN
Dbms_output.put_line (‘Exception captured in nested block. Raising to main block’);
RAISE,
END;
BEGIN
Dbms_output.put_line(‘Inside main block’);
Dbms_output.put_line(‘Calling nested block’);
Nested_block;
EXCEPTION
WHEN sample_exception THEN
Dbms_output.put_line (‘Exception captured in main block');
END:
/
Explicación del código:

 Línea de código 2: Declarar la variable 'sample_exception' como tipo EXCEPCIÓN.

 Línea de código 3: Procedimiento de declaración nested_block.
 Línea de código 6: Imprimiendo la declaración “Dentro del bloque anidado”.
 Línea de código 7: Imprimiendo la declaración "Elevando sample_exception desde un bloque anidado".
 Línea de código 8: Generar la excepción usando 'RAISE sample_exception'.
 Línea de código 10: Manejador de excepciones para la excepción sample_exception en el bloque anidado.
 Línea de código 11: Imprimiendo la declaración 'Excepción capturada en bloque anidado. Elevando al bloque principal”.
 Línea de código 12: Generar la excepción al bloque principal (propagarla al bloque principal).
 Línea de código 15: Imprimiendo la declaración “Dentro del bloque principal”.
 Línea de código 16: Imprimiendo la declaración "Llamando al bloque anidado".
 Línea de código 17: Llamando al procedimiento nested_block.
 Línea de código 18: Excepción
 Línea de código 19: Manejador de excepciones para sample_exception en el bloque principal.
 Línea de código 20: Imprimiendo la declaración "Excepción capturada en el bloque principal".

Puntos importantes a tener en cuenta en la excepción

 En función, una excepción siempre debe devolver un valor o aumentar aún más la excepción. demás Oracle arrojará el error 'Función
devuelta sin valor' en tiempo de ejecución.
 Las declaraciones de control de transacciones se pueden proporcionar en el bloque de manejo de excepciones.
 SQLERRM y SQLCODE son las funciones integradas que proporcionarán el mensaje y el código de excepción.
 Si no se maneja una excepción, de forma predeterminada se revertirán todas las transacciones activas en esa sesión.
 RAISE_APPLICATION_ERROR (-, ) se puede usar en lugar de RAISE para generar el error con el código de usuario y el mensaje. El código
de error debe ser mayor que 20000 y tener el prefijo '-'.

Resumen
Después de este capítulo. deberías poder trabajar para el siguientewing aspectos de pl SQL excepciones

 Manejando las excepciones

 Definir una excepción
 Plantear la excepción
 Propagación de excepciones

Te podría gustar:

 Alcance variable PL/SQL y bloque exterior interior: estructura anidada

 Oracle PL/SQL FOR LOOP con ejemplo
  Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo
 Oracle PL/SQL BULK COLLECT: Ejemplo FORALL
 Transacción Autónoma en Oracle PL/SQL: confirmar, revertir
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)

En este tutorial, aprenderemos a usar SQL en PL/SQL. SQL es el componente real que se encarga de buscar y actualizar los datos en la base de
datos, mientras que PL/SQL es el componente que procesa estos datos. Además, en este artículo, también discutiremos cómo combinar SQL
dentro del bloque PL/SQL.

Transacciones DML en PL/SQL

DML significa Lenguaje de manipulación de datos. Estas declaraciones se utilizan principalmente para realizar la actividad de manipulación.
Se trata de lo siguiente operaiones

 Inserción de datos
 Actualización de datos
 Eliminación de datos
 Selección de datos

En PL/SQL, podemos realizar la manipulación de datos sólo utilizando los comandos SQL.

Inserción de datos
En PL/SQL, podemos insertar los datos en cualquier tabla usando el comando SQL INSERT INTO. Este comando tomará el nombre de la tabla, la
columna de la tabla y los valores de la columna como entrada e insertará el valor en la tabla base.

El comando INSERT también puede tomar los valores directamente de otra tabla usando la instrucción 'SELECT' en lugar de dar los valores para
cada columna. A través de la declaración 'SELECT', podemos insertar tantas filas como contenga la tabla base.

Sintaxis:

BEGIN
INSERT INTO <table_name>(<column1 >,<column2>,...<column_n>)
VALUES(<valuel><value2>,...:<value_n>);
END;

 La sintaxis anterior muestra el comando INSERTAR EN. El nombre y los valores de la tabla son campos obligatorios, mientras que los
nombres de las columnas no son obligatorios si las declaraciones de inserción tienen valores para todas las columnas de la tabla.
 La palabra clave "VALUES" es obligatoria si los valores se proporcionan por separado como se muestra arriba.

Sintaxis:

BEGIN
INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>)
SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;

 La sintaxis anterior muestra el comando INSERT INTO que toma los valores directamente de usando el comando SELECT.
 La palabra clave "VALUES" no debería estar presente en este caso ya que los valores no se proporcionan por separado.

Actualización de datos
La actualización de datos simplemente significa una actualización del valor de cualquier columna de la tabla. Esto se puede hacer usando la
declaración 'ACTUALIZAR'. Esta declaración toma el nombre de la tabla, el nombre de la columna y el valor como entrada y actualiza los datos.
Sintaxis:

BEGIN
UPDATE <table_name>
SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;

 La sintaxis anterior muestra la ACTUALIZACIÓN. La palabra clave 'SET' indica al motor PL/SQL que actualice el valor de la columna con
el valor proporcionado.
 La cláusula "DÓNDE" es opcional. Si no se proporciona esta cláusula, se actualizará el valor de la columna mencionada en toda la
tabla.

Eliminación de datos
La eliminación de datos significa eliminar un registro completo de la tabla de la base de datos. El comando 'BORRAR' se utiliza para este
propósito.

Sintaxis:

BEGIN
DELETE
FROM
<table_name>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;

 La sintaxis anterior muestra el comando ELIMINAR. La palabra clave 'FROM' es opcional y con o sin la cláusula 'FROM' el comando se
comporta de la misma manera.
 La cláusula "DÓNDE" es opcional. Si no se proporciona esta cláusula, se eliminará toda la tabla.

Selección de datos
Proyección/obtención de datos significa recuperar los datos requeridos de la tabla de la base de datos. Esto se puede lograr utilizando el
comando 'SELECT' con la cláusula 'INTO'. El comando 'SELECT' buscará los valores de la base de datos, y la cláusula 'INTO' asignará estos
valores a la variable local de la Bloque PL / SQL.

A continuación se detallan los puntos que deben considerarse en la declaración 'SELECCIONAR'.

 La declaración 'SELECT' debe devolver solo un registro mientras se usa la cláusula 'INTO' ya que una variable puede contener solo un
valor. Si la declaración 'SELECT' devuelve más de un valor, se generará la excepción 'TOO_MANY_ROWS'.
 La declaración 'SELECT' asignará el valor a la variable en la cláusula 'INTO', por lo que necesita obtener al menos un registro de la
tabla para completar el valor. Si no obtuvo ningún registro, se genera la excepción "NO_DATA_FOUND".
 El número de columnas y su tipo de datos en la cláusula "SELECT" debe coincidir con el número de variables y sus tipos de datos en la
cláusula "INTO".
 Los valores se obtienen y completan en el mismo orden que se menciona en la declaración.
 La cláusula 'WHERE' es opcional y permite tener más restricciones en los registros que se van a recuperar.
 La declaración 'SELECT' se puede utilizar en la condición 'WHERE' de otras declaraciones DML para definir los valores de las
condiciones.
 La declaración 'SELECT' cuando se utilizan declaraciones 'INSERT', 'UPDATE', 'DELETE' no debe tener la cláusula 'INTO' ya que no
completará ninguna variable en estos casos.

Sintaxis:

BEGIN
SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;

 La sintaxis anterior muestra el comando SELECT-INTO. La palabra clave "DESDE" es obligatoria y identifica el nombre de la tabla de la
que se deben obtener los datos.
 La cláusula "DÓNDE" es opcional. Si no se proporciona esta cláusula, se recuperarán los datos de toda la tabla.

ejemplo 1: En este ejemplo, veremos cómo realizar DML operaciones en PL / SQL. Vamos a insertar los siguientes cuatro registros en la tabla
emp.

EMP_NOMBRE EMP_NO SALARIO GENERAL

BBB 1000 25000 AAA

XXX 1001 10000 BBB

YYY 1002 10000 BBB

ZZZ 1003 7500 BBB

Luego vamos a actualizar el salario de 'XXX' a 15000 y vamos a eliminar el registro de empleado 'ZZZ'. Finalmente, vamos a proyectar el details
del empleado 'XXX'.

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER;
l_manager VARCHAR2(250);
BEGIN
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES(‘BBB’,1000,25000,’AAA’);
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,’BBB);
INSERT INTO emp(emp_name,emp_no,salary,managed
VALUES(‘YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES(‘ZZZ',1003,7500,'BBB'):
COMMIT;
Dbms_output.put_line(‘Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line(‘Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT:
Dbms_output.put_line('Values Deleted );
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';

Dbms [Link] line(‘Employee Detail’);

Dbms_output.put_line(‘Employee Name:‘||l_emp_name);
Dbms_output.put_line(‘Employee Number:‘||l_emp_no);
Dbms_output.put_line(‘Employee Salary:‘||l_salary);
Dbms [Link] line(‘Emplovee Manager Name:‘||l_manager):
END;
/
Salida:

Values Inserted
Values Updated
Values Deleted
Employee Detail
Employee Name:XXX
Employee Number:1001
Employee Salary:15000
Employee Manager Name:BBB
Explicación del código:

 Línea de código 2-5: Declarando la variable.

 Línea de código 7-14: Insertar los registros en la tabla emp.
 Línea de código 15: Confirmar las transacciones de inserción.
 Línea de código 17-19: Actualización del salario del empleado 'XXX' a 15000
 Línea de código 20: Confirmando la transacción de actualización.
 Línea de código 22: Eliminando el registro de 'ZZZ'
 Línea de código 23: Confirmando la transacción de eliminación.
 Línea de código 25-27: Seleccionar el registro de 'XXX' y completar la variable l_emp_name, l_emp_no, l_salary, l_manager.
 Línea de código 28-32: muestra el valor de los registros recuperados.

Te podría gustar:

 Alcance variable PL/SQL y bloque exterior interior: estructura anidada

 Oracle PL/SQL FOR LOOP con ejemplo
 Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo
 Oracle PL/SQL BULK COLLECT: Ejemplo FORALL
 Transacción Autónoma en Oracle PL/SQL: confirmar, revertir
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)
¿Qué es CURSOR en PL/SQL?
Un cursor es un puntero a esta área de contexto. Oracle crea un área de contexto para procesar una declaración SQL que contiene toda la
información sobre la declaración.

PL/SQL permite al programador controlar el área de contexto a través del cursor. Un cursor contiene las filas devueltas por la declaración SQL.
El conjunto de filas que contiene el cursor se denomina conjunto activo. Estos cursores también se pueden nombrar para poder hacer
referencia a ellos desde otro lugar del código.

El cursor es de dos tipos.

 Cursores implícitos
 Cursor explícito

Cursores implícitos
Siempre que cualquier DML operaocurren en la base de datos, se crea un cursor implícito que contiene las filas afectadas, en ese particular
operación. Estos cursores no se pueden nombrar y, por lo tanto, no se pueden controlar ni hacer referencia a ellos desde otro lugar del código.
Solo podemos hacer referencia al cursor más reciente a través de los atributos del cursor.

Cursor explícito
Los programadores pueden crear un área de contexto con nombre para ejecutar su DML operaciones para tener más control sobre él. El cursor
explícito debe definirse en la sección de declaración del Bloque PL / SQLy se crea para la declaración 'SELECT' que debe usarse en el código.

A continuación se detallan los pasos necesarios para trabajar con cursores explícitos.

 Declarando el cursor Declarar el cursor simplemente significa crear un área de contexto con nombre para la declaración 'SELECT' que
se define en la parte de declaración. El nombre de esta área de contexto es el mismo que el nombre del cursor.
 Cursor de aperturaAl abrir el cursor se le indicará al PL / SQL para asignar la memoria para este cursor. Esto preparará el cursor para
buscar los registros.
 Obteniendo datos del cursorEn este proceso, se ejecuta la instrucción 'SELECT' y las filas recuperadas se almacenan en la memoria
asignada. Estos ahora se denominan conjuntos activos. Obtener datos del cursor es una actividad a nivel de registro, lo que significa
que podemos acceder a los datos registro por registro. Cada declaración de recuperación buscará un conjunto activo y mantendrá la
información de ese registro en particular. Esta declaración es la misma que la declaración "SELECT" que recupera el registro y lo
asigna a la variable en la cláusula "INTO", pero no generará ninguna excepción.
 Cerrar el cursorUna vez que se han obtenido todos los registros, debemos cerrar el cursor para que se libere la memoria asignada a
esta área de contexto.

Sintaxis
DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

 En la sintaxis anterior, la parte de declaración contiene la declaración del cursor y la variable del cursor en la que se asignarán los
datos recuperados.
 El cursor se crea para la declaración 'SELECT' que se proporciona en la declaración del cursor.
 En la parte de ejecución, el cursor declarado se abre, se recupera y se cierra.
Atributos del cursor
Tanto el cursor implícito como el cursor explícito tienen ciertos atributos a los que se puede acceder. Estos atributos brindan más información
sobre el cursor. operaciones. A continuación se muestran los diferentes atributos del cursor y su uso.

Atributo del
Descripción
cursor

Devuelve el resultado booleano 'VERDADERO' si la búsqueda más reciente operaLa operación obtuvo un registro con éxito; de lo contr
%ENCONTRÓ
devolverá FALSO.

%EXTRAVIADO Esto funciona de manera opuesta a %FOUND; devolverá 'VERDADERO' si la búsqueda más reciente operation no pudo recuperar ningú

%ESTA ABIERTO Devuelve el resultado booleano "VERDADERO" si el cursor dado ya está abierto; en caso contrario, devuelve "FALSO"

%NÚMERO DE
Devuelve el valor numérico. Proporciona el recuento real de registros que se vieron afectados por la actividad DML.
FILAS

Ejemplo de cursor explícito:

En este ejemplo, veremos cómo declarar, abrir, recuperar y cerrar el cursor explícito.

Proyectaremos todos los nombres de los empleados de la tabla emp usando un cursor. También usaremos el atributo de cursor para configurar
el bucle para recuperar todos los registros del cursor.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;
LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/
Salida

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Total rows fetched is 3

Explicación del código

 Línea de código 2: Declarar el cursor guru99_det para la declaración 'SELECT emp_name FROM emp'.
 Línea de código 3: Declarando la variable lv_emp_name.

 Línea de código 5: Abriendo el cursor guru99_det.

 Línea de código 6: Configurar la instrucción de bucle básico para recuperar todos los registros en la tabla 'emp'.
 Línea de código 7: Obtiene los datos de guru99_det y asigna el valor a lv_emp_name.
 Línea de código 9: Usando el atributo de cursor '%NOTFOUND' para encontrar si se recupera todo el registro en el cursor. Si se
recupera, devolverá "VERDADERO" y el control saldrá del bucle; de lo contrario, el control seguirá recuperando los datos del cursor y
los imprimirá.
 Línea de código 11: Condición de SALIDA para la declaración de bucle.
 Línea de código 12: Imprima el nombre del empleado recuperado.
 Línea de código 14: Usando el atributo de cursor '%ROWCOUNT' para encontrar el número total de registros que fueron
afectados/recuperados en el cursor.
 Línea de código 15: Después de salir del bucle, el cursor se cierra y la memoria asignada se libera.

FOR instrucción del cursor de bucle

La instrucción “FOR LOOP” se puede utilizar para trabajar con cursores. Podemos dar el nombre del cursor en lugar del límite de rango en la
instrucción del bucle FOR para que el bucle funcione desde el primer registro del cursor hasta el último registro del cursor. La variable del
cursor, la apertura del cursor, la recuperación y el cierre del cursor se realizarán implícitamente mediante el bucle FOR.

Sintaxis
DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
FOR I IN <cursor_name>
LOOP
.
.
END LOOP;
END;

 En la sintaxis anterior, la parte de declaración contiene la declaración del cursor.

 El cursor se crea para la declaración 'SELECT' que se proporciona en la declaración del cursor.
 En la parte de ejecución, el cursor declarado se configura en el bucle FOR y la variable del bucle 'I' se comportará como variable de
cursor en este caso.
Oracle Ejemplo de cursor para bucle:
En este ejemplo, proyectaremos todos los nombres de los empleados de la tabla emp usando un bucle de cursor-FOR.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/
Salida

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY

Explicación del código

 Línea de código 2: Declarar el cursor guru99_det para la declaración 'SELECT emp_name FROM emp'.
 Línea de código 4: Construyendo el bucle 'FOR' para el cursor con la variable de bucle lv_emp_name.
 Línea de código 5: Imprimir el nombre del empleado en cada iteración del bucle.
 Línea de código 8: Salir del bucle

Nota: En el bucle Cursor-FOR, los atributos del cursor no se pueden usar ya que la apertura, recuperación y cierre del cursor se realiza
implícitamente mediante En bucle.

Te podría gustar:

 Oracle Tutorial de SQL dinámico de PL/SQL: Ejecución inmediata y DBMS_SQL

 Declaración de CASO en Oracle PL/SQL con ejemplos
 Oracle PL/SQL FOR LOOP con ejemplo
 Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle PL/SQL BULK COLLECT: Ejemplo FORALL
 Transacción Autónoma en Oracle PL/SQL: confirmar, revertir
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)

¿Qué es la RECOGER A GRANEL?

BULK COLLECT reduce los cambios de contexto entre SQL y el motor PL/SQL y permite que el motor SQL recupere los registros a la vez.

Oracle PL/SQL proporciona la funcionalidad de recuperar los registros de forma masiva en lugar de recuperarlos uno por uno. Este BULK
COLLECT se puede utilizar en la instrucción 'SELECT' para completar los registros de forma masiva o para recuperar el cursor de forma masiva.
Dado que BULK COLLECT recupera el registro en BULK, la cláusula INTO siempre debe contener una variable de tipo de colección. La principal
ventaja de utilizar BULK COLLECT es que aumenta el rendimiento al reducir la interacción entre la base de datos y el motor PL/SQL.

Sintaxis:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;

FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;
En la sintaxis anterior, BULK COLLECT se utiliza para recopilar los datos de las declaraciones 'SELECT' y 'FETCH'.
Cláusula FORALL
El FORALL permite realizar el DML operaciones sobre datos en masa. Es similar a la declaración de bucle FOR excepto en En bucle las cosas
suceden a nivel de récord, mientras que en FORALL no existe el concepto LOOP. En lugar de ello, todos los datos presentes en el rango dado se
procesan al mismo tiempo.

Sintaxis:

FORALL <loop_variable>in<lower range> .. <higher range>

<DML operations>;
En la sintaxis anterior, el DML dado operaLa operación se ejecutará para todos los datos que estén presentes entre el rango inferior y superior.

Cláusula LÍMITE
El concepto de recopilación masiva carga todos los datos en la variable de recopilación de destino de forma masiva, es decir, todos los datos se
completarán en la variable de recopilación de una sola vez. Pero esto no es aconsejable cuando el registro total que se necesita cargar es muy
grande, porque cuando PL / SQL intenta cargar todos los datos, consume más memoria de sesión. Por lo tanto, siempre es bueno limitar el
tamaño de esta colección masiva. operación.

Sin embargo, este límite de tamaño se puede lograr fácilmente introduciendo la condición ROWNUM en la declaración 'SELECT', mientras que
en el caso del cursor esto no es posible.

para superar esto Oracle ha proporcionado la cláusula 'LIMIT' que define la cantidad de registros que deben incluirse en el lote.

Sintaxis:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

En la sintaxis anterior, la instrucción de recuperación del cursor utiliza la instrucción BULK COLLECT junto con la cláusula LIMIT.

Atributos de RECOGER A GRANEL

Similar a cursor atributos BULK COLLECT tiene %BULK_ROWCOUNT(n) que devuelve el número de filas afectadas en el nth Declaración DML de la
declaración FORALL, es decir, dará el recuento de registros afectados en la declaración FORALL para cada valor de la variable de colección. El
término 'n' indica la secuencia de valores en la colección, para la cual se necesita el recuento de filas.

ejemplo 1: En este ejemplo, proyectaremos todos los nombres de los empleados de la tabla emp usando BULK COLLECT y también
aumentaremos el salario de todos los empleados en 5000 usando FORALL.
DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

Salida

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated
Explicación del código:

 Línea de código 2: Declarar el cursor guru99_det para la declaración 'SELECT emp_name FROM emp'.
 Línea de código 3: Declarar lv_emp_name_tbl como tipo de tabla de VARCHAR2(50)
 Línea de código 4: Declarar lv_emp_name como tipo lv_emp_name_tbl.
 Línea de código 6: Abriendo el cursor.
 Línea de código 7: Obteniendo el cursor usando BULK COLLECT con el tamaño LIMIT como variable 5000 intl lv_emp_name.
 Línea de código 8-11: Configurar el bucle FOR para imprimir todos los registros de la colección lv_emp_name.
 Línea de código 12: Usando FORALL actualizando el salario de todos los empleados en 5000.
  Línea de código 14: Comprometiendo la transacción.

Te podría gustar:

 Oracle Tutorial de SQL dinámico de PL/SQL: Ejecución inmediata y DBMS_SQL

 Declaración de CASO en Oracle PL/SQL con ejemplos
 Oracle PL/SQL FOR LOOP con ejemplo
 Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo
 Transacción Autónoma en Oracle PL/SQL: confirmar, revertir
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)

¿Qué son las declaraciones TCL en PL/SQL?

TCL significa Declaraciones de control de transacciones. Guardará las transacciones pendientes o las revertirá. Estas declaraciones desempeñan
un papel vital porque, a menos que se guarde la transacción, los cambios a través de declaraciones DML no se guardarán en la base de datos. A
continuación se muestran las diferentes declaraciones de TCL.

COMETER Guarda todas las transacciones pendientes.

RETROCEDER Descartar todas las transacciones pendientes

PUNTO DE GUARDADO Crea un punto en la transacción hasta el cual se puede realizar la reversión. later

VOLVER A Descartar todas las transacciones pendientes hasta el especificado

La transacción se completará de la siguiente manerawing escenarios.

 Cuando se emita cualquiera de las declaraciones anteriores (excepto SAVEPOINT)

 Cuando se emiten declaraciones DDL. (DDL son declaraciones de confirmación automática)
 CUÁNDO se emiten las declaraciones DCL. (DCL son declaraciones de confirmación automática)

¿Qué es la transacción autónoma?

En PL/SQL, todas las modificaciones realizadas en los datos se denominarán transacción. Una transacción se considera completa cuando se le
aplica la función guardar/descartar. Si no se guarda/descarta, la transacción no se considerará completa y las modificaciones realizadas en los
datos no se harán permanentes en el servidor.

Independientemente de algunas modificaciones realizadas durante una sesión, PL / SQL tratará toda la modificación como una sola transacción
y guardar/descartar esta transacción afecta a todos los cambios pendientes en esa sesión. La transacción autónoma proporciona una
funcionalidad al desarrollador que le permite realizar cambios en una transacción separada y guardar/descartar esa transacción en particular
sin afectar la transacción de la sesión principal.

 Esta transacción autónoma se puede especificar a nivel de subprograma.

 Para hacer que cualquier subprograma funcione en una transacción diferente, se debe proporcionar la palabra clave "PRAGMA
AUTONOMOUS_TRANSATION" en la sección declarativa de ese bloque.
 Le indicará al compilador que trate esto como una transacción separada y guardar/descartar dentro de este bloque no se reflejará en
la transacción principal.
 Es obligatorio emitir COMMIT o ROLLBACK antes de salir de esta transacción autónoma a la transacción principal porque en cualquier
momento solo puede haber una transacción activa.
 Entonces, una vez que realizamos una transacción autónoma, debemos guardarla y completarla, luego solo nosotros podemos volver
a la transacción principal.

Sintaxis:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
.
BEGIN
<executin_part>
[COMMIT|ROLLBACK]
END;
/

 En la sintaxis anterior, el bloque se realizó como una transacción autónoma.

ejemplo 1: En este ejemplo, entenderemos cómo funciona la transacción autónoma.

DECLARE
l_salary NUMBER;
PROCEDURE nested_block IS
PRAGMA autonomous_transaction;
BEGIN
UPDATE emp
SET salary = salary + 15000
WHERE emp_no = 1002;
COMMIT;
END;
BEGIN
SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
dbms_output.put_line('Before Salary of 1002 is'|| l_salary);
UPDATE emp
SET salary = salary + 5000
WHERE emp_no = 1001;

nested_block;
ROLLBACK;

SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;

dbms_output.put_line('After Salary of 1001 is'|| l_salary);
SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;
Salida

Before:Salary of 1001 is 15000

Before:Salary of 1002 is 10000
After:Salary of 1001 is 15000
After:Salary of 1002 is 25000
Explicación del código:

 Línea de código 2: Declarando l_salario como NÚMERO.

 Línea de código 3: Declaración del procedimiento nested_block
 Línea de código 4: Hacer que el procedimiento nested_block sea 'AUTONOMOUS_TRANSACTION'.
 Línea de código 7-9: Incrementar el salario del empleado número 1002 en 15000.
 Línea de código 10: Comprometiendo la transacción.
 Línea de código 13-16: Imprimir el salario details del empleado 1001 y 1002 antes de los cambios.
 Línea de código 17-19: Incrementar el salario del empleado número 1001 en 5000.
 Línea de código 20: Llamar al procedimiento nested_block;
 Línea de código 21: Descartando la transacción principal.
 Línea de código 22-25: Imprimir el salario details del empleado 1001 y 1002 después de los cambios.
 El incremento salarial del empleado número 1001 no se refleja porque se ha descartado la transacción principal. El aumento de salario
para el empleado número 1002 se refleja porque ese bloque se realizó como una transacción separada y se guardó al final.
 Entonces, independientemente de guardar/descartar en la transacción principal, los cambios en la transacción autónoma se
guardaron sin afectar los cambios de la transacción principal.

Te podría gustar:

 Oracle Tutorial de SQL dinámico de PL/SQL: Ejecución inmediata y DBMS_SQL

 Declaración de CASO en Oracle PL/SQL con ejemplos
 Oracle PL/SQL FOR LOOP con ejemplo
 Mientras se realiza el bucle Oracle PL/SQL con ejemplo
 Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo
 Oracle PL/SQL BULK COLLECT: Ejemplo FORALL
 65 preguntas y respuestas de la entrevista PL/SQL (2024)
 Oracle Tutorial de Pl/SQL en PDF para principiantes (descargar ahora)