Anuncios en tutorial de programación PLSQL

lunes, 23 de agosto de 2021

Procedimientos y Funciones en PLSQL

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:

  • Verificar si el usuario tiene permiso de ejecución.
  • Verificar la validez del procedimiento o función.
  • Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:

  • Facilidad para gestionar la seguridad.
  • Mejor rendimiento al estar compilados y almacenados en la base de datos.
  • Mejor gestión de la memoria.
  • Mayor productividad e integridad.

La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).

Sintaxis de un procedimiento PLSQL

   CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim
      (nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}
         Declaración de variables;
         Declaración de constantes;
         Declaración de cursores;
         BEGIN
            Cuerpo del subprograma PL/SQL;
            EXCEPTION
            Bloque de excepciones PL/SQL;
         END;

Sintaxis de una función PLSQL

   CREATE OR REPLACE FUNCTION [esquema].nombre-funcion
      (nombre-parámetro {IN, OUT, IN OUT} tipo-de-dato, ..)
      RETURN tipo-de-dato {IS, AS}
         Declaración de variables;
         Declaración de constantes;
         Declaración de cursores;
         BEGIN
            Cuerpo del subprograma PL/SQL;
            EXCEPTION
            Bloque de excepciones PL/SQL;
         END;

Aclaraciones sobre la sintaxis

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplos prácticos

Ejemplo de creación de un procedimiento PL/SQL:

   CREATE OR REPLACE PROCEDURE
      procedimiento1 (a IN NUMBER, b IN OUT NUMBER) IS
         vmax NUMBER;
         BEGIN
            SELECT salario, maximo
            INTO b, vmax
            FROM empleados
            WHERE empleado_id=a;
            IF b < vmax THEN
               b:=b+100;
            END IF;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
               b:=-1;
               RETURN;
            WHEN OTHERS THEN
               RAISE;
         END;

Ejemplo de SQL script que llama a un procedimiento PLSQL:

   DECLARE
      vsalario NUMBER;
   BEGIN
      procedimiento1 (3213, vsalario)
      dbms_output.put_line
         ('El salario del empleado 3213 es ', vsalario);
   END;

Ejemplo de ejecución desde SQL de un procedimiento PL/SQL que sólo utiliza parámetros de entrada:

   sql> exec proc_solo_parametros_entrada (2000, 2, 'Pepe');

44 comentarios:

Asadasdw dijo...

¿Sabes cómo obtener los parámetros de entrada y salida de un proceso o función mediante un select?

Gracias!

JLPM dijo...

Hola Eloy,

Puedes utilizar la siguiente consulta SELECT:

SELECT
obj.name package_or_procedure,
arg.procedure$ procedure,
arg.argument,
arg.pls_type,
arg.position#,
arg.sequence#,
decode(arg.in_out, null,'IN', 1,'OUT', 2,'IN/OUT', 'Desconocido') in_out

FROM
sys.argument$ arg,
sys.obj$ obj

WHERE
obj.obj# = arg.obj#
AND obj.name = [nombre procedure]

ORDER BY 1, 2, 5, 6

Debes tener en cuenta que para procedimientos incluidos es un paquete PL/SQL debes introducir el nombre del paquete en vez del nombre del procedimiento. En este caso también podrías filtrar por el campo procedure$.

Unknown dijo...

Muy buen blog Pepelu.

Tengo una consulta. ¿Sabes si existe alguna manera de que la BD informe a una aplicación externa de la alteración de una tabla?.
Esto es, imagina que se borra un registro, salta un trigger y este ejecuta un procedimiento, hata ahi bien, pero todo queda dentro de la BD. ¿Existe la posibilidad de comunicarse con un programa externo?, algo asi como una función CALL.

JLPM dijo...

Hola Pedro,

Desde un Trigger PL/SQL puedes llamar a un procedimiento Java almacenado en la base de datos.

Si lo que buscas es llamar directamente a un programa totalmente externo, sé que hay formas de hacerlo pero es bastante complejo explicarlo en un comentario.

Te aconsejo que te informes en los siguientes manuales:

Oracle Application Developper´s Guide - Fundamentals - Capítulo 10 "Calling External Procedures"

Oracle PL/SQL User´s Guide and Reference - Capítulo 8 "PL/SQL Subprograms" Sección "Calling External Subprograms"

