PL / SQL

Los guiones PL/SQL tienen siempre la misma estructura. Separando en bloques cada parte del guión

  DECLARE
   -Variables, cursores, etc.
  BEGIN 
    -Parte ejecutable
    -Sentencias SQL
    -Estructuras de control
    -etc
  EXCEPTION
     -Que hacer en caso de que falle algo
  END;
  /

• En la sección de DECLARE es donde se declaran las variable. Es la cabecera
• Dentro del BEGIN es donde va el programa en si. Es donde pasan cosas
• EXCEPTION nos sirve para especificar que acciones tomar en caso de que se produzca una excepción. En caso de que falle algo.
• END; indica el final del bloque o guión
• / es el comando que ejecuta el guión. si no pones el / no se ejecutará. Es como el “enter”

Todo guión PL/SQL tiene uno o mas bloques. Estos bloques pueden estar totalmente separados en distintos ficheros o en el mismo o incluso anidados uno detro de otro. Todo se estructura en bloques lógicos que empiezan con el Hay 3 tipos de bloques:

Son bloques sin nombrar. Normalmente el bloque principal es un bloque anónimo.

    [ DECLARE ]
    BEGIN
      --Acciones
    EXCEPTION
    END;

Subprogramas, como en todas partes.

  PROCEDURE nombre_de_procedimiento
  IS
  BEGIN
    --Acciones
  [ EXCEPTION ]
  END;

Subprogramas que devuelven un valor, como en todas partes.

  FUNCTION nombre_de_la_funcion
  RETURNS tipo_de_dato
  IS
  BEGIN
    --Acciones
  RETURN valor;
  [ EXCEPTION ]
  END;

En PL/SQL hay tantos tipos de variables como tipos de datos admite Oracle. Se declaran en el bloque DECLARE y se pueden usar a lo largo de todo el bloque. Aún así se pueden distinguir 4 tipos básicos de variables PL/SQL

• ESCALARES : Contienen valores únicos. Y admiten todo tipo de datos, incluso boleanos.
• COMPUESTAS : Contienen valores compuestos. Como por ejemplo registros. Permiten manipular grupos de campos.
• REFERENCIADAS : Punteros.
• LOB : Contienen objetos LOB (grandes).

nombre  [ CONSTANT ] tipo_de_dato [ NOT NULL ] := valor

v_aniversario  DATE;
v_nombre       VARCHAR2(30) := 'juantxu';
c_cualidad CONSTANT VARCHAR2(12) := 'inteligente';

Existe una convención (que puedes seguir o no ) para poner una v_ delante de nombres de variables variables y una C_ delante de nombres de variables constantes. De ese modo es muy facil identificarlo y además conseguimos que no se solapen los nombres de las v_varialbes con los nombres de las columnas, ya que normalmente tenderemos a ponerle el mismo nombre.

Es un atributo que siver para declarar una variable basada en otra previamente declarada o en la definición de una columna de la base de datos. Sirve para decir: Esta variable es del mismo tipo que esta otra

v_nombre    jefes.nombre%TYPE;
v_balance   NUMBER(7,2);
v_como_balance  v_balance%TYPE

Oracle no admite booleanos como tipo de datos (Te apañas con un numero puesto a 0 o 1) Pero PL/SQL si que admite booleanos. Estos se asignan siempre como resultado de una operación lógica. Y valen siempre TRUE , FALSE o NULL

v_sal1 := 100;
v_sal2 := 200;
v_boleano BOOLEAN := ( v_sal1 < v_sal2);

Para transferir valores a subprogramas se usan los parametros IN , OUT e IN OUT.

Es una variable que se declara en el entorno del servidor y se puede utilizar despues para transmitir valores de ejecución. Por ejemplo para sacar por pantalla el valor de una variable. Son Variables especiales y se declaran incluso antes del DECLARE para poderlas usar en el entorno del servidor. Para referirse a ellas en el entorno de ejecucicón se hace precediendolas de dos puntos ( :VARIABLE )

 VARIABLE nombre_de_la_variable TIPO DE DATO

