最近几次出去面试,经常被问及项目中用到过存储过程没。从工作到现在,大大小小的项目几乎都是用的oracle,存储过程肯定是用的。但是呢,自己涉及的模块又都是没有存储过程,也或者有DBA在操作,接触不到,自然而然的说不知道了。于是下定决心一定要把oracle存储过程给补习下。
PS:oracle有好多概念,像视图、索引、触发器、物化视图、游标、函数、包等。后续也会督促自己去学习,并总结整理出来。
本文举例内容描述:
实例: 根据dept_id查询属于此部门的雇员信息。
分析: oracle存储过程本没有返回值,有返回值也是用out关键字来标示。但是像对于根据部门查询雇员的,会返回多条记录。所以此时可以建立一个package,即cursor游标。
pl/sql:
--1 建立一个package
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--2 建立存储过程
create or replace procedure findAllEmp
(deptno in number,p_cursor out testpackage.test_cursor)
is
begin
open p_cursor forselect e.* from employees e where e.department_id = deptno;
end findAllEmp;
--注意蓝字部分的写法格式
java调用:
(1)db工具类
package com.oracle.exe;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
private static final String USERNAME = "hr";
private static final String PASSWORD = "hr";
private static DBUtil dbUtil = null;
private DBUtil()
{
}
public static synchronized DBUtil getInstance()
{
if(dbUtil==null)
{
dbUtil = new DBUtil();
}
return dbUtil;
}
/**
*
* @return
*/
public static Connection getConn()
{
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
*
* @param conn
*/
public void colseConn(Connection conn)
{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(DBUtil.getConn());
}
}
(2)调用过程
package com.oracle.exe;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallProcedure {
private static Connection conn = DBUtil.getConn();
public static void main(String[] args) {
CallProcedure.callCursorProcedure();
}
/**
*
*/
public static void callCursorProcedure()
{
try {
CallableStatement call = conn.prepareCall("{ call findAllEmp (?,?)}");
call.setInt(1, 60);
//注册输出参数的类型
call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
call.execute();
ResultSet rs = (ResultSet)call.getObject(2);
//遍历结果集
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}