ASGBD - Automatització de tasques en Oracle

Automatització

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

Quan parlem d’automatització en Oracle, generalment ens referim a processos que es programen per executar-se automàticament, sense necessitat d’intervenció manual. Oracle ofereix diverses eines i mètodes per fer això

Avantatges Automatització

• Estalvi de temps
• Reducció costos administració
• Reducció errors ( humà)

Tipus

• Programa extern (al SGBD)
• Programador de Tasques de Windows, Crontab en Linux, etc..
• Programa intern : rutina de bbdd + job

Rutina

• Script, guió, programa o seqüència de comandos que permeten dur a terme el processament d'unes certes accions
• Quan és creada rep un nom que permet que siga invocada tantes vegades com siga necessari
• Van ser introduïdes en la versió SQL3, o SQL:1999

Els guions o scripts donaren pas al PL/SQL ( que és diferent del SQL )

SQL (Structured Query Language) és un llenguatge declaratiu. S’utilitza per gestionar i consultar bases de dades. No té estructures de control com bucles o condicions complexes. SELECT (consultar dades), INSERT (inserir dades), UPDATE (actualitzar dades), DELETE (esborrar dades), i altres com DDL, DCL, TCL

PL/SQL (Procedural Language / SQL). És una extensió de SQL creada per Oracle. És un llenguatge procedimental. Permet utilitzar: Variables, Condicions (IF), Bucles (LOOP, WHILE, FOR), Procediments i funcions. S’utilitza per crear programs dins de la base de dades.

El SGBD deu proporcionar:

• Eines necessàries per a crear rutines
• Eines per a executar les rutines automàticament

Avantatges rutina interna

• Rendiment
• Reutilització de codi
• Encapsula regles de negoci
• Major seguretat

Les rutines: S’han de Documentar

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 --

-- Esto es un comentario 

** Fent servir documentació institucional en qualsevol suport (documents, fulls 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:

• No té nom ni es guarda a la base de dades.
• S’executa directament des de la pestanya. Es pot guardar en un fitxer extern i després recuperar.
• És ideal per a proves o per a executar operacions puntuals.
• Pot incloure sentències SQL, declaracions de variables, control de flux, i gestió d’excepcions.

En sqlplus es pot:

• Es pot editar el contingut del buffer amb edit
• Es pot executar el contingut del buffer amb run o /
• Guardar el contingut del buffer en un arxiu ( posa nom + .sql )
• SQL> SAVE nom_fitxer.sql
• SQL> SAVE nom_fitxer.sql REPLACE
• Carrega el contingut d'un fitxer en el buffer (però NO l'executa)

∘ SQL> GET nom_fitxer.sql

• Executa el contingut d'un fitxer
• SQL> @prova.sql
• SQL> @/ruta/completa/prova.sql
• SQL> START prova.sql

En SQL Developer

Es treballa amb WorkSheets (pestanya oberta). Cada pestanya és una connexió

Per executar una linia del WorkSheet, prem Ctrl+Enter

Per executar TOTES les linies, prem F5

Per guardar el contingunt del WorkSeet, Ves a File → Save o Save As…

Per recuperar el text guardat en una fitxer, File → Open → prova_bloc.sql

Per executar el contingut d'un fitxer directament => @/ruta/prova_bloc.sql i prem F5

Estructura bàsica d’un bloc anònim

El primer és activar l'eixida per pantalla. Esta sentència es pot executar una vegada al principi

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

• DECLARE: bloc opcional per declarar variables.
• &nom: Variable de substitució.(Es mostrarà al demanar el valor)
• BEGIN: bloc obligatori per executar codi.
• DBMS_OUTPUT.PUT_LINE: mostra text a pantalla (cal tenir activat SET SERVEROUTPUT ON).
• END; finalitza el bloc.
• . (només en SQL*Plus) envia el bloc al buffer per executar-lo.

&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.

S'usarà '&NOM' amb cometes per demanar un STRING i &NOM sense cometes per demanar un NUMBER

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;   -- DATE és el tipus i SYSDATE una funció 
  BEGIN
     DBMS_OUTPUT.PUT_LINE('La data actual és: ' ||  vData  );
     DBMS_OUTPUT.PUT_LINE('La data actual és: ' || TO_CHAR(vData, 'DD/MM/YYYY hh:mi am'));
  END;
    

