Tipos de Datos en PL/SQL
Escalares
Almacenan valores simples. A su vez pueden subdividirse en:
- Carácter/Cadena: CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONG, RAW, LONG RAW, ROWID, UROWID.
- Numérico: NUMBER, BINARY_INTEGER, PLS_INTEGER, BINARY_DOUBLE, BINARY_FLOAT.
- Booleano: Almacena valores lógicos TRUE, FALSE y NULL
Identificadores
Nombran objetos de un programa. Deben tener entre 1 y 30 caracteres, el primer carácter debe ser una letra y no se admiten espacios ni signos de puntuación.
Variables
Almacenan información que puede cambiar durante la ejecución del programa.
Declaración e inicialización:
<nombre_de_variable> <tipo> [NOT NULL] [{:= | DEFAULT} <valor>];
Uso de los Atributos %TYPE y %ROWTYPE
Se usa %TYPE y %ROWTYPE para declarar variables que sean del mismo tipo que otros objetos ya definidos.
- %TYPE: Declara una variable del mismo tipo que otra, o que una columna de una tabla.
- %ROWTYPE: Declara una variable de registro cuyos campos se corresponden con las columnas de una tabla o vista de la BD.
Ejemplos:
TOTAL IMPORTE%TYPE;
Declara la variable TOTAL del mismo tipo que la variable IMPORTE que se habrá definido previamente.NOMBRE_MOROSO CLIENTES.NOMBRE%TYPE;
Declara la variable NOMBRE_MOROSO del mismo tipo que la columna NOMBRE de la tabla CLIENTES.MOROSO CLIENTES%ROWTYPE;
Declara la variable MOROSO que podrá contener una fila de la tabla CLIENTES.
Constantes y Literales
Son valores que no cambian en el programa.
<nombre_de_constante> CONSTANT <tipo> := <valor>;
Literales: Representan valores constantes directamente. Sirven para visualizar valores, realizar asignaciones a variables, constantes u otros objetos. Tipos:
- Carácter: un único carácter entre comillas simples.
- Cadena: conjunto de caracteres entre comillas simples.
- Numérico: valores enteros o reales.
- Booleano: TRUE, FALSE, NULL.
- Fecha/Hora.
Operadores y Delimitadores
- Asignación:
:=
Asigna un valor a una variable. Ejemplo:Edad := 19;
- Concatenación:
||
Une dos o más cadenas. Ejemplo:'buenos' || 'días'
dará como resultado ‘buenosdías’. - Comparación:
=, !=, <, >, <=, >=, IN, IS NULL, LIKE, BETWEEN
. Funcionan igual que en SQL. - Aritméticos:
+, -, *, /, **
. Se emplean para realizar cálculos. Algunos de ellos se pueden utilizar también con fechas. - Lógicos:
AND, OR y NOT
- Otros indicadores y delimitadores:
( )
Delimitador de expresiones.' '
Delimitador de literales de cadena." "
Delimitador de identificadores (utilización desaconsejable, en general).<< >>
Etiquetas./* */
Delimitador de comentarios de varias líneas.--
Indicador de comentario de una línea.%
Indicador de atributo (TYPE, ROWTYPE, FOUND,…).:
Indicador de variables de transferencia (bind).,
Separador de ítem de lista.;
Terminador de instrucción.@
Indicador de enlace de base de datos.
Estructuras de Control en PL/SQL
Alternativas
Alternativa simple
IF <condicion> THEN instrucciones; END IF;
Alternativa doble
IF <condicion> THEN instrucciones; ELSE instrucciones; END IF;
Alternativa múltiple (elsif)
IF <condicion> THEN instrucciones; ELSIF <condicion> THEN instrucciones; ELSIF <condicion> THEN instrucciones; ELSE instrucciones; END IF;
Repetitivas
Mientras
WHILE <condicion> LOOP Instrucciones; END LOOP;
Iterar… fin iterar salir si…
LOOP instrucciones; EXIT WHEN <condicion> ; instrucciones; END LOOP;
Bucle FOR
FOR <VARIABLECONTROL> IN <VALORINICIO>..<VALORFINAL> LOOP INSTRUCCIONES; END LOOP;
REVERSE
Ejemplo:
SQL> BEGIN FOR i IN REVERSE 1..3 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END;
Ejemplo de escritura de la cadena ‘HOLA’ al revés:
FOR
DECLARE R_CADENA VARCHAR2(10); BEGIN FOR I IN REVERSE 1..LENGTH('HOLA') LOOP R_CADENA := R_CADENA||SUBSTR('HOLA',i,1); END LOOP; DBMS_OUTPUT.PUT_LINE(R_CADENA); END;
WHILE
DECLARE R_CADENA VARCHAR2(10); i BINARY_INTEGER; BEGIN i := LENGTH('HOLA'); WHILE i >= 1 LOOP R_CADENA=R_CADENA||SUBSTR('HOLA',i,1); i := i - 1; END LOOP; DBMS_OUTPUT.PUT_LINE(R_CADENA); END;
Subprogramas: Procedimientos y Funciones en PL/SQL
Son bloques PL/SQL que reciben y devuelven valores. Partes:
- Cabecera: Contiene el nombre del subprograma, los parámetros con sus tipos y el tipo de valor de retorno (en caso de ser una función).
- Cuerpo: Contiene las declaraciones, instrucciones y el manejo de excepciones.
Procedimientos
PROCEDURE <nombreprocedimiento> [(<lista de parámetros>)] IS [<declaraciones>;] BEGIN [<instrucciones>;] [EXCEPTION <excepciones>;] END [<nombreprocedimiento>];
Ejemplo:
Crearemos un procedimiento que reciba un número de empleado y una cadena correspondiente a su nuevo oficio. El procedimiento deberá localizar el empleado, modificar el oficio y visualizar los cambios realizados.
CREATE OR REPLACE PROCEDURE cambiar_oficio (num_empleado NUMBER, nuevo_oficio VARCHAR2) AS v_anterior_oficio emple.oficio%TYPE; BEGIN SELECT oficio INTO v_anterior_oficio FROM emple WHERE emp_no = num_empleado; UPDATE emple SET oficio = nuevo_oficio WHERE emp_no = num_empleado; DBMS_OUTPUT.PUT_LINE(num_empleado||'*Oficio Anterior:'||v_anterior_oficio|| '*Oficio Nuevo :'||nuevo_oficio ); END cambiar_oficio; /
El sistema responderá: Procedimiento creado.
Ahora el procedimiento está creado y almacenado en la base de datos. Para ejecutarlo podemos invocar el procedimiento desde cualquier herramienta de Oracle, por ejemplo, desde SQL*Plus:
SQL> EXECUTE CAMBIAR_OFICIO(7902,'DIRECTOR');
Resultado: 7902*Oficio Anterior:ANALISTA*Oficio Nuevo :DIRECTOR
Procedimiento PL/SQL terminado con éxito.
Otro ejercicio:
CREATE OR REPLACE PROCEDURE cam_ofi_ (v_apellido VARCHAR, nue_oficio VARCHAR2) IS v_n_empleado emple.emp_no%TYPE; BEGIN SELECT emp_no INTO v_n_empleado FROM emple WHERE apellido = v_apellido; cambiar_oficio(v_n_empleado, nue_oficio); END cam_ofi_;
Parámetros
- Parámetros actuales o reales: Son las variables o expresiones indicadas en la llamada a un subprograma.
- Parámetros formales: Son variables declaradas en la especificación del subprograma.
PL/SQL soporta 3 tipos de parámetros:
- IN: Son parámetros de ENTRADA; se usan para pasar valores al subprograma. Dentro del subprograma el parámetro actúa como una constante, es decir, no se le puede asignar ningún valor. Por tanto, se sitúa siempre a la derecha del operador de asignación. El parámetro actual puede ser una variable, constante, literal o expresión.
- OUT: Son parámetros de SALIDA; se usan para devolver valores al programa que hizo la llamada. Dentro del subprograma, el parámetro actúa como una variable no inicializada y no puede intervenir en ninguna expresión, salvo para tomar un valor. Se sitúa siempre a la izquierda del operador de asignación. El parámetro actual debe ser una variable.
- IN OUT: Son parámetros de ENTRADA/SALIDA; permiten pasar un valor inicial y devolver un valor actualizado. Dentro del subprograma actúa como una variable inicializada. Puede intervenir en otras expresiones y puede tomar nuevos valores. El parámetro actual debe ser una variable.
Ejemplos de Manejo de Excepciones y Procedimientos en PL/SQL
Ejemplo 1:
El siguiente bloque visualiza el apellido y el oficio del empleado cuyo número es 7900.
DECLARE V_APE VARCHAR2(10); V_OFICIO VARCHAR2(10); BEGIN SELECT APELLIDO, OFICIO INTO V_APE, V_OFICIO FROM EMPLE WHERE EMP_NO = 7900; DBMS_OUTPUT.PUT_LINE(V_APE||'*'||V_OFICIO); END;
Ejemplo 2 (con gestión de excepciones):
DECLARE V_APE VARCHAR2(10); V_OFICIO VARCHAR2(10); BEGIN SELECT APELLIDO, OFICIO INTO V_APE, V_OFICIO FROM EMPLE WHERE EMP_NO = 7900; DBMS_OUTPUT.PUT_LINE(V_APE||'*'||V_OFICIO); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,'ERROR NO HAY DATOS'); WHEN TOO_MANY_ROWS THEN RAISE_APPLICATION_ERROR(-20000,'ERROR DEMASIADOS DATOS'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000,'ERROR EN LA APLICACIÓN'); END;
Ejemplo 3:
El siguiente programa solicitará la introducción de un número de cliente y visualizará el nombre del cliente correspondiente con el número introducido.
DECLARE V_NOM CLIENTES.NOMBRE%TYPE; BEGIN SELECT NOMBRE INTO V_NOM FROM CLIENTES WHERE CLIENTE_NO=&VN_CLI; DBMS_OUTPUT.PUT_LINE(V_NOM); END;
Ejemplo 4:
Procedimiento PL/SQL sencillo para consultar los datos de un cliente:
CREATE OR REPLACE PROCEDURE VER_DEPART (NUMDEPART NUMBER) AS V_DNOMBRE VARCHAR2(14); V_LOCALIDAD VARCHAR2(14); BEGIN SELECT DNOMBRE, LOC INTO V_DNOMBRE, V_LOCALIDAD FROM DEPART WHERE DEPT_NO = NUMDEPART; DBMS_OUTPUT.PUT_LINE('NUM DEPART:'||NUMDEPART|| ' * NOMBRE DEP:'|| V_DNOMBRE || ' * LOCALIDAD:'||V_LOCALIDAD); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO ENCONTRADO DEPARTAMENTO '); END VER_DEPART;
Ejemplo 5:
Procedimiento que reciba un número de empleado y una cadena correspondiente a su nuevo oficio. El procedimiento deberá localizar el empleado, modificar el oficio y visualizar los cambios.
CREATE OR REPLACE PROCEDURE CAMBIAR_OFICIO (NUM_EMPLEADO NUMBER,NUEVO_OFICIO VARCHAR2) AS V_ANTERIOR_OFICIO EMPLE.OFICIO%TYPE; BEGIN SELECT OFICIO INTO V_ANTERIOR_OFICIO FROM EMPLE WHERE EMP_NO = NUM_EMPLEADO; UPDATE EMPLE SET OFICIO = NUEVO_OFICIO WHERE EMP_NO = NUM_EMPLEADO; DBMS_OUTPUT.PUT_LINE(NUM_EMPLEADO||'*OFICIO ANTERIOR:'||V_ANTERIOR_OFICIO|| '*OFICIO NUEVO :'||NUEVO_OFICIO ); END CAMBIAR_OFICIO;
Ejemplo 6:
Procedimiento que visualiza el precio de un producto cuyo número se pasa como parámetro.
CREATE OR REPLACE PROCEDURE VER_PRECIO(V_NUM_PRODUCTO NUMBER) AS V_PRECIO NUMBER; BEGIN SELECT PRECIO_ACTUAL INTO V_PRECIO FROM PRODUCTOS WHERE PRODUCTO_NO = V_NUM_PRODUCTO; DBMS_OUTPUT.PUT_LINE('PRECIO = '||V_PRECIO); END;
Ejemplo de ejecución: SQL> EXECUTE VER_PRECIO(50);
Resultado: Precio = 1050
Ejemplo 7:
Procedimiento que modifique el precio de un producto pasándole el número del producto y el nuevo precio. El procedimiento comprobará que la variación de precio no supere el 20 por 100:
CREATE OR REPLACE PROCEDURE MODIFICAR_PRECIO_PRODUCTO (NUMPRODUCTO NUMBER, NUEVOPRECIO NUMBER) AS V_PRECIOANT NUMBER(5); BEGIN SELECT PRECIO_ACTUAL INTO V_PRECIOANT FROM PRODUCTOS WHERE PRODUCTO_NO = NUMPRODUCTO; IF (V_PRECIOANT * 0.20) > (NUEVOPRECIO - V_PRECIOANT) THEN UPDATE PRODUCTOS SET PRECIO_ACTUAL = NUEVOPRECIO WHERE PRODUCTO_NO = NUMPRODUCTO; ELSE DBMS_OUTPUT.PUT_LINE('ERROR, MODIFICACIÓN SUPERA 20%'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO ENCONTRADO PRODUCTO '|| NUMPRODUCTO); END MODIFICAR_PRECIO_PRODUCTO; /
Ejemplos de ejecución:
SET SERVEROUTPUT ON
EXECUTE MODIFICAR_PRECIO_PRODUCTO(60,300)
SELECT PRECIO_UNI FROM PRODUCTOS WHERE COD_PRODUCTO=60;
EXECUTE MODIFICAR_PRECIO_PRODUCTO(60,10000)
SELECT PRECIO_UNI FROM PRODUCTOS WHERE COD_PRODUCTO=3;