一、前言
假设有一张用户表system_user，里面包含主键id、姓名name、年龄age的列属性，用户足迹表system_user_history，里面包含主键id、外键user_id、足迹name。
二、DataManipulitionLanguage 数据操作语言
insert 新增操作
insert into system_user values (1,'wjx',25); -> 默认全字段新增，按列属性顺序依次填入参数
insert into system_user (id,name) values (1,'wjx'); -> 显式指定需要新增的列属性
insert into system_user select id,name,age from system_user; -> 默认全字段新增，通过查询语句赋值
insert into system_user (id,name) select id,name from system_user; -> 显式指定需要新增的列属性
update 更新操作
update system_user set name = 'wjx' where id = 1; -> 显式指定需要更新的列属性
delete 删除操作
delete from system_user where id = 1; -> 显式指定需要删除的行
select 查询操作
select 1 from dual; -> 虚拟表
select * from system_user; -> 真实表
full outer join / full join 全外连接，a表有x行，b表有y行，总共为x*y行
select * from system_user,system_user_history; -> 方式一
select * from system_user full outer join system_user_history; -> 方式二
left outer join / left join 左外连接，a表有x行，b表有y行，总共为大于等于x行
select * from system_user su,system_user_history suh where su.id = suh.user_id(+); -> 方式一
select * from system_user su left join system_user_history suh on su.id = suh.user_id; -> 方式二
right outer join / right join 右外连接，a表有x行，b表有y行，总共为大于等于y行
select * from system_user su,system_user_history suh where su.id(+) = suh.user_id; -> 方式一
select * from system_user su right join system_user_history suh on su.id = suh.user_id; -> 方式二
inner join / join 内连接，匹配两个表都符合条件的行
select * from system_user su inner join system_user_history suh on su.id = suh.user_id;
group by 分组，分组条件必须作为查询列
select id from system_user group by id having id = 1
Merge 联合操作，有则更新，无则新增
MERGE INTO
SYSTEM_USER_DETAIL SUD
USING
(SELECT * FROM SYSTEM_USER where AGE = 20) SU
ON
(SU.USER_ID = SUD.USER_ID)
WHEN MATCHED THEN
UPDATE SET SUD.USER_ID = SU.USER_ID
WHEN NOT MATCHED THEN
INSERT (SUD.USER_ID) VALUES (SU.USER_ID);
三、运算符号
alias 别名
select id alias_id from system_user; -> 空格
select id as alias_id from system_user; -> as
select id as "alias_id" from system_user; -> 双引号
|| 连接符
select id || '-' || name || '-' || age from system_user; -> 以id-name-age格式输出属性值
in(set) 等于集合里某个元素即返回
select * from system_user where id in (1,2,3); -> 返回id为1的行
all(set) 大于/小于集合里的所有元素即返回
select * from system_user where id > all (0,2,4); -> 不返回行，id为1的行不满足大于2/4
any(set) 大于/小于集合里的某个元素即返回
select * from system_user where id > any(0,2,4); -> 返回id为1的行，id为1的行满足大于0
distinct 去重
select distinct * from system_user;
= 等于、> 大于、>= 大于等于、< 小于、<= 小于等于、!= 不等于、<> 不等于
select * from system_user where id = 1;
select * from system_user where id > 1;
select * from system_user where id >= 1;
select * from system_user where id < 1;
select * from system_user where id <= 1;
select * from system_user where id != 1;
select * from system_user where id <> 1;
between and 范围闭区间
select * from system_user where id between 1 and 10; -> id在1到10的行
like 模糊查询，%匹配任意多个字符，_匹配任意单个字符，escape 转义关键字
select * from system_user where name like '%\_%' escape '\'; -> 匹配name带有_的行
is null 判断空
select * from system_user where age is null;
is not null 判断非空
select * from system_user where age is not null;
and 并
select * from system_user where id = 1 and name = 'wjx';
or 或
select * from system_user where id = 2 or name = 'wjx';
not 非，搭配其它关键字使用
select * from system_user where id not between 1 and 10; -> id不在1到10范围内
select * from system_user where id not in (1,2,3); -> id不等于1/2/3
order by 排序，desc降序、asc升序
select * from system_user order by id asc,name desc; -> id升序、name降序来排列行
case when then else end 多条件分支
select case id when 1 then 1 else 0 end from system_user;
decode 多条件分支
select decode(id,1,1,0) from system_user;
case id when 1 then 1 else 0 end;
savepoint 设置保存点
savepoint point_user;
rollback 回滚事务
rollback; -> 全回滚
rollback to savepoint point_a; -> 回滚到某个保存点
commit 提交事务
commit;
四、内置函数
lower 全小写
lower('A'); -> a
upper 全大写
upper('a'); -> A
initcap 首字母大写，空格隔开被认为不同词汇
initcap('hello world'); -> Hello World
concat 连接
concat('a','b'); -> ab
concat(concat('%', 'a'), '%'); -> %a%
substr 截取字符串
substr('abcdefg',1,5); -> abcde，从第1个字符开始，截取5个
length 字符串长度
length('abcde'); -> 5
regexp_replace 根据正则表达式替换字符串
regexp_replace(str, ',', ''); -> 字符串/被替换字符/替换字符
regexp_substr 根据正则表达式切割字符串
select regexp_substr(str, '[^,]+', 1, Level,'i') from dual
connect by Level <= LENGTH(str) - LENGTH(regexp_replace(str, ',', '')) + 1);
instr 返回字符串的下标，没有则-1
instr('hello',o); -> 5
lpad 左对齐，缺位补符号
lpad('hello',10,'*'); -> *****hello
rpad 右对齐，缺位补符号
rpad('hello',10,'*'); -> hello*****
trim 清除左右两侧，默认是空格
trim(' hello '); -> hello
trim('h' from 'hello'); -> ello
replace 替换字符串
replace('abc','b','d'); -> adc
round 四舍五入
round(5.234,2); -> 5.23
round(2.345,2); -> 2.35
round(sysdate,'month'); -> 按月份四舍五入
trunc 截断
trunc(2.345,2); -> 2.34
trunc(sysdate); -> 2022-01-01
mod 求余
mod(10,3); -> 1
sysdate 当前日期
select sysdate from dual; -> 今天
select sysdate+1 from dual; -> 明天
select sysdate-1 form dual; -> 昨天
add_months 月份加减
add_months(sysdate,2); -> 两个月后
add_months(sysdate,-2); -> 两个月前
last_day 月份最后一天
last_day(sysdate); -> 当月最后一天
next_day 下一个同等日期
next_day(sysdate,'星期五'); -> 下个星期五日期
to_date 字符串转日期
to_date('2022-01-01','yyyy-mm-dd');
to_char 其它类型转字符串
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'); -> 日期转字符串
to_char(1234567.89,'999,999,999.99'); -> 1,234,567.89 -> 9代表不补零
to_char(1234567.89,'000,000,000.99'); -> 001,234,567.89 -> 0代表补零
to_number 字符串转数字
to_number('1,234','9,999'); -> 1234
nvl 输出非空
nvl(null,0); -> 0
nvl(1,0); -> 1
nvl2 非空输出表达式一，空输出表达式二
nvl2(null,0,1); -> 1
nvl2(2,0,1); -> 0
coalesce 输出非空，可变长度参数
coalesce(null,0,1); -> 0
coalesce(null,null,1); -> 1
nullif 相等输出空
nullif(1,1); -> null
nullif(1,2); -> 1
avg 平均值
select avg(id) from system_user group by id;
count 统计非空行数
select count(*) from system_user;
max 最大值
select max(id) from system_user group by id;
min 最小值
select min(id) from system_user group by id;
stddev 标准差
select stddev(id) from system_user group by id;
sum 合计
select sum(id) from system_user group by id;
listagg 分组拼接
select user_id, listagg(name,',') within group (order by user_id) name
from system_user_history group by user_id; -> 将用户所有足迹用,串起来
CHR & ASCII 特殊字符转义
select ascii('''') from dual; -> 39
select chr(39) from dual; -> '
ASCIISTR & UNISTR 编码互转解决乱码
select ASCIISTR('我') from dual;
select UNISTR('\6211') from dual;
五、约束
not null 非空约束
alter table system_user modify id constraint suid not null; -> 添加名为suid的约束
unique 唯一约束
alter table system_user add constraint suid unique(id); -> 添加名为suid的约束
primary key 唯一非空约束
alter table system_user add constraint suid primary key(id); -> 添加名为suid的约束
foreign key 外键
alter table system_user_history add constraint suhuserid foreign key(user_id)
references system_user(id) -> 关联外表
on delete cascade / on delete set null -> 外表删除时执行操作(级联删除/级联置空)
check 检查
alter table system_user add constraint suage check(age between 18 and 35);
drop 删除约束
alter table system_user drop constraint suid;
disable 约束无效化
alter table system_user disable constraint suid;
enable 约束有效化
alter table system_user enable constraint suid;
modify 约束不能修改，只能删除再添加
alter table system_user modify(id number(10,0) constraint suid not null);
六、DataDefinitionLanguage 数据定义语言
创建表
create table system_user( -> 列级约束
id number(10) constraint id primary key,
name varchar2(20) constraint name unique,
age number(10) constraint age not null
);
create table system_user( -> 表级约束
id number(10),
name varchar2(20),
age number(10),
constraint id primary key(id),
constraint name unique(name),
constraint age not null(age),
constraint range check(age>0 and age<100)
);
create table system_people as select * from system_user; -> 复制现有的表和数据
create table system_people as select * from system_user where 1=2; -> 复制空表
修改表
alter table system_user add(email varchar2(20)); -> 添加列
alter table system_user modify(age number(15) default 0); -> 修改列
alter table system_user drop column email; -> 删除列
alter table system_user rename column name to username; -> 修改列名
删除表
drop table system_user;
清空表
truncate table system_user;
重命名表
rename system_user to system_people;
创建索引
create index index_name on system_user(name);
删除索引
drop index index_name;
创建序列
create sequence seq_system_user -> 序列名
increment by 1 -> 自增量
start with 1 -> 起始值
maxvalue 99999999 -> 最大值
cycle/nocycle -> 是否循环
order/noorder -> 是否有序
cache 20/nocache -> 是否缓存
修改序列
alter sequence seq_system_user order cycle; -> 修改多个属性
删除序列
drop sequence seq_system_user;
使用序列作为自增主键
select seq_system_user.nextval from dual; -> 下一个值
select seq_system_user.currval from dual; -> 当前值
创建视图
create view view_system_user as select id,name from system_user with read only; -> 添加只读权限
create or replace view view_system_user as select * from system_user; -> 修改视图
更新视图
update view_system_user set age = 25 where id = 1;
delete from view_system_user where id = 1;
删除视图
drop view view_system_user;
兼容语法
declare
num number;
begin
select count(1) into num from user_tables where table_name = upper('system_user');
if num > 0 then
execute immediate 'drop table system_user';
end if;
select count(1) into num from user_ind_columns where index_name = upper('system_user_index');
if num > 0 then
execute immediate 'drop index system_user_index';
end if;
select count(1) into num from user_sequences where sequence_name= upper('system_user_index_seq');
if num > 0 then
execute immediate 'drop sequence system_user_index_seq';
end if;
select count(1) into num from user_tab_cols where table_name = upper('system_user') and column_name = upper('user_id');
if num > 0 then
execute immediate 'alter table system_user drop column user_id';
end if;
select count(1) into num from user_constraints where table_name = upper('system_user') and constraint_name = upper('user_id');
if num > 0 then
execute immediate 'alter table system_usermodify user_id null';
end if;
end;
七、存储过程
create 创建函数
CREATE OR REPLACE PACKAGE -> 定义包名
CUSTOM_PACKAGE IS -> 包内可以定义多个函数
PROCEDURE CUSTOM_PROCEDURE_1(PARAM IN VARCHAR2);
PROCEDURE CUSTOM_PROCEDURE_2(PARAM OUT VARCHAR2);
END CUSTOM_PACKAGE;
CREATE OR REPLACE PACKAGE BODY -> 定义包体
CUSTOM_PACKAGE IS
PROCEDURE CUSTOM_PROCEDURE_1(PARAM IN VARCHAR2) AS ... END;
PROCEDURE CUSTOM_PROCEDURE_2(PARAM OUT VARCHAR2) AS ... END;
END CUSTOM_PACKAGE;
BEGIN -> 调用函数
CUSTOM_PACKAGE.CUSTOM_PROCEDURE_1('');
END;
declare 定义变量
declare
name varchar2(20) := null;
age student.age%type := null; -> 动态定义类型
row student&rowtype := null; -> 动态定义整行
into 赋值
begin
select student.name,student.age into name,age from student where id = 1;
exception
when not found then
end;
cursor 游标，存储临时数据
cursor student_cursor is select * from student; -> 定义游标
student_id student.id%type; -> 定义游标属性
student_name student.name%type; -> 定义游标属性
open student_cursor; -> 打开游标
loop
fetch student_cursor into student_id, student_name; -> 读取数据
exit when student_cursor%NOTFOUND; -> 无数据时退出
end loop;
close student_cursor; -> 关闭游标
type student_type is record ( -> 定义游标类别
student_id student.id%type,
student_name student.name%type
);
type student_cursor is ref cursor return student_type; -> 定义游标返回类别
exception 异常，捕获程序错误
declare custom_exception exception; -> 自定义异常
begin
raise custom_exception; -> 手动抛出异常
commit;
exception
when NO_DATA_FOUND then
dbms_output.put_line(sqlcode); -> 编号
dbms_output.put_line(sqlerrm); -> 描述信息
dbms_output.put_line(dbms_utility.format_error_backtrace); -> 发生位置
dbms_output.put_line(dbms_utility.format_call_stack); -> 发生栈
when others then -> 捕获并处理
rollback;
end;
dbms_output 输出日志
set serveroutput on; -> 开启输出服务
set serveroutput off; -> 关闭输出服务
dbms_output.put('不换行');
dbms_output.new_line; -> 换行
dbms_output.put_line('自动换行');
dbms_metadata 获取创建语句
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_SCHEMA') from dual; -> 参数2/3动态变更
if (boolean) then (logic) elsif (boolean) then (logic) else (logic) end if; 条件表达式
if name = 'wjx' then update student set age = '20' where id = 1;
elsif name = 'xjw' then update student set age = '21' where id = 2;
else update student set age = '21' where id = 3;
end if;
goto 循环
declare x :=10;
begin
<<circle_point>>
if x>0 then
x := x -1;
goto circle_point;
else
dbms_output.put_line('循环结束');
end if;
for (var) in (set) loop (logic) end loop; 循环
begin
for student_list in select * from student loop
if student_list.name = 'wjx' then
update student set age = '20' where id = 1;
else end if;
end loop;
end;
begin
for i in 1..100 loop
dbms_output.put_line(i); -> 打印1-100
end loop;
end;
while (boolean) loop (logic) end loop; 循环
declare x := 0;
begin
while x < 10 loop
x := x+1;
end loop;
end;
loop (logic) exit when (boolean) end loop; 循环
declare x:= 0;
begin
loop x := x + 1;
exit when x > 10;
end loop;
end;
八、权限
为用户授予权限
create public synonym db_name.table_name for table_name; -> 同义词
grant select on db_name.table_name to oracle_username; -> 查询
grant update on db_name.table_name to oracle_username; -> 更新
grant delete on db_name.table_name to oracle_username; -> 删除
grant insert ondb_name.table_name to oracle_username; -> 新增
查询用户表权限
select * from dba_tab_privs
where 1= 1
and table_name = 'table_name'
and grantee = 'oracle_username'
and privilege in ('SELECT','UPDATE','DELETE','INSERT');
九、触发器
触发类型（12种），包含新增、修改、删除
| 类型 | 描述 |
|---|---|
| BEFORE INSERT | 新增前，表级 |
| BEFORE INSERT FOR EACH ROW | 新增前，行级 |
| AFTER INSERT | 新增后，表级 |
| AFTER INSERT FOR EACH ROW | 新增前，行级 |
| BEFORE UPDATE | 更新前，表级 |
| BEFORE UPDATE FOR EACH ROW | 更新前，行级 |
| AFTER UPDATE | 更新后，表级 |
| AFTER UPDATE FOR EACH ROW | 更新后，行级 |
| BEFORE DELETE | 删除前，表级 |
| BEFORE DELETE FOR EACH ROW | 删除前，行级 |
| AFTER DELETE | 删除后，表级 |
| AFTER DELETE FOR EACH ROW | 删除后，行级 |
| :NEW.column | 新值，INSERT/UPDATE有，DELETE无 |
| :OLD.column | 旧值，UPDATE/DELETE有，INSERT无 |
| OR，AFTER INSERT OR DELETE ON system_user | 设立在某几种操作上触发 |
| OF，AFTER UPDATE OF name ON system_user | 设立在某个字段上触发 |
Insert 时机
CREATE OR REPLACE TRIGGER SYSTEM_USER_A_I
AFTER INSERT
ON system_user
FOR EACH ROW
BEGIN
INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'INSERT', :NEW.id, :NEW.name, :NEW.age);
END;
Update 时机
CREATE OR REPLACE TRIGGER SYSTEM_USER_B_U
BEFORE UPDATE
ON system_user
FOR EACH ROW
BEGIN
INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'UPDATE', :OLD.id, :OLD.name, :OLD.age);
END;
CREATE OR REPLACE TRIGGER SYSTEM_USER_A_U
AFTER UPDATE
ON system_user
FOR EACH ROW
BEGIN
INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'UPDATE', :NEW.id, :NEW.name, :NEW.age);
END;
Delete 时机
CREATE OR REPLACE TRIGGER SYSTEM_USER_B_D
BEFORE DELETE
ON system_user
FOR EACH ROW
BEGIN
INSERT INTO system_user_log(ID, DATE, TYPE, USER_ID, USER_NAME, USER_AGE)
VALUES(SEQ_SYSTEM_USER_LOG.nextval, sysdate, 'DELETE', :OLD.id, :OLD.name, :OLD.age);
END;
十、JDBC连接
SQL
String sql = "select id from system_user where name = ?";
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = super.dataSource.getConnection();
ps = connection.prepareStatement(sql);
ps.setString(1, "wjx"); -> 入参
rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("id"); -> 出参
}
} finally {
if(rs != null) rs.close();
if(ps != null) ps.close();
if(connection != null) connection.close();
}
PLSQL
CREATE OR REPLACE PROCEDURE SCHEMA.GET_NEW_USER(in_date IN DATE DEFAULT sysdate, out_user_list OUT SYS_REFCURSOR) IS
-- DECLARE
-- in_date DATE := to_date('2023-01-01', 'yyyy-mm-dd');
BEGIN
OPEN out_user_list FOR
SELECT su.*
FROM system_user su
WHERE create_date > TRUNC(in_date - 7);
END;
/
String sql = "{call SCHEMA.GET_NEW_USER(?, ?)}";
Connection connection = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
connection = super.dataSource.getConnection();
cs = connection.prepareCall(sql);
cs.setString(1, new java.sql.Date());
cs.registerOutParameter(2, -10);
cs.execute();
rs = cs.getObject(2, ResultSet.class);
while (rs.next()) {
String id = rs.getString("id"); -> 出参
}
} finally {
if(rs != null) rs.close();
if(ps != null) ps.close();
if(connection != null) connection.close();
}
FUNTION
create or replace function f_no_param return varchar2 is
begin
return 'hello, no param';
end;
create or replace function f_have_param(id in integer, name out varchar2) return varchar2 is
begin
name := 'wjx';
return 'hello, have param';
end;
String noParamSql = "{? = call f_no_param}";
String haveParamSql = "{? = call f_have_param(?,?)}";
Connection connection = null;
CallableStatement cs1 = null;
CallableStatement cs2 = null;
try {
connection = super.dataSource.getConnection();
cs1 = connection.prepareCall(noParamSql);
cs1.registerOutParameter(1, Types.VARCHAR); -> 返回值是出参
cs1.execute();
String noParamVarchar = cs1.getString(1);
cs2 = connection.prepareCall(haveParamSql);
cs2.registerOutParameter(1, Types.VARCHAR); -> 返回值是出参
cs2.setInt(2, 10); -> id是入参
cs2.registerOutParameter(3, Types.VARCHAR); -> name是出参
cs2.execute();
String haveParamVarchar = cs1.getString(1);
String haveParamName = cs1.getString(3);
} finally {
if(rs != null) rs.close();
if(ps != null) ps.close();
if(connection != null) connection.close();
}
SQL 执行计划
explain plan for select * from system_user where id = 1; -> 生成执行计划
select * from table(dbms_xplan.display); -> 查询执行计划