PL/SQL no és case-sensitive en general. Paraules clau, noms de variables, procediments i funcions no distingueixen entre majúscules i minúscules. ❗❗ Però, els valors dels strings o de camps VARCHAR, SÍ que distingeixen.

SYSDATE és una funció d'ORACLE que torna la data/hora de l'instant actual

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

Bones pràctiques

• Documenta el codi amb comentaris (-- per una línia, /* */ per múltiples).
• Fes servir blocs anònims per a provar procediments o funcions abans de guardar-los.
• Prova condicions, bucles i excepcions dins blocs anònims abans d'incorporar-ho a rutines emmagatzemades.

Ordres útils en SQL*Plus

Els comandos bàsics d'sqlplus no necessiten posar un ";" al final. No són comandos SQL, són propis de l'sqlplus
• EDIT → edita l’script actual
• LIST → mostra el contingut del buffer
• RUN → executa el bloc en el buffer (també r o /)
• SAVE fitxer.sql → desa l’script en un fitxer
• GET fitxer.sql → carrega un script des d'un fitxer
• start fitxer.sql → carrega i executa un script des d'un fitxer
• @fitxer.sql → equival a start

Ordres útils en SQL Developer

• F5 → executa l'script de la sessió activa. Mostra el resultat en l’àrea de Script Output.
• F9 → executa sentència (una,, on està el cursor). Mostra el resultat en l’àrea de Resultats.
• Ctrl+Intro → Equivalent a F9
• . → En SQL Developer no cal posar un punt al final
• Recorda posar SET SERVEROUTPUT ON; abans del bloc anònim

Diferència amb procediments

Els blocs anònims no es poden reutilitzar directament, ja que no tenen nom. (Si es poden guardar en un fitxer extern, i despres recuperar (obrir), i tornar a executar). Per a reutilitzar codi, millor usar procediments o funcions, ja que queden emmagatzemats dins del SGBD.

🧪 Prova-ho!

Prova aquest bloc en SQL Developer:

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

Executa el bloc algunes vegades, i després guarda-lo en un fitxer extern

🧪 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.

🗃️ 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 important 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.

En PL/SQL (Oracle), les variables s’han de declarar sempre abans d’utilitzar-les. Les variables es declaren a la secció DECLARE (o a la part de declaració d’un procediment, funció o paquet).

Estructura bàsica

DECLARE
    nom_variable tipus ;                    -- Es declara però no s'inicialitza. Pren el valor NULL   
    nom_variable tipus [:= valor_inicial];  -- Es declara i s'inicialitza amb un valor  
    nom_variable tipus [DEFAULT valor_inicial];  -- DEFAULT és equivalent a  :=
BEGIN
    -- ús de la variable
END; 

Tipus bàsics

🧪 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

-- En el bloc DECLARE

• Entrada de l’usuari (Eina d'SQL Developer):

  v_nom varchar2(20) := '&nom_usuari';  -- Variable de substitució
  /*  una variable de substitució és un valor que s’introdueix en temps d’execució
     i que Oracle substitueix abans d’executar la sentència */

• Utilitzant el DEFAULT

   v_code number default 100;

-- En el bloc principal BEGIN

• Assignació directa: v_edat := 25;
• Amb SELECT INTO:

  SELECT sou INTO v_sou FROM empleats WHERE id = 5;
              o 
  SELECT avg(sou) INTO v_mitjanasou FROM empleats WHERE departament=101;            
            

❗ Detalls importants

• Les variables només existeixen dins del bloc on es declaren.
• No pots fer SELECT ... sense INTO dins de PL/SQL.
• BOOLEAN només es pot usar dins de PL/SQL, no en SQL estàndard. (❗ sí en oracle23 ❗)

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, divisions i potències 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);

    resultat := a ** b;
    DBMS_OUTPUT.PUT_LINE('Potència: ' || resultat);
END; 

Precedència: No s'ha d'oblidar que no és el mateix 2 + 5 * 3 que (2 + 5) * 3. La precedència o ordre de les operacions és crucial en el resultat

En PL/SQL este ordre de precedència és el següent:

Encara que conegues la precedència, usa parèntesis per fer el codi: *més clar, *menys propens a errors, *més llegible per altres desenvolupadors

Altres operacions aritmètiques amb funcions: ABS, ROUND, TRUNC, MOD, CEIL, FLOOR, SQRT, TO_CHAR

🔤 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; 

