1、单行注释:用--
2、多行注释:用/**/
1、管理员用户
管理员用户可以访问数据库中的任何数据,不需要权限,也可以创建用户、修改用户密码、赋予用户权限等行为。
2、普通用户
普通用户被管理员创建,所有访问权限来自于管理员的赋予。创建的用户都是普通用户 初始状态不具备任何权限。
如果想要用户可以正常进行登录,需要我们提供对应的权限。
**- connect 权限 连接权限 (允许用户进行登录)
用户创建、添加权限、删除修改权限的例子
-- 查询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字节
**varchar2(n) ** 不定长 最大不能超过4000字节
char和varchar2之间的区别:
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的方式 存储目标资源的公网路径 进行存储
建立约束例子
-- 检查约束 限制性别的字段值只能是男或者女
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);
插入:
修改:
删除:
数据添加的本质是硬盘读写,大量的硬盘读写会导致硬盘性能下降,数据库为了避免这样的情况,所以有了日志文件,并且引入了session会话。
oracle是支持多个用户同时登录的数据库,每当发生了用户登录的行为,则我们的数据库都会为该用户分配一个session会话,每一个用户都有一个自己的日志文件,创建表create用户属于DDL语句,而用户所执行的所有DDL语句都会被立刻提交到数据库。但是所有的DML语句并不会被立刻提交到数据库,而是会存放在于会话锁绑定的一个日志文件中,如果你不提交sql语句到数据库,则管理员和其他用户都无法看到你没提交到数据库的执行的sql语句得到的数据。
truncate删除的数据是不可以恢复的 DDL操作 DDL操作是直接提交数据库的。
将结果提交到数据库中,必须执行指令commit;,不然只能在当前用户的会话中查看到。
savepoint 叫做存档点 对当前日志文件中的数据操作进行存档 savepoint A;
rollback 回滚 读档 可以将日志文件中的数据状态 回滚到指定存档点的时候 rollback to A;
向公司的数据库插入100w条数据 这个是业务
我需要准备100w条insert语句 负责数据库插入,这100w条insert语句 就可以看住是一个整体,这个整体叫做事务。
字段修改(增/删/重名命/修改字段类型/删除用户/删除表):
查询语句:
单表简单查询
可使用别名 修改字段在显示时候的效果 在字段名之后空格跟别名
字符串连接符号 || 可以将字段的值和指定的字符串进行拼接。例子: 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提供了五种连接查询的方式
只会复制表的字段和字段的值 不会复制约束
create table emp1 as select * from emp;
left join 在连接的时候 会以左表的数据为主 不管左表中的数据在右表中是否有匹配数据 都强制显示
right join 在连接的时候 会以右表的数据为主 不管右表中的数据 在左表中是否有匹配数据 都强制显示
inner join 一般可以简写为join 如果发现一张表的数据在另一张表中没有匹配信息 则不显示
full outer join 满外连接 不管左右两张表中的数据 是否有匹配信息存在 都强制显示
自连接 表自己和自己做笛卡尔积 不等值连接
序列是用来生成连续的整数数据的对象 一般用来为数据库表的主键提供值
语法如下:
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;
-- 一般来说 为了保护基表的数据 所以在创建视图的时候 必须添加只读约束
视图的意义何在?
同义词可以理解为数据库对象的别名
-- 切换到管理员用户
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);
-- 位图索引 聚簇索引(了解即可)
索引的特点:
表空间
块 区 段
数据块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是一个强类型语言 编译语言 高级语言
强类型语言和弱类型语言
编译语言 脚本语言
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语句的数据类型之外 还有标量数据类型和属性数据类型
标量数据类型 该类型的变量的只有一个值 包括数字型 字符型 日期型 布尔类型
所有的标量数据类型
属性数据类型 当声明的变量的值 是数据库表的某一行 或者是数据库某个字段的类型的时候 使用属性数据类型
%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 则设置薪水为0
declare
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语句 不能被直接调用 也不可以接受任何参数
当我们对指定数据进行DML操作的时候 所触发的触发器
语句级触发器 行级触发器
语句级触发器只会触发一次
行级触发器 只要满足条件 就会触发 会多次触发
before after触发器
before触发器 在触发事件 发生之前 触发阿奇的代码就会执行
after 在触发事件发生自后 执行触发器的代码
create [or replace] trigger 触发器名称
{before|after}
触发条件
on 表名
[for each row ] -- 如果加上这一行就说明当前触发器为行级触发器
where 触发条件 -- 声明 什么时候会触发触发器
begin
--触发器的代码
end;
-触发器例子
-- 在创建触发器之前 先创建两张表 用于后续的场景
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
包 package
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 是私有过程 没有在包头中定义 就不可以通过包直接访问