基于前两节文章(点击打开链接),对oracle中一般认识(这里是说与mysql作比较),这里会对oracle特殊部分做一些详细的了解:oracle的plsql的程序设计、oracle的存储过程、存储函数、触发器做一些了解;
一、plsql的程序设计
(一)、使用oracle的plsql的作用:由于是使用oracle数据库自己本身提供的程序,所以操作起来比其他外部的程序要快,效率高一些;
(二)、对oracle数据库的认识
(三)、oracle的plsql的基础语法
1、plsql中的结构
例如:helloWord
set serveroutput on
declare
-- 说明部分
begin
--程序
dbms_output.put_line('Hello World');
end;
/
2、plsql中的变量定义
eg:
引用型变量
--查询7839的姓名和薪水
set serveroutput on
declare
--定义变量保存姓名和薪水
--pename varchar2(20);
--psal number;
pename emp.ename%type;
psal emp.sal%type;
begin
--得到姓名和薪水
select ename,sal into pename,psal from emp where empno=7839;
dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
记录型变量
--查询7839的姓名和薪水
set serveroutput on
declare
--定义记录型变量:代表一行
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
eg:
--判断用户从键盘上输入的数字
set serveroutput on
--接收键盘输入
--num 是一个地址值,在该地址上保存了输入的值
accept num prompt '请输入一个数字';
declare
--定义变量保存输入的数字
pnum number := #
begin
if pnum = 0 then dbms_output.put_line('您输入的是0');
elsif pnum = 1 then dbms_output.put_line('您输入的是1');
elsif pnum = 2 then dbms_output.put_line('您输入的是2');
else dbms_output.put_line('其他数字');
end if;
end;
/
4、plsql中的循环
--打印1~10
set serveroutput on
declare
pnum number := 1;
begin
loop
--退出条件
exit when pnum > 10;
dbms_output.put_line(pnum);
--加一
pnum := pnum + 1;
end loop;
end;
/
5、plsql中使用 光标 来接收一个集合的数据 把接收到的集合数据存储在光标中
/*
1. 光标属性
%isopen %rowcount(影响的行数)
%found %notfound
*/
不带参数的光标示例
--查询并打印员工的姓名和薪水
/*
1. 光标属性
%isopen %rowcount(影响的行数)
%found %notfound
*/
set serveroutput on
declare
--定义光标
cursor cemp is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
open cemp;
loop
--取一条记录
fetch cemp into pename,psal;
--退出条件
--exit when 没有取到记录;
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close cemp;
end;
/
--查询某个部门的员工姓名
set serveroutput on
declare
--定义光标保存某个部门的员工姓名
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
open cemp(20);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
最后,来一个总的示例,给emp表的 员工涨工资:
--涨工资,总裁1000 经理800 其他400
set serveroutput on
declare
--alter table "SCOTT"."EMP" rename column "JOB" to empjob
cursor cemp is select empno,empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%type;
begin
rollback;
open cemp;
loop
--取一条记录
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断职位
if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
close cemp;
--why? --> ACID
commit;
dbms_output.put_line('完成');
end;
/
6、plsql中的例外,也就是异常
6.1、例外提供了程序的健壮性和容错性
6.2、oracle的例外有两种方式,一种是系统本身提供的、另一种是自己可以定义的类型
eg:
系统提供的异常
--被0除
set serveroutput on
declare
pnum number;
begin
pnum := 1/0;
exception
when zero_divide then dbms_output.put_line('1:0不能做被除数');
dbms_output.put_line('2:0不能做被除数');
when value_error then dbms_output.put_line('算术或者转换错误');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外');
end;
/
自定义的例外
--查询50号部门的员工姓名
set serveroutput on
declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
open cemp;
--取第一条记录
fetch cemp into pename;
if cemp%notfound then
--抛出例外
raise no_emp_found;
end if;
--进程pmon (process monitor)
close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外');
end;
/
二、oracle 的存储过程和存储函数
1、说明,存储过程、存储函数、触发器也都是oracle的数据库对象;
(一)、存储过程的结构
简单的示例:
--打印Hello World
/*
调用存储过程
1. exec sayhelloworld();
2. begin
sayhelloworld();
sayhelloworld();
end;
/
*/
create or replace procedure sayhelloworld
as
--说明部分
begin
dbms_output.put_line('Hello World');
end;
/
--给指定员工涨100,并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
as
--说明部分
psal emp.sal%type;
begin
--得到涨前的薪水
select sal into psal from emp where empno=eno;
update emp set sal=sal+100 where empno=eno;
--要不要commit?
dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100));
end;
/
(二)、存储函数的结构
eg:
--查询某个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
as
--月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
/
--查询某个员工的姓名 月薪 职位
/*
思考:
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中的所有员工信息 ---> 返回集合
*/
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
(四)、使用包和包体来使存储过程out一光标
2. 查询某个部门中的所有员工信息 ---> 返回集合
包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
(五)、使用jdbc调用存储过程和存储函数
package demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
private static String user = "scott";
private static String password = "tiger";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/*
* 运行java程序:
* java -Xms100M -Xmx200M HelloWorld
*
* 技术方向:
* 1. 性能调优
* 2. 故障诊断:ThreadDump
* window: ctrl+break
* linux: kill -3 pid
*/
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;// Java GC
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import demo.util.JDBCUtils;
public class TestOracle {
/*
* create or replace procedure queryEmpInfo(eno in number, pename out
* varchar2, psal out number, pjob out varchar2)
*/
@Test
public void testProcedure() {
// {call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call queryEmpInfo(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 对于in参数,赋值
call.setInt(1, 7839);
// 对于out参数,申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
// 执行
call.execute();
// 取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name + "\t" + sal + "\t" + job);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
/*
* create or replace function queryEmpIncome(eno in number) return number
*/
@Test
public void testFunction() {
// {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{?=call queryEmpIncome(?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 对于out参数,申明
call.registerOutParameter(1, OracleTypes.NUMBER);
// 对于in参数,赋值
call.setInt(2, 7839);
// 执行
call.execute();
// 取出结果
double income = call.getDouble(1);
System.out.println(income);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
@Test
public void testCursor() {
// {call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 对于in参数,赋值
call.setInt(1, 20);
// 对于out参数,申明
call.registerOutParameter(2, OracleTypes.CURSOR);
// 执行
call.execute();
// 取出结果
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(name+"\t"+sal);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, rs);
}
}
}
三、oracle的触发器
1、说明:触发器也是oracle的一个数据库对象
2、触发器的创建以及例子
一个简单的触发器的例子:
--每当成功插入新员工后,自动打印“成功插入新员工”
create trigger saynewemp
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新员工');
end;
/
利用触发器实施安全性的检查
/*
复杂的安全性检查
禁止在非工作时间插入新员工
1. 周末:to_char(sysdate,'day') in ('星期六','星期日')
2. 上班前 下班后:to_number(to_char(sysdate,'hh24')) not between 9 and 18
*/
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期日') or
to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
--禁止插入
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
/
利用触发器实施数据的确认
/*
数据确认
涨后的薪水不能少于涨前的薪水
*/
create or replace trigger checksalary
before update
on emp
for each row
begin
--if 涨后的薪水 < 涨前的薪水 then
if :new.sal < :old.sal then
raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||' 涨后:'||:new.sal);
end if;
end;
/