Oracle期末复习总结——速成

Oracle体系结构

数据库—>数据库实例—>表空间(逻辑单位) —>数据文件(物理单位)

新建一个项目：

MySQL：创建一个数据库，创建相应的表

• Oracle：创建一个表空间，创建用户，用户创建表

Oracle和MySQL的差别：

• 遵循SQL标准
• 相同的功能，有不同的方言
• Oracle安全级别高，MySQL开源免费

虚表、伪表

• dual：Oracle中的虚表，伪表，主要用于补齐语法结构

注意

• 别名中不能有特殊字符

• null值不能做四则运算

• 字符串拼接：

  • java： + 号拼接
  • Oracle： || 拼接

• 在Oracle中双引号主要用来起别名，单引号是用的值

• where 和 having的区别：
  where后面不能接聚合函数，可以接单行函数

  having是在group by之后执行的，可以接聚合函数

函数

• nvl(arg1,arg2)：如果参数1为null，就返回参数2
• nvl2(arg1,arg2,arg3)：如果参数1为null，就返回参数3，否则返回参数2
• nullif(arg1,arg2)：如果参数1=参数2，返回null,否则返回参数1
• concat(str1,str2)：在mysql和Oracle中都有，拼接字符串
• sum()：忽略空值
• avg()：有空值时会发生错误
• ceil()：向上取整
• floor：向下取整
• round()：四舍五入
• trunc()：截断
• mod()：求余

字符函数

• substr(str1,起始索引，长度)：字符串截取，无论起始是0、1都从开头截取
• length(str1)：获取字符串长度
• trim()：去除字符左右两边的空格
• replace()：替换字符串

日期函数

• sysdate：查询当前日期
• add_months(sysdate,3)：查询三个月后的日期
• sysdate+3：查询3天后的日期
• months_between()：查询相隔月数

转换函数

• to_number()：字符转数值，鸡肋
• to_char(sal,’$9,999.99’)：数值转字符，第二个参数是格式
• to_char(sysdate,‘yyyy-mm-dd hh:mm:ss’)：日期转字符
• to_date(‘2020-04-10’,‘yyyy-mm-dd’)：字符转日期

条件查询

• 关系运算符：> >= < <= != = <>
• 逻辑运算符：and or not
• 其他运算符：
  • like 模糊查询
  • in() 在某个集合内
  • between…and…在某个区间内
  • is null 判断为空
  • is not null 判断非空

排序

• 排序：order by
• 升序：asc ascend
• 降序：desc descend

注意 nulls 默认值为first

Eg： select * from emp order by comm desc nulls last;

Oracle中的外连接

（+）实际上是如果没有对应的记录就加上空值

select * from emp e1,dept d1 where e1.deptno=d1.deptno(+);

查询包含员工的部门信息 exists

select * from dept where exists(select * from emp where emp.deptno=dept.deptno);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MtYjJkFo-1609849184601)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20201229204240640.png)]

伪列：rownum,rowid

• rownum：系统自动生成的一列，用来表示行号

• rowid：表示每行数据指向磁盘上的物理地址

SQL执行顺序

from - where - group by - having - select - rownum - order by

Oracle中分页查询只能用子查询，通过rownum

集合运算

• 并集：将两个查询结果合并

union：去除重复的，并且排序

union all：不去重

• 差集：minus
• 交集：intersect
• 注意事项：
  • 列的类型要一致
  • 按照顺序写
  • 列的数量要一致，如果不足，用空值填充

表空间

创建表空间

create tablespace 表空间名称
datafile '文件的路径（服务器上）'
size 大小
autoextend on 自动扩展
next 每次扩展的大小;

• 注意：创建表空间时要切换到system账号下创建

删除表空间

drop tablespace 表空间名称;

创建用户

create user 用户名
identified by 密码
default tablespace 表空间名称

• Oracle中的角色：connect、resource、dba

• dba拥有最高权限

授权

grant 角色|权限 to 用户
revoke 角色|权限 from 用户

