动态SQL循环 [英] Dynamic SQL LOOP

问题描述

动态SQL不是我的朋友，基本上，我的想法是我可以使用带有"p_in_table"参数的过程来获取表中包含的行数.

Dynamic SQL is not my friend, basically the idea is that I can use the procedure with the "p_in_table" paramter to get the number of rows contained in the table.

CREATE OR REPLACE PROCEDURE how_many_rows(p_in_table VARCHAR2)
IS
  TYPE cur_cur IS REF CURSOR;
  v_cur_cur  cur_cur;
  v_rowcount NUMBER(28);
  v_cur_txt  VARCHAR2(299);    
  BEGIN
    v_cur_txt := 'SELECT * FROM ' || p_in_table;

    OPEN v_cur_cur FOR v_cur_txt;

    LOOP
      v_rowcount := v_cur_cur%ROWCOUNT;
      EXIT WHEN v_cur_cur%NOTFOUND;
    END LOOP;

    CLOSE v_cur_cur;

    dbms_output.put_line(v_rowcount);
  END;

如果有人告诉我我在做什么错，这会很高兴吗?

Would preciate it if someone would tell me what am I doing wrong?

推荐答案

问题是您没有遍历游标-没有fetch语句或类似的东西，因此，基本上，您有一个无限循环.为避免这种情况，您需要执行以下操作:

The problem is that you not iterating through cursor - no fetch statement or something like that, so, basically, you have an infinite loop. To avoid this you need to do something like this:

CREATE OR REPLACE PROCEDURE how_many_rows
   (p_in_table VARCHAR2) IS
   TYPE cur_cur IS REF CURSOR;
   v_cur_cur cur_cur;
   v_rowcount NUMBER(28);
   v_cur_txt VARCHAR2(299);
   v_row SOME_TABLE%ROWTYPE; --add row variable
BEGIN
   v_cur_txt := 'SELECT * FROM '|| p_in_table;

OPEN v_cur_cur FOR v_cur_txt;
   LOOP
      v_rowcount := v_cur_cur%ROWCOUNT;
      FETCH v_cur_cur INTO v_row; --fetch a row in it
         EXIT WHEN v_cur_cur%NOTFOUND;
   END LOOP;
CLOSE v_cur_cur;

DBMS_OUTPUT.PUT_LINE(v_rowcount);
END;

但是，正如您所看到的，要执行此操作，您需要知道要查询的表，因此这不是一般的解决方案.也许有一个解决方法，但是我建议您使用更简单有效的方法，例如使用EXECUTE IMMEDIATE:

But, as you can see, to do this you need to know, what table you're quering, so this is not general solution. Maybe there is a workaround for this, but i suggest, you use more simple and efficient approach, for example with EXECUTE IMMEDIATE:

CREATE OR REPLACE PROCEDURE HOW_MANY_ROWS(p_in_table VARCHAR2)
       IS
v_tmp NUMBER;
BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || p_in_table INTO v_tmp;
DBMS_OUTPUT.PUT_LINE(v_tmp);

END;

---

好吧，我对如何使用您的方式实现这一点进行了思考，这就是我的最终结果-只需从您的表中获取ROWNUM，每个表都有它，并且您知道它的类型-NUMBER.因此，此过程通常适用于以下情况:

---

Ok, I gave a thought on how to achieve this using your way, and here is what i've ended up with - just fetch ROWNUM from your table, every table has it and you know it's type - NUMBER. So this procedure will work in general case:

CREATE OR REPLACE PROCEDURE how_many_rows
   (p_in_table VARCHAR2) IS
   TYPE cur_cur IS REF CURSOR;
   v_cur_cur cur_cur;
   v_rowcount NUMBER(28);
   v_cur_txt VARCHAR2(299);
   v_row NUMBER; --add rownum variable
BEGIN
   v_cur_txt := 'SELECT ROWNUM FROM '|| p_in_table; --select only rownum from target table

OPEN v_cur_cur FOR v_cur_txt;
   LOOP
      v_rowcount := v_cur_cur%ROWCOUNT;
      FETCH v_cur_cur INTO v_row; --fetch rownum in it
         EXIT WHEN v_cur_cur%NOTFOUND;
   END LOOP;
CLOSE v_cur_cur;

DBMS_OUTPUT.PUT_LINE(v_rowcount);
END;

这篇关于动态SQL循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！