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 pueden llamar o referir dentro de los otros bloques.
Aparte de esto, cubriremos las principales diferencias entre estos dos subprogramas. Además, vamos a discutir las funciones integradas de Oracle.
En este tutorial de procedimiento almacenado de Oracle, aprenderá:
- Terminologías en subprogramas PL / SQL
- ¿Qué es el procedimiento en PL / SQL?
- ¿Qué es la función?
- Similitudes entre procedimiento y función
- Procedimiento vs. Función: diferencias clave
- Funciones integradas en PL / SQL
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 una variable o marcador de posición de cualquier tipo de datos PL / SQL válido 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 la creación.
- Estos parámetros se incluyen en la declaración de llamada de estos subprogramas para interactuar con 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 como
- IN Parámetro
- Parámetro OUT
- Parámetro IN OUT
IN 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 o 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 OUT:
- Este parámetro se utiliza para obtener resultados de los subprogramas.
- Es una variable de lectura y 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 IN OUT:
- Este parámetro se utiliza tanto para dar entrada como para obtener salida de los subprogramas.
- Es una variable de lectura y 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.
REGRESO
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 debe salir del subprograma. Una vez que el controlador encuentra 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 cambiará de esos bloques padre a los subprogramas llamados. 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 de cualquier tipo de datos PL / SQL válido.
¿Qué es el procedimiento en PL / SQL?
Un procedimiento en PL / SQL es una unidad de subprograma que consta de un grupo de instrucciones PL / SQL que se pueden llamar por su nombre. Cada procedimiento en PL / SQL tiene su propio nombre único por el cual se puede hacer referencia a él y llamarlo. Esta unidad de subprograma en la base de datos de Oracle 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 el requisito. Una vez creados, se almacenarán como objetos de base de datos.
A continuación se muestran 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 la base de datos.
- Se puede llamar a estos procedimientos PLSQL 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, parte de manejo de excepciones (opcional).
- Los valores se pueden pasar al procedimiento de Oracle o extraerse 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 instrucción RETURN para devolver el control al bloque de llamada, pero no puede devolver ningún valor a través de la instrucción RETURN.
- Los procedimientos no se pueden llamar directamente desde sentencias SELECT. Se pueden llamar desde otro bloque o mediante la palabra clave EXEC.
Sintaxis:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREAR PROCEDIMIENTO 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 hubiera) 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 procedimiento de Oracle que toma el nombre como entrada e imprime el mensaje de bienvenida como salida. Vamos a utilizar el comando EXEC para llamar al procedimiento.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_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 : Impresión del mensaje de bienvenida concatenando el nombre de entrada.
- El procedimiento se compiló correctamente.
- Línea de código 7 : Llamar al procedimiento usando el comando EXEC con el parámetro 'Guru99'. Se ejecuta el procedimiento y el mensaje se imprime como "Bienvenido Guru99".
¿Qué es la función?
Funciones es un subprograma PL / SQL independiente. Al igual que el procedimiento PL / SQL, las funciones tienen un nombre único por el que pueden ser referidas. 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 usa 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, el retorno es obligatorio en las funciones.
- La función sin declaraciones DML se puede llamar directamente en la consulta SELECT, mientras que la función con operación DML solo 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, parte de manejo de excepciones (opcional).
- Los valores se pueden pasar a la función o obtener 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 que no sean RETURN.
- Dado que siempre devolverá el valor, en la instrucción de llamada siempre acompaña al operador de asignación para completar las variables.
Sintaxis
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION indica al compilador que cree una nueva función. La palabra clave 'OR REPLACE' indica al compilador que reemplace la función existente (si existe) 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. Vamos a utilizar un bloque anónimo y una sentencia de selección para llamar a la función.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 la función de Oracle con el nombre 'welcome_msg_func' y con un parámetro 'p_name' de tipo 'IN'.
- Línea de código 2 : declarando 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 tipo de datos igual 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 : Impresión del 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 se pueden llamar desde otros bloques PL / SQL.
- Si la excepción generada en el subprograma no se maneja en la sección de manejo de excepciones del subprograma, entonces se propagará al bloque de llamada.
- Ambos pueden tener tantos parámetros como sea necesario.
- Ambos se tratan como objetos de base de datos en PL / SQL.
Procedimiento vs. Función: diferencias clave
| Procedimiento | Función |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Funciones integradas en PL / SQL
PL / SQL contiene varias funciones integradas para trabajar con cadenas y tipos de datos de fecha. Aquí vamos a ver 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 |
|---|---|---|
| TO_CHAR | Convierte el otro tipo de datos en tipo de datos de caracteres | 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-LUN-DD'); Salida: 15/1/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 doble; 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 caracteres.
| Nombre de la función | Uso | Ejemplo |
|---|---|---|
| INSTR (texto, cadena, inicio, ocurrencia) | Da la posición de un texto particular en la cadena dada.
| Seleccione INSTR ('AIRPLANE', 'E', 2,1) de la salida dual : 2 Seleccione INSTR ('AIRPLANE', 'E', 2,2) de la salida dual: 9 ( segunda aparición de E) |
| SUBSTR (texto, inicio, longitud) | Da el valor de subcadena de la cadena principal.
| seleccione substr ('aeroplano', 1,7) de salida dual : aeropla |
| SUPERIOR (texto) | Devuelve las mayúsculas del texto proporcionado. | Seleccione superior ('guru99') de dual; Salida : GURU99 |
| INFERIOR (texto) | Devuelve la minúscula del texto proporcionado. | Seleccione inferior ('avión') de doble; Salida : avión |
| INITCAP (texto) | Devuelve el texto dado con la letra inicial en mayúsculas. | Seleccione ('guru99') de la salida dual : Guru99 Seleccione ('mi historia') de la salida dual : Mi historia |
| LONGITUD (texto) | Devuelve la longitud de la cadena dada. | Seleccione LENGTH ('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 : $$$$ guru99 |
| 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 Salida dual : guru99 ---- |
| LTRIM (texto) | Recorta el espacio en blanco inicial del texto | Seleccione LTRIM ('Guru99') de dual; Salida : Guru99 |
| RTRIM (texto) | Recorta el espacio en blanco final del texto | Seleccione RTRIM ('Guru99') de dual; Salida ; Guru99 |
Funciones de fecha
Estas son funciones que se utilizan para manipular fechas.
| Nombre de la función | Uso | Ejemplo |
|---|---|---|
| ADD_MONTHS (fecha, no. De meses) | Agrega los meses dados a la fecha | ADD_MONTH ('2015-01-01', 5); Salida : 01/05/2015 |
| SYSDATE | Devuelve la fecha y hora actuales del servidor. | Seleccione SYSDATE de dual; Salida : 4/10/2015 2:11:43 PM |
| TRUNC | Redondea la variable de fecha al valor más bajo posible | seleccione sysdate, TRUNC (sysdate) de dual; Salida : 4/10/2015 2:12:39 PM 4/10/2015 |
| REDONDO | Redondea la fecha al límite más cercano, ya sea más alto o más bajo | Seleccione sysdate, ROUND (sysdate) de salida dual : 10/4/2015 2:14:34 PM 10/5/2015 |
| MONTHS_BETWEEN | Devuelve el número de meses entre dos fechas. | Seleccione MONTHS_BETWEEN (sysdate + 60, sysdate) de la salida dual : 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