oracle 建表之前先删除语句
首先,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{@Autowiredprivate ImportMapper importMapper;@Overridepublic void createTable(Map<String, Object> map) {importMapper.dropTable((String)map.get("tableName"));// 调用存储过程删除表,有就删除,没有不做操作importMapper.createTable(map); //动态建表}}?