1. Secciones de un bloque pl-sql:
DECLARE
- - sección declarativa, se declaran variables, constantes y cualquier dato accesible en todo el bloque
BEGIN
- - (obligatoria) sección ejecutable, inicia las sentencias del pl
EXCEPTION
- - sección de control de errores y de resultados no esperadosEND;
- - (obligatoria) fin del pl
1.1. Tipos de bloque pl-sql
Sin nombre: Se construyen dinámicamente dentro de otro pl y se guardan en memoria.
BEGIN
DBMS_OUTPUT.PUT_LINE('nombre');
END;
Con nombre: Son funciones o procedimientos con nombre y se guarda en la propia base de datos. Empiezan así:
CREATE OR REPLACE PROCEDURE ver_usuario(nomusu VARCHAR2)
2. Variables
Primero hay que habilitar la salida de script en la pantalla del editor, así podremos mostrar mensajes y depurar el código
set serveroutput on;
Creamos variables en la sección DECLARE
DECLARE
edad integer := 50;
-- define un número entero llamado edad y le asigna el valor 50
nombre varchar2(40) := 'John Doe';
-- define un texto con tamaño máximo 40 y le asigna un valor
id char(7) := 'AD00014';
-- define un texto con tamaño fijo de 7 y le asigna un valor
sueldo number(7,2) := 35000,00;
-- define un tamaño total y cuantas posiciones serán decimales.
-- En el ejemplo de 7 posiciones, 5 son enteros y 2 serán decimales
fecha_actual date := sysdate;
-- define una variable de tipo fecha y le asigna la fecha actual del sistema
fecha_nacimiento date := to_date('23/05/1971','dd/mm/yyyy');
-- Define una variable tipo fecha asignándole la fecha desde un formato concreto
cargo varchar2(50) default 'Empleado';
-- variable tipo texto con un valor por defecto
Mostramos el valor de las variables en la sección BEGIN
BEGIN dbms_output.put_line(‘Nombre : ‘ || nombre); -- usa una función del sistema dbms_output para mostrar el nombre -- La dos barras || encadenan un texto entre comillas simples con el valor de
la variable nombre
PL completo.
DECLARE
edad integer := 50;
nombre varchar2(40) := 'John Doe';
id char(7) := 'AD00014';
sueldo number(7,2) := 35000.00;
fecha_nacimiento date := to_date('23/05/1971','dd/mm/yyyy');
cargo varchar2(50) default 'Empleado';
BEGIN
DBMS_OUTPUT.PUT_LINE(nombre || '. ' || edad ||' años de edad');
DBMS_OUTPUT.PUT_LINE('Nació el ' || fecha_nacimiento);
DBMS_OUTPUT.PUT_LINE(cargo || ' ID-' || id || ', salario ' ||
sueldo || ' €/año');
END;
Salida por consola:
John Doe. 50 años de edad Nació el 23/05/71 Empleado ID-AD00014, salario 35000 €/año Procedimiento PL/SQL terminado correctamente.
3. Constantes
Se declaran igual que las variables pero con la palabra reservada CONSTANT y ya no se les puede cambiar su valor.
DECLARE
id CONSTANT char(7) := 'AD00014';
BEGIN
id := ‘AD00015’;
-- aquí se genera un error
END;
4. Condicionales
La orden para una condicional es IF.
if edad < 18 then
DBMS_OUTPUT.PUT_LINE('Es menor de edad');
elsif edad = 18 then
DBMS_OUTPUT.PUT_LINE('Acaba de cumplir la mayoría de edad');
elsif edad > 18 and edad < 65 then
DBMS_OUTPUT.PUT_LINE('Nació el ' || fecha_nacimiento);
else
DBMS_OUTPUT.PUT_LINE('Está en edad de jubilación');
end if;
Si el valor de la variable edad es menor a 18 muestra un mensaje por pantalla, si es 18 muestra otro. Si fuese mayor a 18 y menor a 65 muestra la fecha de nacimiento y si no se cumplen las condiciones anteriores se muestra otro mensaje.
5. Bucle loop
Se combina con el condicional if para repetir una serie de sentencias hasta que se cumple la condición y se llama a la sentencia exit
DECLARE
contador INTEGER := 0;
BEGIN
LOOP
dbms_output.put_line('Contador = ' || contador);
contador := contador + 1;
IF contador = 10 THEN
EXIT;
END IF;
END LOOP;
END;
También se puede usar el comando WHEN para salir del bucle cuando se cumpla la condición.
DECLARE
contador INTEGER := 0;
BEGIN
LOOP
dbms_output.put_line('Contador = ' || contador);
contador := contador + 1;
EXIT WHEN contador = 10;
END LOOP;
END;
La salida en ambos casos sería:
Contador = 0 Contador = 1 Contador = 2 Contador = 3 Contador = 4 Contador = 5 Contador = 6 Contador = 7 Contador = 8 Contador = 9 Procedimiento PL/SQL terminado correctamente.
6. Gestión de cadenas de texto
Funciones propias de Oracle para cambiar cadenas de texto.
DECLARE
id CONSTANT char(7) := 'AD00014';
cargo varchar2(50) default ' EMPLEADO ';
BEGIN
DBMS_OUTPUT.PUT_LINE ('Código de empleado: ' || SUBSTR(id, 1,2));
-- obtiene los dos primeros caracteres que indican el codigo
-- función SUBSTR(texto, posición, longitud) si la posición es negativa
empieza por la derecha
DBMS_OUTPUT.PUT_LINE ('Número de empleado: ' || LTRIM( SUBSTR(id, -5,5),
'0'));
--Obtiene el numero al final de la cadena limpiando los ceros a la izquierda
-- función LTRIM( texto, carácter a borrar por la izquierda)
DBMS_OUTPUT.PUT_LINE ('Cargo: ' || INITCAP(LOWER(TRIM(' ' from cargo))));
-- obtiene el cargo asegurándose que esté en minuscula(LOWER) y la primera en
mayúscula(INITCAP)
-- función TRIM(carácter a borrar a izquierda y derecha, texto)
END;
Salida del PL
Código de empleado: AD Número de empleado: 14 Cargo: Empleado Procedimiento PL/SQL terminado correctamente.
7. Bucle while
Se repite el bucle mientras se cumpla la condición después del WHILE
DECLARE
contador INTEGER := 0;
BEGIN
WHILE contador < 10 LOOP
dbms_output.put_line('Contador = ' || contador);
contador := contador + 1;
END LOOP;
END;
8.BucleFOR
DECLARE BEGIN FOR CLIENTE IN (SELECT ID_CLIENTE, NOMBRE FROM MSV_CLIENTES
WHERE ROWNUM < 5 ORDER BY ID_CLIENTE) -- recorre los 5 registros que ha traído la consulta LOOP dbms_output.put_line('ID: ' || CLIENTE.ID_CLIENTE || ' Nombre: '
|| CLIENTE.NOMBRE); END LOOP; END;
Salida
ID: 1 Nombre: Ana María ID: 2 Nombre: Josep ID: 3 Nombre: GREGORIA ID: 4 Nombre: Mauricio Procedimiento PL/SQL terminado correctamente.
9. Bucles anidados
Se trata de un bucle dentro de otro bucle. Por ejemplo para mostrar la tabla de multiplicar de varios números
DECLARE
TABLA INTEGER :=1;
NUMERO INTEGER :=1;
BEGIN
WHILE TABLA < 5 LOOP
WHILE NUMERO < 10 LOOP
DBMS_OUTPUT.PUT_LINE(TABLA || ' x ' || NUMERO || ' = ' || TABLA * NUMERO);
NUMERO := NUMERO + 1;
END LOOP;
TABLA := TABLA +1;
NUMERO := 1;
END LOOP;
END;
-- muestra la tabla de multiplicar del 1 al 4
-
1 x 1 = 1
1 x 2 = 2
1 x 3 = 3
1 x 4 = 4
1 x 5 = 5
1 x 6 = 6
1 x 7 = 7
1 x 8 = 8
1 x 9 = 9
2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
2 x 4 = 8
2 x 5 = 10
2 x 6 = 12
2 x 7 = 14
2 x 8 = 16
2 x 9 = 18
3 x 1 = 3
3 x 2 = 6
3 x 3 = 9
3 x 4 = 12
3 x 5 = 15
3 x 6 = 18
3 x 7 = 21
3 x 8 = 24
3 x 9 = 27
4 x 1 = 4
4 x 2 = 8
4 x 3 = 12
4 x 4 = 16
4 x 5 = 20
4 x 6 = 24
4 x 7 = 28
4 x 8 = 32
4 x 9 = 36
10. Arrays
En los arrays de PL/SQL el primer elemento es el 1, a diferencia del resto de lenguajes de programación que el primer elemento de un array siempre es el 0. Y deben inicializarse antes de poder asignarles un valor.
DECLARE
TYPE array_nombres IS VARRAY (5) OF VARCHAR2(30);
-- primero se define el tipo que será un VARRAY de 5 valores de texto de hasta
30 caracteres
nombres array_nombres;
-- se crea una variable del tipo creado anteriormente
BEGIN
nombres := array_nombres('','','','','');
-- es necesario inicializar el array para poder usarlo
nombres(1) := 'Pedro';
nombres(2) := 'Juan';
nombres(3) := 'María';
nombres(4) := 'Isabel';
nombres(5) := 'Fernando';
for n in 1..nombres.COUNT loop
-- la variable n toma los valores 1,2,3,4 y 5 que es el tamaño del array
DBMS_OUTPUT.PUT_LINE( n || ' - ' || nombres(n));
end loop;
END;
La salida sería esta:
1 - Pedro 2 - Juan 3 - María 4 - Isabel 5 - Fernando Procedimiento PL/SQL terminado correctamente.
11. Procedimientos almacenados
create or replace procedure saludo
as
begin
DBMS_OUTPUT.PUT_LINE('Hola');
end saludo;
Salida al compilarlo
Procedure SALUDO compilado
Una vez compilado se guarda esquema actual de la base de datos. En la sección Procedimientos
Para ejecutarlo una vez compilado
execute saludo; -- solo para SQL*plus
o también.
begin
saludo;
end; -- método estándar, permite además varias líneas
Salida
Hola Procedimiento PL/SQL terminado correctamente.
En la sección Procedimientos aparecerá SALUDO y si miramos en la pestaña detalles nos saldrá toda la información del procedimiento
Para buscarlo por sql y que nos muestre todos sus campos también podemos usar la tabla user_objects o user_procedures:
select * from user_objects where object_type = 'PROCEDURE'
and object_name = 'SALUDO'; select * from user_procedures where object_name = 'SALUDO';
Ejercicio: La tabla msv_clientes tiene un campo descuento que hay que dejar a 0 en todos los clientes
create or replace procedure borra_descuentos as begin update msv_clientes set descuento = 0; end borra_descuentos;
Ejecutando el procedimiento ya compilado
begin borra_descuentos; end;
12. Parámetros entrada en procedimientos
Procedimiento que indica el porcentaje de descuento por parámetro de entrada.
create or replace procedure aplica_descuentos(cantidad in integer)
-- cantidad es el nombre, in es el parámetro que indica entrada, integer
es el tipo de dato as begin update msv_clientes set descuento = cantidad where msv_clientes.fecha_baja is null; --condicional para solo aplicar el descuento a clientes activos end aplica_descuentos;
Ejecución
begin aplica_descuentos(5); end;
13. Procedimientos almacenados con variables
Ejercicio: Partiendo de un nif, aplicar un descuento del 10% si tiene contratado
más de un servicio
Consultas previas de ayuda para montar el procedimiento
-- consulta los clientes con sus servicios contratados select msv_clientes.nombre,msv_clientes.doi, msv_servicios.tratamiento
from msv_clientes inner join msv_servicios on msv_clientes.id_cliente = msv_servicios.id_cliente; --consulta el número de servicios contratados por un cliente concreto Select count(*) from msv_clientes inner join msv_servicios on msv_clientes.id_cliente = msv_servicios.id_cliente and msv_clientes.doi = '58438039F';
Procedimiento completo
create or replace procedure descuento_especial(documento in varchar2,cantidad
in integer) as v_numservicios integer; v_id_cliente integer; -- crea dos variables para recoger el número de servicios contratados y
su id_cliente begin select count(*) into v_numservicios from msv_clientes inner join msv_servicios on msv_clientes.id_cliente = msv_servicios.id_cliente and msv_clientes.doi = documento; --consulta el número de servicios y almacena el resultado
en v_numservicios if v_numservicios > 0 then select msv_clientes.id_cliente into v_id_cliente from msv_clientes where msv_clientes.doi = documento; -- si el numero de servicios es mayor a uno obtiene su id_cliente update msv_clientes set descuento = cantidad where msv_clientes.id_cliente = v_id_cliente; -- usa el id_cliente para actualizar su campo descuento end if; end descuento_especial;
Ejecución del procedimiento
begin
descuento_especial('58438039F',15);
end descuento_especial;
14. Funciones
Función que monta el nombre completo con el apellido primero
create or replace function f_completo(nombre varchar2, apellido1 varchar2,
apellido2 varchar2) return varchar2 is begin return apellido1 || ' ' || apellido2 || ', ' || nombre; end;
Ejecución de la función en una select
select f_completo(nombre, apellido1, apellido2) from msv_clientes;
15. Funciones con sentencia CASE
create or replace function f_diasemana(numero int) return varchar2 is dia varchar2(25); begin dia:=’’; case numero when 1 then dia := ‘Lunes’; when 2 then dia := ‘Martes’; when 3 then dia := ‘Miércoles’; when 4 then dia := ‘Jueves’; when 5 then dia := ‘Viernes’; when 6 then dia := ‘Sábado’; when 7 then dia := ‘Domingo’; else dia :=’DIA NO VALIDO’; end case; return dia; end;
Para ejecutar la función
select f_diasemana(1) from msv_clientes; -- no se usa la tabla pero se necesita indicar una para ejecutar el select
No hay comentarios:
Publicar un comentario