oracle的存储过程和存储函数

示例的数据表下载的地址：http://download.csdn.net/detail/u010342038/9506955

示例1：

--第一个存储过程：打印Hello World
/*
调用存储过程：
要开启后台输入才能看到结果:set serveroutput on
只是调用一次
1.exce sayhelloworld();
可以调用多次
2.begin
    sayhelloworld();
    sayhelloworld();
    ...
    ...
end;
*/
create or replace procedure sayhelloworld
as
--说明部分（相当于PL/SQL declare）
begin
  dbms_output.put_line('Hello Word');
end;

调用存储过程：

1.

set serveroutput on

exce sayhelloworld();

2.

set serveroutput on

begin
    sayhelloworld();
    sayhelloworld();

end;

示例2：

--创建一个带参数的存储过程（要用到out输出才能有返回值，这就和存储函数的差别）

--给指定的员工涨100钱的工资，并且打印涨前和涨后的薪水

--in是输入
create or replace procedure raisesalary(eno in number)
as
  --定义一个变量保存涨前的薪水
  psal emp.sal%type;
begin
  --得到员工涨前的薪水
  select sal into psal from emp where empno=eno;
  --给员工涨100
  update emp set sal=sal+100 where empno=eno;
  --需不需要commit?
  --注意一般不在存储过程和存储函数中,commit和rollback
  dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));
end;

/*
如何调用：
begin
  raisesalary(7369);
  raisesalary(7499);
  commit
end;
*/

示例3：

--创建存储函数(可以有返回值)
--获取某个员工的年薪（薪水和奖金）
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;
  --直接返回年收入
  --nvl函数的作用：如果pcomm为null,把pcomm赋值成0;
  --如果没有加nvl,如果pcomm为null返回值会出现错误
  return psal*12+nvl(pcomm,0);
end;

示例4：

--查询某个员工的信息
create or replace procedure queryempinform(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
as
begin
  --得到该员工的姓名、月薪和职位
  select ename,sal,ejob into pename,psal,pjob from emp where empno=eno;
end;

用java来操作oracle的存储过程

连接oracle的工具类

package demo;


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:@localhost:1521:orcl";
private static String user = "demo";
private static String password = "demo";

//注册数据库的驱动
static{


try {
//java的驱动通过java反射
Class.forName(driver);
//Oracle的驱动
//DriverManager.registerDriver(driver);

} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

//获取数据库的连接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public static void release(Connection conn,Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
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;
}
}
}
}

Junit测试

示例1：

package test;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import demo.JDBCUtils;


public class TestProcedure {
/*
 * create or replace procedure 
 * queryempinform(eno in number,
                  pename out varchar2,
                  psal   out number,
                  pjob   out varchar2)
 */
@Test
public void testProcedure(){
//{call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{call queryempinform(?,?,?,?)}";

Connection conn = null;
CallableStatement call = null;

try {
//得到一个连接
conn = JDBCUtils.getConnection();
//通过连接创建出statment
System.out.println(sql);
call = conn.prepareCall(sql);

//对于in参数，赋值（1相当于第一个？）
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 (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}

用java来操作oracle的存储函数

示例2：

package test;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import demo.JDBCUtils;


public class TestFunction {
/*
 * create or replace function queryempincome(eno in number)
 */

@Test
public void testFunction(){
//{?= call <procedure-name[(<arg1>,<arg2>,...)]>}
String sql = "{? = call queryempincome(?)}";
Connection conn = null;
CallableStatement call = null;

try {
//得到数据库连接
conn = JDBCUtils.getConnection();
//基于连接创建statment
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 (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}

用java来操作oracle的程序包

示例:3：

oracle的存储过程程序包：

程序包：

CREATE OR REPLACE
PACKAGE MYPACKAGE AS
  --把光标（cursor）的类型赋给empcursor
  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;

java代码：

package test;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import org.junit.Test;
import demo.JDBCUtils;
public class TestCursor {
/*
 * PACKAGE MYPACKAGE AS
  --把光标（cursor）的类型赋给empcursor
  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);
  
  END MYPACKAGE;
 */
@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();
//基于连接创建statement
call = conn.prepareCall(sql);

//对于in参数，赋值(10是部门号)
call.setInt(1, 10);
//对于out参数，申明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行调用
call.execute();

//取出该部门中所有员工的信息
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//取出该员工的员工号，姓名、薪水和职位
int empno = rs.getInt("empno");
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
String job = rs.getString("ejob");

System.out.println(empno+"\t"+name+"\t"+sal+"\t"+job);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
}