Procedimientos almacenados

Publicado: julio 13, 2011 en Oracle

PL/SQL

PL/SQL
¿Qué es PL/SQL?

  • Lenguaje de procesamiento procedimental.
  • Implementado por Oracle.
  • Dispone de estructuras de programación similares a las de la mayoría de los lenguajes de programación.
  • Objetivo: Interactuar con la B.D.

PL/SQL
Estructura Bloques Anónimos

  • [DECLARE]
    Define objetos PL/SQL que serán utilizados
    dentro del mismo bloque
  • BEGIN
    Sentencias Ejecutables
  • [EXCEPTION]
    Qué hacer si la acción ejecutada causa error
  • END;

PL/SQL
Estructura de Procedimiento

  • PROCEDURE nombre IS
    Sección Declarativa
  • BEGIN
    Sección Ejecutable
  • [EXCEPTION]
    Sección de Excepciones
  • END;

PL/SQL
Estructura de Función

  • FUNCTION nombre RETURN tipo_dato IS
    Sección Declarativa
  • BEGIN
    Sección Ejecutable
  • [EXCEPTION]
    Sección de Excepciones
  • END;

PL/SQL
Declaración de Variables

  • Reglas para nombres:
    • Dos variables pueden tener el mismo nombre, si están en bloques diferentes.
    • El nombre de la variable (identificador) no debería ser el mismo que el de una columna de una tabla utilizada en el bloque.

PL/SQL
Asignación e Inicialización de Variables

  • Asignación:

  • Inicialización:

  • Por defecto, todas las variables se inicializan a NULL.

PL/SQL
Tipos de Variables

  • ESCALARES
  • BOOLEANAS
  • COMPUESTAS
  • LOB
  • DE ENLACE (BIND)

PL/SQL
Variables Escalares

  • VARCHAR2 (longitud_máxima)
  • NUMBER [(precisión, escala)]
  • DATE
  • CHAR [(longitud_máxima)]
  • LONG
  • LONG RAW
  • BOOLEAN
  • BINARY_INTEGER

PL/SQL
Atributo %TYPE

  • Permite declarar una variable basada en:
    • Otras variables previamente declaradas
    • La definición de una columna de la base de datos
  • Preceder de %TYPE por:
    • La tabla y la columna de la base de datos
    • El nombre de la variable definida con anterioridad
  • Ejemplo:
    v_ename
    emp.ename%TYPE;

PL/SQL
Variables BOOLEANAS

  • A una variable Boolean sólo se le pueden asignar los valores: TRUE, FALSE o NULL.
  • Estas variables están conectadas por los operadores lógicos AND, OR y NOT.

PL/SQL
Tipos de Datos Compuestos

  • Tipos:
    • REGISTROS PL/SQL
    • TABLAS PL/SQL
  • Contienen componentes internos

PL/SQL
Creación de un Registro

  • Sintaxis:

  • Donde declaración_campo significa:

PL/SQL
Ejemplo de Registro PL/SQL

  • Declarar un registro para almacenar el número de empleado, nombre, trabajo y sueldo de un nuevo empleado:

  • Los componentes individuales del registro se referenciarán de forma cualificada; en este ejemplo:

PL/SQL
El Atributo %ROWTYPE

  • Define un registro con la estructura de la tabla o vista de la B.D.
  • Los campos del registro toman sus nombres y tipos de datos de las columnas de la vista o tabla.
  • Ejemplo:
    DECLARE registro_empleado emp%ROWTYPE;

PL/SQL
Ventajas de %ROWTYPE

  • El número y los tipos de datos de las columnas de la tabla pueden no ser conocidos.
  • Simplifica la programación al no tener que definir explícitamente los campos y tipos del registro.
  • Es útil para realizar recuperaciones de filas con la sentencia SELECT:

PL/SQL
Tablas PL/SQL

  • Cuentan con dos componentes:
    • TIPO DE DATOS DE CLAVE PRIMARIA BINARY_INTEGER
    • COLUMNA DE TIPO DE DATOS ESCALARES O DE REGISTRO.
  • Aumentan dinámicamente porque no tienen restricciones.
  • Se almacenan en memoria.

PL/SQL
Creación de Tablas PL/SQL

  • Sintaxis:

  • Ejemplo:

PL/SQL
Creación de Tablas PL/SQL

PL/SQL
Variables LOB

  • Permiten almacenar bloques de datos no estructurados, como pantallas de texto, imágenes gráficas, vídeo clips, y sonido, de hasta 4 Gb. de tamaño.

