Oracle常见问题

1.oracle没有自增主键策略，需要手动创建主键的生成策略

https://blog.csdn.net/hu_dongyang/article/details/79039737

1.1 创建序列

语法 ``CREATE SEQUENCE 序列名 [相关参数]`

1.2 参数说明

• INCREMENT BY :序列变化的步进，负值表示递减。(默认1)
• START WITH:序列的初始值 。(默认1)
• MAXvalue:序列可生成的最大值。(默认不限制最大值，NOMAXVALUE)
• MINVALUE:序列可生成的最小值。(默认不限制最小值，NOMINVALUE)
• CYCLE:用于定义当序列产生的值达到限制值后是否循环(NOCYCLE:不循环，CYCLE:循环)。
• CACHE:表示缓存序列的个数，数据库异常终止可能会导致序列中断不连续的情况，默认值为20，如果不使用缓存可设置NOCACHE

例如：CREATE SEQUENCE SEQ_DEMO INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE;

1.3 修改和删除

• 修改、删除序列
• 使用 alter 命令进行修改
• 使用 drop 命令删除

1.4 序列的使用

oracle数据库必须要有序列才能自动生成主键，不然会报错的

• currval 表示序列的当前值，新序列必须使用一次nextval 才能获取到值，否则会报错

• nextval 表示序列的下一个值。新序列首次使用时获取的是该序列的初始值，从第二次使用时开始按照设置的步进递增

• 查询序列的值：select seq_name.[currval,nextval] from dual;

  dual是mysql中的一个虚标,用来构成select的语法规则，oracle保证dual里面永远只有一条记录

• 查看所有已创建的序列：select * from user_sequences

• SQL语句中使用：insert into table (id) values (seq_name.nextval)

1.5 结合Mybatis插入前使用生成的主键

  <insert id="insertSelective" keyColumn="ACCOUNT_ID" keyProperty="accountId" parameterType="com.thinkive.simulate.match.api.server.domain.entity.MatchAccount">
    <!--keyProperty:bean中的属性名-->
    <!--keyColumn:返回的列名-->
    <selectKey keyProperty="accountId" keyColumn="ACCOUNT_ID" resultType="string" order="BEFORE"> 
        select SEQ_ACCOUNT.nextval from dual 
    </selectKey>
    insert into T_MATCH_ACCOUNT
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="accountId != null">
        ACCOUNT_ID,
      </if>
      <if test="userId != null">
        USER_ID,
      </if>
      <if test="matchId != null">
        MATCH_ID,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="accountId">
        #{accountId,jdbcType=VARCHAR},
      </if>
      <if test="userId != null">
        #{userId,jdbcType=VARCHAR},
      </if>
      <if test="matchId != null">
        #{matchId,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

2.oracle jdbc映射关系

3.分页查询rownum

https://blog.csdn.net/qq_39196949/article/details/84379874

https://www.cnblogs.com/wyb628/p/7675691.html

ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类 推。如果你用>,>=,=,between...and这些条件，例如：select * from T_MATCH_ACCOUNT where rownum>=5因为从缓冲区或数据文件中得到的第一条记录的rownum为1，不满足rownum>=5则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

-- 查询第10条记录之后的所有记录
select * from (select rownum r,tma.* from T_MATCH_ACCOUNT tma) t where r>10
-- 查询前10条的数据
select * from (select rownum r,tma.* from T_MATCH_ACCOUNT tma where rownum<=10) t
-- 查询第1条数据
SELECT * from T_MATCH_ACCOUNT where rownum=1
-- 查询第10条数据
SELECT * from (select rownum r,tma.* from T_MATCH_ACCOUNT tma where rownum<=10) t where r=10
-- 查询第10-20条数据
SELECT * from (select rownum r,tma.* from T_MATCH_ACCOUNT tma where rownum<=20) t where r>=10 and r<=20

4.字符串的拼接 concat和||

• concat函数

CONCAT( string1, string2 ) concat函数只能拼接两个字符串

SELECT concat('hello','world') from dual  -- helloworld

若拼接两个以上的字符串，则需要嵌套使用

SELECT concat('hello',concat('你好世界','world')) from dual -- hello你好世界world

• || 可以连接多个字符串

SELECT '你好'||'世界'||'你好'||'明天' from dual  -- 你好世界你好明天

5.insert all into批量插入

https://blog.csdn.net/libertine1993/article/details/52775575

INSERT ALL INTO tb_red VALUES(1000, 8001, '2016-10-10 10:59:59', 1, 8001, '测试用户1000', '红名单0', '男', '膜法学院', '被测')
INTO tb_red  VALUES (1001, 8001, '2016-10-10 11:00:00', 2, 8001, '测试用户1001', '红名单1', '男', '膜法学院', '被测')
INTO tb_red  VALUES (1002, 8001, '2016-10-10 11:00:01', 0, 8001, '测试用户1002', '红名单2', '男', '膜法学院', '被测')
INTO tb_red  VALUES (1003, 8001, '2016-10-11 10:59:59', 1, 8001, '测试用户1003', '红名单3', '男', '膜法学院', '被测')
INTO tb_red  VALUES (1004, 8001, '2016-10-11 11:00:00', 2, 8001, '测试用户1004', '红名单4', '男', '膜法学院', '被测')
INTO tb_red  VALUES (1005, 8001, '2016-10-11 11:00:01', 0, 8001, '测试用户1005', '红名单5', '男', '膜法学院', '被测')
select 1 from dual;

• 第一句用的是insert all into 不是 insert into
• 最后跟的selecr 1 from dual语句中的dual表可以被替换为任何一个只要不是tb_red的表
• 和mysql的写法不一样，多个values之间不用逗号分隔，但是需要加into tablename的形式的语句在每个values前面
• 只适合于Oralce 9i以上版本

6.常用函数

decode条件函数

https://blog.csdn.net/qichangjian/article/details/88975499

• decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

IF 条件=值1 THEN 　　　　RETURN(返回值1) ELSIF 条件=值2 THEN 　　　　RETURN(返回值2) 　　　　...... ELSIF 条件=值n THEN 　　　　RETURN(返回值n) ELSE 　　　　RETURN(缺省值) END IF

• decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

sign函数

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

select id,sign(id-2) from t_decode;

nvl函数

NVL(eExpression1, eExpression2)

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

round函数

round(数值,保留的小数位deciamls)

decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。

round(11.2234,2) ==> 11.22

round(11.22794,2) ==> 11.23 四舍五入

ROUND(1234.5678,0) ==> 1235

ROUND(1234.5678,-1) ==> 1230

ROUND(1234.5678,-2) ==> 1200

ROUND(1234.5678,-3) ==> 1000

随机数

select ROUND(dbms_random.value(1,99),0） from dual

取 1-99 的随机数，并保留0位小数

7.执行计划

-- 创建索引
create index idx_city_comm on DHMP_SN_EH_D_20211027(city_name,ra_name);

-- 删除索引
drop index idx_city_comm;

-- 设置命令行显示的尺寸大小
set linesize 500;
-- 开启执行时间显示
set timing on;

-- 开启执行计划 不输出结果
set autotrace traceonly;
-- 关闭执行计划
SET AUTOTRACE OFF;

-- 清除缓存 (慎用)
alter system flush buffer_cache; 

-- 查看指定表的所有索引
select * from user_indexes where table_name='表名';

 首先设置行数：set pagesize 300;

 然后设置列宽：set linesize 150;

8.查询表的列名

SELECT column_name from user_tab_columns where table_name=upper('SNI_APP_CONVERGE_H__20211113')

9.模糊匹配表名

select table_name from user_tables where table_name like 'TEP_TABLE_%'