Oracle Procedimientos almacenados y funciones de PL/SQL con ejemplos

¿Qué son los subprogramas PL/SQL?

En este tutorial, verá una descripción detallada de cómo crear y ejecutar los bloques, procedimientos y funciones mencionados.

Los procedimientos y las funciones son subprogramas que se pueden crear y guardar en la base de datos como objetos de base de datos. También se pueden llamar o referenciar dentro de otros bloques.

También cubrimos las principales diferencias entre estos dos subprogramas y analizamos las Oracle funciones integradas.

Terminologías en subprogramas PL/SQL

Antes de aprender sobre los subprogramas PL/SQL, analizaremos la terminología que forman parte de estos subprogramas.

Parámetro

Un parámetro es una variable o marcador de posición de cualquier valor válido. Tipo de datos PL/SQL a través del cual el subprograma PL/SQL intercambia valores con el código principal. Este parámetro permite la entrada a los subprogramas y extracción de valores de ellos.

• Estos parámetros deben definirse junto con los subprogramas en el momento de su creación.
• Se incluyen en la instrucción de llamada para interactuar con los subprogramas.
• El tipo de datos del parámetro en el subprograma y en la instrucción que lo llama deben ser iguales.
• No se debe mencionar el tamaño del tipo de datos al declarar los parámetros, ya que el tamaño es dinámico.

En función de su finalidad, los parámetros se clasifican de la siguiente manera:

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

EN Parámetro

• Se utiliza para proporcionar datos de entrada a los subprogramas.
• Es una variable de solo lectura dentro de los subprogramas; su valor no se puede cambiar dentro del subprograma.
• En la instrucción de llamada, puede ser una variable, un valor literal o una expresión, como '5*8' o 'a/b'.
• Por defecto, los parámetros son de tipo IN.

Parámetro de salida

• Se utiliza para obtener la salida de los subprogramas.
• Es una variable de lectura y escritura dentro de los subprogramas; su valor puede modificarse dentro de ellos.
• En la instrucción de llamada, siempre debe haber una variable para almacenar el valor del subprograma.

Parámetro ENTRADA SALIDA

• Se utiliza tanto para introducir datos como para obtener resultados de los subprogramas.
• Es una variable de lectura y escritura dentro de los subprogramas; su valor puede modificarse dentro de ellos.
• En la instrucción de llamada, siempre debe haber una variable para almacenar el valor del subprograma.

El tipo de parámetro debe especificarse al crear los subprogramas.

DEVOLUCION

RETURN es la palabra clave que indica al compilador que transfiera el control del subprograma a la instrucción que lo llama. En un subprograma, RETURN simplemente significa que el control debe salir del subprograma; una vez que el controlador encuentra RETURN, el código que le sigue se omite.

Normalmente, el bloque principal llama a los subprogramas, y el control pasa del bloque principal al subprograma llamado. La instrucción RETURN en el subprograma devuelve el control al bloque principal. En el caso de las funciones, la instrucción RETURN también devuelve un valor, cuyo tipo de dato se especifica al declarar la función.

¿Qué es un procedimiento en PL/SQL?

A Procedimiento En PL/SQL, una unidad de subprograma consta de un grupo de sentencias PL/SQL que se pueden llamar por su nombre. Cada procedimiento tiene su propio nombre único y se almacena en el Oracle base de datos como objeto de base de datos.

Nota: Un subprograma no es más que un procedimiento, y debe crearse manualmente según los requisitos. Una vez creado, se almacena como un objeto de base de datos.

Las características de una unidad de subprograma de procedimiento en PL/SQL son:

• Los procedimientos son bloques independientes que se pueden almacenar en el base de datos de CRISPR Medicine News.
• Se les puede llamar por su nombre para ejecutar las sentencias PL/SQL.
• Se utilizan principalmente para ejecutar un proceso.
• Pueden contener bloques anidados, o estar anidados dentro de otros bloques o paquetes.
• Contienen una parte de declaración (opcional), una parte de ejecución y una parte de manejo de excepciones (opcional).
• Los valores se pueden pasar a un procedimiento o recuperarse de él mediante parámetros.
• Estos parámetros deben incluirse en la declaración de llamada.
• Un procedimiento puede tener una instrucción RETURN para devolver el control al bloque que lo llama, pero no puede devolver ningún valor a través de RETURN.
• Los procedimientos no se pueden llamar directamente desde las sentencias SELECT; se pueden llamar desde otro bloque o mediante la palabra clave EXEC.

