Oracle存储过程
来源:互联网 发布:动态图截取软件 编辑:程序博客网 时间:2024/04/30 21:45
--删除三个月前的表
create or replace procedure drop_old_table(tables_name varchar2) is
v_newTableName varchar2(50);
v_createSql varchar2(200);
begin
for k in 1..to_number(to_char(last_day(add_months(sysdate,-3)),'dd')) loop
v_newTableName := upper(tables_name||trim(to_char(add_months(sysdate,1),'MM'))||trim(to_char(k,'00')));
v_createSql := 'drop table '||v_newTableName;
if length(v_createSql) > 0 then
drop_mytable(v_newTableName);
end if;
end loop;
end;
--创建下个月要用的表
create or replace procedure createOneMonthTable(tables_name varchar2,) is
v_newTableName varchar2(50);
v_oldTableName varchar2(50);
v_createSql varchar2(200);
table_num number(2);
begin
for k in 1..to_number(to_char(last_day(add_months(sysdate,1)),'dd')) loop
v_newTableName := upper(tables_name||trim(to_char(add_months(sysdate,1),'MM'))||trim(to_char(k,'00')));
v_oldTableName := upper(tables_name||to_char(sysdate,'MM')||to_char(last_day(sysdate),'dd'));
v_createSql := 'create table '||v_newTableName||' as select * from '||v_oldTableName||' where 1=2';
select count(*) into table_num from user_tables where table_name = upper(v_newTableName);
if length(v_createSql) > 0 and table_num <=0 then
execute immediate ''||v_createSql||'';
end if;
end loop;
end;
--创建安装时需要创建的所有chr表
create or replace procedure createCurrentMonthTable(tables_name varchar2,old_tables_name varchar2) is
v_newTableName varchar2(50);
v_oldTableName varchar2(50);
v_createSql varchar2(200);
table_num number(2);
begin_num number(2);
end_num number(2);
begin
begin_num := to_number(to_char(sysdate,'dd'));
end_num := to_number(to_char(last_day(sysdate),'dd'));
for k in begin_num..end_num loop
v_newTableName := upper(tables_name||trim(to_char(sysdate,'MM'))||trim(to_char(k,'00')));
v_oldTableName := upper(old_tables_name);
v_createSql := 'create table '||v_newTableName||' as select * from '||v_oldTableName||' where 1=2';
select count(*) into table_num from user_tables where table_name = upper(v_newTableName);
if length(v_createSql) > 0 and table_num <=0 then
execute immediate ''||v_createSql||'';
end if;
end loop;
end;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
AMERICAN_AMERICA.AL32UTF8
create or replace procedure drop_old_table(tables_name varchar2) is
v_newTableName varchar2(50);
v_createSql varchar2(200);
begin
for k in 1..to_number(to_char(last_day(add_months(sysdate,-3)),'dd')) loop
v_newTableName := upper(tables_name||trim(to_char(add_months(sysdate,1),'MM'))||trim(to_char(k,'00')));
v_createSql := 'drop table '||v_newTableName;
if length(v_createSql) > 0 then
drop_mytable(v_newTableName);
end if;
end loop;
end;
--创建下个月要用的表
create or replace procedure createOneMonthTable(tables_name varchar2,) is
v_newTableName varchar2(50);
v_oldTableName varchar2(50);
v_createSql varchar2(200);
table_num number(2);
begin
for k in 1..to_number(to_char(last_day(add_months(sysdate,1)),'dd')) loop
v_newTableName := upper(tables_name||trim(to_char(add_months(sysdate,1),'MM'))||trim(to_char(k,'00')));
v_oldTableName := upper(tables_name||to_char(sysdate,'MM')||to_char(last_day(sysdate),'dd'));
v_createSql := 'create table '||v_newTableName||' as select * from '||v_oldTableName||' where 1=2';
select count(*) into table_num from user_tables where table_name = upper(v_newTableName);
if length(v_createSql) > 0 and table_num <=0 then
execute immediate ''||v_createSql||'';
end if;
end loop;
end;
--创建安装时需要创建的所有chr表
create or replace procedure createCurrentMonthTable(tables_name varchar2,old_tables_name varchar2) is
v_newTableName varchar2(50);
v_oldTableName varchar2(50);
v_createSql varchar2(200);
table_num number(2);
begin_num number(2);
end_num number(2);
begin
begin_num := to_number(to_char(sysdate,'dd'));
end_num := to_number(to_char(last_day(sysdate),'dd'));
for k in begin_num..end_num loop
v_newTableName := upper(tables_name||trim(to_char(sysdate,'MM'))||trim(to_char(k,'00')));
v_oldTableName := upper(old_tables_name);
v_createSql := 'create table '||v_newTableName||' as select * from '||v_oldTableName||' where 1=2';
select count(*) into table_num from user_tables where table_name = upper(v_newTableName);
if length(v_createSql) > 0 and table_num <=0 then
execute immediate ''||v_createSql||'';
end if;
end loop;
end;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
AMERICAN_AMERICA.AL32UTF8
- Oracle:存储过程,存储函数
- Oracle-存储过程 存储函数
- Oracle存储过程、存储函数
- oracle 存储函数,存储过程
- Oracle存储过程
- 调试oracle存储过程。
- ORACLE 存储过程实战
- Oracle 存储过程
- oracle存储过程使用
- Oracle 创建存储过程
- 调试oracle存储过程
- oracle java存储过程
- 创建Oracle存储过程
- 加密oracle存储过程
- oracle存储过程分页
- oracle存储过程笔记
- ORACLE存储过程--注意事项
- oracle procedure 存储过程
- C#位运算 http://www.cnblogs.com/sweting/archive/2009/11/05/1596873.html
- JVM调优总结(二)-一些概念
- 07 - 基本控件:ListView 列表
- 多线程信号量控制
- DBUnit向mysql导数据异常
- Oracle存储过程
- libgdx 环境搭建
- 仿豌豆荚实现android连接pc方法
- JVM调优总结(三)-基本垃圾回收算法
- Tomcat一步步实现反向代理、负载均衡、内存复制
- SCOPE_IDENTITY和@@identity的区别
- dealloc Don't Use Accessor Methods in Init and Dealloc
- linux下配置虚拟主机
- ztree 添加节点