Oracle数据库相关操作-汇总

1.往数据库中插入时间，数据类型为varchar2

INSERT INTO T_ROBOT_AUDIO_DATA
        (
            ID,
            BUSINESSTYPE,
            NUMBERCODE,
            DEL,
            CREATETIME,
            ENDTIME,
            VOICEURL,
            WINDOW
        )
        VALUES
        (
            #{id,jdbcType=VARCHAR},
            #{businessType,jdbcType=VARCHAR},
            #{numberCode,jdbcType=VARCHAR},
            #{del,jdbcType=VARCHAR},
            to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss'),  -- 其中systimestamp为系统时间，后面转成指定格式字符串
            #{endTime,jdbcType=VARCHAR},
            #{voiceUrl,jdbcType=VARCHAR},
            #{window,jdbcType=VARCHAR}
        )

2.往数据库中插入时间，数据类型为Date

INSERT INTO T_ROBOT_AUDIO_DATA
        (
            ID,
            BUSINESSTYPE,
            NUMBERCODE,
            DEL,
            CREATETIME,
            ENDTIME,
            VOICEURL,
            WINDOW
        )
        VALUES
        (
            #{id,jdbcType=VARCHAR},
            #{businessType,jdbcType=VARCHAR},
            #{numberCode,jdbcType=VARCHAR},
            #{del,jdbcType=VARCHAR},
            SYSDATE,  -- 该字段是 oracle 获取系统时间
            #{endTime,jdbcType=VARCHAR},
            #{voiceUrl,jdbcType=VARCHAR},
            #{window,jdbcType=VARCHAR}
        )

 插入后数据显示如下：

3.oracle中两个时间字符串可以使用 比较符

UPDATE T_ROBOT_AUDIO_DATA t 
SET t.DEL = 0,
t.ENDTIME = '2019-04-19 23:59:59'

WHERE
	t.DEL = 1 
	AND createTime <= '2019-04-19 23:59:59'

4. oracle中使用 listagg() within group()  方式将查询的某个字段根据指定列合并

学生成绩表

姓名	学科	成绩
张三	语文	80
张三	数学	75

 

 

 

 

select listagg('成绩',',')within group('姓名'),姓名 from user;

-- 以姓名分组（可以使用多个字段），将查询到的 成绩 字段使用 ',' 分隔

 

查询结果

姓名	成绩
张三	80,75

 

 

 

mysql使用 group_concat() 方法实现

listagg ( e.role, ',' ) within GROUP ( ORDER BY e.role ) "roleName"  

等价于

GROUP_CONCAT(e.role ORDER BY e.role separator ',' ) "roleName"

 

 

5.oracle中使用 minus 方法剔除集合中重复的列

A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即A minus B将只去除A跟B的交集部分，对于B中存在而A中不存在的记录不会做任何操作。

使用方法：

select id ,name from t_user where id<6 minus select id from t_user where id between 3 and 7

id	name
1	name1
2	name2
3	name3
4	name4
5	name5
6	name6
7	name7

 

 

 

 

 

 

 

 

 其中：select id from t_user where id<6  查询结果为 id： 1,2,3,4,5

            select id from t_user where id between 3 and 7： 3,4,5,6,7

两集合交集为：3,4,5

所以查询的结果为：

结果

id	name
1	name1
2	name2

 

 

 

 

6.oracle中保存图片文件base64编码

oracle表结构中使用类型CLOB，java中可以使用String接收

 

 

7.oracle中建表对大小写字段敏感

 

8.oracle建表设置字段默认为系统时间

CREATE TABLE T_CCWEB_SYSTEM_LOG (
  SYSTEM_LOG_ID VARCHAR2(64) NOT NULL ,
  ACCOUNT VARCHAR2(50) NOT NULL ,
  IP_ADDRESS VARCHAR2(255) ,
  CREATE_TIME DATE DEFAULT SYSDATE  NOT NULL ,
  OPT_TYPE VARCHAR2(30) ,
  MODULE_TYPE VARCHAR2(30) ,
  CONTENT_JSON VARCHAR2(1000) ,
  DETAIL_CONTENT VARCHAR2(1000) 
);

 

 9.oracle 批量更新

 -- 讲id为1的clickCount字段加1，id为2的加2
