PL/SQL中不通过游标把值添加到集合中

最新推荐文章于 2025-08-03 11:03:14 发布

最新推荐文章于 2025-08-03 11:03:14 发布 · 96 阅读

本文介绍如何使用PL/SQL将多行单列或多列数据添加到不同的集合中，包括通过RowNum索引表、RowType记录表及自定义二维表的方式，并提供具体实现代码示例。

1.把多行单列的值添加到索引表中(通过RowNum):例如
declare
  type dname_table_type is table of scott.dept.dname%type
  index by binary_integer;
  dname_table dname_table_type;
  dcount number(2);
begin
  select count(*) into dcount from scott.dept;
  for i in 1..dcount loop
      select t1.dname into dname_table(i) from 
(select rownum rn,t.* from (select * from scott.dept)t)t1
      where t1.rn=i;
  end loop;
  
  for i in 1..dname_table.count loop
      dbms_output.put_line(dname_table(i));
  end loop;
end;
2.PL/SQL记录表把多行多列的值添加到集合中(RowType):例如:
 declare 
  type dname_table_type is table of scott.dept%rowtype
  index by binary_integer; 
  dname_table dname_table_type;
dcount number(2);
 begin 
  select count(*) into dcount from scott.dept;
  for i in 1..dcount loop 
        select t1.deptno,t1.dname,t1.loc into dname_table(i) from
  (select rownum rn,t.* from scott.dept t)t1 
where t1.rn=i; 
 end loop; 
for i in 1..dname_table.count loop
  dbms_output.put_line(dname_table(i).dname||' '||dname_table(i).deptno); 
end loop; 
 end;
3.PL/SQL记录表把多行多列的值添加到集合表中(ReCord),自定义二维表:例如:
 declare 
type dept_record_type is record( 
 deptno scott.dept.deptno%type,
   dname scott.dept.dname%type,
   dloc scott.dept.loc%type );
  type dname_table_type is table of dept_record_type(数据类型) 
  index by binary_integer; 
  dname_table dname_table_type;
dcount number(2);
 begin 
  select count(*) into dcount from scott.dept;
  for i in 1..dcount loop 
        select t1.deptno,t1.dname,t1.loc into dname_table(i) from
  (select rownum rn,t.* from scott.dept t)t1 
where t1.rn=i; 
 end loop; 
for i in 1..dname_table.count loop
  dbms_output.put_line(dname_table(i).dname||' '||dname_table(i).deptno); 
end loop; 
 end;