Altres operacions amb funcions: LENGTH, UPPER, LOWER, INITCAT, SUBSTR, INSTR, REPLACE, TRIM, LTRIM, RTRIM, TO_NUMBER

📅 3. Operacions amb dates

Les dates es poden restar o sumar, on 1 unitat és 1 dia:
(i on per exemple, un 1.5 serà un dia i mig, es a dir 1 dia i 12 hores)

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; 

Al igual que TO_DATE, està el TO_CHAR, amb la posibilitat d'usar màscara ('DD/MM/YY HH:MI PM')

✔️ 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)

S'ha d'anar amb cura en les operacions amb dates, perquè tots els mesos no tenen el mateix nombre de dies, ni tots els anys tampoc tenen el mateix nombre de dies !!

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; 

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

• = igualtat
• <> diferent
• >, <, >=, <=
• AND, OR, NOT

🧪 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; 

En PL/SQL: 'admin' no és igual que 'ADMIN'
Es poden comparar amb < o > també de manera que les cadenes es comparen caràcter per caràcter, segons l’ordre lexicogràfic (semblant a l’ordre del diccionari).

'Ana' < 'Berta'     -- TRUE
'abc' < 'abd'       -- TRUE
'Z' < 'a'           -- TRUE (perquè el codi ASCII de Z és menor)

Les cadenes de text també es poden comparar amb LIKE, usant '%'=>(qualsevol caracter) o '-'=>(un caracter)

IF nom LIKE 'Mar%' THEN

5. Operacions combinades

Es pot 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 els següents :

Condicionals

Estructura IF

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

Exemple IF (🧪 Prova-ho!)

SET SERVEROUTPUT ON;

DECLARE
    edat NUMBER := &dismeedat;
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 := &dismeNota;
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 := &dismeVoltes;
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 := &dismeVoltes;
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

• EXIT WHEN → surt d’un bucle en una condició
• CONTINUE → salta a la següent iteració (a partir de Oracle 11g)
• Els bucles poden ser niats i combinen amb IF o CASE

Bon ús d’estructures

• Usa `FOR` si saps quantes vegades vols repetir
• Usa `WHILE` si depens d’una condició externa
• Usa `IF` per bifurcar el comportament del codi
• ⚠️ Evita bucles infinits, assegura sempre una condició de sortida

⚙️ Procediments, Funcions i Triggers

Definicions ràpides

• Procediment: bloc PL/SQL que realitza una acció, pot tenir paràmetres però no retorna valor directament.
• Funció: com un procediment, però retorna un valor mitjançant RETURN.
• Trigger: bloc de codi que s'executa automàticament quan es produeix un esdeveniment sobre una taula (INSERT, UPDATE, DELETE).

Permisos necessaris

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

GRANT CREATE PROCEDURE, CREATE TRIGGER TO <usu>;
        -- (si té permís de crear, també pot executar)
GRANT EXECUTE ON <esquema>.<procedim> TO <usu>/<rol> [WITH GRANT OPTION];

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

El permís CREATE TRIGGER val per disparadors

Vista del DD

Per vore si un usuari té estos permisos

SELECT *
FROM DBA_SYS_PRIVS
WHERE privilege IN ('CREATE PROCEDURE', 'CREATE TRIGGER');

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, , des de dins d'un altre procediment o funció, o des del DBMS_SCHEDULER amb un job

Exemples

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

FUNCTION – Funció

Diferència clau:

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

Exemples

BEGIN
  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;
END;      

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.

Com..
• Esdeveniment DML: INSERT, UPDATE, DELETE
• Esdeveniment DDL: CREATE, ALTER, DROP, ...
• Esdeveniments de base de dades: LOGON, LOGOFF, STARTUP, SHUTDOWN, etc.

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:

• IN: entrada (per defecte)
• OUT: sortida
• IN OUT: entrada i sortida

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

• Llistar procediments de l’usuari:

SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';
    

• 👀 Consultar paràmetres d’un procediment:

SELECT argument_name, in_out, data_type
FROM user_arguments
WHERE object_name = 'CALCULA_DOBLE';
    

🧹 Esborrar un procediment

DROP PROCEDURE nom_proc;    

Bones pràctiques

• Dona noms clars i significatius
• Documenta el comportament i els paràmetres
• Gestiona excepcions amb EXCEPTION per capturar errors
• ⚠️ Evita fer massa coses dins d’un sol procediment → separa funcionalitats

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;    