Ejemplo 1.

VARIABLE   g_monthly_sal NUMBER
DEFINE     p_annual_sal = 50000
SET VERIFY OFF
DECLARE
       v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
       :g_monthly_sal := v_sal/12;
END;
/
PRINT g_monthly_sal

y obtenemos:

PL/SQL procedure successfully completed.
G_MONTHLY_SAL
-------------
   4166.66667

Ejemplo 2.

	VARIABLE v_sum_sal NUMBER;
DECLARE
	v_depno NUMBER NOT NULL :=10;
BEGIN
	SELECT sum(salary) -- funcion de grupo
	INTO :v_sum_sal 
	FROM employees
	WHERE department_id = v_depno;
END;
/
PRINT  :v_sum_sal 

Y obtenemos:

PL/SQL procedure successfully completed.
 V_SUM_SAL
----------
      4400

Los comentarios en PL/SQL se hacen de la siguiente manera:

• – para comentarios de una línea
• /* varias lineas */ para comentarios de varias lineas

DECLARE
v_sal number(9,8);
BEGIN
-- esto es un comentario de una linea
/* esto es un comentario 
   de varias lineas
   que acaba en la siguente
*/
END;

En el bloque ejecutable ( entre BEGIN y END ) es donde haremos las operaciones. El funcionamiento BASICO de un proceso PL/SQL es:

1. Declaración de variables
2. Carga del resultado de la select en la variable
3. Procesamiento de la variable

Esto se realiza mediante una pequeña modificación de la sintaxis del SELECT.

SELECT columan, columna, ...
INTO   variable, variable, ...
FROM   tabla
WHERE  condicion

Recuerda que:

• Una variable simple almacena SOLO un valor. La select sólo puede devolver un valor (de momento)
• En caso de que se produzcan errores porque no se devuelven datos o se devuelve mas de una fila hay excepciones predefinidas para controlar esos errores: NO_DATA_FOUND y TOO_MANY_ROWS
• Deves declarar tantas variables como columnas desees recoger
• Cada variable debe corresponderse con el tipo de datos que esperas recoger.

DECLARE

	v_depno NUMBER NOT NULL :=10;
BEGIN
	SELECT sum(salary) -- funcion de grupo
	INTO :v_sum_sal 
	FROM employees
	WHERE department_id = v_depno;
END;
/

Pues esto es precisamene la gracia del PL/SQL poder crear procesos que modifiquen los datos. Funciona exactamente igual que todo lo visto hasta ahora.

DECLARE
	v_id EMPLOYEES.EMPLOYEE_ID%TYPE;
	v_nom EMPLOYEES.FIRST_NAME%TYPE;
	v_ape EMPLOYEES.LAST_NAME%TYPE;
	v_sal EMPLOYEES.SALARY%TYPE;
BEGIN
	SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
	INTO v_id, v_nom, v_ape, v_sal
	FROM EMPLOYEES
	WHERE EMPLOYEE_ID = 202;
	

	INSERT INTO JEFES(ID, NOMBRE, APELLIDO, COBRA)
	VALUES(v_id, v_nom, v_ape, v_sal);
END;

/

Podemos actualizar tablas en base a valores previamente obtenios o constantes previamente declaradas.

Vamos a subirle el sueldo a los jefes:

DECLARE
 	c_porcentaje NUMBER(2,2) :=0.95;
BEGIN
	UPDATE JEFES 
SET COBRA = COBRA * c_porcentaje;

END;

/

Vamos a quitar un jefe:

DECLARE
 	c_id NUMBER :=202;
BEGIN
	DELETE FROM  JEFES 
Where id =  c_id;

END;
/

Para hacer efectivo cualquier cambio hecho por una sentencia DML hay que hacer un COMMIT y eso sirve tambien para pl/sql por lo tanto. Nada de lo hecho anteriormente será efectivo hasta que ejecutemos un COMMIT Igualmente si queremos volver al estado aterior también disponemos de ROLLBACK.

