El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

En aquest tema, aprendrem a construir una aplicació senzilla utilitzant PL/SQL. Aquesta aplicació ens permetrà consolidar els coneixements adquirits en els mòduls anteriors i veure com es poden aplicar en un context pràctic.

Objectius

Crear una base de dades simple.
Desenvolupar procediments emmagatzemats per gestionar dades.
Implementar funcions per realitzar càlculs.
Utilitzar triggers per automatitzar tasques.
Aplicar tècniques de depuració i optimització.

Pas 1: Crear la base de dades

1.1 Definir l'esquema de la base de dades

Per a la nostra aplicació, crearem una base de dades per gestionar una biblioteca. La base de dades tindrà dues taules principals: books i authors.

CREATE TABLE authors (
    author_id NUMBER PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

CREATE TABLE books (
    book_id NUMBER PRIMARY KEY,
    title VARCHAR2(200) NOT NULL,
    author_id NUMBER,
    published_date DATE,
    CONSTRAINT fk_author
        FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
);

1.2 Inserir dades inicials

Afegirem algunes dades inicials a les nostres taules per poder treballar amb elles.

INSERT INTO authors (author_id, name) VALUES (1, 'George Orwell');
INSERT INTO authors (author_id, name) VALUES (2, 'Aldous Huxley');

INSERT INTO books (book_id, title, author_id, published_date) VALUES (1, '1984', 1, TO_DATE('1949-06-08', 'YYYY-MM-DD'));
INSERT INTO books (book_id, title, author_id, published_date) VALUES (2, 'Brave New World', 2, TO_DATE('1932-08-01', 'YYYY-MM-DD'));

Pas 2: Desenvolupar procediments emmagatzemats

2.1 Procediment per afegir un nou llibre

Crearem un procediment per afegir un nou llibre a la taula books.

CREATE OR REPLACE PROCEDURE add_book (
    p_title IN VARCHAR2,
    p_author_id IN NUMBER,
    p_published_date IN DATE
) IS
BEGIN
    INSERT INTO books (book_id, title, author_id, published_date)
    VALUES (books_seq.NEXTVAL, p_title, p_author_id, p_published_date);
END;
/

2.2 Procediment per actualitzar la informació d'un llibre

Crearem un procediment per actualitzar la informació d'un llibre existent.

CREATE OR REPLACE PROCEDURE update_book (
    p_book_id IN NUMBER,
    p_title IN VARCHAR2,
    p_author_id IN NUMBER,
    p_published_date IN DATE
) IS
BEGIN
    UPDATE books
    SET title = p_title,
        author_id = p_author_id,
        published_date = p_published_date
    WHERE book_id = p_book_id;
END;
/

Pas 3: Desenvolupar funcions

3.1 Funció per obtenir el nombre de llibres d'un autor

Crearem una funció que retorni el nombre de llibres escrits per un autor determinat.

CREATE OR REPLACE FUNCTION get_books_count_by_author (
    p_author_id IN NUMBER
) RETURN NUMBER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM books
    WHERE author_id = p_author_id;
    
    RETURN v_count;
END;
/

Pas 4: Implementar triggers

4.1 Trigger per actualitzar la data de modificació

Crearem un trigger que actualitzi la data de modificació d'un llibre cada vegada que es faci un canvi en la seva informació.

CREATE OR REPLACE TRIGGER update_book_mod_date
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
    :NEW.modified_date := SYSDATE;
END;
/

Pas 5: Depuració i optimització

5.1 Depuració

Utilitzarem la funció DBMS_OUTPUT.PUT_LINE per depurar els nostres procediments i funcions.

CREATE OR REPLACE PROCEDURE add_book (
    p_title IN VARCHAR2,
    p_author_id IN NUMBER,
    p_published_date IN DATE
) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Adding book: ' || p_title);
    INSERT INTO books (book_id, title, author_id, published_date)
    VALUES (books_seq.NEXTVAL, p_title, p_author_id, p_published_date);
END;
/

5.2 Optimització

Revisarem els nostres procediments i funcions per assegurar-nos que utilitzen els índexs correctament i que no hi ha consultes innecessàries.

Exercicis pràctics

Crear un procediment per eliminar un llibre: Desenvolupa un procediment que permeti eliminar un llibre de la taula books donant el seu book_id.
Crear una funció per obtenir el títol d'un llibre: Desenvolupa una funció que retorni el títol d'un llibre donant el seu book_id.
Crear un trigger per evitar la inserció de llibres duplicats: Desenvolupa un trigger que impedeixi la inserció de llibres amb el mateix títol i autor.

Solucions

Procediment per eliminar un llibre:

CREATE OR REPLACE PROCEDURE delete_book (
    p_book_id IN NUMBER
) IS
BEGIN
    DELETE FROM books
    WHERE book_id = p_book_id;
END;
/

Funció per obtenir el títol d'un llibre:

CREATE OR REPLACE FUNCTION get_book_title (
    p_book_id IN NUMBER
) RETURN VARCHAR2 IS
    v_title VARCHAR2(200);
BEGIN
    SELECT title
    INTO v_title
    FROM books
    WHERE book_id = p_book_id;
    
    RETURN v_title;
END;
/

Trigger per evitar la inserció de llibres duplicats:

CREATE OR REPLACE TRIGGER prevent_duplicate_books
BEFORE INSERT ON books
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM books
    WHERE title = :NEW.title
    AND author_id = :NEW.author_id;
    
    IF v_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Duplicate book entry');
    END IF;
END;
/

Conclusió

En aquest tema, hem après a construir una aplicació senzilla utilitzant PL/SQL. Hem creat una base de dades, desenvolupat procediments emmagatzemats, funcions i triggers, i hem aplicat tècniques de depuració i optimització. Aquestes habilitats són fonamentals per desenvolupar aplicacions més complexes i eficients en el futur.

Construir una aplicació senzilla