动态创建表（oracle）及动态操作表

QQ初级码农

于 2022-12-21 11:32:15 发布

144

文章标签：数据库 sql oracle

本文链接：https://blog.csdn.net/SunnyDuQQ/article/details/128394804

版权

动态创建表

<update id="createTable" parameterType="java.util.Map" statementType="STATEMENT">
		CREATE TABLE  ${tableName}
		(
		<foreach collection="list" separator=","  index="index"  item="item">
			"${item.ENGLISHNAME}" ${item.DATATYPE} ${item.SUBJECTPRECISION}
		</foreach>
		,primary key (主键)
		)
</update>

给表中字段加注释信息（只能单条操作）

<update id="createComment" parameterType="java.lang.String" statementType="STATEMENT">
	COMMENT ON TABLE  ${tableName} IS '${tableName}'
</update>

给表加注释信息（只能单条操作）

<update id="createCommentCol" parameterType="java.lang.String" statementType="STATEMENT">
	COMMENT ON COLUMN ${tableName}."${ENGLISHNAME}" IS '${CHINANAME}'
</update>

给表新增列字段

<update id="updateStru"  parameterType="java.util.Map" statementType="STATEMENT" >
	Alter   Table    ${tableName}    Add   ${column}   ${type}
</update>

动态删除列

<delete id="deleteColumn"  parameterType="java.util.Map" statementType="STATEMENT" >
	Alter table  ${tableName} drop column ${column}
</delete>

动态查询

<select id="findStaticDataList" resultType="java.util.Map" parameterType="java.util.Map">
	select * from ${tableName} where 1=1
	<if test="keyId != null and keyId != ''" >
		and key_id = #{keyId}
	</if>
</select>

动态插入

<insert id="addStaticData"  parameterType="java.util.Map" statementType="STATEMENT" >
        insert into  ${tableName}(
			<foreach collection="columns" item="item" separator=",">
				${item}
			</foreach>
		)values(<include refid="values"/> )
</insert>
<sql id="values">
		<foreach item="item" collection="data" separator="," >
			'${item}'
	    </foreach>
</sql>

动态修改

<update id="updateStaticData" parameterType="java.util.Map" statementType="STATEMENT" >
        update ${tableName} set
			<foreach  item="value" collection="columns" index="key"  separator=",">
				<if test="key !=null and key !=''">
					${key} = '${value}'
				</if>

			</foreach>
		where
		  BID=${bid}
</update>

动态删除

<delete id="deleteInfo" parameterType="java.util.Map">
		delete from ${tableName}
		where
		key_id in
		<foreach collection="keyIdList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
</delete>