-- Bloc anonyme
DECLARE
  -- déclarations (variables, curseurs, exceptions)
  v_name VARCHAR2(100) := 'Alice';
BEGIN
  -- instructions exécutables
  DBMS_OUTPUT.PUT_LINE('Hello ' || v_name);

  -- sous-bloc imbriqué
  DECLARE
    v_inner NUMBER := 42;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_inner);
  END;

EXCEPTION
  -- gestion des erreurs
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM);
END;
/

-- Le / final exécute le bloc dans SQL*Plus / SQLcl / SQL Developer
-- DECLARE est optionnel si pas de variables
-- EXCEPTION est optionnel
-- BEGIN ... END; est obligatoire

-- Numériques
v_int      NUMBER             := 42;
v_dec      NUMBER(10,2)       := 3.14;
v_pls      PLS_INTEGER        := 100;        -- plus rapide que NUMBER
v_bin      BINARY_INTEGER     := 200;
v_float    BINARY_DOUBLE      := 1.5;
v_simple   SIMPLE_INTEGER     := 0;          -- NOT NULL, pas de check overflow

-- Chaînes
v_str      VARCHAR2(4000)    := 'Hello';    -- max 32767 en PL/SQL
v_char     CHAR(10)          := 'ABC';      -- padding espaces
v_clob     CLOB              := 'Texte long...';
v_nchar    NVARCHAR2(100)    := N'Unicode';
v_raw      RAW(200);

-- Date / Heure
v_date     DATE              := SYSDATE;
v_ts       TIMESTAMP         := SYSTIMESTAMP;
v_tstz     TIMESTAMP WITH TIME ZONE;
v_interval INTERVAL DAY TO SECOND;

-- Booléen (PL/SQL uniquement, pas en SQL)
v_bool     BOOLEAN           := TRUE;       -- TRUE, FALSE, NULL

-- LOB
v_blob     BLOB;
v_bfile    BFILE;

-- Constante
c_pi CONSTANT NUMBER := 3.14159;

-- NOT NULL
v_count PLS_INTEGER NOT NULL := 0;

-- %TYPE — ancrage au type d'une colonne ou variable
v_name employees.last_name%TYPE;

-- %ROWTYPE — ancrage à la structure d'une table/curseur
v_emp employees%ROWTYPE;
v_emp.last_name := 'Diallo';

-- Sous-types utilisateur
SUBTYPE t_name IS VARCHAR2(100);
v_first t_name;

TO_NUMBER('42')                        -- string → number
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI') -- date → string
TO_DATE('15/06/2024', 'DD/MM/YYYY')   -- string → date
TO_TIMESTAMP('2024-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
CAST(v_number AS VARCHAR2(20))
TO_CHAR(1234.5, 'FM9,999.00')          -- "1,234.50"

-- ── IF / ELSIF / ELSE ──
IF v_score >= 90 THEN
  v_grade := 'A';
ELSIF v_score >= 80 THEN
  v_grade := 'B';
ELSE
  v_grade := 'C';
END IF;

-- ── CASE expression ──
v_label := CASE v_status
  WHEN 'A' THEN 'Actif'
  WHEN 'I' THEN 'Inactif'
  ELSE 'Inconnu'
END;

-- CASE statement (searched)
CASE
  WHEN v_sal > 10000 THEN do_high();
  WHEN v_sal > 5000  THEN do_mid();
  ELSE do_low();
END CASE;

-- ── Boucle simple (infinie sans EXIT) ──
LOOP
  EXIT WHEN v_i > 10;
  v_i := v_i + 1;
END LOOP;

-- ── WHILE ──
WHILE v_i <= 10 LOOP
  v_i := v_i + 1;
END LOOP;

-- ── FOR (numérique) ──
FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- FOR inversé
FOR i IN REVERSE 1..10 LOOP ... END LOOP;

-- ── FOR curseur implicite ──
FOR rec IN (SELECT * FROM employees WHERE dept_id = 10) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.last_name);
END LOOP;

-- CONTINUE / EXIT
CONTINUE;                -- itération suivante
CONTINUE WHEN cond;      -- continue conditionnel
EXIT;                    -- sort de la boucle
EXIT WHEN cond;          -- exit conditionnel