Formes de cridar / invocar un procediment

• Notació posicional
• Notació nominal

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

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

• IN: entrada (per defecte)
• OUT i IN OUT no es poden fer servir a funcions que es cridin dins SQL

⚠️ Limitacions

• Les funcions que es criden des de SQL no poden modificar dades (no poden fer INSERT, UPDATE o DELETE)
• Han de ser deterministes: el resultat ha de dependre només dels valors d’entrada

📥 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:

• ✔️ Dins SELECT, WHERE, ORDER BY
• ✔️ Assignant a una variable dins un bloc
• ❌ No es poden fer servir dins DML (si tenen codi que modifica dades)

Bones pràctiques

• Utilitza noms clars i descriptius
• Fes servir RETURN només una vegada si és possible
• Documenta què fa la funció i què retorna
• ⚠️ Evita fer INSERT/UPDATE dins funcions si s’han de cridar des de SQL

🧪 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 al DD

-- 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:

• Assegura’t que no hi ha cap procediment, vista o altre objecte que depenga d’eixa funció abans d’eliminar-la.
• Si la funció no existeix, Oracle donarà un error

🚨 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:

• BEFORE: abans de l’esdeveniment
• AFTER: després de l’esdeveniment
• INSTEAD OF: per a vistes, substitueix l’acció

Segons el cas, es poden utilitzar valors d’una fila abans de la operació o després de l’operació: Per fer això s'usarà :NEW o :OLD

Nivells d’actuació

• Fila a fila → actua per cada fila afectada (s'indica posant FOR EACH ROW)
• Per esdeveniment → actua una sola vegada per operació (s'indica no posant res)

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: trigger per a 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;
  

• RAISE_APPLICATION_ERROR és una funció de PL/SQL que serveix per llançar un error personalitzat dins d'una aplicació o procediment. Quan es vol aturar l'execució i mostrar un missatge d'error clar i específic al teu codi, s'utilitzarà RAISE_APPLICATION_ERROR. Això és molt útil per validar dades o controlar condicions especials

  RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]); 

  • error_number → un número d’error entre -20000 i -20999 (Oracle reserva aquests codis per errors definits per l’usuari).
  • message → el missatge que vols mostrar quan succeeix l’error.
  • TRUE / FALSE → opcional, indica si l’error es registrarà a l’alert log.

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 al DD

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

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

Bones pràctiques

• Usa noms clars i descripció funcional
• Documenta el comportament del trigger
• Evita codi complex o accions que modifiquin altres taules si no és necessari
• Controla el rendiment: massa triggers poden degradar l’eficiència
• Si cal registrar accions, fes servir una taula d’auditoria

🛠️ Activació i desactivació de triggers

-- Desactivar
ALTER TRIGGER log_insercio DISABLE;

-- Activar
ALTER TRIGGER log_insercio ENABLE;    

🗑️ Eliminar un trigger

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

• Usa NOCYCLE per evitar duplicats si no vols que torni a començar
• Usa CACHE per millor rendiment en entorns grans (ex: CACHE 20)
• No facis servir CURRVAL abans d’haver cridat NEXTVAL en la sessió
• Dona noms clars i relacionats amb la taula (ex: SEQ_FACTURES)

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,
  .......);

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. Un cursor és com un punter que apunta a un conjunt de resultats d'una SQL i deixa llegir-los u a u.

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

Tipus de cursors

• Implícits: creats automàticament per Oracle per a sentències DML (SELECT .. INTO, INSERT, UPDATE...) )
• Explícits: definits manualment per l’usuari per tractar SELECTs que tornen múltiples files
• FOR cursors: simplificació de l’ús explícit

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. Esta EXCEPCIÓ podrà ser capturada i tractada

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

Quan Oracle crea un cursor implícit, el seu nom lògic és SQL, i es pot consultar l’estat amb atributs:
• sql%FOUND → si s’ha trobat almenys una fila
• sql%NOTFOUND → si no s’ha trobat cap fila
• sql%ROWCOUNT → nombre de files tractades
• sql%ISOPEN → indica si el cursor està obert

Ú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

• Sempre tanca els cursors després d’usar-los
• Utilitza FOR cursors si només necessites llegir
• Evita FETCHs sense EXIT WHEN %NOTFOUND
• Reutilitza variables amb tipus %TYPE o %ROWTYPE