primero ejecutamos:

DECLARE
 	c_id NUMBER :=202;
BEGIN
	DELETE FROM  JEFES 
Where id =  c_id;

END;
/

Con lo que obtenemos:

SQL> select * from jefes;

        ID NOMBRE     APELLIDO        COBRA
---------- ---------- ---------- ----------
        94 jefe94     apellido94         95
        96 jefe96     apellido96         95

Ahora hacemos el ROLLBACK

SQL> rollback;
Rollback complete.

Y volvemos a tener:

SQL> select * from jefes;

        ID NOMBRE     APELLIDO        COBRA
---------- ---------- ---------- ----------
        94 jefe94     apellido94        100
        96 jefe96     apellido96        100
       202 Pat        Fay              6000

pero si al final del PL/SQL añadimos un commit:

DECLARE
 	c_id NUMBER :=202;
BEGIN
	DELETE FROM  JEFES 
Where id =  c_id;
	
COMMIT; 
END;
/

obtemenos esto sin posibilidad de vuelta atras:

SQL> select * from jefes;

        ID NOMBRE     APELLIDO        COBRA
---------- ---------- ---------- ----------
        94 jefe94     apellido94         95
        96 jefe96     apellido96         95

SQL> rollback;

Rollback complete.

SQL> select * from jefes;

        ID NOMBRE     APELLIDO        COBRA
---------- ---------- ---------- ----------
        94 jefe94     apellido94        100
        96 jefe96     apellido96        100

Cualquier sentencia DML supone un COMMIT de cualquier sentencia ejecutada anteriormente. Por lo que si ejecutamos tres inserts, al hacer un ROLLBACK volveremos a la situación que había antes del último insert. Pero no al principio de los tres inserts.

Para llevar un control de las transacciones existen los SAVEPOINTS. Puntos de salvado al que se puede volver en caso de necesidad.

DECLARE
 	c_porcentaje NUMBER(2,2) :=0.95;
BEGIN
	UPDATE JEFES 
SET COBRA = COBRA * c_porcentaje;
SAVEPOINT a;    -- ESTABLEZCO EL PUNTO DE SALVADO A
INSERT INTO JEFES
VALUES( 99, 'jefazo', 'malo', 99999);
SAVEPOINT b;    -- ESTABLEZCO EL PUNTO DE SALVADO B
INSERT INTO JEFES
VALUES( 93, 'jefecillo', 'pelota', 99);
SAVEPOINT c;     -- ESTABLEZCO EL PUNTO DE SALVADO C
ROLLBACK TO SAVEPOINT b;   -- VUELVO AL PUNTO B
COMMIT;  -- HAGO UN COMMIT PARA HACER LOS CAMBIOS EN FIRME (ME QUEDO EN B)
END;
/

Los cursores son areas de trabajo. Cada vez que se ejecuta una sentencia sql se abre un cursor. El programador tambien puede establecer cursores. Pero eso queda fuera del ámbito de esta miniguia.

• SQL%ROWCOUNT Número de filas afectadas por la última sentencia SQL
• SQL%FOUND Atributo booleano que devuelve TRUE si la última sentencia SQL afectó a una o más filas
• SQL%NOTFOUND Atributo booleano que devuelve TRUE si la última sentencia SQL a no afectó a ninguna fila
• SQL%ISOPEN Al finalizar una bloque PL/SQL se cierra el cursor. Por lo que dará FALSE siempre que no se esté ejecutando una secuencia PS/SQL

Y… no se pueden sacar mensajes a pantalla desde PL/SQL? Pues si. Tienes que activar la función que saca mensajes a pantalla porque por defecto no lo hace.

SET SERVEROUTPUT ON

y a partir de aqui ya puedes…. Se hace a través de la función db_ms_output.put_line('mensaje')

DECLARE
 	c_porcentaje NUMBER(2,2) :=0.95;
BEGIN

INSERT INTO JEFES
VALUES( 93, 'jefecillo', 'pelota', 99);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' FILAS INSERTADAS');
END;
/