列的类型

• varchar：在Oracle中目前支持，以后不一定

• varchar2(长度)：可变字符长度

• char(长度)：固定长度字符

• number(总长度,小数长度)：数字类型

• date：年月日时分秒

• timestamp：时间戳，比date类型更精确

子查询创建表

create table 表名 as 查询语句;

• 注意： 只会复制表结构和表中数据，不会复制列的约束

修改表

• 添加列

alter table 表名 add 列名 类型;

• 修改列

alter table 表名 modify 列名 类型;

• 删除列

alter table 表名 drop column 列名;

• 修改列名

alter table 表名 rename column 旧名 to 新名;

• 修改名表

rename 旧名 to 新名;

列的约束

• 主键约束
• 非空约束
• 唯一约束
• 检查约束
• 外键约束

alter table 从表名 add foreign key(列名) references 主表名(列名);

强制删除（先删除约束，再删除表）

drop table 表名 cascade constraint;

级联删除（删除主表，从表里的相关内容也会删除）

alter table 从表名 add foreign key(列名) references 主表名(列名) on delete cascade;

• 使用子查询插入数据
• delete：DML，逐条删除，支持事务操作
• truncate：DDL，先删除表再创建表，不支持事务操作，执行效率高

保存点 ：savepoint 保存点名称

视图

视图：

• 对查询结果的一个封装，数据都来自于原表，视图本身不存储任何数据
• 能够封装复杂的查询结果
• 屏蔽表中的细节

语法：

可以修改数据：

create [or replace] view 视图名 as 查询语句;

创建只读视图：

create [or replace] view 视图名 as 查询语句 with read only;

同义词

create synonym dept for view_test3;

序列

类似于mysql中的auto_increment自增长

语法

create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle是否循环
cache 缓存的数量 | nocache

select seq_test.nextval from dual;查询序列

索引

语法

create index 索引名称 on 表名(列);

注意:主键约束自带主键索引，唯一约束自带唯一索引

PLSQL编程

declare
	变量名 变量类型;
	变量名 变量类型:=初始值
begin
end;

--输出语句
dbms_output.put_line()

vsal emp.sal%type  //引用型变量
erow emp%rowtype   //记录型变量

if语句

declare
       age number:=80;
begin
     if age<18 then
       dbms_output.put_line('小屁孩');
     elsif age>=18 and age<50 then
       dbms_output.put_line('年轻人');
     else
       dbms_output.put_line('老年人');
     end if;
end;

循环语句

• while循环

declare 
     i int:=1;
begin
  while i<=100 loop
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

• for循环

declare 
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;
//1 2 3 4 5...10


declare 
begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;
//10 9 8 7 ...1

• loop循环

declare 
  i int :=1;
begin
  loop 
    exit when i>10 ;
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

游标

普通游标、系统引用游标

游标是用来操作查询结果集的，相当于JDBC中的ResultSet

语法

cursor 游标名 is 查询结果集;

开发步骤

• 1、声明游标

• 2、打开游标 open 游标名

• 3、从游标中取数据 fetch 游标名 into 变量

  ​ 游标名%found ：找到数据

  ​ 游标名%notfound ：没有找到数据

• 4、关闭游标 close 游标名

//使用普通游标查询员工姓名和工资（不带参数）
declare 
  cursor vrows is select * from emp;
  vrow emp%rowtype;
begin
  open vrows;
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'，工资：'||vrow.sal);
  end loop;
  close vrows;
end;

//使用普通游标查询员工姓名和工资（带参数）
declare 
  cursor vrows(dno number) is select * from emp where deptno=dno;
  vrow emp%rowtype;
begin
  open vrows(&dno);
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'，工资：'||vrow.sal);
  end loop;
  close vrows;
end;

系统引用游标

1、声明游标：游标名 sys_refcursor

2、打开游标：open 游标名 for 结果集

3、从游标中取数据

4、关闭游标