-- GOTO (à éviter)
GOTO end_block;
<<end_block>>
NULL;                    -- instruction vide (placeholder)

-- ══ Curseur implicite (SQL dans PL/SQL) ══
SELECT last_name INTO v_name FROM employees WHERE employee_id = 100;
-- Lève NO_DATA_FOUND si 0 lignes, TOO_MANY_ROWS si > 1

-- Attributs du curseur implicite
SQL%FOUND      -- TRUE si le dernier DML a affecté ≥ 1 ligne
SQL%NOTFOUND   -- TRUE si 0 lignes
SQL%ROWCOUNT   -- nombre de lignes affectées
SQL%ISOPEN     -- toujours FALSE pour implicite

-- ══ Curseur explicite ══
DECLARE
  CURSOR c_emp IS
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = 10;
  r_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO r_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r_emp.last_name || ' : ' || r_emp.salary);
  END LOOP;
  CLOSE c_emp;
END;

-- ══ Curseur FOR (plus simple — open/fetch/close automatique) ══
FOR rec IN c_emp LOOP
  DBMS_OUTPUT.PUT_LINE(rec.last_name);
END LOOP;

-- ══ Curseur paramétré ══
CURSOR c_dept(p_dept_id NUMBER) IS
  SELECT * FROM employees WHERE department_id = p_dept_id;

FOR rec IN c_dept(20) LOOP ... END LOOP;

-- ══ Curseur avec FOR UPDATE (verrou) ══
CURSOR c_lock IS
  SELECT * FROM employees WHERE dept_id = 10 FOR UPDATE OF salary;

UPDATE employees SET salary = salary * 1.1 WHERE CURRENT OF c_lock;

-- ── Exceptions prédéfinies ──
EXCEPTION
  WHEN NO_DATA_FOUND      THEN ...  -- SELECT INTO 0 lignes
  WHEN TOO_MANY_ROWS      THEN ...  -- SELECT INTO > 1 ligne
  WHEN ZERO_DIVIDE        THEN ...  -- division par zéro
  WHEN VALUE_ERROR        THEN ...  -- conversion/taille incorrecte
  WHEN DUP_VAL_ON_INDEX   THEN ...  -- violation UNIQUE
  WHEN INVALID_CURSOR     THEN ...  -- opération sur curseur fermé
  WHEN CURSOR_ALREADY_OPEN THEN ...
  WHEN LOGIN_DENIED       THEN ...
  WHEN PROGRAM_ERROR      THEN ...
  WHEN OTHERS             THEN ...  -- catch-all (toujours en dernier)

-- Fonctions d'erreur
SQLCODE    -- code numérique de l'erreur
SQLERRM    -- message d'erreur
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  -- pile d'appels
DBMS_UTILITY.FORMAT_ERROR_STACK      -- pile d'erreurs

-- ── Exception utilisateur ──
DECLARE
  e_salary_too_high EXCEPTION;
BEGIN
  IF v_sal > 50000 THEN
    RAISE e_salary_too_high;
  END IF;
EXCEPTION
  WHEN e_salary_too_high THEN
    DBMS_OUTPUT.PUT_LINE('Salaire trop élevé');
END;

-- ── RAISE_APPLICATION_ERROR (erreur personnalisée avec code) ──
RAISE_APPLICATION_ERROR(-20001, 'Message custom');
-- Codes utilisateur : -20000 à -20999

-- ── PRAGMA EXCEPTION_INIT — associer un code Oracle à une exception ──
DECLARE
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
BEGIN
  ...
EXCEPTION
  WHEN e_deadlock THEN ...
END;

CREATE OR REPLACE PROCEDURE augmenter_salaire (
  p_emp_id  IN     NUMBER,
  p_pct     IN     NUMBER DEFAULT 10,
  p_new_sal    OUT  NUMBER
)
IS
  v_old_sal NUMBER;
BEGIN
  SELECT salary INTO v_old_sal
  FROM employees WHERE employee_id = p_emp_id;

  p_new_sal := v_old_sal * (1 + p_pct / 100);

  UPDATE employees SET salary = p_new_sal
  WHERE employee_id = p_emp_id;

  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010, 'Employé non trouvé');
