Transcription
Base de datos II. Guía ra: Base de datos IITema: PL/SQL y PROCEDIMIENTOS ALMACENADOSObjetivoConocer la definición y utilización de funciones y procedimientos en Oracle.Conocer la definición y utilización de las secuencias en Oracle.Materiales Oracle 12 c Guía Número 6IntroducciónSQL es un lenguaje de conjuntos muy poderoso, cuyo único objetivo es manipular el contenido de bases dedatos relacionales. Sin embargo, SQL no se puede utilizar para implementar toda la lógica de negocios y lafuncionalidad que el usuario final necesita en nuestras aplicaciones. Esto nos lleva a PL/SQL.PL/SQL significa Procedural Language/Structured Query Language (una extensión de programación estructuradasobre SQL). PL/SQL ofrece un conjunto de instrucciones clásicos de la programación estructurada (instruccióncondicional IF, loops o iteraciones, asignaciones), organizado dentro de bloques (lo que se explica másadelante), que complementan y amplían el alcance de SQL.Sin duda que es posible crear aplicaciones sobre Oracle y SQL sin usar PL/SQL. Sin embargo, utilizar PL/SQLpara realizar operaciones específicas de bases de datos, particularmente la ejecución de sentencias SQL, ofrecevarias ventajas, incluyendo una estrecha integración con SQL, un mejor rendimiento a través del tráfico de redreducido, y la portabilidad (los programas PL/SQL pueden correr en cualquier instancia de base de datosOracle). Por lo tanto, el código del front-end de muchas aplicaciones ejecuta tanto sentencias SQL como bloquesPL/SQL, para maximizar el rendimiento al tiempo que mejora la capacidad de mantenimiento de las aplicaciones.Construyendo bloques de programas PL/SQLPL/SQL es un lenguaje estructurado con bloques. Un bloque PL/SQL es definido por las palabras claveDECLARE, BEGIN, EXCEPTION, y END, que dividen el bloque en tres secciones1. Declarativa: sentencias que declaran variables, constantes y otros elementos de código, que después puedenser usados dentro del bloque2. Ejecutable: sentencias que se ejecutan cuando se ejecuta el bloque
3. Manejo de excepciones: una sección especialmente estructurada para atrapar y manejar cualquier excepciónque se produzca durante la ejecución de la sección ejecutableSólo la sección ejecutable es obligatoria. No es necesario que usted declare nada en un bloque, ni que manejelas excepciones que se puedan lanzar.Un bloque es en sí mismo una sentencia ejecutable, por lo que se pueden anidar los bloques unos dentro deotros.Ejemplos:El clásico “¡Hola Mundo!” es un bloque con una sección ejecutable que llama al procedimientoDBMS OUTPUT.PUT LINE para mostrar texto en pantalla:BEGINDBMS OUTPUT.put line('¡Hola Mundo!');END;Las funciones y procedimientos —tipos de bloques con un nombre— son discutidos con mayor detalle másadelante en este artículo, así como los paquetes. En pocas palabras, sin embargo, un paquete es un contenedorde múltiples funciones y procedimientos. Oracle extiende PL/SQL con muchos paquetes incorporados en ellenguaje.El siguiente bloque declara una variable de tipo VARCHAR2 (un string) con un largo máximo de 100 bytes paracontener el string ‘¡Hola Mundo!’. Después, el procedimiento DBMS OUTPUT.PUT LINE acepta la variable, enlugar del literal, para desplegarlo:DECLAREl mensaje VARCHAR2(100) : '¡Hola Mundo!';BEGINDBMS OUTPUT.put line(l mensaje);END;Note que he llamado a la variable l mensaje. Normalmente uso el prefijo l para variables locales —variablesdefinidas dentro del código de un bloque— y el prefijo g para variables globales definidas en un paquete.El siguiente ejemplo de bloque agrega una sección de manejo de excepciones que atrapa cualquier excepción(WHEN OTHERS) que pueda ser lanzada y muestra el mensaje de error, que es retornado por la funciónSQLERRM (provista por Oracle).DECLAREl mensaje VARCHAR2(100) : '¡Hola Mundo!';BEGINDBMS OUTPUT.put line(l mensaje);EXCEPTIONWHEN OTHERSTHENDBMS OUTPUT.put line(SQLERRM);END;El siguiente ejemplo de bloque demuestra la habilidad de PL/SQL de anidar bloques dentro de bloques así comoel uso del operador de concatenación ( ) para unir múltiples strings.
DECLAREl mensaje VARCHAR2(100) : '¡Hola';BEGINDECLAREl mensaje2 VARCHAR2(100) : l mensaje ' Mundo!';BEGINDBMS OUTPUT.put line(l mensaje2);END;EXCEPTIONWHEN OTHERSTHENDBMS OUTPUT.put line(DBMS UTILITY.format error stack);END;PROCEDIMIENTOS ALMACENADOSOracle 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 deprograma PL/SQL: Funciones y Procedimientos almacenados.Los procedimientos o funciones son bloques PL/SQL con nombre, que pueden recibir parámetros ypueden ser invocados desde distintos entornos: SQL*PLUS, Oracle*Forms, desde otrosprocedimientos 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 quelas funciones devuelven un valor.Procedimientos y funcionesSintaxis funciones
Descripción de la sintaxis:Nombre-parámetro: es el nombre que queramos dar al parámetro. Podemos utilizar múltiplespará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 unvalor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetrosson por defecto de tipo entrada.OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devueltodespués de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL noadmiten 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)
PROCEDIMIENTOEjemplo 1La Figura 1 muestra un ejemplo de ejecución del más simple de los bloques de ejemplo de nuestro “¡HolaMundo!” en SQL*Plus.Figura 1: Ejecutando “¡Hola Mundo!” en SQL*PlusLo primero que hacemos después de conectarnos a la base mediante SQL*Plus es habilitar la salida delservidor, por lo que las llamadas a DBMS OUTPUT.PUT LINE resultarán en la visualización de texto en lapantalla. Luego escribimos el código que constituye nuestro bloque. Finalmente, ingresamos una barra (/) paradecirle a SQL*Plus que ejecute ese código.Ejemplo 2Ejemplo 3
Ejemplo 4Procedimiento almacenado Hola MundoSi al final aparece un mensaje de Procedimiento compilado con advertencia para ver los erroresdigite show errors; para ver los errores que del códigoPara llamar al procedimiento se utiliza la siguiente sintaxisEjemplo 5
Ejemplo 5 utilizando parámetros de entradaEjemplo 6.Para este ejemplo crearemos la siguiente tablaAhora crearemos el siguiente procedimiento
En este procedimiento se ha definido el tipo de dato de los parámetros de entrada como del mismotipo que los campos de la tabla “empleado” , es decir: nombreParametro INnombreTabla.nombreColumna%TYPE. Sería equivalente a poner: w codigo emp number, w nombrevarchar2Para ejecutar el procedimiento también puede utilizar la siguiente sintaxtisEXECUTE insertar empleado(14,Juan,'11-05-2011');ANALISIS DE RESULTADOSCrear la siguiente tabla1. Crear un procedimiento almacenado llamada ingresoP1 que permita ingresar los datos adicha tabla, debe tomar en cuenta que la columna total es igual al precio por la cantidadCree la siguiente tablaAhora crearemos una secuencia para hacer el código autoincremental
Probando la secuencia2. Cree un nuevo procedimiento llamado ingreso pedido2, el procedimiento realizara la mismaacción del procedimiento ingresoP1, con la diferencia que cuando se inserten datos en latabla pedidos, también deberán insertarse datos en la tabla auditoria, para que quederegistro de las ventas totales de los productos vendidos
Tema: PL/SQL y PROCEDIMIENTOS ALMACENADOS Objetivo Conocer la definición y utilización de funciones y procedimientos en Oracle. Conocer la definición y utilización de las secuencias en Oracle. Materiales Oracle 12 c Guía Número 6 Introducción SQL es un lenguaje de conjuntos muy poderoso, cuyo único objetivo es manipular el contenido de .
