Oracle数据库学习(2)
写在前面
本文接Oracle数据库学习(1)，上次主要学习了Oracle数据库里的一些基本操作，接下来主要学习一下pl/sql编程语言，并学习使用java来调用存储过程和存储函数。
oracle数据库对象
视图
视图，概念和SqlServer里的视图概念很像，基本是一套概念。这里直接上sql语句：
-- 视图
-- 视图的概念：提供查询的一个窗口，所有数据来自于原表
-- 查询语句创建表
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_empl as select ename job from emp with read only;
-- 视图的作用？
-- 第一，视图可以屏蔽掉一些敏感字段。
-- 第二，保证总部和分部数据及时统一。
可以看到，总体的sql语句也和sqlServer的相差不太多，没什么好说的。作用也十分类似。
索引
所谓的索引，指的就是类似我们在windows上常用的文件索引，通过二叉树来提高查询效率，但由于每次增删改都要重新创建，所以增删改的效率会相应变低。下面来看看sql语句:
-- 索引
-- 索引的概念：在表的列上构建一个二叉树
-- 达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。
-- 单列索引
-- 创建单列索引
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' -- 触发单列索引
pl/sql编程语言
这是一种新的编程语言，可以让sql具有过程化的特性。这点其实和SqlServer家的t-sql有些类似，都具有类似的功能。
关于这门新语言的语法，这里就不再赘述了。这里主要说一下存储过程和存储函数的创建：
-- 存储过程
-- 提前编译好的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;
注释写的很详细，就不再多说了。关于oracle数据库的触发器等，和SqlServer的定义和使用都很类似，这里也就不再说了
使用jdbc来调用存储过程/存储函数
数据库的学习告一段落后，我们要使用java语言来尝试调用存储过程/存储函数，首先需要导jar包。我这里服务器使用的是oracle10g，对应的jar包是ojdbc14.jar。导入好jar包后就可以写junit了:
/**
* java调用存储过程
* @throws Exception
*/
@Test
public void javaCallProcedure() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@#:liuge", "liuge", "abc456");
//得到预编译的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调用存储函数
* @throws Exception
*/
@Test
public void javaCallFunction() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@#:liuge", "liuge", "abc456");
//得到预编译的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();
}
只要遵守对应的语法即可成功调用。具体的jdbc连接过程和mysql数据库类似，这里由于隐私问题我把ip地址改成了#,使用时可以用自己的数据库ip
总结
总的来说，oracle数据库比mysql数据库多了很多内容，更加的复杂和冗余。对于一般的小项目来说很多东西没有必要使用，估计也是因为此才产生了mysql吧。但通过oracle和sqlserver的对比学习，还是收获了不少新东西的。
