• <legend id='OE68I'><style id='OE68I'><dir id='OE68I'><q id='OE68I'></q></dir></style></legend>
    • <bdo id='OE68I'></bdo><ul id='OE68I'></ul>

    <small id='OE68I'></small><noframes id='OE68I'>

    <tfoot id='OE68I'></tfoot>
      <i id='OE68I'><tr id='OE68I'><dt id='OE68I'><q id='OE68I'><span id='OE68I'><b id='OE68I'><form id='OE68I'><ins id='OE68I'></ins><ul id='OE68I'></ul><sub id='OE68I'></sub></form><legend id='OE68I'></legend><bdo id='OE68I'><pre id='OE68I'><center id='OE68I'></center></pre></bdo></b><th id='OE68I'></th></span></q></dt></tr></i><div id='OE68I'><tfoot id='OE68I'></tfoot><dl id='OE68I'><fieldset id='OE68I'></fieldset></dl></div>

      1. 动态 SQL 循环

        Dynamic SQL LOOP(动态 SQL 循环)

          <tbody id='sHfjd'></tbody>

          <legend id='sHfjd'><style id='sHfjd'><dir id='sHfjd'><q id='sHfjd'></q></dir></style></legend>

            <small id='sHfjd'></small><noframes id='sHfjd'>

              • <bdo id='sHfjd'></bdo><ul id='sHfjd'></ul>

                1. <i id='sHfjd'><tr id='sHfjd'><dt id='sHfjd'><q id='sHfjd'><span id='sHfjd'><b id='sHfjd'><form id='sHfjd'><ins id='sHfjd'></ins><ul id='sHfjd'></ul><sub id='sHfjd'></sub></form><legend id='sHfjd'></legend><bdo id='sHfjd'><pre id='sHfjd'><center id='sHfjd'></center></pre></bdo></b><th id='sHfjd'></th></span></q></dt></tr></i><div id='sHfjd'><tfoot id='sHfjd'></tfoot><dl id='sHfjd'><fieldset id='sHfjd'></fieldset></dl></div>

                  <tfoot id='sHfjd'></tfoot>
                  本文介绍了动态 SQL 循环的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  Dynamic 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 循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                  How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                  Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                  Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                  Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                  Reset Sequence in oracle 11g(oracle 11g 中的重置序列)
                    <bdo id='5GNcR'></bdo><ul id='5GNcR'></ul>
                    <legend id='5GNcR'><style id='5GNcR'><dir id='5GNcR'><q id='5GNcR'></q></dir></style></legend>
                  • <tfoot id='5GNcR'></tfoot>

                      <i id='5GNcR'><tr id='5GNcR'><dt id='5GNcR'><q id='5GNcR'><span id='5GNcR'><b id='5GNcR'><form id='5GNcR'><ins id='5GNcR'></ins><ul id='5GNcR'></ul><sub id='5GNcR'></sub></form><legend id='5GNcR'></legend><bdo id='5GNcR'><pre id='5GNcR'><center id='5GNcR'></center></pre></bdo></b><th id='5GNcR'></th></span></q></dt></tr></i><div id='5GNcR'><tfoot id='5GNcR'></tfoot><dl id='5GNcR'><fieldset id='5GNcR'></fieldset></dl></div>

                      <small id='5GNcR'></small><noframes id='5GNcR'>

                            <tbody id='5GNcR'></tbody>