Mòdul ASGBD

ASGBD - Automatització de tasques en Oracle


Automatització

L'automatització consisteix a fer tasques de manera sistemàtica i repetitiva sense que estiga involucrat un usuari en la seua execució

Avantatges Automatització

Tipus

Rutina

El SGBD deu proporcionar:

Avantatges rutina interna

Avantatges Automatització

Les rutines: S’han de Documentar

stop

QUE ??

Descripció de la tasca, descripció de paràmetres d’entrada i eixida, Autor, Versió, Data d’última modificació, etc..

COM ??

Fent servir comentaris en el codi de les rutines. Amb /* */ o --

Fent servir documentació institucional en qualsevol suport (docs,fulles de càlcul,diagrames,etc..)


🧾 Bloc Anònim en PL/SQL

Què és un bloc anònim?

Un bloc anònim és una unitat de codi en PL/SQL que:

Estructura bàsica d’un bloc anònim

  SET SERVEROUTPUT ON;
  
  DECLARE
     vNom VARCHAR2(20) := '&nom';
  BEGIN
     DBMS_OUTPUT.PUT_LINE('Hola ' || vNom);
     DBMS_OUTPUT.PUT_LINE('Benvingut a la programació en PL/SQL');
  END;
  .  -- Només en SQL*Plus!

⚠️ En SQL*Plus s’ha de posar un punt (.) al final per enviar-lo al buffer i després executar-lo amb RUN. En SQL Developer, s’executa amb F5 i no cal posar el punt.

💬 Explicació pas a pas

&nom Una variable de substitució és una variable que s'utilitza principalment en eines com SQL*Plus, SQL Developer o Toad, per a permetre que l’usuari introduïsca un valor en temps d’execució, abans que s'execute un bloc PL/SQL o una sentència SQL. No és part del llenguatge PL/SQL com a tal, sinó una funcionalitat d’aquestes eines per fer scripts més dinàmics i interactius.

PUT_LINE És un procediment del paquet DBMS_OUTPUT d'ORACLE.

PUT_LINE avalua !! i imprimeix el resultat.

  DBMS_OUTPUT.PUT_LINE('La data actual és: '); -- una cadena
  DBMS_OUTPUT.PUT_LINE(  vdata  );             -- una variable (el contenido)
  DBMS_OUTPUT.PUT_LINE(  'vdata'  );           -- una cadena
  DBMS_OUTPUT.PUT_LINE(  sysdate );            --  resultado de una funcion
  DBMS_OUTPUT.PUT_LINE('La data actual és: ' ||  sysdate);  -- una concatenación
  DBMS_OUTPUT.PUT_LINE(  3*4+5**2+1 );              --  un cálculo matemático
  DBMS_OUTPUT.PUT_LINE(  3*4+5**2+1 || '  456');    --  un casting  !!

Exemple senzill

  SET SERVEROUTPUT ON;
  
  DECLARE
     vData DATE := SYSDATE;
  BEGIN
     DBMS_OUTPUT.PUT_LINE('La data actual és: ' || TO_CHAR(vData, 'DD/MM/YYYY'));
  END;

SYSDATE és una funció d'ORACLE que torna la data/temps del instant actual

TO_CHAR és una funció d'ORACLE que transforma de format DATA a format VARCHAR2

Bones pràctiques

Ordres útils en SQL*Plus

.. 📘 Es pot ampliar amb *Tutorial d' sql*plus ..

Ordres útils en SQL Developer

Diferència amb procediments

Els blocs anònims no es poden reutilitzar directament, ja que no tenen nom. Per a reutilització, millor usar procediments o funcions.

🧪 Prova-ho!

Prova aquest bloc en SQL Developer: 

  DECLARE
     vNom VARCHAR2(20) := 'Ada';
  BEGIN
     DBMS_OUTPUT.PUT_LINE('Hola ' || vNom || '! Bon dia!');
  END;

🧪 Pràctica interactiva: Bloc Anònim

Prova aquí mateix la sintaxi d’un bloc anònim PL/SQL. Es la pàgina oficial de ORACLE.

Simulador de codi de ORACLE

🗃️ Comentaris, Variables i Operacions en PL/SQL

Comentaris

Lùs de comentaris és un recurs per poder documentar el codi. Com s'ha dit abans, és molt importat documentar el codi

      --  En dos guionets es posa un comentari d'una línia.
      /*  En la barra i l'asterisc es pot
          posar comentaris de més d'una línia
      */

