BIENVENIDOS.

Este Proyecto fue creado a fin de satisfacer las necesidades los usuario, el contenido que se encuentra es muy especifica, se trata de dar una breve pero clara informacion de los temas a tratar.

11 ago 2011

INTRODUCCIÓN

PL SQL
SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación.
Para abordar el presente tutorial con mínimo de garantias es necesario conocer previamente SQL.
   .
PL/SQL amplia SQL con los elementos caracteristicos de los lenguajes de programación, variables, sentencias de control de flujo, bucles ...
Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. PL/SQL es el lenguaje de programación que proporciona Oracle para extender el SQL estándar con otro tipo de instrucciones.

SECUENCIAS

Las secuencias son una solución fácil y elegante al problema de los codigos autogenerados.
LA sintaxis general es la siguiente:

CREATE SEQUENCE <secuence_name>
[MINVALUE <min_val>]
[MAXVALUE <max_val>]
[START WITH <ini_val>]
[INCREMENT BY <inc_val>]
[NOCACHE | CACHE <cache_val>][CYCLE]
[ORDER];

El siguiente ejemplo crea una secuencia SQ_PRODUCTOS.

CREATE SEQUENCE SQ_PRODUCTOS
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20; 

Se puede simplificar la orden, tomando los valores por defecto. El ejemplo anterior quedaría del siguiente modo:

CREATE SEQUENCE SQ_PRODUCTOS;

DECLARACION DE TABLAS


Las tablas de PL/SQL son tipos de datos que nos permiten almacenar varios valores del mismo tipo de datos.
Una tabla PL/SQL :
  • Es similar a un array
  • Tiene dos componenetes: Un índice de tipo BINARY_INTEGER que permite acceder a los elementos en la tabla PL/SQL y una columna de escalares o registros que contiene los valores de la tabla PL/SQL
  • Puede incrementar su tamaño dinámicamente.
La sintaxis general para declarar una tabla de PL es la siguiente:

TYPE <nombre_tipo_tabla> IS TABLE OF
<tipo_datos> [NOT NULL]
INDEX BY BINARY_INTEGER ;
Una vez que hemos definido el tipo, podemos declarar variables y asignarle valores.


DECLARE
   /* Definimos el tipo PAISES como tabla PL/SQL */
   TYPE PAISES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ; 
   /* Declaramos una variable del tipo PAISES */
   tPAISES PAISES;
BEGIN   tPAISES(1) := 1;
   tPAISES(2) := 2;
   tPAISES(3) := 3;
END;


No es posible inicializar las tablas en la inicialización.
El rango de binary integer es –2147483647.. 2147483647, por lo tanto el índice puede ser negativo, lo cual indica que el índice del primer valor no tiene que ser necesariamente el cero.

Tablas PL/SQL de registros
Es posible declarar elementos de una tabla PL/SQL como de tipo registro.


DECLARE
	
  TYPE PAIS IS RECORD 
  (
    CO_PAIS     NUMBER NOT NULL ,
    DESCRIPCION VARCHAR2(50),
    CONTINENTE  VARCHAR2(20)
  );  
  TYPE PAISES IS TABLE OF PAIS INDEX BY BINARY_INTEGER ;
  tPAISES PAISES;
BEGIN

  tPAISES(1).CO_PAIS := 27;
  tPAISES(1).DESCRIPCION := 'ITALIA';
  tPAISES(1).CONTINENTE  := 'EUROPA';
END;

OPERADORES

Tipo de operador Operadores
Operador de asignación := (dos puntos + igual)
Operadores aritméticos + (suma)
- (resta)
* (multiplicación)
/ (división)
** (exponente)
Operadores relacionales o de comparación = (igual a)
<> (distinto de)
< (menor que)
> (mayor que)
>= (mayor o igual a)
<= (menor o igual a)
Operadores lógicos AND (y lógico)
NOT (negacion)
OR (o lógico)
Operador de concatenación ||

DECLARACIÓN DE VARIABLES.

Tipos de Variables Escalares

Los principales tipos de variables escalares en Oracle PL/SQL son los siguientes:
VARCHAR2(tamaño)
NUMBER[(precisión,escala)]
DATE
CHAR(tamaño)
LONG
BOOLEAN
BINARY_INTEGER
  1. variable1 VARCHAR2(50);
  2. variable2 BINARY_INTEGER:=0;
  3. variable3 NUMBER(10,4);

Variables de tipo HOST

Las variables No-PL/SQL o de tipo HOST son variables globales que pueden usarse tanto dentro como fuera de los bloques PL/SQL.
Las referencias a estas variables vienen precedidas por dos puntos (:)
 
  1. VARIABLE total NUMBER(3,2);
  2. :total := 10/2;

Atributo %TYPE

El atributo %TYPE sirve para declarar una variable a partir de otras declaradas previamente, o a partir del tipo de una columna de la base de datos. Es decir, copiar el tipo de otra variable.
El siguiente ejemplo asigna a la variable1 el mismo tipo que el campo de la base de datos.
 
  1. variable1 tabla.campo%TYPE;

