Utilisation des variables bind avec la clause dynamic SELECT INTO de PL / SQL
j'ai une question concernant l'endroit où les variables de bind peuvent être utilisées dans une déclaration SQL dynamique dans PL/SQL.
par exemple, je sais que c'est valable:
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
INTO v_num_of_employees
USING p_job;
RETURN v_num_of_employees;
END;
/
je me demandais si vous pouviez utiliser une variable bind dans une instruction select comme celle-ci ""
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) INTO :into_bind FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
USING out v_num_of_employees, p_job;
RETURN v_num_of_employees;
END;
/
Note j'ai utilisé un SELECT INTO statement comme chaîne dyamic et j'ai utilisé une variable bind dans la clause INTO.
je suis actuellement je voyage en ce moment et je n'aurai pas accès à mon ordinateur à la maison pendant quelques jours, mais ça me tracasse un peu. J'ai essayé de lire la référence PL/SQL mais ils n'ont pas d'exemple de select comme celui-ci.
Merci
5 réponses
Non vous ne pouvez pas utiliser les variables bind de cette façon. Dans votre deuxième exemple :into_bind dans v_query_str est juste un placeholder pour la valeur de la variable v_num_of_employees . Votre déclaration select into se transformera en quelque chose comme:
SELECT COUNT(*) INTO FROM emp_...
parce que la valeur de v_num_of_employees est null à EXECUTE IMMEDIATE .
Votre premier exemple présente la bonne façon de lier la valeur de retour d'une variable.
Modifier
l'affiche originale a modifié le deuxième bloc de code auquel je me réfère dans ma réponse pour utiliser OUT en mode paramètre pour v_num_of_employees au lieu du mode par défaut IN . Cette modification rend les deux exemples fonctionnellement équivalents.
à mon avis, Un bloc PL/SQL dynamique est quelque peu obscur. Bien qu'il soit très flexible, il est également difficile de l'accorder, difficile de déboguer et difficile de comprendre ce qui se passe. Mon vote va à votre première option,
EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;
utilise toutes les deux des variables de liaison, mais d'abord, pour moi, est plus redeable et accordable que l'option @jonearles.
mettez la déclaration select dans un bloc PL/SQL dynamique.
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'begin SELECT COUNT(*) INTO :into_bind FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job; end;';
EXECUTE IMMEDIATE v_query_str
USING out v_num_of_employees, p_job;
RETURN v_num_of_employees;
END;
/
peut être utilisée dans la requête SQL D'Oracle avec la clause" in".
fonctionne en 10g; Je ne connais pas d'autres versions.
Bind variable est varchar jusqu'à 4000 caractères.
exemple: variable de Liaison contenant une liste de valeurs séparées par des virgules, p.ex.
: bindvar = 1,2,3,4,5
select * from mytable
where myfield in
(
SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
FROM dual
CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
);
(même info que celle que j'ai postée ici: comment préciser dans la clause requête dynamique utilisant une variable? )
Sélectionnez Dans la fonctionnalité fonctionne uniquement pour les PL/SQL Bloc, lorsque vous utilisez Exécution immédiate , oracle interprète v_query_str comme une chaîne de Requête SQL de sorte que vous ne pouvez pas l'utiliser .obtenez mot-clé manquant Exception. dans l'exemple 2, nous utilisons begin end; il est donc devenu PL/sql block et son juridique.