对于oracle使用关联的，表设计，sql等问题

于 2023-11-14 17:11:24 发布
阅读量140 收藏
点赞数
文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_37018378/article/details/134399908
版权
本文详细讨论了在使用Oracle数据库时，表设计、字段命名、数据类型选择、长度设定、非空规则、索引和主键策略，以及如何将数据映射到XML文件中，包括sequence的使用和SQL查询优化，如分页和批量插入的技巧。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一.表设计和xml中要考虑的问题：        

        这是承接上两篇文章，sql使用规范和oracle的安装使用。

        当我想要去使用oracle作为数据库存储数据，一定是要对数据库数据产生操作的。那么有三个地方需要考虑：

        1.数据库表的设计，要考虑全面；例如字段有哪些，合适的类型和长度等，这个在下面展开

        2.字段设计之后，在数据库中如何正确命名？映射到xml文件中又该怎么命名？

        3.非空，索引，主键自增等问题是否考虑？(唯一约束本人很少用到，这里暂不分析)

二.分析user表作为参考案例：

        1.user表是人的基本信息的抽象，至少有以下信息：

姓名 性别年龄身份证号手机号住址email

        可能在平时生活中要email的较少，但确实很常见，且一个手机号联系不保险的。


        2.隐藏信息是要考虑的：

建表用户 建表时间修改用户修改时间逻辑删除状态值

        这里是为了表的安全性，可以查到时间和操作的用户的信息。且数据不再使用物理删除而是逻辑删除，所以状态值也是要考虑的。

        3.唯一标识：主键ID

ID

         这里的ID作为列名是否可行？ 我在项目看到是最好不要。要用"PK_表名" 请各位探讨一下！


        4.字段的类型

        从ID来考虑，是varchar还是integer比较好呢？
        如果是仅仅oracle的话，因为序列是从1开始，所以integer就是OK的。由于我不仅用到了oracle，还有mongo，而mongo的id是自动生成的，且较长了。所以综合考虑，同一为varchar

        从身份证来考虑，最好是使用varchar，为了保证要么一整个字符串都失效要么都完好，不能确实一部分。

        从性别来考虑，我习惯0代表男，1代表女，所以采用integer类型。

        而创建时间，这种时间类型，是用date类型，还是时间戳timestamp类型呢？

以上是我的部分考虑。也请各位按照这个思路去综合考虑。

数据库存储时间的存储类型大概有3种,varchar2,date 和 timestamp

存储到秒级别，用date,

需要到秒级以下，用timestamp,

TIMESTAMP[(seconds_precision)]存 储世纪、4位的年、月、日、时(以24小时格式)、分和秒。seconds_precision为可选参数，用于指定精度，该参数为一个整数，范围从 0~9，默认值为9；意思是表示秒的数字的小数点右边可以存储9位数字。如果试图在秒的小数中保存超过TIMESTAMP存储精度的数字，那么该小数将被 取整

且timestamp可以存储时区

oracle存储时间类型 date 和 timestamp区别_oracle date和timestamp区别-优快云博客

        5.每个字段长度

        字段长度是要考虑的，不能说初学建表就不去考虑。而是更应该考虑。这样逻辑上才不那么会出现漏洞，不然后期改起来是很痛苦的。

        我考虑第一，长度为1的。比如sex，delStatus.

        然后长度固定的，idCard，phone。

        以及合理长度，比如姓名，百度表示最长名字是25个字，那么有没有必要设置长度为75个字节？（中文每一个汉字占三个字节）。

        6.有没有哪些字段是非空的？

        7.每个字段的注释有，且sex这种字段，0、1代表什么意思一定要写明白

        8.比如sex，delStuatus这些字段是要设置默认值的

        9.为了快速的查询，部分字段要加上索引。那么两个问题，1.索引加在哪个字段上，2.是唯一索引还是普通索引。

        根据我的预设需求，会通过名字来查，所以会给名字加上普通索引。        

        10.主键ID是要做自增设计的。

       所以在建表语句中创建了一个sequence(序列)，但是从稳定性来看，最好是在xml中写sql的时候再用上，而不要使用触发器。这会产生一定的不稳定性。

在 Oracle 数据库中，可以通过创建一个 SEQUENCE 来实现自增主键。 SEQUENCE 是一个对象，它生成唯一的数字序列，可以被用来给主键赋值。你需要在创建表之前创建一个 SEQUENCE，然后在插入新行数据时，通过 NEXTVAL 函数调用 SEQUENCE 从而获得下一个自增值


CREATE SEQUENCE table_name_seq START WITH 1 INCREMENT BY 1;


