pl/sql中所有的变量必须事先声明好吗
如果我想在某个循环体内动态的声明变量
应该怎么弄呢
比如这个问题怎么解决
......
select * bulk collect
into n_ac02
from ac02
where aac001 = v_aac001
and aac047 = v_aac047
and aae041 = v_aae041
order by aac047;
n_row := n_ac02.first;
while (n_row is not null) loop
v_aac001_||n_row ac02.aac001%type;
insert into dk97
(bdk001, bdk002, bdk003, bdk004)
values
(n_ac02(n_row).aac001,
n_ac02(n_row).aac047,
n_ac02(n_row).aae041,
'重复补增历史参保记录');
n_row :=n_ac02.next(n_row);
end loop;
......v_aac001_||n_row ac02.aac001%type;
这里应该怎么改
如果我想在某个循环体内动态的声明变量
应该怎么弄呢
比如这个问题怎么解决
......
select * bulk collect
into n_ac02
from ac02
where aac001 = v_aac001
and aac047 = v_aac047
and aae041 = v_aae041
order by aac047;
n_row := n_ac02.first;
while (n_row is not null) loop
v_aac001_||n_row ac02.aac001%type;
insert into dk97
(bdk001, bdk002, bdk003, bdk004)
values
(n_ac02(n_row).aac001,
n_ac02(n_row).aac047,
n_ac02(n_row).aae041,
'重复补增历史参保记录');
n_row :=n_ac02.next(n_row);
end loop;
......v_aac001_||n_row ac02.aac001%type;
这里应该怎么改
for(i=0,i<6,i++)
{
String temp+i=i+'test';
}
那pl/sql中一定也可以实现这种做法
那应该怎么做
str varchar2(100) := '';
begin
for i in 1..6 loop
dbms_output.put_line('the line num is :' || to_char(i));
str:= str || to_char(i);
end loop;
dbms_output.put_line('result=' || str);
end;--结果
the line num is :1
the line num is :2
the line num is :3
the line num is :4
the line num is :5
the line num is :6
result=123456
select aac001,aac047,aae041,'重复补增历史参保记录' from
(select * frmo ac02
where aac001 = v_aac001
and aac047 = v_aac047
and aae041 = v_aae041
order by aac047);
create table t_test_dk97 (
bdk001 varchar2(50),
bdk002 varchar2(50),
bdk003 varchar2(50),
bdk004 varchar2(50)
)
declare
strSql varchar2(1000) := '';
begin
--使用隐性游标 则不需要声明任何变量
--没有具体的表 我用自己的表格进行的测试
--for RecordList in (select * from ac02 where aac001 = v_aac001 and aac047 = v_aac047 and aae041 = v_aae041 order by aac047) loop
-- strSql := 'insert into dk97 (bdk001, bdk002, bdk003, bdk004) values (''' || RecordList.Indcode ||
-- ''',''' || RecordList.Indcnname || ''',''' ||
-- RecordList.Username || ''',''重复补增历史参保记录'')';
for RecordList in (select * from hi_indicators) loop
strSql := 'insert into t_test_dk97 values (''' || RecordList.Indcode ||
''',''' || RecordList.Indcnname || ''',''' ||
RecordList.Username || ''',''重复补增历史参保记录'')';
execute immediate strSql;
--逐行提交
--commit;
end loop;
--只提交一次
commit;
end;--
select * from t_test_dk97
--结果:只给出头几行1 I00000048 门诊人次费用分析 1 重复补增历史参保记录
2 I00000047 门诊工作效率分析 2 重复补增历史参保记录
3 I00000049 住院收入情况 1 重复补增历史参保记录
4 I00000050 入院人数分析 1 重复补增历史参保记录--删除测试表格
drop table t_test_dk97;