Oracle 学习之 DML 语句

1. 数据库

2. 表

2.1. SELECT

SELECT 
	* 
FROM 
	SYS_USER
WHERE
	AGE > 18;

2.2. INSERT

INSERT INTO SYS.SYS_USER(
	ID, 
	USER_NAME, 
	AGE, 
	SEX, 
	CRT_BY, 
	CRT_TM,
	UPD_BY,
	UPD_TM
) VALUES (
	sys_guid(), 
	'ZHANGSAN', 
	18, 
	'1', 
	'100001', 
	sysdate,
	'100001',
	to_date('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
);

2.3. UPDATE

UPDATE
	SYS_USER
SET 
	USER_NAME = 'ZHANGSAN',
	AGE = 18
WHERE
	USER_ID = '0001';

2.4. DELETE

DELETE 
	SYS_USER
WHERE 
	USER_ID = '0001';

2.5. MERGE INTO

MERGE INTO 
	SYS_USER su
USING (
	<foreach collection = "list" item = "item" separator="UNION ALL">
	SELECT 
		#{req.id} AS id,
		#{req.userName} AS userName,
		#{req.age} AS age,
		#{req.sex} AS sex,
		#{req.crtBy} AS crtBy,
		#{req.crtTm} AS crtTm,
		#{req.updBy} AS updBy,
		#{req.updTm} AS updTm
	FROM dual
	</foreach>
) t
ON (
	su.id = t.id
)
WHEN MATCHED THEN 
	UPDATE SET 
		su.USER_NAME = t.userName,
		su.AGE = t.age,
		su.SEX = t.sex,
		su.UPD_BY = t.updBy,
		su.UPD_TM = t.updTm
WHEN NOT MATCHED THEN 
	INSERT (
		su.ID,
		su.USER_NAME,
		su.AGE,
		su.SEX,
		su.CRT_BY,
		su.CRT_TM,
		su.UPD_BY,
		su.UPD_TM
	) VALUES (
		sys_guid(),
		t.userName,
		t.age,
		t.sex,
		t.crtBy,
		t.crtTm,
		t.updBy,
		t.updTm
	)

3. 字段

3.1. 数据类型

3.2. 特殊场景

4.