END augmenter_salaire;
/

-- Modes de paramètres :
-- IN      : lecture seule (défaut)
-- OUT     : écriture seule (retourne une valeur)
-- IN OUT  : lecture + écriture

-- Appel
DECLARE v_sal NUMBER;
BEGIN
  augmenter_salaire(p_emp_id => 100, p_pct => 15, p_new_sal => v_sal);
  DBMS_OUTPUT.PUT_LINE('Nouveau salaire: ' || v_sal);
END;

-- Supprimer
DROP PROCEDURE augmenter_salaire;

CREATE OR REPLACE FUNCTION calculer_age (
  p_birth_date IN DATE
)
RETURN NUMBER
DETERMINISTIC                -- même entrée = même sortie (optimisable)
IS
BEGIN
  RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_birth_date) / 12);
END calculer_age;
/

-- Utilisation dans SQL
SELECT last_name, calculer_age(hire_date) AS anciennete
FROM employees;

-- Utilisation en PL/SQL
v_age := calculer_age(DATE '1990-05-15');

-- Fonction pipelined (retourne des lignes au fur et à mesure)
CREATE OR REPLACE FUNCTION get_names
RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
BEGIN
  FOR rec IN (SELECT last_name FROM employees) LOOP
    PIPE ROW(rec.last_name);
  END LOOP;
  RETURN;
END;

SELECT * FROM TABLE(get_names());

-- Clauses utiles
-- DETERMINISTIC    : résultat cacheable
-- RESULT_CACHE     : cache Oracle interne
-- PARALLEL_ENABLE  : exécution parallèle

-- ══ Spécification (interface publique) ══
CREATE OR REPLACE PACKAGE pkg_employee IS
  -- Constantes publiques
  c_max_salary CONSTANT NUMBER := 100000;

  -- Types publics
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;

  -- Procédures / Fonctions publiques
  PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER);
  FUNCTION  get_salary(p_id NUMBER) RETURN NUMBER;
  PROCEDURE fire(p_id NUMBER);
END pkg_employee;
/

-- ══ Body (implémentation) ══
CREATE OR REPLACE PACKAGE BODY pkg_employee IS

  -- Variable privée (pas dans la spec)
  g_hire_count PLS_INTEGER := 0;

  -- Fonction privée
  FUNCTION validate_salary(p_sal NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_sal BETWEEN 0 AND c_max_salary;
  END;

  PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER) IS
  BEGIN
    IF NOT validate_salary(p_sal) THEN
      RAISE_APPLICATION_ERROR(-20001, 'Salaire invalide');
    END IF;
    INSERT INTO employees(last_name, salary) VALUES(p_name, p_sal);
    g_hire_count := g_hire_count + 1;
  END;

  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
    v_sal NUMBER;
  BEGIN
    SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id;
    RETURN v_sal;
  END;

  PROCEDURE fire(p_id NUMBER) IS
  BEGIN
    DELETE FROM employees WHERE employee_id = p_id;
  END;

END pkg_employee;
/

-- Appel
pkg_employee.hire('Diallo', 45000);
v_sal := pkg_employee.get_salary(100);

-- Variables package = état de session (persistent pendant la session)

-- ══ DML Trigger (ligne) ══
CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  v_action VARCHAR2(10);
BEGIN
  CASE
    WHEN INSERTING THEN v_action := 'INSERT';
    WHEN UPDATING THEN v_action := 'UPDATE';
    WHEN DELETING THEN v_action := 'DELETE';
  END CASE;

  INSERT INTO audit_log(table_name, action, old_val, new_val, change_date)
  VALUES('EMPLOYEES', v_action,
         :OLD.salary,    -- valeur AVANT modification (NULL pour INSERT)
         :NEW.salary,    -- valeur APRÈS modification (NULL pour DELETE)
         SYSTIMESTAMP);
END;
/

-- Prédicats conditionnels dans le trigger :
-- INSERTING, UPDATING, DELETING → BOOLEAN
-- UPDATING('column_name')       → TRUE si cette colonne est modifiée

-- ══ DML Trigger (statement level — pas FOR EACH ROW) ══
CREATE OR REPLACE TRIGGER trg_no_weekend
BEFORE INSERT ON orders
BEGIN
  IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR(-20002, 'Pas d''insertion le weekend');
  END IF;