Variables

Una variable en PL/SQL és un espai de memòria per emmagatzemar dades temporals durant l'execució d’un bloc de codi. Pot contindre textos, números, dates, etc.

Estructura bàsica

DECLARE
       nom_variable tipus [:= valor_inicial];
    BEGIN
       -- ús de la variable
    END;
    /

Tipus bàsics

Tipus Descripció Exemple
VARCHAR2(n) Cadena de text de longitud variable 'Hola', 'Joan'
NUMBER(p,s)
NUMBER(p)
NUMBER		 Números amb precisió i escala 123, 45.67
DATE Data i hora SYSDATE
BOOLEAN TRUE, FALSE, NULL (només PL/SQL) TRUE
%TYPE Tipus igual al d’una columna taula.columna%TYPE

🧪 Exemple de declaració

DECLARE
       v_nom       VARCHAR2(50) := 'Anna';
       v_edat      NUMBER := 30;
       v_sou       NUMBER(8,2);
       v_data_naix DATE := TO_DATE('1995-06-10', 'YYYY-MM-DD');
       v_es_actiu  BOOLEAN := TRUE;
    BEGIN
       DBMS_OUTPUT.PUT_LINE('Nom: ' || v_nom);
       DBMS_OUTPUT.PUT_LINE('Edat: ' || v_edat);
    END;
    /

Variables amb %TYPE

DECLARE
       v_nom_client clients.nom%TYPE;
    BEGIN
       SELECT nom INTO v_nom_client FROM clients WHERE id = 1;
       DBMS_OUTPUT.PUT_LINE('Client: ' || v_nom_client);
    END;
    /

🛠️ Com assignar valors

❗ Detalls importants

Operacions

Operacions amb Variables en PL/SQL

Una vegada declarades, les variables en PL/SQL poden utilitzar-se per fer càlculs, tractament de text, dates i lògica condicional. Ací tens exemples clars i útils:

📐 1. Operacions aritmètiques

Per fer sumes, restes, multiplicacions i divisions amb variables de tipus NUMBER:

DECLARE
       a NUMBER := 10;
       b NUMBER := 3;
       resultat NUMBER;
    BEGIN
       resultat := a + b;
       DBMS_OUTPUT.PUT_LINE('Suma: ' || resultat);
    
       resultat := a - b;
       DBMS_OUTPUT.PUT_LINE('Resta: ' || resultat);
    
       resultat := a * b;
       DBMS_OUTPUT.PUT_LINE('Multiplicació: ' || resultat);
    
       resultat := a / b;
       DBMS_OUTPUT.PUT_LINE('Divisió: ' || resultat);
    END;
    /

🔤 2. Operacions amb cadenes de text

Les cadenes (VARCHAR2) es concatenen amb ||.

DECLARE
       nom VARCHAR2(20) := 'Joan';
       cognom VARCHAR2(20) := 'Garcia';
       nom_complet VARCHAR2(50);
    BEGIN
       nom_complet := nom || ' ' || cognom;
       DBMS_OUTPUT.PUT_LINE('Nom complet: ' || nom_complet);
    END;
    /

📅 3. Operacions amb dates

Les dates es poden restar o sumar, on 1 unitat és 1 dia:

DECLARE
       data_naix DATE := TO_DATE('2000-01-01', 'YYYY-MM-DD');
       avui      DATE := SYSDATE;
       edat_dies NUMBER;
    BEGIN
       edat_dies := avui - data_naix;
       DBMS_OUTPUT.PUT_LINE('Dies des de naixement: ' || edat_dies);
    
       DBMS_OUTPUT.PUT_LINE('Demà serà: ' || (avui + 1));
    END;
    /