Sintaxis

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

• La instrucción CREATE PROCEDURE le indica al compilador que cree un nuevo procedimiento. La palabra clave 'OR REPLACE' le indica que reemplace el procedimiento existente (si lo hay) con el actual.
• El nombre del procedimiento debe ser único.
• La palabra clave 'IS' se usa cuando el procedimiento almacenado está anidado dentro de otro bloque. Si el procedimiento es independiente, se usa 'AS'. Aparte de esta norma de codificación, ambas tienen el mismo significado.

Ejemplo 1: Creación de un procedimiento y su llamada mediante EXEC. En este ejemplo, creamos un Oracle Procedimiento que toma un nombre como entrada e imprime un mensaje de bienvenida como salida, utilizando el comando EXEC para llamarlo.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Explicación:

• Code línea 1: Se crea el procedimiento con el nombre 'welcome_msg' y un parámetro 'p_name' de tipo 'IN'.
• Code línea 4: Imprimir el mensaje de bienvenida concatenando el nombre introducido.
• El procedimiento se ha completado correctamente.
• Code línea 7: Llamando al procedimiento usando EXEC con el parámetro 'Guru99'. El procedimiento se ejecuta e imprime “Bienvenido Guru99 ".

¿Qué es una función?

Una función es un subprograma PL/SQL independiente. Al igual que un procedimiento, una función tiene un nombre único y se almacena como un objeto de base de datos PL/SQL. Sus características son:

• Las funciones son bloques independientes que se utilizan principalmente para realizar cálculos.
• Una función utiliza la palabra clave RETURN para devolver un valor, cuyo tipo de datos se define en el momento de su creación.
• Una función debe devolver un valor o generar una excepción; la instrucción return es obligatoria en las funciones.
• Una función sin sentencias DML puede llamarse directamente en una consulta SELECT, mientras que una función con DML solo puede llamarse desde otros bloques PL/SQL.
• Puede contener bloques anidados, o estar anidado dentro de otros bloques o paquetes.
• Contiene una parte de declaración (opcional), una parte de ejecución y una parte de manejo de excepciones (opcional).
• Los valores se pueden pasar a la función o recuperarse de ella a través de parámetros.
• Estos parámetros deben incluirse en la declaración de llamada.
• Una función también puede devolver un valor a través de parámetros OUT, además de utilizar RETURN.
• Dado que siempre devuelve un valor, la instrucción que la llama siempre utiliza un operador de asignación para rellenar una variable.

Sintaxis

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION le indica al compilador que cree una nueva función. 'OR REPLACE' le indica que reemplace la función existente (si la hay) con la actual.
• El nombre de la función debe ser único.
• Se debe mencionar el tipo de datos RETURN.
• La palabra clave 'IS' se usa cuando la función está anidada dentro de otro bloque. Si la función es independiente, se usa 'AS'.

Ejemplo 1: Creación de una función y su llamada mediante un bloque anónimo. En este programa, creamos una función que toma un nombre como entrada y devuelve un mensaje de bienvenida, utilizando un bloque anónimo y una instrucción SELECT para llamarla.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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;

Code Explicación:

• Code línea 1: Creando la función con el nombre 'welcome_msg_func' y un parámetro 'p_name' de tipo 'IN'.
• Code línea 2: Declarando el tipo de retorno como VARCHAR2.
• Code línea 5: Devuelve el valor concatenado 'Welcome' y el valor del parámetro.
• Code línea 8: Bloque anónimo para llamar a la función anterior.
• Code línea 9: Declarar la variable con el mismo tipo de dato que el tipo de retorno de la función.
• Code línea 11: Llamar a la función y asignar el valor de retorno a la variable 'lv_msg'.
• Code línea 12: Imprimiendo el valor de la variable. La salida es “Bienvenido Guru99 ".
• Code línea 14: Llamando a la misma función a través de una instrucción SELECT. El valor devuelto se dirige a la salida estándar.

Similitudes entre un procedimiento y una función

• Ambos pueden ser llamados desde otros bloques PL/SQL.
• Si una excepción generada en el subprograma no se maneja en su manejo de excepciones sección, se propaga 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

Funciones integradas en PL/SQL

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

Funciones de conversión

Estas funciones integradas convierten un tipo de dato en otro.

Funciones de cadena

Estas funciones se utilizan en el tipo de datos de caracteres.

Funciones de fecha

Estas funciones se utilizan para manipular fechas.

Preguntas Frecuentes