INTRODUCCIÓN AL LENGUAJE DE PROGRAMACIÓN PL/SQL

I

L

P

/

INTRODUCCIÓN

El lenguaje que se emplea para programar varía de un Sistema Manejador de Bases de Datos Relacional (RDBMS) a otro, el que utiliza Oraclese llama PL/SQL (Procedural Lenguaje/SQL) y es un lenguaje de programación que se usa para acceder y trabajar con bases de datos en Oracle desde distintos entornos. Cada unidad PL/SQL puede contener uno o más bloques que pueden estar anidados. Un bloque PL/SQL puede ser anónimo (no tiene nombre) o un subprograma (función o procedimiento, con las características clásicas de éstos).

ESTRUCTURA DE UN BLOQUE PL/SQL

Un bloque anónimo PL/SQL está compuesto de tres partes principales. Su estructura básica es la siguiente:

DECLARE

Sección

Declarativa Variables, constantes, excepciones...

BEGIN

Sección Órdenes SQL

Ejecutable Órdenes PL/SQL

EXCEPTION

Sección

de excepciones Acciones que se realizan

END; /

• Sección declarativa (opcional). Contiene las variables, cursores y tipos usados por el bloque. Sólo es necesaria cuando se requiere definir variables en el bloque.

• Sección ejecutable (obligatoria). Contiene órdenes SQL y órdenes PL/SQL para manipular los datos del bloque.

• Sección de excepciones (opcional). Especifica las acciones a realizar en caso de error o cuando se producen excepciones en la ejecución. Sólo existirá si se van a tratar errores en los bloques.

Para ejecutar un bloque PL/SQL siempre hay que colocar al final la barra /.

Pueden añadirse comentarios al código. Estos comentarios pueden ser especificados con:

/*comentario más comentario */ -- comentario de línea

USO DE VARIABLES

Las variables son zonas de memoria nominadas que permiten almacenar un valor. Pueden utilizarse para pasar valores como argumentos a subprogramas. También podrán utilizarse para almacenar valores devueltos o requeridos por una orden SQL.

Las variables se caracterizan por su nombre, compuesto por letras, números y los caracteres $, _ o #. El nombre puede tener hasta un máximo de 30 caracteres y no debe ser una palabra reservada.

Todas las variables tienen un tipo, el cual puede ser:

• Escalar. Almacenan un valor único. Son los mismos que los de las columnas de las tablas (VARCHAR2, NUMBER, DATE, CHAR, LONG, LONG_RAW, BINARY_INTEGER,

LAW_INTEGER) además de BOOLEAN y CONSTANT.

Las variables BOOLEAN pueden tomar el valor TRUE, FALSE o NULL; las cuales pueden combinarse mediante operadores lógicos (NOT, AND, OR).

Las expresiones pueden devolver valores BOOLEANOS utilizando operadores relacionales (<, <=...).

Las variables CONSTANT se definen con un valor que es fijo.

• Compuesto. Hacen referencia a un objeto existente como columna, tabla, cursor, variable o registro.

• LOB (Large OBjects). Almacenan gran cantidad de información. Permiten almacenar datos no estructurados (imágenes, texto...) de hasta 4 GB de tamaño. Estos tipos son

BFILE, BLOB, CLOB y NCLOB.

La declaración de variables se hace mediante la siguiente sintaxis

identificador [CONSTANT] tipo de dato [NOT NULL] [:= | expresión];

Existen varias formas de asignar valores a las variables utilizando :=, o de forma directa. Sintaxis

nombre de variable := expresión;

donde la expresión puede ser una constante, una variable o un cálculo usando constantes y variables.

Las variables declaradas como NOT NULL siempre deben ser inicializadas. La inicialización puede hacerse utilizando := , o con la palabra reservada DEFAULT.

Si una variable no se inicializa contendrá el valor NULL. Las constantes siempre deben ser inicializadas.

Ejemplo

DECLARE fecha DATE;

