- 存储过程和存储函数是指存储在数据库中的供用户程序调用的子程序。
- 两者间区别在于存储函数可以return 值。
- 如果只有一个返回值，选择存储函数，否则使用存储过程。
1 存储过程
1-1 创建语法
create [or replace] procedure 过程名（参数列表）
as
pl/sql子程序体；
-- as 相当于 declare
1-2 示例
根据人员类型和人员编号获取人员姓名及其生日
create or replace procedure getXmAndCsrq(rylx in varchar2, rybh in varchar2,xm out varchar2,csrq out is
begin
select xm,csrq into xm,csrq from bas_employee where rylx=rylx and rybh=rybh and rownum
end
rownum <=1 可能含有多条数据
1-3 调用
"{call getXmAndCsrq(?,?,?,?)}";
Connection conn = null;
CallableStatement cst = null;
ResultSet rs = null;
try {
conn = DbUtils.getConnection();
cst = conn.prepareCall(sql);
cst.setString(1, "1");
cst.setString(2, "seg179");
cst.registerOutParameter(3, OracleTypes.VARCHAR);
cst.registerOutParameter(4, OracleTypes.VARCHAR);
cst.execute();
String xm = cst.getString(3);
String csrq = cst.getString(4);
System.out.println("姓名："+xm+",出生日期："+csrq);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.release(conn, cst, rs);2 存储函数
2-1 创建语法
create [or replace] function
return 函数值类型
as
pl/sql子程序体；
-- as 相当于 declare 申明变量
2-2 示例
获取成员中最大的年龄
create or replace function getMaxAge(bmdm in varchar2)
return varchar2
is
begin
select min(csrq) into mincsrq from
return (sysdate-mincsrq)/365;
end
返回值包含小数点后若干位。
2-3 调用
//测试函数
@Test
public void testFunction(){
String sql = "{?=call getMaxAge(?)}";
Connection conn = null;
CallableStatement cst = null;
ResultSet rs = null;
try {
conn = DbUtils.getConnection();
cst = conn.prepareCall(sql);
cst.registerOutParameter(1, OracleTypes.VARCHAR);
cst.setString(2, "610581002400");
cst.execute();
String maxAge = cst.getString(1);
maxAge = String.valueOf(Math.ceil(Double.valueOf(maxAge)));
System.out.println("最大年龄为："+maxAge);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.release(conn, cst, rs);
3 游标
3-1 创建语法
包
CREATE OR REPLACE PACKAGEpackage_name /*包头名称*/
IS|AS
包体
OR REPLACE PACKAGE BODY package_name/*包名必须与包头的包名一致*/
IS | AS pl/sql_package_body /*游标,函数,过程的具体定义*/
3-2 示例
包
create or replace package mypackage is
-- Author : ADMINISTRATOR
-- Created : 2017/8/6 18:22:18
-- Purpose :
-- Public type declarations
type empcursor is ref cursor;
-- Public function and procedure declarations
procedure getAllperson(bmdm in varchar2,empList out empcursor);
end
包体
create or replace package body mypackage is
-- Public function and procedure declarations
procedure getAllperson(bmdm in varchar2,empList out empcursor) as
begin
open empList for select * from bas_employee where
end
end
3-3 调用
//测试游标
@Test
public void testCursor(){
String sql = "{call mypackage.getAllperson(?,?)}";
Connection conn = null;
CallableStatement cst = null;
ResultSet rs = null;
try {
conn = DbUtils.getConnection();
cst = conn.prepareCall(sql);
cst.setString(1, "610527002200");
cst.registerOutParameter(2, OracleTypes.CURSOR);
cst.execute();
rs = ((OracleCallableStatement) cst).getCursor(2);
while(rs.next()&&rs.getRow()<=5){
String xm = rs.getString("xm");
String rylx = rs.getString("rylx");
String rybh = rs.getString("rybh");
System.out.println("人员类型："+rylx+",人员编号："+rybh+",姓名："+xm);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.release(conn, cst, rs);
4 代码
连接数据库类 DbUtils.java
public class DbUtils {
//驱动
private static String driver = "oracle.jdbc.OracleDriver";
//数据库地址
private static String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//用户名
private static String user="rm";
//密码
private static String password="rmadmin";
//注册数据库驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//数据库连接
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//释放
public static void release(Connection conn,Statement st,ResultSet rs){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
