从0到1快速学会Oracle数据库（包括SQL），这一篇就够了！

＜＜＜学习提示：目录篇幅太长因此省略，点击右侧目录即可快速跳转对应知识点＞＞＞

Tips：速学SQL传送门

内容太多，因此通过整理单独把SQL核心基础单独提炼到↓↓↓以下链接↓↓↓中

从0到1快速学会基于MySQL数据库的SQL核心基础知识，这一篇就够了！https://blog.csdn.net/weixin_45806267/article/details/145143892?sharetype=blogdetail&sharerId=145143892&sharerefer=PC&sharesource=weixin_45806267&spm=1011.2480.3001.8118

点击↑↑↑以上链接↑↑↑即可快速跳转学习！

• What is SQL? (帮助理解)

如果把数据库（DataBase）比作盘子(想象一下你家有不同尺寸的盘子，因此有很多数据库产品)，那么数据（Data）就是盘子里的菜，结构化查询语言（Structured Query Language）简称SQL，则是吃饭时用来夹菜的筷子。

• SQL的作用?

我们通过（Structured Query Language，SQL）就可以操作数据库管理系统 （DataBase Management System，DBMS），然后通过DBMS再来操作操作数据库（DataBase，DB）和数据库中的数据Data。

一个适合小白学SQL的网站：xuesql.cnhttp://www.xuesql.cn/

1 创建表空间create tablespace

创建表空间：
create tablespace  first_tablespace --first_tablespace表空间名称，自定义
datafile 'D:\first_tablespace' --表空间irst_tablespace对应的数据文件，放在哪个路径，自定义
size  100M --设置该表空间默认最大容量为100M（举例），根据需求自定义默认大小
autoextend  on --自动扩展（默认最大容量100M满了之后打开自动扩展开关）
next 10M;--每次满了之后自动增长10M（举例），根据需求自定义默认大小
--执行之后D:\下会多一个'first_tablespace.dbf'的文件

2 创建用户create user

注意：
① 用户是创建在表空间上的 
② 一个表空间上可以创建多个用户 （表空间：用户=1：N）

创建用户
create user user1 --user1用户名，自定义
identified by 123456 --123456密码，自定义
default tablespace first_tablespace;--指定用户是属于'哪一个表空间'

3 授予用户权限grant privileg

授予用户权限。详细的授权语句如下，'/'代表或者
grant select/insert/update/delete/alter/index/createtab/all privileges
--all privileges表示所有权限
on database/table/view --具体是哪一个数据库或者表的权限
to user/public --给具体哪一个用户或者给所有用户
with grant option; --表示获得授权的用户还可以把权限赋予其他用户

--举例：给user1赋予dba权限
grant dba to user1;--dba代表最高权限

4 收回用户权限revoke privilege 

收回权限：
revoke select/insert/update/delete/alter/index/createtab/all privileges
--all privileges代表所有权限
on table/database--从哪个数据库或者表收回
from user/public--收回具体哪个用户的权限，public收回所有用户的权限
restrict/cascade;
--restrict 只收回指定的用户的权限
--cascade（级联收回） 收回指定用户的权限以及该用户赋予的其他用户的权限

5 创建表create table

5.0 Oracle数据类型

5.1 创建表方法一：利用SQL语句创建新表

Oracle创建表方法一：用SQL语句创建新表（属性=字段，属性和字段一个意思）
create table 表名称(
      属性1 数据类型(长度) primary key,--主键= not null unique  
      属性2 数据类型(长度) check(限制条件),--例：CHECK (Sex='男' OR Sex='女')
      ...
      字段n 数据类型(长度) references 被参照的表名（被参照的属性名）--外键来自哪个表哪个属性
                  on delete cascade--表示删除被参照关系的元组时，同时删除参照关系的元组
                  on delete set null--表示删除被参照关系的元组时，参照关系的元组取null值
                  );
MySQL创建表SQL：
CREATE TABLE 表名称(
字段1 字段1类型（长度） [COMMENT 字段1注释 ],
字段2 字段2类型（长度） [COMMENT 字段2注释 ],
字段3 字段3类型（长度） [COMMENT 字段3注释 ],
......
字段n 字段n类型（长度） [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ];
--comment：注释
--注意: [...] 内为可选参数，最后一个字段后面没有逗号。

--例1
create table item_changename202410
(
ID number primary,
gjbm varchar2(50),
oldname varchar2(100),
oldyb varchar2(4),
newyb varchar2(4),
newname varchar2(100),
changetxt varchar2(50) 
);
--例2
create table item_changefylb202401
(
ID number primary,
gjbm  varchar2(50),
ypmc  varchar2(100),
fylb1 varchar2(2),
fylb1mc varchar2(10),
oldfylb1mc varchar2(20)
);
--例3
create table emp(
entrydate date comment '入职时间',
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate comment '入职时间'
) comment '员工表';

补充知识：char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度。
如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)

5.1 创建表方法二：利用PL/SQL Developer图形化界面创建新表

5.2 主键（实体完整性约束）

从多个候选码中通过  最小化规则  选择出一个作为主码也就是主键。

① 数据库中每张表只能有一个主键，不可能有多个主键；

② 主键的作用：保证数据库的唯一性和完整性。主键的值也称为键值，必须能够唯一标识表中的每一条记录，且不能为null。也就是说：一个表只要定义了主键约束，就不可能有相同的两行记录（因为由主键唯一标识开了）。

③ 若主键只是表中的某一个字段，则在定义主键属性的最后加primary key = not null unique

④【复合/联合主键】若主键是由表中多个字段组成，则在表级完整性约束加primary key(属性1，属性2)。(在一个数据表中通过多个字段作为主键来确定一条记录，那么，这多个字段组成的就是复合/联合主键 

5.3 外键（参照完整性约束）

该字段在当前正在创建的表中属于非主属性，但是在其他表中作为主键，因此在本表中称为外键。

① 若在列级表示外键： 参照的属性后面加 references 被参照的表名（被参照的属性名）

② 若在表级表示多个外键，在创建表的最后加foreign key（当前创建的属性） references 被参照的表名（被参照的属性名）  有几个外键写几行

5.4 用户自定义约束（属性值上的约束）                                      

① null 为空;not null 非空

② unique 唯一，可以在多个属性上用unique，可写在属性后，也可以写在表级约束

③ not null unique 取值唯一且不为空=primary key

④check 限制列中的取值范围。

例：CHECK (Sex='男' OR Sex='女')，CHECK (余额>=0)，CHECK (年龄>=18 AND 年龄<=60)，check(sex in ('男','女'))

6 修改表alter table

6.1 修改表方法一：利用SQL语句进行修改

Oracle修改表方法一：利用SQL语句进行修改
1.增加字段（根据新需求需要新增字段）
alter table 表名 add 
    (新增字段1 数据类型(长度),
     新增字段2 数据类型(长度)
     );
增加字段例子：
alter table t_owners add
(remark varchar2(20),
 outdate date
);

2.修改字段（修改字段的数据类型，扩充字段默认长度）
alter table 表名称 modify
      (要修改的字段1 要修改成什么数据类型(修改后的长度),
       要修改的字段2 要修改成什么数据类型(修改后的长度)
       );
修改字段例子：
alter table t_owners modify
(remark char(30)
outdate timestamp
);

3.修改字段名
alter table 表名称 rename column  原字段名  to 修改后的新字段名;
修改字段名例子：
alter table t_owners rename column outdate to exitdate;

4.删除字段
alter table 表名称 drop column 要删除的字段1,要删除的字段2;
删除字段例子：
alter table t_owners drop column remark,exitdate;

5.删除表
drop table 要删除的表名称;
删除表例子：
drop table t_owners;

6.新增check约束
alter table S  add constraint c_cno check(条件);

MySQL修改表方法一：
1.添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
案例:
为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)。
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

2.修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

3.修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
案例:
将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

4.删除字段
ALTER TABLE 表名 DROP 字段名;
案例:
将emp表的字段username删除
ALTER TABLE emp DROP username;

5.修改表名
ALTER TABLE 表名 RENAME TO 新表名;
案例:
将emp表的表名修改为 employee
ALTER TABLE emp RENAME TO employee;

6.删除表
DROP TABLE [ IF EXISTS ] 表名;
可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不
加该参数项，删除一张不存在的表，执行将会报错)。

7.删除表
TRUNCATE TABLE 表名;
注意: 在删除表的时候，表中的全部数据也都会被删除。

6.2 修改表方法二：利用PL/SQL Developer图形化界面进行修改 

7 增：插入insert into 

插入数据的具体语法为：
1.给全部字段添加数据
insert into 表名 values（值1，值2，值3...）;
★没有指名要插入的列，所以values后需要把所有列的值都写进去。
 
2.给指定字段添加数据
insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);
★指名了要插入的列，values跟对应指名列的值，未指名的字段默认为空。
 
3.批量添加数据（全部字段）
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
 
4.批量添加数据（指定字段）
INSERT INTO 表名 (字段名1, 字段名2, ...) 
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
 
注意事项
★批量增加字段时，每两组值之间用英文状态的逗号,分割。
★插入数据时，指定的字段顺序需要与值的顺序必须是对应的。
★字符串和日期型数据应该包含在引号中。
★插入的数据大小，应该在字段的规定范围内。

注意：批量增加字段时，每两组值之间用英文状态的逗号,分割。

1.给全部字段添加数据

insert into 表名 values（值1，值2，值3...）;
--没有指名要插入的列，所以values后需要把所有列的值都写进去。
 
2.给指定字段添加数据
insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);
--指名了要插入的列，values跟对应指名列的值，未指名的字段默认为空。
 
3.批量添加数据（全部字段）
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
 
4.批量添加数据（指定字段）
INSERT INTO 表名 (字段名1, 字段名2, ...) 
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

8 删：删除delete from

delete from 表名;
--删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等

delete from 表名 where ...;
--删除符合where后条件的行

