1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
|
PROCEDURE PRC_CHECKCONTACT(vTypeContact IN Number,
vNumContact IN Varchar2,
vNom IN Varchar2,
vPrenom IN Varchar2,
vSociete IN Varchar2,
vDeleg IN NUMBER,
vCodeAg IN NUMBER,
p_return_code OUT INTEGER,
p_sql_code OUT INTEGER,
p_error_text OUT VARCHAR2,
cur_Out OUT isarefcursor)
IS
toto NUMBER;
/*
typect NUMBER;
numct VARCHAR2(50);
nomct VARCHAR2(50);
prenomct VARCHAR2(50);
soc VARCHAR2(50);
deleg NUMBER;
codeag NUMBER;*/
sqlstring VARCHAR2(200);
begin
/*typect := vTypeContact;
deleg := vDeleg;
codeag := vCodeAg;
numct := vNumContact;
nomct := vNom;
prenomct := vPrenom;
soc := vSociete;
numct := vNumContact;
*/
sqlstring := 'SELECT c.id_contact,c.type_contact,c.num_contact,c.nom,c.prenom,c.societe,c.informations,c.code_agence,c.delegation ';
sqlstring := sqlstring || 'FROM ISA_CONTACT c ';
sqlstring := sqlstring || 'WHERE ';
sqlstring := sqlstring || 'c.nom = ' || '''' || vNom || ''' ';
sqlstring := sqlstring || 'AND ';
sqlstring := sqlstring || 'c.prenom = ' || '''' || vPrenom || ''' ';
IF vDeleg <> 0 THEN
sqlstring := sqlstring || 'AND c.delegation = ' || '''' || VDeleg || ''' ';
END IF;
IF vCodeAg <> 0 THEN
sqlstring := sqlstring || ' AND c.code_agence = ' || '''' || vCodeAg || ''' ';
END IF;
IF vTypeContact <> 0 THEN
sqlstring := sqlstring || 'AND c.type_contact = ' || '''' || vTypeContact || ''' ';
END IF;
IF LENGTH(vNumContact) > 0 THEN
sqlstring := sqlstring || 'AND c.num_contact = ' || '''' || vNumContact || ''' ';
END IF;
IF LENGTH(vSociete) > 0 THEN
sqlstring := sqlstring || 'AND c.societe = ' || '''' || vSociete || ''' ';
END IF;
dbms_output.put_line('TEST >> ' || sqlstring);
/*OPEN cur_Out FOR
sqlstring;
EXCEPTION
WHEN OTHERS THEN
p_return_code := -100;
p_error_text := SQLERRM;
p_sql_code := SQLCODE;
--RAISE_APPLICATION_ERROR(-20000,'La requête ne renvoie aucun résultat');
*/
end PRC_CHECKCONTACT; |
Partager