END;
/

-- ══ Compound Trigger (Oracle 11g+) ══
CREATE OR REPLACE TRIGGER trg_compound
FOR INSERT ON employees
COMPOUND TRIGGER

  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids := t_ids();

  BEFORE EACH ROW IS
  BEGIN
    :NEW.created_at := SYSTIMESTAMP;
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    v_ids.EXTEND;
    v_ids(v_ids.LAST) := :NEW.employee_id;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FORALL i IN v_ids.FIRST..v_ids.LAST
      INSERT INTO emp_log VALUES(v_ids(i), SYSDATE);
  END AFTER STATEMENT;

END trg_compound;
/

-- ══ DDL Trigger ══
CREATE OR REPLACE TRIGGER trg_ddl_log
AFTER DDL ON SCHEMA
BEGIN
  INSERT INTO ddl_log VALUES(
    ORA_DICT_OBJ_NAME, ORA_SYSEVENT, SYSTIMESTAMP, USER);
END;
/

-- ══ INSTEAD OF Trigger (sur une vue) ══
CREATE OR REPLACE TRIGGER trg_view_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees(last_name) VALUES(:NEW.last_name);
END;
/

-- Désactiver / Réactiver
ALTER TRIGGER trg_emp_audit DISABLE;
ALTER TRIGGER trg_emp_audit ENABLE;
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- ══ Associative Array (INDEX BY) — PL/SQL only ══
DECLARE
  TYPE t_scores IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
  v_scores t_scores;
BEGIN
  v_scores('Alice') := 95;
  v_scores('Bob')   := 87;
  DBMS_OUTPUT.PUT_LINE(v_scores('Alice'));  -- 95
  DBMS_OUTPUT.PUT_LINE(v_scores.COUNT);     -- 2
END;

-- ══ Nested Table ══
TYPE t_names IS TABLE OF VARCHAR2(100);
v_names t_names := t_names('Alice', 'Bob', 'Charlie');
v_names.EXTEND;  v_names(v_names.LAST) := 'Diana';
v_names.DELETE(2);  -- supprime l'index 2 (crée un "trou")

-- ══ VARRAY (taille fixe max) ══
TYPE t_colors IS VARRAY(5) OF VARCHAR2(20);
v_colors t_colors := t_colors('Red', 'Green', 'Blue');

-- ══ Méthodes de collection ══
col.COUNT          -- nombre d'éléments
col.FIRST          -- premier index
col.LAST           -- dernier index
col.NEXT(i)        -- index suivant après i
col.PRIOR(i)       -- index précédent avant i
col.EXISTS(i)      -- TRUE si l'index i existe
col.EXTEND         -- ajoute 1 élément null
col.EXTEND(n)      -- ajoute n éléments
col.TRIM           -- supprime le dernier
col.TRIM(n)        -- supprime les n derniers
col.DELETE         -- supprime tout
col.DELETE(i)      -- supprime l'index i
col.DELETE(i, j)   -- supprime de i à j
col.LIMIT          -- taille max (VARRAY seulement)

-- ══ Type collection pour SQL (CREATE TYPE) ══
CREATE TYPE t_number_tab IS TABLE OF NUMBER;
/
SELECT * FROM TABLE(t_number_tab(1, 2, 3));

-- ══ Record utilisateur ══
DECLARE
  TYPE t_address IS RECORD (
    street  VARCHAR2(200),
    city    VARCHAR2(100),
    zipcode VARCHAR2(10)
  );
  v_addr t_address;
BEGIN
  v_addr.street  := '12 rue de Paris';
  v_addr.city    := 'Saint-Denis';
  v_addr.zipcode := '93200';
END;

-- ══ %ROWTYPE — hérite de la structure d'une table ══
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ' - ' || v_emp.salary);
END;

-- ══ %TYPE — hérite du type d'une colonne ══
v_name employees.last_name%TYPE;
v_sal  employees.salary%TYPE;

-- ══ Table de records ══
TYPE t_emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_emps t_emp_tab;

-- Insérer un record entier dans une table
INSERT INTO employees VALUES v_emp;