EXCEPCIONES PREDEFNIDAS Y DEFINIDAS POR EL USUARIO.

Excepciones predefinidas 
    PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales.
    Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado.
Excepciones definidas por el usuario 
    PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE.
    Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones.



DECLARE
MyExcepcion EXCEPTION;
BEGIN
  -- Ejecucion
EXCEPTION
  -- Excepcion
END;  -- Declaraciones












EXCEPCIONES.

Las excepciones sirven para tratar errores en tiempo de ejecucu¡ion, asi como errores y situaciones definifdas por el usuario. Caundo se produce un error. PL/SQL levanta un excepcion y pasa el control a la seccion  EXCEPTION  correspondiente del bloque PL.

DECLARE -- Declaraciones
BEGIN
  -- Ejecucion
EXCEPTION
  -- Excepcion
END;
    Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior.
    Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones.
    Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR.

CURSORES CON PARAMETRO.

Los cursores con parámetros son útiles cuando queremos restringir los resultados de la consulta, también son prácticos porque los declararemos como variables (no es necesario reescribir el código) y si no es necesario utilizar todas las columnas devueltas (como cuando se usa la sentencia FETCH.
  1.    CURSOR c_prueba(dato1 NUMBER, dato2 VARCHAR2) IS
  2.       SELECT s.col1, s.col2, s.col3
  3.         FROM sales s
  4.        WHERE s.YEAR = dato1
  5.          AND s.status = dato2; 
  6.    
  7.    DBMS_OUTPUT.put_line('Ventas del 2007 que tengan estado CERRADO ');
  8.    --Noten que no es necesario definir la variable wc_cursor
  9.    FOR wc_cursor IN c_prueba(2007, 'CERRADO') LOOP
  10.       DBMS_OUTPUT.put_line('col1 '||wc_cursor.col1||' col2 '||wc_cursor.col2);
  11.    END LOOP;

ATRIBUTOS DEL CURSOR.

Para conocer detalles respecto a la situacion  del cursor hay cuatro atributos para consulta.
%FOUND .
Devuelve verdadero si el ultimo  FETCH ha recuperado algun valor; en caso contrario , devuelve faldo. Si el cursor no estaba abierto  devuelve error y si estaba abierto pero no estaba ejecutado aùn ninun  FETCH  devuelve NULL. Se suele utiliar como condicion de continuacion en bucles para recuperar informacion.

  • %NOTFOUND: hace justamente lo contrario al anterior.

  • %ROWCOUNT: nos devuelve el número de filas recuperadas hasta el momento.

  • DECLARE
          CURSOR C1 IS SELECT nombre from futbolista WHERE Cod='e1';
          Vnom VARCHAR2(15);
       BEGIN
          OPEN C1;
          LOOP
             FETCH C1 INTO Vnom;
             EXIT WHEN C1%NOTFOUND;        
             DBMS_OUTPUT.PUT_LINE (C1%ROWCOUNT || Vnom);
            
          END LOOP;
          CLOSE C1;
       END;


  • %ISOPEN: devuelve verdadero si el cursor esta abierto.


  • CURSORES EXPLICITOS EN PL/SQL.

      Los cursores explicitos se emplean para realizar consultas SELECT que pueden devolver cero filas, o más de una fila.
        Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:
    • Declarar el cursor.
    • Abrir el cursor con la instrucción OPEN.
    • Leer los datos del cursor con la instrucción FETCH.
    • Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
        Para declarar un cursor debemos emplear la siguiente sintaxis:

    CURSOR nombre_cursor IS
         instrucción_SELECT 


        También debemos declarar los posibles parametros que requiera el cursor:


    CURSOR nombre_cursor(param1 tipo1, ..., paramN tipoN) IS
         instrucción_SELECT 


        Para abrir el cursor


         OPEN nombre_cursor;
         o bien (en el caso de un cursor con parámetros)
         OPEN nombre_cursor(valor1, valor2, ..., valorN);

    CURSORES IMPLICITOS.

      Los cursores implicitos se utilizan para realizar consultas SELECT que devuelven un único registro.
       Deben tenerse en cuenta los siguientes puntos cuando se utilizan cursores implicitos:
    • Con cada cursor implicito debe existir la palabra clave INTO.
    • Las variables que reciben los datos devueltos por el cursor tienen que contener el mismo tipo de dato que las columnas de la tabla.
    • Los cursores implicitos solo pueden devolver una única fila. En caso de que se devuelva más de una fila (o ninguna fila) se producirá una excepcion. No se preocupe si aún no sabe que es una excepcion, le valdrá conocer que es el medio por el que PL/SQL gestiona los errores.
       El siguiente ejemplo muestra un cursor implicito:
     
    Excepciones asociadas a los cursores implicitos.
       Los cursores implicitos sólo pueden devolver una fila, por lo que pueden producirse determinadas excepciones. Las más comunes que se pueden encontrar son no_data_found y too_many_rows.

    Excepcion
    Explicacion
    NO_DATA_FOUNDSe produce cuando una sentencia SELECT intenta  recuperar datos pero ninguna fila satisface sus condiciones. Es decir, cuando "no hay datos"
    TOO_MANY_ROWSDado que cada cursor implicito sólo es capaz de recuperar una fila , esta excepcion detecta la existencia de más de una fila.



    
    declare50);
    begin       SELECT DESCRIPCION 
           INTO vdescripcion
           from PAISES
           WHERE CO_PAIS = 'ESP';
           
           dbms_output.put_line('La lectura del cursor es: ' || vdescripcion);
    
    end;
    vdescripcion VARCHAR2(

    USO DE CURSORES.

    Cursores en SQL Server

    Los cursores son una herramienta de SQL que nos permite recorrer el resultado de una consulta SQL y realizar operaciones en cada paso de ésta.
    Es así como nos ayuda a realizar operaciones que de otro modo serían más complejas o irrealizables. A continuación coloco el código de un cursor muy simple para el Analizador de Consultas de SQL Server.
    DECLARE
        Mi_Cursor
    FOR
       SELECT num_emp, nombre, puesto, salario
       FROM empleados
       WHERE num_dept = 'informatica'

    10 ago 2011

    BLOQUES PL/SQL.

    Con  PL/SQL se  pueden construir distintos tipos de programas :procedimientos, funciones,etc.
    todos ellos tienen en común una estructura básica característica del lenguaje denominada BLOQUEO.
    Un bloque tiene tres zonas claramente definidas:
    Una zona de DECLARACIONES donde se declaran objetos (variables, constrantes,etc.)locales. suele ir por la clausura DECLARE (o IS/AS en el procedimiento y funciones). Es opcional.
    Un conjunto de INSTRUCCIONES preceddipor la cláusura BEGIN.
    Una zona de tratamiento de EXCEPCIONES procedido por la cláusura EXCEPCION.


    DECLARE
    <declaraciones<}
    BEGIN
    <ordenes de excepciones<}
    END
    /*La zona de la declaraciones comenzara con DECLARE o con IS
    dependiendo del tipo de bloque.

    CARACTERÍSTICAS DEL LENGUAJE PL/SQL.

    Es un Lenguaje Procedimental deseñado por ORACLE para trabajar con la Base de Datos. Esta incluido en el servicio y en halgunas herramientas de cliente.
    Soporta todos los  comandos de consultas y manipulación de datos , aportando al lenguaje SQL las estructuras de control (bucles, bifurcaciones,etc.) y otros elementos propios de los lenguajes procedimentales de tercera generación.
    Su unidad de trabajo es el bloque, constituido por un conjunto de declaraciones, instrucciones y mecanismos gestión de errores y excepciones.

    ¿Qué es PL/SQL?

    PL/SQL Es una extención se SQL con las características de diseño de lenguajes de programación.
    La manipulacion de datos y declaracion de consultas son incluidas dentro de las unidades procesales de código.

    TIPOS DE DATO EN PL/SQL.

    Cada constante y variable tien un tipo de dato en el cual se especifica el formato de almacenamiento, restricciones y rango de valores validos.

    PL/SQL proporciona una variedad predefinida de tipos de datos . Casi todos los tipos de datos manejados por PL/SQL son similares a los soportados por SQL. A continuación se muestran los TIPOS de DATOS más comunes:

    NUMBER (Numérico): Almacena números enteros o de punto flotante, virtualmente de cualquier longitud, aunque puede ser especificada la precisión (Número de digitos) y la escala que es la que determina el número de decimales.
    -- NUMBER [(precision, escala)]
    saldo NUMBER(16,2); 
    /* Indica que puede almacenar un valor numérico de 16 
       posiciones, 2 de ellas decimales. Es decir, 14 enteros 
       y dos decimales */
  • CHAR (Caracter): Almacena datos de tipo caracter con una longitud maxima de 32767 y cuyo valor de longitud por default es 1

    
    -- CHAR [(longitud_maxima)]
    nombre CHAR(20);
    /* Indica que puede almacenar valores alfanuméricos de 20 
       posiciones */

  • VARCHAR2 (Caracter de longitud variable): Almacena datos de tipo caracter empleando sólo la cantidad necesaria aún cuando la longitud máxima sea mayor.

    
    -- VARCHAR2 (longitud_maxima)
    nombre VARCHAR2(20);
    /* Indica que puede almacenar valores alfanuméricos de hasta 20
       posicones */
    /* Cuando la longitud de los datos sea menor de 20 no se 
       rellena con blancos */

  • BOOLEAN (lógico): Se emplea para almacenar valores TRUE o FALSE.

    hay_error BOOLEAN; 

  • DATE (Fecha): Almacena datos de tipo fecha. Las fechas se almacenan internamente como datos numéricos, por lo que es posible realizar operaciones aritmeticas con ellas.