Groovy Oracle存储过程 - 入侵列索引 [英] Groovy Oracle Stored Proc - invaid column index

问题描述

  sql.call（StoredProcName，[Sql.resultSet（OracleTypes.CURSOR）]）{table  - > 
 table.eachRow {row-> 
 println row 
} 
} 
  

编辑
这段代码引发了一个无效列索引

这段代码看起来像和我在网上看到的例子一样。我错过了什么？我不能在游标上做每行吗？这是我的过程

$ p $ 创建或替换过程StoredProcName（cursor_OUT SYS_REFCURSOR）
as
开始
打开cursor_对于
选择blahblahblah


解决方案

以下代码可以帮助您从Oracle匿名块获取 SYS_REFCURSOR 变量。

我们应该关注几个关键详细信息：

1. 类 groovy.sql.Sql 没有相应的 OutParameter ，我们将它手动设置为 CURSOR_PARAMETER 并将它传递给 sql.call method


2. 请考虑以 {调用DECLARE 并以 END结束）在END后没有分号。否则，我们可能会难以辨认 SQLException 。


3. 问号？ sqlString 中的$ c>是参数绑定的地方。绑定以自然顺序进行，取自 parametersList 的值。
   
   
   
   
   • 在这个例子中，我们有唯一的绑定，所以？与CURSOR_PARAMETER绑定，将值视为 OUT 传递类型的参数;
     
   
   
   • sql.call 和 ResultSet rs 提供游标行 my_cur 声明在匿名块中。
     
   • 我们可以通过使用返回 SYS_REFCURSOR sqlString c>与 OUT 参数的过程无关。所以它可能看起来像这样{call BEGIN？：= MY_FUNC（）; END}甚至{？= call MY_FUNC（）}
   
   

---

  import groovy.sql.OutParameter 
 import groovy.sql.Sql 
 import oracle.jdbc.OracleTypes 
 
 import java.sql.ResultSet 
 
 def driver ='oracle.jdbc.driver.OracleDriver'
 def sql = Sql.newInstance（'jdbc：oracle：thin：@ my-server：1521：XXX'，'usr'，'psw'，driver）
 
 //针对游标类型的特殊OutParameter 
 OutParameter CURSOR_PARAMETER = new OutParameter（）{
 public int getType（）{
返回OracleTypes.CURSOR; 
} 
}; 
 
 //查看匿名块的一些礼仪包装
 String sqlString ={call 
 DECLARE 
 my_cur SYS_REFCURSOR; 
 BEGIN 
 STORED_PROCEDURE_NAME（my_cur）; 
？：= my_cur; 
 END 
} 
; 
 
 //元素的顺序与绑定顺序匹配
 def parametersList = [CURSOR_PARAMETER]; 
 
 
 // rs包含游标的结果集my_cur 
 sql.call（sqlString，parametersList）{ResultSet rs  - > 
 while（rs.next（））{
 println rs.getString（my_column）
} 
}; 
  

并感谢您的问题。

sql.call("StoredProcName", [Sql.resultSet(OracleTypes.CURSOR)]) { table ->
        table.eachRow { row->
            println row
        }
    }

EDIT This block of code is throwing me a invalid column index

This code looks the same as examples that I've seen online. What am I missing? Can I not do eachRow on a cursor? Here is my proc

Create or Replace Procedure StoredProcName(cursor_ OUT SYS_REFCURSOR)
as
Begin
Open cursor_ For
Select blahblahblah

解决方案

The following code can help you get variable of SYS_REFCURSOR from Oracle anonymous block.

We should focus on a few key details:

1. Class groovy.sql.Sql doesn't have corresponding OutParameter and we make it manually as CURSOR_PARAMETER and pass it to sql.call method
2. Consider that the block starts with {call DECLARE and ends with END } without semicolon after END. Otherwise we can get a poorly recognizable SQLException in the face.
3. The question marks ? inside the sqlString are places for parameter bindings. Bindings are made in the natural order taking values from parametersList.
   • In this example we have the only binding, so the ? binds with CURSOR_PARAMETER treating the value as OUT parameter of passed type;
4. There is only one enter into closure after sql.call and ResultSet rs provide rows of cursor my_cur declared in anonymous block.
5. We can simplify sqlString by using a function returning SYS_REFCURSOR unstead of a procedure with OUT parameter. So it could look like this "{call BEGIN ? := MY_FUNC(); END}" or even "{? = call MY_FUNC()}"

---

import groovy.sql.OutParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes

import java.sql.ResultSet

def driver = 'oracle.jdbc.driver.OracleDriver'
def sql = Sql.newInstance('jdbc:oracle:thin:@my-server:1521:XXX', 'usr', 'psw', driver)

// special OutParameter for cursor type
OutParameter CURSOR_PARAMETER = new OutParameter() {
    public int getType() {
        return OracleTypes.CURSOR;
    }
};

// look at some ceremonial wrappers around anonymous block
String sqlString = """{call
    DECLARE
      my_cur SYS_REFCURSOR;
    BEGIN
        STORED_PROCEDURE_NAME(my_cur);
        ? := my_cur;
    END
}
""";

// the order of elements matches the order of bindings
def parametersList = [CURSOR_PARAMETER];


// rs contains the result set of cursor my_cur
sql.call(sqlString, parametersList) { ResultSet rs ->
  while (rs.next()) {
      println rs.getString("my_column")
  }
};

P.S. And thank you for the question.

这篇关于Groovy Oracle存储过程 - 入侵列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！