Mysql存储过程(Java)

java操作：

CallableStatement cs =<span style="color: #000000"> con.prepareCall(sql);
cs.registerOutParameter(</span>1, java.sql.Types.INTEGER);<span style="color: #008000">//</span><span style="color: #008000">注册存储过程的out型参数类型；使用之前必须注册；</span>
<span style="color: #000000">cs.execute();
System.out.println(cs.getInt(</span>2)); <span style="color: #008000">//</span><span style="color: #008000">获取out的输出结果</span>

2、获取查询结果集（来自select查询），且有多个结果集如何处理？

<span style="color: #0000ff">-- 存储过程SQL<br>create</span> <span style="color: #0000ff">procedure</span><span style="color: #000000"> bach_pro()
</span><span style="color: #0000ff">begin</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> table1;
</span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> table2;
</span><span style="color: #0000ff">end</span>

java操作：

 CallableStatement cs =<span style="color: #000000"> con.prepareCall(sql);
 cs.execute();
 ResultSet resultSet </span>=<span style="color: #000000"> cs.getResultSet();
        
 </span><span style="color: #008000">//</span><span style="color: #008000">遍历第一个结果集</span>
 <span style="color: #0000ff">while</span><span style="color: #000000">(resultSet.next()){            
     System.out.println(resultSet.getInt(</span>1)); <span style="color: #008000">//</span><span style="color: #008000"> 输出结果集</span>
<span style="color: #000000"> }
       
 </span><span style="color: #008000">//</span><span style="color: #008000">获取下一个结果集 </span>
<span style="color: #000000"> ResultSet rs2;
 </span><span style="color: #0000ff">while</span><span style="color: #000000">(cs.getMoreResults()){
      rs2 </span>=<span style="color: #000000"> cs.getResultSet();
      </span><span style="color: #0000ff">while</span><span style="color: #000000"> (rs2.next()) {
          System.out.println(rs2.getInt(</span>1)); <span style="color: #008000">//</span><span style="color: #008000">输出结果集列</span>
<span style="color: #000000">      }
 }</span>

3、当我们要在存储过程中处理查询结果集时，我们就需要使用到cursor，下面是cursor一个简单的使用例子

<span style="color: #0000ff">begin</span>
<span style="color: #0000ff">declare</span> stop <span style="color: #0000ff">int</span> <span style="color: #0000ff">default</span> <span style="color: #800000; font-weight: bold">0</span> ;<span style="color: #008080">--</span><span style="color: #008080">需要在cursor前声明参数</span>
<span style="color: #0000ff">declare</span> id_temp <span style="color: #0000ff">int</span><span style="color: #000000">; 
</span><span style="color: #0000ff">declare</span> cur1 <span style="color: #0000ff">cursor</span> <span style="color: #0000ff">for</span> (<span style="color: #0000ff">select</span> id <span style="color: #0000ff">from</span><span style="color: #000000"> xinguan);
</span><span style="color: #0000ff">declare</span> <span style="color: #0000ff">continue</span> handler <span style="color: #0000ff">for</span> <span style="color: #808080">not</span> found <span style="color: #0000ff">set</span> stop <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>; <span style="color: #008080">--</span><span style="color: #008080">声明cursor扫描完后设置值，用于结束循环</span>
<span style="color: #0000ff">open</span><span style="color: #000000"> cur1;
cur1_loop:loop
    </span><span style="color: #0000ff">fetch</span> cur1 <span style="color: #0000ff">into</span> id_temp;  <span style="color: #008080">--</span><span style="color: #008080">从cursor中取值赋给变量</span>
    <span style="color: #0000ff">if</span> stop <span style="color: #0000ff">then</span><span style="color: #000000">
        leave cur1_loop;
    </span><span style="color: #0000ff">end</span> <span style="color: #0000ff">if</span><span style="color: #000000">;
</span><span style="color: #0000ff">end</span><span style="color: #000000"> loop cur1_loop;
</span><span style="color: #0000ff">close</span><span style="color: #000000"> cur1;
</span><span style="color: #0000ff">end</span>

Mysql存储过程(Java)

标签：.sql   statement   sel   strong   param   列表   handle   参数   例子