CLOB
Character Large Object. Se utiliza para almacenar bloques grandes de datos de caracteres.

BLOB
Binary Large Object. Se utiliza para almacenar objetos binarios grandes en la B.D.

BFILE
Binary File. Se utiliza para almacenar objetos binarios grandes en archivos del sistema operativo, fuera de la B.D.

NCLOB
National Language Character Large Object. Se utiliza para almacenar en la B.D. bloques grandes de datos NCHAR de un byte único o multi-bytes de ancho fijo.

PL/SQL
Variables de Enlace (Bind)

  • Son variables de SQL*Plus.
  • Las pueden referenciar bloques de PL/SQL mediante el uso del ampersand (&).
  • Ejemplo:

 

GENERALIDADES DE PL/SQL

PL/SQL
Generalidades de PL/SQL (I)

  • Comentarios en PL/SQL:
    • Anteponga a los comentarios de una sola línea dos guiones (–) a modo de prefijo.
    • Coloque comentarios de varias líneas entre /* y */.
  • Operadores de PL/SQL:

**, NOT
Exponenciación, negación lógica

+, –
Identidad, negación

*, /
Multiplicación, división

+, -, ||
Suma, resta, concatenación

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
Comparación

AND
Conjunción

OR
Inclusión

PL/SQL
Generalidades de PL/SQL (II)

  • Bloques Anidados y Ámbito de la Variable:

PL/SQL
Generalidades de PL/SQL (III)

  • Para hacer referencia a una variable de sustitución en PL/SQL debe anteponer a su nombre dos puntos (:) a modo de prefijo:

 

Generalidades de PL/SQL (IV)

  • Directrices de Programación para facilitar el mantenimiento del código:
    • Documente el código con comentarios.
    • Desarrolle una convención de mayúsculas/minúsculas para el código.
    • Desarrolle convenciones de nomenclatura para identificadores y otros objetos.
    • Sangre el código para facilitar la lectura.
    • Evite la ambigüedad entre variables locales, parámetros formales y nombres de columnas de las tablas de la B.D.

PL/SQL
Generalidades de PL/SQL (V)

  • Cuando trabaje con valores nulos puede evitar algunos de los errores más habituales si recuerda las siguientes reglas:
    • Las comparaciones en las que se incluyen valores nulos siempre resultan NULL.
    • Si se aplica el operador lógico NOT a un valor nulo resulta NULL.
    • En las sentencias de control condicionales, si la condición resulta NULL, no se ejecutarán las sentencias asociadas.

PL/SQL
Sentencias SQL en PL/SQL

  • SELECT recupera exactamente UNA fila.
  • INSERT añade una fila.
  • UPDATE modifica una o más filas existentes.
  • DELETE suprime una o más filas existentes.
  • COMMIT hace permanentes todas las modificaciones pendientes.
  • ROLLBACK elimina todas las modificaciones pendientes.
  • SAVEPOINT marca un punto intermedio en el procesamiento de las transacciones.

PL/SQL
Sentencia SELECT

  • Sintaxis:

  • Recuerde, sólo se debe de recuperar una fila. Más de una fila provocará errores.

PL/SQL
Ejemplo de SELECT

  • Recupere la suma de los salarios de todos los empleados de un departamento específico:

PL/SQL
Inserción de Datos

  • Añada nueva información sobre los empleados en la tabla emp:

PL/SQL
Actualización de Datos

  • Aumente el salario de todos los empleados de la tabla emp que son Analistas:

PL/SQL
Supresión de Datos

  • Suprima filas pertenecientes al departamento 10 de la tabla emp:

PL/SQL
Control de Transacciones

  • COMMIT finaliza la transacción actual realizando todos los cambios pendientes en la B.D.

  • ROLLBACK finaliza la transacción actual desechando todos los cambios pendientes.

PL/SQL
Control de Transacciones

PL/SQL
Control Flujo de Ejecución

  • Puede modificar el flujo lógico de sentencias utilizando sentencias IF condicionales y estructuras de control de bucles.
  • Sentencias IF condicionales:
    • IF-THEN
    • IF-THEN-ELSE
    • IF-THEN-ELSIF
  • Control de bucles:
    • Bucle básico LOOP
    • Bucle FOR
    • Bucle WHILE

PL/SQL
Sentencia IF

  • Sintaxis:

PL/SQL
Flujo de IF-THEN-ELSE

PL/SQL
Flujo de IF-THEN-ELSIF

