动态 SQL 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21090673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic SQL LOOP
提问by Novice
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.
动态 SQL 不是我的朋友，基本上的想法是我可以使用带有“p_in_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?
如果有人告诉我我做错了什么，会很感激吗？
回答by Mikhail
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:
问题是你没有遍历游标 - 没有 fetch 语句或类似的东西，所以，基本上，你有一个无限循环。为了避免这种情况，您需要执行以下操作：
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;
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:
但是，正如您所看到的，要做到这一点，您需要知道要查询的表，因此这不是通用解决方案。也许有一种解决方法，但我建议您使用更简单有效的方法，例如使用 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;
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:
好的，我考虑了如何使用您的方式实现这一点，这就是我最终得到的 - 只需从您的表中获取 ROWNUM，每个表都有它并且您知道它的类型 - NUMBER。所以这个程序在一般情况下可以工作：
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;
