对于存储过程中表名是变量的游标使用

最新推荐文章于 2022-10-23 11:26:22 发布

zxh603

最新推荐文章于 2022-10-23 11:26:22 发布

阅读量1.2k

点赞数 1

分类专栏： oracle 文章标签：存储 date exception table login file

本文链接：https://blog.csdn.net/zxh603/article/details/7683612

版权

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE PROCEDURE DAY_LOGIN
IS
TYPE cur_type IS REF CURSOR;
in_date VARCHAR2(40); --时间
y_date VARCHAR2(40);
t_date varchar2(40);
table_name varchar2(80);
stat_time VARCHAR2(40); --时间
end_time VARCHAR2(40); --时间
file_name varchar2(80);
my_cur cur_type;
ACC_PHONE varchar2(100);
ACC_AREACODE varchar2(100);
ACC_APPVERSION varchar2(100);
ACC_CREATETIME varchar2(100);
dyna_sql varchar2(1000);
testjiao_handle UTL_FILE.file_type;

BEGIN
in_date := TO_CHAR(SYSDATE-1,'yyyy-MM-dd');
y_date := TO_CHAR(SYSDATE-1,'yyyyMMdd');
t_date := TO_CHAR(SYSDATE,'yyyyMMdd');
stat_time := in_date || ' 00:00:00';
end_time := in_date || ' 23:59:59';
file_name := 'WAP_USER_LOGIN_LOG.' || y_date || '.' || t_date || '.001.txt';
table_name := 'T_MO_ACCESS_LOG_' || TO_CHAR(SYSDATE,'yyyyMM');
testjiao_handle := UTL_FILE.FOPEN('DIR_DUMP',file_name,'w');

dyna_sql := 'select ACC_PHONE,ACC_AREACODE,ACC_APPVERSION,to_char(ACC_CREATETIME,''YYYYMMDDHH24MISS'') as ACC_CREATETIME from ' || table_name || ' where ACC_METHOD = ''user.login'' and ACC_CREATETIME between to_date('''|| stat_time ||''',''YYYY-MM-DD HH24:MI:SS'') and to_date('''|| end_time ||''',''YYYY-MM-DD HH24:MI:SS'') order by ACC_AREACODE,ACC_CREATETIME';

open my_cur for dyna_sql;
   fetch my_cur into ACC_PHONE,ACC_AREACODE,ACC_APPVERSION,ACC_CREATETIME;
   while my_cur%found LOOP
      UTL_FILE.PUT_LINE(testjiao_handle,ACC_PHONE ||','|| ACC_AREACODE ||','|| ACC_APPVERSION ||','|| ACC_CREATETIME);
   fetch my_cur into ACC_PHONE,ACC_AREACODE,ACC_APPVERSION,ACC_CREATETIME;
   END LOOP;
   UTL_FILE.FCLOSE(testjiao_handle);

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
END DAY_LOGIN;