一、 oracle介绍[了解]
ORACLE 数据库系统是美国ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组
软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S体系结构的数据库之一。比如
SilverStream 就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据
库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，
它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只
要在一种机型上学习了 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 itheima -- 保存文件所在的目录 datafile 'd:\itheima.dbf' -- 创建表空间的大小 size 100m -- 自动扩展大小 autoextend on -- 每次扩展10兆 next 10;
itcast 为表空间名称
datafile 指定表空间对应的数据文件
size 后定义的是表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 后指定的是一次自动增长的大小。
四、 删除表空间[理解]
-- 删除表空间 -- sql语句 drop tablespace 表空间的名称 drop tablespace itheima;
如果你不删除表空间，无法在远程的服务器的目录中将它删除！
五、 用户[理解]
6. 创建用户
-- 创建用户 create user itheima -- 登录的用户密码 identified by itheima -- 指定用户出生在哪个地方，哪个表空间 default tablespace itheima;
identified by 后边是用户的密码
default tablespace 后边是表空间名称
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户
下的。
7. 用户赋权限
新创建的用户没有任何权限，登陆后会提示
Oracle 中已存在三个重要的角色：connect 角色，resource角色，dba角色。
CONNECT 角色： --是授予最终用户的典型权利，最基本的
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
RESOURCE 角色： --是授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
DBA角色：拥有全部特权，是系统最高权限，只有 DBA才可以创建数据库结构，并且系统
权限也需要DBA授出，且 DBA用户可以操作全体用户的任意基表，包括删除
-- 必须要给用户授权，否则无法登录 -- Oracle数据库中常用角色 connect --连接角色，基本角色 resource --开发者角色 dba --超级管理员角色 -- 给itheima用户授予dba角色 grant dba to itheima; -- 切换到itheima用户下 -- 点击session（会话）下的所有用户注销，然后点击登录
进入 system 用户下给用户赋予 dba 权限，否则无法正常登陆
六、 Oracle数据类型[应用]
七、 表的管理[应用]
1.1 建表
语法： 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 表名
--- 删除表结构 drop table persion;
3.1 表的修改
在 sql中使用 alter 可以修改表
添加语法：ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
修改语法：ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2
-- 修改表结构 --- 添加一列 alter table persion add gender number(1); --- 修改列的类型 alter table persion modify gender char(1); --- 修改列的名称 alter table persion rename column gender to sex; --- 删除一列 alter table persion drop column sex;
4.1 数据库表数据的更新
1. INSERT（增加）
标准写法：
INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...) 简单写法（不建议） INSERT INTO 表名 VALUES(值 1，值 2，...)
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null
---- 添加一条记录 insert into persion (pid,pname) values (1,'小明'); --- 凡是增删改查都要开启事务提交，查询就不需要开启事务 commit;
2. UPDATE（修改）
全部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，.... 局部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；
---- 修改一条记录 update persion set pname = '小马' where pid = 1; commit;
3. DELETE（删除）
语法 : DELETE FROM 表名 WHERE 删除条件;
----- 三个删除 --- 删除表中全部记录 delete from persion; --- 删除表结构 drop table persion; -- 先删除表，再次创建表。效果等同于删除表中的全部记录 -- 在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高 -- 索引可以提供查询效率，但是会影响增删改效率 truncate table persion;
在删除语句中如果不指定删除条件的话就会删除所有的数据
因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数
据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据
库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
5.1 序列
在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,
则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：
CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [{CYCLE|NOCYCLE}] [{CACHE n|NOCACHE}];
---- 序列不真的属于任何一张表，但是可以逻辑和表做绑定。 ---- 序列：默认从1开始，依次递增，主要用来给主键赋值使用。 ---- dual: 虚表，只是为了补全语法，没有任何意义。 create sequence s_persion; select s_persion.nextval from dual;--- s_persion.nextval:当前序列递增 select s_persion.currval from dual;--- s_persion.currval:返回当前的索引 ---- 添加一条记录，采用序列化的方法是。让主键递增 insert into persion (pid,pname) values (s_persion.nextval,'小王'); commit;
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪
一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
八、 Scott用户下的表结构[了解]
---- scott用户，密码tiger。 --- 解锁scott用户 alter user scott account unlock; -- 解锁scott用户的密码【此句也可以用来重置密码】 alter user scott identified by tiger; --- 切换到scott用户下
九、 单行函数[应用]
1. 字符函数
接收字符输入返回字符或者数值，dual是伪表
--- 字符函数 select upper('yes') from dual;---YES小写变大写 select lower('YES') from dual;---yes大写变小写
2. 数值函数
2.1、四舍五入函数：
ROUND() 默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数。
select round(26.18,1) from dual;---四舍五入26.2,后面的参数表示保留的小数
select trunc(56.16,-1) from dual;---直接截取，不在看后面位数的是否大于5
select mod(10,3) from dual; ---求余数
3.日期函数
Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字
---查询出emp表中所有员工入职距离现在几天。 select sysdate-e.hiredate from emp e; --- 算出明天的此刻 select sysdate + 1 from dual; --- 查询出emp表中所有员工入职距离现在几月 select months_between(sysdate,e.hiredate) from emp e; --- 查询出emp表中所有员工入职距离现在几年 select months_between(sysdate,e.hiredate) / 12 from emp e; --- 查询出emp表中所有员工入职距离现在几周 select round((sysdate-e.hiredate)) / 7 from emp e;
4.转换函数
1. TO_CHAR:字符串转换函数
范例：
查询所有的雇员将将年月日分开，此时可以使用TO_CHAR 函数来拆分 拆分时需要使用通配符
年：y, 年是四位使用 yyyy
月：m, 月是两位使用 mm
日：d, 日是两位使用 dd
可以使用 fm 去掉前导零
--- 日期转字符串 select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
2. TO_DATE:日期转换函数
TO_DATE可以把字符串的数据转换成日期类型
--- 字符串转日期 select to_date('2020-10-13 14:8:29','fm yyyy-mm-dd hh24:mi:ss') from dual;
5.通用函数
1．空值处理nvl
范例：查询所有的雇员的年薪
--- 算出emp表中所有员工的年薪 ---- 奖金里面有null值，如果null值和任意数字做算数运算，结果都是null select e.sal * 12 + nvl(e.comm,0) from emp e;
我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null和任何数值计算都是
null，这时我们可以使用 nvl来处理。
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 的默认值
3、条件表达式
---- 条件表达式 ---- 条件表达式的通用写法，MySQL和Oracle都可以写 --- 给emp表中员工起中方名 select e.ename, case e.ename when 'SMITH'then '草皮' when 'ALLEN' then '大耳皮' when 'WARD' then '诸葛小儿' else '无名' end from emp e; --- 判断emp表中员工工资，如果高于3000显示高收入 ---如果高于1500低于3000显示中等收入 ---其余显示低收入 select e.sal, case when e.sal > 3000 then '高收入' when e.sal > 1500 then '中等收入' else '低收入' end from emp e; ---- Oracle中除了起别名，都用单引号 ---Oracle专用条件表达式 select e.ename, decode(e.ename, 'SMITH','草皮', 'ALLEN','大耳皮', 'WARD','诸葛小儿', '无名') 中文名 from emp e;
十、 多行函数（聚合函数）
--- 多行函数【聚合函数】:作用于多行，返回一个值. select count(1) from emp; --- 查询表中数据的总数量 select sum(sal) from emp; --- 查询工资总和 select max(sal) from emp; --- 最大工资 select min(sal) from emp; --- 最低工资 select avg(sal) from emp; --- 平均工资
十一、 分组统计
分组统计需要使用 GROUP BY来分组
语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列 名 1 ASC|DESC，列名 2...ASC|DESC
---- 分组查询 ---- 查询出每个部门的平均工资 ---- 注意分组查询中， 出现在group by后面的原始列，才能出现在select后面 ---- 没有出现在group by后面的列，想在select后面，必须加上聚合函数 ---- 聚合函数有一个特性，可以把多行记录变成一个值。 select e.deptno ,avg(e.sal) --,e.ename from emp e group by e.deptno; ---- 查询出平均工资高于2000的部门信息 select e.deptno ,avg(e.sal) from emp e group by e.deptno having avg(e.sal) > 2000; ---- 所有条件都不能使用别名来判断，原因是，执行的先后顺序 ---- 比如下面条件语句也不能使用别名当条件 select ename,sal s from emp where sal > 1500; ---- 查询出每个部门工资高于800的员工的平均工资 select e.deptno,avg(e.sal) 平均工资 from emp e where e.sal > 800 group by e.deptno; ---- where是过滤分组前的数据，having是过滤分组后的数据 ---- 表现形式：where必须在group by之前，having是在group by之后。 ---- 查询出每个部门工资高于800的员工的平均工资 ---- 然后再查询出平均工资高于2000的部门 select e.deptno,avg(e.sal) 平均工资 from emp e where e.sal > 800 group by e.deptno having avg(e.sal) > 2000;
十二、 多表查询[应用]
1.1.4 1.多表连接基本查询
使用一张以上的表做查询就是多表查询
语法： SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名 1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}
--- 多表查询中的一些概念 --- 笛卡尔积 select * from emp e,dept d;
我们发现产生的记录数是 56条，我们还会发现emp 表是 14 条，dept 表是 4条，56 正是emp
表和 dept表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造
成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可
以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的
外键。
等值连接
---- 等值连接 select * from emp e,dept d where e.deptno = d.deptno;
自连接:自连接其实就是站在不同的角度把一张表看成多张表。
----- 自连接:自连接其实就是站在不同的角度把一张表看成多张表。 ----- 查询出员工姓名，员工领导姓名 select e1.ename 员工, e2.ename 领导 from emp e1,emp e2 where e1.mgr = e2.empno; ----- 查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称 select e1.empno 员工姓名,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;
范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e.empno, e.ename, decode(s.grade, 1,'一级', 2,'二级', 3,'三级', 4,'四级', 5,'五级') grade, d.dname, e1.empno, e1.ename, decode(s1.grade, 1,'一级', 2,'二级', 3,'三级', 4,'四级', 5,'五级') grade from emp e, emp e1, dept d, salgrade s, salgrade s1 where e.mgr = e1.empno and e.deptno = d.deptno and e.sal between s.losal and s.hisal and e1.sal between s1.losal and s1.hisal
1.1.5 2.外连接（左右连接）
1. 右连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门
下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的
---- 内连接 select * from emp e inner join dept d on e.deptno = d.deptno; ---- 查询出所有部门，以及部门下的员工信息。【外连接】 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中专用外连接
---- Oracle中专用外连接 ---- 如果要查询找部门的所有信息，就在e.deptno(+)中加入一个+号 select * from emp e,dept d where e.deptno(+) = d.deptno; ---- 如果要查询找员工的所有信息，就在d.deptno(+)中加入一个+号 select * from emp e,dept d where e.deptno = d.deptno(+);
使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右
边表的关联条件字段上就是右连接
十三、 子查询[应用]
----- 子查询 ----- 子查询返回一个值 ----- 查询出工资和SCOTT一样的员工信息 select sal from emp where ename = 'SCOTT'; select * from emp where sal = (select sal from emp where ename = 'SCOTT'); ----- 子查询返回一个集合 ----- 查询出工资和10号部门任意员工一样的员工信息 select sal from emp where deptno = 10; select * from emp where sal in (select sal from emp where deptno = 10); ----- 子查询返回一张表 ----- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在的部门名称 ----- 1、先查询出每个部门最低工资 select deptno , min(sal) from emp group by deptno; ----- 2、三表联查，得到最终结果 。 select t.deptno ,t.msal ,e.ename,d.dname from (select deptn o , min(sal) msal from emp group by deptno) t,emp e,dept d where t.deptno = e.deptno and t.msal = e.sal and e.deptno = d.deptno;
十四、 Rownum与分页查询[应用]
ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
----- Oracle中的分页 ----- rownum行号：当我们做select操作的时候 ----- 每查询出一行记录，就会在该行加上一个行号 ----- 行号从1开始，依次递增，不能跳着走 -----emp表工资倒叙排序后，每页五条记录，查询第二页 ----- 排序操作会影响rownum的顺序 select * from emp e where rownum < 4 order by e.sal desc; select rownum , e.* from emp e order by e.sal desc; ---- 解决办法 ---- 如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询 select rownum , t.* from ( select e.* from emp e order by e.sal desc )t; ---- emp表工资倒叙排列后，每页5条记录，查询第二页。 ---- 因为rownum行号是从1开始的，rownum行号不能写上大于一个正数 select rownum , t.* from ( select e.* from emp e order by e.sal desc )t where rownum < 11 and rownum > 5; ----- 解决办法 select * from( select rownum rn , t.* from ( select e.* from emp e order by e.sal desc )t where rownum < 11 ) tt where rn > 5; ----- 第二种写法 select * from(select rownum r ,emp.* from emp) b where b.r > 5 and b.r < 11;
十五、视图[应用]
视图就是封装了一条复杂查询的语句。
语法 1.：CREATE VIEW 视图名称 AS 子查询
----- 视图 ----- 视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表 ----- 查询语句创建表 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;
我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改
视图。
我们可以设置视图为只读。
语法 3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
---- 修改视图【不推荐】 update v_emp set job = 'CLERK' where ename = 'ALLEN'; commit; ---- 创建只读视图 create view v_emp1 as select ename,job from emp with read only;
---- 视图的作用？ ---- 第一：视图可以屏蔽掉一些敏感字段。 ---- 第二：保证总部和分部数据及时统一。
十六、索引[应用]
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而
提高数据访问性能。索引有很多种我们主要介绍常用的几种:
为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，
箱 子里面放卡片，卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多，
这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人
专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有
个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成
我们的索引，就知道为什么索引会快，为什么会有开销。
创建索引的语法：
1． 单列索引
单列索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
------ 索引 ------ 索引的概念：索引就是在表的列上构建一个二叉树 ------ 达到大幅度提高查询效率的目的，但是索引会影响增删改查的效率。 --- 单列索引 ---- 创建单列索引 create index idx_ename on emp(ename); ---- 单列索引触发规则，条件必须是索引列中的原始值。 ---- 单行函数，模糊查询，都会影响索引的触发 select * from emp where ename = 'SCOTT';
2． 复合索引
复合索引是基于两个列或多个列的索引。
在同一张表上可以有多个索引，但是 要求列的组合必须不同,比如：
--- 复合索引 ---- 创建复合索引 create index idx_enamejob on emp(ename,job); ---- 复合索引中的第一列为优先检索列 ---- 如果要触发复合索引，必须包含优先检索列中的原始值. select * from emp where ename = 'SCOOT' and job = 'xx';--- 触发复合索引 select * from emp where ename = 'SCOOT'; --- 触发单列索引 select * from emp where ename = 'SCOOT' or job = 'xx'; --- 不触发索引
索引的使用原则：
在大表上建立索引才有意义
在 where子句后面或者是连接条件上的字段建立索引
表中数据修改频率高时不建议建立索引
十七、pl/sql基本语法[了解]
什么是 PL/SQL?
PL/SQL（Procedure Language/SQL）
PLSQL 是 Oracle 对sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循
环等），使 SQL语言具有过程处理能力。把SQL 语言的数据操纵能力与过程语言的数据处理能力结合
起来，使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。
范例 1：为职工涨工资，每人涨 10％的工资。
update emp set sal=sal*1.1
范例 2：例 2: 按职工的职称长工资,总裁涨 1000元,经理涨800 元，其他人员涨 400 元。
这样的需求我们就无法使用一条 SQL来实现，需要借助其他程序来帮助完成，也可以使用 pl/sql。
1.1.1 1.pl/sql程序语法
程序语法：
declare 说明部分 （变量说明，游标申明，例外说明 〕 begin 语句序列 （DML 语句〕… exception 例外处理语句 End;
----pl/sql编程语言 ----pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。 ----pl/sql编程语言比一般的过程化编程语言，更加灵活高效。 ----pl/sql编程语言主要用来编写存储过程和存储函数等。 --- 声明方法 --- 赋值操作可以使用:= 也可以使用into查询语句赋值 declare i number(2) := 10; s varchar2(10) := '小明'; --- 获取sql里面的表字段的数据给它赋值 --- 引用型变量 ename emp.ename%type; --- 这样相当于给了字段里的数据类型给了它 --- 存储表中的一行数据 --- 记录型变量 emprow emp%rowtype; begin dbms_output.put_line(i); dbms_output.put_line(s); --- 采取select语句的方式给它赋值 into 变量名 select ename into ename from emp where empno = 7788; dbms_output.put_line(ename); select * into emprow from emp where empno = 7788; dbms_output.put_line(emprow.ename || '的工作为:'|| emprow.job); end;
1.1.2 2.常量和变量定义
在程序的声明阶段可以来定义常量和变量。
变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number, boolean, long
定义语法： varl char(15); Psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句。
常量定义： married constant boolean:=true
引用变量 Myname emp.ename%type;
引用型变量，即my_name 的类型与emp 表中 ename 列的类型一样 在 sql中使用 into 来赋值
记录型变量 Emprec emp%rowtype 记录变量分量的引用 emp_rec.ename:='ADAMS';
1.1.3 3. if 分支
语法 1： IF 条件 THEN 语句 1; 语句 2; END IF; 语法 2： IF 条件 THEN 语句序列 1； ELSE 语句序列 2； END IF； 语法 3： IF 条件 THEN 语句; ELSIF 语句 THEN 语句; ELSE 语句; END IF;
--- 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;
1.1.4 4.LOOP 循环语句
语法 1： WHILE total <= 25000 LOOP total : = total + salary; END LOOP; 语法 2： Loop EXIT [when 条件]; End loop 语法 3： FOR I IN 1 . . 3 LOOP 语句序列 ; END LOOP ;
--- 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;
1.1.5 5.游标 Cursor
在写 java 程序中有集合的概念，那么在 pl/sql 中也会用到多条记录，这时候我们就要用到游标， 游标可以存储查询返回的多条数据。
语法： CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
--- 游标：可以存放多个对象，多行记录。 --- 输出emp表中所有员工的姓名 declare cursor cl is select * from emp; emprow emp%rowtype; begin open cl; loop fetch cl into emprow; exit when cl%notfound; dbms_output.put_line(emprow.ename); end loop; close cl; 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;
十八、存储过程[理解]
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，
经 编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来
执
行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存 储过程。
创建存储过程语法： create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] AS begin PLSQL子程序体； End; 或者 create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] is begin PLSQL子程序体； End 过程名;
--- 存储过程 --- 存储过程：存储过程就是提前已经编译好的一段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; end; ---- 查询7788员工的工资 select sal from emp where empno = 7788; ----- 测试p1 declare begin p1(7788); end;
十九、存储函数[理解]
create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is 结果变量 数据类型; begin return(结果变量); 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类型参数如何使用 ---- 使用存储过程来算年薪 ---yearsal out number这是输出类型的参数 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;
存储过程和存储函数的区别
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用out 参数，在过程和函数中实 现返回多个值。
--- 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;
二十、触发器[理解]
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。
每当一个特定的数据操作语句 (Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。
---- 触发器，就是制定一个规则，在我们做增删改查操作的时候， ---- 只要满足该规则，自动触发，无需调用。 ---- 语句级触发器：不包含for、each、row的就是语句级触发器 ---- 行级触发器：包含有for、each、row的就是行级触发器。 ----------- 加for、each、row是为了使用：old或者：new对象或者一行记录。 ----- 语句级触发器 ----- 插入一条记录，输出一个新员工入职 create or replace trigger t1 after insert on persion declare begin dbms_output.put_line('一个新员工入职'); end; ---- 触发器 insert into persion values(s_persion.nextval,'小红'); commit; select * from persion; ----- 行级触发器 ----- 不能给员工降薪 ----- 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; ---- 触发2 update emp set sal = sal - 1 where empno = 7788;
二十一、Java程序调用存储过程[应用]
1.1.6 1.java 连接 oracle 的 jar 包
1.1.7 2.数据库连接字符串
String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl"; String username="scott"; String password="tiger";
测试代码：
1.1.8 3.实现过程的调用
1.1.8.1 1.调用过程
1.1.8.1.1 1.过程定义
1.1.8.1.2 2.过程调用
package com.itheima.oracle; import oracle.jdbc.OracleTypes; import org.junit.Test; import java.sql.*; public class OracleDemo { @Test public void javaCallOracle() throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); //得到预编译的Statement对象 PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?"); //给参数赋值 pstm.setObject(1, 7788); //执行数据库查询操作 ResultSet rs = pstm.executeQuery(); //输出结果 while(rs.next()){ System.out.println(rs.getString("ename")); } //释放资源 rs.close(); pstm.close(); connection.close(); } /** * java调用存储过程 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用 * @throws Exception */ @Test public void javaCallProcedure() throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); //得到预编译的Statement对象 CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}"); //给参数赋值 pstm.setObject(1, 7788); pstm.registerOutParameter(2, OracleTypes.NUMBER); //执行数据库查询操作 pstm.execute(); //输出结果[第二个参数] System.out.println(pstm.getObject(2)); //释放资源 pstm.close(); connection.close(); } /** * java调用存储函数 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用 * @throws Exception */ @Test public void javaCallFunction() throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); //得到预编译的Statement对象 CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}"); //给参数赋值 pstm.setObject(2, 7788); pstm.registerOutParameter(1, OracleTypes.NUMBER); //执行数据库查询操作 pstm.execute(); //输出结果[第一个参数] System.out.println(pstm.getObject(1)); //释放资源 pstm.close(); connection.close(); } }
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.itheima</groupId> <artifactId>jdbc_oracle</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.4.0</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> <scope>test</scope> </dependency> </dependencies> </project>