depNum NUMBER(2) NOT NULL := 10; ciudad VARCHAR2(10):= ‘Ciudad Real’; KmMilla CONSTANT NUMBER:= 1.4;

Se pueden realizar operaciones entre variables y ser almacenadas. Su sintaxis es la siguiente

SET campo = campo + varcampo ;

Suma lo que hay en campo y varcampo y lo almacena en campo que puede ser una columna de una tabla.

Dentro de las variables compuestas esta el atributo %TYPE que permite hacer referencia a una columna de una tabla o una variable que se haya definido anteriormente. Su sintaxis es la siguiente.

nombreVariable tabla.columna %TYPE;

Ejemplo

vnombre empleado.nombre%TYPE; o

balance NUMBER;

Es posible hacer referencia a estructuras completas de tabla o cursor, con el fin de crear variables que tengan la misma estructura, para ello utilizamos %ROWTYPE, su sintaxis es la siguiente:

nombreVariable {tabla | cursor} %ROWTYPE;

Ejemplo

vArticulo Articulos%ROWTYPE;

También podemos utilizar registros que son conjuntos de variables de diferente tipo que están relacionadas entre sí, para ser tratadas como una unidad. Su sintaxis es la siguiente.

TYPE tipo_registro IS RECORD(

Campo1 tipo1 [NOT NULL] [ :=expr1], Campo2 tipo2 [NOT NULL] [ :=expr2], Campon tipon [NOT NULL] [ :=exprn]);

Cada declaración de campo es una declaración de variables. Ejemplo:

DECLARE

TYPE rCliente IS RECORD( numCli NUMBER(4), nomCli VARCHAR2(30), direcCli VARCHAR2(50), codPostCli NUMBER(5)); cliente rCliente; ÓRDENES SQL en PL/SQL

Las órdenes que se pueden ejecutar dentro de PL/SQL son únicamente las del lenguaje de manipulación de datos (SELECT, INSERT, UPDATE, DELETE). Así como sentencias de control de flujo.

SELECT

La instrucción SELECT almacenará los valores que obtenga en las variables indicadas tras

INTO y en el mismo orden. Es obligatorio incluir la cláusula INTO. El comando SELECT debe prepararse para que sólo devuelva una fila. La sintaxis es la siguiente:

SELECT lista

INTO variable [, variable, variable...] FROM tabla

WHERE condición;

Ejemplo

Sea la siguiente tabla:

AREA NOM SEC --- --- ---- 123 obras A 234 sanidad A 345 transporte B 456 vialidad B 567 salud A SET SERVEROUTPUT ON; DECLARE

vArea NUMBER(5);

vNombre VARCHAR2(20); BEGIN

SELECT ClaveArea, nom INTO vArea, vNombre FROM area WHERE sec='C'; DBMS_OUTPUT.PUT_LINE('area '||varea); DBMS_OUTPUT.PUT_LINE('nombre '||vnombre); END; /

En los siguientes comandos la sintaxis no varía, se utiliza la que se explico en el previo 2. INSERT

INSERT INTO tabla VALUES(...); UPDATE

UPDATE tabla

SET valor = expresión WHERE condición; DELETE

ÓRDENES PL/SQL

Dentro de PL/SQL se puede hacer uso de funciones numéricas, de carácter, de fecha, de conversión de tipos de datos, excepto las de agrupamiento (ya que éstas se aplican sobre una columna de una tabla). Ver apéndice.

Los operadores en PL/SQL son los mismos que para SQL: aritméticos, lógicos, concatenación, y paréntesis. Además, existe el operador exponencial (**).

También se utilizan estructuras de control, estas permiten elegir la forma en la que se van a ejecutar las diferentes instrucciones dentro del programa. Las más importantes son las estructuras condicionales y los ciclos.

Estructuras condicionales

Las estructuras condicionales se utilizan para la realización de acciones dependiendo del cumplimiento o no de determinadas condiciones. Las estructuras de condicionales más comunes son IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF y CASE.

Sentencia IF-THEN

