PL/SQL

UIPR

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PL/SQL (SINTAXIS)

 

•       La estructura de una rutina de PL/SQL es como sigue.

        DECLARE

                /* Sección declarativa, se declaran las variables de PL/SQL, “types”, “cursors” */

        BEGIN

                /* Sección ejecutable, aquí van los comandos de PL/SQL y de SQL */

        EXCEPTION

                /* Sección de manejo de errores, es opcional.

                Errores customizados de –20000 hasta –20999. */

        END;

                -- Especifica que es el final de la rutina de PL/SQL.

        /       -- Debe estar luego del END.

 

PL/SQL (ESTRUCTURA)

 

•      Se pueden crear variables de diferentes tipos:

•    NUMBER – valores numéricos (E.g. en caso de decimales el formato es NUMBER (7,2), 7 lugares de los cuales 2 son decimales; 99999.99)

•    VARCHAR2 – valores alfanuméricos; si los valores no ocupan todo el espacio del campo, ORACLE solo utiliza el el espacio del dato.

•    CHAR – valores alfanuméricos; si los valores no ocupan todo el espacio del campo, ORACLE rellena con espacios en blanco.

•    DATE – fechas y tiempo (E.g. 22-JUL-2002)

•    %TYPE – toma el tipo de variable en la tabla.

•    %ROWTYPE – toma la tabla completa.

•      Se pueden crear estructuras de control   

•    IF-THEN-ELSE  ELSIF

•    LOOP Y WHILE

 

 

PL/SQL (“BLOCKS”)

 

•      “Anonymous Blocks” – son dinámicos y ejecutan una sola vez.

 

Ej.

DECLARE

BEGIN

END ;

/

 

•      “Named Blocks” – son iguales a los anónimos la diferencia es que se le da un nombre.

 

Ej.

<<insert_into>>

DECLARE

BEGIN

END insert_into;

/

 

PL/SQL (“BLOCKS”)

 

•      “Subprograms” – son “Procedures”, “Packages”, “Functions” que se almacenan en la base de datos.  Estos tienen nombres, y pueden ser ejecutados llamándolos en la aplicación.

•      “Triggers” – son “blocks” que se almacenan en la base de datos con un nombre; a diferencia de los “Subprograms”,  estos ejecutan de acuerdo a un evento estipulado en su programación.

 

PL/SQL (LOOPS)

DECLARE

inscount integer := 0;

BEGIN

WHILE inscount < 10 LOOP

insert into corredord values (sysdate, user);

inscount := inscount + 1;

END LOOP;

END;

/

DECLARE

inscount integer := 0;

BEGIN

LOOP

insert into corredord values (sysdate, user);

inscount := inscount + 1;

EXIT WHEN inscount = 10;

END LOOP;

END;

/

 

PL/SQL (Ejemplo Variables)

DECLARE

   id VARCHAR2(14):=  ‘111223333';

   ins VARCHAR2(25);

BEGIN

   select asegurado INTO ins from corredor

   where id_cor = id;

  IF ins = 'NO' THEN

    DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||id||' no está

    asegurado.');

  ELSE

    DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||id||' puede correr.');

  END IF;

EXCEPTION

   WHEN no_data_found THEN

   raise_application_error (-20011, 'El corredor no existe.');

END;

/

 

PL/SQL (Ejemplo Variables %TYPE)

 

DECLARE

   id VARCHAR2(14):= '111223333';

   ins corredor.asegurado%TYPE;

BEGIN

   select asegurado INTO ins from corredor

   where id_cor = id;

  IF ins = 'NO' THEN

   DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||id||' no está asegurado.');

  ELSE

   DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||id||' puede correr.');

  END IF;

EXCEPTION

   WHEN no_data_found THEN

   raise_application_error (-20011, 'El corredor no existe.');

END;

/

 

PL/SQL (Ejemplo Variables %ROWTYPE)

 

