PL/SQL
Francisco Moreno
Universidad Nacional
Subprogramas: Procedimientos
• A excepción de los triggers, hasta ahora los
bloques PL/SQL que se han presentado son
– Anónimos (sin nombre)
– Temporales (no quedan almacenados en la BD)
• Los bloques PL/SQL se pueden almacenar
(garantizar su persistencia) en la BD mediante
subprogramas (funciones y procedimientos)
• Los subprogramas pueden tener parámetros
Procedimientos
Sintaxis:
CREATE [OR REPLACE] PROCEDURE
nombre_procedimiento
[( par1 [modo] tipo [, par2 [modo]
tipo...])]
IS | AS
Bloque PL/SQL
• La opción REPLACE remplaza el procedimiento
si existe
• Se puede usar AS o IS (son equivalentes)
• El bloque PL/SQL comienza con la
palabra BEGIN o con la declaración de las
variables locales (sin usar la palabra
DECLARE).
• Para ver los errores de compilación en SQL*Plus
se puede usar el comando SHOW ERRORS
Herramientas como SQL Navigator, PL/SQL
Developer, entre otras; facilitan mucho las
labores de depuración.
• El "modo" especifica el tipo del parámetro así:
- IN (modo predeterminado): Parámetro de
entrada.
- OUT: Parámetro de salida. El subprograma
devuelve un valor en el parámetro.
- IN OUT: Parámetro de entrada y salida. El
subprograma devolverá un valor posiblemente
diferente al enviado inicialmente.
• Para declarar los parámetros usar en lo posible %TYPE
y %ROWTYPE
• No se puede especificar tamaño para los parámetros en
lo que respecta al tipo de datos
Ejemplo 1. Sea la tabla:
CREATE TABLE registro(
id_usuario VARCHAR2(10),
fecha DATE,
estacion VARCHAR2(15)
);
CREATE OR REPLACE PROCEDURE registrarse IS
BEGIN
INSERT INTO registro
VALUES (USER, SYSDATE,
USERENV('TERMINAL'));
END;
/
Para ejecutarlo en SQL*Plus:
EXECUTE registrarse;
registrarse se puede invocar, por ejemplo,
desde un trigger de LOGON así:
CREATE OR REPLACE TRIGGER
registrar
AFTER LOGON ON DATABASE
BEGIN
registrarse; --Llama al procedimiento
END;
/
Para crear este trigger se requiere:
GRANT ADMINISTER DATABASE TRIGGER TO
user;
Ejemplo 2. Sea la tabla:
DROP TABLE emp;
CREATE TABLE emp(
cod NUMBER(8) PRIMARY KEY,
nom VARCHAR2(15),
depto NUMBER(3)
);
INSERT INTO emp VALUES(12,'María',10);
INSERT INTO emp VALUES(15,'Ana',5);
INSERT INTO emp VALUES(76,'Lisa',15);
CREATE OR REPLACE PROCEDURE
consulta_emp
(v_nro IN [Link]%TYPE) Parámetro de entrada
IS
v_nom [Link]%TYPE;
BEGIN
SELECT nom INTO v_nom
FROM emp
WHERE cod = v_nro;
DBMS_OUTPUT.PUT_LINE(v_nom);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Empleado no existe');
END;
/
EXECUTE consulta_emp(15);
Los parámetros de salida usualmente son recibidos por
variables de otros subprogramas que los invocan.
CREATE OR REPLACE PROCEDURE consulta_emp
(v_nro IN [Link]%TYPE, v_nom OUT [Link]
%TYPE)
Parámetro de salida
IS
BEGIN
SELECT nom INTO v_nom -- Se llena el parámetro
FROM emp
WHERE cod = v_nro;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_nom := 'Sin nombre'; -- Si no se llena queda en
NULL
END;
/
Invocación desde otro subprograma:
CREATE OR REPLACE PROCEDURE
invoca_consulta(v_nro IN [Link]%TYPE)
IS
nombre [Link]%TYPE; Parámetro de salida
BEGIN Retornará con el nombre
consulta_emp(v_nro, nombre);
DBMS_OUTPUT.PUT_LINE('El nombre es: ' ||
nombre);
END;
/
Para ejecutar: EXECUTE invoca_consulta(15);
Ejemplo 3
• Sea el modelo:
MOVIMIENTO CUENTA
de
#cons #nro
generadora
*valor de *saldo
Sean las tablas:
CREATE TABLE cuenta(
nro NUMBER(8) PRIMARY KEY,
saldo NUMBER(8) NOT NULL
);
CREATE TABLE mvto(
cons NUMBER(8) PRIMARY KEY,
valor NUMBER(8) NOT NULL,
cta NUMBER(8) NOT NULL REFERENCES cuenta
);
Ingreso de datos:
INSERT INTO cuenta VALUES(1,100);
INSERT INTO cuenta VALUES(2,400);
INSERT INTO cuenta VALUES(3,600);
INSERT INTO mvto VALUES(3,20, 2);La cuenta 1 no
INSERT INTO mvto VALUES(4,20, 2);tiene
INSERT INTO mvto VALUES(5,30, 2);movimientos
INSERT INTO mvto VALUES(6,20, 2);
INSERT INTO mvto VALUES(7,20, 2);
INSERT INTO mvto VALUES(8,10, 2);
INSERT INTO mvto VALUES(1,30, 3);
INSERT INTO mvto VALUES(2,10, 3);
Desarrollar un procedimiento que imprima
cada cuenta con sus n valores de
movimiento más altos, en formato horizontal:
Ejemplo: si n = 3, la salida debe ser:
No tiene movimientos
1
2 30 20 20
3 30 10 Solo tiene dos movimientos
Aunque acá se muestran los datos ordenados por nro. de cuenta,
por simplicidad no se considerará este aspecto en la solución
CREATE OR REPLACE
PROCEDURE topn_horizontal(n IN POSITIVE) IS
cadena VARCHAR2(1000);
cont NUMBER(8);
BEGIN
FOR mi_c1 IN (SELECT nro FROM cuenta) LOOP
cadena := mi_c1.nro;
cont := 0;
FOR mi_c2 IN (SELECT valor FROM mvto WHERE cta =
mi_c1.nro
ORDER BY 1 DESC) LOOP
cadena := cadena || ' ' || mi_c2.valor;
cont := cont + 1;
EXIT WHEN cont = n;
END LOOP;
DBMS_OUTPUT.PUT_LINE(cadena);
END LOOP;
END;
• ¿La solución usando solo SQL?
• La idea es crear una consulta SQL en la
que el usuario solo indica el valor de n
• El problema es que en el momento de
escribir la consulta no se sabe cuantas
columnas se van a necesitar para los top n
valores Pero afortunadamente esto se
puede solucionar, por ejemplo, con la
función de agregación LISTAGG
Pero primero, ¿qué hace la siguiente consulta?
Se usa la función analítica ROW_NUMBER.
Una vista
CREATE OR REPLACE VIEW v_pos AS
SELECT cta, valor,
ROW_NUMBER() OVER (PARTITION
BY cta
ORDER BY valor DESC) AS pos
FROM mvto; Nombre para la
columna de salida
Para crear vistas se requiere:
GRANT CREATE ANY VIEW
TO user;
Función LISTAGG:
CREATE TABLE cancion(idalbum NUMBER(8),idtrack
NUMBER(8),PRIMARY KEY(idalbum, idtrack),titulo
VARCHAR2(20));
INSERT INTO cancion VALUES(1, 1, 'Sweet Freeek');
INSERT INTO cancion VALUES(1, 2, 'Sledgehammer');
INSERT INTO cancion VALUES(1, 3, 'In The Name Of Love');
INSERT INTO cancion VALUES(2, 1, 'Forward');
INSERT INTO cancion VALUES(2, 2, 'Sunlight');
Cadena de
caracteres para
separar los valores
¿Qué hace esta consulta? a concatenar
SELECT idalbum, LISTAGG(idtrack||' '||titulo, ' - ')
WITHIN GROUP(ORDER BY idtrack)
FROM cancion
GROUP BY idalbum;
• Ahora ¿qué hace la siguiente consulta?
En este ejemplo el
separador es el
SELECT cta, LISTAGG(valor,' ') espacio
WITHIN GROUP(ORDER BY valor
DESC)
AS listado
FROM v_pos
GROUP BY cta;
• Entonces:
SELECT cta || ' ' || LISTAGG(valor, ' ')
WITHIN GROUP(ORDER BY valor
DESC)
AS listado
FROM v_pos Colocar acá el
número deseado
WHERE pos <= n
GROUP BY cta;
Pero hay un problema:
¡quedan faltando las cuentas que no tienen movimientos!
Entonces, una solución en SQL es:
SELECT [Link] || ' ' ||
(SELECT LISTAGG(valor, ' ')
WITHIN GROUP(ORDER BY valor
DESC)
FROM v_pos
WHERE cta = [Link] AND pos <= n) AS
listado
Colocar acá el
FROM cuenta c1; número deseado
• Tarea analizar la siguiente consulta y ver si es
equivalente a la anterior solución:
SELECT nro || ' ' || listado
FROM cuenta LEFT OUTER JOIN
(SELECT LISTAGG(valor, ' ')
WITHIN GROUP(ORDER BY valor DESC)
AS listado, cta
FROM v_pos
Colocar acá el
WHERE pos <= n número deseado
GROUP BY cta
) ON (cta = nro);
• Sin embargo, dada la cantidad de
funciones nuevas que se han incorporado
a SQL en las últimas versiones, este
empieza a perder su simplicidad y quizás
sea preferible una solución vía PL/SQL…
Ejercicio: hacer el mismo problema anterior
pero sin considerar valores repetidos, es
decir, los n valores de movimiento más
altos no repetidos