Les Paramètres de liaison Oracle SQL Dynamique

J'ai une procédure stockée qui accepte plusieurs paramètres (pName, pHeight, pTeam)

J'ai la requête comme ceci:

SQLQuery VARCHAR2(6000);
TestCursor T_CURSOR;

SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';


-- Build the query based on the parameters passed.
IF pName IS NOT NULL
  SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
END IF;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = :pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
END IF;


OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;

Si j'exécute la procédure de passage de tous les paramètres, il fonctionne correctement.

Mais si je ne passe que d'un ou deux paramètres, puis les erreurs de procédure:

ORA-01006: bind variable does not exist

Comment puis-je lier sélectivement la variable avec les paramètres en fonction de l'endroit où la valeur du paramètre a été utilisé? Par exemple, si seulement pName était passé, puis je tiens seulement à l'exécution de la requête:

OPEN TestCursor FOR SQLQuery USING pName;

Ou si les deux pName et pTeam était passé, puis:

OPEN TestCursor FOR SQLQuery USING pName, pTeam;

Espère que quelqu'un peut jeter plus de façons de résoudre ce problème. Merci.

Edit:
Je pourrais effectivement utiliser les éléments suivants:

-- Construire la requête basée sur les paramètres passés.
SI pName n'EST PAS NULL
SQLQuery := SQLQuery | | " ET le Nom COMME "' || pName || "' ';
FIN SI;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE ''' || pTeam || ''' ';
END IF;


OPEN TestCursor FOR SQLQuery;

Mais ce serait TRÈS vulnérable à l'Injection SQL...

OriginalL'auteur Batuta | 2012-06-12

Ce n'est pas très élégant mais cela signifie que vous pouvez toujours fournir tous les trois variables de liaison, même si certains d'entre eux sont nulles. N'ajouter le supplément WHERE clauses en cas de besoin.

(J'ai essayé de formater le SQL dynamique pour le rendre plus lisible, vous pouvez simplement fournir en tant que chaîne longue).

FUNCTION myFunc (
   pName   IN VARCHAR2,
   pHeight IN VARCHAR2,
   pTeam   IN VARCHAR2
)
   RETURN T_CURSOR
IS
   -- Local Variables
   SQLQuery   VARCHAR2(6000);
   TestCursor T_CURSOR;
BEGIN
   -- Build SQL query
   SQLQuery := 'WITH t_binds '||
                ' AS (SELECT :v_name AS bv_name, '||
                           ' :v_height AS bv_height, '||
                           ' :v_team AS bv_team '||
                      ' FROM dual) '||
               ' SELECT id, '||
                      ' name, '||
                      ' height, '||
                      ' team '||
                 ' FROM MyTable, '||
                      ' t_binds '||
                ' WHERE id IS NOT NULL';

   -- Build the query WHERE clause based on the parameters passed.
   IF pName IS NOT NULL
   THEN
     SQLQuery := SQLQuery || ' AND Name LIKE bv_name ';
   END IF;

   IF pHeight > 0
   THEN
     SQLQuery := SQLQuery || ' AND Height = bv_height ';
   END IF;

   IF pTeam IS NOT NULL
   THEN
     SQLQuery := SQLQuery || ' AND Team LIKE bv_team ';
   END IF;

   OPEN TestCursor 
    FOR SQLQuery 
  USING pName, 
        pHeight, 
        pTeam;

   -- Return the cursor
   RETURN TestCursor;
END myFunc;

Je ne suis pas en face d'une station de travail avec une DB access donc je ne peux pas tester la fonction, mais il devrait être proche (s'il vous plaît pardonnez les erreurs de syntaxe, ça a été une longue journée!)

Espère que ça aide...

Où était t_binds déclaré? Ou dois-je besoin de le déclarer quelque part?
Il est déclaré dans le WITH clause dans l'instruction SQL, c'est un mannequin table pour contenir les variables de liaison. Voir ici: orafaq.com/node/1879
Essayé cela, mais encore, dit-ORA-01008: pas toutes les variables liées
Vous aurez à me donner plus d'informations que "Essayé cela, mais encore, dit-ORA-01008: pas toutes les variables liées" si vous voulez aider à la résoudre. J'utilise cette méthode à moi-même pour presque exactement la même situation et il fonctionne très bien, la syntaxe pourrait être un peu comme ma réponse explique, mais le principe de base est saine.
J'ai essentiellement exécuté le proc que vous avez fourni... et il dit que les variables ne sont pas liés.

