存储过程procedure

    存储过程(procedure):存储在数据库中执行某种功能的程序，就是把完成某种功能的程序存储起来，当我们想完成这个功能时直接调用存储方法，提高执行效率，因为他只编译一次。

无参的存储过程

create or replace 
procedure p
is 
cursor c 
  is
    select * from emp2 for update;
begin
  for v_temp in c loop
    if(v_temp.deptno = 10) then
      update emp2 set sal = sal + 10 where current of c;
    elsif(v_temp.deptno = 20) then
      update emp2 set sal = sal + 20 where current of c;
    else
      update emp2 set sal = sal + 50 where current of c;
    end if;
    end loop;
    commit;
end;

在数据库中调用无参的存储过程：

exec p;

或者：

begin
    p;
end;

带参数的存储过程

create or replace 
procedure p_test
  (v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
  if(v_a > v_b) then
    v_ret := v_a;
  else
    v_ret := v_b;
  end if;
  v_temp := v_temp+1;
end;

在数据库中调用带参数的存储过程：

declare
  v_a number := 5;
  v_b number := 10;
  v_res number;
  v_temp number :=15;
begin
  p_test(v_a,v_b,v_res,v_temp);
  dbms_output.put_line(v_res);
  dbms_output.put_line(v_temp);
end;

但是，我们发现没有输出，只输出一个 匿名块功能执行完毕，这是因为我们没有打开serveroutput

set serveroutput on;

接下来，我们用程序去调用存储过程，通过jdbc连接oracle,顺便我们复习一下jdbc连接oracle的步骤，我把连接数据库的基本信息写在了配置文件中，放在了类路径下

注意这里还有一个知识点，就是程序加载配置文件的步骤

         private static Properties pp = null;
	private static InputStream fis= null;
	static {
		pp = new Properties();
		fis=CallProcedure.class.getClassLoader().getResourceAsStream("db.properties");
	          try {
			pp.load(fis);
		} catch (IOException e) {
			System.out.println("加载配置文件失败");
		}
		url = pp.getProperty("url");
		user = pp.getProperty("user");
		driver = pp.getProperty("driver");
		password = pp.getProperty("password");
	}

 

调用无参的存储过程：

package com.test.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
 * 调用无参的存储过程
 * @author 
 *jdk1.6和ojdbc7使用时会出现版本不兼容错误Unsupported major.minor version 51.0
 */
public class CallProcedure {
	private static Connection conn = null;
	private static CallableStatement cstmt = null;
	private static ResultSet rs = null;
	private static String url = "";
	private static String user = "";
	private static String driver = "";
	private static String password = "";
	private static Properties pp = null;
	private static InputStream fis= null;
	static {
		pp = new Properties();
		fis=CallProcedure.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			pp.load(fis);
		} catch (IOException e) {
			System.out.println("加载配置文件失败");
		}
		url = pp.getProperty("url");
		user = pp.getProperty("user");
		driver = pp.getProperty("driver");
		password = pp.getProperty("password");
	}
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName(driver);
			//2.创建连接
			conn = DriverManager.getConnection(url,user,password);
			//3.调用存储过程
			cstmt = conn.prepareCall("{call p}");
			//4.执行
			cstmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		} catch (SQLException e) {
			System.out.println("连接失败");
		}finally{
			try {
				if(cstmt != null)
					cstmt.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println("cstmt关闭失败");
			}
		}
	}
}

调用有参的存储过程：注意，一定要设置out类型参数的类型，这里是java.sql.Types.INTEGER

package com.jiangtao.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.sun.xml.internal.ws.wsdl.writer.document.Types;
/**
 * 调用有参的存储过程
 * @author Administrator
 *jdk1.6和ojdbc7使用时会出现版本不兼容错误Unsupported major.minor version 51.0
 */
public class CallProcedure2 {
	private static Connection conn = null;
	private static CallableStatement cstmt = null;
	private static ResultSet rs = null;
	private static String url = "";
	private static String user = "";
	private static String driver = "";
	private static String password = "";
	private static Properties pp = null;
	private static InputStream fis= null;
	static {
		pp = new Properties();
		fis=CallProcedure2.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			pp.load(fis);
		} catch (IOException e) {
			System.out.println("加载配置文件失败");
		}
		url = pp.getProperty("url");
		user = pp.getProperty("user");
		driver = pp.getProperty("driver");
		password = pp.getProperty("password");
	}
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName(driver);
			//2.创建连接
			conn = DriverManager.getConnection(url,user,password);
			//3.调用存储过程
			cstmt = conn.prepareCall("{call p_test(?,?,?,?)}");
			//3.1设置out类型参数的具体类型
			cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
			//3.2设置in参数
			cstmt.setInt(1, 5);
			cstmt.setInt(2,10);
			cstmt.setInt(4,10);
			//4.执行
			cstmt.execute();
			//注意:要输出的getXXX和上文3.1设置的类型要一致
			System.out.println(cstmt.getInt(3)+", "+cstmt.getInt(4));
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		} catch (SQLException e) {
			System.out.println("连接失败");
		}finally{
			try {
				if(cstmt != null)
					cstmt.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println("cstmt关闭失败");
			}
		}
	}
}