Es la estructura condicional en la que se ejecuta una secuencia de instrucciones si la condición es cierta. Su sintaxis es la siguiente:

IF condición THEN

Secuencia_de_instrucciones END IF;

Sentencia IF-THEN-ELSE

Es la estructura condicional en la que se ejecuta una primera secuencia de instrucciones si la condición es cierta, y se ejecuta una segunda secuencia de instrucciones si la condición es falsa. Su sintaxis es la siguiente:

IF condición THEN Secuencia_de_instrucciones1 ELSE Secuencia_de_instrucciones2 END IF; Sentencia IF-THEN-ELSIF

Estructura condicional que se utiliza para seleccionar una acción entre varias alternativas mutuamente excluyentes. Si la primera condición es falsa o nula, se pasar a preguntar por la

siguiente condición de la cláusula ELSIF comenzando una nueva estructura IF-THEN-ELSE. Su sintaxis es la siguiente:

IF condición THEN

Secuencia_de_instrucciones1 ELSIF condición2 THEN

Secuencia_de_instrucciones2 ELSE

Secuencia_de_instrucciones3 END IF;

Los operadores utilizados en las condiciones son los mismos que en SQL: =, >, <, ¡,

>=,<=,IS NULL, IS NOT NULL, BETWEN, LIKE, AND, OR, etc. Sentencia CASE

Al igual que IF, la sentencia CASE selecciona una secuencia de sentencias a ejecutar, pero a diferencia del IF se utiliza un selector y no una expresión booleana. Su sintaxis es la siguiente:

CASE selector

WHEN expresión1 THEN secuencia_de_instrucciones1; WHEN expresión2 THEN secuencia_de_instrucciones2; …

WHEN expresiónN THEN secuencia_de_instruccionesN; [ELSE secuencia_de_instrucciones N+1];

END CASE;

La condición ELSE es opcional y sólo se aplica si ninguna de las condiciones WHEN anteriores se ejecuta.

Procesamientos repetitivos

Son conjuntos de instrucciones que se utilizan para ejecutar órdenes de forma repetida. Los más comunes son LOOP, WHILE y FOR.

Sentencia LOOP

PL/SQL permite llevar a cabo operaciones repetitivas mediante la instrucción LOOP. Utilizada sola, la instrucción LOOP inicia ciclos sin fin. Su sintaxis es la siguiente:

[<etiqueta>] LOOP

instrucciones;

[EXIT etiqueta WHEN condición] END LOOP [etiqueta];

EXIT etiqueta WHEN condición

Donde etiqueta es el nombre del ciclo y condición es la que se debe cumplir para poder salir del ciclo.

Sentencia FOR

El ciclo FOR permite ejecutar las instrucciones específicas dentro del bucle haciendo variar un índice. Las instrucciones se ejecutan tantas veces como cambia el valor del índice. Su sintaxis es la siguiente:

FOR índice IN [REVERSE] min..max LOOP instrucciones;

… END LOOP;

Donde el índice se declara de forma implícita. min, max son constantes, expresiones o variables. El comando REVERSE , es opcional y decrementa el valor entre min y max.

Sentencia WHILE

Un ciclo WHILE se realiza solamente si la condición es verdadera. Su sintaxis es la siguiente

[<etiqueta>]

WHILE condicion LOOP instrucciones; ...

END LOOP [<etiqueta>];

Donde la condición es una combinación de expresiones relacionadas mediante operadores:

<,>,=,!=,AND,OR,LIKE,…

Para ejecutar un bloque de órdenes PL/SQL se utiliza el comando COMMIT

Para presentar la salida de la ejecución de los bloques en pantalla se utiliza el paquete

DBMS_OUTPUT, la sintaxis adecuada es la siguiente.

DBMS_OUTPUT.PUT_LINE(‘Mensaje’ || variable);

Es necesario utilizar SET SERVEROUTPUT ON para habilitar las salidas por pantalla Ejemplo

SET SERVEROUTPUT ON;