📘 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:

• NO_DATA_FOUND → no hi ha resultats
• TOO_MANY_ROWS → SELECT retorna més d’una fila
• ZERO_DIVIDE → divisió per zero
• ....

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

• RAISE → provoca una excepció
• WHEN ... THEN → captura una excepció
• WHEN OTHERS THEN → captura qualsevol error no gestionat abans
• SQLERRM → mostra el missatge d’error
• SQLCODE → mostra el codi numèric de l’error

🧠 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

• Captura errors específics abans de fer servir OTHERS
• Informa l’usuari o registra l’error
• No capturis OTHERS sense mostrar cap missatge (silenciar errors és perillós)
• Fes servir excepcions personalitzades quan tinguis validacions pròpies

📅 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:

• Exportacions i còpies de seguretat
• Importacions de dades
• Execució d'informes
• Neteja de logs i arxius antics
• Tasques de manteniment periòdic

📄 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    

Una vegada creat el fitxer .bat, s'ha de crear una tasca programada. Panel de control → Tareas programadas → Agregar Tarea → ...

Consulta alguna guia de com usar el programador de tasques de Windows. guia1 , guia2

🐧 Script .sh (Linux)

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

En Linux, s'utilitza el CRON / CRONTAB per afegir tasques que s'executaran a determinades hores / dies () . Consulta alguna guia de com usar CRON guia1, guia2, o guia3, per a entendre les diferents opcions de programació horària.

📆 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;     -- crear i administrar els jobs seus
GRANT EXECUTE ON dbms_scheduler TO usuari;   -- sols administrar els seus jobs
 

GRANT CREATE ANY JOB TO usuari;    crear jobs d'altres, i administrar-los

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;    

Executar sense esperar al moment programat

BEGIN
    DBMS_SCHEDULER.RUN_JOB('JOB_INFORME');
END;

Habilitar o deshabilitar la programació

BEGIN
  DBMS_SCHEDULER.DISABLE('JOB_INFORME');
  DBMS_SCHEDULER.ENABLE('JOB_INFORME');
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');
  -- ES POT CANVIAR : job_action , repeat_interval , comments, job_type (PLSQL_BLOCK' 'STORED_PROCEDURE' 'EXECUTABLE')
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.  ( abreviatures MON, TUE, WED, THU, FRI, SAT i SUN ).
BYSETPOS=1 → Només el primer dimarts del mes.
BYHOUR=1; BYMINUTE=5 → A la 01:05 AM.    

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

FREQ és el paràmetre que indica la freqüència de repetició de la planificació

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;'
Cada tres mesos
REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=JA,AP,JL,OC'  
     (abreviatures dels mesos: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV , DEC)   

En Oracle DBMS_SCHEDULER, si no s’indica l’hora (BYHOUR, BYMINUTE, BYSECOND) en el REPEAT_INTERVAL, s’utilitza l’hora del START_DATE del job.

Regla general: Quan no s’indica un component temporal al REPEAT_INTERVAL, Oracle el pren del START_DATE

==> Documentació oficial d'ORACLE per .Calendaring Syntax.

📘 Consultar jobs en el DD

SELECT job_name, state FROM user_scheduler_jobs;  
SELECT * FROM user_scheduler_jobs;         

Aturar o Eliminar un job

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

No és obligatòri fer els tres pasos abans d'eliminar (Oracle no dona error), però si molt recomanable, per assegurar-se de que no quede un job a mig acabar, Per evitar processos “fantasma”, bloquejos inesperats o resultats incomplets.

-- Si el job no respon, es pot forçar amb
BEGIN
  DBMS_SCHEDULER.STOP_JOB(
    job_name => 'JOB_INFORME',
    force    => TRUE
  );
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;   

Altres accions que es poden emprar:
     LOGON, LOGOFF, STARTUP, SHUTDOWN, ERRORLOGON

Exemple complet d’automatització

• informe.sql: genera un informe amb SELECTs
• informe.bat: l’executa des del sistema
• DBMS_SCHEDULER: programa la seva execució cada dia a les 8h

Bones pràctiques

• Guarda scripts en carpetes versionades (ex: GIT)
• Documenta les tasques automatitzades
• Revisa els permisos dels usuaris que executen les tasques
• ⚠️ Evita duplicar jobs o crear-ne de recurrents sense control

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

Repàs...