Oracle PL/SQL and SQL XML Generation Techniques

PL/SQL Components for Race Management

The following PL/SQL objects demonstrate core database programming concepts, including custom functions for calculation, procedures for data manipulation and ranking, and triggers for auditing.

1. Calculating Global Time: The tempo_global Function

This function calculates the total elapsed time for a specific pilot by summing the duration of all stages recorded in the TempoEtapas table. It includes robust error handling using SQLCODE.

CREATE FUNCTION tempo_global (ID_piloto IN NUMBER)
RETURN NUMBER
IS
    soma_tempo NUMBER;
    coderro NUMBER;
    CURSOR c1 IS
        SELECT tempo_inicio, tempo_fim
        FROM TempoEtapas
        WHERE Piloto_idPiloto = ID_piloto;

BEGIN
    soma_tempo := 0;
    FOR item IN c1 LOOP
        soma_tempo := soma_tempo + (item.tempo_fim - item.tempo_inicio);
    END LOOP;
    RETURN soma_tempo;
EXCEPTION
    WHEN OTHERS THEN
        coderro := SQLCODE;
        RAISE_APPLICATION_ERROR(-20002, 'Código erro: ' || coderro);
END;

2. Generating the Dakar Podium: The dakar_podium Procedure

This procedure determines the ranking for the Dakar event based on the calculated global time (using the tempo_global function). It filters pilots who have completed 14 stages, calculates their total time, and inserts the results (position, first name, last name, total time, and formatted time) into a temporary table (temp).

CREATE OR REPLACE PROCEDURE dakar_podium (categoria IN VARCHAR2) IS
    posicao NUMBER := 1;
    nomecompleto VARCHAR2(45);
    pnome VARCHAR2(45);
    unome VARCHAR2(45);
    tempoglobal NUMBER;

    CURSOR C1 IS
        SELECT nome, tempo_global(id_Piloto) AS tglobal
        FROM pilotos p
        JOIN tempoetapas te ON te.Pilotos.idPiloto = p.id_Piloto
        WHERE (SELECT COUNT(*) FROM tempoetapas WHERE Pilotos.idPiloto = p.idPiloto) = 14
        ORDER BY tempo_global(id_Piloto) ASC;

BEGIN
    DELETE FROM temp;
    OPEN C1;
    LOOP
        FETCH C1 INTO nomecompleto, tempoglobal;
        EXIT WHEN C1%NOTFOUND;

        pnome := SUBSTR(nomecompleto, 1, INSTR(nomecompleto, ' ') - 1);
        unome := SUBSTR(nomecompleto, INSTR(nomecompleto, ' ', -1) + 1);

        INSERT INTO temp (num1, str1, str2, num2, str3)
        VALUES (posicao, pnome, unome, tempoglobal, TO_CHAR(TO_DATE(tempoglobal, 'SSSSS'), 'HH24:MI:SS'));

        posicao := posicao + 1;
    END LOOP;
END;

3. Auditing Data Changes: The loggar Trigger

This AFTER row-level trigger records all INSERT, UPDATE, and DELETE operations performed on the TempoEtapas table into a Log table. It also includes a cleanup mechanism to remove log entries older than 5 days.

CREATE OR REPLACE TRIGGER loggar
AFTER UPDATE OR DELETE OR INSERT ON TempoEtapas
FOR EACH ROW
BEGIN
    -- Delete log entries older than 5 days
    DELETE FROM LOG WHERE SYSDATE - data_alteracao > 5;

    IF INSERTING THEN
        INSERT INTO Log VALUES (USER, SYSDATE, 'INSERT', :NEW.etapa_idetapa, :NEW.piloto_idPiloto, :NEW.tempo_inicio, :NEW.tempo_fim);
    ELSIF UPDATING THEN
        INSERT INTO Log VALUES (USER, SYSDATE, 'UPDATE', :OLD.etapa_idetapa, :OLD.piloto_idPiloto, :OLD.tempo_inicio, :OLD.tempo_fim);
    ELSIF DELETING THEN
        INSERT INTO Log VALUES (USER, SYSDATE, 'DELETE', :OLD.etapa_idetapa, :OLD.piloto_idPiloto, :OLD.tempo_inicio, :OLD.tempo_fim);
    END IF;
END;

SQL Techniques: Generating XML Data

These SQL queries demonstrate how to use Oracle's XML functions (specifically XMLELEMENT) to structure relational data into XML format, joining DEPARTMENTS and EMPLOYEES tables.

4. Basic XML Generation using XMLELEMENT

This query generates an XML structure where each row represents a DEPARTMENT containing nested Employee elements, filtered for departments 30 and 40.

SELECT
    XMLELEMENT("DEPARTMENT",
        XMLELEMENT("Employee", job_id, last_name)
    )
FROM DEPARTMENTS
JOIN employees ON departments.department_id = employees.department_id
WHERE department_id = 30 OR department_id = 40
ORDER BY last_name;

5. XML Generation with Attributes using XMLATTRIBUTES

This query enhances the XML output by adding the department_id as an attribute named deptno to the DEPARTMENT element.

SELECT
    XMLELEMENT("DEPARTMENT",
        XMLATTRIBUTES(department_id AS "deptno"),
        XMLELEMENT("Employee", job_id, last_name)
    )
FROM DEPARTMENTS
JOIN employees ON departments.department_id = employees.department_id
WHERE department_id = 30 OR department_id = 40
ORDER BY last_name;