Espero haberte ayudado.

Zorro dijo...

Excelente blog, muy informativo.

Lo estaré leyendo frecuentemente, saludos !

Anónimo dijo...

Hola!, felicitaciones por la pagina!. Necesito saber como llamar en un select, una funcion que se encuentra en otra instancia .
Gracias!!
Pablo

JLPM dijo...

Hola Ospaco,

El funcionamiento es parecido a cuando se accede a tablas remotas. Puedes echar un vistazo en este enlace: DBLinks.

Un ejemplo sería:

SELECT nombre
FROM productos
WHERE funcion1@dbs1(almacen) > 0;

En donde funcion1 sería una función definida en una base de datos remota que devuelve un resultado numérico.

De todas formas, yo te recomendaría definir la función remota localmente.

Winn Live dijo...

No funciona las ejecuciones de los procedimientos cuando tienen paramtros de salida, me vendria bien una ayuda

JLPM dijo...

Hola Félix, algo estarás haciendo mal, no hay ningún secreto a la hora de utilizar parámetros de salida en un procedimiento. Si quieres puedes mandarme un email a pepelublog[arroba]gmail.com.

Ramiro dijo...

Hola Pepelu, excelente tu blog, te felicito.
Una pregunta:
Como puedo pasar parametros de vectores y matrices a un procedimiento?
Tengo un sistema en el cual existe un modulo administrador y una opcion de Gestion de Permisos. El usuario administrador tiene la posibilidad de agregar varios permisos a los usuarios que vaya a crear, y quisiera saber si eso se puede, como si fuera codigo de VB, Java, C, etc.
Gracias por tu atencion.

JLPM dijo...

Hola Ramiro,

Puedes utilizar una tabla temporal en lugar de vectores y matrices. La tablas temporales se utilizan para almacenar datos exclusivos a una sesión Oracle.

Echa un vistazo a esta entrada: Tablas temporales PL/SQL.

Anónimo dijo...

Hola saludos tengo una pregunta como puedo saber si un procedimiento se esta ejecutando con una sentencia sql ... para no ejecutarlo dos veces al mismo tiempo agradesco en lo que me puedas ayudar...gracias..

JLPM dijo...

Hola Mtriana,

La mejor forma de controlar este tipo de cosas es utilizando el paquete de funciones DBMS_LOCK, pero el asunto de los LOCKS de usuario se merece un post completo, por lo que me lo apunto para escribir artículo completo sobre el tema.

JLPM dijo...

Mtriana,

Ya tienes la entrada sobre el uso del paquete estándar PL/SQL DBMS_LOCK.

Espero que te sirva de ayuda.

Manuel Garcia dijo...

Hola !,

Por favor, necesito saber cuál es la sintaxis de la instrucción PL/SQL que me permite llamar a un procedimiento desde otro procedimiento.

Gracias.

JLPM dijo...

Manuel, es bastante sencillos:

[identificador_propietario].[nombre_paquete].nombre_procedimiento
(parametro1, ... , parametroN);

El propietario o esquema (owner ID) sólo se necesita si no eres tú el propietario del procedimiento, es decir, si no lo creaste tú o si lo creastes bajo otro propietario o esquema.

Y el nombre del paquete sólo se necesita si el procedimiento se creó dentro de un paquete.

Manuel Garcia dijo...

Excelente!! ... Muchas Gracias.

JP dijo...

Interesante tu blog. Un pequeño detalle, las funciones si permiten tener parámetros de salida.

JLPM dijo...

Tienes toda la razón JP, gracias por hacérmelo notar. Ya está corregido.

EvilSaya dijo...

una consulta como hago para ocupar una secuencia en un procedimiento sin tener que declara afuera una variable extra
create or replace PROCEDURE crea_carrera
(c_cod IN TBD_CARRERA.ID_CARRERA%TYPE,
c_desc IN TBD_CARRERA.DESC_CARRERA%TYPE)
aS
v_CurrVal NUMBER;
BEGIN
insert into TBD_CARRERA values(SEC_ID_CARR.NextVal,c_desc);
SELECT SEC_ID_CARR.currval
INTO v_CurrVal
FROM dual;
END crea_carrera ;
despues llamo al procedure

declare
v_CurrVal NUMBER;
begin
crea_carrera(v_CurrVal,'desccarrera2');
end