OriginalL'auteur Ollie

Vous pouvez utiliser le DBMS_SQL paquet. Ce qui fournit un autre moyen d'exécuter du SQL dynamique. C'est peut-être un peu plus compliqué à utiliser, mais il peut être plus souple, surtout avec un nombre variable de lier des paramètres.

Voici comment vous pouvez l'utiliser (attention: je n'ai pas testé):

FUNCTION player_search (
pName        IN VARCHAR2,
pHeight      IN NUMBER,
pTeam        IN VARCHAR2
) RETURN SYS_REFCURSOR
IS 
cursor_name   INTEGER;
ignore        INTEGER;
id_var        MyTable.ID%TYPE;
name_var      MyTable.Name%TYPE;
height_var    MyTable.Height%TYPE;
team_var      MyTable.Team%TYPE;
BEGIN
-- Put together SQLQuery here...
-- Open the cursor and parse the query         
cursor_name := DBMS_SQL.OPEN_CURSOR; 
DBMS_SQL.PARSE(cursor_name, SQLQuery, DBMS_SQL.NATIVE); 
-- Define the columns that the query returns.
-- (The last number for columns 2 and 4 is the size of the
-- VARCHAR2 columns.  Feel free to change them.)
DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, id_var); 
DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, name_var, 30); 
DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, height_var); 
DBMS_SQL.DEFINE_COLUMN(cursor_name, 4, team_var, 30); 
-- Add bind variables depending on whether they were added to
-- the query.
IF pName IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pName', pName);
END IF;
IF pHeight > 0 THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pHeight', pHeight);
END IF;
IF pTeam IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pTeam', pTeam);
END IF;
-- Run the query.
-- (The return value of DBMS_SQL.EXECUTE for SELECT queries is undefined,
-- so we must ignore it.)
ignore := DBMS_SQL.EXECUTE(cursor_name); 
-- Convert the DBMS_SQL cursor into a PL/SQL REF CURSOR.
RETURN DBMS_SQL.TO_REFCURSOR(cursor_name);
EXCEPTION 
WHEN OTHERS THEN 
-- Ensure that the cursor is closed.
IF DBMS_SQL.IS_OPEN(cursor_name) THEN 
DBMS_SQL.CLOSE_CURSOR(cursor_name); 
END IF; 
RAISE; 
END;

(Note: DBMS_SQL.TO_REFCURSOR est nouveau dans Oracle 11g.)

+1, j'aime DBMS_SQL que c'est plus "pris en charge", mais un peu plus compliqué que la méthode que j'ai suggéré.

OriginalL'auteur Luke Woodward

L'approche que j'utilise est à inclure dans le SQL dynamique un d'AUTRE cas, les membres de l'inverse de la SI. Votre code tests pName n'est pas nulle, donc, je voudrais ajouter une clause à la requête générée par des essais que pName EST Null. De cette façon, vous pouvez passer les mêmes paramètres de tous les temps, sans affecter les résultats de la requête.

SQLQuery VARCHAR2(6000);
TestCursor T_CURSOR;
SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';
-- Build the query based on the parameters passed.
IF pName IS NOT NULL  
SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
ELSE 
SQLQuery := SQLQuery || 'AND :pName IS NULL';
END IF;
IF pHeight IS > 0
SQLQuery := SQLQuery || 'AND Height = :pHeight ';
ELSE
SQLQuery := SQLQuery || 'AND :pHeight <=0 ';
END IF;
IF pTeam IS NOT NULL
SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
ELSE
SQLQuery := SQLQuery || 'AND :pTeam IS NULL';
END IF;
OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;

OriginalL'auteur Donogst

Comment sur

SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';
SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
SQLQuery := SQLQuery || 'AND (Height = :pHeight OR :pHeight = 0)';
OPEN TestCursor FOR SQLQuery USING nvl(pName, '%'), nvl(pTeam, '%'), nvl(pHeight, 0), nvl(pHeight, 0);

like '%' n'affecte pas la valeur null. donc si l'un de ces champs est nulle, u obtiendrez faux

OriginalL'auteur archimede

Vous devez vous connecter pour publier un commentaire.