oracle集合类型应用举例

最新推荐文章于 2024-08-07 06:48:08 发布

tomtomabc

最新推荐文章于 2024-08-07 06:48:08 发布

阅读量171

点赞数

CC 4.0 BY-SA版权

文章标签： Oracle

本文链接：https://blog.youkuaiyun.com/tomtomabc/article/details/83612192

本文介绍了一个PL/SQL包的实现，该包包括了不同类型的过程，用于从表中读取数据并批量插入到另一个表。文章详细展示了如何使用游标循环遍历记录集，以及如何利用游标获取单条记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--condition--
create table t1(id int);
create table t2(id int);
create table t3(id int);

create or replace package aa_test is

-- Author : tom
-- Created : 2010-5-10 17:04:24
-- Purpose :

type tom_re is record(
id t1.id%type);

type tom_insert_tab is table of tom_re index by binary_integer;

tom_insert_array tom_insert_tab;

procedure tom_insert(iv_tom_insert_array in tom_re);

procedure tom_print;

procedure tom_print1;

procedure tom_print2;

procedure tom_print3;

end aa_test;

create or replace package body aa_test is

procedure tom_insert(iv_tom_insert_array in tom_re) is
begin
insert into t3 (id) values (iv_tom_insert_array.id);
end tom_insert;

procedure tom_print is
cursor cur_tom is
select * from t1;
begin
open cur_tom;
loop
fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
dbms_output.put_line('----' || tom_insert_array(i).id);
end loop;

exit when cur_tom%notfound;

end loop;

close cur_tom;
dbms_output.put_line('----' || tom_insert_array.count);
tom_insert_array.delete; --清空数组--
dbms_output.put_line('----' || tom_insert_array.count);
end tom_print;

procedure tom_print1 is
cursor cur_tom is
select * from t1;
begin
open cur_tom;

fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
dbms_output.put_line('----' || tom_insert_array(i).id);
end loop;

close cur_tom;
end tom_print1;

procedure tom_print2 is
cursor cur_tom is
select * from t1;
v_id integer;
begin
open cur_tom;
loop
fetch cur_tom
into v_id; --循环多遍--
exit when cur_tom%notfound;
dbms_output.put_line('--v_id--' || v_id); --这种情况需要放exit后面--
end loop;

close cur_tom;
end tom_print2;

procedure tom_print3 is
cursor cur_tom is
select * from t1;
begin
open cur_tom;

fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
tom_insert(tom_insert_array(i));
dbms_output.put_line('--insert times:--'|| i);
end loop;
close cur_tom;

commit;
end tom_print3;

end aa_test;