Oracle数据库学习记录
Oracle数据库
一、数据库分类
- 关系型(模型)数据库
MySQL、Oracle、SQLServer、H2(内存数据库，单元测试使用)
- 非关系型数据库
NoSQL、MongoDB、Redis(不用SQL语言操作的数据库)、MemCache（内存数据库）
关系：表，表和表联系
主键：唯一区分一条记录,一个列或多列组合
外键：在别的表中的主键
面试点：Database DBMS 服务器
三范式：压缩数据库空间，范式太多会增加查询的时间
- 第一范式：字段不能再拆分了。
- 第二范式：有主键，能唯一区分一条记录
- 第三范式：不存在其他表中的非主键字段（外键）
二、Oracle物理存储结构文件
- 控制文件*.ctl sqlplus 查询 select name from v$controlfile;
- 数据文件*.dbf 一个数据文件只属于一个表空间 一个表空间多个数据文件
- select file_name,tablespace_name,bytes,autoextensible from dba_data_files;
- 日志文件*.log 恢复数据库数据（备份文件，归档重做文件（日志文件写满后归档），参数文件pfile（parameter file） ，spfile(server parameter file)）
三、逻辑结构
表空间 ：
system系统表空间，数据字典 sysaux辅助系统表空间 temp临时表空间（排序等一些临时数据使用）users用户表空间，undotbs1回退表空间，撤销表空间（类似于回收站）
select name from v$tablespace;
- 创建表空间，指定表空间的名称，表空间的文件位置，文件大小，是否自动扩展
create tablespace tab1 datafile 'D:\app\Wjg\oradata\orcl\tab1.dbf' size 88k;
- 修改文件大小
alter database datafile 'd:\app\Wjg\oradata\orcl\tab1.dbf' resize 99k;
- 删除表空间
删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
- 给表空间增加文件
alter tablespace tab1 add datafile 'd:\app\wjg\oradata\orcl\tab1_2.dbf' size 87k;
增加表空间大小有三种方法：
- alter database datafile 'd:\app\Wjg\oradata\orcl\tab1.dbf' resize 99k;
- alter database datafile 'd:\app\Wjg\oradata\orcl\tab1_2.dbf' autoextend on next 1k maxsize 100k;
- alter database tempfile 'd:\app\Wjg\oradata\orcl\tab2_temp.dbf' resize 99k;
- alter tablespace tab1 add datafile 'd:\app\wjg\oradata\orcl\tab1_2.dbf' size 87k;
当文件被表空间引用时，不要直接物理删除文件，否则会破坏数据库
给指定表空间创建临时表空间
用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象
创建临时表空间
create temporary tablespace tab1_temp tempfile 'D:\app\wjg\oradata\orcl\tab1_temp1.dbf' size 3m;
创建用户（默认的表空间是user表空间）
create user ychs1 identified by 123456;
删除用户
drop user ychs1;
创建用户并绑定指定的表空间和临时表空间
create user ychs1 identified by 123456 default tablespace tab1_data temporary tablespace tab1_temp;
授权用户
grant create session,create table,unlimited tablespace to ychs1;
用户登录
sqlplus ychs1/123456
用户创建表
create table t1(id int);
删除表空间，删除临时表空间
drop tablespace tab1_data including contents and datafiles;
drop tablespace tab1_temp including contents and datafiles;
创建一个表空间
create tablespace tab2_data datafile 'd:\app\wjg\oradata\orcl\tab2_data.dbf' size 1m;
创建一个临时表空间
create temporary tablespace tab2_temp tempfile 'd:\app\wjg\oradata\orcl\tab2_temp.dbf' size 3m;
为什么要创建临时表空间https://www.cnblogs.com/zhangyingai/p/7082594.html
更改用户ychs1的表空间和临时表空间
alter user ychs1 default tablespace tab2_data;
alter user ychs1 temporary tablespace tab2_temp;
段：
1、数据段 2、索引段 3、回滚段 4、临时段
在执行CREATE INDEX ,select order by, select distinct , select group by等SQL语句的时候Oracle数据库会为这些语句分配一个临时段，
在数据库管理中，若要经常执行上面这类SQL语句时，最好调整SORT_AREA_SIZE初始化参数，来增大排序区
数据区：磁盘分配最小单位
块：最小的逻辑单位
查看块的大小 ：show parameters block
set line 100
四、内存结构
SGA(系统全局区)
各种池，各种缓存，数据字典，主要和系统有关
PGA(程序全局区)
用户会话，主要和用户有关
五、SQL(结构化查询语言)
面试题：
SQL执行顺序，from，where，group by，having，select，distinct，order by，limit
Structured Query Language
数据类型：
NUMBER（数值类型）38位、number(m,n)小数位，整数位m-n;
CHAR（定长字符串）2000Byte、
VARCHAR2（变长字符串）4000Byte、
DATE（日期）、
long（变长最长2G）、
TIMESTAMP时间戳、
BLOB（大数据类型）（4G-1）db_block_size、
CLOB（大数据类型）
如果给定长的字符串设置了太大的空间，若要使其变短使用trim(属性)函数
先：update t1 set name=trim(name);
再：alter table t1 modify name char(10);
DDL:
数据定义语言（data define language)，create 、drop 、alter；主要是用来操作对象的，不针对数据
数据类型
用户的表：select table_name,tablespace_name from all_tables where owner='YCHS1'; select table_name from user_tables;
创建一个表并指定主键： create table t_student(id number(10) primary key);
给表增加属性：
alter table t1 add name char(10);
alter table t1 add address varchar2(100) not null;
查看属性： desc t1;
修改表的字段类型：alter table t1 modify address varchar2(20);不能小于已知长度的最大值
为此列指定的允许精度 alter table t1 add chinese number(4,1);总共四位，小数会进行四舍五入
删除属性：alter table t1 drop column math;
删除多个属性：alter table t1 drop (math,english);
表名重命名：alter table t1 rename to t2;
属性重命名：alter table t1 rename column chinese to chinese_score;
序列器
创建序列器 create sequence seq_1;
- 取下一个新值nextval
- 取当前值currval
DML:
数据操作语言（data manipulation language），insert、update、delete（清空记录不释放空间）、truncate（清空记录释放空间）；
往表中插入一条记录：insert into t1(id,name,address) values(1,'name1','add1');
更新一条记录： update t1 set birthday=sysdate where id=1;
更新属性的长度去除空格：update t1 set name=trim(name);
插入一行：insert into t1(id,name,address,chinese,math,birthday) values(seq_1.nextval,'name','address',100,100,sysdate);
删除表信息：delete from t_student;产生回滚信息
截断表信息：truncate table t_student;物理删除
DQL:
查看所有的表
select table_name from user_tables;
数据查询语言（data query language ），select；主要查询；
查看属性的字符长度：select length(name),length(address) from t1;
别名：
sqlplus命令：col ename heading 姓名
select id as 编号,clazz 班级,join_date 报道时间 from t_student;
不是表中的列，时计算出来的结果列，列名是一个较长的表达式，用一个别名表示。（带有表达式和别名的select语句）
select comm+sal as money from emp;
多表查询时，给表起别名，可以简化表名。
筛选查询：
查30部门的人：
select * from emp where deptno=30;
查30部门职位是销售员：（包含逻辑筛选）
select * from emp where deptno=30 and job='SALESMAN';
查30部门职位是销售员的员工，或部门是20的员工：
select * from emp where deptno=20 or (deptno=30 and job='SALESMAN');
distinct显示不重复记录
select distinct deptno from emp;
选择每个部门下的职位
select distinct deptno,job from emp;
去重和分组功能相同，分组可以统计。
模糊查询
like关键字
- 两个通配符 _(代表有且仅有一个字符) %(代表0个或多个字符)
select ename from emp where ename like 'K___';
select ename from emp where ename like '%K%';
模糊查询对性能的影响
模糊匹配放在后面影响不大，放在前面失去索引效果，全表扫描。
in关键字
匹配一组信息中的某一个时用到
select empno,ename,job from emp where job in('PRESIDENT','MANAGER');
not关键字
between关键字
大于等于第一个值，小于等于第二个值
is null关键字
select * from emp where comm is null;
分组查询：
一般和聚集函数一起使用，为了使用没有歧义，建议select后面的列名，和group by后面的列名一致。
group by字段
select deptno,job from emp group by deptno,job;
排序
按照部门升序，工资降序排列
select deptno,sal from emp order by deptno,sal desc;
where 、group by、 order by 的顺序
选择工资大于800，的部门，按照升序排列；
- 先where过滤
- 再通过部门分组
- 最后通过部门号排序
select deptno from emp where sal>800 group by deptno order by deptno desc;
select deptno,avg(sal) avg_sal from emp group by deptno having avg(sal)>2000;
-
先按照部门分组
-
统计平均工资，别名不产生
-
having过滤
-
返回结果此时生成别名。
（面试点）伪列rowid、rownum：
select rownum from emp;
生成伪列号：
select t.*,rownum from (select * from emp) t;
对伪列号进行查询（有分页的效果）：
select * from (select t.*,rownum rn from (select * from emp) t) where rn>(pagenumber-1)*5 and rn<=pagesize*5;
UNION 、UNION ALL
select empno from emp union select empno from emp;
UNION 去掉相同的记录
select empno from emp union all select empno from emp;
UNION ALL 不去掉相同的记录
select empno from emp union select sal from emp;数据类型和列数量一致就可以
多表关联查询:
某个字段在多张表中有同样的名称，SQL语句中应该在字段前加表名来区分
内连接：（有限制条件的交叉连接）inner join
select * from emp inner join dept on dept.deptno=emp.deptno;
select * from emp,dept where emp.deptno=dept.deptno;
外连接：（左连接left join就是以左为主，右边right join的记录可能为空， 右连接就是以右为主左边的可能为空）
select empno,emp.deptno,dname from emp left join dept on emp.deptno=dept.deptno;
外连接的Oracle写法，谁有加号就以谁为主，另外一边会有空值出现。
select e.empno,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+);
完全外连接：（相当于左连接和右连接的并集）full join
自连接：（显示雇员和管理者之间的对应关系）inner join提问
select em2.ename 上层管理,em1.ename 下属员工 from emp em1 join emp em2 on em1.mgr=em2.empno;
交叉连接：(笛卡尔积连接) 理解多表关联查询的原理cross join
select e.deptno 员工表的部门编号,e.empno 员工编号,e.ename 员工姓名,d.deptno 部门编号 from emp e cross join dept d;
子查询：
单行子查询：返回一行数据的子查询语句。
select empno,ename,sal from emp where sal>(select min(sal) from emp) and sal<(select max(sal) from emp);
多行子查询：
-
使用in运算符
-
使用any运算符 大于查询结果的最小值
查询工资大于10号部门的任意一个员工工资的其他部门的员工信息。意思是大于10号部门员工的最低工资
select deptno,ename,sal from emp where sal > any(select sal from emp where deptno=10) and deptno!=10;
- 使用all运算符 大于查询结果的最大值
关联子查询：
使用关联子查询检索工资大于同职位的平均工资的员工信息
select empno,ename,sal from emp f where sal>(select avg(sal) from emp where job=f.job) order by job;
嵌套查询：
select * from emp,(select deptno from emp where job='PRESIDENT') emp2 where emp.deptno=emp2.deptno;
select * from emp where deptno=(select deptno from emp where job='PRESIDENT');
面试点：将子查询in转换成exists
select * from emp where deptno in (select deptno from emp where job in ('PRESIDENT','CLERK'));
exists:只有里面的SQL语句被满足的时候才可以显示一条记录
select * from emp where exists(select 4 from emp emp2 where emp2.job='PRESIDENT' and emp.deptno=emp2.deptno);
如何将重复的电话号码去除；
- 找到重复的电话号
select phone from t_student group by phone having count(phone)>1;
- 将重复的记录删除
delete from t_student where id in(select id from t_student t1,(select min(id) min,phone from t_student group by phone having count(phone)>1) t2 where t1.phone=t2.phone and t1.id!=t2.min);
删除有两遍重复的emp2表
delete from emp2 where rowid not in (select min(rowid) from emp2 group by empno);
DQL中的函数：
函数（字符 LOWER/UPPER/LENGTH/SUBSTR/INSTR/TRIM）；
select lower (ename) from emp;
select upper(lower(ename)) from emp;
substr：第一个参数是字符串内容，第二个参数从第几个开始，第三个参数表示选几位数。
select substr(ename,0,3) from emp;
instr(字符串内容，待检查的字符)返回参数2在参数1中的位置
选择字符A再属性中出现的位置
select instr(ename,'A') from emp;
数学：
select round(1.1),round(1.5),trunc(1.1),trunc(1.9),mod(5,2),mod(5,3) from dual;
员工表中员工入职年限：
select sysdate-hiredate,(sysdate-hiredate)/365,trunc((sysdate-hiredate)/365) year,hiredate from emp;
日期
SYSDATE、ADD_MONTHS、LAST_DAY、MONTHS_BETWEEN
ADD_MONTHS(当前日期,增加的月份)
select add_months(sysdate,1),last_day(sysdate) from dual;
select hiredate,months_between(sysdate,hiredate),months_between(sysdate,hiredate)/12 from emp;
转化函数
TO_DATE/TO_CHAR/TO_NUMBER/NVL/NVL2
日期格式：yyyy-mm-dd HH24:mi:ss
select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mi:ss') from dual;
或
alter session set nls_date_format = 'yyyy"年"mm"月"dd"日"';
字符转日期：
insert into t1 values(to_date('2022-01-08 16:26:29','yyyy-mm-dd HH24:mi:ss'));
null值的转化：
员工工资+奖金 nvl(p1,p2),如果p1为空返回p2，否则返回p1
select sal,nvl(comm,0) comm,sal+nvl(comm,0) money from emp;
员工奖金不为空 则加100，否则给200 nvl2(p1,p2,p3)如果p1为空，则返回p3，否则返回p2
select sal,comm,nvl2(comm,comm+100,200) from emp;
decode
decode(p1，p2，p3，...，pn)p1是数据源，如果p1=p2 返回p3，p1=p4 返回p5... 如果都不满足返回pn
select decode(job,'CLERK','文员','SALESMAN','销售员','MANAGER','经理','PRESIDENT','董事长','其他') from emp;
行转列学生成绩表
select name,sum(decode(subject,'MBA',score)) MBA,sum(decode(subject,'数学',score)) 数学 from t_score group by name;
min/max/avg/sun/count/having
按照部门显示各部门的工资
select min(sal),max(sal),sum(sal),avg(sal) from emp;
group by 和 统计函数的综合
select deptno, min(sal),max(sal),sum(sal),avg(sal) from emp group by deptno having avg(sal)>2000;
DCL:
数据控制语言（data control language），grant、revoke；授权和回收授权
create user ychs2 identified by 123456 default tablespace tab2_data temporary tablespace tab2_temp;
user YCHS2 lacks CREATE SESSION privilege; logon denied
grant create session to ychs2;
revoke create session from ychs2;
角色
dba_role_prives 用户角色表
dba_sys_privs 角色权限表
dba_roles角色
查找一个用户有哪些角色
select * from dba_role_prives where grantee='SCOTT';
自定义角色
create role myrole;
在系统字典中查找角色
select * from dba_roles where role='MYROLE';
为角色授权
grant create session to myrole;
查找角色的权限
select * from dba_sys_privs where grantee='MYROLE';
给用户赋予角色
grant resource to ychs2;
撤销用户的角色
revoke resource from ychs2;
删除角色
drop role myrole;
给用户赋予赋予权力的权力
grant create session to ychs2 with admin option;
with admin/grant option
with admin option A用户给B用户授权，A用户的权限被收回，不影响B用户。对于系统自带的权限（如connect）只能通过with admin option来授权。
with grant option A用户给B用户授权，A用户的权限被收回，B用户也失去权限。A用户给B用户授权，只能由A收回，其他人不能收回，谁给的权限，谁来收回。
TCL:
事务控制语言（transaction control language），commit、rollback；针对临时表空间里面的数据。
commit:
保存数据
savepoint、rollback to:
insert into t_student(id,name) values(4,'name4');
savepoint a4;
insert into t_student(id,name) values(5,'name5');
rollback to a4;
rollback
回退到commit 保存前的位置
六、约束
查看一个用户有哪些约束
select constraint_name from user_constraints;
select constraint_name from user_constraints where table_name='EMP';
主键
创建表时的约束（系统默认给约束名称）主键默认不能为空
create table t_student(id number(10) primary key);
查看默认建表时的约束
select constraint_name,constraint_type from all_constraint where table_name='T_STUDENT';
创建表后的约束
alter table t_student add constraint pk_t1 primary key(id);
删除约束
alter table t_student drop constraint pk_t1;
外键
添加外键：
alter table t_student add constraint fk_student_clazz foreign key(clazz) references t_clazz(id);
唯一约束
alter table t_class add constraint uniq_clazz_name unique(name);
为空的时候并没有约束
为空约束
alter table t_clazz modify name null;
非空约束
alter table t_clazz modify name not null;
默认值
alter table t_student add join_date default sysdate;
alter table t_student add join_date default sysdate not null;
保证字段永远有值
检查：应该放到SQL语句的最后面
无自定义名称
alter table t_student add sex number(1) check(sex=0 or sex=1);
有自定义名称
alter table t_student add constraint chk_student_sex check(sex in(0,1));
检查范围
alter table t_student add constraint chk_student_age check(age>=18 and age<30);
alter table t_student add constraint chk_student_age check(age between 18 and 30);
七、索引
是数据库提供的一种机制，可以提高数据性能
- 全表扫描
- 索引扫描、必须要使用索引字段
主键索引：建主键时生成
唯一索引：建唯一约束时生成
普通索引：需要手动创建
create index idx_clazz_stunumber on t_clazz(stunumber);
select * from t_student 等价于 select id,clazz,join_date,sex,age from t_student;后者节省性能；
drop index idx_clazz_stunumber;
八、视图
通过视图修改数据时实际上，就是在修改基本表中的数据。反之对应
虚拟的：
由sql语句组成，不占用空间，不保存数据。需要基表（视图的数据来源）
简单\复杂视图
简单视图可以维护数据（插入），
create or replace view v_emp_simple as select empno,ename from emp;
with read only
create or replace view v_emp_check as select empno,ename,deptno from emp where deptno=10 with read only;
无法对只读视图执行 DML 操作
复杂视图
复杂视图是指包含函数、表达式或分组数据的视图，当视图子查询包含函数或表达式时，必须为其定义列别名。
统计所有部门人数，最小，最大平均工资（奖金）
select e.deptno,d.dname,count(*) people_num, min(sal+nvl(comm,0)) min_sal,max(sal+nvl(comm,0)) max_sal,avg(sal+nvl(comm,0)) avg_sal from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname;
创建视图的权利
grant create view to scott;
创建视图
create view v_emp_sal_stat as select e.deptno,d.dname,count(*) people_num, min(sal+nvl(comm,0)) min_sal,max(sal+nvl(comm,0)) max_sal,avg(sal+nvl(comm,0)) avg_sal from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname;
查找视图
select * from v_emp_sal_stat;
物化视图
占用空间保存视图。手动更新数据\自动更新数据
select table_name from user_tables;
on demand手动更新
create materialized view mv_emp as select empno from emp;
更新emp表
delete from emp where empno in(1,2);已删除2行。
物化视图没有变化 select * form mv_emp;
刷新在查看exec dbms_mview.refresh('MV_EMP');
on commit自动更新
create materialized view mv_emp refresh force on commit as select * from emp;
视图作用
1.简化查询
2.灵活控制权限 可以控制到列，可以控制到一定条件下的记录
create or replace view v_emp_basic as select empno,ename from emp where deptno=10;
相关参数
force关键字强制视图
t_teacher表还未建立
create or replace force view v_teacher as select * from t_teacher;
警告: 创建的视图带有编译错误。
检查视图 with check option 主要用来检测是否满足where条件
create or replace view v_emp_check as select empno,ename,deptno from emp where deptno=10 with check option;
往检查视图中插入数据插入成功
insert into v_emp_check(empno,ename,deptno) values(2,'name1',10);
插入失败
insert into v_emp_check(empno,ename,deptno) values(3,'name2',20);
视图 WITH CHECK OPTION where 子句违规
用户下的视图
select view_name from dba_views where owner='SCOTT';
在当前用户下查看有哪些视图
select view_name from user_views;
九、程序
dbms_output.put_line('hello')
if else
-- Created on 2021/12/14 by WJG
declare
-- Local variables here
i integer;
begin
-- Test statements here
if i is null then
dbms_output.put_line('i is null');
else
dbms_output.put_line(i);
end if;
end;
if elsif else
-- Created on 2021/12/14 by WJG
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=99;
if i<60 then
dbms_output.put_line('不及格');
elsif i<=80 then
dbms_output.put_line('及格');
elsif i<=90 then
dbms_output.put_line('良');
else
dbms_output.put_line('优秀');
end if;
end;
搜索case
select name,subject,case when score between 0 and 59 then '不及格'
when score between 60 and 79 then '及格'
when score between 80 and 89 then '良'
else '优秀' end
from t_score;
简单case
select ename,case job when 'CLERK' then '文员'
when 'SALESMAN' then '售货员'
when 'PRESIDENT' then '销售员'
when 'MANAGER' then '经理'
else '分析师' end
from emp;
while loop
declare
-- Local variables here
--i integer default 0;
i integer:=0;
begin
-- Test statements here
while i<5 loop
dbms_output.put_line('i='||i);
i:=i+1;
end loop;
end;
for loop
declare
-- Local variables here
--i integer default 0;
begin
-- Test statements here
for i in 0..5 loop
dbms_output.put_line('i='||i);
end loop;
end;
do loop
declare
-- Local variables here
--i integer default 0;
i integer:=0;
begin
-- Test statements here
loop
dbms_output.put(' '||i);
i:=i+1;
exit when i=5;
end loop;
dbms_output.put_line('');
end;
删除字符串[1,2,3,4]中对应数据库id的值
declare
-- Local variables here
ids varchar(20) :='3,5,9';--要删除记录的id[1,2,3,4]
i integer;
v_id varchar(2);-- 每一个待删除的记录id
p integer; -- 逗号的位置
begin
-- 初始化数据
delete from t_teacher;
for i in 1..10 loop
insert into t_teacher values(i);
end loop;
-- 初始化数据结束
-- 对ids进行处理
if ids is null then
dbms_output.put_line('ids is null');
else
loop
p:=instr(ids,',');
if p=0 then
-- ids此时只有一个数据
delete from t_teacher where id=ids;-- ids=3
exit;
else
-- ids中有多个数据
v_id:=substr(ids,1,p-1);-- 获取待删除的记录id
delete from t_teacher where id=v_id;
ids:=substr(ids,p+1);-- 截取删除id之后的字符串，不包含逗号
end if;
end loop;
end if;
-- 提交数据
commit;
end;
十、游标
显示
通过代码生成
静态\动态
授权 grant debug connect session to scott;
declare
-- 静态游标
cursor c is select * from emp;
emp_row emp%rowtype;
begin
-- 打开游标
open c;
loop
fetch c into emp_row;
exit when c%notfound;-- 退出
dbms_output.put_line(emp_row.empno||''||emp_row.ename);
end loop;
-- 关闭游标
close c;
end;
declare
-- 静态游标
cursor c is select * from emp;
begin
for emp_row in c loop
dbms_output.put_line(emp_row.empno||''||emp_row.ename);
end loop;
end;
declare
-- 带参数的游标
cursor c(v_empno emp.empno%type,v_ename emp.ename%type) is select empno,ename from emp where empno=v_empno;
v_empno emp.empno%type;
v_ename emp.ename%type;
begin
open c(7788,'SCOTT');
loop
fetch c into v_empno,v_ename;
exit when c%notfound;
dbms_output.put_line(v_empno||' '||v_ename);
end loop;
close c;
end;
---------------
declare
cursor cur_emp (v_job emp.job%type) is
select empno,ename,sal
from emp
where job=v_job;
type record_emp is record
(
v_empno emp.empno%type,
v_ename emp.ename%type,
v_sal emp.sal%type
);
emp_row record_emp;
begin
open cur_emp('MANAGER');
loop
fetch cur_emp into emp_row;
exit when cur_emp%notfound;
dbms_output.put_line(emp_row.v_empno||' '||emp_row.v_ename||' '||emp_row.v_sal);
end loop;
close cur_emp;
end;
动态游标
declare
-- 动态游标类型
type dync is ref cursor;
-- 动态游标
c dync;
emp_row emp%rowtype;
begin
open c for select * from emp;
loop
fetch c into emp_row;
exit when c%notfound;
dbms_output.put_line(emp_row.empno||''||emp_row.ename);
end loop;
close c;
end;
隐式
在平常在进行SELECT查询、DML操作Oracle都会自动创建声明“隐式游标”来处理结果数据；
declare
begin
-- 执行完成后，打开了系统的隐式游标
update t_score set score=score+1;
if SQL%found then
dbms_output.put_line('更新了数据'||sql%rowcount||'条');
end if;
commit;
if SQL%notfound then
dbms_output.put_line('没有数据');
end if;
if SQL%isopen=false then
dbms_output.put_line('关闭');
end if;
end;
%found , %notfound，%rowcount，%isopen
declare
teacher_row t_teacher%rowtype;
begin
delete from t_teacher where id=4;
if sql %rowcount=1 then
dbms_output.put_line('删除成功');
else
dbms_output.put_line('删除失败');
end if;
end;
十一、4种可以存储的PL/SQL程序块
1.存贮过程procedures
带有名称的程序段。存储在数据库中的一段程序,处理一些业务，比SQL语句功能强大且灵活。
处理业务逻辑，重点是处理业务，不在于返回值，存储过程不能被sql语句直接执行或调用，只能通过execute执行
备表
create table emp2 as select * from emp;
只拷贝数据不建立表
insert into emp2 select * from emp;
p_emp_sal_upgrade
create or replace procedure p_emp_sal_upgrade(i_empno emp.empno%type,
o_result out boolean) is
-- 变量定义区
v_sal emp.sal%type;
begin
select case when sal between 700 and 1200 then 500
when sal between 1201 and 1400 then 400
when sal between 1401 and 2000 then 300
when sal between 2001 and 3000 then 200
else 100 end into v_sal from emp where empno=i_empno;
update emp set sal=sal+v_sal where empno=i_empno;
-- 或者
/*if v_sal>=700 and v_sal<=1200 then
update emp set sal=sal+500 where empno=i_empno;
elsif v_sal>=1201 and v_sal<=1400 then
update emp set sal=sal+400 where empno=i_empno;
elsif v_sal>=1401 and v_sal<=2000 then
update emp set sal=sal+300 where empno=i_empno;
elsif v_sal>=2001 and v_sal<=3000 then
update emp set sal=sal+200 where empno=i_empno;
else
update emp set sal=sal+100 where empno=i_empno;
end if;
*/
if sql%rowcount = 1 then
o_result:=true;
else
o_result:=false;
end if;
commit;
end p_emp_sal_upgrade;
2.自定义函数
重点在于返回值，可以在select语句中调用
create or replace function f_emp_sal_grade(i_empno emp.empno%type) return salgrade.grade%type is
FunctionResult salgrade.grade%type;
begin
select s.grade into FunctionResult from emp e,salgrade s where e.empno=i_empno
and e.sal between s.losal and s.hisal;
return(FunctionResult);
end f_emp_sal_grade;
编译
使用
select f_emp_sal_grade(7788) from dual;
3.程序包
把相关的存储过程和函数放在一个包下
包头--规范--定义对象 类似于接口
create or replace package pak_emp is
min_sal number;
max_sal number;
avg_sal number;
function f_emp_min_sal(i_deptno emp.deptno%type) return number;
function f_emp_max_sal(i_deptno emp.deptno%type) return number;
function f_emp_avg_sal(i_deptno emp.deptno%type) return number;
procedure p_emp_stat(i_deptno emp.deptno%type,
min_sal out number,
avg_sal out number,
max_sal out number);
end pak_emp;
包体--实现了规范--存储过程和函数 类似于实现类
create or replace package body pak_emp is
function f_emp_min_sal(i_deptno emp.deptno%type) return number is
result number;
begin
select min(sal) into result from emp where deptno = i_deptno;
return(result);
end f_emp_min_sal;
function f_emp_max_sal(i_deptno emp.deptno%type) return number is
result number;
begin
select max(sal) into result from emp where deptno = i_deptno;
return(result);
end f_emp_max_sal;
function f_emp_avg_sal(i_deptno emp.deptno%type) return number is
result number;
begin
select avg(sal) into result from emp where deptno = i_deptno;
return(result);
end f_emp_avg_sal;
procedure p_emp_stat(i_deptno emp.deptno%type,
min_sal out number,
avg_sal out number,
max_sal out number) is
begin
select min(sal), max(sal), avg(sal)
into min_sal, max_sal, avg_sal
from emp
where deptno=i_deptno;
end p_emp_stat;
end pak_emp;
查看
异常
系统自动抛出，程序被动处理
-- 针对某一个员工增加指定工资，结果成功或失败
create or replace procedure p_empno_sal_upgrade(i_empno emp.empno%type,
i_money integer,
o_result out varchar) is
v_sal emp.sal%type;
begin
-- 没有记录系统会保存，查出大于一条系统也会报错
select sal into v_sal from emp where empno=i_empno;
update emp set sal=sal+i_money where empno=i_empno;
if sql%rowcount = 1 then
o_result:='success';
else
o_result:='failure';
end if;
commit;
exception when others then
o_result:='error';
dbms_output.put_line(sqlcode||'-'||sqlerrm);
end;
自定义异常
sqlcode/sqlerrm
-20000/ORA-20000:
sqlcode/sqlerrm
1/User-Defined Exception
create or replace procedure p_empno_sal_upgrade(i_empno emp.empno%type,
i_money integer,
o_result out varchar) is
v_sal emp.sal%type;
money_illeg exception;-- 增加工资不合理异常
pragma exception_init(money_illeg,-20000); --关联错误号和异常变量名
param_null exception;-- 参数为空异常
begin
-- 自定义异常
if i_empno=7788 then
raise_application_error(-20001,'7788用户不允许涨工资');
end if;
if i_money is null or i_empno is null then
raise param_null;
end if;
-- 非预定义异常
-- 如果增加的工资不合理，则抛异常
if i_money<100 or i_money>1000 then
raise money_illeg;
end if;
-- 没有记录系统会保存，查出大于一条系统也会报错
select sal into v_sal from emp where empno=i_empno;
update emp set sal=sal+i_money where empno=i_empno;
if sql%rowcount = 1 then
o_result:='success';
else
o_result:='failure';
end if;
commit;
exception
-- 自定义异常
when param_null then
o_result:='error';
dbms_output.put_line('参数为空');
dbms_output.put_line(sqlcode||'/'||sqlerrm);
-- 非预定义异常
when money_illeg then
o_result:='error';
dbms_output.put_line('参数不合理，100-1000之间');
dbms_output.put_line(sqlcode||'/'||sqlerrm);
-- 预定义异常
when no_data_found then
o_result:='error';
dbms_output.put_line('没有数据');
when others then
o_result:='error';
dbms_output.put_line(sqlcode||'/'||sqlerrm);
end;
4.触发器
语句级触发器
创建一个语句级的触发器，将用户对dept表的操作信息保存到dept_log表中
先创建一个表
create table dept_log(operate_tag varchar2(10),operate_time date);
创建一个触发器，名为tri_dept
create or replace trigger tri_dept --创建一个触发器，名为tri_dept
before insert or update or delete -- 在查入，更新，或删除前执行
on dept
declare
-- local variables here
var_tag dept_log.operate_tag%type;-- 创建一个变量用来记录当前操作的类型
begin
if inserting then
var_tag:='插入';
elsif updating then
var_tag:='更新';
elsif deleting then
var_tag:='删除';
end if;
insert into dept_log values(var_tag,sysdate); -- 执行插入操作
end tri_dept;
行级触发器
将t_student表中更新或插入一条数据时，自动设置反向的电话号码
当update和insert一条数据时，触发器启动
列标识符 :new.id 或:old.id 表示新插入的id值或更改前的id值
create or replace trigger trg_phone_reverse
before insert or update
on t_student
for each row
declare
begin
select reverse(:new.phone) into :new.phone_reverse from dual;
end trg_phone_reverse;
alter trigger 名称 disable/enable
替代触发器
它的触发时机关键字是instead of
与其他类型的触发器不同的是，替代触发器是定义在视图上的。由于视图有些是由多个基表连接组成的逻辑结构，所以一般不允许用户进行DML操作(insert update delete) 用户通过替代触发器编写代码就可以实现对构成视图基表的操作。
创建一个多表连接视图
create view view_emp_dept
as select empno,ename,dept.deptno,dname,job,hiredate
from emp,dept
where emp.deptno = dept.deptno;
创建一个触发器
在下面的触发器中，如果新插入的部门编号不在dept表中，则首先向dept中插入关于新部门编号的数据行，然后再向emp表中插入记录行
create or replace trigger tri_insert_view
instead of insert
on view_emp_dept
for each row
declare
-- local variables here
row_dept dept%rowtype;
begin
select * into row_dept from dept where deptno =:new.deptno;
/*if sql%notfound then
insert into dept(deptno,dname)
values(:new.deptno,:new.dname);
end if;*/
exception
when no_data_found then
insert into dept(deptno,dname) values(:new.deptno,:new.dname);
insert into emp(empno,ename,deptno,job,hiredate)
values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end tri_insert_view;
创建一个视图
create or replace view v_student_score as
select st.id,st.name,st.phone,st.phone_reverse,sc.subject,sc.score
from t_student st,t_score sc
where st.id=sc.id;
-- 给视图的触发器要带instead of
create or replace trigger trg_view_student
instead of insert
on v_student_score
for each row
declare
begin
insert into t_student(id,name,phone,phone_reverse)
values(:new.id,:new.name,:new.phone,:new.phone_reverse);
insert into t_student(id,name,subject,score)
values(:new.id,:new.name,:new.subject,:new.score);
end;
用户事件触发器
create or replace trigger trg_logon
after logon
on database
begin
insert into t_log(username,logon_time) values(user,sysdate);
end;
create or replace trigger trg_logoff
before logoff
on database
begin
update t_log set logoff_time=sysdate where username=user and logoff_time is null;
end;
十二、分区表
提高性能，不用全表扫描，扫描分区表，用到了很多表空间，用到了多个数据文件
按照值的范围分区
按照number分区
create table customer(id number not null primary key,
name varchar2(10),
status char(1))
PARTITION by range(id)(
partition customer_p1 values less than (10) tablespace tab3_data,
partition customer_p2 values less than (20) tablespace tab4_data
);
插入数据
insert into customer(id,name,status) values(1,'name1',1);
insert into customer(id,name,status) values(2,'name2',1);
insert into customer(id,name,status) values(11,'name11',1);
查看效果
select * from customer;
select * from customer partition(customer_p1) where id=1;
insert into customer(id,name,status) values(21,'name21',1);
按照日期分区
order_date
create table order_activities(id number not null primary key,
order_date date,
money number)
partition by range(order_date)(
partition order_activities_p1 values less than (to_date('2021-12-01','yyyy-mm-dd')) tablespace tab3_data,
partition order_activities_p2 values less than (to_date('2021-12-22','yyyy-mm-dd')) tablespace tab4_data,
partition order_activities_p3 values less than (maxvalue) tablespace tab5_data
);
往不同分区中插入数据
insert into order_activities(id,order_date,money) values(1,to_date('2021-11-01','yyyy-mm-dd'),1000);
insert into order_activities(id,order_date,money) values(2,add_months(sysdate,-1),1000);
insert into order_activities(id,order_date,money) values(3,sysdate-2,1000);
insert into order_activities(id,order_date,money) values(4,sysdate,1000);
查看结果
select * from order_activities;
select * from order_activities partition(order_activities_p1);
select * from order_activities partition(order_activities_p2);
select * from order_activities partition(order_activities_p3);
按照值的内容分区，内容固定类似于枚举
按照列表分区
address
create table product(id number not null primary key,
name varchar2(20),
address varchar2(20))
partition by list(address)(
partition product_t1 values('heilongjiang','jilin','liaoning') tablespace tab5_data,
partition product_t2 values('hebei','shandong','shanxi') tablespace tab6_data
);
查入数据
insert into product values(1,'name1','heilongjiang');
insert into product values(2,'name2','liaoning');
insert into product values(3,'name3','hebei');
查看分区
select * from product partition(product_t2);
select * from product partition(product_t1);
没有规律采用的是哈希算法
create table sales(id number not null primary key,
name varchar2(10))
partition by hash(name)(
partition sales_p1 tablespace tab3_data,
partition sales_p2 tablespace tab3_data
);
insert into sales values(1,'name1');
insert into sales values(2,'name2');
insert into sales values(3,'name3');
insert into sales values(4,'name4');
insert into sales values(5,'name5');
insert into sales values(6,'name6');
select * from sales partition(sales_p1);
select * from sales partition(sales_p2);
把上面的分区策略组合使用组合分区
操作
- 添加分区
- 删除分区
不懂阅读博客https://blog.51cto.com/tianzt/171759分区
十三、事务
ACID：原子性，一致性，隔离性，持久性
递归
home表
create table home(id number not null primary key,
name varchar2(20),pid number);
insert into home values(1,'name1',null);
insert into home values(2,'name2',1);
insert into home values(3,'name2',1);
insert into home values(4,'name4',2);
insert into home values(5,'name5',2);
insert into home values(6,'name6',3);
insert into home values(7,'name7',6);
insert into home values(8,'name8',7);
-- 找子树
select * from home start with id=6
connect by prior id = pid;
-- 找祖先
select * from home start with id=8
connect by prior pid=id;
上面的sql语句可以理解为 从home表中
浙公网安备 33010602011771号