truncate table  表名;
--等价于不带where的delete语句，
优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；
缺点是truncate是一个DDL语句，不能被撤销rollback

drop table  表名;
--删除所有内容，包括表的关系模式结构等

注意事项:
• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

delete from 表名;--删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等

delete from 表名 where ...;--删除符合where后条件的行

truncate table  表名;--等价于不带where的delete语句，

优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；

缺点是truncate是一个DDL语句，不能被撤销rollback

drop table  表名;--删除所有内容，包括表的关系模式结构等

 具体详见：Oracle删除数据的三种方式http://t.csdnimg.cn/TGkzV

9 改：更新update

update  表名 set 列名='要修改为的新值' where  条件;
--把表中符合where条件的行对应的列update为要修改为的值
注意事项:
修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

update更新实例:
--例1：项目名称不变，收费项目类别“诊查费”要改为“诊察费”
 update T001
     set mc = '诊察费'
   where dl = '0111'
     and bm = 05
     and mc = '诊查费';
  
 update medi000
    set fylb1mc = '诊察费', fylb2mc = '诊察费', fylb3mc = '诊察费'
  where fylb1 = '05'
    and fylb2 = '05'
    and fylb1mc = '诊查费'
    and fylb2mc = '诊查费';

--例2：20241101医疗项目相关变更工作
update KA0203 a
   set AKA090 =
       (select newcode
          from item_changecode202410 t
         where t.oldcode = a.aka090)
 where aka090 in (select oldcode from item_changecode202410);

update medi000 a
   set gjbm =
       (select newcode from item_changecode202410 t where t.oldcode = a.gjbm)
 where gjbm in (select oldcode from item_changecode202410);
 
update medi000 a
   set gjbm =
       (select center_code from ybdzb t where t.his_code = a.ypbm)
 where a.ypbm in (select his_code from ybdzb);

update  表名 set 列名='要修改为的新值' where  条件;

--把表中符合where条件的行对应的列update为要修改为的值

补充：

Tips:假设A，B两个表，表A中有属性参照了表B的属性。

1、先更新/删除参照的表A，再更新/删除被参照的表B

2、插入先插入被参照的表B，再插入参照的表A

10 查：单表查询

SQL99标准查询：
SELECT 
  查询列表 
FROM 表1 别名1 
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】;

--例：
select *
  from KA0203
 where aka090 in (select oldcode from item_changecode202410);
 
select *
  from ybdzb
 where center_code in (select oldcode from item_changecode202410);

select *
  from medi000
 where gjbm in (select oldcode from item_changecode202410);
 
select * from medi000 where gjbm not in (select center_code from ybdzb);

select t.*, t.rowid
  from medi000 t
 where ypbm not in (select his_code from ybdzb)
   and dzbz = '1';

***Oracle实现分页：
示例4：实现分页查询
rownum结合order by 使用规则：
★第一层子查询先做order by
★第二层子查询带出rownum 
★第三层查询再来选择排序后要显示的行号结果

select * from 
(select rownum r,t.* from 
(select * from z_detail order by lrrq desc) --第一层子查询先做order by
t) --第二层子查询带出rownum
where r>10 and r<=20;--第三层查询再来选择排序后要显示的行号结果


***MySQL特有分页参数limit：限制。
★语法：limit 起始索引，查询记录数;
★起始索引=（页码-1）*每一页展示的记录数
实例1：第一页，每页10条
SELECT * FROM emp limit 0,10;
起始索引0=（1-1）*10

实例2：查询第二页员工数据，每页展示10条。
SELECT * FROM emp limit 10,10;
起始索引10=（2-1）*10

以此类推：
第三页
SELECT * FROM emp limit 20,10;
第四页
SELECT * FROM emp limit 30,10;

①在Oracle里，列的别名可以使用as或者直接空格，而表的别名直接空格不能使用as，否则报错。②在Oracle里，对表使用别名后，凡是用到表名的地方都只能使用别名，否则报错。

  select  TOP | DISTINCT   选择列表 | *  --distinct去重

  from   表名--表若重命名，引用时一定要用新的名称

 where  查询条件--条件范围对应整个表，结果返回之前执行，不能使用聚合函数

 group by  分组条件

having  过滤条件--条件范围对应分组后的数据，必须跟在group by后，对返回结果过滤，可以使用聚合函数

 order by 排序字段 asc | desc ;

20241210补充：select之后一定是分组聚合的函数或者是聚合函数，同下①。

① 使用了聚集函数时，一般都要group by分组：group by 后面跟select之后除了聚集函数之外的所有属性列，若select之后无聚集函数，则group by之后跟select之后的所有属性列。

②group by之后跟的字段名必须是重命名之前的(不能跟重命名之后的新名称)。

② having限制条件，去掉不满足having后条件的分组。

③ asc 升序 (默认) ，desc 降序

④ in 在集合中= or

⑤ not in 不在集合中  【where后条件语句中】之前有指定的具体的属性名，之后的子查询结果不是all，是具体的属性名

⑥ exists 存在

⑦ not exists 不存在  【where后条件语句中】之前没有指定的属性名，之后的子查询结果是可以是all

⑧【字符串操作】like '_database'  _代表一个字符    not   like

   【字符串操作】like '%database'  %代表任意多个字符

⑨【转义字符escape】为了使模式中包含特殊字符，即%和_，允许使用escape关键词来定义转义符，like语句后面紧 跟escape '\'表明转义符'\' 后面的特殊字符为普通字符。

like 'ab\%cd%' escape '\';--匹配所有以ab%cd开头的字符串

⑩ is null 为空

⑪ is not null 非空

⑫ Default 默认值：default null默认为空；或者接在not null之后，例：not null default '60'

⑬ count ([distinct | all]*) 统计元组个数:有几行

Count (*) 统计行数，null也算在内

Count (列名) 统计某个值的个数，null不算在内

⑭ sum ([distinct | all]*)   计算某一列的总和

⑮ avg ([distinct | all]*)   计算某一列的平均值

⑯ max ([distinct | all]*)   计算某列的最大值

⑰ min ([distinct | all]*)   计算某列的最小值

有聚集函数就要用group by，group by 后跟select之后未使用聚集函数的列名

11 查：内外连接查询

1. inner join(等值连接或者叫内连接)：相当于查询A、B交集部分数据。
2. left ioin (左连接)：查询左表所有数据，以及两张表交集部分数据
3. right join (右连接)：查询右表所有数据，以及两张表交集部分数据。
4. full join (全外连接)：返回左右表中所有的记录和左右表中交集部分的记录。

5. 自连接：顾名思义，就是自己连接自己。当前表与自身的连接查询，自连接必须使用表别名。对于自连接查询，可以是内连接查询，也可以是外连接查询。

SQL99标准查询：
SELECT 
  查询列表 
FROM 表1 别名1 
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】;

***Oracle实现分页：
示例4：实现分页查询
rownum结合order by 使用规则：
★第一层子查询先做order by
★第二层子查询带出rownum 
★第三层查询再来选择排序后要显示的行号结果

11.1 等值连接（内连接）

只返回两个表中连接条件相等的记录。

语句格式： ...from A (inner)  join B on 条件;--(inner) 可以省略

显示内连接：select * from m_main A inner join z_main B ON A.DAH=B.DAH;

隐式内连接：select * from m_main A, z_main B where A.DAH=B.DAH;

11.2 左外连接

返回包括左表中的所有记录和右表中连接条件相等的记录。

语句格式：...from 主表A left (outer) join 副表B  on  条件;--(outer)可以省略

select * from m_main A  left  outer join z_main B ON A.DAH=B.DAH;

11.3 右外连接

返回包括右表中的所有记录和左表中连接条件相等的记录。

语句格式：...from 副表A right (outer) join 主表B on 条件;--(outer)可以省略

select * from m_main A  right  outer join z_main B ON A.DAH=B.DAH;

11.4 全外连接

 返回左右表中所有的记录和左右表中连接条件相等的记录。

语句格式：...from A full (outer)  join B on 条件;--(outer)可以省略

select * from m_main A  full  outer join z_main B ON A.DAH=B.DAH;

11.5  自连接：自己连接自己

自连接：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
 
自连接实例1：A. 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;
自连接实例2：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来。
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;
 
***注意：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底
是哪一张表的字段。

11.6 Oracle中左右连接特有语法

1. 如果是左外连接，就在右边的条件一端加上(+)
2. 如果是右外连接，就在左边的条件一端加上(+)

左外连接，就在右边的条件一端加上(+)
select * from m_main A,z_main B  where A.DAH=B.DAH(+);

右外连接，就在左边的条件一端加上(+)
select * from m_main A,z_main B  where A.DAH(+)=B.DAH;

12  查：嵌套查询（子查询）

12.1  where 子句中的子查询

（1） 单行子查询

顾名思义子查询的结果只返回一行数据。

• 单行子查询单行操作符

单行子查询实例：
select *  from z_detail where hj>= (select avg(hj) from z_detail where lrsj=2024);

（2） 多行子查询

顾名思义子查询的结果返回多行数据。

• 多行子查询单行操作符

多行子查询实例：
select *  from z_detail where zyh  in  (select zyh from z_main where brxm like '杨%');

12.2 from子句中的子查询

firom 子句的子查询为多行子查询。把子查询的结果看成一个表。

select * from 
(select b.fph fph,a.brxm xm from z_main a,z_detail b where a.zyh=b.zyh )
where fph='9999999';

12.3 select子句中的子查询

select 子句的子查询必须为单行子查询。

13 查：分页查询

 分页查询：联合利用order by和rownum实现分页查询。

ROWNUM 的分配是基于查询结果的生成顺序，为查询结果集的每一行分配一个唯一的行号，从 1 开始。 

注意：

① ROWNUM的生成时间：扫描结果之后，结果排序之前

② ROWNUM后的运算符只能是<或者<=

***1.Oracle中实现分页方法一：利用三层嵌套。
rownum结合order by 使用规则：
★第一层子查询先做order by
★第二层子查询带出rownum 
★第三层查询再来选择排序后要显示的行号结果

