Oracle Procedimientos almacenados y funciones de PL/SQL con ejemplos

En este tutorial, verá la descripción detallada sobre cómo crear y ejecutar los bloques nombrados (procedimientos y funciones).

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

Aparte de esto, cubriremos las principales diferencias entre estos dos subprogramas. Además, vamos a discutir el Oracle funciones integradas.

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 principal o padre llamará a los subprogramas y, a continuación, el control pasará de ese bloque principal a los subprogramas llamados. La instrucción RETURN en el subprograma devolverá el control a su bloque principal. En el caso de las funciones, la instrucción 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.

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.
• Las funciones sin instrucciones DML se pueden llamar directamente en una consulta SELECT, mientras que las funciones con operaciones DML solo se pueden 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, en la declaración de llamada siempre se acompaña con el operador de asignación 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.
No se puede llamar a la declaración SELECT	Se puede llamar a una función que no contiene declaraciones DML en la 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 valor
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. Informat '9' denota el número de dígitos	Seleccione TO_NUMBER('1234′,'9999') de dual; Salida: 1234 Seleccione TO_NUMBER('1,234.45′,'9,999.99') de dual; 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, aparición)	Da la posición de un texto particular en la cadena dada. texto – cadena principal cadena: texto que debe buscarse inicio – posición inicial de la búsqueda (opcional) acuerdo – aparición de la cadena buscada (opcional)	Seleccione INSTR('AEROPLANE','E',2,1) de dual Salida: 2 Seleccione INSTR('AEROPLANE','E',2,2) de dual Salida: 9 (2ºnd aparición de E)
SUBSTR (texto, inicio, longitud)	Proporciona el valor de la subcadena de la cadena principal. texto – cadena principal inicio – posición inicial longitud: longitud que se va a subcadenar	seleccione substr('avión',1,7) de dual Salida: aeropla
SUPERIOR ( texto )	Devuelve la mayúscula del texto proporcionado.	Seleccione superior('guru99') de dual; Salida: GURU99
BAJAR ( texto )	Devuelve las minúsculas del texto proporcionado.	Seleccione inferior ("AerOpLane") de doble; 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 dual Salida: Mi historia
LONGITUD ( texto )	Devuelve la longitud de la cadena dada.	Seleccione LONGITUD ('guru99') de dual; Salida: 6
LPAD (texto, longitud, pad_char)	Rellena la cadena en el lado izquierdo para la longitud dada (cadena total) con el carácter dado	Seleccione LPAD('guru99', 10, '$') de dual; Salida: $$$$gurú99
RPAD (texto, longitud, pad_char)	Rellena la cadena en el lado derecho para la longitud dada (cadena total) con el carácter dado	Seleccione RPAD('guru99′,10,'-') de dual Salida: gurú99—-
LTRIM (texto)	Recorta el espacio en blanco inicial del texto.	Seleccione LTRIM('Guru99') de dual; Salida: Gurú99
RTRIM (texto)	Recorta el espacio en blanco final del texto.	Seleccione RTRIM('Guru99 ') de dual; 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 2:11:43
TRUNC	Redondeo de la variable de fecha al menor valor posible.	seleccione sysdate, TRUNC(sysdate) de dual; Salida: 10/4/2015 2:12:39 10/4/2015
REDONDA	Redondea la fecha al límite más cercano, ya sea superior o inferior.	Seleccione sysdate, ROUND(sysdate) de dual Salida: 10/4/2015 2:14:34 10/5/2015
MESES_ENTRE	Devuelve el número de meses entre dos fechas.	Seleccione MONTHS_BETWEEN (sysdate+60, sysdate) de dual Salida: 2

Resumen

En este capítulo hemos aprendido lo siguiente.

• 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