-- ══ NDS (Native Dynamic SQL) ══

-- DDL dynamique
EXECUTE IMMEDIATE 'CREATE TABLE tmp_test (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE 'DROP TABLE tmp_test';

-- DML avec bind variables (USING)
EXECUTE IMMEDIATE
  'UPDATE employees SET salary = salary * :pct WHERE dept_id = :dept'
  USING 1.1, 10;

-- SELECT INTO
EXECUTE IMMEDIATE
  'SELECT last_name FROM employees WHERE employee_id = :id'
  INTO v_name
  USING 100;

-- Retour OUT
EXECUTE IMMEDIATE
  'BEGIN :result := my_func(:x); END;'
  USING OUT v_result, IN 42;

-- ══ DBMS_SQL (ancien, plus flexible pour SQL très dynamique) ══
DECLARE
  v_cursor INTEGER;
  v_rows   INTEGER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'DELETE FROM tmp WHERE id = :id', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', 42);
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

-- ⚠️ Toujours utiliser des bind variables pour éviter l'injection SQL
-- ❌ 'WHERE id = ' || v_id       → injection possible
-- ✅ 'WHERE id = :id' USING v_id → sûr

-- ══ BULK COLLECT — récupérer plusieurs lignes d'un coup ══
DECLARE
  TYPE t_names IS TABLE OF employees.last_name%TYPE;
  v_names t_names;
BEGIN
  SELECT last_name BULK COLLECT INTO v_names
  FROM employees
  WHERE department_id = 10;

  FOR i IN v_names.FIRST..v_names.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(v_names(i));
  END LOOP;
END;

-- BULK COLLECT avec LIMIT (gros volumes)
DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  v_emps t_emp_tab;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_emps LIMIT 1000;
    EXIT WHEN v_emps.COUNT = 0;

    -- Traiter le batch
    FORALL i IN v_emps.FIRST..v_emps.LAST
      INSERT INTO emp_archive VALUES v_emps(i);

    COMMIT;
  END LOOP;
  CLOSE c_emp;
END;

-- ══ FORALL — DML en masse (1 context switch au lieu de N) ══
FORALL i IN v_ids.FIRST..v_ids.LAST
  DELETE FROM employees WHERE employee_id = v_ids(i);

