orcal mysql 常用操作

【orcal锁表查询SQL】

SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid; 

【杀死进程】

alter system kill session '1060, 15204'; 

【oracle查询某个字段在那张表中用到】

select column_name,table_name,data_type,data_length,data_precision,data_scale 
from user_tab_columns 
where column_name='字段名';	    

mysql中的转字符：date_format(date,'%Y-%m-%d %T')  相当于oracle中的to_char()   
mysql中的转日期：   str_to_date(date,'%Y-%m-%d')  	相当于oracle中的to_date()
to_char(CREATE_DATE,'yyyy-MM-dd') = '2019-03-30';
mysql中的转数字：cast( '123 '  as  SIGNED   INTEGER)相当于oracle中的TO_NUMBER(): 

【模糊查询】

mysql: concat('%', #{area}, '%')
orcal: c.name like concat(concat('%',#{unitsname}),'%')

【时间转格式】

orcal ：to_char( b.recard_time, 'yyyy-mm-dd hh24:mi:ss' )  >= to_char(#{recardtime},'yyyy-mm-dd hh24:mi:ss')	

【orcal 批量插入:】

<if test="projectIds != null "> 
    and a.PROJECT_ID   in
    <foreach item="id" collection="projectIds" open="(" separator="," close=")">
        #{id}
    </foreach>   
</if>

【orcal插入之前获取Id】

<selectKey keyProperty="id" resultType="Integer" order="BEFORE">
	    	SELECT BIZ_PARTICIPAT_UNITS_ID_SEQ.NEXTVAL FROM DUAL
</selectKey>

【if判断字符串】

<if test="word == '1'.toString() ">and pb.word is null </if>

【orcal导出】

SELECT *
FROM (
         SELECT t1.Table_Name  AS "表名称",
                t3.comments    AS "表说明",
                t1.Column_Name AS "字段名称",
                t1.Data_Type   AS "数据类型",
                t1.Data_Length AS "长度",
                t1.NullAble    AS "是否为空",
                t2.Comments    AS "字段说明",
                t1.Data_Default   "默认值"
         FROM cols t1
                  LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name
             AND t1.Column_Name = t2.Column_Name
                  LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
         WHERE NOT EXISTS(
                 SELECT t4.Object_Name
                 FROM User_objects t4
                 WHERE t4.Object_Type = 'TABLE'
                   AND t4.Temporary = 'Y'
                   AND t4.Object_Name = t1.Table_Name
             )
         ORDER BY t1.Table_Name,
                  t1.Column_ID
     ) k
WHERE k."表名称" = 'BIZ_BUILDER'

【mysql导出】

SELECT COLUMN_NAME              列名,
       COLUMN_TYPE              数据类型,
       DATA_TYPE                字段类型,
       CHARACTER_MAXIMUM_LENGTH 长度,
       IS_NULLABLE              是否为空,
       COLUMN_DEFAULT           默认值,
       COLUMN_COMMENT           备注
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
  -- sxepp为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
    table_schema = 'sxepp'
  AND -- sx_jc_car为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称
    table_name = 'sx_jc_car'

【查询的数据添加''】

SELECT ''''||ID||''''	FROM SYS_USER WHERE PROJECT_CHECK_ID='4b11a4d91df643bf9f9ca546b414fa09'