存储过程procedure

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

无参的存储过程

create or replace procedure pis 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)isbegin  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关闭失败");}}}}