//使用系统游标查询员工姓名和工资
declare 
  vrows sys_refcursor;
  vrow emp%rowtype;
begin
  open vrows for select * from emp;
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'，工资：'||vrow.sal);
  end loop;
  close vrows;
end;

//使用for循环遍历游标
//不需要打开、关闭游标
declare 
  cursor vrows is select * from emp;
begin
  for vrow in vrows loop
    dbms_output.put_line('姓名：'||vrow.ename||'，工资：'||vrow.sal);
  end loop; 
end;

例外（意外）

• 程序运行的过程中发生异常

declare 
	--声明变量
begin
	--业务逻辑
exception
	--处理异常
	when 异常1 then
		...
	when 异常2 then
		...
	when others then
		...	处理其他异常
end;

• 异常类型

zero_divide：除零异常
value_error：类型转换异常
too_many_rows：查询出多行记录，但赋值给rowtype类型的值
no_data_found：没有找到数据

• 自定义异常

异常名 exception;
raise 异常名
-- 抛出自定义异常
declare
   cursor vrows is select * from emp where ename='123456';
   vrow emp%rowtype;
   verror exception;
begin
  open vrows;
  fetch vrows into vrow;
  if vrows%notfound then 
    raise verror;
  end if;
exception
  when verror then
    dbms_output.put_line('自定义异常');
  when others then
    dbms_output.put_line('其他异常');
end;

存储过程

• 存储过程实际上是封装在服务器上一段PLSQL代码片段，已经编译好的代码，客户端调用存储过程，执行效率非常高效

语法

create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型...) is|as 
--声明部分
begin
--业务逻辑
end;

创建存储过程

create or replace procedure pro_addsal(in_ename in emp.ename%type,in_sal in emp.sal%type)
as 
cur_sal emp.sal%type;
begin
  select sal into cur_sal from emp where ename=in_ename;
  dbms_output.put_line('当前工资：'||cur_sal);
  update emp set sal=sal+in_sal where ename=in_ename;
  dbms_output.put_line('涨薪后的：'||cur_sal+in_sal);
  commit;
end;

调用存储过程

方式一

call 存储过程名(参数);

方式二（用的最多）

declare 

begin
	存储过程名(参数);
end;

存储函数

语法

create [or replace] function 存储函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型...) return 数据类型 
is|as 
--声明部分
begin
--业务逻辑
end;

存储过程和存储函数的区别

• 本质上没有区别
• 函数存在的意义是给过程调用，存储过程里面调用存储函数
• 参数默认是in
• 函数可以在SQL语句里面直接调用

-- 用函数查询指定员工的年薪
create or replace function getsal(in_ename in emp.ename%type) return emp.sal%type
is
vsal emp.sal%type;
begin
  select sal*12+nvl(comm,0) into vsal from emp where ename=in_ename;
  return vsal;           
end;

-- 调用函数
declare
  vsal emp.sal%type;
begin
  vsal:=GETSAL('SMITH');
  dbms_output.put_line(vsal);
end;
-- 查询员工的姓名和年薪
select ename,getsal(ename) from emp;

触发器

触发器：当用户执行了insert | update | delete这些操作之后，可以触发一系列其他的动作/业务

作用：

• 在动作之前/之后，触发业务逻辑
• 插入数据之前做一些校验

语法

create [or replace] trigger 触发器名称
before | after
insert | update | delete
on 表名
[for each row]
declare
begin
end;

-- 创建触发器
create trigger tig_test1
after insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入黑马程序员！');
end;
-- 插入数据
insert into emp (empno,ename)values(9999,'zzl');

触发器的分类

• 语句级触发器：不管影响多少行，都只会执行一次

• 行级触发器：影响多少行，就触发多少次

  ​ ：old 代表旧的记录

  ​ ：new 代表新的记录

--旧的工资不能大于新的工资
create trigger tri_test1
before
update
on emp
for each row
declare

begin
  if :old.sal>:new.sal then
    raise_application_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

update emp set sal=sal-10;