现已知有一个表T2(id,code,date)需从T1表中取得相应数据。使用oracle的存储过程方法。
如果用sql语言实现大致逻辑是这样的:
insert into T2(id,code,date) select (t.id,t.code,t.date) from T1 t where t.date=to_date('2/23/2016 ','mm/dd/yyyy ');
通过日期查询T1中的数据加入到T2中。
而用存储过程的方法如下:
首先建立包说明:
create or replace package get_to_T1 is
procedure Query_T1;
procedure Insert_T2(
into_id in T1.ID%type,
into_code in T1.CODE%type%type,
into_date in T1.date%type
);
end get_to_T1 ;
然后编写包主题内容:
create or replace package get_to_T1 is
procedure Query_T1
is
m_system_date T1.date%type;
m_c_id T1.ID%type;
m_c_code T1.CODE%type;
num int;
--声明对象
cursor c_policy_main is --定义游标
select t.ID,t.CODE,t.date
from T1 t
where t.date =m_system_date;
--查询并数据
begin
m_system_date:=to_date('02/23/2016','MM/dd/yyyy');
--传入一个时间
for r_policy in c_policy_main loop
m_system_date:=r_policy.date;
m_c_id:=r_policy.id;
m_c_code:=r_policy.code;
--循环打印c_policy_main里的数据
select count(*) into num from T2 where id=m_c_id;
if num=0 then
Insert_T2(m_c_id,m_c_code,m_system_date);
end if;
--if判断查重
commit;
--提交操作
end loop;
end Query_T1;
procedure Insert_T2(
into_id in T1.ID%type,
into_code in T1.CODE%type%type,
into_date in T1.date%type
) is
--声明对象
begin
insert into T2(id,code,date)
values (into_id,into_code,into_date);
end Insert_T2;
end get_to_T1 ;