You need to sign in or sign up before continuing.
Select Git revision
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
package.sql 9.27 KiB
CREATE OR REPLACE PACKAGE "UNICAEN_TBL" AS
ACTIV_TRIGGERS BOOLEAN DEFAULT TRUE;
ACTIV_CALCULS BOOLEAN DEFAULT TRUE;
TYPE t_params IS RECORD (
p1 VARCHAR2(30), v1 VARCHAR2(80),
p2 VARCHAR2(30), v2 VARCHAR2(80),
p3 VARCHAR2(30), v3 VARCHAR2(80),
p4 VARCHAR2(30), v4 VARCHAR2(80),
p5 VARCHAR2(30), v5 VARCHAR2(80)
);
CALCUL_PROC_PARAMS t_params;
FUNCTION QUERY_APPLY_PARAM( sqlQuery VARCHAR2, param VARCHAR2, value VARCHAR2) RETURN CLOB;
FUNCTION MAKE_WHERE(param VARCHAR2 DEFAULT NULL, value VARCHAR2 DEFAULT NULL, alias VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
PROCEDURE CALCULER( TBL_NAME VARCHAR2 );
PROCEDURE CALCULER( TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2 );
PROCEDURE CALCULER(TBL_NAME VARCHAR2, params t_params);
PROCEDURE DEMANDE_CALCUL( TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2 );
PROCEDURE ANNULER_DEMANDES;
PROCEDURE ANNULER_DEMANDES( TBL_NAME VARCHAR2 );
PROCEDURE CALCULER_DEMANDES;
-- AUTOMATIC GENERATION --
-- END OF AUTOMATIC GENERATION --
END UNICAEN_TBL;
/
CREATE OR REPLACE PACKAGE BODY "UNICAEN_TBL" AS
TYPE t_dems_values IS TABLE OF BOOLEAN INDEX BY VARCHAR2(80);
TYPE t_dems_params IS TABLE OF t_dems_values INDEX BY VARCHAR2(30);
TYPE t_dems IS TABLE OF t_dems_params INDEX BY VARCHAR2(30);
dems t_dems;
FUNCTION MAKE_WHERE(param VARCHAR2 DEFAULT NULL, value VARCHAR2 DEFAULT NULL,
alias VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
res VARCHAR2(120) DEFAULT '';
BEGIN
IF param IS NULL THEN
RETURN '1=1';
END IF;
IF alias IS NOT NULL THEN
res := alias || '.';
END IF;
IF value IS NULL THEN
RETURN res || param || ' IS NULL';
END IF;
RETURN res || param || ' = q''[' || value || ']''';
END;
FUNCTION QUERY_APPLY_PARAM(sqlQuery VARCHAR2, param VARCHAR2, value VARCHAR2) RETURN CLOB IS
pos NUMERIC;
paramLen NUMERIC;
paramComm VARCHAR2(200);
debComm NUMERIC;
endComm NUMERIC;
debReal NUMERIC;
realParam VARCHAR2(80); realValue VARCHAR2(120);
q CLOB;
BEGIN
q := sqlQuery;
IF param IS NULL THEN
RETURN q;
END IF;
paramlen := length(param);
IF value IS NULL THEN
realValue := ' IS NULL';
ELSE
BEGIN
realValue := TO_NUMBER(value);
EXCEPTION
WHEN VALUE_ERROR THEN
realValue := 'q''[' || value || ']''';
END;
realValue := '=' || realValue;
END IF;
LOOP
pos := instr(q, '/*@' || param, 1, 1);
EXIT WHEN pos = 0;
debComm := pos - 1;
endComm := instr(q, '*/', pos, 1);
paramComm := substr(q, debComm, endComm - debComm);
debReal := instr(paramComm, '=', 1, 1);
realParam := trim(substr(paramComm, debReal + 1));
--realParam := 'AND ' || substr(q,pos + paramLen + 4,endComm-pos - paramLen - 4);
realParam := 'AND ' || realParam || realValue;
q := substr(q, 1, debComm) || realParam || substr(q, endComm + 2);
END LOOP;
RETURN q;
END;
FUNCTION QUERY_APPLY_PARAMS(sqlQuery VARCHAR2, useParams BOOLEAN DEFAULT FALSE) RETURN CLOB IS
q CLOB;
BEGIN
q := sqlQuery;
IF NOT useParams THEN
RETURN q;
END IF;
IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p1 IS NOT NULL THEN
q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1);
END IF;
IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p2 IS NOT NULL THEN
q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p2, UNICAEN_TBL.CALCUL_PROC_PARAMS.v2);
END IF;
IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p3 IS NOT NULL THEN
q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p3, UNICAEN_TBL.CALCUL_PROC_PARAMS.v3);
END IF;
IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p4 IS NOT NULL THEN
q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p4, UNICAEN_TBL.CALCUL_PROC_PARAMS.v4);
END IF;
IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p5 IS NOT NULL THEN
q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p5, UNICAEN_TBL.CALCUL_PROC_PARAMS.v5);
END IF;
RETURN q;
END;
FUNCTION PARAMS_MAKE_FILTER(useParams BOOLEAN DEFAULT FALSE) RETURN VARCHAR2 IS
filter VARCHAR2(4000) DEFAULT '';
BEGIN
IF NOT useParams THEN
RETURN '1=1';
END IF;
IF unicaen_tbl.calcul_proc_params.p1 IS NOT NULL THEN
IF filter IS NOT NULL THEN
filter := filter || ' AND ';
END IF;
filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p1 || ', t.' || unicaen_tbl.calcul_proc_params.p1 || ') ';
IF unicaen_tbl.calcul_proc_params.v1 IS NULL THEN
filter := filter || 'IS NULL';
ELSE
filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v1 || ']''';
END IF;
END IF;
IF unicaen_tbl.calcul_proc_params.p2 IS NOT NULL THEN
IF filter IS NOT NULL THEN
filter := filter || ' AND ';
END IF;
filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p2 || ', t.' || unicaen_tbl.calcul_proc_params.p2 || ') ';
IF unicaen_tbl.calcul_proc_params.v2 IS NULL THEN
filter := filter || 'IS NULL';
ELSE
filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v2 || ']''';
END IF;
END IF;
IF unicaen_tbl.calcul_proc_params.p3 IS NOT NULL THEN
IF filter IS NOT NULL THEN
filter := filter || ' AND ';
END IF;
filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p3 || ', t.' || unicaen_tbl.calcul_proc_params.p3 || ') ';
IF unicaen_tbl.calcul_proc_params.v3 IS NULL THEN
filter := filter || 'IS NULL';
ELSE
filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v3 || ']''';
END IF;
END IF;
IF unicaen_tbl.calcul_proc_params.p4 IS NOT NULL THEN
IF filter IS NOT NULL THEN
filter := filter || ' AND ';
END IF;
filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p4 || ', t.' || unicaen_tbl.calcul_proc_params.p4 || ') ';
IF unicaen_tbl.calcul_proc_params.v4 IS NULL THEN
filter := filter || 'IS NULL';
ELSE
filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v4 || ']''';
END IF;
END IF;
IF unicaen_tbl.calcul_proc_params.p5 IS NOT NULL THEN
IF filter IS NOT NULL THEN
filter := filter || ' AND ';
END IF;
filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p5 || ', t.' || unicaen_tbl.calcul_proc_params.p5 || ') ';
IF unicaen_tbl.calcul_proc_params.v5 IS NULL THEN
filter := filter || 'IS NULL';
ELSE
filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v5 || ']''';
END IF;
END IF;
IF filter = '' THEN
RETURN '1=1'; END IF;
RETURN filter;
END;
PROCEDURE CALCULER(TBL_NAME VARCHAR2) IS
params t_params;
BEGIN
ANNULER_DEMANDES(TBL_NAME);
CALCULER(TBL_NAME, params);
END;
PROCEDURE CALCULER(TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2) IS
calcul_proc varchar2(30);
params t_params;
BEGIN
IF NOT UNICAEN_TBL.ACTIV_CALCULS THEN RETURN; END IF;
SELECT custom_calcul_proc INTO calcul_proc FROM tbl WHERE tbl_name = CALCULER.TBL_NAME;
params.p1 := param;
params.v1 := value;
unicaen_tbl.calcul_proc_params := params;
IF calcul_proc IS NOT NULL THEN
EXECUTE IMMEDIATE
'BEGIN ' || calcul_proc || '(UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1); END;';
ELSE
EXECUTE IMMEDIATE
'BEGIN UNICAEN_TBL.C_' || TBL_NAME || '(TRUE); END;';
END IF;
END;
PROCEDURE CALCULER(TBL_NAME VARCHAR2, params t_params) IS
calcul_proc varchar2(30);
BEGIN
IF NOT UNICAEN_TBL.ACTIV_CALCULS THEN RETURN; END IF;
SELECT custom_calcul_proc INTO calcul_proc FROM tbl WHERE tbl_name = CALCULER.TBL_NAME;
unicaen_tbl.calcul_proc_params := params;
IF calcul_proc IS NOT NULL THEN
EXECUTE IMMEDIATE
'BEGIN ' || calcul_proc || '(UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1); END;';
ELSE
EXECUTE IMMEDIATE
'BEGIN UNICAEN_TBL.C_' || TBL_NAME || '(TRUE); END;';
END IF;
END;
PROCEDURE DEMANDE_CALCUL(TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2) IS
BEGIN
dems(TBL_NAME)(param)(value) := TRUE;
END;
PROCEDURE ANNULER_DEMANDES IS
BEGIN
dems.delete; END;
PROCEDURE ANNULER_DEMANDES(TBL_NAME VARCHAR2) IS
BEGIN
IF dems.exists(tbl_name) THEN
dems(tbl_name).delete;
END IF;
END;
FUNCTION HAS_DEMANDES RETURN BOOLEAN IS
BEGIN
RETURN dems.count > 0;
END;
PROCEDURE CALCULER_DEMANDES IS
d t_dems;
tbl_name VARCHAR2(30);
param VARCHAR2(30);
value VARCHAR2(80);
BEGIN
d := dems;
dems.delete;
tbl_name := d.FIRST;
LOOP EXIT WHEN tbl_name IS NULL;
param := d(tbl_name).FIRST;
LOOP EXIT WHEN param IS NULL;
value := d(tbl_name)(param).FIRST;
LOOP EXIT WHEN value IS NULL;
calculer(tbl_name, param, value);
value := d(tbl_name)(param).NEXT(value);
END LOOP;
param := d(tbl_name).NEXT(param);
END LOOP;
tbl_name := d.NEXT(tbl_name);
END LOOP;
IF HAS_DEMANDES THEN -- pour les boucles !!
CALCULER_DEMANDES;
END IF;
END;
-- AUTOMATIC GENERATION --
-- END OF AUTOMATIC GENERATION --
END UNICAEN_TBL;