-- FORALL avec SAVE EXCEPTIONS (continue malgré les erreurs)
BEGIN
  FORALL i IN v_data.FIRST..v_data.LAST SAVE EXCEPTIONS
    INSERT INTO target VALUES v_data(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Index: ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ' Code: ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
    END LOOP;
END;

-- INDICES OF / VALUES OF pour collections sparse
FORALL i IN INDICES OF v_sparse_coll
  UPDATE ...

-- ══ SYS_REFCURSOR (weak ref cursor prédéfini) ══
CREATE OR REPLACE PROCEDURE get_employees (
  p_dept_id IN  NUMBER,
  p_cursor  OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_cursor FOR
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;
END;
/

-- Appel
DECLARE
  v_cur SYS_REFCURSOR;
  v_id  NUMBER;  v_name VARCHAR2(100);  v_sal NUMBER;
BEGIN
  get_employees(10, v_cur);
  LOOP
    FETCH v_cur INTO v_id, v_name, v_sal;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ' : ' || v_sal);
  END LOOP;
  CLOSE v_cur;
END;

-- ══ Strong REF CURSOR (typé) ══
TYPE t_emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;

-- ══ REF CURSOR pour SQL dynamique ══
OPEN v_cur FOR 'SELECT * FROM ' || v_table_name;

-- ── Contrôle de transaction ──
COMMIT;                          -- valide toutes les modifications
ROLLBACK;                        -- annule tout depuis le dernier COMMIT
SAVEPOINT sp1;                   -- point de sauvegarde
ROLLBACK TO SAVEPOINT sp1;       -- annule jusqu'au savepoint

-- ── Transaction autonome ──
CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;   -- transaction indépendante
BEGIN
  INSERT INTO error_log VALUES(p_msg, SYSTIMESTAMP);
  COMMIT;                          -- commit indépendant du parent
END;

-- ── Verrous ──
SELECT * FROM employees
WHERE employee_id = 100
FOR UPDATE;                      -- verrouille la ligne

FOR UPDATE OF salary;            -- verrouille la colonne
FOR UPDATE NOWAIT;               -- erreur immédiate si verrouillé
FOR UPDATE WAIT 5;               -- attend 5 secondes
FOR UPDATE SKIP LOCKED;          -- ignore les lignes verrouillées

-- ── Isolation ──
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- défaut Oracle
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

-- Activer la sortie (SQL*Plus / SQLcl)
SET SERVEROUTPUT ON SIZE UNLIMITED

-- Afficher
DBMS_OUTPUT.PUT_LINE('Message');
DBMS_OUTPUT.PUT('Sans retour à la ligne');
DBMS_OUTPUT.NEW_LINE;

-- ── DBMS_APPLICATION_INFO (monitoring dans v$session) ──
DBMS_APPLICATION_INFO.SET_MODULE('Mon App', 'Chargement');
DBMS_APPLICATION_INFO.SET_ACTION('Étape 1');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('user=alice');

-- ── Timing ──
v_start := DBMS_UTILITY.GET_TIME;    -- centièmes de seconde
-- ... traitement ...
v_elapsed := (DBMS_UTILITY.GET_TIME - v_start) / 100;  -- secondes

-- ── Warnings de compilation ──
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
SHOW ERRORS;                         -- après CREATE avec erreurs
SELECT * FROM user_errors WHERE name = 'MY_PROC';

-- ══ UTL_FILE — lecture/écriture de fichiers serveur ══
-- Nécessite un DIRECTORY Oracle : CREATE DIRECTORY my_dir AS '/tmp';
DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('MY_DIR', 'output.csv', 'W');  -- W=write, R=read, A=append
  UTL_FILE.PUT_LINE(v_file, 'id,name,salary');
  FOR rec IN (SELECT * FROM employees) LOOP
    UTL_FILE.PUT_LINE(v_file,
      rec.employee_id || ',' || rec.last_name || ',' || rec.salary);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN UTL_FILE.FCLOSE(v_file); END IF;
    RAISE;
END;

-- Lecture
v_file := UTL_FILE.FOPEN('MY_DIR', 'input.txt', 'R');
LOOP
  BEGIN
    UTL_FILE.GET_LINE(v_file, v_line);
    DBMS_OUTPUT.PUT_LINE(v_line);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN EXIT;
  END;
END LOOP;
UTL_FILE.FCLOSE(v_file);

-- ══ UTL_HTTP — requêtes HTTP depuis PL/SQL ══
DECLARE
  v_resp UTL_HTTP.RESP;
  v_body VARCHAR2(4000);
BEGIN
  v_resp := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/data');
  v_resp := UTL_HTTP.GET_RESPONSE(v_resp);
  UTL_HTTP.READ_TEXT(v_resp, v_body);
  UTL_HTTP.END_RESPONSE(v_resp);
  DBMS_OUTPUT.PUT_LINE(v_body);
END;

-- ══ DBMS_SCHEDULER — jobs planifiés ══
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'JOB_CLEANUP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN cleanup_proc; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled    => TRUE
  );
END;

-- ══ Type objet (OOP en PL/SQL) ══
CREATE OR REPLACE TYPE t_person AS OBJECT (
  name  VARCHAR2(100),
  age   NUMBER,

  MEMBER FUNCTION greet RETURN VARCHAR2,
  MEMBER PROCEDURE birthday,
  ORDER MEMBER FUNCTION compare(p t_person) RETURN INTEGER
);
/

CREATE OR REPLACE TYPE BODY t_person AS
  MEMBER FUNCTION greet RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello, I am ' || SELF.name || ', age ' || SELF.age;
  END;

  MEMBER PROCEDURE birthday IS
  BEGIN
    SELF.age := SELF.age + 1;
  END;

  ORDER MEMBER FUNCTION compare(p t_person) RETURN INTEGER IS
  BEGIN
    RETURN CASE WHEN SELF.age < p.age THEN -1
                WHEN SELF.age > p.age THEN 1 ELSE 0 END;
  END;
END;
/

-- Héritage (UNDER)
CREATE TYPE t_employee UNDER t_person (
  salary NUMBER,
  OVERRIDING MEMBER FUNCTION greet RETURN VARCHAR2
) NOT FINAL;
/