实例：
select * from 
(select rownum r,t.* from 
(select * from z_detail order by lrrq desc) --第一层子查询先做order by
t) --第二层子查询带出rownum
where r>10 and r<=20;--第三层查询再来选择排序后要显示的行号结果

***2.Oracle中实现分页方法二：利用minus实现分页查询。
select rownum,t.* from  z_doctors_advice t where rownum<=20
mius
select rownum,t.* from  z_doctors_advice t where rownum<=10;

***3.Oracle中实现分页方法三：利用row_number()函数实现分页查询。
实例：
select tt.* from 
(select row_number() over(order by lrrq asc) 行号,t.* 
from z_main t) tt
where 行号>10 and 行号<=20;

***4.MySQL中的分页查询（limit分页参数只适用于MySQL）
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
★注意事项:
• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
 
案例:
***MySQL特有分页参数limit：限制。
★语法：limit 起始索引，查询记录数;
★起始索引=（页码-1）*每一页展示的记录数
实例1：第一页，每页10条
SELECT * FROM emp limit 0,10;
起始索引0=（1-1）*10
 
实例2：查询第二页员工数据，每页展示10条。
SELECT * FROM emp limit 10,10;
起始索引10=（2-1）*10
 
以此类推：
第三页
SELECT * FROM emp limit 20,10;
第四页
SELECT * FROM emp limit 30,10;

14 函数（字符、数值、日期、转换、其他、行列、分析） 

 14.1 常用字符函数

（1） LENGTH 返回字符串的长度

【示例】
SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;

length('高乾竞')    length('北京市海锭区')     length('北京TO_CHAR')
-----------------    --------------            --------------------
          3              6                               9

（2） SUBSTR 截取子字符串（求字符串的子串）

SUBSTR(原字符串,从第几位开始截取,截取几位) 若不指定截取几位,则从第几位开始截取一直到字符串结束。

SQL> select substr('13088888888',3,8) test from dual;

test
--------
08888888

（3）CONCAT(c1,c2) 同：c1||c2 连接两个字符串

【示例】
 select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

高乾竞电话
----------------
010-88888888转23

（4） REPLACE替换字符串

REPLACE(原字符串,要替换的字符串,替换后新的字符串) ，替换后新的字符串，默认为空(即删除之意，不是空格)。

REPLACE(c1,c2[,c3])
【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
【参数】
c1   希望被替换的字符或变量 
c2   被替换的字符串
c3   要替换的字符串，默认为空(即删除之意，不是空格)
【返回】字符型

【示例】
SQL> select replace('he love you','he','i') test from dual;

test
-------------
i love you

14.2 常用数值函数（数学函数）

（1）ROUND 四舍五入函数：返回四舍五入后的值

round(x[,y])
【功能】返回四舍五入后的值,y可以省略，省略时默认为整数部分
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
【返回】数字

【示例】
  select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;
返回：   5555.67     ,    5600    ,    5556

【相近】trunc(x[,y])
返回截取后的值，用法同round(x[,y]),只是不四舍五入

（2） TRUNC 数字截取

trunc(x[,y])
【功能】返回x按精度y截取后的值；y可以省略，省略时默认为整数部分
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【返回】数字

【示例】
  select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;
返回：5555.66                    5500               5555

【相近】round(x[,y])
返回截取后的值，用法同trunc(x[,y]),只是要做四舍五入

（3）MOD(x,y) 取模（取余数） ：返回x除以y的余数

mod(x,y)
【功能】返回x除以y的余数
【参数】x,y，数字型表达式
【返回】数字

【示例】
  select mod(23,8),mod(24,8) from dual;
返回：7,0

 14.3 常用日期函数

（1） sysdate 返回当前日期

sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期

【示例】select sysdate  hz from dual;
返回：2024-12-12 08:46:29

（2）add_months(d1,n1) 加月

add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期

【示例】select sysdate,add_months(sysdate,3)  hz from dual;
返回：2024-12-12 08:48:54,2025-03-12 08:48:54

（3）last_day 求所在月最后一天

last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。
【参数】：d1，日期型
【返回】：日期

【示例】select sysdate,last_day(sysdate)  hz from dual;
返回：2024-12-12 09:15:15，2024-12-31 09:15:15

（4） TRUNC 日期截取

trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）
最近月初日期：month或mon或mm或rm 
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)  
最近世纪初日期：cc或scc

【返回】：日期
 
【示例】
select sysdate 当时日期,--2024-12-12 09:21:52
trunc(sysdate) 今天日期,--2024-12-12
trunc(sysdate,'day') 本周星期日,--2024-12-08
trunc(sysdate,'month') 本月初,--2024-12-01
trunc(sysdate,'q') 本季初日期,--2024-10-01
trunc(sysdate,'year') 本年初日期 from dual;--2024-01-01

14.4 常用转换函数

（1）to_char 将日期或数据转换为字符串类型，返回varchar2字符型

一、日期转字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;--2024-12-12
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--2024-12-12 09:42:37

select to_char(sysdate, 'yyyy') || '年' || to_char(sysdate, 'mm') || '月' ||
       to_char(sysdate, 'dd') || '日'
  from dual;
--2024年12月12日

二、数字转字符串
select to_char(100) from dual;

（2）to_date 字符串转日期

【示例1】select to_date('2025-01-01','yyyy-mm-dd') from dual;--2025-01-01

TO_DATE(X[,c2[,c3]])
【功能】将字符串X转化为日期型
【参数】c2,c3,字符型，参照to_char()
【返回】字符串

如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x

【相反】 to_char(date[,c2[,c3]])

【示例2】
select to_date('199912','yyyymm'),--1999-12-01
to_date('2000.05.20','yyyy.mm.dd'),-2000-05-20
(date '2008-12-31') XXdate, --2008-12-31
to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),--2008-12-31 12:31:30
(timestamp '2008-12-31 12:31:30') XXtimestamp --31-12月-08 12.31.30.000000000 下午
from dual;

（3）to_number 字符串转数字

TO_NUMBER(X[[,c2],c3])
【功能】将字符串X转化为数字型
【参数】c2,c3,字符型，参照to_char()
【返回】数字串

【相反】 to_char(date[[,c2],c3])

【示例】
select '100'+0 from dual;--字符串内容为数字时+0也可以转成数字类型  
select TO_NUMBER('202501'),TO_NUMBER('520.1314') from dual;--202501  520.1314

转换为16进制。 
TO_CHAR(100,'XX')= 64 

 14.5 其它常用函数

（1）NVL  NVL2空值处理函数

select nvl(检测的值,0) from dual;--检测的值如果为空就返回0，检测的值不为空则返回它本身。

select nvl2(检测的值，不为null返回值1，为null返回值2)  from dual;

select nvl(null,0) from dual;
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致 

【语法】NVL2 (expr1, expr2, expr3) 
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
	expr2和expr3类型不同的话，expr3会转换为expr2的类型 

（2） decode 条件取值 （Oracle特有）

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值);如果条件等于值1，就返回翻译值1；如果条件等于值2，就返回翻译值2；如果条件都不等于这些值，就返回最后的缺省值。

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
·含义解释：　　
　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　
　　该函数的含义如下：　　
　　IF 条件=值1 THEN
　　RETURN(翻译值1)
　　ELSIF 条件=值2 THEN
　　RETURN(翻译值2)
　　......
　　ELSIF 条件=值n THEN
　　RETURN(翻译值n)　　
　　ELSE
　　RETURN(缺省值)
　　END IF
　　
或：
　　when case 条件=值1 THEN
　　RETURN(翻译值1)
　　ElseCase 条件=值2 THEN
　　RETURN(翻译值2)
　　......
　　ElseCase 条件=值n THEN
　　RETURN(翻译值n)　　
　　ELSE
　　RETURN(缺省值)
　　END

【示例】
select decode(400,1,100,2,200,300,3,400,4) from dual;--4
select decode(400,1,100,2,200,300,3) from dual;--返回空值
select decode(400,1,100,2,200,300,3,888) from dual;--888

（3）case   when  then  end 条件取值 （通用）

【注意点】
1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名
5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序

case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1> 
[when <表达式条件值2> then <满足条件时返回值2> 
……
[else  <不满足上述条件时返回值>]]
end

【功能】当：<表达式>＝<表达式条件值1……n> 时，返回对应 <满足条件时返回值1……n> 
当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，
decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2> ……,<不满足上述条件时返回值>)

【参数】
<表达式> 默认为true (逻辑型)
<表达式条件值1……n> 类型要与<表达式>类型一致，
若<表达式>为字符型，则<表达式条件值1……n>也要为字符型

【示例】
建立环境：
create table xqb
(xqn number(1,0));
insert into xqb xqn values(1);
insert into xqb xqn values(2);
insert into xqb xqn values(3);
insert into xqb xqn values(4);
insert into xqb xqn values(5);
insert into xqb xqn values(6);
insert into xqb xqn values(7);
commit;

查询结果：
SELECT xqn, 
       CASE
          WHEN xqn = 1  THEN '星期一'
          WHEN xqn = 2  THEN '星期二'
          WHEN xqn = 3  THEN '星期三'
	  else '星期三以后'
       END 星期
FROM xqb

另类写法
SELECT xqn, 
       CASE xqn
          WHEN 1  THEN '星期一'
          WHEN 2  THEN '星期二'
          WHEN 3  THEN '星期三'
	  else '星期三以后'
       END 星期
FROM xqb

decode正确表达：
SELECT xqn, 
decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期
FROM xqb

decode错误表达：
SELECT xqn, 
decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后') 星期
FROM xqb

组合条件表达：
SELECT xqn, 
       CASE
          WHEN xqn <= 1  THEN '星期一'
          WHEN xqn <= 2  THEN '星期二'   --条件同：not(xqn<=1) and xqn<=2
          WHEN xqn <= 3  THEN '星期三'   --条件同：not(xqn<=1 and xqn<=2) and xqn<=3
	  else '星期三以后'
       END 星期
