存储过程:
create table LOG
(
ID NUMBER,
TIME_INPUT DATE,
TIME_OCCUR DATE,
TITLE VARCHAR2(200),
CONTENT VARCHAR2(4000),
RECORDER VARCHAR2(50)
)
创建sequence
-- Create sequence
create sequence SEQ_LOG
minvalue 1
maxvalue 999999999999999999999
start with -9
increment by -41031
nocache;
包名:
CREATE OR REPLACE PACKAGE PKG_LOG IS
TYPE query_result IS REF CURSOR;
/*
说明:添加日志
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_add(
v_TIME_INPUT VARCHAR2,
v_TIME_OCCUR VARCHAR2,
v_TITLE VARCHAR2,
v_CONTENT VARCHAR2,
v_RECORDER VARCHAR2
);
/*
说明:删除日志
输入:记录id
输出:
*/
procedure log_delete(v_id number);
/*
说明:修改日志
输入:日志id 记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_update(
v_id number,
v_TIME_INPUT VARCHAR2,
v_TIME_OCCUR VARCHAR2,
v_TITLE VARCHAR2,
v_CONTENT VARCHAR2,
v_RECORDER VARCHAR2
);
/*
说明:查询日志:通过id查询
输入:日志id
输出:
*/
procedure log_query_byid(
v_id number,
r out query_result
);
/*
说明:查询日志:通过日期查询
输入:记录日志日期 日志发生日期(可以一次只传递一个日期,也可以一个也不传,也可以全部都传,不传默认全部查询)
输出:
*/
procedure log_query_bytime(
v_TIME_INPUT VARCHAR2,
v_TIME_OCCUR VARCHAR2,
r out query_result
);
END PKG_LOG;
包体:
CREATE OR REPLACE PACKAGE body PKG_LOG IS
/*
说明:添加日志
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_add(v_TIME_INPUT VARCHAR2,
v_TIME_OCCUR VARCHAR2,
v_TITLE VARCHAR2,
v_CONTENT VARCHAR2,
v_RECORDER VARCHAR2) is
timeInput date;
timeOccur date;
begin
timeInput := to_date(v_TIME_INPUT, 'yyyy-mm-dd');
timeOccur := to_date(v_TIME_OCCUR, 'yyyy-mm-dd');
insert into log
values
(seq_log.nextval,
timeInput,
timeOccur,
v_TITLE,
v_CONTENT,
v_RECORDER);
commit;
end log_add;
/*
说明:删除日志
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_delete(v_id number) is
begin
delete from log l where l.id = v_id;
commit;
end log_delete;
/*
说明:修改日志
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_update(v_id number,
v_TIME_INPUT VARCHAR2,
v_TIME_OCCUR VARCHAR2,
v_TITLE VARCHAR2,
v_CONTENT VARCHAR2,
v_RECORDER VARCHAR2) is
timeInput date;
timeOccur date;
begin
timeInput := to_date(v_TIME_INPUT, 'yyyy-mm-dd');
timeOccur := to_date(v_TIME_OCCUR, 'yyyy-mm-dd');
update log l
set l.time_input = timeInput,
l.TIME_OCCUR = timeOccur,
l.TITLE = v_TITLE,
l.CONTENT = v_CONTENT,
l.RECORDER = v_RECORDER
where l.id = v_id;
commit;
end log_update;
/*
说明:查询日志:通过id查询
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_query_byid(v_id number, r out query_result) is
query_r query_result;
begin
open query_r for
select * from log l where l.id = v_id;
r := query_r;
end log_query_byid;
/*
说明:查询日志:通过日期查询
输入:记录日志日期 日志发生日期 标题 详细内容 记录人
输出:
*/
procedure log_query_bytime(v_TIME_INPUT varchar2,
v_TIME_OCCUR VARCHAR2,
r out query_result) is
timeInput date;
timeOccur date;
query_r query_result;
begin
if v_TIME_INPUT is null and v_TIME_OCCUR is null then
open query_r for
select *
from log l;
elsif v_TIME_INPUT is null then
timeOccur := to_date(v_TIME_OCCUR, 'yyyy-mm-dd');
open query_r for
select * from log l where l.TIME_OCCUR = timeOccur;
elsif v_TIME_OCCUR is null then
timeInput := to_date(v_TIME_INPUT, 'yyyy-mm-dd');
open query_r for
select * from log l where l.TIME_INPUT = timeInput;
else
timeOccur := to_date(v_TIME_OCCUR, 'yyyy-mm-dd');
timeInput := to_date(v_TIME_INPUT, 'yyyy-mm-dd');
open query_r for
select *
from log l
where
l.TIME_INPUT = timeInput and
l.TIME_OCCUR = timeOccur;
end if;
r := query_r;
end log_query_bytime;
END PKG_LOG;
package com.dada.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestLog {
boolean isInsert = false;
Connection conn = null;
public static void main(String[] args) {
TestLog tl = new TestLog();
// tl.log_add("2012-12-28", "2012-12-27", "故障", "故障1", "p1");
// tl.log_delete(1);
// tl.log_update(1,"2012-12-28", "2012-12-29", "故障", "故障2", "p2");
// tl.log_querybyid(1);
// tl.log_querybytime("2012-12-28", "");
// tl.log_querybytime("", "2012-12-27");
// tl.log_querybytime("", "");
tl.log_querybytime("2012-12-28", "2012-12-29");
}
public Connection init_oracle() {
// 获取oracle数据库的链接
// url="jdbc:数据库名:连接的方式:@主机Ip:端口号:数据库名称"
try {
// 建立连接
String url = "jdbc:oracle:thin:@192.168.0.100:1521:orcl";
String user = "test";
String password = "test";
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
System.out.println("数据库连接异常!");
e.printStackTrace();
}
return conn;
}
// 测试增加存储过程
public void log_add(String time_input,String time_occur,String title,String content,String recorder) {
getConn();
CallableStatement pstmt = null;
try {
// 调用存储过程
pstmt = conn
.prepareCall("{ call pkg_log.log_add(?,?,?,?,?)}");
//为占位符赋值
pstmt.setString(1, time_input);
pstmt.setString(2, time_occur);
pstmt.setString(3, title);
pstmt.setString(4, content);
pstmt.setString(5, recorder);
isInsert = pstmt.execute();
System.out.println("添加结果:"+isInsert);
closePstmtAndConn(pstmt,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试删除存储过程
public void log_delete(int id) {
getConn();
CallableStatement pstmt = null;
try {
// 调用存储过程
pstmt = conn
.prepareCall("{ call pkg_log.log_delete(?)}");
//为占位符赋值
pstmt.setInt(1, id);
isInsert = pstmt.execute();
System.out.println("删除结果:"+isInsert);
closePstmtAndConn(pstmt,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试修改存储过程
public void log_update(int id,String time_input,String time_occur,String title,String content,String recorder) {
getConn();
CallableStatement pstmt = null;
try {
// 调用存储过程
pstmt = conn
.prepareCall("{ call pkg_log.log_update(?,?,?,?,?,?)}");
//为占位符赋值
pstmt.setInt(1, id);
pstmt.setString(2, time_input);
pstmt.setString(3, time_occur);
pstmt.setString(4, title);
pstmt.setString(5, content);
pstmt.setString(6, recorder);
int isInsert = pstmt.executeUpdate();
System.out.println("修改结果:"+isInsert);
closePstmtAndConn(pstmt,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试通过id查询存储过程
public void log_querybyid(int id) {
getConn();
ResultSet rs = null;
CallableStatement pstmt = null;
try {
// 调用存储过程
pstmt = conn
.prepareCall("{ call pkg_log.log_query_byid(?,?)}");
//为占位符赋值
pstmt.setInt(1, id);
pstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
pstmt.executeQuery();
rs = (ResultSet) pstmt.getObject(2);
while (rs.next()) {
int v_id = rs.getInt(1);
String v_TIME_INPUT = rs.getString(2);
String v_TIME_OCCUR = rs.getString(3);
String v_TITLE = rs.getString(4);
String v_CONTENT = rs.getString(5);
String v_RECORDER = rs.getString(6);
System.out.println(
"日志id:"+v_id+"\t"+
"记录时间:"+v_TIME_INPUT+"\t"+
"发生时间:"+v_TIME_OCCUR+"\t"+
"标题:"+v_TITLE+"\t"+
"内容:"+v_CONTENT+"\t"+
"记录者:"+v_RECORDER
);
}
closePstmtAndConn(pstmt,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试通过时间查询存储过程
public void log_querybytime(String time_input,String time_occur) {
getConn();
ResultSet rs = null;
CallableStatement pstmt = null;
try {
// 调用存储过程
pstmt = conn
.prepareCall("{ call pkg_log.log_query_bytime(?,?,?)}");
//为占位符赋值
pstmt.setString(1, time_input);
pstmt.setString(2, time_occur);
pstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
pstmt.executeQuery();
rs = (ResultSet) pstmt.getObject(3);
while (rs.next()) {
int v_id = rs.getInt(1);
String v_TIME_INPUT = rs.getString(2);
String v_TIME_OCCUR = rs.getString(3);
String v_TITLE = rs.getString(4);
String v_CONTENT = rs.getString(5);
String v_RECORDER = rs.getString(6);
System.out.println(
"日志id:"+v_id+"\t"+
"记录时间:"+v_TIME_INPUT+"\t"+
"发生时间:"+v_TIME_OCCUR+"\t"+
"标题:"+v_TITLE+"\t"+
"内容:"+v_CONTENT+"\t"+
"记录者:"+v_RECORDER
);
}
closePstmtAndConn(pstmt,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 关闭连接
*/
public void closeConn() {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
/**
* 获取连接
*/
public void getConn() {
if(conn !=null) {
closeConn();
}
init_oracle();
}
/**
* 关闭连接
* @param pstmt
* @param conn
*/
public void closePstmtAndConn(PreparedStatement pstmt,Connection conn) {
if(pstmt!=null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt=null;
}
if(conn!=null) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}