oracle数据库，目前还是有很多公司在用的，所以一起来学习一下吧！ 文章有点长，可以收藏慢慢看哦。
- 一、 oracle介绍
- 1. 数据库
- 2. 实例
- 3. 用户
- 4. 表空间
- 5. 数据文件（dbf、ora）
- 二、 创建表空间
- 三、 用户
- 6. 创建用户
- 7. 用户赋权限
- 四、 Oracle数据类型
- 五、 表的管理
- 1.1 建表
- 2.1 表删除
- 3.1 表的修改
- 4.1 数据库表数据的更新
- 六、 Scott用户下的表结构
- 七、 单行函数
- 1. 字符函数
- 2. 数值函数
- 3.日期函数
- 4.转换函数
- 5.通用函数
- 八、 多行函数（聚合函数）
- 1.统计记录数 count()
- 2.最小值查询 min()
- 3.最大值查询 max()
- 4.查询平均值 avg()
- 5.求和函数 sum()
- 九、 分组统计
- 十、 多表查询[应用]
- 外连接（左右连接）
- 子查询
- oracle中的分页
- 视图
- 视图的作用？
- 索引
- pl/sql 基本语法
- 什么是 PL/SQL?
- pl/sql中的if判断
- pl/sql中的loop循环
- 游标
- 存储过程
- 测试f_yearsal
- out类型参数如何使用
- 测试p_yearsal
- in和out类型参数的区别是什么？
- 存储过程和存储函数的区别
- 触发器
- 语句级触发器
- 行级别触发器
- 触发器实现主键自增
一、 oracle介绍
ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组 软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。
比如SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据 库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；
作为一个关系数据库， 它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只 要在一种机型上学习了ORACLE 知识，便能在各种类型的机器上使用它。
1. 数据库
Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者DBF、控制文件、联机日 志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统 只有一个库。可以看作是 Oracle就只有一个大数据库。
2. 实例
一个 Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构 （Memory Structures)组成。一个数据库可以有 n个实例。
3. 用户
用户是在实例下建立的。不同实例可以建相同名字的用户。
4. 表空间
表空间是 Oracle对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每 个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件 只能属于一个表空间。
5. 数据文件（dbf、ora）
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于 一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数 据文件，只能删除其所属于的表空间才行。
注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到 一个或者多个数据文件中。
由于 oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同 一个名字的表！这里区分就是用户了！
二、 创建表空间
表空间？ORACLE数据库的逻辑单元。数据库---表空间 一个表空间可以与多个数据 文件（物理结构）关联
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立 多个表。
create tablespace nateshao
datafile 'D:nateshao.dbf'
size 100m
autoextend on
next 10m
nateshao为表空间名称datafile指定表空间对应的数据文件size后定义的是表空间的初始大小autoextend on自动增长 ，当表空间存储都占满时，自动增长next后指定的是一次自动增长的大小。
三、 用户
6. 创建用户
create user nateshaouser
identified by nateshao
default tablespace nateshao
identified by后边是用户的密码
default tablespace后边是表空间名称
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户 下的。
7. 用户赋权限
新创建的用户没有任何权限，登陆后会提示
Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。
- CONNECT 角色：--是授予最终用户的典型权利，最基本的
- RESOURCE 角色：--是授予开发人员的
- DBA 角色：拥有全部特权，是系统最高权限，只有
DBA才可以创建数据库结构，并且系统 权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除grant dba to itcastuser进入system用户下给用户赋予dba权限，否则无法正常登陆
CONNECT 角色RESOURCE 角色ALTER SESSION --修改会话CREATE CLUSTER --建立聚簇CREATE CLUSTER --建立聚簇CREATE PROCEDURE --建立过程CREATE DATABASE LINK --建立数据库链接CREATE SEQUENCE --建立序列CREATE SEQUENCE --建立序列CREATE TABLE --建表CREATE SESSION --建立会话CREATE TRIGGER --建立触发器CREATE SYNONYM --建立同义词CREATE TYPE --建立类型CREATE VIEW --建立视图
四、 Oracle数据类型
前面三个常用
No数据类型描述1Varchar， varchar2(常用)表示一个字符串2NUMBERNUMBER(n)表示一个整数，长度是 n，NUMBER(m,n):表示一个小数，总长度是 m，小 数是 n，整数是 m-n3DATA表示日期类型4CLOB大对象，表示大文本数据类型，可存 4G5BLOB大对象，表示二进制数据，可存 4G
五、 表的管理
1.1 建表
语法：Create table 表名
Create table 表名（
字段 1 数据类型 [default 默认值],
字段 2 数据类型 [default 默认值],
...
字段 n 数据类型 [default 默认值]
）;
范例：创建 person 表
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));
2.1 表删除
语法：DROP TABLE 表名
3.1 表的修改
在 sql 中使用 alter 可以修改表
- 添加语法： ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
- 修改语法 ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
- 修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2
范例：在 person 表中增加列 address
alter table person add(address varchar2(10));
范例：把 person 表的 address 列的长度修改成 20 长度
alter table person modify(address varchar2(20));
4.1 数据库表数据的更新
1. INSERT（增加）
标准写法：
INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...)
简单写法（不建议）
INSERT INTO 表名 VALUES(值 1，值 2，...)
- 注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null
insert into person values(2,'李四',1,null,'北京育新');
2. UPDATE（修改）
全部修改：
UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....
局部修改：
UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；
3. DELETE（删除）
语法 :
DELETE FROM 表名 WHERE 删除条件;
- 在删除语句中如果不指定删除条件的话就会删除所有的数据
- 因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数 据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据 库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
5.1序列
在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
范例:创建一个 seqpersonid 的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual; -- (这样写法有误解)
在插入数据时需要自增的主键中可以这样使用
insert into persion values(seqpersonid.nextval,'千羽'，1，null,'广东深圳');
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪 一张表使用都可以，但是我们一般都是一张表用一个序列。
六、 Scott用户下的表结构
雇员表（EMP）
No字段类型描述1empnonumber(4)表示雇员编号，是唯一编号2enamevarchar2(10)表示雇员姓名3jobvarchar2(10)表示工作单位4mgrnumber(4)表示员工雇员的领导编号5hiredatedate表示雇佣时间6salnumber(7,2)表示月薪工资7commnumber(7,2)表示奖金，或者称为佣金8depinonumber(2)部门编号
部门表（dept）
No字段类型描述1depinonumber(2)部门编号，是唯一的编号2dnamevarchar2(14)部门名称3locvarchar2(13)部门位置
工资等级表（salgrade）
No字段类型描述1gradenumber等级名称2losalnumber此等级的最低工资3hisalnumber此等级的最高工资
奖金表（bonus）
No字段类型描述1enamevarchar2(10)雇员姓名2jobvarchar2(9)雇员工作3salnumber雇员工资4commnumber雇员奖金（佣金）
七、 单行函数
1. 字符函数
接收字符输入返回字符或者数值，dual 是伪表
- 把小写的字符转换成大小的字符:
upper('smith')
select upper('smith') from dual; -- SMITH
- 把大写字符变成小写字符:
lower('SMITH')
select lower('smith') from dual; -- SMITH
2. 数值函数
- 四舍五入函数：
ROUND()默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
select round(12.534) from dual;
select round(12.534,2) from dual;
3.日期函数
Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 数字 = 日期
日期 – 日期 = 数字
- 范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(
sysdate– 入职日期) / 7就是周数
select ename,round((sysdate - hiredate) / 7) from emp;
- 获得两个时间段中的月数：
MONTHS_BETWEEN()范例：查询所有雇员进入公司的月数
select ename,round(MONTHS_BETWEEN(sysdate - hiredate)) from emp;
4.转换函数
TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用 TO_CHAR函数来拆分。拆分时需要使用通配符
年：y, 年是四位使用 yyyy
月：m, 月是两位使用 mm
日：d, 日是两位使用 dd
select empno,ename,
to_char(hiredate,'yyyy') 年,
to_char(hiredate,'mm') 月,
to_char(hiredate,'dd') 日,
from emp;
TO_DATE:日期转换函数
TO_DATE可以把字符串的数据转换成日期类型
select to_date('1985-04-22',yyyy-mm-dd) from dual;
5.通用函数
1. 空值处理 nvl
范例：查询所有的雇员的年薪
select ename,sal*12 comm from emp;
我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null和任何数值计算都是 null，这时我们可以使用 nvl来处理。
select ename,nvl(comm,0),sal * 12 nvl(comm,0) from emp;
2.Decode 函数
该函数类似if....else if...esle
语法：DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值
范例：查询出所有雇员的职位的中文名
select e.ename,
decode(e.ename,
'SMITH', '曹贼',
'ALLEN', '大耳贼',
'WARD', '诸葛小儿',
'无名') "中文名"
from emp e;
3.case when
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select t.empno,
t.ename,
case
when t.job = 'CLERK' then
'业务员'
when t.job = 'MANAGER' then
'经理'
when t.job = 'ANALYST' then
'分析员'
when t.job = 'PRESIDENT' then
'总裁'
when t.job = 'SALESMAN' then
'销售'
else
'无业'
end
from emp t
八、 多行函数（聚合函数）
1.统计记录数 count()
范例：查询出所有员工的记录数
select count(*) from emp; -- 不建议使用 count(*)，可以使用一个具体的列以免影响性能。
select count(ename) from emp;
2.最小值查询 min()
范例：查询出来员工最低工资
select min(sal) from emp;
3.最大值查询 max()
范例：查询出员工的最高工资
select mac(sal) from emp;
4.查询平均值 avg()
范例：查询出员工的平均工资
select avg(sal) from emp;
5.求和函数 sum()
范例：查询出 20 号部门的员工的工资总和
select sum(sal) from emo t where t.deptno = 20;
九、 分组统计
分组统计需要使用 GROUP BY 来分组
语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列 名 1 ASC|DESC，列名 2...ASC|DESC
范例：查询每个部门的人数
select deptno,count(ename) from emp group by deptno
范例：查询出每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
如果我们想查询出来部门编号，和部门下的人数
select deptno,count(ename) from emp;
注意：
- 如果使用分组函数，
SQL只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其 他字段。 - 如果使用分组函数，不使用
GROUP BY只可以查询出来分组函数的值
-- 打好框架
select xx from xx where xx group by xx;
范例：按部门分组，查询出部门名称和部门的员工数量
select d.deptno,d.dname from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.name;
范例：查询出部门人数大于 5 人的部门
分析：需要给 **count(ename)**加条件，此时在本查询中不能使用 where，可以使用 HAVING
select d.deptno,d.dname from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.name
having count(ename) > 5;
范例：查询出部门平均工资大于 2000 的部门
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
十、 多表查询[应用]
使用一张以上的表做查询就是多表查询
语法：SELECTDISTINCT} *列名.. FROM 表名 别名，表名 1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...
范例：查询员工表和部门表
---多表查询中的一些概念
---笛卡尔积
select *
from emp e, dept d;
我们发现产生的记录数是 56 条，我们还会发现 emp表是 14 条，dept表是 14 条，56 正是 emp表和 dept表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是 depno，depno就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的 外键。
---等值连接
select *
from emp e, dept d
where e.deptno=d.deptno;
关联之后我们发现数据条数是 14 条，不在是 56 条。
多表查询我们可以为每一张表起一个别名
范例：查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e , dept d
on e.deptno = d.deptno;
---内连接
select *
from emp e inner join dept d
on e.deptno = d.deptno;
外连接（左右连接）
- 右连接 当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门 下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的
使用( )表示左连接或者右连接，当( )在左边表的关联条件字段上时是左连接，如果是在右 边表的关联条件字段上就是右连接。
查询出所有部门，以及部门下的员工信息。【外连接】
select *
from emp e right join dept d
on e.deptno=d.deptno;
查询所有员工信息，以及员工所属部门
select *
from emp e left join dept d
on e.deptno=d.deptno;
oracle中专用外连接
select *
from emp e, dept d
where e.deptno( ) = d.deptno; // d.deptno显示全了。
子查询
子查询：
在一个查询的内部还包括另一个查询，则此查询称为子查询。Sql的任何位置都可以加入子查询。
范例：查询比 7654 工资高的雇员
分析：查询出 7654 员工的工资是多少，把它作为条件
slect * from emp t1
where t1.sal > (select t.sal from emp t where t.empno = 7654);
子查询在操作中有三类：
单列子查询：返回的结果是一列的一个内容
单行子查询：返回多个列，有可能是一个完整的记录
多行子查询：返回多条记录
查询出员工姓名，员工领导姓名 （从from 后面开始写。员工姓名在哪张表，员工领导姓名在哪张表）自连接：自连接其实就是站在不同的角度把一张表看成多张表。
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;
查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;
oracle中的分页
ROWNUM:表示行号，当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号。行号从1开始，依次递增，不能跳着走。
排序操作会影响rownum的顺序
select rownum, e.* from emp e order by e.sal desc
如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。
select rownum, t.* from(
select rownum, e.* from emp e order by e.sal desc) t;
emp表工资倒叙排列后，每页五条记录，查询第二页。rownum行号不能写上大于一个正数。
select * from(
select rownum rn, tt.* from(
select * from emp order by sal desc
) tt where rownum<11
) where rn>5
oracle 底层
视图
视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表。(视图就是封装了一条复杂查询的语句)。
查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
创建视图【必须有dba权限】
create view v_emp as select ename, job from emp;
---查询视图
select * from v_emp;
---修改视图[不推荐]
update v_emp set job='CLERK' where ename='ALLEN';
commit;
---创建只读视图
create view v_emp1 as select ename, job from emp with read only;
创建只读视图
create view v_emp1 as select ename, job from emp with read only;
视图的作用？
第一：视图可以屏蔽掉一些敏感字段。第二：保证总部和分部数据及时统一。
语法 1：CREATE VIEW 视图名称 AS 子查询
范例：建立一个视图，此视图包括了 20 部门的全部员工信息
create view empvd20 as select * from emp t where t.deptno = 20
视图创建完毕就可以使用视图来查询，查询出来的都是 20 部门的员工
select * from empvd20 t;
语法 2：CREATE OR REPLACE VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖。
create or replace view empvd20 as select * from emp t where t.deptno = 20
视图很少修改，我们可以设置视图为只读。
语法 3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
create or replace view empvd20
as select * from emp t where t.deptno = 20 with read only;
索引
- 索引的概念：索引就是在表的列上构建一个二叉树。达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而 提高数据访问性能。索引有很多种我们主要介绍常用的几种: 创建索引：
- 单列索引
单列索引是基于单个列所建立的索引，比如: CREATE index 索引名 on 表名(列名)
创建单列索引
create index idx_ename on emp(ename);
单列索引触发规则，条件必须是索引列中的原始值。
单行函数，模糊查询，都会影响索引的触发。
select * from emp where ename='SCOTT'
- 复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是 要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
范例：给 person 表的 name 建立索引
create index pname_index on person(name);
范例：给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name, gender);
索引的使用原则：
- 在大表上建立索引才有意义
- 在 where 子句后面或者是连接条件上的字段建立索引
- 表中数据修改频率高时不建议建立索引
- 复合索引中第一列为优先检索列
- 如果要触发复合索引，必须包含有优先检索列中的原始值。
select * from emp where ename='SCOTT' and job='xx';---触发复合索引
select * from emp where ename='SCOTT' or job='xx';---不触发索引
select * from emp where ename='SCOTT';---触发单列索引。
pl/sql 基本语法
什么是 PL/SQL?
PL/SQL（Procedure Language/SQL）
PLSQL是 Oracle对 sql语言的过程化扩展，指在 SQL命令语言中增加了过程处理语句（如分支、循 环等），使 SQL语言具有过程处理能力。
把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合 起来，使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。
pl/sql编程语言主要用来编写存储过程和存储函数等。
声明方法: 赋值操作可以使用:=也可以使用into查询语句赋值
declare
i number(2) := 10;
s varchar2(10) := '小明';
ena emp.ename%type;--- 引用型变量
emprow emp%rowtype;--- 记录型变量
begin
dbms_output.put_line(i); // 输出10
dbms_output.put_line(s); // 输出小明
select ename into ena from emp where empno = 7788;
dbms_output.put_line(ena);
select * into emprow from emp where empno = 7788;
dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
end;
pl/sql中的if判断
- 输入小于18的数字，输出未成年
- 输入大于18小于40的数字，输出中年人
- 输入大于40的数字，输出老年人
declare
i number(3) := ⅈ
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
pl/sql中的loop循环
用三种方式输出1到10是个数字
--------------- while循环 -------------
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i 1;
end loop;
end;
--------------- exit循环 --------------
declare
i number(2) := 1;
begin
loop
exit when i>10;
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;
游标
可以存放多个对象，多行记录。
输出emp表中所有员工的姓名
declare
cursor c1 is select * from emp;
emprow emp%rowtype;
begin
open c1;
loop
fetch c1 into emprow;
exit when c1%notfound;
dbms_output.put_line(emprow.ename);
end loop;
close c1;
end;
给指定部门员工涨工资
declare
cursor c2(eno emp.deptno%type)
is select empno from emp where deptno = eno;
en emp.empno%type;
begin
open c2(10);
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal 100 where empno=en;
commit;
end loop;
close c2;
end;
查询10号部门员工信息
select * from emp where deptno = 10;
存储过程
存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal=sal 100 where empno = eno;
commit;
end;
select * from emp where empno = 7788;
----测试p1
declare
begin
p1(7788);
end;
通过存储函数实现计算指定员工的年薪
- 存储过程和存储函数的参数都不能带长度
- 存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number
is
s number(10);
begin
select sal*12 nvl(comm, 0) into s from emp where empno = eno;
return s;
end;
测试f_yearsal
存储函数在调用的时候，返回值需要接收。
declare
s number(10);
begin
s := f_yearsal(7788);
dbms_output.put_line(s);
end;
out类型参数如何使用
使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
s number(10);
c emp.comm%type;
begin
select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
yearsal := s c;
end;
测试p_yearsal
declare
yearsal number(10);
begin
p_yearsal(7788, yearsal);
dbms_output.put_line(yearsal);
end;
in和out类型参数的区别是什么？
凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。
存储过程和存储函数的区别
- 语法区别：关键字不一样，存储函数比存储过程多了两个return。
- 本质区别：存储函数有返回值，而存储过程没有返回值。如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值， 而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。我们可以使用存储函数有返回值的特性，来自定义函数。而存储过程不能用来自定义函数。
案例需求：查询出员工姓名，员工所在部门名称。
案例准备工作：把scott用户下的dept表复制到当前用户下。
create table dept as select * from scott.dept;
使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
使用存储函数来实现提供一个部门编号，输出一个部门名称。
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno = dno;
return dna;
end;
使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno)
from emp e;
触发器
就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，自动触发，无需调用。
- 语句级触发器：不包含有for each row的触发器。
- 行级触发器：包含有for each row的就是行级触发器。
加for each row是为了使用:old或者:new对象或者一行记录。
语句级触发器
插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare
begin
dbms_output.put_line('一个新员工入职');
end;
触发t1
insert into person values (1, '小红');
commit;
select * from person;
行级别触发器
不能给员工降薪
raise_application_error(-20001~-20999之间, '错误提示信息');
create or replace trigger t2
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20001, '不能给员工降薪');
end if;
end;
触发t2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;
触发器实现主键自增
分析：在用户做插入操作的之前，拿到即将插入的数据，
给该数据中的主键列赋值。
create or replace trigger auid
before
insert
on person
for each row
declare
begin
select s_person.nextval into :new.pid from dual;
end;
查询person表数据
select * from person;
---使用auid实现主键自增
insert into person (pname) values ('a');
commit;
insert into person values (1, 'b');
commit;
到这里就结束啦，下期再见！