-- Utilisation
DECLARE
  v_p t_person := t_person('Alice', 30);
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_p.greet());
  v_p.birthday();
END;

-- ── Expressions régulières améliorées ──

-- ── JSON natif ──
DECLARE
  v_json JSON := JSON('{"name":"Alice","age":30}');
BEGIN
  DBMS_OUTPUT.PUT_LINE(JSON_VALUE(v_json, '$.name'));
END;

-- ── PL/SQL qualifiées expressions ──
v_names := t_names('Alice', 'Bob');  -- déjà possible avant, mais étendu

-- ── BOOLEAN en SQL (enfin !) ──
CREATE TABLE features (
  name    VARCHAR2(100),
  enabled BOOLEAN DEFAULT FALSE
);
SELECT * FROM features WHERE enabled = TRUE;

-- ── IF [NOT] EXISTS pour DDL ──
CREATE TABLE IF NOT EXISTS my_table (id NUMBER);
DROP TABLE IF EXISTS my_table;

-- ── Table value constructor ──
SELECT * FROM (VALUES
  (1, 'Alice'),
  (2, 'Bob')
) AS t(id, name);

-- ── Direct joins for UPDATE / DELETE ──
UPDATE employees e SET e.salary = e.salary * 1.1
FROM departments d
WHERE e.dept_id = d.dept_id AND d.name = 'IT';

-- ── SQL Domains (contraintes réutilisables) ──
CREATE DOMAIN email_domain AS VARCHAR2(255)
  CHECK (REGEXP_LIKE(VALUE, '^[^@]+@[^@]+\.[^@]+$'));

-- ── Schema-level IF / CASE / Loop (PL/SQL-like en SQL) ──

-- ── GROUP BY column alias ──
SELECT EXTRACT(YEAR FROM hire_date) AS yr, COUNT(*)
FROM employees GROUP BY yr;

-- ── Annotations (métadonnées sur les objets) ──
CREATE TABLE orders (
  id NUMBER ANNOTATIONS('description', 'Primary key of orders')
);

-- ── SELECT ... RETURNING pour INSERT/UPDATE/DELETE ──
INSERT INTO employees(name,salary) VALUES('Eve',50000)
RETURNING employee_id INTO v_id;

-- ✅ BULK COLLECT + FORALL pour les traitements en masse
-- ✅ LIMIT sur BULK COLLECT pour contrôler la mémoire
-- ✅ PLS_INTEGER au lieu de NUMBER pour les compteurs
-- ✅ %TYPE et %ROWTYPE pour l'ancrage (maintenance)
-- ✅ Bind variables (USING) dans le SQL dynamique
-- ✅ SAVE EXCEPTIONS avec FORALL pour la gestion d'erreurs en masse
-- ✅ PRAGMA AUTONOMOUS_TRANSACTION pour le logging indépendant
-- ✅ Packages pour organiser le code (encapsulation)
-- ✅ Nommer les conventions : v_ (var), p_ (param), c_ (const), g_ (global)
-- ✅ RESULT_CACHE sur les fonctions déterministes fréquentes
-- ✅ Compound triggers pour éviter le mutating table
-- ✅ DBMS_APPLICATION_INFO pour le monitoring
-- ✅ Curseurs FOR (auto open/fetch/close)
-- ✅ Exception handlers spécifiques avant WHEN OTHERS

-- ❌ COMMIT dans les boucles (1 COMMIT par batch, pas par ligne)
-- ❌ SELECT * quand quelques colonnes suffisent
-- ❌ Concaténation dans le SQL dynamique (injection SQL !)
-- ❌ WHEN OTHERS THEN NULL (avaler les erreurs silencieusement)
-- ❌ Curseur explicite quand FOR...IN suffit
-- ❌ NUMBER pour les compteurs simples (utiliser PLS_INTEGER)
-- ❌ Logique métier dans les triggers (difficile à maintenir)
-- ❌ GOTO (code spaghetti)
-- ❌ Variables globales de package sans nécessité
-- ❌ Ignorer DBMS_UTILITY.FORMAT_ERROR_BACKTRACE dans WHEN OTHERS