在SQL FOR-IN（SELECT）循环中使用字符串变量的值 [英] Use the value of a string variable in SQL FOR-IN (SELECT) loop

问题描述

我有一个SQL过程，应该执行一个FOR-IN（SELECT ..）循环，其中SELECT内容应该根据一些输入参数而变化。我的想法是将SELECT字符串存储到一个变量，然后尝试提取变量值在FOR-IN循环，但没有成功，到目前为止（早期有一个修复的SELECT语句使用，那是我想替换现在）。代码看起来如下

I have an SQL procedure, that should execute a FOR-IN (SELECT ..) loop, where the SELECT content should vary depending on some input parameters. My idea was to store the SELECT string into a variable, and then try to extract the variable value in the FOR-IN loop but without success so far (earlier there was a fix SELECT statement used there, that's what I am trying to replace now). The code looks about as follows

PROCEDURE run(p_boolean BOOLEAN)
IS
BEGIN
  DECLARE
    v_mystring VARCHAR(50);
  BEGIN
    IF p_boolean = TRUE
    THEN
      v_mystring := 'SELECT something...';
    ELSE
      v_mystring := 'SELECT something else...';
    END IF;

    FOR p_table_name IN (would-like-to-use-the-value-of-v_mystring-here-some-way)
    LOOP
      ...
    END LOOP;

  END;
END;

在SQL中相当新手，很可能会发生尝试使用字符串变量的整个概念这里的值是错误的。我浏览了一些教程，并尝试了一些其他的想法（例如光标），但没有结果。任何想法赞赏

Being quite novice in SQL, it might well happen that the entire concept of trying to use a string variable value here is wrong. I browsed through some tutorials and tried some other ideas (e.g. cursor), but no result. Any idea is appreciated

推荐答案

假设Oracle的PL / SQL，您可以打开 REFCURSOR 使用动态字符串并在LOOP中调用它。

Assuming Oracle's PL/SQL ,You can open a REFCURSOR using dynamic String and call it in a LOOP..

PROCEDURE run(p_boolean BOOLEAN)
IS
BEGIN
  DECLARE
    v_mystring VARCHAR(50);
    v_my_ref_cursor sys_refcursor;
  BEGIN
    IF p_boolean = TRUE
    THEN
      v_mystring := 'SELECT something...';
    ELSE
      v_mystring := 'SELECT something else...';
    END IF;

    OPEN v_my_ref_cursor FOR v_mystring;

    LOOP
      FETCH v_my_ref_cursor INTO your_variables/record
      EXIT WHEN v_my_ref_cursor%NOTFOUND;
        ..

    END LOOP;
    CLOSE v_my_ref_cursor;

  END;
END;

这篇关于在SQL FOR-IN（SELECT）循环中使用字符串变量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！