L'exécution d'une instruction sql dynamique dans un SYS_REFCURSOR

est-il possible d'exécuter une dynamique morceau de sql dans plsql et de retourner les résultats dans un sys_refcursor? J'ai collé ma tentative soo loin, mais dosnt couture de travailler, c'est l'erreur im obtenir via mon application java

ORA-01006: lier une variable n'a pas
existe ORA-06512: à
"LIVEFIS.ERC_REPORT_PK", la ligne 116
ORA-06512: à ligne 1

mais cela pourrait être quelque chose interprétée par java, tout coutures pour compiler des beaux-soo im pas sûr.

 procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
                                ,pReport out SYS_REFCURSOR) is
  begin
    declare
      lsql  varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
          ||' where c.id = ccf.carer_id (+)'
          ||' AND cf.id (+) = ccf.cared_for_id';

    begin

     if pPostcode is not null and pAge <= 0 then
        lsql := lsql||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
      elsif pPostcode is null and pAge > 0 then 
         lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge';
      elsif pPostcode is not null and pAge > 0 then
         lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge'
                      ||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
      end if;


        execute immediate lsql
        into pReport;


    end;
  end;

Im nouveau à plsql et même les plus récents de sql dynamique soo toute aide/suggestions seraient grandement appréciées.

Merci Encore

Jon

OriginalL'auteur user250643 | 2010-02-09

8

vous aurez pour lier les paramètres pAge et pPostcode. Dans SQL dynamique vous préfixe avec un deux-points (:). Si vous utilisez EXECUTE IMMEDIATE ou OPEN ... FOR, vous liez vos paramètres via la poste, c'est pourquoi j'ai renommé :P1 :P2 dans l'exemple:
```
DECLARE
   lsql VARCHAR2(500) := 'SELECT c.id 
                            FROM carer c, cared_for cf, carer_cared_for ccf 
                           WHERE c.id = ccf.carer_id (+)
                             AND cf.id (+) = ccf.cared_for_id';
BEGIN
   IF pPostcode IS NULL THEN
      lsql := lsql || ' AND :P1 IS NULL';
   ELSE
      lsql := lsql || ' AND c.postcode like ''%''|| upper(:P1)||''%''';
   IF pPostcode pAge > 0 THEN
      lsql := lsql || ' AND :P2 = ROUND((MONTHS_BETWEEN(sysdate,
                                                        c.date_of_birth)/12))';
   ELSE
      lsql := lsql || ' AND nvl(:P2, -1) <= 0';
   END IF;
   OPEN pReport FOR lsql USING pPostcode, pAge;
END;
```
Remarque: Le nombre et la position des variables de liaison doit être connu au moment de la compilation, c'est pourquoi j'utilise souvent la construction ci-dessus (en ajoutant le paramètre à sa position même si elle n'est pas utilisée). L'ajout d'une tautologie (comme dans AND :P1 IS NULL) à une question qui n'affecte pas les expliquer son plan.

+1: les variables de CONTEXTE sont plus accommodante dans cette situation
Poneys: absolument
+1: Certainement. Ne sais pas ce que je pensais...
Comment le faire sans l'AIDE de la clause de lier les variables (par exemple: dans le cas de paramètres optionnels)? J'ai lu sur DBMS_SQL.BIND_VARIABLE, mais quand j'essaie de l'utiliser, tout semble avoir besoin d'être réécrit. Savez-vous comment faire cela?
Avec EXECUTE IMMEDIATE ou OPEN ... FOR, le nombre de variables et de leur position doivent être connus au moment de la compilation, afin d'utiliser les paramètres facultatifs, vous devez utiliser une solution de contournement, comme illustré dans cet exemple. Vous pouvez utiliser DBMS_SQL pour les requêtes dynamiques sans cette limitation, voir par exemple Ce AFIN de répondre. Vous ne pouvez pas mélanger régulièrement SQL dynamique (EXECUTE IMMEDIATE) avec DBMS_SQL que la syntaxe sont très différents.

OriginalL'auteur Vincent Malgrat
5

Vous ne pouvez pas affecter un refcursor grâce à l'utilisation d'exécution immédiate.

Vous aurez à construire le code SQL dans une chaîne, puis ouvrir.
```
sql_str := 'SELECT * FROM...';
open pReport for sql_str;
```
Techniquement, si le SELECT retourne un curseur de type de données, vous pouvez sélectionner dans un ref cursor (exécution immédiate ou une sql statique). Mais vous avez raison de cette sélection est de retourner une valeur d'id, pas un ref cursor.

OriginalL'auteur RC.

Utilisation de l'OUVRIR POUR la syntaxe et de lier les variables.

procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
                            ,pReport out SYS_REFCURSOR) 
is
  lsql  varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
      ||' where c.id = ccf.carer_id (+)'
      ||' AND cf.id (+) = ccf.cared_for_id';

begin

 if pPostcode is not null and pAge <= 0 then
    lsql := lsql||' AND c.postcode like upper(''%''||:1||''%'')';
    open pReport for lsql using pPostcode;
  elsif pPostcode is null and pAge > 0 then 
     lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1';
    open pReport for lsql using pAge;
  elsif pPostcode is not null and pAge > 0 then
     lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1'
                  ||' AND c.postcode like upper(''%''||:2||''%'')';
    open pReport for lsql using pAge, pPostcode;
  end if;

end all_carers_param_dy;
/

SQL dynamique est dur, difficile à comprendre et difficile à obtenir le droit. L'une des zones difficiles est de la manipulation de la répétition. C'est une bonne idée de déclarer les articles du bolierplate comme des constantes. Notez également que nous pouvons diviser les grandes chaînes de caractères sur plusieurs lignes sans avoir à les concaténer avec '||'. Cela réduit le temps de maintenance.

create or replace procedure all_carers_param_dy 
    (pPostcode in carer.postcode%type
      , pAge Number 
      , pReport out SYS_REFCURSOR)  
is 
  lsql varchar2(500) ;

  root_string constant varchar2(500) :='SELECT c.id FROM carer c
                                , cared_for cf,carer_cared_for ccf   
                      where c.id = ccf.carer_id (+)  
                      and cf.id (+) = ccf.cared_for_id'; 
  pc_string constant varchar2(256) := 
      ' AND c.postcode like upper(''%''||:pc||''%'')';
  age_string constant varchar2(256) := 
      ' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :age';
begin 

 if pPostcode is not null and pAge <= 0 then 
    lsql := root_string || pc_string; 
    open pReport for lsql using pPostcode; 

  elsif pPostcode is null and pAge > 0 then  
     lsql := root_string || age_string; 
    open pReport for lsql using pAge; 

  elsif pPostcode is not null and pAge > 0 then 
     lsql := root_string || age_string 
                         || pc_string; 
    open pReport for lsql using pAge, pPostcode; 

  end if; 
end all_carers_param_dy; 
/

OriginalL'auteur APC

Vous devez vous connecter pour publier un commentaire.