Java调用Oracle存储过程

最新推荐文章于 2021-12-16 23:23:31 发布

爱上双眼皮儿的猫

最新推荐文章于 2021-12-16 23:23:31 发布

阅读量281

点赞数

分类专栏： java

java 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

步骤：

1、编写Oracle存储过程

2、编写数据库获取连接工具类

3、编写简单应用调用存储过程

实现：

1、Oracle存储过程：

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*测试表*/</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> test(
    id varchar2(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">32</span>),
    name varchar2(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">32</span>)
);</span>

<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*存储过程 插入数据*/</span>    
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REPLACE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PROCEDURE</span> insert_procedure(
    PARA1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IN</span> VARCHAR2,
    PARA2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IN</span> VARCHAR2
) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AS</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BEGIN</span>
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> test (id, name) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (PARA1, PARA2);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> insert_procedure;</span>

<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">/*存储过程 返回结果集*/</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REPLACE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PROCEDURE</span> select_procedure(
    para_id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IN</span> VARCHAR2,
    name OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */
) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AS</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BEGIN</span>
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OPEN</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> test <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> id = para_id;</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li></ul>

2、JDBC工具类

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.Connection;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.DriverManager;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.ResultSet;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.SQLException;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.Statement;

<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">DBUtil</span> {</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String DRIVER = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"oracle.jdbc.driver.OracleDriver"</span>;
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String URL = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"jdbc:oracle:thin:@localhost:1521/orcl"</span>;
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String USERNAME = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pfm"</span>;
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String PASSWORD = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pfm"</span>;

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 通过静态代码块 注册数据库驱动
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> {
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            Class.forName(DRIVER);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 获得Connection
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @return</span>
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> Connection <span class="hljs-title" style="box-sizing: border-box;">getConnection</span>() {
        Connection conn = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
            e.printStackTrace();
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> conn;
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 获得Statement
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @return</span>
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> Statement <span class="hljs-title" style="box-sizing: border-box;">getStatement</span>() {
        Statement st = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            st = getConnection().createStatement();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
            e.printStackTrace();
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> st;
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 关闭ResultSet
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> rs
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">closeResultSet</span>(ResultSet rs) {
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> (rs != <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>) {
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
                rs.close();
            } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 关闭Statement
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> st
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">closeStatement</span>(Statement st) {
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> (st != <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>) {
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
                st.close();
            } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 关闭Connection
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> conn
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">closeConnection</span>(Connection conn) {
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> (conn != <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>) {
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
                conn.close();
            } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 关闭全部
     * 
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> rs
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> sta
     *<span class="hljs-javadoctag" style="color: rgb(102, 0, 102); box-sizing: border-box;"> @param</span> conn
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">closeAll</span>(ResultSet rs, Statement sta, Connection conn) {
        closeResultSet(rs);
        closeStatement(sta);
        closeConnection(conn);
    }

}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li><li style="box-sizing: border-box; padding: 0px 5px;">61</li><li style="box-sizing: border-box; padding: 0px 5px;">62</li><li style="box-sizing: border-box; padding: 0px 5px;">63</li><li style="box-sizing: border-box; padding: 0px 5px;">64</li><li style="box-sizing: border-box; padding: 0px 5px;">65</li><li style="box-sizing: border-box; padding: 0px 5px;">66</li><li style="box-sizing: border-box; padding: 0px 5px;">67</li><li style="box-sizing: border-box; padding: 0px 5px;">68</li><li style="box-sizing: border-box; padding: 0px 5px;">69</li><li style="box-sizing: border-box; padding: 0px 5px;">70</li><li style="box-sizing: border-box; padding: 0px 5px;">71</li><li style="box-sizing: border-box; padding: 0px 5px;">72</li><li style="box-sizing: border-box; padding: 0px 5px;">73</li><li style="box-sizing: border-box; padding: 0px 5px;">74</li><li style="box-sizing: border-box; padding: 0px 5px;">75</li><li style="box-sizing: border-box; padding: 0px 5px;">76</li><li style="box-sizing: border-box; padding: 0px 5px;">77</li><li style="box-sizing: border-box; padding: 0px 5px;">78</li><li style="box-sizing: border-box; padding: 0px 5px;">79</li><li style="box-sizing: border-box; padding: 0px 5px;">80</li><li style="box-sizing: border-box; padding: 0px 5px;">81</li><li style="box-sizing: border-box; padding: 0px 5px;">82</li><li style="box-sizing: border-box; padding: 0px 5px;">83</li><li style="box-sizing: border-box; padding: 0px 5px;">84</li><li style="box-sizing: border-box; padding: 0px 5px;">85</li><li style="box-sizing: border-box; padding: 0px 5px;">86</li><li style="box-sizing: border-box; padding: 0px 5px;">87</li><li style="box-sizing: border-box; padding: 0px 5px;">88</li><li style="box-sizing: border-box; padding: 0px 5px;">89</li><li style="box-sizing: border-box; padding: 0px 5px;">90</li><li style="box-sizing: border-box; padding: 0px 5px;">91</li><li style="box-sizing: border-box; padding: 0px 5px;">92</li><li style="box-sizing: border-box; padding: 0px 5px;">93</li><li style="box-sizing: border-box; padding: 0px 5px;">94</li><li style="box-sizing: border-box; padding: 0px 5px;">95</li><li style="box-sizing: border-box; padding: 0px 5px;">96</li><li style="box-sizing: border-box; padding: 0px 5px;">97</li><li style="box-sizing: border-box; padding: 0px 5px;">98</li><li style="box-sizing: border-box; padding: 0px 5px;">99</li><li style="box-sizing: border-box; padding: 0px 5px;">100</li><li style="box-sizing: border-box; padding: 0px 5px;">101</li><li style="box-sizing: border-box; padding: 0px 5px;">102</li><li style="box-sizing: border-box; padding: 0px 5px;">103</li><li style="box-sizing: border-box; padding: 0px 5px;">104</li><li style="box-sizing: border-box; padding: 0px 5px;">105</li><li style="box-sizing: border-box; padding: 0px 5px;">106</li><li style="box-sizing: border-box; padding: 0px 5px;">107</li><li style="box-sizing: border-box; padding: 0px 5px;">108</li><li style="box-sizing: border-box; padding: 0px 5px;">109</li><li style="box-sizing: border-box; padding: 0px 5px;">110</li><li style="box-sizing: border-box; padding: 0px 5px;">111</li><li style="box-sizing: border-box; padding: 0px 5px;">112</li></ul>

3、调用存储过程：

<code class="language-java hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.CallableStatement;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.Connection;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.PreparedStatement;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.ResultSet;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> java.sql.SQLException;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> oracle.jdbc.driver.OracleTypes;

<span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
 * 测试调用存储过程
 * 
 */</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">StoredTest</span> {</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">main</span>(String[] args) {
        insert_call();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//select_call();</span>
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 执行存储过程 插入数据
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">insert_call</span>() {
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;

        CallableStatement proc = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>; <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 创建执行存储过程的对象</span>
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            proc = conn.prepareCall(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"{ call insert_procedure(?,?) }"</span>);
            proc.setString(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"1"</span>); <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 设置第一个输入参数</span>
            proc.setString(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"hello call"</span>); <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 设置第一个输入参数</span>
            proc.execute();<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 执行</span>

        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
                <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 关闭IO流</span>
                proc.close();
                DBUtil.closeAll(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>, pst, conn);
            } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (Exception e) {
                e.printStackTrace();
            }
        }
    }

    <span class="hljs-javadoc" style="color: rgb(136, 0, 0); box-sizing: border-box;">/**
     * 执行存储过程 查询数据
     */</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">select_call</span>() {
        Connection conn = DBUtil.getConnection();

        CallableStatement stmt;
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            stmt = conn.prepareCall(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"{ call select_procedure(?, ?) }"</span>); <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 用此调用方法不能实现多行语法</span>
            stmt.setString(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"1"</span>);
            stmt.registerOutParameter(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>, OracleTypes.CURSOR);
            stmt.execute();
            ResultSet rs = (ResultSet) stmt.getObject(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>);
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">while</span> (rs.next()) {
                System.out.println(rs.getString(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name"</span>));
            }
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (SQLException e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            DBUtil.closeConnection(conn);
        }
    }
}</code>