• Author / Uploaded
• Miguel Leito Leo

Citation preview

PROCEDIMIENTOS Y FUNCIONES ADMINISTRACIÓN DE BASE DE DATOS ORACLE

ALUMNOS: Minaya Alejandro Díaz Paredes Victor Oswaldo León Apaclla Alvarez

DOCENTE: Cesar Pisconte AULA:

TURNO: Anexo

Noche

SISE SEDE COMAS - LIMA 25 de Marzo de 2018

Procedimientos y funciones Oracle permite acceder y manipular información de la base de datos definiendo objetos procedurales (subprogramas) que se almacenan en la base de datos. Estos objetos procedurales son unidades de programa PL/SQL. Los procedimientos o funciones PL/SQL son subprogramas o bloques PL/SQL con nombre, compuestos por un conjunto de sentencias SQL que pueden recibir parámetros y pueden ser invocados desde distintos entornos (SQL*PLUS, Oracle*Forms). Desde otros procedimientos y funciones, y desde otras herramientas Oracle y aplicaciones. 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. - 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. Los procedimientos y funciones llevan a cabo tareas específicas, 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 podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento.

Sintaxis de un procedimiento PLSQL: CREATE OR REPLACE PROCEDURE [esquema].nombre-procedimiento (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-función (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; Descripción de 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.). 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');

PROCEDIMIENTOS. Un procedimiento (almacenado) es un subprograma que ejecuta una acción específica y que no devuelve ningún valor por sí mismo, como sucede con las funciones. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. El concepto de Stored Procedure (Procedimiento Almacenado) se debe a que el programa se guarda o almacena en el motor de Base Datos de Oracle, esto permite que cuando el procedimiento sea invocado o llamado este se ejecute directamente en el Servidor de la Base de Datos de Oracle logrando así una respuesta mucho más rápida. Para crear un procedimiento (stored procedure: procedimiento almacenado) usaremos la siguiente sintaxis: CREATE {OR REPLACE} PROCEDURE nombre_proc( param1 [IN | OUT | IN OUT] tipo,... ) IS -- Declaración de variables locales BEGIN -- Instrucciones de ejecución [EXCEPTION] -- Instrucciones de excepción END; Tras crear el procedimiento, éste se compila y luego se almacena en la BD de forma compilada. Este procedimiento luego puede ser invocado desde cualquier bloque PL/SQL. El uso de OR REPLACE permite sobrescribir un procedimiento existente. Si se omite, y el procedimiento existe, se producirá, un error. Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo, aunque si puede ser utilizando el operador %TYPE. CREATE OR REPLACE PROCEDURE Actualiza_Saldo(cuenta NUMBER, nuevo_saldo NUMBER) IS

-- Declaración de variables locales BEGIN UPDATE SALDOS_CUENTAS SET SALDO = nuevo_saldo, FX_ACTUALIZACION = SYSDATE WHERE CO_CUENTA = cuenta; END Actualiza_Saldo;

También podemos asignar un valor por defecto a los parámetros, utilizando la cláusula DEFAULT o el operador de asignación (:=). CREATE OR REPLACE PROCEDURE Actualiza_Saldo(cuenta NUMBER, new_saldo NUMBER DEFAULT 10) Una vez creado y compilado el procedimiento almacenado podemos ejecutarlo. Existen dos formas de pasar argumentos a un procedimiento almacenado a la hora de ejecutarlo: Notación posicional. Se pasan los valores de los parámetros en el mismo orden en que el procedure los define. BEGIN Actualiza_Saldo(200501,2500); COMMIT; END; Notación nominal. Se pasan los valores en cualquier orden nombrando explícitamente el parámetro y su valor separados por el símbolo =>. BEGIN Actualiza_Saldo(cuenta => 200501,nuevo_saldo => 2500); COMMIT; END;

FUNCIONES. Oracle ofrece varios tipos de funciones para realizar distintas operaciones. Se pueden emplear las funciones del sistema en cualquier lugar en el que se permita una expresión en una sentencia "select". Las funciones, como los procedimientos almacenados son bloques de código que permiten agrupar y organizar sentencias SQL que se ejecutan al invocar la función. Las funciones tienen una estructura similar a la de los procedimientos. Como los procedimientos, las funciones tienen una cabecera, una sección de declaración de variables y el bloque "begin... end" que encierra las acciones. Una función, además contiene la cláusula "return". Una función acepta parámetros, se invoca con su nombre y retorna un valor. Para crear una función empleamos la instrucción "create function" o "create or replace function". Si empleamos "or replace", se sobre escribe (se reemplaza) una función existente; si

se omite y existe una función con el nombre que le asignamos, Oracle mostrará un mensaje de error indicando tal situación. La sintaxis básica parcial es: create o replace function NOMBREFUNCION(PARAMETRO1 TIPODATO, PARAMETRON TIPODATO) return TIPODEDATO is DECLARACION DE VARIABLES begin ACCIONES; return VALOR; end; Ejemplo de creación de una función CREATE OR REPLACE FUNCTION obtener_salario (w_código_emp IN emp.código_emp%TYPE) RETURN NUMBER IS w_salario emp.salario_emp%TYPE; BEGIN SELECT salario_emp INTO w_salario FROM emp WHERE código _emp = w_código_emp; RETURN w_salario; END obtener_salario; Cada función debe devolver un valor del tipo especificado utilizando la sentencia RETURN. Para asignar un valor a una variable, dentro de una función DEBE usarse ":=" (dos puntos e igual). Si no se le definen parámetros a una función, no deben colocarse los paréntesis. Podemos emplear una función sin incluir campos de una tabla.

Estructuras de control IF IF (expresion) THEN ... ELSIF (expresion) THEN ... ELSE ... END IF;

WHILE WHILE (expresion) LOOP ...

END LOOP;

FOR FOR contador IN [REVERSE] inicio..fin LOOP ... END LOOP;

CASE CASE var WHEN 'valor' THEN ... WHEN 'valor2' THEN ... ELSE ... END CASE;

¿Cuándo debo construir Procedures y Function en Oracle? Saber cuándo se debe desarrollar Procedures en Oracle y Function en Oracle es una decisión muy importante que se debe tomar a la hora de construir un sistema que sea eficiente, modular, ordenado y fácil de mantener. Decidir si hacer Procedures en Oracle o Function en Oracle te permitirá ordenar tú código fuente para que sea legible y entendible por cualquier persona que lo revise, podrás evitar tener código fuente duplicado lo que permite realizar una fácil mantención de este, podrás reutilizar código fuente en otros procesos lo que te permitirá ahorrar tiempo y trabajo. Todos estos conceptos son muy importantes para construir procesos que puedan ser mantenidos y/o desarrollados por cualquier persona que tenga los conocimientos para construir código en la Base de Datos Oracle. Para determinar si debes utilizar Procedures en Oracle tienes que responder de forma afirmativa a las siguientes preguntas: 1. ¿El proceso que debes construir tendrá parámetros de Entrada? ¿Necesitas que el proceso te retorne varios parámetros de Salida?

2.

Por ejemplo, si necesitas un proceso que te permita obtener los datos de un empleado en particular, las respuestas a las preguntas 1 y 2 es SI, Para determinar si debes utilizar una Function en Oracle tienes que responder de forma afirmativa a las siguientes preguntas: 1. ¿Lo que debo construir será usado de forma masiva por otros procesos? 2. ¿Necesito obtener siempre un solo resultado? Por ejemplo, si te das cuenta que en varios procesos que debes desarrollar en tu sistema siempre necesitas obtener la edad de un empleado para poder determinar ciertas acciones, las respuestas a las preguntas 1 y 2 es SI.

Una Function en Oracle puede recibir uno o varios parámetros de entrada pero siempre retornara un solo valor, en este caso la edad del empleado. Esta Function puede ser utilizada por cualquier proceso que requiera obtener la edad del empleado. En resumen para poder determinar si se debe utilizar Procedures en Oracle o una Function en Oracle solo se deben responder dos preguntas fáciles, para el caso del Procedure debes preguntarte ¿La cantidad de parámetros de entrada y salida que necesitas obtener?, y para el caso de la Function debes preguntarte si ¿Lo que se necesita será usado masivamente por otros procesos y si solo requieres un valor como resultado?

CONCLUSIÓN. Un Procedure (Procedimiento) es el conjunto de etapas, métodos o definiciones paso a paso, que se utilizan para hacer algo o resolver un problema. En la programación estructurada es un subprograma o parte de un programa principal, Los Procedures en Oracle siempre deben recibir al menos un parámetro de entrada y deberá tener uno o varios parámetros de salida, pueden haber excepciones donde tal vez no necesites parámetros de salida, pero por lo menos se debe tener un parámetro que indique si el proceso termino correctamente. Una Function (Función) es un conjunto de instrucciones o bloque de código que tienen un objetivo en particular e implementa acciones y permiten retornar un resultado, es referenciado por un nombre, puede recibir argumentos y pueden ser ejecutadas desde cualquier parte del código. Una Function en Oracle a diferencia de los Procedures en Oracle siempre devuelve un valor como respuesta siempre.