Programación PL/SQL: Tipos de Datos, Estructuras de Control y Subprogramas

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;

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.