EvilSaya dijo...

pero tengo k declarar v_CurrVal , ome sale error

JLPM dijo...

Hola EvilSaya,

Veo algunas inconsistencias, el procedure lo defines como crea_carrera(c_cod, c_desc) y dentro no usas para nada el valor c_cod.

Luego obviamente para llamar al procedimiento utilizando como parámetro una variable, esta variable tiene que estar declarada.

Unknown dijo...

Hola, tengo un procedimiento almacenado que crea tablas y necesito modificarlo para que genere tablas en otros esquemas de BBDD, diferentes al que contiene el procedimiento, ¿como doy permisos al procedimiento para que pueda generar tablas en los otros esquemas?

JLPM dijo...

Hola David, para que un usuario o esquema pueda crear tablas en otros esquemas debe tener permisos para CREATE ANY TABLE, para ello debes utilizar el siguiente comando SQL:

GRANT CREATE ANY TABLE TO [usuario o esquema]

Unknown dijo...

Hola pepelu gracias por el apoyo que brindas...tengo una pregunta...puedo pasar como parametro a una funcion un parametro? es decir en una funcion que calcula los dias habiles en un rango de fechas los parametros son fecha inicial y fecha final, pero esos tambien son los parametros de entrada del query principal

JLPM dijo...

Hola Sandra, no entiendo muy bien tu pregunta. En una función puedes utilizar como parámetros constantes o variables, y puedes llamarla desde otra función o procedimiento. No hay ningún problema si quieres utilizar como parámetro una variable que sea el parámetro de otra función.

Nestor Roca dijo...

Hola, como hago para llamar un sp que esta empaquetado desde el mismo gestor de consultas de oracle?

Nestor Roca dijo...

este sp retorna datos en una variable tipo cursor

JLPM dijo...

Hola Nroca, echa un vistazo al artículo en el que cuento cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento. En los comentarios hay un ejemplo sobre cómo hacer lo que estas preguntando.

Ing. SaG dijo...

Quisiera saber como le hago para que esto funcione:

CREATE OR REPLACE PROCEDURE select_TIEMPO(a in number, b in varchar2, c in number)
AS
BEGIN
select *
from tiempo;
END select_TIEMPO;
/

JLPM dijo...

Hola Ing. SaG, debes utilizar un cursor. Echa un vistazo a este artículo: cursores PL/SQL.

SAM dijo...

buenas noches para quien me pueda colaborar es que tengo que Crear un procedimiento que reciba como parámetro la categoría de un vehículo y un porcentaje, el procedimiento debe actualizar todos los valores de los vehículos que están en proceso de venta que corresponden a la categoría definida, incrementando el valor en el porcentaje dado.

Marta dijo...

Hola, a ver si me puedo explicar bien. Tengo 2 esquemas, y en uno de ellos tengo un paquete con unas funciones, y quiero poder acceder a ese paquete desde otro paquete en el esquema 2. He dado permisos de ejecucion al usuario del esquema 2, en el paquete del esquema 1, y llamo a la funcion, con el nombre del paquete.nombre de la funcion (paquete.funcion). Bueno pues eso es lo que me da error, ORA-00904. A ver si me podeis echar una mano. Gracias!

JLPM dijo...

Hola Marta, el error ORA-00904 sale cuando el nombre de una columna es inválido y según lo que comentas creo que según tu has intuido se trata de un problema de permisos. Puede ser que dentro de la función se llame a algún objeto de la base de datos sobre el que no tiene permisos el usuario del esquema 2.

Unknown dijo...

Entonces es posible que los procedimientos devuelvan un valor como se ve en ejemplo¿?

Entonces cuando seria ideal usar los procedimientos y las fucniones, ya que lo que he leido es que los procedimientos no retornan variables, mientras que las funciones si,
solo e visto que cuando un procedimiento es usado en la suite de oracle se tienen que poner 2 argumentos de salida uno de errobuff y el otro errcode ,.

Entonces cuando usar funcion o preocedimiento,

otra duda, cuando se usarian los paketes

JLPM dijo...

Tic Tak, efectivamente los procedimientos PL/SQL, aparte de parámetros de entrada, pueden utilizar parámetros que sean de tipo salida o incluso de tipo entrada/salida.

