Oracle常用重复建表，索引，序列，表列等通用方法封装

在日常开发过程中，构建自动化的部署，要求开发人员提供的数据库Sql能够多次重复的执行，这就要求对Oracle的一些对象，例如：表，索引，序列，表的列等需要做判断，如果存在，先drop掉，然后新建。否则，脚本只能执行一次，下次执行的时候就会报对象已经被占用的错误，附件的Oracle package封装了日常使用的几个操作的解决办法。

 

  --判断表的列是否存在，存在drop掉
  procedure DROP_COLUMN_IFEXISTS(p_table_name varchar2,p_column_name varchar2);

  --判断表是否存在，存在drop掉
  procedure DROP_TABLE_IFEXISTS(p_table_name varchar2);

  --判断sequence是否存在 存在drop掉
  procedure DROP_SEQUENCE_IFEXISTS(p_sequence_name varchar2);
  
  --判断index是否存在 存在drop掉
  procedure DROP_INDEX_IFEXISTS(p_index_name varchar2);

 

调用示例，首先，将附件的Oracle package通过commond（命令行窗口）做导入。

1.打开命令窗口

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0 

Connected as oms

 

SQL>

 

2.输入@然后按下回车键，选择解压出来的1.toos_package.sql，部署package成功。

 

SQL> @

Started spooling to C:\Program Files\PLSQL Developer\1.toos_package.log

 

Creating package TOOLS_PACKAGE

==============================

 

 

Package created

 

Creating package body TOOLS_PACKAGE

===================================

 

 

Package body created

Stopped spooling to C:\Program Files\PLSQL Developer\1.toos_package.log

 

3.部署成功后，看看我们的测试sql：2.test.sql，测试sql中包含了我们日常工作常见的建立表，索引，序列，表列的操作。

--如下语句保证重复执行
call tools_package.drop_table_ifexists('test_sj123');
call tools_package.drop_index_ifexists('ind_test_name');
call tools_package.drop_sequence_ifexists('SEQ_SJ_123');
call tools_package.drop_column_ifexists('test_sj123','age');

--创建表
create table test_sj123
( 
    id integer,
    name varchar2(100)
);
--创建index
create index ind_test_name on test_sj123(name);

--创建sequence
create sequence SEQ_SJ_123
minvalue 1
maxvalue 99999
start with 1
increment by 1
nocache
cycle;

--对表增加列
alter table test_sj123 add  age integer;

 

工作中，如果需要有的时候书写系统设计文档，要将数据表的结构及注释附上，如果在word里面一个一个写，太麻烦，如果在建表的时候你已经帮表加了注释，那么恭喜你，通过一条sql就可以帮你把注释全部拉出来，拷贝粘贴到word即可。

 --将表的列类型，注释拉出来
  procedure QUERY_TABLE_COMMENTS(p_table_name varchar2,p_result out sys_refcursor);

 调用toos_package中的QUERY_TABLE_COMMENTS存储过程，如下：

 直接拷贝到word即可。

 

工作中，还有另外一种需求，对数据库表进行insert，update操作，如果表的列过多，手写sql是一件非常麻烦的事情，但是调用

  --自动生成insert，update，select等语句
  procedure autoGenerateSQL
  (
     tableName varchar2,
     type varchar2,
     out_result out varchar2
  );

 轻松帮你搞定

 执行后自动生成

insert into OMS_EXPENSE_PROVISION(PROVISION_ID,PROJECT_CODE,PROJECT_MANAGER_ID,PROJECT_MANAGER,PROJECT_NAME,APPLICATION_CODE,ORDER_AMOUNT,APP_BUSINESS_TYPE_ID,BUSINESS_MANAGER_ID,BUSINESS_MANAGER,SBU_ID,LARGER_AREA_ID,LARGER_AREA,EXPENSE_TYPE,PROVISION_MONTH,PROVISION_AMOUNT,CREATER_BY,CREATER_DATE,LAST_UPDATER_BY,LAST_UPDATER_DATE,IS_VALID,MONEY_TYPE,TECH_COOP_APPLY_TYPE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)