Sql

PL/SQL TEMA 7.1 FUNDAMENTOS DEL LENGUAJE 1 – Introducción 2 – Estructura de los bloques PL/SQL 3 – Unidades léxicas 4 – Declaración de variables 5 – Tipos de datos 6 – Estructuras de control 6.1 – Condicional 6.2 – Repetitivas 6.3 – Salto 6.4 – Otros 1 – Introducción SQL es un lenguaje de 4º Generación, es decir, el lenguaje describe lo que debe de hacerse, pero no la forma de realizarlo. Los lenguajes de 3ª Generación tienen una naturaleza más procedimental, por lo que la resolución de problemas con ellos supone la realización de algoritmos, siendo las estructuras procedimentales de los mismos muy útiles en la realización de programas. El PL/SQL combina la potencia del SQL como lenguaje de 4ªG. con las estructuras procedimentales de un lenguaje 3ªG. PL/SQL Lenguaje Procedimental / Lenguaje de Consultas Estructuradas. Es una implementación de ORACLE corporation, que permite el uso tanto de sentencias SQL para acceder a la BD como de sentencias de control de flujo para procesar los datos. Así como la declaración de variables, constantes, definición de procedimientos y funciones, captura y tratamiento de errores en tiempo de ejecución. La unidad básica de un programa PL/SQL es el BLOQUE. Todos los programas PL/SQL están formados por bloques dispuestos en forma secuencial o anidada. Existen distintos tipos de bloques: 1º) Bloque anónimos: Se construyen de manera dinámica y se ejecutan una sola vez. 2º) Bloques nominados: Son bloques anónimos q tienen las mismas características pero que tienen una etiqueta que permite diferenciarlos. 3º) Subprogramas: Existen tres tipos: - Procedimientos - Funciones - Paquetes almacenados Se ejecutan mediante una llamada o invocación explícita y por tanto se ejecutarán tantas veces como queramos. 1

4º) Disparadores o Triggers: Son bloques nominados que se almacenan y se ejecutan múltiples veces, pero esta ejecución se produce de manera implícita cada vez que tiene lugar un suceso de disparo. En nuestro caso el suceso es siempre una sentencia DML.

2 – Estructura de los bloques PL/SQL Todos los bloques tienen tres secciones diferenciales: Sección Declarativa, Sección Ejecutable y Sección de Tratamiento de Excepciones. La única obligatoria es la Sección Ejecutable siendo opcionales las otras dos. SINTAXIS: DECLARE /* Declaraciones */ BEGIN /* Sentencias ejecutables */ EXCEPTION /* Captura y tratamiento de excepciones */ END; •

Sección declarativa: Se localizan todas las variables, constantes, cursores y tipos de datos, funciones y procedimientos locales. Todos ellos estarán disponibles sólo para este bloque.

•

Sección ejecutable: Es donde se lleva a cabo el trabajo del bloque. Pueden aparecer tanto órdenes SQL (en nuestro caso sólo DML), como órdenes procedimentales.

•

Sección de Excepciones: Aquí se lleva acabo el tratamiento de errores en tiempo de ejecución. No se ejecutará a menos que se produzca la excepción.

3 – Unidades léxicas •

Identificadores: Se inician con una letra que puede ir seguida de cero o más letras o números, $, _, o #. La longitud máxima es de 30 caracteres. No discrimina entre mayúsculas y minúsculas. Si se quiere identificas entre mayúsculas y minúsculas incluir los caracteres prohibidos o encerrando la definición entre comillas dobles (“un numero”, “B/N”).

•

Literales: Existen tres tipos: -

De carácter: Constan de uno o más caracteres encerrados entre comillas simples. Se pueden asignar a variables de tipo CHAR o VARCHAR2 sin conversión. Pueden incluir cualquier carácter imprimible, incluidas las comillas simples, comillas dobles…

2

•

-

Numéricos: Son valores enteros o reales que pueden ser asignados a una variable de tipo NUMBER sin conversión. No permite el uso del punto en un literal entero.

-

Booleanos: Solo pueden tomar tres valores (true, flase, null). Sólo pueden ser asignados a una variable booleana. Se usan básicamente en las condiciones de las sentencias condicionales repetitivas.

Comentarios: Existen dos tipos: -

Monolínea: -- comentario

-

Multilínea: /* comentario */

4 – Declaración de variables SINTAXIS id_variable [CONSTANT] tipo [NOT NULL] [: = valor] | [DEFAULT valor]; Ejemplo: DECLARE V_descripcion VARCHAR2(50); V_cont BINARY_INTEGER :=0 ; V_sino BOOLEAN; BEGIN /* Sentencias ejecutables */ END; Características: - Si no se inicializa la variable, se le asigna como valor predeterminado NULL. - Si se incluye la cláusula NOT NULL es obligatorio inicializar dicha variable. - Si se usa CONSTANT en la declaración, es preciso utilizar la variable, siendo imposible posteriormente cambiar dicho valor. En el resto del bloque se tratan como de sólo lectura. - Se puede usar la palabra clave DEFAULT, en lugar del operador de asignación. - Sólo puede haber una declaración de variable por línea.

