PROCEDURE

PROCEDURE

                                    PROCEDURE CON CURSOR

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: Funciones y

Procedimientos almacenados.• Los procedimientos o funciones son bloques PL/SQL con nombre, 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. •

Los procedimientos y funciones llevan a cabo tareas específicas, y su mayor diferencia radica en que las funciones devuelven un valor.

• Sintaxis Procedimientos

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;

Descripción de la sintaxis:

• Nombre-parámetro: es el nombre que 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 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.

·         Cuando se crea un procedimiento o función, Oracle automáticamente compila el código fuente, guarda el código objeto en un área compartida de la SGA (System Global Area) y almacena tanto el código fuente como el código objeto en catálogos del diccionario de datos.

·         El código objeto permanece en la SGA, por tanto, los procedimientos o funciones se ejecutan más rápidamente y lo pueden compartir muchos usuarios. Cuando es necesario liberar áreas de la SGA, Oracle aplica el algoritmo ‘menos-usado-recientemente’. Si en un

momento determinado se libera el área SQL de un procedimiento o función, la próxima vez que se ejecute se vuelve a cargar el código objeto, que está almacenado en catálogo, en la SGA

 Llamadas a procedimientos

• Desde otro procedimiento, función y triggers

CREATE PROCEDURE proceso ... IS ...

BEGIN ...

/* llamada al procedimiento contratar_empleado */

contratar_empleado (2645, ’Contabilidad’,’19/12/1999’);

END;

Herramientas de desarrollo de aplicaciones de Oracle:

SQL*Plus, SQL*Dba, SQL*Forms, SQL*Menu, SQL*ReportWriter, etc.

EXECUTE contratar_empleado (2645, ’Contabilidad’,’19/12/1999’);

SQL> SELECT *

Llamadas a funciones

Desde otro procedimiento, función y triggers

CREATE PROCEDURE proceso ... IS ...

BEGIN ...

/* llamada a la función obtener_salario */

w_sal :=obtener_salario (w_código);

END;

• Desde un bloque anónimo

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Salario cod_emp 1 '||obtener_salario(1));

END;

Desde una instrucción SQL

SELECT cod_emp, nom_emp, obtener_salario(cod_emp)

FROM emp;

Documentación procedimientos y funciones

• Para obtener los nombres de todos los procedimientos y funciones

se puede consultar la VISTA USER_OBJECTS

SELECT object_name, object_type FROM USER_OBJECTS

WHERE object_type IN (‘PROCEDURE’ , ‘FUNCTION’);

• Para obtener el texto de un procedimiento o función almacenado se

puede consultar la VISTA USER_SOURCE

SELECT text FROM USER_SOURCE

WHERE type = ‘PROCEDURE’

AND name = ‘CONTRATAR_EMPLEADO’

Depuración de procedimientos y funciones

• Para visualizar los errores de compilación se puede consultar la

VISTA USER_ERRORS o el comando SHOW ERRORS.

• Se pueden visualizar valores o mensajes desde un procedimiento o

función, invocando al package standard DBMS_OUPUT.

Procedimiento                               Descripción

DBMS_OUTPUT.PUT                   Añade texto a la línea actual

DBMS_OUTPUT.NEW_LINE      Marca un final de línea

DBMS_OUTPUT.PUT_LINE        Combina PUT y NEW_LINE

Es necesario activar SERVEROUTPUT (SET SERVEROUTPUT ON)

para ver las salidas desde procedimientos o funciones almacenados

1  DECLARE

  2  V_ENAME EMP.ENAME%TYPE;

  3  BEGIN

  4  SELECT ENAME INTO V_ENAME

  5  FROM EMP

  6  WHERE EMPNO='&VS_EMPNO';

  7  DBMS_OUTPUT.PUT_LINE(V_ENAME);

  8* END;

SQL> /

Introduzca un valor para vs_empno: 7654

antiguo   6: WHERE EMPNO='&VS_EMPNO';

nuevo   6: WHERE EMPNO='7654';

Procedimiento PL/SQL terminado con éxito.