JDBC(四)—CallableStatement执行匿名块

背景

记录JDBC一系列的内容，实际上这章才是我在项目中遇到的问题，也是因为这个问题，我才想看看JDBC的内容，虽然这部分杜老师并没有提到过，但是如果想解决这部分的东西，也需要前面的铺垫，毕竟所有东西都是由简到难。项目中使用jdbc动态的连接数据库，主要是为了做一些操作，然后分析返回的结果，而遇到的问题就是如何返回结果参数。

问题

项目是由好几个人开发的，原本这部分是别人开发的，用python写的，但是由于种种原因需要我根据他的逻辑重新用java写一遍，问题来了

  result = cursor.execute(query_sql, other_xml=other_xml)
  l_other_xml = other_xml.getvalue()

这个sql是个匿名块（不懂的也可以理解是存储过程的简化版），取执行sql的同时，还传了参数，用来返回在sql中执行的结果，网上执行存储过程，存储函数的方法一大堆，匿名块找不到，一开始没办法，打算把匿名块改成存储过程，结果被告知，客户是不允许我们在库里定义存储过程的，尴尬了。

解决方法

• 首先看下匿名块

BEGIN
                    FOR i IN (SELECT other_xml
                                FROM dba_hist_sql_plan
                            WHERE sql_id = TRIM('%s')
                                AND plan_hash_value = TO_NUMBER(TRIM('%s'))
                                AND other_xml IS NOT NULL
                            ORDER BY
                                    id)
                    LOOP
                        :other_xml := i.other_xml;
                        EXIT;
                    END LOOP;
                EXCEPTION
                    WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
                        :other_xml := NULL;
                END;

具体执行了什么也不懂，DBA写的，但是现在需求是获取到最后的other_xml的值，

• 改写sql

        declare \n" +
                                "  other_xml CLOB;\n" +
                                "          \n" +
                                "             BEGIN\n" +
                                "                FOR i IN (SELECT other_xml\n" +
                                "                            FROM gv$sql_plan\n" +
                                "                            WHERE sql_id = TRIM(?)\n" +
                                "                            AND plan_hash_value = TO_NUMBER(TRIM(?))\n" +
                                "                            AND other_xml IS NOT NULL\n" +
                                "                            ORDER BY\n" +
                                "                                child_number, id)\n" +
                                "                LOOP\n" +
                                "                    other_xml := i.other_xml;\n" +
                                "                    EXIT;\n" +
                                "                END LOOP;\n" +
                                "               ?:=other_xml;\n" +
                                "            EXCEPTION\n" +
                                "                WHEN OTHERS THEN\n" +
                                "                    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);\n" +
                                "                    other_xml := NULL;\n" +
                                "            END;

如果你仔细的看，这里加了个

?:=other_xml;

看到问号，会想到什么？
这里的sql03就是上面的改写过的sql

CallableStatement call3 = connect.prepareCall(sql03);
                call3.setString(1,sqlId);
                call3.setString(2,planHashValue);
                call3.registerOutParameter(3,OracleTypes.CLOB);
                call3.execute();
                Clob other_xml=call3.getClob(3);

你仔细看，是有三个问号，代表个参数，第一个和第二个都代表的是传入参数，第三个代表的是返回参数，CallableStatement可以设置注册返回参数，下标根据？的位置来，这里并不区分传入的还是传出的，我一开始认为第一个传出的就是下标1和传入的不关联，结果就错了，然后通过get下标来获取，这里有个固定就是，传入和获取要么都使用下标，要么都是用名字。当然CallableStatement这个也可以执行存储过程和函数，但是这个网上一大堆，就不写了。