5 – Tipos de datos •

Ámbito y visibilidad:

Una variable, declarada en un bloque es considerada local para ese bloque y global para todos sus subloques. Por tanto una variable definida en el bloque externo puede ser referenciada desde un bloque interno. Si en el bloque interno se ha definido una variable con el mismo nombre que utilizado en el bloque exterior, esta variable local oculta a la 3

variable local. Pero podemos diferenciar a la variable más externa anteponiéndola el nombre del bloque exterior al que pertenece, si el bloque fuera nominado. etiqueta_bloque_externo.id_variable <<etiqueta>> Ejemplo: <> DECLARE /*1*/ V_bandera BOOLEAN; V_NSS NUMBER(9); BEGIN/* 1 */ /* Sentencias */ DECLARE /*2*/ V_NSS CHAR (11); V_fecha_inicio DATE; BEGIN /*2*/ /* Sentencias */ END; /*2*/ END Bloque1; •

Uso del %TYPE:

En muchos casos las variables PL/SQL se emplean para manipular los datos almacenados en una tabla de la BD. En este caso la variable que alberga el dato debe de tener el mismo tipo que la columna de la tabla. Si la cantidad de código es grande, el proceso puede llevar mucho tiempo y ser fuente de errores. PL/SQL proporciona la herramienta %TYPE, que añadida al final del nombre de una columna de una tabla o incluso de una variable previamente declarada, devuelve el tipo de la misma. id_variable id_tabla.id_columna%TYPE; Ejemplo: DECLARE V_DNI VARCHAR2 (9); V_DNI2 USUARIOS.DNI%TYPE;

6 – Estructuras de control 6.1 – Condicional IF – THEN – ELSE SINTAXIS:

4

IF condición THEN Sentencia; [ELSIF condición2 THEN Sentencias2; ELSE Sentencias3;] END IF; PL/SQL no tiene ninguna capacidad para mostrar datos por pantalla. •

Para la salida de datos vamos a utilizar el paquete DBMS_OUTPUT. DBMS_OUTPUT.putline (‘…. ‘); DBMS_OUTPUT.enable(nº_caracteres); Este paquete está diseñado para la depuración de errores y no para la generación de informes de propósito general, ya que para eso ORACLE dispone de la herramienta Reports.

•

Para introducir datos usaremos: V_nombre VARCHAR2 (25) := ’&Introduce nombre’;

EJEMPLO: 1) Pedir un número por teclado y decir si es par o impar. CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_num NUMBER := '&Numero'; BEGIN IF MOD(V_num, 2) = 0 THEN DBMS_OUTPUT.PUT_LINE ('El número '|| V_num ||' es PAR'); ELSE DBMS_OUTPUT.PUT_LINE ('El número '|| V_num ||' es IMPAR'); END IF; END; /

SELECT..INTO SELECT lista_items INTO lista_variables FROM tablas WHERE condición; 2) Obtener la cuota de socio de María Fernández Santamarina e indicar en qué rango está (El rango puede ser: económica < 15, normal < 30 y elevada > 30)

5

Ruben

Una secuencia de órdenes dentro de un comando IF – THEN – ELSE, se ejecuta sólo si su condición asociada es verdadera. Si la condición devuelve FALSE O NULL, la secuencia de órdenes no se ejecutará, con lo cual es conveniente eliminar el caso de los nulos. 3) Actualizar la cuota de socio nº J7777, según su antigüedad. Aumentaremos un 2% si su antigüedad es > a 6 años, un 2.5% si es > a 4 años y un 3% en cualquier otro caso. (Saber la fecha de alta de J7777; calcular su antigüedad, y actualizar su cuota según la antigüedad). CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_fecha_alta USUARIOS.FECHA_ALTA%TYPE; K_nums CONSTANT USUARIOS.NUM_SOCIO%TYPE:='J7777';    V_antiguedad NUMBER;     V_cuotaActual USUARIOS.cuota_socio%TYPE;    V_ing NUMBER; BEGIN SELECT fecha_alta INTO V_fecha_alta FROM USUARIOS WHERE num_socio = K_nums and fecha_alta is not null;   V_antiguedad := TRUNC(MONTHS_BETWEEN (SYSDATE,V_fecha_alta)/12);   IF V_antiguedad > 6 THEN v_ing:=1.02; ELSIF V_antiguedad >4 THEN v_ing:=1.025; ELSIF V_antiguedad <=4 THEN v_ing:=1.03; END IF; UPDATE USUARIOS SET cuota_socio = cuota_socio * v_ing; dbms_output.put_line ('La antiguedad es: '||V_antiguedad); dbms_output.put_line ('La cuota actual es: '||V_cuotaActual); END;

6.2 – Repetitivas Existen cuatro tipos de estructuras repetitivas: - Simple - While - For numéricos - For de cursor a) Simples: LOOP 6

