Oracle数据库之——视图、索引、PL/SQL语法、存储过程、存储函数、触发器、Java调用存储过程
一、视图
视图就是封装了一条复杂查询的语句。
语法
语法1：
CREATE VIEW 视图名称 AS 子查询
例子：建立一个视图，此视图包括了 20 部门的全部员工信息create view empvd20 as select * from emp t where t.deptno = 20
语法2：
如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖。CREATE OR REPLACE VIEW 视图名称 AS 子查询
语法3：
如果修改视图但是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。
CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
例子：create or replace view empvd20 as select * from emp t where t.deptno = 20 with readonly
—视图的作用？
- 第一：视图可以屏蔽掉一些敏感字段。
- 第二：保证总部和分部数据及时统一。
二、索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。
索引的概念：索引就是在表的列上构建一个二叉树
达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。
为什么添加了索引之后，会加快查询速度呢？
举个例子：图书馆
如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。
索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的。
所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。
现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。
单例索引
单列索引是基于单个列所建立的索引CREATE index 索引名 on 表名(列名)
例子：create index idx_ename on emp(ename);
单列索引触发规则：条件必须是索引列中的原始值。
单行函数，模糊查询，都会影响索引的触发
复合索引
创建复合索引：create index idx_enamejob on emp(ename, job);
复合索引中第一列为优先检索列
如果要触发复合索引，必须包含有优先检索列中的原始值。
1 | select * from emp where ename='SCOTT' and job='xx';---触发复合索引 |
索引的使用原则：
- 在大表上建立索引才有意义
- 在 where 子句后面或者是连接条件上的字段建立索引
- 表中数据修改频率高时不建议建立索引
三、pl/sql 基本语法
什么是 PL/SQL?
PL/SQL（Procedure Language/SQL）
PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。
把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
pl/sql编程语言主要用来编写存储过程和存储函数等。
程序语法：
1 | declare |
varl char(15);
Psal number(9,2);
1 |
|
declare
emprec emp.ename%type;
begin
select t.ename into emprec from emp t where t.empno = 7369;
dbms_output.put_line(emprec);
end;
1 |
|
emp_rec.ename:=’ADAMS’;
declare
p emp%rowtype;
begin
select * into p from emp t where t.empno = 7369;
dbms_output.put_line(p.ename || ‘ ‘ || p.sal);
end;
1 |
|
IF 条件 THEN
语句 1;
END IF;
1 | - 语法 2： |
IF 条件 THEN 语句序列 1；
ELSE 语句序列 2；
END IF；
1 | - 语法 3： |
IF 条件 THEN 语句;
ELSIF 语句 THEN 语句;
ELSE 语句;
END IF;
1 |
|
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
1 | ### exit循环 |
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
1 | ### for循环 |
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
1 |
|
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;
1 |
|
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;
1 |
|
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL 子程序体；
End;
或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL 子程序体；
End 过程名;
1 |
|
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal=sal+100 where empno = eno;
commit;
end;
—-测试p1
declare
begin
p1(7788);
end;
1 |
|
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;
1 |
create or replace function 函数名(Name in type, Name in type, …) return 数据类型 is 结果变量 数据类型;
begin
return(结果变量);
end 函数名;
1 |
|
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;
1 |
|
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
1 |
|
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 |
|
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;
1 |
|
—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;
1 |
|
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;
1 |
|
String driver=”oracle.jdbc.OracleDriver”;
String url=”jdbc:oracle:thin:@192.168.56.10:1521:orcl”;
String username=”scott”;
String password=”tiger”;
1 |
|
–统计年薪的过程
create or replace procedure proc_countyearsal(eno in number,esal out number)
as
begin
select sal*12+nvl(comm,0) into esal from emp where empno=eno;
end;
–调用
declare
esal number;
begin
proc_countyearsal(7839,esal);
dbms_output.put_line(esal);
end;
1 |
|
@Test
public void testProcedure01(){
String driver=”oracle.jdbc.OracleDriver”;
String url=”jdbc:oracle:thin:@192.168.56.10:1521:orcl”;
String username=”scott”;
String password=”tiger”;
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");
callSt.setInt(1, 7839);
callSt.registerOutParameter(2, OracleTypes.NUMBER);
callSt.execute();
System.out.println(callSt.getObject(2));
} catch (Exception e) {
e.printStackTrace();
}
}
```