PL/SQL
Condiciones Booleanas

PL/SQL
Bucle Básico: LOOP

  • Sintaxis:

  • Donde condición es una expresión o variable booleana (TRUE, FALSE o NULL).

PL/SQL
Bucle FOR

  • Sintaxis:

  • No declare el índice; se declara implícitamente como un BINARY_INTEGER. Fuera del bucle el índice no está definido.
  • Los límites desde..hasta deben de ser literales numéricos. Pueden ser expresiones que se convierten en valores numéricos.

PL/SQL
Bucle WHILE

  • Sintaxis:

  • La condición se evalúa al inicio de cada iteración

PL/SQL
Etiquetas y Loops Anidados

  • Puede anidar bucles a varios niveles.
  • Utilice etiquetas para distinguir entre los bloques y los bucles.
  • Salga al bucle externo con la sentencia EXIT que hace referencia a la etiqueta.
  • Los nombres de etiquetas deben ir antes de la palabra LOOP y entre
    los delimitadores << >>.

PL/SQL
Etiquetas y Loops Anidados

  • Ejemplo:

 

 

 

CURSORES

PL/SQL
Definición de Cursor

  • Útiles para las consultas que devuelven más de una fila.
  • Son declarados y nombrados por el programador, y manipulados por medio de sentencias específicas en las acciones ejecutables del bloque.

PL/SQL
Control de Cursores

  • 1º. Crear un área SQL específica DECLARE
  • 2º. Identificar el juego activo OPEN
  • 3º. Cargar la fila actual en variables FETCH
  • 4º. Si todavía existen filas sin leer, volver a 3º.
  • 5º. Si no existen más filas a leer CLOSE

PL/SQL
Declaración del Cursor

  • Sintaxis:

  • No incluya la cláusula INTO en la declaración del cursor.
  • Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.

PL/SQL
Ejemplo de Declaración

PL/SQL
Apertura del Cursor

  • Sintaxis:

  • Si la consulta no devuelve ninguna fila, no se producirá ninguna excepción al abrir el cursor.
  • Para los cursores declarados con la cláusula FOR UPDATE, la sentencia OPEN bloquea estas filas.

PL/SQL
Recuperación de Datos

  • Sintaxis:

  • Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y en el mismo orden.
  • Compruebe si el cursor tiene filas.

PL/SQL
Cierre del Cursor

  • Sintaxis:

  • Cierre el cursor una vez completado el procesamiento de las filas.
  • Si es necesario, vuelva a abrir el cursor.
  • No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR).

PL/SQL
Atributos de Cursores

  • Proporcionan información de estado del cursor:

PL/SQL
El Atributo %ISOPEN

  • Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperación para comprobar si el cursor está abierto.
  • Ejemplo:

PL/SQL
Atributos %NOTFOUND, %ROWCOUNT

  • Utilice %ROWCOUNT para recuperar un número exacto de filas.
  • Utilice %NOTFOUND para determinar cuándo salir del bucle de lectura del cursor.
  • Antes de la primera recuperación, %NOTFOUND es NULL, así que si FETCH no se ejecuta nunca satisfactoriamente, no se saldría nunca del bucle de lectura.

PL/SQL
Ejemplo %NOTFOUND, %ROWCOUNT

  • Ejemplo:

PL/SQL
Bucles FOR de Cursor

  • Sintaxis:

  • Apertura, recuperación y cierre implícitos.
  • No declare el registro, está declarado implícitamente.

PL/SQL
Cursores con Parámetros

  • Sintaxis:

  • Permite abrir un cursor varias veces con un juego activo distinto cada vez.

PL/SQL
Cursores con Parámetros

  • Cada parámetro formal de la declaración del cursor debe tener un parámetro real correspondiente en la sentencia OPEN.
  • La sintaxis de los parámetros es:

PL/SQL
Cursores con Parámetros

  • Transfiera el número de departamento y el cargo a la cláusula WHERE:

PL/SQL
Cláusula FOR UPDATE

  • Sintaxis:

  • El bloqueo explícito le permite denegar el acceso mientras dura una transacción.
  • Bloquee las filas antes de la actualización o supresión.
  • La cláusula FOR UPDATE es la última cláusula de una sentencia SELECT, incluso después del ORDER BY.
  • NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesión, de lo contrario se espera.

PL/SQL
Cláusula WHERE CURRENT OF

  • Sintaxis:

  • Incluya la cláusula FOR UPDATE en la definición del cursor para bloquear las filas.
  • Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila actual del cursor.
Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s