FROM xqb

 14.6 rank 和 dense_rank ：计算一组数值中的排序值。

（1）rank 相同的值，排名相同，序号跳跃

（2）dense_rank 相同的值，排名相同，序号连续

【语法】rank ( ) OVER ( [query_partition_clause] order_by_clause )
              dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当。
【区别】dence_rank在并列关系时，相关等级不会跳过。rank则跳过。
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 
dense_rank()是连续排序，有两个第二名时仍然跟着第三名。

【示例】
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

　　在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
　　
　　其语法为：
　　
　　RANK ( ) OVER ( [query_partition_clause] order_by_clause )
　　
　　在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
　　
　　其语法为：
　　
　　RANK ( expr [, expr]... ) WITHIN GROUP
　　( ORDER BY
　　expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
　　[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
　　)
　　
　　例子1：
　　有表Table内容如下
　　COL1　COL2
　　　 1　1
　　　 2　1
　　　 3　2
　　　 3　1
　　　 4　1
　　　 4　2
　　　 5　2
　　　 5　2
　　　 6　2
　　分析功能：列出Col2分组后根据Col1排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
　　
　　SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
　　
　　结果如下：
　　COL1　COL2　Rank
　　　 1　1　　 1
　　　 2　1　　 2
　　　 3　1　　 3
　　　 4　1　　 4
　　　 3　2　　 1
　　　 4　2　　 2
　　　 5　2　　 3
　　　 5　2　　 3
　　　 6　2　　 5
　　
　　例子2：
　　TABLE：A （科目，分数）
　　数学，80
　　语文，70
　　数学，90
　　数学，60
　　数学，100
　　语文，88
　　语文，65
　　语文，77
　　
　　现在我想要的结果是：（即想要每门科目的前3名的分数）
    数学，100
　　数学，90
　　数学，80
　　语文，88
　　语文，77
　　语文，70
　　
　　那么语句就这么写：
　　
　　select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
　　where t.rk<=3;
　　
　　例子3：
　　合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
　　
　　SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
　　
　　结果如下：
　　Rank
　　4
　　
　　dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过
　　
　　例如：表
　　
　　A　　　　　　B　　　　　　C
　　a　　　　　liu　　　　　wang
　　a　　　　　jin　　　　　shu
　　a　　　　　cai　　　　　kai
　　b　　　　　yang　　　　 du
　　b　　　　　lin　　　　　ying
　　b　　　　　yao　　　　　cai
　　b　　　　　yang　　　　 99
　　
　　例如：当rank时为：
　　select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
　　 A　　　　　B　　　　　　 C　　　　　LIU
　　 a　　　　　cai　　　　　 kai　　　　　1
　　 a　　　　　jin　　　　　 shu　　　　　2
　　 a　　　　　liu　　　　　 wang　　　　 3
　　 b　　　　　lin　　　　　 ying　　　　 1
　　 b　　　　　yang　　　　　du　　　　　 2
　　 b　　　　　yang　　　　　99　　　　　 2
　　 b　　　　　yao　　　　　 cai　　　　　4
　　
　　而如果用dense_rank时为：
　　select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
　　 A　　　　　B　　　　　　 C　　　　　LIU
　　 a　　　　　cai　　　　　kai　　　　　1
　　 a　　　　　jin　　　　　shu　　　　　2
　　 a　　　　　liu　　　　　wang　　　　 3
　　 b　　　　　lin　　　　　ying　　　　 1
　　 b　　　　　yang　　　　 du　　　　　 2
　　 b　　　　　yang　　　　 99　　　　　 2
　　 b　　　　　yao　　　　　cai　　　　　3 

（3） ROW_NUMBER：用于取前几名，或者最后几名等

【语法】row_number() over (partition by 字段1 order by  字段2) 
【功能】表示根据字段1分组，在分组内部根据字段2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
row_number() 返回的主要是“行”的信息，并没有排名
主要功能：用于实现分页；取前几名，或者最后几名等

【示例】
表内容如下：
name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test

我想有一个sql语句，搜索的结果是 
A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test
实现: 
select name,seqno,description 
from(select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name) where id<=3;

（4） 利用row_number()实现分页查询 

--利用row_number()实现分页查询
select tt.* from 
(select row_number() over(order by lrrq asc) 行号,t.* 
from z_main t) tt
where 行号>10 and 行号<=20;

15 集合操作：并union、交intersect、差minus、（except只能用于SQLserver）

集合操作要求两个关系模式必须一致（属性名称、个数、字段类型等）。

① union (无重并集) ： 当执行 UNION时，自动去掉结果集中的重复行，并以第 一列的结果进行升序排序。 

② union all (有重并集) ：  不去掉重复行，并且不对结果集进行排序。

③ intersect (交集)：取两个结果集的交集，并且以第一列的结果进行升序排列。 

④ minus  (差集)： 只显示在第一个集合中存在， 在第二个集合中不存在的数据（ A减去B，在查询结果1中，不在查询结果2中）。 

⑤ except (差集)    注：except只能用于SQLserver。只显示在第一个集合中存在， 在第二个集合中不存在的数据（ A减去B，在查询结果1中，不在查询结果2中）。

差集举例：
table1:
f_name  f_date
name1   2009-6-1
name2   2009-6-2

table2:
f_name  f_date
name3   2009-6-2
name4   2009-6-3

查询f_date差集：
1、在SQLserver中用except（ 注：except只能用于SQLserver）：
select f_date from table1 
except 
select f_date from table2;

except查询结果：
2009-6-1

2、在oracle中用minus：
select f_date from table1 
minus 
select f_date from table2

minus查询结果：
2009-6-1

15.1  利用minus实现分页查询 

Oracle中实现分页方法二：
利用minus实现分页查询 
select rownum,t.* from  z_doctors_advice t where rownum<=20
mius
select rownum,t.* from  z_doctors_advice t where rownum<=10;

16 普通视图：封装SQL，是一个虚表。

普通视图为了简化开发，物化视图为了提高性能。

一、视图是从一个或几个基本表（或视图）导出的'虚拟表'，数据是随着基本表的更新而更新。

二、数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

注意注意：因为视图是一个虚拟表，所以你修改视图的数据时，实际上就是在修改基本表的数据。

三、view的作用：

① 简化了操作，把经常使用的数据定义为视图

② 安全性，用户只能查询和修改能看到的数据

③ 逻辑上的独立性，屏蔽了真实表的结构带来的影响

四、view的更新条件

①从多个表得出的视图不允许更新

②使用了分组、聚集函数的视图不允许更新

③从单个表通过投影选择操作得出的视图允许更新

普通视图为了简化开发，物化视图为了提高性能。
***创建视图语法（注意[]内的内容可以省略）：
create [or replace]  [force]  view 视图名[列表名]
as subquery                                       --select查询语句
[with check option]                               --带检查约束的视图
[with read only];                                 --只读视图

语法注释：
1 [or replace]：若所创建的视图已经存在，则Oracle会自动重建该视图，
  也就是说，如果原来没有就创建，已有就更新覆盖重建。

2 [force]：强制生成，可以创建一个带错误的视图，不管基表是否存在，Oracle都会创建该视图。

3 视图名[列表名]:组成视图的属性列名全部省略或者全部指定。如果省略属性列名，
  则隐含该视图由select子查询目标列的主属性组成。

3 subquery --select查询语句：可以是任意复杂的select语句，
   但通常不允许含有order by子句和distinct短语。

4 [with check option]：表示对该视图进行update,insert,delete操作时，
必须保证更新、插入或删除的行必须满足视图定义中的谓词条件（即必须满足as后的查询语句中的条件）。 

5 [with read only];--只读视图，只可以读取视图中的数据，不能对其修改。

***删除视图语法：
drop view  view_name;  --view_name是您要删除的视图名。

[with check option]：表示对该视图进行update,insert,delete操作时，必须保证更新、插入或删除的行必须满足视图定义中的谓词条件（即必须满足as后的查询语句中的条件）。 

[with read only]：只读视图，只可以读取视图中的数据，不能对其修改。 

键保留表：把主键保留下来的那个基本表。因此修改多表关联得出的视图，只允许修改键保留表的字段值。 非键保留表的字段只能查询不能修改。

17 物化视图MV：实际存在的表，存储了视图查询的结果数据。

普通视图为了简化开发，物化视图为了提高性能。

普通视图：数据库只存放普通视图的定义，而不存放视图对应的数据，其真实数据在基表中，即每次查询视图都是需要执行as后的查询语句。

物化视图：它保存了视图查询的实际数据副本，而不仅仅是查询逻辑。为了防止每次都查询，将结果集存储起来，这种有真实数据的视图，称为物化视图。

什么时候用物化视图？ 1 数据量很大+2 经常需要查询+3 涉及多表关联查询。

 物化视图：相当于查询单表，所以查询效率更快；但是需要占用存储空间。

普通视图为了简化开发，物化视图为了提高性能。
***创建物化视图语法：
create materialized view 视图名 
build [immediate|deferred]
refresh [fast|complete|force] 
[ 
on [commit|demand] | 
start with (start_time) next (next_time) 
] 
 [enable | disable] query rewrite
as 
subquery --创建物化视图用的查询语句 

解释：
0 [|] []内的可以省略，此时相当于把普通视图物化；| 两边表示根据实际需求选择其中之一。
1 materialized 实体化、物化
2 build [immediate|deferred] 视图的构建方式，默认是build immediate
3 immediate 立即的；是在创建物化视图的时候立即生成数据。
4 deferred 延迟的：是在创建物化视图时先不生成数据，以后根据需要再生成数据。
5 refresh [fast|complete|force] 视图的刷新机制，默认是refresh force
5.1 fast 快速刷新，增量刷新：只更新自上次刷新以来发生更改的部分。
    为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。
    create materialized view log on subquery查询语句中的表名 with rowid。
5.2 complete 完全刷新。相当于重新执行一次创建视图的查询语句。 
5.3 force 强制：强制自动选择前两个方式：能用增量就优先使用增量，不能增量就完全刷新。
6 commit 自动提交——自动刷新：当基本表中有数据提交的时候，立即刷新物化视图。
7 demand 需求、需要——手动刷新：手动触发一个命令，物化视图才会更新。
8 start with (start_time) next (next_time) ：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；
9 [enable | disable] query rewrite 是否支持查询重写。
   分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，
   Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，
   而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。 很少使用。
   enable：启用 ，disable：禁用，query：查询，rewrite：重写

***删除物化视图语法：
drop materialized view 视图名;

物化视图创建成功之后，会产生一个物化视图，以及与其同名的一个基本表。

物化视图的实质是通过查询语句去更新对应的表。 

18 序列Sequence：产生自动增长且连续的值。

为什么要用序列？？？因为Oracle在定义表的时候它不能定义自动增长，没这个功能，所以我们在插入值的时候需要从序列中取值。

序列是Oracle提供的用于产生一系列唯一数字的数据库对象。数据库系统按照一定规则自增的数字序列，因为自增所以不会重复。

序列常见应用场景
    1.    主键生成：通过序列为表的主键字段生成唯一值。
    2.    订单号、发票号：为业务逻辑生成唯一的编号。
    3.    批量插入数据：在插入大量数据时，快速生成连续的唯一值。

序列的主要特性
    1.    自动生成：序列会自动递增（或递减），无需手动维护。
    2.    并发安全：多个用户同时访问时，序列值不会冲突。
    3.    灵活性：可以控制初始值、递增量、最大值等。
    4.    可复用性：序列值生成独立于任何表，可以在多个表中使用。

注意事项
    1.    序列的连续性：
    •    如果事务回滚，序列值不会回退。
    •    并发情况下可能会跳过某些值。
    2.    性能优化：
    •    使用 CACHE 可以提高性能，但如果数据库崩溃，缓存的值可能会丢失。
    •    对于需要绝对连续的值，建议结合其他逻辑实现。

***查询序列的当前值：current：当前
select sequence_name.currval from dual;--查询序列的当前值，不管执行多少次，都是当前值
***查询序列的下一个值：next：下一个，接下来
select sequence_name.nextval from dual;--每执行一次，序列就往前走一个值

***创建序列：
CREATE sequence sequence_name
  START WITH start_value
  increment by increment_value  --increment：增量
  [MAXVALUE max_value | NOMAXVALUE]
  [MINVALUE min_value | NOMINVALUE]
  [CYCLE | NOCYCLE] --cycle 循环
  [CACHE cache_size | NOCACHE];  --cache 缓存，把cache_size个值放到内存里面，查询的时候更快
参数说明：
	•	START WITH：指定序列生成的起始值，默认是 1。
	•	INCREMENT BY：指定每次递增（或递减）的值，默认是 1。可以为负数。
	•	MAXVALUE / NOMAXVALUE：指定序列的最大值，默认是无最大值。
	•	MINVALUE / NOMINVALUE：指定序列的最小值，默认是 1。
	•	CYCLE / NOCYCLE：是否允许序列循环（超出最大或最小值后重头开始）。默认是 NOCYCLE。
	•	CACHE / NOCACHE：指定缓存的序列值数量，默认是CACHE 20。NOCACHE 不把值放进内存。

***查询序列的当前值：
select sequence_name.currval from dual;--查询序列的当前值,不管执行多少次，都是当前值
***查询序列的下一个值：
select sequence_name.nextval from dual;--每执行一次，序列就往前走一个值

***修改序列：
可以使用ALTER SEQUENCE修改序列的属性，但不能更改START WITH起始值或重置序列值。
ALTER SEQUENCE sequence_name
  INCREMENT BY 10   --指定每次递增（或递减）的值，默认是 1。可以为负数。
  MAXVALUE 10000    --指定序列的最大值为10000
  NOCYCLE;

***删除序列：
如果不需要序列，可以删除它：
DROP SEQUENCE sequence_name;

【实例1：创建查询序列】
create sequence sequence_test2
 start with 1
 increment by 1
 maxvalue 100000
 cycle     --一次循环的值不能小于一次缓存的数
 cache 100;--一次缓存的数有多少？cache值*增长值
 
 select sequence_test2.currval from dual;
 select sequence_test2.nextval from dual;
 drop sequence sequence_test2;
【实例2：利用序列插入ID】
create sequence seq_owners start with 11 increment by 1;--业主序列，为传入id做准备
insert into t_owners
  values
    (seq_owners.nextval,--通过序列生成
     v_name,
     addressid,
     housenumber,
     waternumber,
     sysdate,
     v_ownertypeid);

19 同义词Synonym：为数据库对象起别名。

同义词Synonym是一种数据库对象，用于为表、视图、序列、存储过程或其他对象创建一个替代名称（别名）。同义词可以用来简化访问或隐藏实际对象名称，提高数据库的灵活性和安全性。

同义词的作用
    1.    简化对象访问
    •    当对象位于其他用户模式（Schema）中时，使用同义词可以简化访问，无需每次指定模式名称。
    •    例如，HR.EMPLOYEES 可以通过同义词直接访问为 EMPLOYEES。
    2.    隐藏对象的实际名称和位置
    •    同义词可以充当一个逻辑层，隐藏实际的表名或存储过程名称，从而提高系统的灵活性。
    •    例如，重命名底层表时，只需更新同义词，而不需要修改所有的应用程序代码。
    3.    增强数据库安全性
    •    同义词允许用户访问底层对象而无需直接赋予对象的权限。
    •    例如，用户可以通过同义词访问 SCOTT.DEPT 表，而无需直接授予该用户对 DEPT 表的权限。
    4.    跨数据库访问
    •    在分布式数据库中，同义词可以用于引用远程数据库的对象，简化分布式查询。

同义词的分类
    1.    公有同义词（Public Synonym）
    •    为所有用户创建，任何用户都可以使用。
    •    使用 CREATE PUBLIC SYNONYM  synonym_name语句创建。
    2.    私有同义词（Private Synonym）
    •    为特定用户创建，仅创建者和被授权的用户可以使用。
    •    使用 CREATE SYNONYM  synonym_name语句创建。

同义词的注意事项
    1.    同义词并不存储数据
    •    同义词只是指向底层对象的引用，删除同义词不会影响实际数据。
    2.    需要确保目标对象的权限
    •    创建同义词后，用户仍需对目标对象具有适当的访问权限。
    3.    命名冲突
    •    如果公有同义词和私有同义词同名，Oracle 优先使用私有同义词。
    4.    远程对象引用
    •    可以通过数据库链接创建同义词，引用远程数据库中的对象。

常见应用场景
    1.    多模式协作
    •    方便不同用户共享和访问其他模式下的对象。
    2.    逻辑分离
    •    在应用程序中使用统一的同义词名称，方便切换底层数据库对象。
    3.    分布式数据库访问
    •    通过同义词屏蔽数据库链接的复杂性。

***创建同义词
1 私有同义词
CREATE SYNONYM synonym_name 
FOR schema_name.object_name;

示例：
CREATE SYNONYM emp_synonym 
FOR hr.employees;
这样，用户可以通过 emp_synonym 访问 hr.employees 表。

2 公有同义词
CREATE PUBLIC SYNONYM synonym_name 
FOR schema_name.object_name;

示例：
CREATE PUBLIC SYNONYM emp_public_synonym 
FOR hr.employees;
所有用户都可以通过 emp_public_synonym 访问 hr.employees 表。

***删除同义词
使用 DROP SYNONYM 或 DROP PUBLIC SYNONYM 删除同义词。

***删除私有同义词
DROP SYNONYM emp_synonym;

***删除公有同义词
DROP PUBLIC SYNONYM emp_public_synonym;

20 索引index：为了提高查询速度，相当于书本的目录。

【定义】索引是用于加速数据存取的数据库对象，合理的使用索引可以大大减少I/O次数，从而提高数据访问性能。

索引是一个表中一列或多列的值的集合，以及指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用：提高查询性能；减少'查询'时间；提高系统性能。

索引的优化场景
    1.    对经常用于查询条件的列创建索引。
    2.    对用于排序、分组或连接的列创建索引。
    3.    对主键和唯一性约束列，默认创建唯一索引。

【索引是为查询服务的，数据量特别大时才使用索引，在经常被用来查询的字段上建立索引】

***创建索引
create [unique | cluster] index 索引名
on 表名(字段1 asc/desc,字段2 asc/desc);

参数说明：
1 普通索引：不加[unique | cluster]时。
1 unique 唯一索引：此索引的每一个索引项只对应唯一的一条数据记录。
2 cluster 聚簇索引：索引项的顺序与表中记录的物理顺序一致的索引。
3 复合（组合）索引：(字段1 asc/desc,字段2 asc/desc)这里面有多个字段。
（经常需要查询多个字段的时候用复合索引）
4 在经常被用来查询的字段上建立索引，一个表只能有一个聚簇索引，可以有多个非聚簇索引。

***重建索引
当索引被频繁更新或表数据变化很大时，可以重建索引以优化性能。
ALTER INDEX index_name REBUILD;

***禁用索引
可以在特殊情况下临时禁用索引：
ALTER INDEX index_name UNUSABLE;

***删除索引
drop index 索引名; --删除索引

【示例】
1. 创建索引
CREATE INDEX index_name ON table_name(column_name);

2. 查看索引
查看表的索引信息：
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS 
FROM USER_INDEXES 
WHERE TABLE_NAME = 'EMPLOYEES';

查看索引的列：
SELECT INDEX_NAME, COLUMN_NAME 
FROM USER_IND_COLUMNS 
WHERE TABLE_NAME = 'EMPLOYEES';

3. 删除索引
DROP INDEX index_name;

20 PL/SQL和PL/SQL Developer的区别

20.1 什么是PL/SQL？什么是PL/SQL Developer？

PL/SQL（Procedural Language/SQL）则是一种编程语言，它是Oracle对标准SQL的扩展，结合了SQL的灵活性和高级编程语言的特性，允许在普通的SQL语句上添加编程语言的特点，如逻辑判断、循环等，从而实现复杂的功能或计算。

PL/SQL Developer是一个工具。PL/SQL Developer是一款专为Oracle数据库管理员和开发人员设计的集成开发环境（IDE），它提供了代码编辑、调试、数据库对象管理、数据查询与操作等功能，极大地提高了开发和管理效率。

总的来说，PL/SQL是实际编写数据库应用程序的编程语言，而PL/SQL Developer是用于开发和管理PL/SQL代码的工具。

21 PL/SQL编程语言相关知识

Oracle中游标、存储函数、存储过程、触发器都必须基于PL/SQL。 

（1）PL/SQL基本语法结构

PL/SQL语法结构三大块：declare声明变量→begin变量赋值→代码逻辑（判断、循环）→输出结果→异常处理→end；（除了declare begin end;其他的用不到的就不加）。

PL/SQL语法结构三大块：declare声明变量→begin变量赋值→代码逻辑（判断、循环）→输出结果→异常处理→end；（除了declare begin end;其他的用不到的就不加）。

PL/SQL语法结构三大块：declare声明变量→begin变量赋值→代码逻辑（判断、循环）→输出结果→异常处理→end；（除了declare begin end;其他的用不到的就不加）。

（1） PL/SQL基本语法结构：
[ declare
 --声明变量名1 数据类型(长度);
 --声明变量名2 数据类型(长度);
 --声明变量名n 数据类型(长度);
           ]
begin
    变量名:=变量值；--代码逻辑，进行赋值
[ exception
when no_data_found 
then
DBMS_output.put_line('没有找到账目数据！') --no_data_found 异常处理
when too_many_rows
then
DBMS_output.put_line('返回了多条账目数据！')--too_many_rows 异常处理
            ]--常见的两种异常处理
         end;

（2）PL/SQL声明变量的语法（变量：variable）

（2） 声明变量的语法：
变量名 数据类型(长度);

（3）PL/SQL变量赋值的语法

（3） 变量赋值的语法：
方法一：变量名:=变量值；--直接赋值
方法二：select 列名  into 变量名;--从数据库查出来给这个变量赋值。

（4）PL/SQL输出函数

PL/SQL中输出函数：
DBMS_output.put_line('你需要缴纳的水费是：' || v_money);

参数解释：
 put_line()内置的存储过程，相当于一个方法，调用它可以用来输出。 
 DBMS_output.所在的包，可以把一堆存储过程放进这个包里，put_line()是其中一个。

（5） 1—4的示例

实例1：方法一： 直接赋值
declare
  v_price   number(10, 2); --单价，有两位小数
  V_usenum  number; --水费字数，不定长度，默认18
  v_usenum2 number(10, 2); --吨数
  v_money   number(10, 2); --金额
begin
  v_price   := 2.45; --单价赋值
  v_usenum  := 9123; --水费字数赋值
  V_usenum2 := round(v_usenum / 1000, 2); --吨数
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('你需要缴纳的水费是：' || v_money);
  --DBMS_output.所在的包，可以把一堆存储过程放进这个包里，put_line()是其中一个
  --put_line()内置的存储过程，相当于一个方法，调用它可以用来输出
end;

输出：你需要缴纳的水费是：22.34

方法二：select 列名  into 变量名;--从数据库查出来给这个变量赋值。
declare
  v_price        number(10, 2); --单价，有两位小数
  V_usenum       number; --水费字数，不定长度，默认18
  v_usenum2      number(10, 2); --吨数
  v_money        number(10, 2); --金额
  v_lastmonth    number; --上月水表数
  v_currentmonth number; --当月水表数
begin
  v_price := 2.45; --单价赋值
  --select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  select usenum, num0, num1
    into v_usenum, v_lastmonth, v_currentmonth
    from t_account
   where year = '2024'
     and month = '12'
     and owneruuid = 2;--select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  V_usenum2 := round(v_usenum / 1000, 2); --吨数
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('你需要缴纳的水费是：' || v_money);
end;

（6）PL/SQL属性类型：(引用型  表名.列名%type ）+(记录型 表名%rowtype)

方法一：(引用型  表名.列名%type ）
方法一作用：引用某表某列的字段类型，使声明的变量和某表中某个字段的类型一样。

方法二：(记录型 表名%rowtype)
方法二作用：把某个表一行的记录赋值给变量。

【实例】
***方法一：(引用型  表名.列名%type ）
declare
  v_price        number(10, 2); --单价，有两位小数
  V_usenum       t_account.usenum%type; --属性类型：(引用型  表名.列名%type)
  v_usenum2      number(10, 2); --吨数
  v_money        number(10, 2); --金额
  v_lastmonth    t_account.num0%type;--属性类型：(引用型  表名.列名%type)
  v_currentmonth t_account.num1%type;--属性类型：(引用型  表名.列名%type)
begin
  v_price := 2.45; --单价赋值
  --select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  select usenum, num0, num1
    into v_usenum, v_lastmonth, v_currentmonth
    from t_account
   where year = '2024'
     and month = '12'
     and owneruuid = 2;--select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  V_usenum2 := round(v_usenum / 1000, 2); --吨数
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('你需要缴纳的水费是：' || v_money);
end;

***方法二：(记录型 表名%rowtype)
declare
  v_price        number(10, 2); --单价，有两位小数
  v_usenum2      number(10, 2); --吨数
  v_money        number(10, 2); --金额
  v_account      t_account%rowtype;--(记录型 表名%rowtype):台账行记录类型
begin
  v_price := 2.45; --单价赋值
  --select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  select * --(记录型 表名%rowtype):台账行记录类型赋值
    into v_account
    from t_account
   where year = '2024'
     and month = '12'
     and owneruuid = 2;--select 列名  into 变量名;--从数据库查出来给这个变量赋值。
  V_usenum2 := round(v_account.v_usenum / 1000, 2);--(记录型 表名%rowtype):台账行记录类型
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('水表数：'v_account.v_usenum ||'你需要缴纳的水费是：' || v_money);
end;

（7）PL/SQL常见异常处理no_data_found+too_many_rows

预定义异常/例外：Oracle本身给我们定义好的异常类型共21个。当PL/SQL程序违反 Oracle 规则或超越系统限制时隐式引发。

用户定义异常/例外：用户可以在 PL/SQL块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。

常见的异常处理：
（1）ORA-01403 no_data_found :使用select into 时未返回行。
（2）ORA-01422 too_many_rows :使用select into时，结果集超过一行。
（3）此时需要做exception when...then...异常处理。

***常见的异常处理：（1）ORA-01403 no_data_found :使用select into 时未返回行。
declare
  v_price        number(10, 2); --单价，有两位小数
  v_usenum2      number(10, 2); --吨数
  v_money        number(10, 2); --金额
  v_account      t_account%rowtype;--(记录型 表名%rowtype):台账行记录类型
begin
  v_price := 2.45; --单价赋值
  --select 整行  into 变量;--从数据库查出来给这个变量赋值。
  select * 
    into v_account
    from t_account
   where year = '2024'
     and month = '12'
     and owneruuid = 2000;
因为t_account表里没有owneruuid为2000的记录，所以未返回数据，执行会报错ORA-01403，因此需要做no_data_found异常处理。
  V_usenum2 := round(v_account.v_usenum / 1000, 2);
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('水表数：'v_account.v_usenum ||'你需要缴纳的水费是：' || v_money);
exception 
when no_data_found 
then
DBMS_output.put_line('没有找到账目数据！') ;--no_data_found 异常处理
end;

输出结果：没有找到账目数据！

***常见的异常处理：（2）ORA-01422 too_many_rows :使用select into时，结果集超过一行。
declare
  v_price        number(10, 2); --单价，有两位小数
  v_usenum2      number(10, 2); --吨数
  v_money        number(10, 2); --金额
  v_account      t_account%rowtype;--(记录型 表名%rowtype):台账行记录类型
begin
  v_price := 2.45; --单价赋值
  --select 整行  into 变量;--从数据库查出来给这个变量赋值。
  select * 
    into v_account
    from t_account
   where year = '2024'
     and month = '12';
     --and owneruuid = 2000
因为没有指定具体的owneruuid，所以返回多条记录，执行会报错ORA-01422，因此需要做too_many_rows异常处理。
  V_usenum2 := round(v_account.v_usenum / 1000, 2);
  v_money   := v_price * v_usenum2; --金额
  DBMS_output.put_line('水表数：'v_account.v_usenum ||'你需要缴纳的水费是：' || v_money);
exception 
when no_data_found 
then
DBMS_output.put_line('没有找到账目数据！'); --no_data_found 异常处理
when too_many_rows
then
DBMS_output.put_line('返回了多条账目数据！');--too_many_rows 异常处理
end;

输出结果：返回了多条账目数据！

（8）PL/SQL条件判断3个语法 

条件判断基本语法1:
if 条件 then
  业务逻辑
end if;

条件判断基本语法2：
if 条件 then
  业务逻辑
else
  业务逻辑
end if;

条件判断基本语法3： 
if 条件 then
  业务逻辑
elsif 条件 then
  业务逻辑
else
  业务逻辑
end if;

以下是条件判断实例：

***以下是条件判断实例：
declare
  v_price1  number(10, 2); --不足5吨的单价1
  v_price2  number(10, 2); --超过5吨不足10吨的单价2
  v_price3  number(10, 2); --超过10吨的单价3
  v_account t_account%rowtype; --已使用的水表记录数
  v_usenum2 number(10, 2); --使用吨数
  v_money   number(10, 2); --水费金额
begin
  --赋值方法1：对单价进行直接赋值
  v_price1 := 2.45; --不足5吨的单价
  v_price2 := 3.45; --超过5吨不足10吨的单价
  v_price3 := 4.45; --超过10吨的单价

  --赋值方法2：从数据库中提取出来给变量赋值
  select *
    into v_account
    from t_account
   where year = '2024'
     and month = '12'
     and id = '12';

  --把记录数换算成吨数
  v_usenum2 := round(v_account.usenum / 1000, 2);

   --条件判断：阶梯水费的计算
   if v_usenum2<=5 then
     v_money:=v_price1*v_usenum2;--计算不足5吨的水费
     elsif v_usenum2>5 and v_usenum2<=5 then
      v_money:=v_price1*5+(v_usenum2-5)*v_price2;--计算超过5吨不足10吨的水费
     else
       v_money:=v_price1*5*v_price2*5+(v_usenum2-10)*v_price3;--计算超过10吨的水费
       end if;
--输出  
DBMS_OUTPUT.put_line('您的水表记录数:'||v_account||'换算成吨是：'||v_usenum2'吨'||'您需要缴纳的水费是：'||v_money)
--异常处理
exception
  when no_data_found then
    dbms_output.put_line('错误提示：没有找到对应数据！') ;
    when too_many_rows then
      dbms_output.put_line('错误提示：返回多条数据记录！');
      end;

（9）PL/SQL中3种循环语法

***1 for循环语法结构：
for循环自动生成变量，但是改变量只能在loop end loop中间来用。
【for循环实例】：输出从1开始的100个数。
declare
begin
  for v_num in 1..100
    loop
      dbms_output.put_line(v_num);
      end loop;
  end;

***2 无条件循环语法结构：
loop 
  --循环语句
  end loop;
  
【无条件loop循环实例】：输出从1开始的100个数。
declare
v_num number;
begin
  v_num:=1;
  loop
    DBMS_output.put_line(v_num);
    v_num:=v_num+1;
    if v_num>100 then
      exit;
      end if;
    end loop;
end;
  
***3 有条件循环语法结构：--while后的条件成立的时候再进入循环
while 
  loop 
  --循环语句
  end loop;
  
【有条件while loop循环实例】：输出从1开始的100个数。
declare 
v_num number;
begin
  v_num:=1;
  while v_num<=100--v_num<=100再进入循环
    loop
        dbms_output.put_line(v_num);
        v_num:= v_num+1;
      end loop;
  end;

22 游标cursor：是一个数据缓冲区（结果集）。

SQL面向集合，一条SQL可以处理一条或多条记录。而主语言是面向记录的，一组主变量一次只能放一条记录，所以引入游标cursor，通过移动cursor指针来决定获取哪一条记录。

结合上图：游标其实就是系统为用户开设的一个数据缓冲区，存放SQL语句的查询结果。通过打开游标，移动指针，一行接一行的结果值提取出来赋给对应变量。

22.1 PL/SQL中的游标语法结构

【PL/SQL中游标语法结构】：
declare
cursor 游标名称 is SQL语句;--声明游标
begin
open 游标名称; --打开游标
loop          --进入循环
  fetch 游标名称 into 变量;--抓取结果值赋给变量
  exit  when 游标名称%notfound;--游标指针走到底了，就退出
dbms_output.put_line();--输出结果集
  end loop;--结束循环
  close 游标名称;--关闭游标
end;--结束plsql结构体

【实例】：打印业主类型为1的价格表。
select * from t_pricetable where ownertypeid = 1;--该查询结果有三条记录，是个结果集。
--游标 输出结果集
declare
cursor cur_test1 is select * from t_pricetable where ownertypeid=1;--声明游标
v_pricetable t_pricetable%rowtype;--声明行记录类型变量
begin
  open cur_test1;
  loop
    fetch cur_test1 into v_pricetable;--提取游标指针对应的行值
    exit when cur_test1%notfound;--指针走到底了退出循环
    dbms_output.put_line('价格:'||v_pricetable.price||
    '吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
    end loop;
  close cur_test1;
  end;

***带参数的游标
declare
cursor cur_test1(v_ownertype number) is
 select * from t_pricetable where ownertypeid=v_ownertype;--声明带参数的游标
v_pricetable t_pricetable%rowtype;--声明行记录类型变量
begin
  --open cur_test1(1);--打开为ownertypeid=1的价格表
  open cur_test1(2);--打开为ownertypeid=2的价格表
  loop
    fetch cur_test1 into v_pricetable;--提取游标指针对应的行值
    exit when cur_test1%notfound;--指针走到底了退出循环
    dbms_output.put_line('价格:'||v_pricetable.price||
    '吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
    end loop;
  close cur_test1;
  end;

***for循环带参数的游标
declare
cursor cur_test1(v_ownertype number) is
 select * from t_pricetable where ownertypeid=v_ownertype;--声明带参数的游标
begin
  for v_pricetable in  cur_test1(1)--（）里是参数值
      loop
    dbms_output.put_line('价格:'||v_pricetable.price||
    '吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
    end loop;
  end;

22.2 数据库系统工程师教材中的游标语法结构

  exec sql declare 游标名 cursor for

  select 语句;--定义游标cursor,说明性语句，其中的select语句并不执行

  exec sql open 游标名;--打开游标cursor,执行游标定义中的SELECT语句，
  同时游标处于活动状态。cursor是一个指针，此时指向查询结果第一行之前。

  exec sql fetch 游标名 into 变量表;--推进游标cursor,该语句执行时，
  游标推进一行，并把游标指向的行（称为当前行）中的值取出，送到共享变量中。

  exec sql close 游标名;--关闭游标cursor,该语句关闭游标，
  使它不再和查询结果相联系。游标关闭后，后面还可以再打开。

23 存储函数（自定义函数）function：优化查询，简化开发。

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL进行逻辑的处理，它的作用是可以简化开发。

***存储函数语法结构：
create or replace function 函数名称 --之前没有就新建，已有就覆盖更新
(参数名称 参数类型,参数名称 参数类型,...)--不用指明长度
return 结果变量数据类型--不用指明长度
is
变量声明部分;--需要指明长度
begin
  逻辑部分;
  return 结果变量;
  [exception 
    异常处理部分;]
    end;
    
【案例】创建存储函数，根据地址ID来查询地址名称。
create or replace function fun_getaddress
(v_id number)
return varchar2
is
V_name varchar2(30);
begin
  --查询地址表
  select name into v_name from t_address where id=v_id;
  return v_name;
  end;
执行后function下会有一个对应名称的绿色图标，说明创建成功！

***测试该存储函数
select fun_getaddress(3) from dual;--id=3的地址信息。

***自定义存储函数的应用【优化查询，简化开发】
select id,name,fun_getaddress(addressid) from t_owners;

24 存储过程procedure：主语言调用它，提高程序执行的效率。

思想：存储过程把业务逻辑都写在数据库里 ，编程语言就不用写逻辑，直接调存储过程的结果值，大大提高了程序执行的效率。

1. 可以包含事务，是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过call调用存储过程的名称并给出参数来执行。

2.procedure中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

3.提高程序执行的效率：由于procedure在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快，可以直接通过存储过程的名称进行'调用'。

24.1 存储过程语法结构

***Oracle存储过程语法结构
create [or replace] procedure 存储过程名称
in 参数1 数据类型，out 参数2 数据类型，in out 参数3 数据类型）--未指明时，默认是in
is | as
变量声明部分;
begin
  逻辑部分;
  [exception 
  异常处理部分]
  end;
  
 ***调用存储过程
 方法一：Call 存储过程名称（参数1，参数2...）;
 方法二：begin 存储过程名称（参数1，参数2...）; end;
 
 ***删除存储过程：
 Drop 存储过程名称;

24.2 创建和调用不带传出参数的存储过程 

create sequence seq_owners start with 11 increment by 1;--业主序列，为传入id做准备

***实例1：创建不带传出参数的存储过程
create or replace procedure pro_owners_add
(v_name in varchar2,--名称,没有指名传入或者传出参数时，默认是传入in
addressid in number,--地址编号
housenumber in varchar2,--门牌号
waternumber in varchar2,--水表号
v_ownertypeid in number--业主类型
)
is 
begin
  insert into t_owners
  values(seq_owners.nextval,--通过序列生成
         v_name,addressid,housenumber,waternumber,sysdate,v_ownertypeid);
     commit;
  end;

***调用不带传出参数的存储过程方法一：
call pro_owners_add('scott',2,'22333','66888',1);
commit;

***调用不带传出参数的存储过程方法二：
begin 
  pro_owners_add('张三',2,'85694','56892',1);
end;

--提交之后可以查看t_owners表看一下这两行有没有插入成功。
select * from t_owners;

24.3 创建和调用带传出参数的存储过程（只能用declare begin end;来调用）

create sequence seq_owners start with 11 increment by 1;--业主序列，为传入id做准备

***实例2：创建带传出参数的存储过程
create or replace procedure pro_owners_add
(v_name  varchar2,--名称,没有指名传入或者传出参数时，默认是传入in
addressid in number,--地址编号
housenumber in varchar2,--门牌号
waternumber in varchar2,--水表号
v_ownertypeid in number,--业主类型
v_id out number--传出参数：把业主id返回来
)
is 
begin
  select seq_owners.nextval into v_id from dual;--对传出参数ID赋值
  insert into t_owners
  values(v_id,v_name,addressid,housenumber,
         waternumber,sysdate,v_ownertypeid);--新增业主
     commit;
  end;

***调用带传出参数的存储过程【只能用declare begin end;来调用】：
declare
v_id number;--把传出参数在这里声明
begin 
  pro_owners_add('李四',3,'48965','25863',2,v_id);
  dbms_output.put_line('传出参数业主ID是：'||v_id)
end;

select * from t_owners where id=v_id;--查询表中是否有记录

24.4 数据库系统工程师教材中的存储过程语法结构

***创建存储过程的语法结构
create procedure 存储过程名称 
（in 参数1 数据类型，out 参数2 数据类型，in out 参数3 数据类型）
declare 局部变量 变量类型
as
begin
  sql 
Notfound →exit 若没有找到就退出循环
Exception →rollback 若遇异常就回滚
Commit/rollback--显示提交或回滚
Return 数字（默认0：成功   1：失败）--提示成功与否
  end;

参数解释：
1 参数的数据类型只需要指明类型名即可，不需要指定'宽度'。具体宽度由外部调用者决定。
2 in：为默认值，表示该参数为输入型参数，在过程体中值一般不变。
3 out：表示该参数为输出参数，可以作为存储过程的输出结果，供外部调用者使用。
4 in out：既可作为输入参数，也可作为输出参数。

***调用存储过程
Call 存储过程名称（参数1，参数2...）;

***删除存储过程
Drop 存储过程名称;

25 触发器Trigger：建立在表上条件满足时自动执行触发动作。

【定义】：触发器（Trigger）是存储在数据库中的一段 PL/SQL 程序，简单来说，触发器就像一个“监听器”，用于在数据库表上发生某些事件（如插入、更新、删除）时自动执行你事先编写好的代码。触发器的执行是由事件触发的，而不是显式调用的。

【作用】：
    1.操作自动化
    • 当发生某些操作时，自动更新其他表或执行其他逻辑。
    2.数据完整性保障（数据确认）
    • 实现更复杂的业务规则，例如验证数据合法性。
    • 确保表之间的数据一致性。
    3.安全性检查
    4.审计与日志记录
    5.数据的备份与同步

【类别】：
1. 按时间点分类：
• 前置触发器：before 触发器：在事件发生前触发。【还没有提交，还可以修改】
• 后置触发器：after触发器：在事件发生后触发。【已经提交，无法修改】
• instead of 触发器：替代执行事件，用于视图。
2. 按触发范围分类：
• 行级触发器：对表中每一行变化触发，必须加 for each row。
• 语句级触发器：针对整个SQL语句触发一次，必须加for each statement。

触发器的语法一般包含以下部分：
• 触发条件：定义触发器在什么事件下执行（如插入、更新、删除）。
• 触发时间：定义触发器是在操作执行前（before）还是执行后（after）触发。
• 触发范围：是针对每一行数据（for each row）还是整个 SQL 语句for each statement。

25.1 触发器的语法结构

***触发器Trigger语法结构：
create [or replace] trigger 触发器名称
before | after--只能选择一个
[delete][or insert][or update of 列名] 
--update of列名可写多个用逗号分隔,表示仅在这些列被修改时触发。
--若update不加 of列名，则修改表中任何一列都会触发。
on 表名--若删除该表，则自动删除触发器
[for each row | for each statement]
--可选，默认for each statement，行级触发器时必须选用for each row
  [when 触发条件]--可选，相当于开关，仅当条件满足时触发
    declare
    ...
    begin
    触发动作(触发器逻辑:SQL或PL/SQL语句)--触发动作自动提交，不用写commit
    end;

***需要用到伪记录变量：
可以通过[:old.列名]获得这一列被修改之前也就是旧的数据。--例:old.name
可以通过[:new.列名 ]获得这一列被修改之后也就是新的数据。--例:new.name

参数解释：
1 trigger在此操作前before还是后after触发。
2 表中哪些行或列变动后触发,delete删除行,insert插入行,update修改表中的值时激发。
  (也可用update of 列名 指定是哪列的值被修改)。
2 [delete][or insert][or update of 列名]：两种两种以上情况都要触发的话，中间用or连接。
4 哪个表上的行被delete insert或哪些列被update后触发。
5 for each row表示为行级触发器，对每一个被影响的元组（即每一行）执行一次触发过程。
6 for each statement表示为语句级触发器，对整个事件只执行一次触发过程，为默认方式。
7 触发条件：指明当什么条件满足时，执行下面的触发动作(例如：余额<0,将余额置于0,并写入贷款表)。
8 触发动作：定义触发动作，即当触发条件满足时，需要数据库自动执行什么。

***修改触发器
alter trigger 触发器名称 before/after
delete/insert/update of 列名
on 表名--哪个表的行或列发生变动后激发触发器
as
 declare
    ...
    begin
    触发动作(触发器逻辑:SQL或PL/SQL语句)--触发动作自动提交，不用写commit
    end;

***删除触发器
drop trigger 触发器名称;

需要用到伪记录变量：***伪记录赋值就等于修改***
可以通过[:old.列名]获得这一列被修改之前也就是旧的数据。--例:old.name
可以通过[:new.列名]获得这一列被修改之后也就是新的数据。--例:new.name 

25.2 before 触发器：在事件发生前触发。

***before触发器案例:
需求：当用户输入本月累计表数后，自动计算出本月使用数。
create or replace trigger tri_account_num1--account表中num1代表本月累计数
before
update of num1
on t_account
for each row
  declare
  
  begin
    --通过伪记录变量值来修改对应字段的值，赋值就等于修改
    :new.usenum := :new.usenum1-:old.usenum0;--通过赋值就等于修改
    --:new.usenum 本月使用数
    --:new.usenum1 本月累计表数
    --:old.usenum0 上月累计表数
    end;

25.3  after触发器：在事件发生后触发。

***after触发器案例:
【体现审计功能】需求：当用户修改了业主信息表的业主姓名时，记录修改前和修改后的值。
1 创建业主名称修改日志表：用于记录业主更改前后的名称
create table t_owners_log
(updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
commit;
select * from t_owners_log;

2 创建后置触发器，自动记录业主更改前后日志
create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row 
  declare
  begin
    --向t_owners_log插入记录。
    insert into t_owners_log 
    values(sysdate,:newid,:old.name,:new.name);--触发器自动提交插入
    end;
    
3 这个update提交之后，通过触发器自动执行，t_owners_log日志表就自动生成一条记录。
upadate t_owners set name='李四' where ID=4;

25.4 数据库系统工程师教材中的触发器语法结构

1  用于复杂的业务规则或要求，预编译效率高，不能包含事务，可以调用procedure。
2  触发条件成立的时候，触发动作会'自动执行'，不能被调用;
3  trigger可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
4  不能在临时表或系统表上创建trigger，但trigger可以引用临时表。
5  在一个表上最多只能建立6个触发器。
6  缺点：不能返回数据，破坏代码结构，维护困难。 

***创建触发器语法：
 create trigger 触发器名称  before/after --trigger在此操作前before还是后after触发
 delete/insert/update of 列名--表中哪些行或列变动后激发trigger,delete删除行,insert插入行,update修改表中的值时激发(也可用update of 列名 指定是哪列的值被修改)
 on 表名--哪个表上的行被delete insert或哪些列被update后激发trigger
 referencing new row as nrow --referencing 引用
--referencing：trigger运行过程中，系统会生成两个临时视图，分别存放更新前和更新后的值
 referencing old row as orow 
--对于行级触发器，为old row和new row; 
--对于语句级触发器，为old table和new table，默认old
 for each row--表示为行级触发器，对每一个被影响的元组（即每一行）执行一次触发过程。
 for each statement--表示为语句级触发器，对整个事件只执行一次触发过程，为默认方式。
 when 触发条件--指明当什么条件满足时，执行下面的触发动作(余额<0,将余额置于0,并写入贷款表)
   begin
     触发动作--定义触发动作，即当触发条件满足时，需要数据库做什么
     end;

***修改触发器
alter trigger 触发器名称 before/after
delete/insert/update of 列名
on 表名--哪个表的行或列发生变动后激发触发器
as
begin
  SQL语句
  end;

***删除触发器
drop trigger 触发器名;

至此，恭喜您！圆满完成了学习Oracle 数据库的学习之旅！

本文完成于2024-12-15 10:48:01，原创不易，点个关注点个赞吧。

本文完成于2024-12-15 10:48:01，原创不易，点个关注点个赞吧。

本文完成于2024-12-15 10:48:01，原创不易，点个关注点个赞吧。

学习Oracle到此正式结束，快快实践熟能生巧吧！接下来的是本人在工作中的一些实际问题记录。

学习Oracle到此正式结束，快快实践熟能生巧吧！接下来的是本人在工作中的一些实际问题记录。

学习Oracle到此正式结束，快快实践熟能生巧吧！接下来的是本人在工作中的一些实际问题记录。

26 工作中常见时间格式转换

常见1： t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') --lrsj数据类型转为date（用在where后条件）

select t.*,t.rowid 
from m_detail t
where 
t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and
t.lrsj <=TO_DATE('2024-01-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and t.ksbm='1050';

常见2：  replace(substr(cyrq,1,10),'-') cysj--cyrq数据类型为varchar2（用在select查询后）

select distinct zyh,
replace(substr(cyrq,1,10),'-') cysj,
to_number(to_char(lrsj,'yyyymmdd')) jssj
from z_jzjl 
where  zhbj='0';

常见3：  to_char(lrsj,'yyyymmdd')--lrsj 数据类型是date（用在select查询后）

select t.hj, 
to_char(lrsj,'yyyymmdd') lrsj,
t.*,t.rowid 
from m_detail t
where 
t.lrsj >=TO_DATE('2024-01-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 and
t.lrsj <=TO_DATE('2024-01-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
 and t.ksbm='1050';

27 查询表中某列字段相同的重复数据

SQL 查询表中某列字段相同的重复数据的方法https://blog.csdn.net/pleaseprintf/article/details/131502019?spm=1001.2014.3001.5506

常用方法：使用 COUNT() 函数：下述查询语句中，我们通过 GROUP BY ZYH对表中的数据进行分组，并使用 COUNT(*) 函数计算每个分组中的记录数。然后使用 HAVING 条件筛选出记录数大于 1 的分组，即表示重复数据。

例：查询表中某字段相同的重复数据：

--查询9月份出院列表中住院号相同的重复记录。
select zyh,count(*)
  from z_out_list t
 where t.lrrq >= '20240901'
   and t.lrrq <= '20240930'
   group by zyh 
   having count(*)>1;