Las funciones PLSQL también pueden utilizar parámetros de salida pero no retornan variables, lo que devuelven es un valor que el el programa que llama a la función se puede asignar a una variable.

La decisión de utilizar un procedimiento o una función depende del uso que le quieras das- Las funciones PL/SQL tienen la ventaja de que pueden incluirse dentro de una consulta SELECT, cosa que no se puede hacer con un procedimiento.

Lo que comentas de los parámetros errbuff y retcode es propio de los programas concurrentes de la Oracle eBusiness Suite que llaman a procedimientos PL/SQL. Son los parámetros de salida a los que el procedimiento debe asignar un valor y que utiliza el gestor de programas concurrentes para determinar si un programa terminó en error (retcode) y que error ocurrió (errbuff). Pero esto no es algo propio de PL/SQL, de hecho si no conociera la Oracle eBusiness Suite. no sabría de que estás hablando.

Los paquetes no son más que conjuntos de procedimientos y funciones, son una forma de organizar los programas PL/SQL. Por ejemplo, si creas un conjunto de funciones y procedimientos que se utilizan para procesar información de clientes, podría interesarte agruparlos en un paquete PLSQL llamado CLIENTES_UTIL_PKG.

Lisbeth dijo...

Buenos Días, tengo una duda.. se necesita hacer mantenimiento a una tabla (insert y update) a través de un procedimiento, la tabla tiene 150 campos, los mismos que tiene como entrada el procedimiento. Por otro lado se necesita hacer la invocaión al mismo a traves de varias opciones de un sistema con la diferencia que no todos necesitan actualizar todos los campos, algunos solo actualizan 5 campos, otros 10, otros mas y otros menos. La pregunta es: hay alguna manera de que cada opción del sistema pueda realizar la invocación al mismo procedimiento sólo con los campos que necesiten actualizar??, Gracias...

JLPM dijo...

Lisbeth, lo que comentas se puede programar, los parámetros de un procedimiento o función pueden utilizarse o no utilizarse pudiendo pasar valores nulos (NULL) si no se van a utilizar.

Unknown dijo...

Un procedimiento puede llamar a otro procedimiento? porque entendi que un procedmiento puede llamar a una funcion.

JLPM dijo...

Hola Rebeca, efectivamente desde un procedimiento puedes llamar a otro procedimiento, y por supuesto también a funciones.

Unknown dijo...

me puede ayudar en este ejercicio?

crear una funcion para generar el valor del atributo idalquiler quien es del tipo number y su valor debera ser formado concatenando la fecha actual y un numero secuencialen la fecha:Ejemplo si hoy es 24/10/2016 entonces el dia seria 241020161(DDMMYYYYn,n es el secuencial del dia, si el primer alquler de la fecha sera 1, el siguiente sera 2, para el siguiente dia iniciara en 1
)

JLPM dijo...

Wendy, el ejercicio es bastante sencillo y se puede implementar de diferentes formas. Si te doy la respuesta no vas a aprender lo mismo que si te esfuerzas y lo sacas por ti misma.

Unknown dijo...

BUEN DIA
amigo aun no puedo llamar un procedimiento, en oracle 11g express edition.
Este es el procedimiento:
create or replace procedure "INGUSUARIO"
(contraseñausu IN NUMBER,
usuariousu IN VARCHAR2,
nivelusu IN NUMBER,
edadusu IN NUMBER)
is
begin
insert into usuarios(CONTRASEÑA,USUUSUARIO,USUNIVELACADEMICO,USUEDAD)
values (contraseñausu,usuariousu,nivelusu,edadusu);
end;

Y lo intento llamar asi: EXEC INGUSUARIO (123456,'leonardo',11,18);
pero me da error ORA-00900: instrucción SQL no válida
Agradezco tu ayuda para saber si tengo bien hecho los procedimientos o las funciones al llamarlas que hagan lo que espero.

JLPM dijo...

Hola Leonardo,

Supongo que el problema es que estás intentando ejecutar el EXEC desde un entorno IDE que no lo soporta (en SQL Developer si que funciona). Ten en cuenta que EXEC es un comando de SQL*Plus.

En los entornos IDE como Toad o SQL Developer lo apropiado sería utilizar los siguiente:

BEGIN
INGUSUARIO (123456,'leonardo',11,18);
END;

Un saludo.