Pasted

CREATE OR REPLACE FUNCTION FINDCREATURE(numero CREATURE.CREATUREID%TYPE) RETURN VARCHAR2 IS
chaine VARCHAR2(2000);
myCreature Creature%ROWTYPE;
jsonWriter VARCHAR2(500);
TYPE tabOfInt IS TABLE OF NOVEL.NOVELID%TYPE INDEX BY BINARY_INTEGER;
myTabOfInt1 tabOfInt;
myTabOfInt2 tabOfInt;
 
TYPE tabOfJson IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
myTabOfJson1 tabOfJson;
myTabOfJson2 tabOfJson;
 
BEGIN
    SELECT * INTO myCreature FROM Creature WHERE CREATUREID = numero;
    jsonWriter:=findwriter(myCreature.writerId);
    chaine:='{ "creatureId" : '||myCreature.creatureId||', "description" : "'||myCreature.description||'", "firstWriter" : '||jsonWriter||', "setOfNovels" : [';
    --On cherche les Novel pour le setOfNovels
    SELECT novelId BULK COLLECT INTO myTabOfInt1 FROM APPEARANCE WHERE CREATUREID=numero;
    
    IF(myTabOfInt1.count>1)
    THEN
        FOR i IN myTabOfInt1.FIRST .. myTabOfInt1.LAST LOOP
        myTabOfJson1(i):=findNovel(myTabOfInt1(i));
        END LOOP;
    ELSE
        IF(myTabOfInt1.COUNT=1)
        THEN
        myTabOfJson1(1):=findNovel(myTabOfInt1(1));
        END IF;
    END IF;
    
    
    IF(myTabOfJson1.count>1)
    THEN
        FOR i IN myTabOfJson1.FIRST .. myTabOfJson1.last-1 LOOP
            chaine:=chaine||myTabOfJson1(i)||', ';
        END LOOP;
        chaine:=chaine||myTabOfJson1(myTabOfJson1.LAST);
    ELSE
        IF(myTabOfInt1.COUNT=1)
        THEN
            chaine:=CONCAT(chaine,myTabOfJson1(1));
        END IF;
    END IF;
    
    chaine:=CONCAT(chaine,'], "setOfNames" : [ ');
 
    --On cherche les noms pour setOfNames
    SELECT creatureNameId BULK COLLECT INTO myTabOfInt2 FROM CreatureName WHERE creatureId=numero;
    IF(myTabOfInt2.count>1)
    THEN
        FOR i IN myTabOfInt2.FIRST .. myTabOfInt2.LAST LOOP
        myTabOfJson2(i):=findCreatureName(myTabOfInt2(i));
        END LOOP;
    ELSE
        IF(myTabOfInt2.COUNT=1)
        THEN
            myTabOfJson2(1):=findCreatureName(myTabOfInt2(1));
        END IF;
    END IF;
    
    IF(myTabOfJson2.count>1)
    THEN
        FOR i IN myTabOfJson2.FIRST .. myTabOfJson2.last-1 LOOP
            chaine:=chaine||myTabOfJson2(i)||', ';
        END LOOP;
        chaine:=chaine||myTabOfJson2(myTabOfJson2.LAST);
    ELSE
        IF(myTabOfInt2.COUNT=1)
        THEN
            chaine:=CONCAT(chaine,myTabOfJson2(1));
        END IF;
    END IF;
    
    chaine:=CONCAT(chaine, '] }');
    RETURN chaine;
END;