Oracle存储过程

declare cursor c1 is select * from T_xfendcase;--定义光标
 one_row c1%rowtype;
begin
  open c1;
   Loop                                        --循环
      Fetch  c1 into one_row;                  --操作游标数据
      Exit when c1%notfound;               
      dbms_output.put_line(one_row.id);
    end loop;       
   Exception                                   --操作异常关闭
     when others then  
         close c1;   
         Dbms_Output.put_line(Sqlerrm);   
            
   if c1%isopen then                           --游标打开关闭 
    --close cursor    
      close c1;   
   end if;   
end;

JAVA 调用存储过程
步骤:
1、创建存储过程的脚本,
    使用sqlserver2000 中的pubs 数据库中的 jobs表为例.
   create procedure  showAll
    as
    select * from jobs 
 
    create procedure obtainJob_desc
    @outputParam varchar(20) output,
    @id          int
    as
    select @outputParam = job_desc from jobs where job_id = @id

    create procedure obtainReturn
    as
    declare @ret int
    select @ret = count(*) from jobs
    return @ret

    declare @ret int
    exec  @ret = obtainReturn
    print @ret

  
２、 用来获得连接的函数
   public  Connection getConnection()...{
      Connection con = null;
      try ...{
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
       con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
      } catch (Exception e) ...{
       e.printStackTrace();
      }
      return con ;
     }

  ３、调用得到结果集的存储过程
 public void getResultSet()...{
            //获得连接
            Connection con = this.getConnection();
            try ...{
                //showAll为存储过程名
                java.sql.CallableStatement cstm = con.prepareCall("{call showAll }");
                ResultSet rs = cstm.executeQuery();
                while(rs.next())...{
                    //这里写逻辑代码。
                    System.out.println(rs.getString(1));
                }
                rs.close();
                con.close();
            } catch (SQLException e) ...{
                // TODO Auto-generated catch block
                e.printStackTrace();
            } 
        }
       ４、调用带有 输入 ,输出 参数的存储过程。
  public void getOutParameter(int inParam)...{
            String outParam;
            Connection con = this.getConnection(); 
            try ...{
                CallableStatement cstm = con.prepareCall("{call obtainJob_desc (?,?)}");
                cstm.registerOutParameter(1, Types.VARCHAR);
                cstm.setInt(2, inParam);
                cstm.execute();;
                //得到输出参数。
                String outParma = cstm.getString(2);
                System.out.println(outParma);
                cstm.close();
                con.close();
            } catch (SQLException e) ...{
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    ５、调用带返回值的存储过程。
 public void getReturn()...{
            int ret;
            Connection con = this.getConnection();
            try ...{
                CallableStatement cstm = con.prepareCall("{?=call obtainReturn()}");
                cstm.registerOutParameter(1, Types.INTEGER); 
                cstm.execute();
                //得到返回值
                 ret = cstm.getInt(1);
                System.out.println(ret);
                cstm.close();
                con.close();
            } catch (SQLException e) ...{
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

更多学习
http://www.iteye.com/topic/649874