你需要将 table_name_seq 替换为你想要使用的名称，以及根据需要更改 START WITH 和 INCREMENT BY 值。 START WITH 指定初始序列值，INCREMENT BY 指定每次调用 NEXTVAL 函数时序列增加的步长

        以下是部分sql示例：

新建一张表 
create table test
(id int primary key,
name varchar2(10));

create sequence 序列名称
start with 1 -- 起始值
increment by 1 -- 增量
maxvalue 99999999 -- 最大值
nocycle -- 达到最大值后是否重新计算，当前为不重新计算，cycle为重新计算
nocache; -- 不要缓存，容易跳号
 
不用触发器：
insert into student values (zsc_seq.nextval,'zsc');
 
触发器实现（不要改动，就当做一个脚本）
CREATE OR REPLACE TRIGGER 触发器名称
BEFORE INSERT ON 表名称 FOR EACH ROW
BEGIN
SELECT 序列名称.NEXTVAL INTO :NEW.字段名称 FROM DUAL;
END;

删除序列：
在删除一个序列之前，您需要确认要删除的序列的名称。您可以使用以下命令查看所有可用的序列
SELECT sequence_name FROM user_sequences;

在删除一个序列之前，您需要停止使用它。如果有表在使用该序列，那么它不能被删除。使用以下命令检查表是否正在使用该序列：
SELECT table_name FROM user_tab_columns WHERE column_name = 'PK_USERS_ID';

如果有表正在使用该序列，您将需要先修改表。您可以使用以下命令修改表，使其不再使用该序列：
ALTER TABLE BOYANZSC_TORACLEDEMO_USERS MODIFY (PK_USERS_ID DEFAULT NULL);
一旦您的序列不再被使用，您可以使用以下命令来删除它：

DROP SEQUENCE PK_USERS_ID;

 参考文章：

创建序列和触发器icon-default.png?t=N7T8https://www.cnblogs.com/chenzblog/p/15657006.html

删除序列icon-default.png?t=N7T8https://www.php.cn/faq/533076.html

 

三.映射到xml

<mapper namespace="com.zsc.dao.UserMapper">
    <resultMap id="BaseResultMap" type="com.zsc.po.ToracleDemoUserPO">
        <id column="PK_USER" jdbcType="INTEGER" property="userId" />
        <result column="USER_NAME" jdbcType="VARCHAR" property="userName"/>
        <result column="AGE" jdbcType="INTEGER" property="age"/>
        <result column="SEX" jdbcType="INTEGER" property="sex"/>
        <result column="IDCARD" jdbcType="VARCHAR" property="idCard"/>
        <result column="PHONENUM" jdbcType="INTEGER" property="phoneNum"/>
        <result column="EMAIL" jdbcType="VARCHAR" property="email"/>
        <result column="CREATE_USER" jdbcType="VARCHAR" property="createUser"/>
        <result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="UPDATE_USER" jdbcType="VARCHAR" property="updateUser"/>
        <result column="UPDATE_TIME" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="DEL_STATUS" jdbcType="INTEGER" property="delStatus"/>
        <result column="ADDRESS_ID" jdbcType="VARCHAR" property="userAddressId"/>
    </resultMap>



    <sql id="Base_Column_List">
        PK_USER,
        USER_NAME,
        AGE,
        SEX,
        IDCARD,
        PHONENUM,
        EMAIL,
        CREATE_USER,
        CREATE_TIME,
        UPDATE_USER,
        UPDATE_TIME,
        DEL_STATUS,
        ADDRESS_ID
    </sql>

这两块是一定要有的，后面的用处很大。比如BaseResultMap可以作为返回的类型，Base_Column_List可以替代全表字段，避免*的使用。

