自定义Oracle实用小方法（持续更新）

目录

目标

功能列表

一键生成javabean

生成Mybatis插入格式

生成查询格式

查看表和列的备注

随机生成日期

生成JSON格式的数据

生成随机数

---

目标

简化Oracle日常开发，包括：自动生成测试数据、一键生成SQL、查询相关表集合等。

---

功能列表

一键生成javabean

功能说明：输入表名称，获取表的所有字段并组装成javabean。

--生成java实体类
select 
replace(
wm_concat('//'||a.comments ||CHR(10) ||'private '|| 
(
CASE 
WHEN lower(b.data_type)='number' then 'Long/Integer/BigDecimal'
WHEN lower(b.data_type)='varchar2' then 'String'
WHEN lower(b.data_type)='date' then 'Date'
end)
|| ' '|| lower(b.column_name) ||';'||CHR(10) )  
,',',''
) javaBean
from user_col_comments a inner join user_tab_columns b on a.table_name=b.table_name and a.column_name=b.column_name
and a.table_name = upper('表名称') 
;

调用&返回结果：

//发布状态 0未发布 1已发布
private Long/Integer releaseflag;
//发布时间
private Date releasetime;
//创建人
private String create_user;
//创建时间
private Date create_time;
//更新人
private String upuser;
//更新时间
private Date uptime;
//状态，-1删除，0正常
private Long/Integer state;
//线上销售模板ID
private Long/Integer moduleid;
//模板名称
private String modulename;
//备注
private String remark;

---

生成Mybatis插入格式

功能说明：输入表名称，获取Mybatis插入格式。

--生成Mybatis插入格式。
select 
'INSERT INTO '|| a.table_name||  '('  ||
wm_concat( 
CHR(10) ||'<!--'||b.comments||'-->'||CHR(10) ||
 lower(a.column_name) 
)  || CHR(10) ||')VALUES('||
wm_concat( CHR(10) ||
'#{'|| lower(a.column_name)||',jdbcType='||
(
CASE 
WHEN lower(data_type)='number' then 'NUMERIC'
WHEN lower(data_type)='varchar2' then 'VARCHAR'
WHEN lower(data_type)='date' then 'TIMESTAMP'
end
)
||'}' 
)
||CHR(10)||')' mybatisInsert
from user_tab_columns a inner join     user_col_comments  b  
on  b.table_name=a.table_name and a.column_name=b.column_name
where 
a.table_name = upper('表名称')   
group by a.table_name  ;

调用&返回结果：

INSERT INTO ONLINE_RECORDS_STORES(
<!--线上商品发布门店记录主键ID-->
recstoreid,
<!--状态，-1删除，0正常-->
state,
<!--更新时间-->
uptime,
<!--更新人-->
upuser,
<!--创建时间-->
create_time,
<!--创建人-->
create_user,
<!--发布失败原因-->
lossreason,
<!--发布状态 0发布成功 1发布失败-->
releaseflag,
<!--平台 4新美大 20饿了么2.0-->
o2otype,
<!--线上门店名称-->
o2ostoresname,
<!--线上门店ID-->
o2ostoresuid,
<!--线下门店名称-->
storename,
<!--线下门店ID-->
storeuid,
<!--线上商品发布记录主键ID(与线上商品发布记录信息表ONLINE_PRODUCTS_RECORDS.RECORDID关联)-->
recordid
)VALUES(
#{recstoreid,jdbcType=NUMERIC},
#{state,jdbcType=NUMERIC},
#{uptime,jdbcType=TIMESTAMP},
#{upuser,jdbcType=VARCHAR},
#{create_time,jdbcType=TIMESTAMP},
#{create_user,jdbcType=VARCHAR},
#{lossreason,jdbcType=VARCHAR},
#{releaseflag,jdbcType=NUMERIC},
#{o2otype,jdbcType=NUMERIC},
#{o2ostoresname,jdbcType=VARCHAR},
#{o2ostoresuid,jdbcType=NUMERIC},
#{storename,jdbcType=VARCHAR},
#{storeuid,jdbcType=NUMERIC},
#{recordid,jdbcType=NUMERIC}
)

---

生成查询格式

功能说明：输入表名称，获取查询格式。

--生成查询格式。
select 
'SELECT'||
wm_concat( 
 CHR(10) ||lower(column_name) ||' ' ||lower(column_name)
)||CHR(10)  ||'FROM'||CHR(10) ||  table_name ||' WHERE'    mybatisInsert
from user_tab_columns where table_name = upper('表名称') group by table_name ;

调用&返回结果：

SELECT
flavorprice flavorprice,
state state,
uptime uptime,
upuser upuser,
create_time create_time,
create_user create_user,
price price,
flavorflag flavorflag,
flavorname flavorname,
flavorid flavorid,
flavorclassid flavorclassid,
moduleid moduleid,
mproductid mproductid,
onlineflavorid onlineflavorid
FROM
ONLINE_MODULE_FLAVOR WHERE

---

查看表和列的备注

--表备注
select table_name,comments,table_type from user_tab_comments where 
comments like '%备注字段的关键字%' 
and table_name LIKE '%表名称%' 
ORDER BY table_name;
--列备注
select lower(column_name),comments from user_col_comments where table_name='表名称' ORDER BY column_name;

调用&返回结果：

--表备注
ONLINE_MODULE_PRODUCTS	线上销售模板商品信息表	TABLE
--列备注
create_time	创建时间
create_user	创建人
imgsync	图片是否同步到第三方: 0同步 1不同步
min_buy_num	最小购买量
moduleid	线上销售模板ID(与线上销售模板信息表ONLINE_PRODUCTS_MODULE.MODULEID关联)
mprodclassuid	线上销售模板商品类别ID(与线上销售模板商品类别信息表ONLINE_MODULE_PRODCLASS.MPRODCLASSUID关联)
mproductid	线上销售模板商品主键ID
offclassuname	线下商品类别名称
offpackageprice	线下套餐价格

---

随机生成日期

--当前日期数
SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL; 
--2020年1月1日的日期数
SELECT TO_CHAR(TO_DATE('2020-01-01','yyyy-mm-dd '),'J') FROM DUAL; 
--2020年的一个随机日期
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2458850,2458850+365)),'J') FROM DUAL;

---

生成JSON格式的数据

-- 生成json数据
SELECT '[' || CHR(10) || '{"其他参数名称":"其他参数值","items名称":[{' || CHR(10) || WM_CONCAT('"' || lower(b.column_name) || '":' || '"' || (
		SELECT CASE 
				WHEN lower(b.data_type) = 'number' THEN '1'
				WHEN lower(b.data_type) = 'varchar2' THEN '测试数据'
				WHEN lower(b.data_type) = 'date' THEN to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
			END
		FROM dual
	) || '"' || CHR(10)) || '}]}]' AS json数据
FROM user_col_comments a
	INNER JOIN user_tab_columns b ON a.table_name = b.table_name
AND a.column_name = b.column_name
AND a.table_name = upper('表名称') ;

---

生成随机数

--随机整数[2,1]
select ROUND(dbms_random.value(1,2)) value from dual;