oracle在创建表的时候指定格式化,关于ORACLE定时创建数据库表（转载）

第一步：创建一个执行创建操作的存储过程

在这一步首先要解决的问题就是构造表名。在Oracle中格式话输出时间可以用to_char函数来处理，例如：

SQL> select to_char(sysdate, ’yyyy/mm/dd hh24:mi:ss’) from dual;

TO_CHAR(SYSDATE,’YYYY/MM/DDHH2

------------------------------

2009/02/14 17:22:41

以上SQL格式化输出了时间，要得到我们所需要的格式直接修改一下SQL即可

SQL> select to_char(sysdate, ’yyyymmdd’) from dual;

TO_CHAR(SYSDATE,’YYYYMMDD’)

---------------------------

20090214

得到时间格式字符串后我们就可以将表名的前缀和时间连接在一起形成完整的表名。  这里需要注意，在Oracle中链接两个字符串需要使用‘||’符号，而在Sql Server中直接使用‘+’号就可以了，因为我以前一直在Sql Server下编程，好久都没编写Oracle的SQL所以费了很大的功夫才发现这个问题。完整的Sql就是

SQL> select ’tbl_programme_’ || to_char(sysdate, ’yyyymmdd’) from dual;

’TBL_PROGRAMME_’||TO_CHAR(SYSD

------------------------------

tbl_programme_20090214

接下来就是创建表的代码了，因为新表需要tbl_programme保持一致，所以直接CTAS来创建表那是非常适合的了，代码如下：

Create table tablename as select * from tbl_programme

如果需要指定一个TableSpace则将该SQL做适当修改：

Create table tablename tablespace p2p as select * from tbl_programme

所以整个创建存储过程的SQL就是

create or replace procedure sp_createtab_tbl_programme

Authid Current_User

as

tabname varchar(200);

begin

select ’TBL_PROGRAMME_’ || to_char(sysdate, ’yyyymmdd’) into tabname from dual;

--create table tabname as select * from tbl_programme where 1 != 1;

execute immediate ’create table ’ || tabname ||’ tablespace p2p as select * from tbl_programme where 1 != 1’;

commit;

end;

/

这里还需要注意一下在Oracle里面如果要对一个变量赋值的话有两种方式：

(1) 使用：=进行赋值

(2) 使用select ‘xjkxj ’ into 变量名称 from tabname

另外，在存储过程中定义变量的时候一般放在as/is后begin前面。  在存储过程一般是不能直接使用create table，truncate table这类似的语句的，如果要使用这些语句必须使用excute immediate + 所要执行的sql语句来实现。

注意上面用红色标志的语句：Authid Current_User

这个语句比较重要，如果我们在创建存储过程的时候不添加这条语句执行该存储过程将不会成功，原因是默认情况向存储过程是没有Create table等权限的，即使当前用户有DBA的权限也不行，如果存储过程中存在创建表的操作，可以有以下两种方式来解决该问题。

(1) 显示的赋予该用户Create table的权限，grant create table to user。

(2) 在存储过程中使用Authid Current_User 标识使用当前用户的权限。

第二步：创建JOB

创建JOB就比较简单了，下面就是创建JOB的代码

每天晚上1电job启动一次，执行sp_createtab_tbl_programme存储过程。

VARIABLE testjobid number;

begin

sys。dbms_job。submit(:testjobid,’sp_createtab_tbl_programme;’,trunc(sysdate+1)+1/24,’trunc(sysdate+1)+1/24’);

commit;

end;

/

这里需要注意的是，在submit方法的前面一定要先定义job这个变量，另外，submit方法的第二个参数是一个存储过程的名，记得在后面添加“：”号，在next_date是一个时间类型变量而不是一个字符串，所以需要注意不要把它当成字符串，不需要对该参数加引号。  最后一个参数interval是一个字符串类型，记得添加引号。最常见的错误如下图所示：

ORA-01008： not all variables bound就是没有定义变量的意思。一定记的在使用submit方法时定义jobid变量。

原文：https://www.cnblogs.com/yfssss/p/11571536.html