SentenciasEXIT WHEN CONDICIÓN;(depende de dónde se coloque así funcionará) END LOOP; EJEMPLO: Programa que escribe los diez primeros números enteros. CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_cont BINARY_INTEGER :=0; K_salida CONSTANT NUMBER := 10; BEGIN LOOP V_cont := V_cont +1; dbms_output.put_line(V_cont); EXIT WHEN V_cont = K_salida; END LOOP; END; /

OTRO EJEMPLO: Crear una tabla física(Temp_table) con dos columnas, una debe de ser numérica(n_col) y otra de que coja 100 caracteres (Char_col). Escribir en la tabla la suma acumulada de los primeros 30 números naturales en la columna numérica. En la columna de caracteres debe de ir poniendo Vuelta1, Vuelta 2, Vuelta 3,... CLEAR BUFF; SET SERVEROUTPUT ON; CREATE TABLE Temp_table( n_col NUMBER, char_col VARCHAR2(100)); DECLARE V_sum NUMBER :=0; V_cont BINARY_INTEGER :=0; K_salida CONSTANT NUMBER := 30; BEGIN LOOP V_cont := V_cont + 1; V_sum := V_sum + V_cont; INSERT INTO temp_table (n_col,char_col) VALUES (V_sum, 'Vuelta '||V_cont); EXIT WHEN V_cont = K_salida; END LOOP; COMMIT; END;

b) While: While condición loop Sentencias; End loop; 7

EJEMPLO: CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_cont BINARY_INTEGER :=0; V_sum NUMBER :=0; K_salida CONSTANT NUMBER :=50; BEGIN dbms_output.put_line('Cálculo de la suma de los ' ||K_salida||' primeros nº naturales'); WHILE (V_cont < K_salida) LOOP V_cont:= V_cont + 1; V_sum := v_sum + V_cont; INSERT INTO temp_table (char_col, n_col) VALUES( 'Item '||V_cont, V_sum); END LOOP; dbms_output.put_line('Suma '||V_sum); END; /

OTRO EJEMPLO: Programa que lea un número por teclado y diga el número de veces que es divisible por dos. CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_cont BINARY_INTEGER :=0; V_num NUMBER := '&Introduce_Numero'; --variable q mete el usuario K_par CONSTANT NUMBER :=2; BEGIN WHILE MOD(V_num, K_par)=0 LOOP V_cont := V_cont + 1; V_num := V_num / K_par; END LOOP; dbms_output.put_line('Es divisible ' ||V_cont||' veces'); END;

c) For numércos: FOR cont_bucle IN [REVERSE] valor_inicial Sentencias; END LOOP;

.. valor_final LOOP

Es un bucle en el que conocemos a priori las veces que se va a repetir, pues conocemos el valor inicial y el valor final. Los límites del bucle se examinan una sola vez y determinan el número total de iteraciones. Por defecto el contador del bucle, se inicializa al límite inferior y se incrementa en una unidad en cada iteración del bucle hasta llegar al límite superior. Si se incluye la palabra reverse, el índice del bucle realiza las iteraciones desde el límite superior hasta el límite inferior.

8

Los límites superior e inferior, no tienen porqué ser literales numéricos. Pueden ser cualquier expresión PL/SQL que pueda ser convertida a un valor numérico. El índice del bucle, se declara implícitamente como BINARY INTEGER, no siendo necesario declararlo, ya que si se declara, el índice del bucle ocultará la declaración externa de dicho contador o variable que hayamos declarado. Con la sentencia EXIT [WHEN condición], o IF condición THEN – EXIT, podemos hacer una salida del bucle (como si fuera un break en C). El REVERSE vale para cambiar el orden del bucle, del valor inicial al valor final. ACCEPT n_var PROMPT texto;  Sirve para poner una frase. EJEMPLO: Programa que pida un número del 1 al 10 por teclado y escriba la cuenta atrás desde dicho número. ACCEPT numero PROMPT 'Introduce número del 1 al 10:'; -- ejecutar antes esto y luego el declare (hacerlo por separado) CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE V_num NUMBER:='&Numero '; BEGIN FOR V_cont IN REVERSE 1..V_num LOOP --Muestra desde el número introducido hasta el 1 dbms_output.put_line (V_cont); END LOOP; dbms_output.put_line('YA ESTAAA!!'); END; / --(hacerlo por separado) una vez hecho todo esto le meto el UNDEF UNDEF numero; --Para volver a ejecutar el programa y que nos pida un nuevo número

OTRO EJEMPLO: Modificar el contenido de las 10 primeras filas de la tabla Temp_table poniendo en la columna numérica, la mitad del valor que aparecen. CLEAR BUFF; SET SERVEROUTPUT ON; DECLARE BEGIN FOR V_cont IN REVERSE 1..10 LOOP --coge las 10 1ªs filas UPDATE temp_table SET n_col = n_col/2 WHERE V_cont = to_number(SUBSTR(char_col,7,length (char_col))); --pasa a numero entero la cadena END LOOP; dbms_output.put_line('TACHAAAN!!'); END; /

9

6.3 – Salto

6.4 – Otros

10