功能 每小时向 表 BIDR查找当前时间前一个小时的数据 放入 windy_detail_yyyy_mm_dd 表中
下面是 BIDR的表结构
create table "BE0803"."BIDR"(
"ID" NUMBER(10) primary key,
"LAB_IP" VARCHAR2(255),
"LOGIN_DATE" TIMESTAMP(6),
"LOGIN_NAME" VARCHAR2(255),
"LOGIN_IP" VARCHAR2(255),
"TIME_DURATION" NUMBER(19),
"LOGINOUT_DATE" TIMESTAMP(6),
);
windy_detail_yyyy_mm_dd 和 bidr一样
下面是我写的 PROCEDURE
创建成功 运行也无报异常 就是找不到数据 不知道那里出错 各位帮我看看 （下面是测试代码所以时间字定义）
create or replace procedure bidr_to_detail( hh varchar2)
as
id bidr.ID%type;
login_name bidr.LOGIN_NAME%type;
login_ip bidr.LOGIN_IP%type;
login_date bidr.LOGIN_DATE%type;
loginout_date bidr.LOGINOUT_DATE%type;
lab_ip bidr.LAB_IP%type;
time_duration bidr.TIME_DURATION%type;
num number;
cid1 integer;
cid2 integer;
res1 integer;
res2 integer;
t1 varchar2(25);
t2 varchar2(25);
selectsql varchar2(255);
isnertsql varchar2(255);
d1 date;
d2 varchar(25);
begin
t1:='hh';
t2:='yyyy_mm_dd:hh';
cid1:=dbms_sql.open_cursor;
d1:=to_date(hh,'yyyy_mm_dd:hh');
selectsql:='select id ,login_ip,login_date,login_name,login_ip,time_duration,loginout_date from bidr where trunc(loginout_date,''hh'')=to_date('''||hh||''',''yyyy_mm_dd:hh'')';
dbms_sql.parse(cid1,selectsql,dbms_sql.native);
dbms_sql.define_column(cid1,1,id);
dbms_sql.define_column(cid1,2,lab_ip,255);
dbms_sql.define_column(cid1,3,login_date);
dbms_sql.define_column(cid1,4,login_name,255);
dbms_sql.define_column(cid1,5,login_ip,255);
dbms_sql.define_column(cid1,6,time_duration);
dbms_sql.define_column(cid1,7,loginout_date);
res1:=dbms_sql.execute(cid1);
cid2:=dbms_sql.open_cursor;
d2:=to_char(d1,'yyyy_mm_dd');
isnertsql:='insert into windy_detail_'||d2||' values(:id,:lab_ip,:login_date,:login_name,:login_ip,:time_duration,:loginout_date)';
dbms_sql.parse(cid2,isnertsql,dbms_sql.native);
loop
if (dbms_sql.fetch_rows(cid1))>0 then
dbms_sql.column_value(cid1,1,id);
dbms_sql.column_value(cid1,2,lab_ip);
dbms_sql.column_value(cid1,3,login_date);
dbms_sql.column_value(cid1,4,login_name);
dbms_sql.column_value(cid1,5,login_ip);
dbms_sql.column_value(cid1,6,time_duration);
dbms_sql.column_value(cid1,7,loginout_date);
dbms_sql.bind_variable(cid2,':id',id);
dbms_sql.bind_variable(cid2,':lab_ip',lab_ip);
dbms_sql.bind_variable(cid2,':login_date',login_date);
dbms_sql.bind_variable(cid2,':login_name',login_name);
dbms_sql.bind_variable(cid2,':login_ip',login_ip);
dbms_sql.bind_variable(cid2,':time_duration',time_duration);
dbms_sql.bind_variable(cid2,':loginout_date',loginout_date);
res2:=dbms_sql.execute(cid2);
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cid1);