文章目录
oracle
查看sql执行效率
SELECT
last_active_time,
fetches,
executions,
module,
loads,
elapsed_Time / 1000000 || 's' total_elapsed_Time,
TO_CHAR (
elapsed_Time / 1000000 / DECODE (executions, 0, 1, executions) * 1000,
'9999999.99'
) || 'ms' elapsed_Time,
sql_profile,
sql_id,
sql_text
FROM
v$sql
WHERE
UPPER (sql_text) LIKE '%INSERT INTO SMS_QUEUE_TIMING%'
-- AND last_active_Time >= SYSDATE - 5 / 1440
ORDER BY
3 DESC;
拿表字段排序后更新
- 验重需求,旧数据的值乱序插入的,所以字段验重会很麻烦,需要将字段的值进行排序后更新
更新的是字符串里的数字排序
- 写一个脚本进行更新该字段从小到大排序后更新回去
- 这里的
TO_NUMBER
是为了让字符串转成数字类型再排序
BEGIN
DECLARE
v_sql VARCHAR2(300);
BEGIN
FOR r IN (
SELECT ID, ROUTE_PROVINCE FROM SMS_ROUTE order by ID
) LOOP
SELECT 'UPDATE SMS_ROUTE'||' SET ROUTE_PROVINCE = '||chr(39)||tmp.new_str||chr(39)||' WHERE ID = '||r.ID INTO v_sql
FROM (SELECT LISTAGG(value, ',') WITHIN GROUP ( ORDER BY value) as new_str
FROM (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(r.ROUTE_PROVINCE, '[^,]+', 1, LEVEL))) AS value
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(r.ROUTE_PROVINCE, ',') + 1)
) tmp;
EXECUTE immediate v_sql;
COMMIT;
END LOOP;
END;
END;
/
COMMIT;
更新字符串里的字符数据
- 将字符串长度定义长点
v_sql VARCHAR2(3000);
- 就不需要和上面那样加
TO_NUMBER
了 - 排序类似这样的数据:
【爱他美自营】,【阿里巴巴】,【一汽大众】
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
查看该oracle数据库数据集,是UTF8
还是GBK
?- 若是
GBK
则程序对应排序使用String result = list.stream().sorted(Collator.getInstance(Locale.CHINA)).collect(Collectors.joining(","));
(【阿里巴巴】在前) - 若是
UTF8
则程序对应使用String result = list.stream().sorted().collect(Collectors.joining(","));
(【一汽大众】在前)
BEGIN
DECLARE
v_sql VARCHAR2(3000);
BEGIN
FOR r IN (
SELECT ID, ROUTE_SIGN,'SMS_ROUTE' as TAB_NAME FROM SMS_ROUTE order by ID
) LOOP
SELECT 'UPDATE '||r.TAB_NAME||' SET ROUTE_SIGN = '||chr(39)||tmp.new_str||chr(39)||' WHERE ID = '||r.ID INTO v_sql
FROM (SELECT LISTAGG(value, ',') WITHIN GROUP ( ORDER BY value) as new_str
FROM (SELECT TRIM(REGEXP_SUBSTR(r.ROUTE_SIGN, '[^,]+', 1, LEVEL)) AS value
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(r.ROUTE_SIGN, ',') + 1)
) tmp;
EXECUTE immediate v_sql;
COMMIT;
END LOOP;
END;
END;
修复连表查询,搜不到主表数据问题
错误示范
- 因为在最后的where 做了时间条件查询,是拿子表的字段判断,所以搜出来搜不到主表数据
SELECT
sc.CHANNEL_ID AS CHANNEL_ID,
NVL(SUM( sp.SUBMIT_SUCCESS_NUM ),0) AS SUBMIT_SUCCESS_NUM,
NVL(SUM( sp.SUBMIT_FAIL_NUM ),0) AS SUBMIT_FAIL_NUM,
NVL(SUM( sp.SEND_SUCCESS_NUM ),0) AS SEND_SUCCESS_NUM,
NVL(SUM( sp.SEND_FAIL_NUM ),0) AS SEND_FAIL_NUM,
NVL( SUM( sp.SUBMIT_SUCCESS_NUM ) + SUM( sp.SUBMIT_FAIL_NUM ) + SUM( sp.SEND_SUCCESS_NUM ) + SUM( sp.SEND_FAIL_NUM ),0) AS TOTAL_NUM
FROM
SMS_CHANNEL sc
LEFT JOIN STA_PROVINCE sp ON sc.CHANNEL_ID = sp.CHANNEL_ID
<where>
sp.SEND_DATE >= #{qo.startTime,jdbcType=TIMESTAMP}
AND sp.SEND_DATE <![CDATA[ <= ]]> # {qo.endTime,jdbcType=TIMESTAMP}
<if test="qo.mobileType != null">
AND sp.MOBILE_TYPE = #{qo.mobileType,jdbcType=INTEGER}
</if>
<if test="qo.channelIdList != null and qo.channelIdList.size()>0">
AND sc.CHANNEL_ID in
<foreach collection="qo.channelIdList" close=")" open="(" item="channelId" separator=",">
#{channelId,jdbcType=INTEGER}
</foreach>
</if>
</where>
GROUP BY
sc.CHANNEL_ID
ORDER BY
TOTAL_NUM DESC
正确示范
- 将时间查询的判断 写在连表子表里的
where
中,整体搜出来就可以搜到主表数据了
SELECT
sc.CHANNEL_ID AS CHANNEL_ID,
NVL(SUM( sp.SUBMIT_SUCCESS_NUM ),0) AS SUBMIT_SUCCESS_NUM,
NVL(SUM( sp.SUBMIT_FAIL_NUM ),0) AS SUBMIT_FAIL_NUM,
NVL(SUM( sp.SEND_SUCCESS_NUM ),0) AS SEND_SUCCESS_NUM,
NVL(SUM( sp.SEND_FAIL_NUM ),0) AS SEND_FAIL_NUM,
NVL( SUM( sp.SUBMIT_SUCCESS_NUM ) + SUM( sp.SUBMIT_FAIL_NUM ) + SUM( sp.SEND_SUCCESS_NUM ) + SUM( sp.SEND_FAIL_NUM ),0) AS TOTAL_NUM
FROM
SMS_CHANNEL sc
LEFT JOIN (select * from STA_PROVINCE WHERE
SEND_DATE >= #{qo.startTime,jdbcType=TIMESTAMP}
AND SEND_DATE <![CDATA[ <= ]]> #{qo.endTime,jdbcType=TIMESTAMP}
)sp
ON sc.CHANNEL_ID = sp.CHANNEL_ID
<where>
<if test="qo.mobileType != null">
AND sp.MOBILE_TYPE = #{qo.mobileType,jdbcType=INTEGER}
</if>
<if test="qo.channelIdList != null and qo.channelIdList.size()>0">
AND sc.CHANNEL_ID in
<foreach collection="qo.channelIdList" close=")" open="(" item="channelId" separator=",">
#{channelId,jdbcType=INTEGER}
</foreach>
</if>
</where>
GROUP BY
sc.CHANNEL_ID
ORDER BY
TOTAL_NUM DESC
根据另一张新表,批量更新旧表新字段数据
SMS_CHANNEL
原来的字段有LANDING_AREA
代表省市,值为:省编码-市编码
,现在新增省市id(SYS_PROVINCE_AREA_ID
)字段作为外键id关联省市表SYS_PROVINCE_AREA
, 新增新字段SYS_PROVINCE_AREA_ID
来替代原来的LANDING_AREA
存储方式- 所以一开始新增这个字段 表里肯定没这个字段数据的,需要用sql进行初始化赋值
- 省市表
SYS_PROVINCE_AREA
字段有 省名,省编码,市,市编码
UPDATE SMS_CHANNEL t1
SET SYS_PROVINCE_AREA_ID = (
SELECT
spa.id
FROM
SYS_PROVINCE_AREA spa,
(
SELECT
SUBSTR( LANDING_AREA, 1, INSTR( LANDING_AREA, '-' ) - 1 ) AS before,
SUBSTR( LANDING_AREA, INSTR( LANDING_AREA, '-' ) + 1 ) AS after,
LANDING_AREA
FROM
SMS_CHANNEL sct1
) sc
WHERE
spa.PROVINCES_CODE = sc.before
AND spa.AREA_CODE = sc.after
AND t1.LANDING_AREA = sc.LANDING_AREA
AND ROWNUM = 1
);
获取当天每个时段时间
- oracle写法
SELECT
TO_CHAR( TO_DATE( '2022-08-08 00 : 00 : 00', 'yyyy-mm-dd hh24:mi:ss' ) - ( 25-Rownum ) / 24, 'hh24:mi:ss' ) AS TEMP_TIME
FROM
DUAL CONNECT BY ROWNUM <= 24
- 橙色部分为传参:当天日期时间零点
获取过去一周时间
SELECT
TO_CHAR(( TO_DATE( '2023-02-13 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) + ROWNUM - 1 ), 'yyyy-mm-dd' ) AS TEMP_TIME
FROM
DUAL CONNECT BY ROWNUM <= TO_DATE('2023-02-19 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) - TO_DATE( '2023-02-13 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) + 1
筛选出传参中含有表字段数据的数据出来
- oracle写法,这里的
大内容
作为传参值 - 若数据库字段
content
的数据是"内", 则会被检索出来
select content from SMS_KEYWORDS where instr('大内容',content ) > 0
删除重复的数据
- 表名:SMS_WHITE_NO
- 在mapper.xml文件里的写法
DELETE FROM SMS_WHITE_NO a WHERE rowid <![CDATA[ < ]]> (SELECT MAX(b.rowid) FROM SMS_WHITE_NO b WHERE a.TELEPHONE=b.TELEPHONE and a.APPLY_TYPE = b.APPLY_TYPE )
插入数据,若有则更新操作
<update id="checkInitDefaultSysCodeExist">
BEGIN
<foreach collection="initDefaultSysCode" item="c">
MERGE INTO SYS_REPORT_CODE a USING (SELECT #{c.sysCode,jdbcType=VARCHAR} as SYS_CODE FROM dual) b ON (a.SYS_CODE = b.SYS_CODE)
WHEN MATCHED THEN
UPDATE SET a.UPDATE_TIME = a.UPDATE_TIME
WHEN NOT MATCHED THEN
INSERT(ID, SYS_CODE, SYS_DESC,SCOPE_TYPE, MENO, CREATE_TIME, UPDATE_TIME, STATUS)
VALUES(SYS_REPORT_CODE_SEQ.nextval,#{c.sysCode,jdbcType=VARCHAR},#{c.sysDesc,jdbcType=VARCHAR},#{c.scopeType,jdbcType=INTEGER},#{c.meno,jdbcType=VARCHAR},SYSDATE,SYSDATE,#{c.status,jdbcType=INTEGER});
</foreach>
END;
</update>
- 示例
搜索匹配数据库字段值-
前面的值
- 使用正则表达式,
LANDING_AREA
为数据库字段,qo.provinceCodeStr
是传参 LANDING_AREA
的值可能是11-152
,11-155
,12-155
, 要搜索"-“前面是11的值,那么传参就传"11”,通过以下函数匹配检索出来
SELECT
CHANNEL_ID
FROM
SMS_CHANNEL
WHERE
regexp_like ( LANDING_AREA, regexp_replace( '^str$|^str-|-str-|*-str$', 'str', #{qo.provinceCodeStr,jdbcType=VARCHAR} ) )
mysql
批量插入数据
-
mapper
-
xml写法
<insert id="insertBatch">
begin
<foreach collection="list" item="item">
INSERT INTO SMS_QUEUE (<include refid="Base_Column_List_All"/>)values (
#{item.id,jdbcType=VARCHAR}, #{item.content,jdbcType=VARCHAR}, #{item.mobiles,jdbcType=VARCHAR},
#{item.mobilesNum,jdbcType=INTEGER}, #{item.msgNum,jdbcType=INTEGER}, #{item.smsId,jdbcType=VARCHAR},
#{item.smsIdPrefix,jdbcType=VARCHAR},#{item.smsIdSuffix,jdbcType=VARCHAR},
#{item.mobileType,jdbcType=INTEGER},
#{item.serverId,jdbcType=INTEGER}, #{item.channelIdFirst,jdbcType=INTEGER},
#{item.channelIdSecond,jdbcType=INTEGER},
#{item.status,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP},#{item.sendTime,jdbcType=TIMESTAMP},
#{item.auditAuthor,jdbcType=VARCHAR},#{item.auditTime,jdbcType=TIMESTAMP},#{item.customerNo,jdbcType=INTEGER},
#{item.agentId,jdbcType=INTEGER},#{item.enterpriseId,jdbcType=INTEGER},#{item.userId,jdbcType=VARCHAR},
#{item.port,jdbcType=VARCHAR}, #{item.postType,jdbcType=INTEGER},#{item.priority,jdbcType=INTEGER},
#{item.impLogId,jdbcType=INTEGER},#{item.filtrateContent,jdbcType=VARCHAR},#{item.clientType,jdbcType=INTEGER},
#{item.userPrice,jdbcType=DECIMAL},#{item.channelPrice,jdbcType=DECIMAL},#{item.applyType,jdbcType=INTEGER},#{item.apiUserId,jdbcType=VARCHAR},
#{item.accountType,jdbcType=INTEGER},#{item.payType,jdbcType=INTEGER},#{item.batchNo,jdbcType=VARCHAR},#{item.isRoute,jdbcType=INTEGER},
#{item.pkNumber,jdbcType=INTEGER},#{item.pkTotal,jdbcType=INTEGER},#{item.connType,jdbcType=INTEGER},#{item.standardContentSeq,jdbcType=VARCHAR},
#{item.sourcePort,jdbcType=VARCHAR},#{item.sourceSign,jdbcType=VARCHAR},#{item.sign,jdbcType=VARCHAR},#{item.allContent,jdbcType=VARCHAR},
#{item.sysInjectDesc,jdbcType=VARCHAR},#{item.billType,jdbcType=INTEGER},#{item.completeContentUniKey,jdbcType=VARCHAR},
#{item.signAppendFlag,jdbcType=INTEGER},#{item.signInitialPosition,jdbcType=INTEGER},#{item.smsTiming,jdbcType=INTEGER},
#{item.rulesAppId,jdbcType=INTEGER}
);
</foreach>
end;
</insert>
<insert id="insertBatchT">
INSERT INTO SMS_QUEUE (<include refid="Base_Column_List_All"/>)
<foreach collection="list" item="item" separator="UNION ALL">
select
#{item.id,jdbcType=VARCHAR}, #{item.content,jdbcType=VARCHAR}, #{item.mobiles,jdbcType=VARCHAR},
#{item.mobilesNum,jdbcType=INTEGER}, #{item.msgNum,jdbcType=INTEGER}, #{item.smsId,jdbcType=VARCHAR},
#{item.smsIdPrefix,jdbcType=VARCHAR},#{item.smsIdSuffix,jdbcType=VARCHAR},
#{item.mobileType,jdbcType=INTEGER},
#{item.serverId,jdbcType=INTEGER}, #{item.channelIdFirst,jdbcType=INTEGER},
#{item.channelIdSecond,jdbcType=INTEGER},
#{item.status,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP},#{item.sendTime,jdbcType=TIMESTAMP},
#{item.auditAuthor,jdbcType=VARCHAR},#{item.auditTime,jdbcType=TIMESTAMP},#{item.customerNo,jdbcType=INTEGER},
#{item.agentId,jdbcType=INTEGER},#{item.enterpriseId,jdbcType=INTEGER},#{item.userId,jdbcType=VARCHAR},
#{item.port,jdbcType=VARCHAR}, #{item.postType,jdbcType=INTEGER},#{item.priority,jdbcType=INTEGER},
#{item.impLogId,jdbcType=INTEGER},#{item.filtrateContent,jdbcType=VARCHAR},#{item.clientType,jdbcType=INTEGER},
#{item.userPrice,jdbcType=DECIMAL},#{item.channelPrice,jdbcType=DECIMAL},#{item.applyType,jdbcType=INTEGER},#{item.apiUserId,jdbcType=VARCHAR},
#{item.accountType,jdbcType=INTEGER},#{item.payType,jdbcType=INTEGER},#{item.batchNo,jdbcType=VARCHAR},#{item.isRoute,jdbcType=INTEGER},
#{item.pkNumber,jdbcType=INTEGER},#{item.pkTotal,jdbcType=INTEGER},#{item.connType,jdbcType=INTEGER},#{item.standardContentSeq,jdbcType=VARCHAR},
#{item.sourcePort,jdbcType=VARCHAR},#{item.sourceSign,jdbcType=VARCHAR},#{item.sign,jdbcType=VARCHAR},#{item.allContent,jdbcType=VARCHAR},
#{item.sysInjectDesc,jdbcType=VARCHAR},#{item.billType,jdbcType=INTEGER},#{item.completeContentUniKey,jdbcType=VARCHAR},
#{item.signAppendFlag,jdbcType=INTEGER},#{item.signInitialPosition,jdbcType=INTEGER},#{item.smsTiming,jdbcType=INTEGER},
#{item.rulesAppId,jdbcType=INTEGER}
from dual
</foreach>
</insert>