oracle 建表之前先删除语句

最新推荐文章于 2022-10-18 16:37:24 发布

chunrenrong2587

最新推荐文章于 2022-10-18 16:37:24 发布

阅读量89

点赞数

文章标签：数据库 java

原文链接：https://my.oschina.net/HuifengWang/blog/232980

版权

首先,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); //动态建表
	}
}

转载于:https://my.oschina.net/HuifengWang/blog/232980