oracle 建表之前先删除语句

mwvoetsbmn

　　首先,Oracle 没有这种语句 create table xxx if exists!
　　
　　所以我们可以另辟蹊径,通过写存储过程,然后用mybatis 调用,在动态建表之前先调用存储过程验证时候有该表,如果有就通过我们写的存储过程删除,存储过程代码如下:

CREATE OR REPLACE
procedure        "PROC_DROPIFEXISTS"(
p_table in varchar2
) is
v_count number(10);
begin
select count(*)
into v_count
from user_objects
where object_name = upper(p_table);
if v_count > 0 then
execute immediate 'drop table ' || p_table ||' cascade constraints';
end if;
end;

　　mybatis的xml文件代码如下:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mypro.employee.dao.ImportMapper" >
<select  id="dropTable" parameterType="Map" statementType="CALLABLE">
<![CDATA[  
{call PROC_DROPIFEXISTS (#{tableName,jdbcType=VARCHAR})}  
]]>  
</select >
<update id="createTable" parameterType="Map">
<![CDATA[
create table ${tableName} (id integer,name varchar(20))
]]>
</update>
</mapper>
　　Service里面直接调用就OK

package com.mypro.employee.service.impl;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.mypro.employee.dao.ImportMapper;
import com.mypro.employee.service.ImportService;
@Service
@Transactional(rollbackFor=Exception.class)
public class ImportServiceImpl  implements ImportService{
@Autowired
private ImportMapper importMapper;
@Override
public void createTable(Map<String, Object> map) {
importMapper.dropTable((String)map.get("tableName"));// 调用存储过程删除表,有就删除,没有不做操作
importMapper.createTable(map); //动态建表
}
}