以前写过的动态sql例子

最新推荐文章于 2024-09-14 18:42:05 发布

zw_82

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量901

点赞数

文章标签： sql login insert integer date

本文链接：https://blog.csdn.net/zw_82/article/details/1297204

版权

create or replace procedure zhouwu_day2month
as
    select_cur integer;
    insert_cur integer;
    day_i      integer;
    month_i    integer;
    ret_i      integer;
    login_name     zhouwu_day_1.login_name%type;
    login_ip       zhouwu_day_1.login_ip%type;
    lab_ip         zhouwu_day_1.lab_ip%type;
    time_duration zhouwu_day_1.time_duration%type;
begin
    select_cur:=dbms_sql.open_cursor;
    insert_cur:=dbms_sql.open_cursor;
    day_i:=to_number(to_char(sysdate-1,'dd'));
    month_i:=to_number(to_char(sysdate-1,'mm'));
      dbms_sql.parse(select_cur,'select sum(time_duration),login_name,login_ip,lab_ip from zhouwu_day_'||day_i||' where logout_date between trunc(sysdate-1,''dd'') and trunc(sysdate,''dd'') group by login_name,login_ip,lab_ip',dbms_sql.native);
      dbms_sql.define_column(select_cur,1,time_duration);
      dbms_sql.define_column(select_cur,2,login_name,10);
      dbms_sql.define_column(select_cur,3,login_ip,32);
      dbms_sql.define_column(select_cur,4,lab_ip,32);
      ret_i:=dbms_sql.execute(select_cur);
      loop
          if(dbms_sql.fetch_rows(select_cur)>0) then
          dbms_sql.column_value(select_cur,1,time_duration);
          dbms_sql.column_value(select_cur,2,login_name);
          dbms_sql.column_value(select_cur,3,login_ip);
          dbms_sql.column_value(select_cur,4,lab_ip);
     dbms_output.put_line('begin to parse insert_cur!');
          dbms_sql.parse(insert_cur,'insert into zhouwu_month_'||month_i||'(login_name,login_ip,logout_date,lab_ip,time_duration) values(:login_name,:login_ip,sysdate,:lab_ip,:time_duration)',dbms_sql.native);
      dbms_output.put_line('insert successful successful successful !');
          dbms_sql.bind_variable(insert_cur,':login_name',login_name);
          dbms_sql.bind_variable(insert_cur,':login_ip',login_ip);
          dbms_sql.bind_variable(insert_cur,':lab_ip',lab_ip);
          dbms_sql.bind_variable(insert_cur,':time_duration',time_duration);
          ret_i:=dbms_sql.execute(insert_cur);
            else
                exit;
          end if;
      end loop;
end zhouwu_day2month;