import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;
import org.junit.Test;
import demo.utils.JDBCUtills;
/*
* Statement < PreparedStatement < CallableStatement
*/
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(??/)}
String sql = "{call queryEmpInfo(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try{
conn = JDBCUtills.getConnection();
call = conn.prepareCall(sql);
//Set value to param
call.setInt(1, 7839);
//declare out parameter
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//run
call.execute();
//get returned values
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name);
System.out.println(sal);
System.out.println(job);
}catch(Exception ex){
ex.printStackTrace();
}finally{
JDBCUtills.release(conn, call, null);
}
}
/*
* create or replace
function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){ //存储函数测试用例
//{?=call procedure-name(??/)}
String sql = "{?= call queryEmpIncome(?)}";
Connection conn = null;
CallableStatement call = null;
try{
conn = JDBCUtills.getConnection();
call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);
call.execute();
double income = call.getDouble(1);
System.out.println(income);
}catch(Exception ex){
ex.printStackTrace();
}finally{
JDBCUtills.release(conn, call, null);
}
}
@Test
public void testCursor(){ //访问游标测试用例
String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; //注意此句要有{}
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try{
conn = JDBCUtills.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 20);
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+ " " + sal);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
JDBCUtills.release(conn, call, rs);
}
}
}
在Java语言中调用
在Java语言中调用存储过程
在Java语言中调用存储函数
什么时候用存储过程/存储函数？
原则：如果只有一个返回值，用存储函数；否则，就用存储过程。
触发器
触发器定义
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
触发器的类型
语句级触发器
?在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器（FOR EACH ROW）
?触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。
创建触发器
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
PLSQL块
触发器简单实例
/*
第一个触发器:对update
*/
create or replace trigger sayHello
after update
on emp
begin
dbms_output.put_line('Hello World');
end;
/
行级触发器
触发语句与伪记录变量的值
触发器应用场景实例
场景一（语句触发器）
/*
触发器应用场景一：实现复杂的安全性检查
限制非工作时间向数据库emp插入数据
1. 周末：星期六 星期日 to_char(sysdate,'day')
2. <9 or >18点 to_number(to_char(sysdate,'hh24'))
*/
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;
/
场景二（行级触发器）
/*
触发器应用场景二：确认数据
涨工资不能越涨少
行级触发器的两个伪记录变量 :old , :new
*/
create or replace trigger checksal
before update
on emp
for each row
begin
if :old.sal > :new.sal then
raise_application_error(-20002,'涨后的工资不能少于涨前的。涨后:'||:new.sal||' 涨前：'||:old.sal);
end if;
end;
/
触发器练习
限制每个部门只招聘5名职工，超过计划则报出错误信息
*****************************************************************************
/*
练习：限制每个部门只招聘5名职工，超过计划则报出错误信息
*/
create or replace trigger limitEmpCount
before insert on emp
for each row
declare
pCount number;-- 保存每个部门的员工数
begin
select count(*) into pcount from emp where deptno=:new.deptno;
if pcount > 5 then raise_application_error(-20004,'部门:' || :new.deptno || ' 员工已有5人');
end if;
end;
?