一. 视图和索引(掌握)
1. 视图
概念
1. 什么是视图 : 对sql语句的一种封装,它是一个虚拟的表。
2. 使用场景:对经常需要查询的数据,进行封装。屏蔽一些敏感字段
3. 优点:屏蔽一些敏感字段 , 封装复杂的查询语句, 提高工作效率。
基本语法
- 创建视图
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION ]
[WITH READ ONLY]
REPLACE:删除已有视图,重新创建视图。
FORCE:不管基表是否存在,都会创建视图。
WITH CHECK OPTION:插入或修改的数据行必须满足视图定义的约束。
WITH READ ONLY:只读。
- 删除视图
DROP VIEW view_name
示例
---查询语句创建表
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;
---视图的作用?
---第一:视图可以屏蔽掉一些敏感字段。
---第二:保证总部和分部数据及时统一。
2. 索引(重点)
概念
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:单列索引,复合索引
为什么添加了索引之后,会加快查询速度呢?
图书馆:如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱子里面放卡片,卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多,这个就有点像索引。
索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的.
所以建索引有个原则:如果有一个字段如果不经常查询,就不要去建索引。
语法
- 单列索引:单列索引是基于单个列所建立的索引
CREATE index 索引名 on 表名(列名)
- 复合索引:复合索引是基于两个列或多个列的索引
Create index 索引名 on 表名(列名1,列名2)
- 唯一索引 : 如果某一列的值是唯一不重复的可以使用唯一索引
Create unique index 索引名 on 表名(列名1,列名2)
- 反向键索引 : 如果某一列的数据,是连续的数据,可以使用反向键索引
CREATE index 索引名 on 表名(列名) reverse
- 位图索引 : 如果某一列的数据,可能的值不是很多 (低基数列),可以使用位图索引
CREATE BITMAP index 索引名 on 表名(列名)
一般我们会为查询比较多的列创建索引 .
示例
---索引
--索引的概念:索引就是在表的列上构建一个二叉树
----达到大幅度提高查询效率的目的,但是索引会影响增删改的效率。
---单列索引
---创建单列索引
create index idx_ename on emp(ename);
---单列索引触发规则,条件必须是索引列中的原始值。
---单行函数,模糊查询,都会影响索引的触发。
select * from emp where ename='SCOTT'
---复合索引
---创建复合索引
create index idx_enamejob on emp(ename, job);
---复合索引中第一列为优先检索列
---如果要触发复合索引,必须包含有优先检索列中的原始值。
select * from emp where ename='SCOTT' and job='xx';---触发复合索引
select * from emp where ename='SCOTT' or job='xx';---不触发索引
select * from emp where ename='SCOTT';---触发单列索引。
create index idx_enamejob on emp(ename, job , mgr);
-- 触发索引原则 : 偏左前缀原则
select * from emp where ename='SCOTT' -- 触发索引
select * from emp where ename='SCOTT' and job = 'CLERK' -- 会触发索引
select * from emp where ename='SCOTT' and job = 'CLERK' and mgr = 8960 -- 会触发索引
select * from emp where job = 'CLERK' -- 不会触发索引
select * from emp where job = 'CLERK' and ename='SCOTT' -- 不会触发索引
select * from emp where ename='SCOTT' and mgr = 8960 -- 不会触发索引
二. PL/SQL编程(了解)
1. 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:按职工的职称长工资,总裁涨1000元,经理涨800元,其他人员涨400元。
这样的需求我们就无法使用一条SQL来实现,需要借助其他程序来帮助完成,也可以使用pl/sql。
pl/sql程序语法(熟悉)
[declare
-- 声明变量
]
begin
-- 代码逻辑
[exception
-- 异常处理
]
end;
常量和变量定义(熟悉)
在程序的声明阶段可以来定义常量和变量。
1. 定义普通变量
变量的基本类型就是oracle中的建表时字段的变量如char, varchar2, date, number, boolean, long
定义语法:变量名称 char(15)
;
例如:
declare
s varchar2(10); -- 声明变量
begin
s:='张三丰'; -- 给变量赋值
dbms_output.put_line(s);
end;
2. 引用变量
在变量较少的情况下,定义的变量是某个表的某列字段,可以采用 表名.字段名%type
。
例如:
declare
s varchar2(10); -- 声明变量
ena emp.ename%type; ---引用型变量
begin
s:='张三丰'; -- 给变量赋值
select ename into ena from emp where empno = 7788 ;
dbms_output.put_line(s);
dbms_output.put_line(ena);
end;
3. 行记录型变量
在变量较多的情况下,可以采用纪录型,把表名定义成变量 表名%rowtype。
例如:
declare
s varchar2(10); -- 声明变量
emprow emp%rowtype;---记录型变量
begin
s:='张三丰'; -- 给变量赋值
select * into emprow from emp where empno = 7788;
dbms_output.put_line(emprow.ename || '的工作为:' || emprow.job);
end;
注意:引用类型select into变量赋值和行记录类型select into变量赋值 ,select 查询的结果只能有一条数据
完整示例:
---声明方法
---赋值操作可以使用:=也可以使用into查询语句赋值
declare
i number(2) := 10;
s varchar2(10) := '小明';
ena emp.ename%type;---引用型变量
emprow emp%rowtype;---记录型变量
begin
dbms_output.put_line(i);
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;
条件判断(熟悉)
语法
[外链图片转存失败(img-MDNeIKKt-1565492687411)(assets/markdown-img-paste-20181114235337868.png)]
示例
---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;
循环(熟悉)
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循环
---for循环
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
游标(熟悉)
在写java程序中有集合的概念,那么在pl/sql中也会用到多条记录,这时候我们就要用到游标,游标可以存储查询返回的多条数据。
语法
CURSOR 游标名[ (参数名 数据类型,参数名 数据类型,...)] IS SELECT
使用步骤
游标的使用步骤:
- 打开游标: open c1; (打开游标执行查询)
- 取一行游标的值:fetch c1 into pjob; (取一行到变量中)
- 关闭游标: close c1;(关闭游标释放资源)
- 游标的结束方式 exit when c1%notfound
示例:
- 普通游标
---游标:可以存放多个对象,多行记录。
---输出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); -- 传递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;
2. 存储过程
概念
储过程就是提前已经编译好的一段pl/sql语言,放置在数据库端.可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
基本语法
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称(参数名 in 类型, 参数名 out 类型, 参数名 类型)
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;
-- 如果需要对参数赋值( := select into ), 一般都是用out类型参数 ,其他情况都使用in
示例
---存储过程
----定义给指定员工涨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;
----调用存储过程
declare
begin
p1(7788);
end;
select * from emp where empno = 7788;
输出参数的存储过程
---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来修饰。
3. 存储函数
概念
存储函数也叫自定义函数,可以接收一个或者多个参数,返回一个结果。类似于Java中的方法。
基本语法
CREARE [ OR REPLACE] FUNCTION 函数名(参数名称 参数类型, 参数名称 参数类, ...)
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量 ;
[EXCEPTION
异常处理部分]
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;
4. 存储过程和存储函数的区别
1. 语法区别:关键字不一样,存储函数比存储过程多了两个return。
2. 本质区别:存储函数有返回值,而存储过程没有返回值。
如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数。
即便是存储过程使用了out类型的参数,起本质也不是真的有了返回值,
而是在存储过程内部给out类型参数赋值,在执行完毕后,我们直接拿到输出类型参数的值。
3. 我们可以使用存储函数有返回值的特性,来自定义函数。而存储过程不能用来自定义函数。
示例
----案例需求:查询出员工姓名,员工所在部门名称。
----案例准备工作:把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;
三. 触发器(了解)
1. 概述
触发器,就是制定一个规则,在我们做增删改操作的时候,只要满足该规则,自动触发,无需调用。
触发器可用于
- 数据确认(数据的校验)
- 实施复杂的安全性检查
- 做审计,跟踪表上所做的数据操作等
- 数据的备份和同步
2. 触发器分类
-
语句级触发器:不包含有
for each row
的触发器。 -
行级触发器:包含有
for each row
的就是行级触发器。 加for each row是为了使用:old或者:new对象或者一行记录。
在触发器中触发语句与伪记录变量的值
[外链图片转存失败(img-kXdKfM89-1565492687414)(assets/markdown-img-paste-20181115002614169.png)]
3. 基本语法
CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
变量声明部分
begin
PLSQL 块
End ;
4. 示例
---语句级触发器
----插入一条记录,输出一个新员工入职
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;
四. java代码调用存储过程(了解)
1. 创建maven项目
2. 导入依赖坐标
<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>
3. 编写jdbc代码连接oracle数据库
胖连接,通过pl/SQL
瘦连接,
Statment
Parpare
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();
}
}