Vous n'êtes pas identifié(e).
Bonjour,
J'ai créé une fonction (procédure stockée) qui nécessite l'insertion dans une table avec plusieurs millliers d'enregistrements.
J'aimerais donc faire un commit par lot de 10000 enregistrements par exemple. Comme l'utilisation de commit et rollback n'est permise dans une fonction, existe-il une alternative pour que l'insertion soit effective après traitement de 10000 enregistrements?
Toutes suggestions sont les bienvenues.
Merci d'avance.
Hors ligne
Quel est le but d'avoir un commit par 10k enregistrements ? Si c'est une question de visibilité des données, il n'y a pas de solution simple, à part de déplacer la fonction dans un script externe. Si c'est pour minimiser les pertes en cas d'erreur, vous pouvez utiliser SAVEPOINT / ROLLBACK TO
Julien.
https://rjuju.github.io/
Hors ligne
En fait, le problème c'est que l'exécution de la fonction va certainement prendre du temps (une journée et même plus). Donc au moins s'il y a commit, les données validées seront enregistrées dans la base.
Ci-après la fonction - qu'en pensez-vous ? La table appartement contient 104000 enregistrements ...
J'aimerais ajouter commit avant end loop mais ce n'est pas possible? comment faire ou comment optimiser cette fonction?
===============================
CREATE OR REPLACE FUNCTION generer_avis_imposition(var_exer_annee character varying, dat_avis_impos_date_limite date, int_operateur integer)
RETURNS character varying AS
$BODY$DECLARE
-- Declaration
BEGIN
var_role_type := 'IF';
SELECT INTO enreg *
FROM role
WHERE exer_annee= var_exer_annee
AND role_type = var_role_type
AND role_homolo IS NULL;
IF enreg.role_code IS NULL THEN
--avis_impos_ref
SELECT valeur_param_exer_val INTO int_role_num
FROM valeur_param_exercice
WHERE param_exer_key = 'SEQ_ROLE_IF' ;
INSERT INTO role (exer_annee, role_type,role_num,role_article) VALUES (var_exer_annee, var_role_type,int_role_num,1)
--recupere code apres insert
RETURNING "role_code" INTO STRICT int_rode_code;
--mise à jour sequence
UPDATE valeur_param_exercice
SET valeur_param_exer_val = int_role_num + 1
WHERE param_exer_key = 'SEQ_ROLE_IF' ;
ELSE
int_rode_code = enreg.role_code ;
END IF;
SELECT param_valeur INTO var_avis_impos_verificateur
FROM param_sys
WHERE param_key = 'P_NOM_VERIFICATEUR' ;
FOR enreg IN (SELECT
a.bien_code,
parcel_adresse , appart_superf_batie , parcel_superf_non_batie ,pr.dec_decoup_terri_code as rue ,quartier
FROM
appartement AS a
INNER JOIN batiment AS ba ON ba.batim_code = a.batim_code
INNER JOIN parcelle as pr ON pr.bien_code = ba.bien_code
INNER JOIN proprietaire_bien AS p ON p.bien_code = a.bien_code
INNER JOIN (SELECT A.type_decoup_code as type , A.decoup_terri_code as quartier , B.decoup_terri_code
FROM decoupage_territoire as A
INNER JOIN decoupage_territoire as B on A.decoup_terri_code = B.dec_decoup_terri_code
) AS dec ON dec.decoup_terri_code = pr.dec_decoup_terri_code
WHERE (a.appart_declare_if = 'f' OR a.appart_declare_if IS NULL)
AND appart_superf_batie > 0)
LOOP
SELECT INTO enreg_2 c.contri_nif , c.contri_adresse
FROM proprietaire_bien as p
INNER JOIN contribuable as c ON c.contri_nif = p.contri_nif
WHERE p.bien_code = enreg.bien_code;
t_avis_impos_adresse_contribuable := enreg_2.contri_adresse ;
v_contri_nif := enreg_2.contri_nif ;
--avis_impos_ref
SELECT valeur_param_exer_val INTO int_valeur_param_exer_val
FROM valeur_param_exercice
WHERE param_exer_key = 'SEQ_AI' ;
--liste contribuable
t_avis_impos_contribuable := '' ;
v_sep := '';
FOR enreg_2 IN (SELECT *
FROM vw_contribuable
WHERE bien_code = enreg.bien_code ) LOOP
t_avis_impos_contribuable := t_avis_impos_contribuable || v_sep || enreg_2.contri_nom_rs ;
v_sep := ',';
END LOOP;
v_avis_impos_ref = int_valeur_param_exer_val || '/' ||var_exer_annee ;
t_avis_impos_adresse_bien := enreg.parcel_adresse ;
t_avis_impos_quartier := enreg.quartier ;
-- numéro article
SELECT MAX(avis_impos_article::int) INTO int_max_avis_impos_article
FROM avis_imposition
WHERE role_code = int_rode_code ;
IF int_max_avis_impos_article IS NULL THEN
int_avis_impos_article := 1 ;
ELSE
int_avis_impos_article := int_max_avis_impos_article + 1 ;
END IF;
INSERT INTO avis_imposition (contri_nif,exer_annee,role_code ,avis_impos_ref , avis_impos_contribuable,avis_impos_adresse_bien , avis_impos_quartier ,avis_impos_bien ,avis_impos_adresse_contribuable , avis_impos_article ,avis_impos_date_limite ,avis_impos_verificateur ,operateur , avis_impos_genere)
VALUES (v_contri_nif,var_exer_annee, int_rode_code , v_avis_impos_ref , t_avis_impos_contribuable , t_avis_impos_adresse_bien ,t_avis_impos_quartier , enreg.bien_code ,t_avis_impos_adresse_contribuable,int_avis_impos_article,dat_avis_impos_date_limite , var_avis_impos_verificateur , int_operateur ,'t')
RETURNING "avis_impos_code" INTO STRICT int_avis_impos_code;
--mise à jour sequence
UPDATE valeur_param_exercice
SET valeur_param_exer_val = int_valeur_param_exer_val + 1
WHERE param_exer_key = 'SEQ_AI' ;
---Article role
---detail
res := regenerer_details_avis_if(int_avis_impos_code, enreg.bien_code , var_exer_annee , NULL ) ;
SELECT
SUM( COALESCE( det_avis_if_taux_batie , 0) * COALESCE(det_avis_if_surf_batie,0) )
+ SUM(COALESCE(det_avis_if_taux_non_batie,0) * COALESCE(det_avis_if_surf_non_batie ,0) ) INTO flo_avis_impos_mt_ppal
FROM details_avis_if
WHERE avis_impos_code = int_avis_impos_code ;
UPDATE avis_imposition
SET avis_impos_mt_ppal = flo_avis_impos_mt_ppal ,
avis_impos_reste_ppal = flo_avis_impos_mt_ppal
WHERE avis_impos_code = int_avis_impos_code ;
UPDATE appartement
SET appart_declare_if = 't'
WHERE bien_code = enreg.bien_code ;
-- COMMIT;
END LOOP;-- end appartement
--mise à jour article role
UPDATE role SET role_article = (SELECT MAX(avis_impos_article::int) FROM avis_imposition WHERE role_code = int_rode_code) WHERE role_code = int_rode_code ;
RETURN '1' ;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION regenere_avis_imposition(character varying, date, integer)
OWNER TO postgres;
========================================
Merci.
Serge
Hors ligne
Le faire de rajouter un COMMIT intermédaire veut aussi dire qu'en cas d'erreur, vous avez la moitié du traitement qui est fait, et c'est en général délicat de revenir à un état cohérent.
Je n'ai pas regardé en détail votre procédure stockée, mais à première vue il me semble tout à fait possible de remplacer un traitement à base de curseurs par une (ou pusieurs si besoin) requêtes SQL, ce qui sera beaucoup plus performant.
Julien.
https://rjuju.github.io/
Hors ligne
