1.触发器
创建触发器（id自增）
前提：主键是number，不是字符串类型或其他
COMMIT;
-- CREATE SEQUENCE
CREATE SEQUENCE 序列名
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
COMMIT;
CREATE OR REPLACE TRIGGER 触发器名
BEFORE INSERT ON 表名
FOR EACH ROW
DECLARE
-- LOCAL VARIABLES HERE
BEGIN
SELECT 序列名.NEXTVAL INTO :NEW.ID FROM DUAL;
END 触发器名;
CREATE OR REPLACE TRIGGER TIB_MT_WAREHOUSE
BEFORE INSERT ON MT_WAREHOUSE
FOR EACH ROW
DECLARE
-- LOCAL VARIABLES HERE
BEGIN
SELECT SEQ_MT_WAREHOUSE.NEXTVAL INTO :NEW.ID FROM DUAL;
END TIB_MT_WAREHOUSE;
在这里插入代码片
###### 1.1查看库中所有的触发器
select trigger_name from all_triggers
1.2查看某张表的触发器
select trigger_name from all_triggers where table_name='XXX';
1.3查询触发器详细信息
select text from all_source where type='TRIGGER' AND name='TR_XXX';
2层次查询; 树结构查询；父子孙级查询
2.1
SELECT 字段
FROM 表名
--WHERE
CONNECT BY PRIOR FD.ID_ = FD.PARENT_ID
START WITH FD.PARENT_ID IS NULL
2.1.1包括分页
<select id="listForPageV2" resultMap="FADeptDTOMap">
<![CDATA[
SELECT *
FROM (
SELECT
]]>
ROWNUM RN,
<include refid="baseFields" />
<![CDATA[
FROM FA_DEPT FD
]]>
<include refid="queryCondition"/>
CONNECT BY PRIOR FD.ID_ = FD.PARENT_ID
START WITH FD.PARENT_ID IS NULL
<if test="qo != null and qo.orderBySql != null and qo.orderBySql != '' ">
ORDER SIBLINGS BY ${qo.orderBySql}
</if>
<![CDATA[
) P
WHERE P.RN > #{start, jdbcType=INTEGER}
AND P.RN <= #{end, jdbcType=INTEGER}
]]>
</select>
3判断结构
3.1
select
case
when 字段 = 条件 then 自定义值1
when 字段 is null then 自定义值1
else 自定义值2
end as 别名
from table
coalesce括号中的值没限制数量，但值类型必须相同。
val1为null时，判断val2是否为null，不为null则选择val2,若为null，则判断下一个是否为null
select coalesce(val1,val2,select Max(字段)...)
nvl只能放两个值，nvl2只能放三个值，值得类型没有限制
select nvl(val1,val2) from 表名
selcet nvl2(val1,val2,val3) from 表名
ifnull(列名,10) 若为null,转换为固定值10
if(flag,val1,val2) 若flag为true,则选择val1;false则选择val2
3.2内容转变
select t.*,decode(sex,0,'男',1,'女') from 表名 t
replasce(列,'a','b') 若遇到a则替换成b
4.将值放到一起
4.1将多行的值放到一行
listagg(字段名,分隔符)
order by 是放到一组中时排序，如字段中为 1,2,3 或3,2.1
select listagg(mcm2.material_id,',') within group(ORDER BY mcm2.material_id)
from MT_CONFIG_MATERIAL mcm2
4.2将多列的值放到一列
select t.config||t.desc_||t.vendor,t.component from MT_MATERIAL t
5.删除
清空表中所有数据
TRUNCATE TABLE 表名
删除表中输有数据
Delete From tableName
Delete From 表名 Where 条件
truncate、delete 清空表数据的区别 :
1> truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)
2> truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因
3> truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的 ID数。而 delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录，只能使用 DELETE语句配合 where条件
6.新增
6.1单条数据插入
在mapper中方法为
public Integer add(@Param(“relatedScrap”)RelatedScrap relatedScrap);
其中若无注解，则SQL中直接写字段
INSERT INTO MT_MATERIAL_SCRAP MMS
(
MMS.PROJECT_ID,
MMS.MILESTONE_ID,
MMS.PARENT_ID,
MMS.COMPONENT,
MMS.CREATOR,
MMS.CREATOR_EMPNO,
MMS.ADD_TIME,
MMS.LAST_EDITOR,
MMS.LAST_EDITOR_EMPNO,
MMS.EDIT_TIME
)
VALUES
(
#{relatedScrap.projectId, jdbcType=NUMERIC},
#{relatedScrap.milestoneId, jdbcType=NUMERIC},
#{relatedScrap.parentId, jdbcType=NUMERIC},
#{relatedScrap.component, jdbcType=VARCHAR},
#{relatedScrap.creator, jdbcType=NVARCHAR},
#{relatedScrap.creatorEmpNo, jdbcType=NVARCHAR},
SYSDATE,
#{relatedScrap.creator, jdbcType=NVARCHAR},
#{relatedScrap.creatorEmpNo, jdbcType=NVARCHAR},
SYSDATE
)
6.2多条数据插入
SQL写法
INSERT ALL
INTO 表名(字段1，字段2，字段3) VALUES(值1，值2，值3)
INTO 表名(字段1，字段2，字段3) VALUES(值4，值5，值6)
INTO 表名(字段1，字段2，字段3) VALUES(值7，值8，值9)
SELECT 1 FROM DUAL
mybatis写法
参数一定要加@Param(“rsList”)注解
public Integer addList(@Param(“rsList”)List rsList);
INSERT ALL
<foreach collection="rsList" item="obj" index="index">
INTO MT_MATERIAL_SCRAP
<trim prefix="(" suffix=")" suffixOverrides="," >
PROJECT_ID,
MILESTONE_ID,
PARENT_ID,
COMPONENT,
CREATOR,
CREATOR_EMPNO,
ADD_TIME,
LAST_EDITOR,
LAST_EDITOR_EMPNO,
EDIT_TIME
</trim>
VALUES
(
#{obj.projectId, jdbcType=NUMERIC},
#{obj.milestoneId, jdbcType=NUMERIC},
<if test="obj.parentComponent != null">
(SELECT MMS.ID FROM MT_MATERIAL_SCRAP MMS WHERE MMS.PARENT_ID IS NULL AND MMS.COMPONENT = #{obj.parentComponent, jdbcType=NUMERIC}),
</if>
<if test="obj.parentComponent == null ">
NULL,
</if>
#{obj.component, jdbcType=VARCHAR},
#{obj.creator, jdbcType=NVARCHAR},
#{obj.creatorEmpNo, jdbcType=NVARCHAR},
SYSDATE,
#{obj.creator, jdbcType=NVARCHAR},
#{obj.creatorEmpNo, jdbcType=NVARCHAR},
SYSDATE
)
</foreach>
SELECT 1 FROM DUAL
6.3多条数据插入，存在则更新，不存在则插入
在并发量比较高的时候，可能两个线程都查询某个记录不存在，所以会执行两次插入，然后其中一条必然会因为主键（这里说的主键不是递增主键）冲突而失败。
MERGE INTO…语句
INSERT INTO 表名 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
7.在SQL中分割字符串
7.1substr(字符串,截取开始位置,截取长度) //返回截取的字，截取长度可不填
substr(‘Hello World’,0,1) //返回结果为 ‘H’ *从字符串第一个字符开始截取长度为1的字符串
substr(‘Hello World’,1,1) //返回结果为 ‘H’ *0和1都是表示截取的开始位置为第一个字符
select substr('Hello World',-3,3) value from dual;
7.2instr 函数：返回子字符串在源字符串中的位置
语法：INSTR(源字符串，查找的字符串，[start]，[show_time])
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错。
7.3将substr与instr结合
获取一列数据中的“_”符号之前的数据
SELECT
SUBSTR (SOURCE_CODE, 1, INSTR (SOURCE_CODE, '_', 1, 1) - 1) AS CITY
FROM
TABLE_CODE_TEST
该处引用https://blog.csdn.net/xiaochenXIHUA/article/details/119743090这篇文章
7.4将字符串传成多行
with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
select regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^/]+'))
7.5 多行转换成字符串
方法一
select replace(WMSYS.WM_CONCAT(a.name),',','；') from user a
方法二
select listagg(mcm2.material_id,',') within group(ORDER BY mcm2.material_id)
from MT_CONFIG_MATERIAL mcm2
8分页
8.1分页1
SELECT
*
FROM (
SELECT
DISTINCT MVCM.CONFIG FROM MT_V$_CONFIG_MATERIAL MVCM
ORDER BY MVCM.CONFIG
) P
WHERE ROWNUM > 0 AND ROWNUM <= 8
8.2分页2
SELECT
*
FROM (
SELECT
<if
test="qo != null and qo.orderBySql != null and qo.orderBySql != '' ">
ROW_NUMBER() OVER( ORDER BY ${qo.orderBySql} ) RN,
</if>
MVCM.CONFIG
FROM MT_V$_CONFIG_MATERIAL MVCM
ORDER BY MVCM.CONFIG
) P
WHERE P.RN > #{start, jdbcType=INTEGER}
AND P.RN <= #{end, jdbcType=INTEGER}
9已知外键的查询
原文作者：https://blog.csdn.net/ibhjvbg/article/details/123344265
班级表（A_CLASS）
学生表( STUDENT)
首先会执行外循环（select * from student）
外循环返回的结果每一行都会拿着去内层循环执行
Exists 方法描述如果在 Dictionary对象中指定的关键字存在，返回True，若不存在，返回False
例如 exist P表示P不空时为真; not exist P表示p为空时为真in表示一个标量和一元关系的关系。
例如：s in P表示当s与P中的某个值相等时为真; s not in P 表示s与P中的每一个值都不相等时为真。
注意
1.exists执行外循环后，会拿着外循环的值，去内层查询，如果查询到就直接返回true，并且终止本次循环，如果是false，则会一直执行，直至循环完成还为false，则本次内循环不符合条件;
2.内层的判断条件不要写！=;查询的结果会不尽人意
10删除唯一索引
//第一步 删除表与索引之间的联系
ALTER TABLE MT_MATERIAL DROP CONSTRAINT PK_MT_MATERIAL ;
//第二步 执行删除索引语句
DROP INDEX PK_MT_MATERIAL ;
//第三步 若要重新创建索引则执行
-- 新增唯一索引
create unique index PK_MT_MATERIAL on MT_MATERIAL (ID);
在where后增加判断条件
select * from 表名 where 1= (CASE WHEN 字段1 = '值1' THEN 1 ELSE 0 END) and 字段2 = '值2'
格式说明
简单Case函数
格式说明
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2.......
else 默认值 end
eg:
select
case job_level
when '1' then '1111'
when '2' then '1111'
when '3' then '1111'
else 'eee' end
from dbo.employee
格式 :Case搜索函数
case
when 列名= 条件值1 then 选项1
when 列名=条件值2 then 选项2.......
else 默认值 end
eg:
update employee
set e_wage =
case
when job_level = '1' then e_wage*1.97
when job_level = '2' then e_wage*1.07
when job_level = '3' then e_wage*1.06
else e_wage*1.05
end