update T_CCWEB_ACTIVITY
   SET
            clickCount =  CASE id
            when '1' then clickCount + 1
            when '2' then clickCount + 2
            END
  WHERE
            id in ('1','2')
        

mybatis 中 xml 对应如下： 

update T_CCWEB_ACTIVITY
        SET
            clickCount =  CASE id

        <foreach collection="list" item="item" separator="" index="index" open="" close="">
            when #{item.id} then clickCount + #{item.clickCount}
        </foreach>
        END
        WHERE
        id in
        <foreach collection="list" item="item" separator="," index="index" open="(" close=")">
             #{item.id}
        </foreach>

10. oracle中存储过程的创建

存储过程创建参考

create [or replace] procedure 存储过程名（param1 in type，param2 out type）
as
   变量1 类型（值范围）;
   变量2 类型（值范围）;
begin
   select count(*) into 变量1 from 表A where列名=param1；
   if (判断条件) then
        select 列名 into 变量2 from 表A where列名=param1；
        dbms_output.Put_line('打印信息');
   elsif (判断条件) then
        dbms_output.Put_line('打印信息');
   else
        raise 异常名（NO_DATA_FOUND）;
   end if;
exception
   when others then
        rollback;
end;

CREATE OR REPLACE PROCEDURE MYDEMO02 
AS 
        name VARCHAR(10);
        age NUMBER(10);
BEGIN
        name := 'xiaoming';--:=则是对属性进行赋值
        age := 18;
        dbms_output.put_line ( 'name=' || name || ', age=' || age );--这条是输出语句
END;
--存储过程调用(下面只是调用存储过程语法)
BEGIN 
    MYDEMO02();
END;

 

11. oracle中存储过程中游标的使用

-- 更新字段
create or replace procedure p_update_Activity_order is
  MAX_ORDERNUM int; -- 声明变量
--定义游标
  cursor c_test_tbl2 is  -- 将查询出来的集合赋值给 c_test_tbl2  
		SELECT
			* 
		FROM
			T_CCWEB_ACTIVITY 
		WHERE
			To_date( To_char( Trunc( SYSDATE ), 'yyyy/mm/dd' ), 'yyyy/mm/dd' ) - to_date( ENDTIME, 'yyyy-mm-dd' ) > 0;

  c_row T_CCWEB_ACTIVITY%rowtype; -- 将 c_row 属性定义为行属性（对应查询出来的一行数据）
begin
	SELECT max(ORDERNUM)+1 INTO MAX_ORDERNUM from T_CCWEB_ACTIVITY;
  --使用游标进行更新
  for c_row in c_test_tbl2 loop
	--设置过期的活动状态,设置过期活动的OrderNum字段沉底
    UPDATE T_CCWEB_ACTIVITY SET eventstatus = 0, ORDERNUM = MAX_ORDERNUM where id = c_row.id;
    MAX_ORDERNUM := MAX_ORDERNUM + 1;
  end loop;
  commit;
end;

 

12. oracle中使用定时任务

定时任务参考

-- 定时任务

declare  
job2 number;   
begin  
 dbms_job.submit(job2,                         -- 定时任务编号
     what => 'p_update_Activity_display(3);',  -- 指定存储过程
     next_date => sysdate,                -- 执行时间
     interval => 'sysdate+1/(24*60*60)'); -- 每隔1s处理一次用户表  
   commit;  
end;


-- 查询已经存在的定时任务
select job as jobno,next_date,next_sec,failures,broken from user_jobs;

 

13. mysql中table_schema

使用如下sql，得到的字段 table_schema值为数据库用户名

  select table_schema,table_name,table_comment
  FROM information_schema.tables
  WHERE table_name = 't_im_dictionary'
  ORDER BY table_name DESC;