ORACLE存储过程（二）之存储过程的调用

任何一门语言或者是技术的诞生，使用才是硬道理，为什么我们要使用存储过程？不适用可以吗？有没有比存储过程更好的东东？，带着这些一连串的问题，让我们走进存储过程的使用阶段。
1.无返回值的存储过程

create or replace procedure AddNewUser
       (n_id      user_info.id%TYPE,
        n_name    user_info.name%TYPE,
        n_pwd     user_info.pwd%TYPE,
        n_address user_info.address%TYPE
        ) is
 begin
  INSERT INTO user_info
    (id, name, pwd, address)
  VALUES
    (n_id, n_name, n_pwd, n_address);

end AddNewUser;

2.带返回值的存储过程（非列表）

create or replace procedure count
      (username in varchar2,
       userpassword in varchar2, 
       userCount out integer
       ) as
       begin
          select count(*) into userCount from USER_INFO where NAME = username and PWD = userpassword;
       end count;

JDBC 调用有返回值存储过程：

package com.citichy;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestProcedure {
 public void Test(){
     Connection con = null;
     try { 
    	 Class.forName("oracle.jdbc.driver.OracleDriver");  
    	 con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","wepull","wepull");  
    	 // 设置要执行的SQL语句，如果是执行存储过程，那么执行存储过程的格式按括号内所示  
    	 CallableStatement cst = con .prepareCall("{call check_user(?,?,?) }");      	
    	 cst.setString(1, "zhangxuan");  
    	 cst.setString(2, "123");    // 设置存储过程中输出参数的值  
    	 cst.registerOutParameter(3, Types.INTEGER);  
    	 cst.execute();    // 执行完存储过程后，取得存储过程中输出参数的值  
    	 int testPrint = cst.getInt(3);   
    	 System.out.println(testPrint);
    }catch (ClassNotFoundException e){
    	e.printStackTrace();
    }catch (SQLException e){  
    	 e.printStackTrace();   
    }finally {   
    	try {
    		if (null != con) {
    			con.close();     
    		}
    	} catch (SQLException e) {
    		e.printStackTrace();    
    	}   	 
     }
 }
}

 注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

3.带返回值的存储过程（列表）

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，
（1）创建程序包

create or replace package testpackage
   as
     type test_cursor is ref cursor;
   end testpackage;

type test_cursor is ref cursor-----创建游标“cursor”指针类型变量名为test_cursor；

(2)存储过程

create or replace procedure testc(p_cursor out testpackage.test_cursor)is
   begin
      open p_cursor for select * form user_info;
   end testc;

(3)JDBC 调用有返回列表的存储过程

package com.citichy;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestProcedure {
 public void Test(){
     Connection con = null;
     ResultSet rs = nul;
     try { 
       Class.forName("oracle.jdbc.driver.OracleDriver");  
       con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","wepull","wepull");  
       // 设置要执行的SQL语句，如果是执行存储过程，那么执行存储过程的格式按括号内所示  
       CallableStatement cst = conn.prepareCall("{ call hyq.testc(?) }");        
       cst.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR); // cst.registerOutParameter(1,Types.CURSOR);
       cst.execute();    // 执行完存储过程后，取得存储过程中输出参数的值  
       rs = (ResultSet)proc.getObject(1);
       while(rs.next()){
          System.out.println("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td></tr>");
       }
    }catch (ClassNotFoundException e){
      e.printStackTrace();
    }catch (SQLException e){  
       e.printStackTrace();   
    }finally {   
      try {
        if (null != con) {
          con.close();     
        }
      } catch (SQLException e) {
        e.printStackTrace();    
      }      
     }
 }
}

OK，存储过程的书写以及调用都弄明白了，接下来总结一下。

一、什么是存储过程。
   存储过程是数据库服务器端的一段SQL程序集，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。
二、什么时候需要用存储过程
　　如果服务器定义了存储过程，应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：a.服务器往往具有强大的计算能力和速度;b.避免把大量的数据下载到客户端，减少网络上的传输量。
　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。
　　不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。

 优点：

1、预编译：存储过程预先编译好放在数据库内，减少编译语句所花的时间。

2、缓存：预编译的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其它次数的执行速度会明显提高。

3、减少网络传输：特别是对于一些处理数据的存储过程，不必像直接使用SQL语句那样多次传送数据到客户端。

4 、可维护性高：更新存储过程通常要比更改、测试和部署应用程序需要的时间和精力要少。

5、代码的重用：一个可以重用的存储过程可以应用到应用程序的多个位置。

6、增强安全性：通过对用户授权对存储过程的访问权限，它们可以提供对特定数据的访问；提高数据安全性，来防止SQL注入。

缺点：

1、如果需要对存储过程的输入输出参数做更改的话，还要更改程序。

2、可移植性差：因为存储过程将应用程序的业务处理绑定到数据库中，以此使用存储过程来处理业务逻辑限制了应用程序的可移植性。
三、存储过程的参数
　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：
　　第一种称为输入参数，由客户程序向存储过程传递值。
　　第二种称为输出参数，由存储过程向客户程序返回结果。
　　第三种称为输入/输出参数，既可以由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果。
　　第四种称为状态参数，由存储过程向客户程序返回错误信息。
　　要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。