✔️ El tipus "DATE" pot emmagatzemar fins al minut i el segon. (Exemple d'un valor de tipus DATE: 23-MAI-2025 14:35:22)
Si es necessita més precisió, es pot utilitzar el tipus "TIMESTAMP", el qual pot emmagatzemar fins a milionèsima de segon (Exemple d'un valor de tipus TIMESTAMP: 23-MAI-2025 14:35:22.123456)

4. Operacions lògiques

Amb variables BOOLEAN, pots fer condicions:

DECLARE
       actiu BOOLEAN := TRUE;
       menor BOOLEAN := FALSE;
    BEGIN
       IF actiu AND NOT menor THEN
          DBMS_OUTPUT.PUT_LINE('L’usuari està actiu i no és menor.');
       END IF;
    END;
    /

Pots utilitzar variables BOOLEAN o fer comparacions entre nombres, textos o dates amb els operadors següents:

🧪 Exemple 1: comparacions numèriques

DECLARE
      nota NUMBER := 7.5;
    BEGIN
      IF nota >= 5 THEN
          DBMS_OUTPUT.PUT_LINE('Aprovat');
      ELSE
          DBMS_OUTPUT.PUT_LINE('Suspès');
      END IF;
    END;
    /

🧪 Exemple 2: condicions múltiples

DECLARE
      edat NUMBER := 16;
      autoritzat BOOLEAN;
    BEGIN
      autoritzat := (edat >= 18);
      IF autoritzat THEN
          DBMS_OUTPUT.PUT_LINE('Pot entrar');
      ELSE
          DBMS_OUTPUT.PUT_LINE('Accés denegat');
      END IF;
    END;
    /

🧪 Exemple 3: combinació amb AND i OR

DECLARE
      edat NUMBER := 20;
      carnet_conduir BOOLEAN := TRUE;
    BEGIN
      IF edat >= 18 AND carnet_conduir THEN
          DBMS_OUTPUT.PUT_LINE('Pot conduir legalment');
      ELSE
          DBMS_OUTPUT.PUT_LINE('No pot conduir');
      END IF;
    END;
    /

🧪 Exemple 4: comparació de cadenes

DECLARE
      rol VARCHAR2(20) := 'admin';
    BEGIN
      IF rol = 'admin' THEN
          DBMS_OUTPUT.PUT_LINE('Accés complet');
      ELSE
          DBMS_OUTPUT.PUT_LINE('Accés limitat');
      END IF;
    END;
    /

5. Operacions combinades

Pots combinar números i textos, per exemple per calcular salaris:

DECLARE
       nom VARCHAR2(20) := 'Laura';
       hores NUMBER := 5;
       sou_hora NUMBER := 12.5;
       total_sou NUMBER;
    BEGIN
       total_sou := hores * sou_hora;
       DBMS_OUTPUT.PUT_LINE(nom || ' cobrarà ' || total_sou || ' euros.');
    END;
    /

✨ Pots provar aquests blocs en Oracle SQL Developer amb F5 per veure el resultat a DBMS Output.

🔁 Estructures de control en PL/SQL

Les estructures de control són fonamentals per controlar el flux d'execució d’un bloc de codi PL/SQL. Oracle suporta múltiples tipus:

Condicionals

Estructura IF

  IF condicio THEN
     instruccions;
  ELSIF altra_condicio THEN
     instruccions;
  ELSE
     instruccions;
  END IF;

Exemple IF (🧪 Prova-ho!)

  DECLARE
     edat NUMBER := 20;
  BEGIN
     IF edat >= 18 THEN
        DBMS_OUTPUT.PUT_LINE('Ets major d''edat');
     ELSE
        DBMS_OUTPUT.PUT_LINE('Ets menor');
     END IF;
  END;

Estructura CASE

  CASE expressio
     WHEN valor1 THEN instruccions;
     WHEN valor2 THEN instruccions;
     ELSE instruccions;
  END CASE;

Exemple CASE (🧪 Prova-ho!)

  DECLARE
     nota NUMBER := 7;
  BEGIN
     CASE
        WHEN nota >= 9 THEN DBMS_OUTPUT.PUT_LINE('Excel·lent');
        WHEN nota >= 5 THEN DBMS_OUTPUT.PUT_LINE('Aprovat');
        ELSE DBMS_OUTPUT.PUT_LINE('Suspès');
     END CASE;
  END;

🔁 Bucles

🔄 LOOP bàsic (amb sortida manual)

  LOOP
     instruccions;
     EXIT WHEN condicio;
  END LOOP;

Exemple LOOP (🧪 Prova-ho!)

  DECLARE
     i NUMBER := 1;
  BEGIN
     LOOP
        DBMS_OUTPUT.PUT_LINE('Iteració: ' || i);
        i := i + 1;
        EXIT WHEN i > 5;
     END LOOP;
  END;

WHILE LOOP

  WHILE condicio LOOP
     instruccions;
  END LOOP;

Exemple WHILE (🧪 Prova-ho!)

  DECLARE
     i NUMBER := 1;
  BEGIN
     WHILE i <= 3 LOOP
        DBMS_OUTPUT.PUT_LINE('Valor: ' || i);
        i := i + 1;
     END LOOP;
  END;

FOR LOOP

  FOR variable IN inici..final LOOP
     instruccions;
  END LOOP;

Exemple FOR

  BEGIN
     FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE('Posició: ' || i);
     END LOOP;
  END;

Notes addicionals

Bon ús d’estructures

⚙️ Procediments, Funcions i Triggers

Definicions ràpides

❗ Un usuari necessita tindre permís per executar rutines, o per crear-les i executar-es !!

GRANT CREATE PROCEDURE, CREATE TRIGGER TO ;
      (si te permís de crear, també pot executar)
      GRANT EXECUTE ON . TO  [WITH GRANT OPTION];

El permís CREATE PROCEDURE val per procediments, I també per funcions

El permís CREATE TRIGGER val per disparadors

PROCEDURE – Procediment

Els procediments NO retornen valor i NO poden ser cridades dins SELECT o expressions. Poden ser cridats des de dins d'un bloc anònim, directament des de linea d'ordres sqlplus o SQL Developer, o des de dins d'un altre procediment o funció

Exemples

    DBMS_OUTPUT.PUT_LINE('Suspès')
    afegir_client('Maria', 'Gonzalez', '123456789');  
    actualitzar_sou(101, 3000);

FUNCTION – Funció

Diferència clau:

Les funcions retornen un valor i poden ser cridades dins SELECT o expressions.

Exemples

    data_actual := SYSDATE;
    valor_aleatori := DBMS_RANDOM.VALUE(1, 100);
    data_convertida := TO_DATE('2025-04-20', 'YYYY-MM-DD');

    select upper(nom) , sysdate, sou from empleats;
    SELECT CONCAT(nom, ' ', cognom) FROM clients;
    select sysdate from dual;

TRIGGER – Disparador

Diferència clau:

Els trigger no retornen un valor, i no se'ls pot cridar Son blocs de codi amb nom que son cridats automaticament pel sistema quan passa algun esdeveniment.

Exemples

    -- Al Inserir un nou empleat es disparara el trigger associat 
    -- al INSERT en la taula EMPLEATS
    INSERT INTO empleats (id_empleat, nom, cognom, sou)   
    VALUES (101, 'Joan', 'García', 2500);
    -- En este moment, el sistema crida al trigger

⚙️ Procediments en PL/SQL

Què és un procediment?

Un procediment és un bloc de codi PL/SQL emmagatzemat amb un nom que es pot cridar per executar una acció. Pot rebre paràmetres i fer servir sentències SQL, estructures de control, bucles, excepcions, etc.

Estructura bàsica

CREATE [OR REPLACE] PROCEDURE nom_proc (
    param1 [IN | OUT | IN OUT] tipus,
    ...
) IS
BEGIN
   -- Codi del procediment
END nom_proc;

Tipus de paràmetres:

Sols vorem els IN, que a més a més, no cal posar la paraula IN perquè és el tipus per defecte

🧪 Exemple senzill

CREATE OR REPLACE PROCEDURE saluda (nom VARCHAR2) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hola, ' || nom || '!');
END;

Execució:

BEGIN
   saluda('Joan');
END;

O també amb execute

execute mostrar_salutacio('Ada');

🔍 Consultar procediments

Desar en fitxer i executar

Pots escriure un procediment en un fitxer .sql i executar-lo amb:

@proc_saluda.sql

Bones pràctiques

Exemple complet amb control d’errors

CREATE OR REPLACE PROCEDURE insereix_alumne (
   nom IN VARCHAR2,
   edat IN NUMBER
) IS
BEGIN
   INSERT INTO alumnes(nom, edat) VALUES (nom, edat);
   DBMS_OUTPUT.PUT_LINE('Alumne inserit correctament.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Esborrar un procediment

  DROP PROCEDURE nom_proc;

Formes de cridar / invocar un procediment

    afegir_client('Joan', 'García', '123456789');
    afegir_client( p_nom => 'Joan', p_cognom => 'García', p_telefon => '123456789');
    o
    afegir_client(p_telefon => '123456789', p_nom => 'Joan', p_cognom => 'García');

Totes són correctes

Funcions en PL/SQL

📘 Què és una funció?

Una funció és un bloc de codi PL/SQL que rep zero o més paràmetres i retorna un valor. A diferència dels procediments, les funcions es poden cridar dins sentències SQL i expressions.

Al igual que en els procediments existeixen funcions predefinides que podem utilitzar en oracle

Tipus de Funcions Funcions
Funcions Numèriques ROUND, TRUNC, MOD, SQRT, POWER, SIGN, ABS
Funcions de Cadenes (Strings) LOWER, UPPER, TRIM, SUBSTR, LENGTH, REPLACE, INSTR, TRANSLATE, CHR, ASCII
Funcions per treballar amb NULL NVL, NVL2, NULLIF, COALESCE
Funcions de Dates SYSDATE, LAST_DAY, EXTRACT, ADD_MONTHS
Funcions de Conversió TO_NUMBER, TO_DATE, TO_CHAR

I, al igual que en els procediments , podem definir noves funcions (funcions definides per l'usuari)

Hem de tindre en compte, que una funció necessita saber quin tipus de dada va a retornar, i, a més a més, en algun punt del cos de la funció, ha de retornar una dada d'aquest tipus

Estructura bàsica

CREATE [OR REPLACE] FUNCTION nom_func (  paràmetres  ) 
RETURN tipus 
IS
BEGIN
    -- codi
    RETURN valor;
END nom_func;

Exemple simple

CREATE OR REPLACE FUNCTION suma ( a NUMBER, b  NUMBER ) 
RETURN NUMBER 
IS
BEGIN
   RETURN a + b;
END;

Exemple Crida dins d’un bloc:

DECLARE
   resultat NUMBER;
BEGIN
   resultat := suma(4, 5);
   DBMS_OUTPUT.PUT_LINE('Resultat: ' || resultat);
END;

Exemple Crida dins d'un SELECT

SELECT suma(10, 20) FROM dual;

Exemple real: calcular salari amb plus

CREATE OR REPLACE FUNCTION salari_total ( base NUMBER, plus NUMBER DEFAULT 0 ) 
RETURN NUMBER 
IS
BEGIN
   RETURN base + plus;
END;

Crida:

SELECT salari_total(1500, 200) FROM dual;

Tipus de paràmetres

⚠️ Limitacions

📥 Consulta de funcions

-- Funcions de l’usuari
SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';

-- Arguments d’una funció
SELECT argument_name, data_type, in_out
FROM user_arguments
WHERE object_name = 'SUMA';

🧪 Exemple amb condició

CREATE OR REPLACE FUNCTION es_parell (n IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
   IF MOD(n, 2) = 0 THEN
      RETURN 'Sí';
   ELSE
      RETURN 'No';
   END IF;
END;

Crida:

SELECT es_parell(8) FROM dual;

Com cridar funcions:

Bones pràctiques

🧪 Pràctica combinada

Calcular l’IRPF aplicat a un salari i retornar-lo:

CREATE OR REPLACE FUNCTION calcula_irpf (
   salari IN NUMBER
) RETURN NUMBER IS
BEGIN
   RETURN salari * 0.15;
END;

Aquesta funció es pot cridar dins un SELECT per veure quants impostos es retenen: 

SELECT nom, calcula_irpf(salari) AS irpf
FROM empleats;

🔍 Consultes útils

-- Veure procediments i funcions de l'usuari
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION');

🧹 Esborrar una funció

DROP FUNCTION nom_de_la_funcio;

⚠️ Consideracions:

🚨 Disparadors (Triggers) en Oracle

📘 Què és un trigger?

Un trigger és un bloc de codi PL/SQL que s’executa automàticament quan es produeix un esdeveniment (com un INSERT, UPDATE o DELETE) sobre una taula o vista.

El bloc de codi del trigger s'executara:

Segons el cas, es poden utilitzar valors d’una fila abans de la operació o després de l’operació: :new i :old

Nivells d’actuació

Sintaxi bàsica (fila a fila)

CREATE OR REPLACE TRIGGER nom_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON nom_taula
FOR EACH ROW
BEGIN
   -- accions amb :NEW i :OLD
END;

🧪 Exemple: trigger de control INSERT

CREATE OR REPLACE TRIGGER log_insercio
AFTER INSERT ON alumnes
FOR EACH ROW
BEGIN
   DBMS_OUTPUT.PUT_LINE('Nou registre afegit: ' || :NEW.nom);
END;

:NEW i :OLD representen els valors nous i antics respectivament per a cada fila.

Exemple: impedir valors negatius

CREATE OR REPLACE TRIGGER evitar_negatius
BEFORE INSERT OR UPDATE ON productes
FOR EACH ROW
BEGIN
   IF :NEW.preu < 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'El preu no pot ser negatiu');
   END IF;
END;

Exemple: trigger d’auditoria

Crear una taula d’auditoria:

CREATE TABLE aud_alumnes (
   usuari   VARCHAR2(30),
   data_op  DATE,
   accio    VARCHAR2(10)
);

Trigger que afegeix registre:

CREATE OR REPLACE TRIGGER audita_alumnes
AFTER INSERT OR DELETE ON alumnes
FOR EACH ROW
BEGIN
   INSERT INTO aud_alumnes VALUES (
      USER,
      SYSDATE,
      CASE
         WHEN INSERTING THEN 'INSERT'
         WHEN DELETING THEN 'DELETE'
      END
   );
END;

🔎 Consultes útils

-- Veure triggers de l’usuari actual
SELECT trigger_name, table_name, triggering_event, status
FROM user_triggers;

-- Consultar codi
SELECT trigger_body FROM user_triggers WHERE trigger_name = 'LOG_INSERCIO';

Bones pràctiques

🛠️ Activació i desactivació de triggers

-- Desactivar
ALTER TRIGGER log_insercio DISABLE;

-- Activar
ALTER TRIGGER log_insercio ENABLE;

-- Eliminar
DROP TRIGGER log_insercio;

Seqüències en Oracle

📘 Què és una seqüència?

Una seqüència és un objecte de base de dades que genera una sèrie de valors numèrics consecutius, normalment usats per a claus primàries, codis únics o control d’identificadors. És l’equivalent a un autoincrement en altres SGBD.

Crear una seqüència

   -- Donar permís
   GRANT CREATE SEQUENCE TO usuari;
   -- Crear seqüència 
  CREATE SEQUENCE seq_alumnes START WITH 1 INCREMENT BY 1 
  -- Esborrar seqüència
  DROP SEQUENCE seq_alumnes;

Ús de la seqüència

Per obtenir el següent valor (es pot fer servir a INSERTs):

seq_alumnes.NEXTVAL

Per consultar el valor actual (després d'haver fet NEXTVAL com a mínim una vegada):

seq_alumnes.CURRVAL

Exemple d’ús en una inserció

INSERT INTO alumnes (id, nom, edat)  VALUES (seq_alumnes.NEXTVAL, 'Laia', 21);

🔍 Consultar seqüències existents

-- Seqüències de l’usuari
SELECT sequence_name, min_value, max_value, increment_by
FROM user_sequences;

⚙️ Modificar una seqüència

ALTER SEQUENCE seq_alumnes  INCREMENT BY 5 ;

🗑️ Eliminar una seqüència

DROP SEQUENCE seq_alumnes;

Bones pràctiques

Exemple complet

CREATE SEQUENCE seq_factura START WITH 1000 INCREMENT BY 10 ;
-- Inserció amb NEXTVAL
INSERT INTO factures (id, data_emissio) VALUES (seq_factura.NEXTVAL, SYSDATE);

Identity Column

Una identity column és un altre mecanisme d’oracle per generar valors sencers seqüencials únics i assignar-li'ls a camps numèrics; s'utilitzen generalment per a les claus primàries de les taules garantint que els seus valors no es repetisquen.

CREATE TABLE empleados (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY
  PRIMARY KEY,
  .......);
Característica SEQUENCE IDENTITY Column
Definició Objecte independent que genera valors numèrics seqüencials. Columna d'una taula que genera automàticament valors únics.
Ús principal Generar claus primàries o valors seqüencials a voluntat. Generar automàticament un valor únic per fila nova.
Com s'utilitza Cal cridar `nom_seq.NEXTVAL` explícitament en la inserció. Oracle gestiona el valor automàticament (amb `GENERATED ALWAYS` o `BY DEFAULT`).
Control de valor L'usuari decideix quan i com consumir el següent valor. El valor s'incrementa automàticament amb cada inserció (segons la configuració).
Flexibilitat Molt flexible: pot ser utilitzada per diferents taules o processos. Està lligada a una sola taula i columna.
Exemple de creació CREATE SEQUENCE seq_client_id START WITH 1 INCREMENT BY 1; id NUMBER GENERATED BY DEFAULT AS IDENTITY
Compartició entre taules Es pot usar en múltiples taules. Només es pot usar en una taula específica.

Cursors en PL/SQL

📘 Què és un cursor?

Un cursor és un mecanisme de PL/SQL que permet recórrer múltiples files retornades per una consulta SELECT.

Serveix per treballar fila a fila amb el conjunt de resultats, com un bucle sobre una taula.

Tipus de cursors

Cursors implícits

No es declaren, sols tornen un resultat o fila:

 SELECT DESCRIPCION INTO vdescripcion from PAISES WHERE CO_PAIS = 'ESP';

Els cursors implícits només poden retornar una única fila. En cas que es retorne més d'una fila (o cap fila) es produirà una excepció: NO_DATA_FOUND o TOO_MANY_ROWS

Si no sabem si va a tornar cap fila o més d'una, es pot comprobar abans amb

SELECT count(*) INTO vcant from factura WHERE idcli = 'V00923';

O utilitzar una EXCEPTION i preguntar per %FOUND

Atributs útils

Ús d’un cursor explícit

Passos:

  1. Declarar el cursor
  2. Obrir-lo
  3. Llegir fila a fila
  4. Tancar-lo

Exemple complet

DECLARE
   CURSOR c_alumnes IS
     SELECT nom, edat FROM alumnes;
   
   v_nom alumnes.nom%TYPE;
   v_edat alumnes.edat%TYPE;
BEGIN
   OPEN c_alumnes;
   LOOP
      FETCH c_alumnes INTO v_nom, v_edat;
      EXIT WHEN c_alumnes%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Nom: ' || v_nom || ' - Edat: ' || v_edat);
   END LOOP;
   CLOSE c_alumnes;
END;

Cursor FOR – simplificat

Oracle gestiona automàticament l’obertura, lectura i tancament.

BEGIN
   FOR reg IN (SELECT nom, edat FROM alumnes) LOOP
      DBMS_OUTPUT.PUT_LINE('Nom: ' || reg.nom || ', Edat: ' || reg.edat);
   END LOOP;
END;

Cursors amb paràmetres

DECLARE
   CURSOR c_per_edat(min_edat NUMBER) IS
     SELECT nom FROM alumnes WHERE edat > min_edat;

   v_nom alumnes.nom%TYPE;
BEGIN
   OPEN c_per_edat(18);
   LOOP
      FETCH c_per_edat INTO v_nom;
      EXIT WHEN c_per_edat%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Major de 18: ' || v_nom);
   END LOOP;
   CLOSE c_per_edat;
END;

Bones pràctiques

📘 Consulta de cursors actius

(per sessions de treball, administradors)

SELECT * FROM v$open_cursor
WHERE user_name = 'NOM_USUARI';

Gestió d’Excepcions en PL/SQL

📘 Què és una excepció?

Una excepció és una situació d’error que es produeix durant l’execució d’un bloc PL/SQL. Oracle permet capturar aquestes situacions per gestionar-les de manera controlada i evitar que el bloc falli de manera abrupta.

Estructura general amb excepcions

BEGIN
   -- codi normal
EXCEPTION
   WHEN tipus_excepcio THEN
      -- codi d'error
END;

Tipus d’excepcions

Excepcions predefinides

Oracle les reconeix automàticament. Exemple:

Exemple

DECLARE
   v_nom alumnes.nom%TYPE;
BEGIN
   SELECT nom INTO v_nom FROM alumnes WHERE id = 999;
   DBMS_OUTPUT.PUT_LINE(v_nom);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Cap alumne amb aquest ID');
END;

Excepcions no predefinides

Oracle les pot capturar amb SQLCODE i SQLERRM:

BEGIN
   -- operació que pot fallar
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
END;

🔧 Excepcions personalitzades

Es poden declarar amb EXCEPTION i cridar amb RAISE:

DECLARE
   ex_preu_invalid EXCEPTION;
   v_preu NUMBER := -5;
BEGIN
   IF v_preu < 0 THEN
      RAISE ex_preu_invalid;
   END IF;
EXCEPTION
   WHEN ex_preu_invalid THEN
      DBMS_OUTPUT.PUT_LINE('Error: preu negatiu no vàlid');
END;

Claus d’ús

🧠 Exemple final combinat

DECLARE
   v_total NUMBER := 0;
BEGIN
   v_total := 100 / 0;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('No es pot dividir per zero!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error inesperat: ' || SQLERRM);
END;

Bones pràctiques

Tasques automatitzables en Oracle

Què podem automatitzar?

En un entorn Oracle, és habitual automatitzar tasques per millorar la gestió i el rendiment del sistema. Algunes de les tasques més comunes són:

📄 Scripts SQL i .bat

Pots crear scripts `.sql` amb comandes Oracle i executar-los des de scripts `.bat` o `.sh` mitjançant sqlplus.

Exemple: script SQL

-- arxiu: informe.sql
SET ECHO OFF
SET FEEDBACK OFF
SPOOL informe_resultats.txt

SELECT * FROM alumnes;

SPOOL OFF
EXIT;

🖥️ Script .bat (Windows)

-- arxiu: llança_informe.bat
sqlplus usuari/contrasenya@XE @informe.sql
pause

🐧 Script .sh (Linux)

#!/bin/bash
sqlplus usuari/contrasenya@XE @informe.sql

📆 Oracle SCHEDULER - JOBS

Oracle incorpora un sistema per programar tasques directament des de la base de dades: DBMS_SCHEDULER.

Donar permís

  --  Un usuari necessita tindre permís per crear jobs (treballs) i executar-los !!
  grant create job to usuari;
  grant execute on dbms_scheduler to usuario;

Crear una tasca programada

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_INFORME',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN informe_alumnes(); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=8',
    enabled         => TRUE
  );
END;

Modificar una tasca programada

BEGIN
  dbms_scheduler.set_attribute_null( name=>’nom’, attribute=>’a’);
  dbms_scheduler.set_attribute(name=>’nom’,attribute=>’a’,value=>’v’);
END;

Paràmetre repeat_interval 

  FREQ=MONTHLY → S'executa un cop al mes. (Defineix la repetició mensual)
  BYMONTHDAY=1 El dia 1 de cada mes
  BYDAY=TU → Els dimarts.
  BYSETPOS=1 → Només el primer dimarts del mes.
  BYHOUR=1; BYMINUTE=5 → A la 01:05 AM.

FREQ={SECONDLY | MINUTELY | HOURLY | DAILY | WEEKLY | MONTHLY | YEARLY}; A partir d’aquí, pots afegir modificadors com BYSECOND, BYMINUTE, BYHOUR, BYDAY, BYMONTH, etc

Exemples d'ús del paràmetre

  Cada dia a les 8:30 AM
  REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=8; BYMINUTE=30;'
  Cada dilluns i dimecres a les 10:00 AM i 4:00 PM
  REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=MON,WED; BYHOUR=10,16; BYMINUTE=0;'
  El primer dimarts de cada mes a les 09:00 AM
  REPEAT_INTERVAL => 'FREQ=MONTHLY; BYDAY=TU; BYSETPOS=1; BYHOUR=9; BYMINUTE=0;'
  L'últim divendres de cada mes a les 18:00
  REPEAT_INTERVAL => 'FREQ=MONTHLY; BYDAY=FR; BYSETPOS=-1; BYHOUR=18; BYMINUTE=0;'
  Cada 6 hores
  REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=6;'

🔍 Consultar jobs

SELECT job_name, state FROM user_scheduler_jobs;

Aturar o eliminar un job

BEGIN
  DBMS_SCHEDULER.DISABLE('JOB_INFORME');
  DBMS_SCHEDULER.DROP_JOB('JOB_INFORME');
END;

Triggers de sistema (a nivell de sessió)

També pots utilitzar triggers de sistema per executar codi quan es produeixen accions específiques:

CREATE OR REPLACE TRIGGER t_inici_sessio
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO log_sessio(usuario, data)   VALUES (USER, SYSDATE); 
END;

Exemple complet d’automatització

Bones pràctiques

SQL dinàmic

En algunes tasques de manteniment, s’utilitzarà el diccionari de dades (amb un cursor)- i per a cada objecte de DD s’aplicarà una sentència DDL

Si esta sentència correspon al DDL, no es pot executar directament dins d'un bloc de codi

En este cas, es farà ús de la sentència execute immediate

execute immediate és una instrucció de PL/SQL que permet executar sentències SQL dinàmiques és a dir, sentències construïdes en temps d'execució com cadenes de text.

Exemple

  create procedure defrag_taules (vuser varchar2(20))
  -- Este proc desfragmenta totes les taules d’un usuari/schema
  as
  cursor c is select table_name,tablespace_name
  from dba_tables where user=vuser ;
  begin
  for x in c loop
    execute immediate ('alter table ' || x.table_name || ' move');
    dbms_output.put_line('Taula desfragmentada:' || x.table_name);
    end loop;
  end;

Altres recursos

Ús de Dates en Oracle

link

AuithID en procedure

FOR en procedure