oracle 遍历表 PL/SQL 中的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41048162/
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
Iterate through all rows in table PL/SQL
提问by Wont Provide
From table1 I would like to gather values from certain columns. First of all I have tried to copy one table to another but I stuck when tried to:
从 table1 我想从某些列收集值。首先，我尝试将一张表复制到另一张表，但在尝试时卡住了：
for row in row_count
for column in column_count
insert into table2 at (x,y) value from (row,column)
column++
end
row++
end
My first function to count how many rows is:
我计算行数的第一个函数是：
create or replace FUNCTION func_count_rows(table_name IN varchar2,
debug boolean default false)
RETURN number IS
total number(2) := 0;
BEGIN
IF debug = true THEN
DBMS_OUTPUT.put('Function count rows: ');
DBMS_OUTPUT.PUT_LINE('select count(*) from ' || table_name || ';');
DBMS_OUTPUT.put('Returns: ');
DBMS_OUTPUT.PUT_LINE('');
END IF;
execute immediate 'select count(*) from ' || table_name into total;
RETURN total;
END;
Then my procedure to first print values but I stuck here:
然后我的程序首先打印值，但我卡在这里：
create or replace procedure gather_values (rows_quantity in VARCHAR2,
column_count in VARCHAR2,
debug boolean default false
)
is begin
select
FOR i IN 1..rows_quantity LOOP
DBMS_OUTPUT.PUT_LINE('#### ROW 1 ####');
FOR i IN 1..94 LOOP
END LOOP;
END LOOP;
end;
I don't know how to get column quantity and value from exact (x,y) of table.
我不知道如何从表的精确 (x,y) 中获取列数量和值。
Could you kindly help me? Thank you.
你能帮我吗？谢谢你。
I have forget to tell that I'm using oracle SQL enviroment.
我忘了告诉我我正在使用 oracle SQL 环境。
回答by Dmitriy
First of all, this has nothing in common with PL/SQL:
首先，这与 PL/SQL 没有任何共同之处：
for row in row_count
for column in column_count
insert into table2 at (x,y) value from (row,column)
column++
end
row++
end
See documentation here.
请参阅此处的文档。
To copy all rows from one table to another:
要将一个表中的所有行复制到另一个表：
insert into table2 (x,y)
select a, b
from table1;
It is a simple SQL query, it can be used as is or inside a PL/SQL procedure.
它是一个简单的 SQL 查询，可以按原样使用，也可以在 PL/SQL 过程中使用。
There is a lot of possibilities to iterate all rows of a table. The most simple:
迭代表的所有行有很多可能性。最简单的：
for i in (select column1, column2, ... from table1) loop
dbms_output.put_line(i.column1);
end loop;
Another ways:
另一种方式：
- Using cursors
- Using collections
- Using dynamic SQL and dbms_sql package
- 使用游标
- 使用集合
- 使用动态 SQL 和 dbms_sql 包
To count rows in a table, you can use SQL query:
要计算表中的行数，可以使用 SQL 查询：
select count(*)
from table1
or almost the same PL/SQL code (you don't need to use execute immediate):
或几乎相同的 PL/SQL 代码（您不需要使用execute immediate）：
declare
total number;
begin
select count(*)
into total
from table1;
dbms_output.put_line('count of rows: ' || total);
end;
/
But in any case you don't need to know, how many rows and columns a table contains, to iterate them. You need only to know, how to filter, which of them you want to iterate.
但在任何情况下，您都不需要知道一个表包含多少行和列来迭代它们。您只需要知道如何过滤，您想要迭代其中的哪些。