Hasta ahora se han definido bloques anónimos que se compilan cada vez que son ejecutados y que no se almacenan en la base de datos. Si deseamos que estos bloques sean guardados en la base de datos, entonces utilizamos procedimientos almacenados (stored procedure). Un procedimiento almacenado es un conjunto de comandos de SQL que pueden ser compilados y almacenados en el servidor. Una vez realizado esto, los clientes no necesitan volver a teclear todas las instrucciones sino únicamente hacer referencia al procedimiento. Esto mejora el rendimiento del servidor, ya que la instrucción de SQL solamente es revisada una sola vez y menos información debe ser enviada entre el cliente y el servidor. Los procedimientos son llamados también subprogramas.

Para crear un procedimiento se utiliza la siguiente sintaxis:

CREATE [OR REPLACE] PROCEDURE nombreProcedimiento (

argumento [IN | OUT | IN OUT] <tipo> [, argumento [IN|OUT|IN OUT] <tipo>,…]

{AS} BEGIN

<codigo del procedimiento> [EXCEPTION]

END nombreProcedimiento; /

La cláusula OR REPLACE se utiliza para crear un procedimiento que ya existe. Los argumentos pueden tener los modos:

IN variable de entrada OUT variable de salida

IN OUT variable de entrada/salida

El cuerpo de un procedimiento es un bloque PL/SQL con sus secciones declarativa, ejecutable y de manejo de excepciones. La declarativa se sitúa entre las palabras claves AS y la palabra clave BEGIN, la ejecutable entre BEGIN y la palabra EXCEPTION y la de excepciones entre

EXCEPTION y END. (No existe la palabra clave DECLARE en un procedimiento y su lugar lo ocupan AS.) Se debe incluir el nombre del procedimiento después de la orden END que cierra la declaración. Los parámetros de un procedimiento pueden tener valores predeterminados que se declaran mediante la siguiente sintaxis.

Nombre argumento [modo] <tipo>{:=|DEFAULT}valor inicial

EXEC <nombredelprocedimiento> (parámetros) Para borrar un procedimiento:

DROP PROCEDURE <nombre del procedimiento>

SECUENCIAS

Dentro de los procedimientos podemos utilizar secuencias que son objetos que generan una serie de números únicos y se utilizan frecuentemente para tablas que usan columnas como llaves. Cuando en una aplicación se inserta una nueva fila en una tabla, la aplicación solicita una secuencia a la base de datos para proporcionar el siguiente valor disponible en la secuencia para el valor de la clave principal de la nueva fila. Su sintaxis es la siguiente:

CREATE SECUENCE <nombre secuencia> [ {INCREMENT BY | START WITH]} entero |{MAXVALUE integer | NOMAXVALUE} |{MINVALUE integer | NOMINVALUE} | {CYCLE | NOCYCLE}

| {CACHE integer | NOCACHE} | {ORDER | NOORDER}

} … ] ;

INCREMENT BY indica la amplitud de la secuencia. START WITH indica el número con que comienza la secuencia. MINVALUE y MAXVALUE son los números más alto y más bajo que genera la secuencia. Para reanudar una secuencia donde comenzó se utiliza CYCLE. CACHE permite guardar en memoria un conjunto preasignado de números de secuencia. ORDER asigna los números de secuencia en orden de peticiones.

Ejemplos

1. Para ejecutar los siguientes ejemplos es necesario generar la tabla area e insertar los datos:

CREATE TABLE area(

claveArea NUMBER(5) NOT NULL, Nom VARCHAR2(20),

sec CHAR(1),

CONSTRAINT area_pk PRIMARY KEY (claveArea), CONSTRAINT tipo_sec CHECK(sec IN('A','B','C')));

Con los siguientes datos:

claveArea nom sec

123 Obras A 234 Sanidad A 345 Transporte A 456 Vialidad B 567 Salud C

Una vez realizado esto activamos la salida en pantalla.

SET SERVEROUTPUT ON;

A continuación se presentan dos formas de creación de procedimientos:

Para dar de alta un área pasándole los parámetros desde otro bloque PL/SQL, realice lo siguiente:

CREATE OR REPLACE PROCEDURE altaArea(c_area IN NUMBER, nom IN VARCHAR, sec IN CHAR)

AS BEGIN

INSERT INTO area VALUES(c_area,nom,sec); COMMIT;

DBMS_OUTPUT.PUT_LINE('Área creada: '||nom); END altaArea;

/

Para poderlo ejecutar, empleamos

EXEC altaArea(235,’Mecatronica’,’A’);

Con lo que obtenemos la siguiente salida

Área creada: Mecatronica

Otra forma de crear este procedimiento sería utilizando %TYPE.

CREATE OR REPLACE PROCEDURE altaNuevaArea( v_area area.claveArea%TYPE,

v_sec area.sec%TYPE )

AS BEGIN

--Inserta una nueva fila en la tabla área

INSERT INTO area (claveArea,nom,sec) VALUES (v_area,v_nom,v_sec); COMMIT;

END altaNuevaArea; /

Para proporcionar los parámetros al procedimiento anterior realizamos

BEGIN

altaNuevaArea (132, 'Transferencia', 'A'); END;

/

o bien

EXEC altaNuevaArea(232,'Contratación','B');

2. Para eliminar o dar de baja un área realizamos los siguiente

CREATE OR REPLACE PROCEDURE bajaArea(v_area IN NUMBER) AS

BEGIN

DELETE FROM area WHERE claveArea=v_area; COMMIT; DBMS_OUTPUT.PUT_LINE('Baja de Área'); END bajaArea; / Para ejecutarlo EXEC bajaArea(123);

Obteniéndose como salida

3. Para modificar el nombre de un área se realiza lo siguiente

CREATE OR REPLACE PROCEDURE cambiaArea(varea IN NUMBER,vnom IN VARCHAR2)

AS BEGIN

UPDATE area SET nom=vnom WHERE claveArea=varea; COMMIT;

DBMS_OUTPUT.PUT_LINE('Área modificada: '||vnom); END cambiaArea;

/

Ejecutando el procedimiento

EXEC cambiaArea(234,'Ciencias');

Se obtiene como salida

Área modificada: Ciencias

4. En el caso de que se necesite cambiar el nombre de una área, de modo de que si no existiera se cree un nuevo registro, para ello es necesario primero generar una secuencia para darle un número secuencial a la clave de área.(Considerando que la clave de área es de tipo númerico).

CREATE SEQUENCE secuencial START WITH 100 INCREMENT BY 1;

Ahora se puede asignar el número de secuencia a la columna claveArea de la tabla área de la siguiente forma: DECLARE VAREA number(5):=121; vnom VARCHAR2(15):='Civil'; vsec CHAR(1):='B'; BEGIN

UPDATE area SET nom=vnom WHERE claveArea=varea; IF SQL%NOTFOUND THEN

INSERT INTO are(claveArea,nom,sec) VALUES(secuencial.NEXTVAL,vnom,vsec); END IF;

END; /

Recuerde, si el área 125 no existe se genera una nueva con número 100. Muestre los datos para que observe el resultado.

5. Ahora, sea la siguiente definición de tabla salario:

CREATE TABLE salarios (

empleo CHAR(20) NOT NULL PRIMARY KEY, salario NUMBER(5)

);

Agregue los siguientes datos al esquema anterior, utilizando un procedimiento almacenado, muestre la tabla y después realice lo siguiente:

empleo salario Capturista 5000 Secretaria 3000 Administrador 6000

Gerente 10000

Este procedimiento actualiza el valor del salario de los empleados que son capturistas según el factor proporcionado.

CREATE OR REPLACE PROCEDURE ajusteSalario( factor IN NUMBER)

AS BEGIN

UPDATE salarios SET salario=salario*factor WHERE empleo=‘capturista’; COMMIT; END ajusteSalario; / Ejecútelo utilizando EXEC ajusteSalario(1.4);