oracle数据库笔记
注释
1、单行注释：用--
2、多行注释：用/**/
用户的身份
1、管理员用户
管理员用户可以访问数据库中的任何数据，不需要权限，也可以创建用户、修改用户密码、赋予用户权限等行为。
2、普通用户
普通用户被管理员创建，所有访问权限来自于管理员的赋予。创建的用户都是普通用户 初始状态不具备任何权限。
如果想要用户可以正常进行登录，需要我们提供对应的权限。
**- connect 权限 连接权限 (允许用户进行登录)
- resource 权限 资源权限(允许用户创建属于自己的表)
- dba 权限 管理员权限 (允许用户以管理员身份进行登录) 不要赋予该权限
- 权限撤回 revoke
- 用户状态 lock unlock 用户一般情况下，在创建的时候，默认都是解锁的，可以在创建用户的时候，定义用户的状态为锁定状态，锁定状态的用户，就算具有权限，也无法登录数据库
- 可以使用alter关键字对指定用户进行解锁
**
用户、表的创建
用户创建、添加权限、删除修改权限的例子
-- 查询scott用户下的emp表的所有记录
select * from scott.emp;
-- 创建普通用户
-- 创建一个叫做mqq的用户 定义密码为a12345
-- 创建的用户都是普通用户 初始状态不具备任何权限
create user mqq identified by a12345;
--连接权限
grant connect to mqq;
--资源权限
grant resource to mqq;
-- 也可以合并写为一条
grant connect,resource to mqq;
-- 撤回权限
revoke connect from mqq;
revoke resource from mqq;
-- 也可以合并写为
revoke connect,resource from mqq;
-- 锁定用户
create user mqq identified by a12345 account lock;
-- 使用alter关键字对指定用户进行解锁
alter user mqq account unlock;
创建表
create table 表名称(
表字段 字段类型，
表字段 字段类型
);
基本字段类型有：
number（n） – 最多可以容纳8位长度的数据
number（n，m） – 限制字段最多能够容纳n位数字 其中小数部分m位 整数部分n-m位
char(n)： 定长 最大不能超过2000字节
- 存储的是字符串类型 代表的是该字段所能容纳的数据的
- 最大字节大小 字符集 英文字符 一个字符占据一个字节
- 中文字符 根据所使用的编码不同 一个中文字符可能占据2 3 甚至是4个字节
- 当前oracle 所使用的字符集 一个汉字占据三个字节
**varchar2(n) ** 不定长 最大不能超过4000字节
char和varchar2之间的区别：
- char 定长类型，如果规定20个字节，但只使用了三个字节，则oracle会使用空格自动补足剩下的17个字节
- varchar2 类型后面的数字只是定义了字段能够存储的最大值。但是，实际在存储时候所占据的空间大小以存储的数据为准
- varchar2 节约了空间 但是牺牲了效率 时间换空间
- char类型 就牺牲了空间 但是提高了效率 空间换时间
- 阿里的要求 字段值几乎相等的情况下 必须使用char类型
date 时间类型数据，值如果为sysdate（oracle 的一个自带函数），代表当前的时间。
to_ date 将字符串转换成指定格式的日期 to_ date(‘2018-09-01 06:06:06’,‘YYYY-MM-DD HH24:MI:SS’)
timestamp 精确日期类型 可以精确到秒后6位 甚至包含时区
clob 存储大文本 或者是xml配置文件
blob 存储二进制对象 图片 音频 视频等等
现在的开发中 blob 和 clob使用比较少 因为数据库存储大数据量的数据 性能消耗过大
一般来讲需要存储的资源 会以公网url的方式 存储目标资源的公网路径 进行存储
插播一个硬盘和内存
- 所有的数据都是在硬盘中进行读和写入的
- 但是在对数据进行操作 或者是运行程序的时候 需要将数据从硬盘提取到内存中进行读取
- 你想要将数据提取到内存中 就必须向内存申请空间
约束
- primary key -主键约束，值不可以为空，不可以出现重复值，一张表中只能有一个字段建立主键约束
- alter table 表名 add constraint 约束名称 check（检查约束语句）-检查约束
- alter table 表名 modify(字段名 not null);-非空约束 限制该字段字段值不可以为空
- alter table 表名 modify(字段名 default ‘默认值’);-默认约束 插入数据时，如果没有给添加了默认约束的字段提供值，则约束会给其提供默认值
- alter table 表名 add constraint 约束名称 unique(字段名); -唯一约束 限制字段中插入的值不可以重复(主键约束可以理解为是一种特殊的唯一约束) 该字段的值不可重复
- alter table 需要建立约束的表名 add constraint 约束名称 foreign key(需要建立约束的表的字段) references 被关联的表(被关联的表的字段);（alter table 子表表名 add constraint 外键约束的名称 foreign key(字段名) references 父表名称(字段名)） 被关联字段必须是唯一的（1. 子表在插入数据的时候 必须在父表中拥有关联记录 2. 父表的数据 如果在子表中有关联数据存在 父表数据无法删除 3.被建立外键约束的父表 是不可以被直接删除的）
建立约束例子
-- 检查约束 限制性别的字段值只能是男或者女
alter table student add constraint ch_stu_gen check(gender = '男' or gender='女');
-- 检查约束 限制学生表中age的范围在18 ~25
alter table student add constraint ch_stu_age check(age between 18 and 25);
-- 非空约束 限制姓名字段值不可以为空
alter table student modify(sname not null);
-- 默认约束
alter table student modify(addr default '南京');
-- 唯一约束 地址不可重复
alter table student add constraint un_stu_addr unique(addr);
插入/修改/删除数据
插入：
- 插入全部字段值
insert into 表名 values(字段1，字段2，…);括号中需要有所有字段的值 - 插入部分指定字段值
insert into 表名(指定字段1，指定字段2) values(指定字段值1，指定字段值2);
修改：
- 修改一个字段
update 表名 set 要修改字段名 = ‘要修改字段的值’ where 条件字段=‘条件字段值’; - 修改几个字段
update 表名 set 要修改字段名1 = ‘要修改字段名1’ ,要修改字段名2 = 要修改字段值2 where 条件字段= ‘条件字段值’;
删除：
- 删除数据
delete from student where 删除条件字段=‘删除条件字段值’;
数据库语句的分类
- 数据定义语言 DDL create alter drop turncate(清空表数据)
- 数据操纵语言 DML insert update delete
- 数据查询语言 DQL select order by where group by having join 等和查询相关的数据库关键字
- 事务控制语言 TCL commit rollback savepoint
- 数据控制语言 DCL grant revoke
- 冷知识 oracle单表最多支持一亿条数据 mysql 单表最多只支持500W条
数据添加的本质是硬盘读写，大量的硬盘读写会导致硬盘性能下降，数据库为了避免这样的情况，所以有了日志文件，并且引入了session会话。
oracle是支持多个用户同时登录的数据库，每当发生了用户登录的行为，则我们的数据库都会为该用户分配一个session会话，每一个用户都有一个自己的日志文件，创建表create用户属于DDL语句，而用户所执行的所有DDL语句都会被立刻提交到数据库。但是所有的DML语句并不会被立刻提交到数据库，而是会存放在于会话锁绑定的一个日志文件中，如果你不提交sql语句到数据库，则管理员和其他用户都无法看到你没提交到数据库的执行的sql语句得到的数据。
truncate删除的数据是不可以恢复的 DDL操作 DDL操作是直接提交数据库的。
将结果提交到数据库中，必须执行指令commit;，不然只能在当前用户的会话中查看到。
savepoint 叫做存档点 对当前日志文件中的数据操作进行存档 savepoint A;
rollback 回滚 读档 可以将日志文件中的数据状态 回滚到指定存档点的时候 rollback to A;
- 1.日志文件中的数据 只有在你使用commit或者切换用户登录的情况下 才会提交
- 2.commit 会提交并清空当前用户的日志文件中的所有数据 包括存档点
- 3.在数据没有被提交的情况下 使用rollback to 保存点的名称 可以使日志文件的数据恢复到定义保存点时候的状态 如果直接使用rollback 没有指定保存点 数据会默认回滚到上一次commit的时候
向公司的数据库插入100w条数据 这个是业务
我需要准备100w条insert语句 负责数据库插入，这100w条insert语句 就可以看住是一个整体，这个整体叫做事务。
- 事务的四大特性 原子性 一致性 持久性和隔离性
- 原子性 所有的事务中的dml语句 要么全部提交 要么全部不提交
- 持久性 确保所有数据都能够长期的存储在数据库中执性的sql语句需要及时被commit
- 隔离性 在A事务操作数据的时候 B事务 不可以操作数据 如果B想要操作 只能在A操作之前操作 或者在A操作完成之后进行操作 目的是避免多个事务同时操作一个数据 所产生的错误数据(脏数据)
字段修改（增/删/重名命/修改字段类型/删除用户/删除表）：
- 增加字段 alter table 表名 add 字段 字段类型;
- 删除字段 alter table 表名 drop column 字段名;
- 重命名字段 alter table 表名 rename column 原字段名 to 修改后的字段名;
- 修改字段的数据类型 原本是字符串类型的数据 如果表里有数据 不可以转为number类型 alter table 表名 modify 字段名 字段类型;
- 用户修改密码 alter user 要修改的用户名 identified by 密码;
- 用户删除 drop user 用户名;-- 表的删除 drop table 表名;
查询语句：
单表简单查询
-
- 代表oracle的通配符表示选择表中的所有字段，from 之后代表你数据的来源，所有的查询语句 最少要有select 和 from关键字
-
可使用别名 修改字段在显示时候的效果 在字段名之后空格跟别名
-
字符串连接符号 || 可以将字段的值和指定的字符串进行拼接。例子： select ename||‘的薪水为’||sal 薪资情况 from emp;
-
concat 也是做字符串拼接的 但是每一次只能拼接两个
select concat(concat(concat(‘员工的姓名叫’,ename),‘他的收入为’),sal) 薪资情况 from emp;
-
字段的值可以经过数学运算之后再显示
-
where关键字 条件筛选
-
运算符号：and or > < >= <= = 不等于 != <> between and in not
-
空值处理 如果对应的某一个字段没有值 可以用null代表空值，值为0 不等于空值。
-
nvl 对字段的空值进行处理 可以指定值为null的字段值 如何显示
查询所有人的奖金,奖金为空的显示为0 不为空的正常显示 select ename,nvl(comm,0) from emp; -
nvl2 不管你如何查询 改变的只是查询结果的显示 不会改变表中原有的数据
-
floor 对向下取证
comm表示你还需要处理的字段 comm+1000 代表如果字段的值不为空 则+1000显示 100 代表如果字段的值为空 则显示为100 查询所有人的奖金 所有奖金为空的人 设置奖金显示为100 所有奖金不为空的人 奖金在原有基础上+1000 select ename,nvl2(comm,comm+1000,100) 修改显示之后的奖金 from emp; 查询所有部门编号为20并且sal>1500的所有员工的姓名,部门编号和年收入 年收入=（sal+comm）*12 select ename,deptno,(sal+nvl(comm,0))*12 from emp where deptno=20 and sal >1500; -
聚合函数 对多行数据进行统计的函数 如果没有指定数据统计的范围 默认统计所有数据
-
min max avg count sum
-
最小值 最大值 平均 统计出现次数 求和
查询emp表中所有员工的最低薪水 最高薪水 平均薪水 薪水总合 select min(sal) 最低薪水,max(sal) 最高薪水,floor(avg(sal)) 平均薪水,sum(sal) 薪水综合from emp;
-
-
count 统计的值指定字段下 非空值的数量
-- 统计所有奖金不为空的人数 select count(comm) from emp; -
count(*) 代表的是统计记录行数
-- 统计emp表的员工人数 select count(*) from emp; -
group by 分组
-
分组会将数据按照你所指定的分组字段进行分类 所有指定字段值相同的数据会被分配到一组 你可以使用我们的聚合函数对每一组的数据进行分可别统计
-- 查询各部门的平均工资？ select deptno,avg(sal) from emp group by deptno; -
可以使用聚合函数分别统计不同组的相关信息
-
数据被分组之后 select后面只可以出现 被分组的字段本身 和五种聚合函数
-
如果出现了其他字段查询 会无法执行
-
数据被分组之后 从行数据 变成了组数据 原本行数据字段不可以被查询
-
可以同时基于两个字段进行分组 分组的依据 是这两个字段值的组合
select count(*),deptno,job from emp group by (deptno,job);
-
-
条件筛选 having 用来筛选分组之后的数据（组数据）
-
条件筛选 where 用来筛选分组之前的数据 (行数据)
-- 查询各个部门中去除JAMES这个员工之外的所有部门的平均工资情况,并且只筛选平均工资大于2000的部门数据 select deptno,avg(sal) from emp where ename!='JAMES' group by deptno having avg(sal)>2000; -
排序 按照指定的字段的值 对数据进行排序
-
asc 代表升序 asc通常可以省略不写
-
desc 代表降序
-- 查询所有员工的信息 并且按照工资进行升序排序 select * from emp order by sal asc; select * from emp order by sal desc;
-
-
多条件排序
-
空值在进行比较的时候 会被当做无穷大 要排序的字段如果包含空值 请使用nvl函数
-
将其转换为0
-- 查询所有员工的信息 按照工资进行升序排序 工资相等的按照comm降序排序 select * from emp order by sal asc,nvl(comm,0) desc; select * from emp order by nvl(comm,0) desc;
-
-
去重 distinct
-- 查询emp表中的所有部门信息 select distinct deptno from emp order by deptno; -
sql关键字的执行流程
书写顺序— 执行顺序
select-----6.从前面的筛选之后的数据中 选择指定的字段进行显示
distinct—5.按照指定字段 对数据进行去重
from-------1.from 决定了数据的来源
where------2.where 对分组之前的数据进行筛选
group by—3.group by 对筛选之后的数据 进行分组
having-----4. 对分组之后的数据进行筛选
order by—7.对前六部得到的数据筛选完成之后按照指定字段进行排序
--查询各个部门的平均工资,并且按照平均工资降序排序
-- 使用别名的时候 别名的定义必须在别名的使用之前
-- 字段 表名 order by排序字段 可以使用别名
select deptno,avg(sal) a from emp group by deptno order by a desc;
子查询
-
不相关子查询 把一条查询语句的查询结果 作为另一条语句的查询条件或者数据来源
-
子查询语句运行完成之后 外部语句才会执行
-- 查询所有薪资比SMITH高的员工信息 select * from emp where sal >(select sal from emp where ename='SMITH')
rowid rownum 伪列
-
rowid(为索引提供服务的) 数据库中的任意一行数据 都有一个独一无二的rowid
-
rowid相当于是每一条记录的身份证
-
e相当于是emp表的别名 代表emp
-
e.* 访问emp表的所有非伪列字段
-
如何在查询数据的时候 进行数据的去重？
create table jerk( id number, jname varchar2(20) );-
使用distinct去重
select distinct id,jname from jerk; -
使用分组去重
select id,jname from jerk group by id,jname; -
使用rowid进行去重
select rowid,j.* from jerk j; -
利用分组 从每一个jerk中 获取一个rowid 利用子查询进行筛选
– max min 将重复数据分到一组
– 字符比较大小是根据ASCII码值比较的
select * from jerk where rowid in
(select max(rowid) from jerk group by id)
-
-
rowid优点
-
不仅可以用去查询去重 也可以用来删除重复的数据
-
删除jerk表中的重复记录，从每个组中提取一个rowid ，删除的时候 不删除该数据
delete from jerk where rowid not in (select max(rowid) from jerk group by id)
-
模糊查询
-
like 后面的是匹配的表达式
-
% 代表匹配任意的 0个 或1个 或任意个字符
-
_ 代表匹配任意单个字符
-- 查询所有姓名以j开头的员工信息 select * from emp where ename like 'J%' -- 查询姓名第二个字符为A的员工信息 select * from emp where ename like '_A%'; -- 查询姓名第二个字符为A 倒数第二个字符为E的员工信息 select * from emp where ename like '_A%E_';
交并集
-
minus 去交集 A集合去除自己和B集合重复的部分
-
union 取并集
-
如果把集合比喻为查询语句 union时候 顺序不影响结果，minus 集合在前在后的顺序对minus的结果是有影响的
虚表 -
这张表是没有任何属性的 用来给程序员临时显示数据使用的
select 8888*8888 from dual;
日期相关函数
select sysdate,add_months(sysdate,1) from dual;
-- 添加月份的第一个参数 可以是系统函数 sysdate 可以是你表中的日期类型字段
-- 也可以是你使用to_date转换而生成的日期
-- 查询2044年的100个月之后的日期
select add_months(to_date('2044-04-04','YYYY-MM-DD'),100) from dual;
select last_day(sysdate) from dual;
-
常用的单行函数
-- emp 中 有一个员工的入职时间 -- 要你根据入职时间获取员工的工龄 -- 三种常用方法 选择你喜欢的使用就可以了 -- 日期类型的数据 是可以直接相减的 得到的是天数 需要除以365 select ename,floor((sysdate-hiredate)/365) 入职时间 from emp -- 使用to_char 函数 将日期类型中的年份提取出来 进行计算 select ename,to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') 入职时间 from emp -- 使用extract 对日期的指定数据进行提取 select ename,extract(year from sysdate)-extract(year from hiredate) 入职时间 from emp; -- rownum 分页查询 -- rownum 伪列 相当于是每一行数据所对应的行号 -- rownum只和你from 之后的数据源有关 select rownum ,e.* from emp e; -- 查询emp表的前十条记录 select * from emp where rownum<11; -- 工资前十高的员工信息 select * from emp where rownum<11 order by sal desc; -- 利用子查询 先对数据进行排序 再进行rownum的筛选 select * from (select * from emp order by sal desc) where rownum<11; -- rownum 不可以使用大于作为筛选条件 -- rownum在进行比对的时候 只要一次不满足 后续比对就会停止 select * from emp where rownum>0; -- 我即想使用rownum的特性 进行数据筛选 又想避开rownum弊端 -- 在子查询中 rownum是伪列 它具有特殊的效果 -- 如果我们把子查询和语句的结果 单做是一张表 rownum的别名r 就变成了 -- 一个普通字段 他具备这rownum的属性 但是不具备rownum的特性 -- 通过这种方式规避rownum的弊端 实现数据截取 -- 查询emp表中第六条到第十条记录 select * from (select rownum r,e.* from emp e) where r between 6 and 10 -- 查询emp表中 工资第六到第十高的员工,并且要求员工的部门为10 求出符合要求的员工的姓名和sal select ename,sal from (select * from (select rownum r,a.* from (select * from emp order by sal desc)a) where r between 6 and 10) where deptno = 10;
多表查询
-
笛卡尔积就是将两个集合中所有元素的匹配可能
-
数据库将每一张表 当做是一个集合 每一行记录当做是一个元素 将两张表以笛卡尔积的方式拼接在一起 列出所有记录和记录之前组合的可能性 得到的数据就是两张表做笛卡尔积之后的数据。
-
等值连接 select * from emp e,dept d where e.deptno = d.deptno;
-
企业要求 笛卡尔积不可以做三表及以上
-
92sql范式 和 97sql范式
-
两种范式的区别在于数据筛选的写法和性能的差异
-- 查询所有员工的姓名 部门编号和部门名称 -- 92范式书写 select e.ename,d.deptno,d.loc from emp e,dept d where e.deptno=d.deptno; -- 97sql范式 所使用的都是连接查询 select e.ename,d.deptno ,d.loc from emp e join dept d on e.deptno=d.deptno; -
97 范式中 表和表还是做笛卡尔积 但是进行等值连接的时候 进行连接的字段会写在 on 后面 从代码角度而言 更加清晰
-
-
在可以使用子查询的场景下 子查询的性能最优
-
如果查询的字段 来自于两张表 就无法使用子查询 所以优先选择连接查询
-
子查询>连接查询>直接笛卡尔积
-
97sql提供了五种连接查询的方式
- cross join 直接笛卡尔积
- inner join 内连接 一般可以简写成join
- left join 左连接
- right join 右连接
- full outer join 满外连接
-
只会复制表的字段和字段的值 不会复制约束
create table emp1 as select * from emp; -
left join 在连接的时候 会以左表的数据为主 不管左表中的数据在右表中是否有匹配数据 都强制显示
-
right join 在连接的时候 会以右表的数据为主 不管右表中的数据 在左表中是否有匹配数据 都强制显示
-
inner join 一般可以简写为join 如果发现一张表的数据在另一张表中没有匹配信息 则不显示
-
full outer join 满外连接 不管左右两张表中的数据 是否有匹配信息存在 都强制显示
-
自连接 表自己和自己做笛卡尔积 不等值连接
数据库建模 三大范式
- 第一范式 所有的列不可以再分(列的原子性)
- 第二范式 所有的字段都和主键直接相关
- 第三范式 表和表之间只存关系 不存数据
数据库对象（表 序列 索引 表空间 同义词 视图 游标 触发器 plsql 存储过程 存储函数 包）
-
序列是用来生成连续的整数数据的对象 一般用来为数据库表的主键提供值
-
语法如下:
-
create sequence 序列名称
-
start with xx -序列从某一个整数值开始生成数字
-
increment by xx -序列的增长间隔 如果是正数 则升序生成 如果是负数则降序生吃鞥
-
maxvalue xx | nomaxvalue -指定序列所生成数字的最大值 或者不设置最大值
-
minvalue xx | nominvalue -指定序列所生成数字的最小值 或者不设置最小值
-
就算你不设置最大最小值 序列所生成值也是有上限的
-
升序情况 默认最大值为1027 降序默认最大值为-1
-
最小值 升序默认最小值为1 降序默认最小值为-1026
-
cycle | nocycle – cycle 表示序列所生成的值达到最大值之后 会从最小值重新开始循环
-
nocycle表示没有循环 一旦到达表截止 序列就不可以再生成新的值
-
cache xx | nocache – 序列所生产的数字叫做序列号
-
如果使用cache缓存 序列会根据序列的规则预先生成一组序列号 存储在内存中 方便用户直接使用
-
当内存中的序列号用完的时候 系统会自动生成一组新的序列号 保存在内存中
-
oracle默认会生成20个序列号
-
nocache 不会预先存储序列号在内存中而是每次获取的时候再进行计算
-- 创建一个从1开始 最大值为40 每次增长间隔为1 不使用循环 并且使用缓存 序列中有预先分配好的30个序列号的序列 drop sequence myseq; create sequence myseq maxvalue 40 minvalue 1 start with 1 increment by 1 nocycle cache 30 -
序列对象默认提供了两个属性
-
nextval 从序列中取出下一个值
-
currval 从序列中取出当前值(如果序列没有生成过序列号 是无法获取当前值的)
-
dual
select myseq.nextval from dual; select myseq.currval from dual; create sequence myseq1 maxvalue 40 minvalue 1 start with 3 increment by 5 cycle nocache -
在序列设置循环的情况下 起始值只在第一次循环有效
-
第二次循环开始之后 就会从最小值开始生成 起始值就无效了
-
在定义序列的时候 序列的最小值 必须小于等于起始值
-
最大值必须大于等于起始值
-- 为jerk表创建序列 利用序列自动生成主键 create sequence myseq2 minvalue 1 start with 1 increment by 1 nocycle nomaxvalue nocache insert into jerk values(myseq2.nextval,'m'); insert into jerk values(myseq2.nextval,'q'); insert into jerk values(myseq2.nextval,'q'); -
在我们后续学完plsql语言和触发器之后 可以结合序列和触发器 实现表主键的自动增长
-
序列本身是有弊端的 最大值太小 只有1027 数据量大的情况下比较容易重复
-
所以一般在开发的时候 数据库的主键会采用uuid
-
开发过程中 会有一个工具叫做uuid生成器 可以确保数据库的主键字段值一定不会重复
-
-
视图本质是一个预查询
-
创建视图之前 所有普通用户必须由管理员赋予创建视图的权限
-
切换到sys 赋予创建视图的权限
grant create view to scott; -
视图语法
create[or replace][force|noforce] view 视图名称 as select 查询语句 [with read only] -
如果在创建视图的时候 选择了or replace参数 当视图已经存在的时候 则会使用当前视图进行替换
-
force 及时视图基于的 表并不存在 也可以创建视图 只不过视图不可以正常使用
-
with read only 表示当前视图是一个只读视图
--创建一个视图 对应的查询需求为 查询emp表中各个部门工资前2高的员工信息 create or replace view emp_rank as select a.empno,a.ename,a.job,nvl(a.comm,0) co,d.dname,d.loc from (select e.*,row_number()over(partition by deptno order by sal desc)rn from emp e)a join dept d on a.deptno=d.deptno where rn <3 select * from test_view; -- 如果你在创建视图的时候 不添加只读约束 使用者是可以通过视图修改基表数据的 delete from test_view where ename='JONES'; select * from emp; -- 一般来说 为了保护基表的数据 所以在创建视图的时候 必须添加只读约束 -
视图的意义何在？
- 1.简化复杂查询
- 2.保护基表数据
- 3.屏蔽使用者不关心 或者不希望被使用者访问的字段信息
-
-
同义词可以理解为数据库对象的别名
-- 切换到管理员用户 create user cll identified by a123; -- 赋予用户最基本的权限 以及创建同义词的权限 grant connect,resource to cll; grant create synonym to cll; -- 切换回cll用户 -- 对scott用户的emp表建立一个同义词 create synonym myemp for scott.emp; -- scott用户赋予用户cll权限 grant select on emp to cll; -- 赋予表的所有权限 grant all on emp to cll; -
索引 优化查询
-
建议拓展阅读 树 节点 二叉树 完全二叉树 前序后序中序便利
-
hash散列BTree B+Tree
-
select * from emp where ename = ‘SMITH’ 表中有一亿条数据
-
在没有索引的情况下 数据库会从第一条数据遍历到最后一条数据索引
-
索引是建立在数据库的某一个字段中的 索引会对建立索引的字段的值
-
进行预排序 当你使用了建立索引的字段作为where 条件或者order by条件的时候
-
数据库就不会进行全表遍历 而是通过索引 直接获取目标行的rowid 通过rowid直接访问数据
-
当我们创建表的时候 数据库会自动为所有的主键和唯一约束的字段 自动创建索引
1.普通索引 普通索引允许字段中的值出现重复的 -- 给emp表的ename字段建立普通索引 create index ename_idx on emp(ename); 2.唯一索引 建立的字段必须是主键或者唯一键 create unique index dname_idx on dept(dname); 3.联合索引 将两个字段捆在一起 将他们的值的组合作为索引排序的依据 create index test_idx on emp(mgr,sal); -- 位图索引 聚簇索引(了解即可) -
索引的特点：
- 极大的提高了查询的性能 表的数据量越大 索引带来的性能提升就越大
- 建立索引的字段 重复值越少 索引的性能越好 性别这样的字段 是不适合建立索引的
- 索引也不是越多越好 一张表最好不要超过五个索引 某个字段是否需要被建立索引 应该考虑这个字段是否是被查询高频使用的字段或者是排序字段 如果字段的使用评率很高 那么就算有重复值 也必须建立索引
- 我们对表数据的增删改 会导致数据库自动重建索引 在建立索引的时候 需要考虑 性能的消耗 但是 如果表的数据量非常大 不管这张表是否会频繁增删改查 都必须创建索引 因为查询性能是第一优先级
- 在进行查询的时候 需要注意where之后条件的使用 不合规范的使用会导致数据库 放弃使用索引 而进行全表检索(sql优化)
-
-
表空间
-
块 区 段
-
数据块block
-
oracle中的最小存储单位 为了屏蔽不同操作系统存储结构所带来的差异性
-
oracle定义了数据块的概念 将对所有数据的操作 转换成对oracle块的操作 数据的读写就不会受到操作系统的影响了
-
oracle所有对数据的操作和空间分配 实际上都是针对数据块block进行操作的
-
我们从表中搜索到一行数据 oracle会从硬盘或者内存缓冲区中 读取到改行所在的数据块
-
数据块 一般是以kb作为单位来定义的 默认是8kb
-
block之上的概念是 区 extent
-
区是比块大一级的存储结构 表示的是一连串连续的block集合
-
物理存储读写是随机读写的
-
在进行存储信息读写的时候 oracle将分配数据块进行存储 但是没法保证
-
所有分配的block都是连续存储的 所以才定义了分区
-
块的上一级 段 segment 是分区的上一层单位
-
段的类型可以是表 可以是其他的数据库对象
-
一般来说一个数据库对象对应一个段 一个段对应多个区 一个区对应多个块
-
数据库提供了一个系统视图 用来展示每一个数据库对象的存储详情
-
表空间 数据库的逻辑组成部分
- 从物理上讲 数据库的数据是存放在表空间所对应的数据文件中的
- 从逻辑上讲 数据库的数据 是存放在表空间内的
-
表空间一般是由一个或者多个数据文件所组成的 数据文件和日志文件都是数据库中非常重要的文件
-一个表空间至少有一个数据文件 一个表空间可以拥有多个段 但是 一个段只能属于一个表空间 -
system 系统表空间
-
sysaux 系统辅助表空间
-
Users表空间 如果是普通用户的数据库对象 没有指定表空间存储的情况下 默认会存放在users表空间下
-
temp 当数据库进行大量的子查询计算或者排序计算的时候 如果数据库的内存比较紧张会将一部分的临时数据 存放在临时表空间中
-
用户自定义表空间
-- 创建自定义表空间 create tablespace myspace DATAFILE 'c:/myspace_1.ora' size 10M, 'c:/myspace_2.ora' size 50M extent management local -- 表空间中区的分配由oracle系统自动分配 -- 可以在创建数据库对象(段)的时候 指定其所属表空间 create table lzs( lid number ) tablespace myspace; create table wjx( wid number primary key ) tablespace myspace; -- 可以在创建用户的时候 指定该用户所使用的表空间 该用户后续创建的所有数据库对象 都会自动存储到 -- 你指定的表空间内 create user css identified by a123 default tablespace myspace; -- 修改表空间的数据文件大小 alter database datafile 'c:/myspace_1.ora' resize 50M; -- 可以给指定表空间添加数据文件 alter tablespace myspace add datafile 'c:/myspace_3.ora' size 10M; -- 删除表空间 管理员是拥有直接删除表空间的权限的 如果是普通用户删除 --需要赋予对应的权限 grant drop tablespace to xx; -- 删除表空间以及表空间内的所有数据 drop tablespace myspace including contents;
-
-
数据库的数据文件是不可以从磁盘物理删除的 数据库在启动的时候 会自动检查所有的表空间的数据文件是否正常 如果你没有用过数据库 而是直接在本地通过磁盘物理的删除了数据文件 则数据库将无法启动
-
plsql是一个强类型语言 编译语言 高级语言
-
强类型语言和弱类型语言
- 强类型语言的特点 在声明变量并使用的时候 必须声明变量的数据类型 plsql java c++
- 弱类型语言的特点 声明变量的时候 不需要提供变量的类型 变量的类型由变量的值所决定 javascript shell
-
编译语言 脚本语言
-
C++ 代码必须先经过编译 才可以执行 plsql java
-
脚本语言拥有对应的解释器 解释执行即可 python javascript shell
-
高级语言 c++ c# java php ruby go javascript
-
中级语言 C语言(丹尼斯里奇 为了编写unix系统)
-
低级语言 汇编语言 每一个指令操作的都是硬件 缺点:入门门槛太高 太难理解
-
机器语言 0101
-
越低级的语言 离计算机底层越近 执行效率越高 嵌入式开发使用的都是C语言
-
如果普通用户想要执行plsql语言 需要通过sys用户进行权限赋予
grant create procedure to scott; grant execute any procedure to scott; grant debug connect session to scott; -- 切换到scott用户 declare --你在plsql中需要使用的变量 在delcare出定义声明 -- plsql语言和sql语句的数据类型是通用的 number char varchar2 date 是可以在plsql中直接使用的 -- plsql中=代表值比较 赋值使用:= sname varchar2(20):='fangling'; -- 也可以使用default关键字给变量提供初始值 但是在代码执行的时候 赋值还是通过:= sname2 varchar2(20) default 'zhoukun'; begin -- 你要运行的代码逻辑 -- plsql的控制台打印 类似于c++ 的print dbms_output.put_line(sname||'的好基友是'||sname2); end; -
plsql中的常量定义 一旦被定义 值无法被修改
declare pi constant number:=3.14; r number default 3; area number; begin -- pi:=3.141502654; area:=pi*r*r; dbms_output.put_line('面积为'||area); end; -
plsql中 除了可以直接使用sql语句的数据类型之外 还有标量数据类型和属性数据类型
-
标量数据类型 该类型的变量的只有一个值 包括数字型 字符型 日期型 布尔类型
-
所有的标量数据类型
- varchar2 char number date 常用的 通用类型
- timestamp oracle默认的高精度日期类型
- boolean 布尔类型 true false
- binary_integer plsql附加数据类型 值介于-231~231之间的整数
- natural 自然数类型 表示从0开始的正整数
- natrualn 和natrual几乎一样 但是值不可以为null
- positive 正整数
- positiven 非空正整数
- real 18位精度的浮点型
- int integer smallint 都是整数类型
- numberde number的子类型 表示38位精度的整数
- String 字符类型 和varchar2完全相同
-
属性数据类型 当声明的变量的值 是数据库表的某一行 或者是数据库某个字段的类型的时候 使用属性数据类型
-
%rowtype 代表的是数据库的某一行数据
declare myemp emp%rowtype;-- 声明一个变量用来存储emp表的一行记录(不可以存储多行) begin select * into myemp from emp where empno=7934; -- 你想访问该行数据中存储的那一个字段的值 就通过.+字段名进行访问 dbms_output.put_line(myemp.ename||'--'||myemp.sal); end;
-%type 数据类型的借用 引用某一个变量或者数据库的表字段数据类型 作为自己的数据类型
declare sal emp.sal%type;-- 定义一个变量叫做sal 数据类型和emp表的sal字段保持一致 mysal number(4):=3000; totalsal mysal%type;-- 定义变量totalsal的数据类型和mysql的保持一直 begin -- 查询emp弄为7934的员工薪水 打印该薪水加上mysal之后的数据结果 select sal into sal from emp where empno=7934; totalsal:=sal+mysal; dbms_output.put_line(totalsal); end;-
plsql的运算符号
-
– 算数运算符 + - * / ** 求幂 3**2=9
– 关系运算符 > < >= <= = 只能用来做值的比较 不可以用于赋值
– 不等于 != <>
– 范围运算符 … 1…100
– 逻辑运算符 and or not 和sql语句中所使用的是一样的 -
顺序结构 分支结构 循环结构
-
分支结构 if else
-
if 条件 then -- 你要运行的语句 end if; if 条件一 then 满足条件一所执行的语句 elsif 条件二 then 满足条件而所执行的语句 else 以上条件都不满足的时候 所执行的语句 end if; -- 使用plsql进行数据修改 查询emp的JAMES的sal 如果大于900 则工资增加10000 -- 如果工资小于900 工资扣100 -- 如果工资等于900 工资设置为1 declare newsal emp.sal%type; begin select sal into newsal from emp where ename='JAMES'; if newsal>900 then update emp set sal=sal+10000 where ename='JAMES'; elsif newsal=900 then update emp set sal=1 where ename='JAMES'; else update emp set sal=sal-500 where ename='JAMES'; end if; commit; end; -- 循环结构 while loop for in -- 求1-100的和 declare counter number(3):=0; sumResult number:=0; begin while counter<100 loop counter:=counter+1; sumResult:=sumResult+counter; end loop; dbms_output.put_line('1-100的计算结果为:'||sumResult); end; -- for in 方式求 1-100和 declare counter number(3):=0; sumResult number:=0; begin for counter in 1..100 loop sumResult:=sumResult+counter; end loop; dbms_output.put_line('1-100的计算结果为:'||sumResult); end; -- 打印emp表中 所有员工的ename和sal -- %rowtype变量 一次只能存储一行数据 需要借助rownum -- 通过循环和rownum 每一次循环读取表中的一行数据 存到%rowtype变量中 然后打印 -- 一直到循环结束 declare emp_row emp%rowtype;-- 用来存储emp表一行记录的变量 v_count number(2); -- 记录表的行数 v number(2) default 0;-- 记录循环次数 begin -- 先获取表一共有多少行数据 该数据决定了循环的最终次数 select count(*) into v_count from emp; -- 死循环 直接使用 loop end loop 在循环内设置循环退出的条件 loop v:=v+1; select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno into emp_row from (select rownum r,e.* from emp e)a where a.r=v; -- 打印数据 dbms_output.put_line(emp_row.ename||'--'||emp_row.sal); -- 设置循环结束的条件 exit when v=v_count; end loop; end; -
练习 借助for循环和if else进行判断
– 遍历所有员工 将员工的工资和所有员工的平均工资进行对比
– 如果员工工资大于公司平均工资 则涨薪500
– 并且打印更新之后的薪水 打印内容如下
– xx员工的工资太高了 所以给其涨薪500 当前sal为xx
– 如果员工工资小于公司的平均工资 则降薪1000 并且打印更新最后的薪水
– xx员工的薪资太低了 为了鼓励该员工 给其降薪1000 当前sal为xx
– 如果员工的工资降薪之后小于0 则设置薪水为0declare v_count number(2); -- 记录循环总次数 v number(2) default 0; -- 记录循环次数 avg_sal emp.sal%type; -- 存放平均工资的变量 v_sal emp.sal%type;-- 存放的是员工的薪水 emp_row emp%rowtype; -- 存放的是emp表的某一整行数据 v_empno emp.empno%type;-- 存储员工编号 v_ename emp.ename%type;-- 存储员工姓名 begin -- 获取表的总记录数 select count(*) into v_count from emp; -- 获取公司的平均工资 select avg(sal) into avg_sal from emp; -- 遍历过程 loop v:=v+1; select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno into emp_row from (select rownum r,e.* from emp e)a where a.r=v; -- 行记录中 将后续需要使用的数据取出 存放到我们定义好的变量中 v_sal:=emp_row.sal; v_empno:=emp_row.empno; v_ename:=emp_row.ename; -- 比较判断 if v_sal>avg_sal then update emp set sal=sal+500 where empno=v_empno; dbms_output.put_line(v_ename||'员工工资太高,所以加薪500元,当前sal为:'||(v_sal+500)); else -- 先判断降薪之后工资是否可能小于0 if(v_sal-1000)<0 then update emp set sal=0 where empno=v_empno; dbms_output.put_line(v_ename||'员工工资太低,所以降薪,当前sal为0'); else update emp set sal=sal-1000 where empno=v_empno; dbms_output.put_line(v_ename||'员工工资太低,所以降薪1000元,当前sal为:'||(v_sal-1000)); end if; end if; -- 表的数据遍历借书 循环节数 exit when v=v_count; end loop; commit; end;
-
-
触发器 在事件发生的时候 自动执行的sql语句 不能被直接调用 也不可以接受任何参数
- 根据触发器的创建语句 以及操作的不同对象 触发器分为几类
- 1 DML触发器
-
当我们对指定数据进行DML操作的时候 所触发的触发器
-
语句级触发器 行级触发器
-
语句级触发器只会触发一次
-
行级触发器 只要满足条件 就会触发 会多次触发
-
before after触发器
-
before触发器 在触发事件 发生之前 触发阿奇的代码就会执行
-
after 在触发事件发生自后 执行触发器的代码
create [or replace] trigger 触发器名称 {before|after} 触发条件 on 表名 [for each row ] -- 如果加上这一行就说明当前触发器为行级触发器 where 触发条件 -- 声明 什么时候会触发触发器 begin --触发器的代码 end;
-
- 1 DML触发器
-触发器例子
-- 在创建触发器之前 先创建两张表 用于后续的场景 drop table student; create table student( id number(20), stu_no varchar2(20), stu_name varchar2(20), stu_age number(2), stu_major varchar2(30) ); -- 日志记录表 create table stu_log( log_id number,-- 日志id log_action varchar2(100),-- 记录你对学生表进行的操作 log_date date,-- 记录操作发生的时间 log_message varchar2(30)-- 记录操作相关的信息 ); -- 创建序列 用于后续使用 create sequence seq_test start with 1 nomaxvalue nominvalue nocycle nocache -- 创建一个行级触发器(before触发器) 用来实现主键id的自动插入 create or replace trigger modify_stu -- 创建触发器 before insert on student -- 定义触发条件 在向学生表插入数据之前 for each row -- 定义为行级触发器 -- 触发器被触发之后所执行的代码 declare next_id number; begin -- 从序列中取出数据 存放在变量next_id上 方便后续使用 select seq_test.nextval into next_id from dual; -- :new代表的是你即将插入的insert语句 -- :new.id代表的是你即将插入的insert语句对应的id字段值 -- 由你从序列中获取的数据 代替你insert语句中原有的id属性 :new.id:=next_id; end; -- 向学生表中插入数据 但是不提供id insert into student(stu_no,stu_name,stu_age,stu_major) values('11001100','王建宇',22,'计算机'); insert into student(stu_no,stu_name,stu_age,stu_major) values('11001101','王宇',22,'计科'); select * from student; --行级触发器(after触发器) -- 功能把所有对student表的操作记录都记录到stu_log表中 create or replace trigger modify_stu1 -- 当插入数据或者删除数据或者修改学生表的stu_name属性的时候 就会触发 after insert or delete or update of stu_name on student for each row begin -- 对触发不同条件提供不同的操作 if inserting then -- 如果是insert操作触发了触发器 --当有新的数据插入到学生表中的时候 日志表记录行为为insert 并且记录插入数据的时间 -- 和新插入的学生姓名 -- :new.stu_name 代表你新插入的语句中的stu_name字段的值 insert into stu_log values(1,'insert',sysdate,:new.stu_name); elsif deleting then -- 对删除行为所执行的触发器 -- 如果是删除操作触发了触发器 日志表会记录相关信息 -- :old代表的是被删除的行记录 -- :old.stu_name 将被删除的数据的stu_name属性值 insert into stu_log values(2,'delete',sysdate,:old.stu_name); elsif updating then -- :old.stu_name 代表的是被更新之前的学生姓名 -- :new.stu_name 代表的是被更新之后的学生姓名 insert into stu_log values(3,'update_old',sysdate,:old.stu_name); insert into stu_log values(4,'update_new',sysdate,:new.stu_name); end if; end; -- 语句级触发器(before触发器) 用来控制对表的修改 create or replace trigger modify_stu before insert or update or delete on student begin if deleting then raise_application_error(-20001,'该表不允许删除数据'); -- 自定义错误 前者是错误的编号 后者是错误的信息 -- 错误id 20000之前是系统错误id 我们无法使用 -- 当错误出现的时候 DML操作不会被执行 elsif updating then raise_application_error(-20002,'该表不允许更新数据'); elsif inserting then raise_application_error(-20003,'该表不允许插入数据'); end if; end; - 根据触发器的创建语句 以及操作的不同对象 触发器分为几类
-
游标 用来遍历多行数据的工具
-
显示游标 隐式游标 有点类似于C语言的指针
-
游标的属性
-
游标名称%FOUND 返回的是一个布尔值 代表最近一次游标对数据的提取是否成功
-
成功为true 否则为false
-
游标名称%NOTFOUND 获取的布尔值于%FOUND正好相反
-
游标名称%ISOPEN 当游标已经被打开的时候 返回ture 否则返回fasle
-
游标名称%ROWCOUNT 返回的是游标已经读取的记录数量
-
fetch 游标名称 into XX 移动游标指针并提取数据至指定变量
-游标例子– 查询emp表的所有员工姓名和薪水
declare
cursor c_cursor
is
select ename,sal from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
– 游标在使用之前 需要进行打开
open c_cursor;
– 从游标中 将数据提取出来
–fetch c_cursor into v_ename,v_sal;
while c_cursor%FOUND loop
– 对当前游标的指针中是否有数据进行判断
dbms_output.put_line(v_ename||‘—’||v_sal);
fetch c_cursor into v_ename,v_sal;
end loop;
– 游标在使用完成之后 需要进行关闭
close c_cursor;
end;– 使用游标 给emp表中员工工资低于1200的员工涨薪50
declare
v_empno emp.empno%type;
v_sal emp.sal%type;
cursor c_cursor is select empno,sal from emp;
begin
open c_cursor;
loop
fetch c_cursor into v_empno,v_sal;
exit when c_cursor%NOTFOUND;
if v_sal<1200 then
update emp set sal = sal+50 where empno=v_empno;
dbms_output.put_line(‘员工编号为’||v_empno||‘的员工已涨薪!’);
end if;
dbms_output.put_line(‘游标读取的记录数据量为:’||c_cursor%rowcount);
end loop;
close c_cursor;
end;
-
-
显式游标的每次使用 都需要经历打开 关闭 判断 fetch提取 使用太过麻烦
-
这些操作不应该由程序员完成
-
隐式游标 只需要定义和使用 打开 关闭 判断 提取 都被省略了
-- 遍历emp表所有员工的ename和sal declare cursor c_cur is select ename,sal from emp; begin -- 省略了游标的open -- v_row相当于是你临时定义的一个rowtype 类型的变量 -- 用来存储你从游标中提取的整行数据 -- fetch 和 %found也被隐式游标自动省略了 for v_row in c_cur loop dbms_output.put_line(v_row.ename||'--'||v_row.sal); end loop; -- 游标的关闭 也被省略了 end; -- 利用游标 遍历emp表的数据 将其中sal>500 并且职位为SALESMAN的员工 -- 从emp表删除 rollback; declare cursor c_cur is select empno from emp where job='SALESMAN' and sal>500; begin for v_row in c_cur loop delete from emp where empno = v_row.empno; end loop; end;
-
-
存储过程和存储函数
-
plsql匿名代码块
-
将plsql的代码 存放在存储过程中 而存储过程又是存放在数据库中的
-
只要用户有对应的权限 所有的用户都可以使用该存储过程实现功能
-
定义存储过程，调用存储过程的例子
-- 定义一个存储过程 用来获取emp表的总人数 create or replace procedure emp_count as v_count number(2); begin select count(*) into v_count from emp; dbms_output.put_line('emp表总人数为'||v_count); end; -- 调用存储过程 begin emp_count; end; -- 定义一个存储过程 用来展示所有员工的信息 和员工的总人数 create or replace procedure emp_list as cursor emp_cursor is select empno,ename,sal from emp; begin -- 借助游标遍历数据 for emp_row in emp_cursor loop dbms_output.put_line(emp_row.empno||'---'||emp_row.ename||'---'||emp_row.sal); end loop; -- 在当前存储过程中 调用其他已经写好的存储过程 emp_count; end; -- 调用 begin emp_list; end; -
我们希望可以根据不同的条件 让存储过程获取不同的结果，所以引入了参数的概念
-
in out in out参数
-
in 参数 定义一个输入的参数变量 用于从存储过程外把参数传递给存储过程内
-
out参数 定义一个输出参数变量 用于从存储过程获取数据
-
in out参数 兼备以上两者的工程
-- 编写一个给指定员工增加指定工资的存储过程 change_sal select * from emp; -- p_empno和p_raise都是我们指定的参数的名称 in代表这是一个传入参数 number代表的是参数的 -- 数据类型 default代表默认值 当你调用存储过程没有提供参数的时候 参数就会使用默认值做为自己的值 create or replace procedure change_sal(p_empno in number default 7788,p_raise in number default 10) as v_ename varchar2(10); v_sal number(5); begin -- 获取指定员工的ename和sal 并且存储到本地变量中 select ename,sal into v_ename,v_sal from emp where empno=p_empno; -- 指定JAMES员工不可以加薪水 if p_empno=7900 then dbms_output.put_line('该员工不配涨薪'); raise_application_error(-20001,'该员工不允许涨薪,管老师说的'); end if; -- 通过update 语句 对指定员工进行sal更新 update emp set sal=sal+p_raise where empno=p_empno; -- 打印被加薪人的情况 dbms_output.put_line('打工人'||v_ename||'的工资被改为'||(v_sal+p_raise)); -- 异常处理 代码发生错误的时候 所执行的操作 -- others代表所有的错误类型 后续我们会加入指定错误类型 exception when others then dbms_output.put_line('发生错误,拒绝修改!'); rollback; end; begin change_sal; end; -- out参数 可以用来返回存储过程的运行结果 create or replace procedure emp_count(p_total out number) as begin --由存储过程给参数进行赋值 select count(*) into p_total from emp; end; -- 在plsql中 调用搓出过程 并且获取out输出参数的值 declare v_empcount number; begin -- 调用存储过程 将存储过程的out参数的值 传递给v_empcount -- 等价于 v_empcount:=p_total emp_count(v_empcount); dbms_output.put_line('打工人总人数为:'||v_empcount); end; -- in out 参数 不仅负责输入还负责输出 -- 定义一个存储过程 负责给指定的电话号码加区号 create or replace procedure add_region(p_phone in out varchar2) as begin p_phone:='025-'||p_phone; end; -- 用来测试的plsql declare v_phone varchar2(100); begin v_phone:='88888888'; -- 由于 v_phone是 in out类型的参数 -- 先将v_phone的值传递给add_region存储过程的内容 -- p_phone的值就变为了88888888 经过存储过程的拼接 变成了025-88888888 -- 又由于其out参数的特性 这个处理好的电话号会再次被传递给v_phone -- 等价于 v_phone:=p_phone add_region(v_phone); dbms_output.put_line('加区号的电话号码为:'||v_phone); end;-
存储函数 存储函数和存储过程都一样 都是plsql代码的封装
-
但是区别在于 存储函数拥有参数 但是参数只能是in 类型 in可以省略不写
-
在定义存储函数的时候 会定义return数据类型 也就是返回类型
-
在代码执行部分 会出现return表达式 但是只有一个return语句会执行
-
存储函数一旦执行了return 函数就会立刻借书运行
-- 创建一个存储函数 通过empno获取ename create or replace function get_emp_ename(p_empno number default 7788) -- 定义的是返回值类型 return varchar2 as v_ename varchar2(10); begin select ename into v_ename from emp where empno=p_empno; return(v_ename); exception when NO_DATA_FOUND then dbms_output.put_line('没有该编号的雇员'); return(null); when too_many_rows then dbms_output.put_line('返回记录过多,请重新输入！'); return(null); when others then dbms_output.put_line('发生其他类型的错误！'); return(null); end; -- 测试语句 declare v_ename varchar2(20); begin v_ename:=get_emp_ename(7900); dbms_output.put_line('雇员7900的姓名为:'||v_ename); v_ename:=get_emp_ename(7839); dbms_output.put_line('雇员7839的姓名为:'||v_ename); end; -
数据字典 存放了当前用户的所有存储过程和存储函数的源代码
-
user_source 系统提供的视图
select * from user_source where name = 'GET_DEPT_NAME'; -
USER_OBJECTS 查看一个存储过程或者函数是否处于有效状态
select * from user_objects- status表示数据库对象的状态 valid表示可用的 （通过编译 可以运行）
- invalid 不可用(有错误 未通过编译)
-
-
-
包 package
- 是用来存储相关程序结构的对象 一般存储于数据字段中
- 包由两个部分组成 一个是包头 package 一个是包体 packagebody
- 包头相当于是对外的操作接口 对调用者而言是可见的
- 包体是包的代码部分和实现部分 对调用者而言是不可见的黑盒
- 包相遇是遥控器主体 ，包头相当于遥控器上的所有按键，包体相当于是遥控器内部的电子元器件
- 让使用者和设计者分离 使用者只需要知道如何通过按键实现不同的遥控功能即可 遥控器上的每一个按键对应的是某一个存储过程或者存储函数
- 封装 高内聚 低耦合
- 包中可以包含什么程序结构？存储过程 存储函数 变量 常量 游标
- 包头拥有说明部分 可以出现在包的不同位置
- public 公有元素 在包头中说明 整个应用程序都可以访问
- private 私有元素 在包体的说明部分说明 只能被包的内部其他部分访问
- local 本地元素 在包中包含的存储过程或者函数的声明部分声明 只能在定义该元素的过程或者函数中使用
- 包的特点 可以方便的将存储过程和存储函数组织到一起，如果有多个包 每一个包是独立的 在不同的保重 存储过程和存储函数可以重名
- oracle自己 也提供了很多系统预定义的系统包 这些包可以在数据库的任何地方使用
-
dbms_output .put_line
-
dbms_output 控制台相关程序
-
dbms_ddl 编译过程 函数 和包
-
dbms_mail oracle的邮箱机制
-
dmbs_lock oracle的复杂锁机制
-- 创建一个简化版的 用来的管理emp信息的包 叫做emoployee 可以用来从emp表获取员工信息 -- 修改员工名称 修改工资等等功能 -- 在创建包的时候 包和包体可以一起编译 也可以分开编译 -- 如果在一起编译 包头写在前 包体写在后 中间是用 /分割 create or replace package employe -- 包头部分 is procedure show_detail;-- 包头中声明了一个存储过程 用来展示信息 procedure get_employe(p_empno number); procedure save_employe; procedure change_name(p_newname varchar2); procedure change_sal(p_newsal number); end employe; / create or replace package body employe -- 包体部分 is employe emp%rowtype; -- 为你所有的包头中声明的存储过程或者函数提供代码实现 -- show_detail存储过程的代码实现 展示雇员信息的 procedure show_detail as begin dbms_output.put_line('-------雇员信息如下--------'); dbms_output.put_line('雇员编号:'||employe.empno); dbms_output.put_line('雇员名称:'||employe.ename); dbms_output.put_line('雇员职务:'||employe.job); dbms_output.put_line('雇员工资:'||employe.sal); dbms_output.put_line('雇员部门编号:'||employe.deptno); end show_detail; -- 从emp表获取一个雇员信息 procedure get_employe(p_empno number) as begin select * into employe from emp where empno=p_empno; dbms_output.put_line('获取雇员'||employe.ename||'信息成功!'); end get_employe; -- 保存雇员信息到雇员表 procedure save_employe as begin update emp set ename=employe.ename,sal=employe.sal where empno=employe.empno; dbms_output.put_line('雇员信息保存成功!'); end save_employe; -- 修改雇员姓名 procedure change_name(p_newname varchar2) as begin employe.ename:=p_newname; dbms_output.put_line('修改名称成功!'); end change_name; -- 修改雇员工资 procedure change_sal(p_newsal number) as begin employe.sal:=p_newsal; dbms_output.put_line('修改工资完成!'); end change_sal; end employe; -
包是长期存储在数据库中
-
我们在包体中定义了变量 employe rowtype
-
这个变量只要被赋值过 变量的值可以长期存储
-
通过包中定义的变量 存储数据
-
包中的操作 都是针对于这个变量进行操作
-
如果需要数据最终存储到对应表中 必须要执行dml语句
begin -- employe.get_employe(7900); -- employe.show_detail; employe.change_sal(666); employe.change_name('KOBE'); employe.save_employe; end; select * from emp; -- 创建一个包 对emp表进行完整的crud create or replace package emp_pk -- 包头 is v_emp_count number(5);-- 存放雇员总人数 -- 负责初始化雇员人数(给变量进行赋值)和工资修改的上下限 procedure init(p_max number,p_min number); procedure list_emp;-- 展示雇员信息 -- 插入雇员 procedure insert_emp(p_empno number,p_ename varchar2,p_job varchar2,p_sal number); procedure delete_emp(p_empno number);-- 删除雇员 -- 修改雇员工资 procedure change_emp_sal(p_empno number,p_sal number); end emp_pk; / create or replace package body emp_pk is v_message varchar2(50);-- 存放用来输出的信息 v_max_sal number(7);-- 后续在定义工资的最大值时候 用来在包中存储最大值的变量 v_min_sal number(7);-- 工资的下限 -- 定义函数 判断对应的雇员是否存在 -- 在包头中声明的存储过程或者函数 可以被所有其他用户调用 -- 如果没有在包头中声明 而是定义在包体中 只能在当前包中被 其他存储过程或函数调用 -- 外界不可以获取 function exist_emp(p_empno number) return boolean; procedure show_message;-- 用来展示数据的存储过程 -- 进行初始化的存储过程 procedure init(p_max number,p_min number) is begin select count(*) into v_emp_count from emp;-- 给包头中定义的用来存储雇员表总人数的变量赋值 -- 最大最小薪资的赋值 v_max_sal:=p_max; v_min_sal:=p_min; v_message:='初始化过程已经完成!'; show_message;-- 这是后续我们需要写的专门负责打印信息的过程 可以先把调用写好 end init; -- 进行雇员信息查询的存储过程 procedure list_emp is begin dbms_output.put_line('姓名 职务 工资'); -- 这是游标的最最最简单写法 相当于定义了一个匿名游标 游标指向的数据范围 -- 为select * from emp emp_rec就相当于是用来读取游标信息的rowtype类型变量 for emp_rec in(select * from emp) loop dbms_output.put_line(emp_rec.ename||' '||emp_rec.job||' '||emp_rec.sal); end loop; end list_emp; -- 插入雇员的存储过程 procedure insert_emp(p_empno number,p_ename varchar2,p_job varchar2,p_sal number) is begin -- 这是我们后续编写的存储函数 用来判断对应雇员的编号的数据是否存在的 -- 先把调用写好 if NOT EXIST_EMP(p_empno) then insert into emp(empno,ename,job,sal) values(p_empno,p_ename,p_job,p_sal); commit; -- 插入数据成功之后 我们原本记录的雇员总人数此时应该+1 v_emp_count:=v_emp_count+1; v_message:='雇员'||p_empno||'已经被成功插入'; else v_message:='雇员'||p_empno||'已经存在,数据插入失败!'; end if; show_message; -- 异常处理 exception when others then v_message:='发生错误,雇员'||p_empno||'插入失败,请检查参数!'; show_message; end insert_emp; -- 删除雇员的过程 procedure delete_emp(p_empno number) is begin -- 在删除之前 先判断被删除的雇员是否存在 if exist_emp(p_empno) then delete from emp where empno=p_empno; commit; -- 由于数据被删除了 表记录总数应该减一 v_emp_count:=v_emp_count-1; v_message:='雇员'||p_empno||'已经被删除!'; else v_message:='雇员'||p_empno||'不存在,无法被删除!'; end if; show_message; exception when others then v_message:='雇员'||p_empno||'删除失败,请检查参数!'; show_message; end delete_emp; -- 修改雇员工资 procedure change_emp_sal(p_empno number,p_sal number) is begin -- 对工资是否超出范围进行判断 if(p_sal>v_max_sal or p_sal<v_min_sal) then v_message:='工资超出修改范围'; elsif not exist_emp(p_empno) then -- 如果员工不存在 也不可以修改 v_message:='雇员'||p_empno||'不存在,不可以修改工资!'; else update emp set sal=p_sal where empno=p_empno; commit; v_message:='雇员'||p_empno||'工资已经修改成功!'; end if; show_message; exception when others then v_message:='雇员'||p_empno||'修改异常,请检查参数!'; show_message; end change_emp_sal; -- 显示信息过程 procedure show_message is begin dbms_output.put_line('提示信息:'||v_message); end show_message; -- 判断雇员是否存在的函数 function exist_emp(p_empno number) return boolean is v_num number;-- 局部变量 在包中的函数的内部定义的 只有在当前函数中可以使用 begin select count(*) into v_num from emp where empno=p_empno; if v_num=1 then return true; else return false; end if; end exist_emp; end emp_pk; -- 初始化包的数据 begin emp_pk.init(20000,0); end; -- 显示员工列表 begin emp_pk.list_emp; end; -- 插入雇员信息 begin emp_pk.insert_emp(8888,'小周','STUDENT',50); end; -- 通过全局变量v_emp_count查看雇员人数 begin dbms_output.put_line(emp_pk.v_emp_count); end; -- 删除记录 begin emp_pk.delete_emp(8888); emp_pk.list_emp; end; -- 修改雇员工资 begin emp_pk.change_emp_sal(7900,50000); end; -- 通过包的拥有者将包的执行权限赋予给指定用户 grant execute on emp_pk to cll; -
v_emp_count 公有变量 定义在包头中 是可以直接被外部通过包名.变量名直接访问的
-
v_max_sal 和 v_min_sal 定义在包体内 不能被外界访问的 只能通过
-
包内部的过程或者函数来进行访问和修改
-
同样 exist_emp和show_message 是私有过程 没有在包头中定义 就不可以通过包直接访问
-
519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