四.sequence的使用

        刚才提到了sequence不要在数据库中去使用，那么在sql中如何使用？

        一般是结合新增这一类的sql使用

 <insert id="saveUser" parameterType="com.zsc.po.ToracleDemoUserPO">
        <selectKey resultType="java.lang.Integer" keyProperty="shiftid" order="BEFORE">
            SELECT SEQ_USERID.nextval FROM dual
        </selectKey>

        INSERT INTO BOYANZSC_TORACLEDEMO_USER
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="shiftid != null">
                USER_ID,
            </if>
            <if test="savePO.userName != null">
                USER_NAME,
            </if>
            <if test="savePO.age != null">
                AGE,
            </if>
            <if test="savePO.sex != null">
                SEX,
            </if>
            <if test="savePO.idCard != null">
                IDCARD,
            </if>
            <if test="savePO.phoneNum != null">
                PHONENUM,
            </if>
            <if test="savePO.email != null">
                EMAIL,
            </if>
            <if test="savePO.createUser != null">
                CREATE_USER,
            </if>
            <if test="savePO.createTime != null">
                CREATE_TIME,
            </if>
            <if test="savePO.updateUser != null">
                UPDATE_USER,
            </if>
            <if test="savePO.updateTime != null">
                UPDATE_TIME,
            </if>
            <if test="savePO.delStatus != null">
                DEL_STATUS,
            </if>
            <if test="savePO.userAddressId != null">
                ADDRESS_ID
            </if>
        </trim>
        VALUES
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="shiftid != null">
                #{shiftid},
            </if>
            <if test="savePO.userName != null">
                #{savePO.userName},
            </if>
            <if test="savePO.age != null">
                #{savePO.age},
            </if>
            <if test="savePO.sex != null">
                #{savePO.sex},
            </if>
            <if test="savePO.idCard != null">
                #{savePO.idCard},
            </if>
            <if test="savePO.phoneNum != null">
                #{savePO.phoneNum},
            </if>
            <if test="savePO.email != null">
                #{savePO.email},
            </if>
            <if test="savePO.createUser != null">
                #{savePO.createUser},
            </if>
            <if test="savePO.createTime != null">
                #{savePO.createTime},
            </if>
            <if test="savePO.updateUser != null">
                #{savePO.updateUser},
            </if>
            <if test="savePO.updateTime != null">
                #{savePO.updateTime},
            </if>
            <if test="savePO.delStatus != null">
                #{savePO.delStatus},
            </if>
            <if test="savePO.userAddressId != null">
                #{savePO.userAddressId},
            </if>
        </trim>
    </insert>

这里将序列值从dual虚拟表中查出来，代替ID这一类的数据值。

五.分页的sql，用到oracle的另一个知识叫做"行号" ROWNUM

ROWNUM 也是一个保留字所以无法直接在查处行号的那一层直接用行号比较，所以要取别名，降级之后，在外层才可以使用行号去作比较。

<select id="queryUserList" resultMap="BaseResultMap">
        select
            toUserPage.USER_ROWNUM,<include refid="Base_Column_List"/>
        from
            (select
                ROWNUM USER_ROWNUM, <include refid="Base_Column_List" />
            from
                BOYANZSC_TORACLEDEMO_USER
            <where>
                <if test="PagePO.userName != null and PagePO.userName != ''">
                    USER_NAME like '%' || #{PagePO.userName} || '%'
                </if>
                <if test="PagePO.sex != null and PagePO.sex != ''">
                    and SEX = #{PagePO.sex}
                </if>
                <if test="PagePO.age != null and PagePO.age != ''">
                    and AGE = #{PagePO.age}
                </if>
                    and DEL_STATUS = 0
            </where>) toUserPage
        where
            USER_ROWNUM <![CDATA[>]]> #{pagePaeams.start}
        AND
            USER_ROWNUM <![CDATA[<=]]> #{pagePaeams.finish}
    </select>

六.批量插入，对于子查询不让用select * ，才如何解决

方法就是按照传入的参数里面的字段，去分别取别名。而且select后面直接全部陈列出来。(这是现在的办法，后续应该会优化)

<insert id="batchInsertUser" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT INTO BOYANZSC_TORACLEDEMO_USER (
            PK_USER,
            USER_NAME,
            AGE,
            SEX,
            IDCARD,
            PHONENUM,
            EMAIL,
            CREATE_USER,
            CREATE_TIME,
            UPDATE_USER,
            UPDATE_TIME,
            DEL_STATUS,
            ADDRESS_ID
            )
            SELECT
                   SEQ_USERID.nextval,
                   batch.USERNAME,
                   batch.AGE,
                   batch.SEX,
                   batch.IDCARD,
                   batch.PHONENUM,
                   batch.EMAIL,
                   batch.CREATEUSER,
                   batch.CREATETIME,
                   batch.UPDETEUSER,
                   batch.UPDATETIME,
                   batch.DELSTATUS,
                   batch.ADDRESSID
            FROM(
                <foreach collection="toracleDemoUserPOList" item="item" separator="union all">
                    SELECT
                        #{item.userName} as USERNAME,
                        #{item.age} as AGE,
                        #{item.sex} as SEX,
                        #{item.idCard} as IDCARD,
                        #{item.phoneNum} as PHONENUM,
                        #{item.email} as EMAIL,
                        #{item.createUser} as CREATEUSER,
                        #{item.createTime} as CREATETIME,
                        #{item.updateUser} as UPDETEUSER,
                        #{item.updateTime} as UPDATETIME,
                        #{item.delStatus} as DELSTATUS,
                        #{item.userAddressId} as ADDRESSID
                    FROM
                        dual
                </foreach>
                )batch
    </insert>

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值