DECLARE

   corredorpuede corredor%ROWTYPE;

BEGIN

   select * INTO corredorpuede from corredor

   where id_cor = '111223333';

  IF corredorpuede.asegurado = 'NO' THEN

    DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||corredorpuede.id_cor|| ' llamado ' ||corredorpuede.nombre|| ' no está asegurado.');

  ELSE

    DBMS_OUTPUT.PUT_LINE('El corredor con el seguro social '||corredorpuede.id_cor|| ‘llamado ' ||corredorpuede.nombre|| ' está asegurado.');

  END IF;

EXCEPTION

   WHEN no_data_found THEN

   raise_application_error (-20011, 'El corredor no existe.');

END;

/

 

PL/SQL (CURSORES)

 

CURSOR – Cuando se procesa un “SQL Statement” ORACLE lo distribuye en un área de memoria conocida como el “context area”.  El cursor actua como un manejador de “context area”.  Por medio del cursor, un programa PL/SQL puede controlar el “context area” y lo que le sucede mientras el “statement” del cursor es procesado.

Cursor Explícito – es aquel que se le dá un nombre el cual realiza la ejecución por medio de un SELECT.

Cursor Implícito – es aquel que se utiliza para los “SQL Statement” de INSERT, UPDATE, DELETE. Los SELECT deben ser de una sola fila de resultado.

 

PL/SQL (CURSORES SYNTAXIS)

 

CURSOR EXPLICITO – la creación de un cursor explícito tiene cuatro pasos.

•             DECLARE -  aquí se le da nombre al cursor asociándolo con un SELECT.

•             OPEN - abrir el cursor para la búsqueda de datos.

•             FETCH – desplega el resultado de los datos obtenidos por el cursor en una variable.

•             CLOSE – es importante cerrar el cursor ya que su funcionamiento se basa en un “LOOP”.

•             De la 2 a la 4 van entre el BEGIN y END de la estructura PL/SQL.

 

 

PL/SQL (CURSORES DETALLES)

 

•             Las variables que van a utilizar los cursores deben estar en la sección DECLARE.

•             Las variables que se declaran con %TYPE deben ir antes de la declaración del cursor.

•             Las varibles que se declaran con %ROWTYPE deben ir después de la declaración del cursor.

 

PL/SQL (CURSORES Ejemplo %TYPE)

 

DECLARE

     c_id_cor corredor.id_cor%TYPE;

     c_nombre corredor.nombre%TYPE;

     cursor prueba_cur IS

     select id_cor, nombre from corredor;

BEGIN

  OPEN prueba_cur;

    LOOP

      FETCH prueba_cur INTO c_id_cor, c_nombre;

      EXIT WHEN prueba_cur%NOTFOUND;

      dbms_output.put_line(c_id_cor||' '|| c_nombre||' '||user);

    END LOOP;

  CLOSE prueba_cur;

END;

/

 

PL/SQL (CURSORES Ejemplo %ROWTYPE)

 

DECLARE

     cursor prueba_cur IS

     select * from corredor;

     corredores corredor%ROWTYPE;

BEGIN

  OPEN prueba_cur;

    FETCH prueba_cur INTO corredores;

      WHILE prueba_cur%FOUND LOOP

          dbms_output.put_line(corredores.id_cor||' '||  corredores.nombre||' '||user);

    FETCH prueba_cur INTO corredores;

       END LOOP;

  CLOSE prueba_cur;

END;

/

 

PL/SQL (CURSORES Ejemplo EXCEPTION)

 

DECLARE

     CURSOR prueba_cur IS

     select * from inscripcion;

     inscripciones inscripcion%ROWTYPE;

     invalidvalue EXCEPTION;

BEGIN

  OPEN prueba_cur;

    FETCH prueba_cur INTO inscripciones;

       IF prueba_cur%NOTFOUND THEN

        RAISE invalidvalue;

       END IF;

    WHILE prueba_cur%FOUND LOOP

      dbms_output.put_line(inscripciones.fecha_com||' '|| inscripciones.costo||' '||user);

    FETCH prueba_cur INTO inscripciones;

    END LOOP;

  CLOSE prueba_cur;

EXCEPTION

  WHEN invalidvalue THEN

  raise_application_error (-20111, 'No hay valores en la tabla.');

END;

/

 

PL/SQL (PROCEDURE SINTAXIS)

 

     Cuando creamos un “PROCEDURE” la estructura general es como sigue:

 

CREATE or REPLACE PROCEDURE ProcedureName(

     p_variable1 IN OUT date,

     p_variable2 IN OUT number,

     p_variable3 IN OUT varchar2,

       p_variableN IN OUT tablename.column%TYPE) AS

BEGIN

     insert into tablename VALUES (p_variable1, p_variable2, p_variable3, p_variableN);

  COMMIT;

END ProcedureName;

/

 

PL/SQL (PROCEDURE DETALLES)

 

•             En los “PROCEDURES” no se utiliza %ROWTYPE.

•             La variable se declara IN OUT cuando por medio del “PROCEDURE” tanto se insertan o se obtienen datos de esa variable en las tablas.

•             La variable se declara IN cuando por medio del “PROCEDURE” solo se insertan datos de esa variable en las tablas.

•             La variable se declara OUT cuando por medio del “PROCEDURE” solo se obtienen datos de esa variable en las tablas.

•             El “PROCEDURE” se llama desde una programación.

 

PL/SQL (PROCEDURE Ejemplo)

 

CREATE or REPLACE PROCEDURE AddNewIns(

     p_fecha IN date,

     p_costo IN number,

     p_id IN varchar2,

     p_fecha_com IN date) AS

BEGIN

     insert into inscripcion values (p_fecha, p_costo, p_id, p_fecha_com);

  COMMIT;

END AddNewIns;

/

 

PL/SQL (PROCEDURE LLAMARLO)

 

DECLARE

     p_cant NUMBER(1) := 1;

     p_precio NUMBER(10) := 15;

     p_costo NUMBER(10) := p_cant * p_precio;

BEGIN

     AddNewIns(SYSDATE, p_costo, ‘222334444', '15-DEC-02');

END;

/

 

PL/SQL (TRIGGERS SINTAXIS)

 

CREATE or REPLACE TRIGGER TriggerName

     before insert or update or delete ON tablename

     for each row

BEGIN

     insert into tablename values (sysdate, user);

END TriggerName;

/

 

PL/SQL (TRIGGERS DETALLES)

 

•              INSERT, UPDATE, DELETE son los DML que se especifican para que se dispare “TRIGGER”, existen otros más avanzados.

•              BEFORE, AFTER especifica cuando en la ocurrencia del DML se dispare el “TRIGGER”.

•              Los “TRIGGERS”  se pueden desactivar o reactivarlos.

        alter TRIGGER triggername disable;

        alter TRIGGER triggername enable;

•              Para saber si un TRIGGER está activo:

     SELECT status from user_triggers

     where trigger_name = UPPER(‘triggername’);  El UPPER es preventivo por si se crean “TRIGGERs” en letras minúsculas.  ORACLE trae los datos de las tablas que crea se crean en ORACLE en mayúsculas, aunque se hayan creado en minúsculas por el usuario.  (No se confunda; las tablas que usted crea le trae los valores como fueron insertados en las tablas).

 

 

PL/SQL (TRIGGERS Ejemplo)

 

CREATE or REPLACE trigger AlterCorredor

     before insert or update or delete on corredor

     for each row

BEGIN

     insert into corredord values (SYSDATE, USER);

END AlterCorredor;

/

PL/SQL (“Debugging”)

 

Cuando creamos un objeto de PL/SQL y nos presenta un “Warning” escribimos:

     Select * from user_errors;

     Con esto recibimos un “feedback” de que estamos haciendo mal en la codificación.