关于数据采集的PL/SQL

Question

功能 每小时向 表 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);
        dbms_sql.close_cursor(cid2);
end;
--exec bidr_to_detail('2006-01-08:11');

Answer 1

怎么写这么麻烦，直接用dbms_sql执行下面语句不就行了么'insert into windy_detail_' ¦ ¦to_char(（to_date(hh,'yyyy_mm_dd:hh'),'yyyy_mm_dd')） ||
'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'')'

Answer 2

写丢了点，补上
'insert into windy_detail_' ¦ ¦to_char(（to_date(hh,'yyyy_mm_dd:hh'),'yyyy_mm_dd')） ¦ ¦ 
'（id ,login_ip,login_date,login_name,login_ip,time_duration,loginout_date）'
'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'')'