无法加载类groovy.sql.sql

Question

我试图从groovy脚本调用Oracle存储过程，结果出现了标题中的错误。

以下是我的剧本：

import groovy.sql.Sql
import java.util.logging.*

Logger.getLogger('groovy.sql').level = Level.ALL

def dbUser = 'YOU'
def dbPassword = 'WILL'
def dbDriver = 'oracle.jdbc.driver.OracleDriver'
def dbURL = 'NEVER:KNOW'  

def query = Sql.newInstance(dbURL, dbUser, dbPassword, dbDriver)

def affiliateNumber = 18
def depositIdentifier = 55
def depositType = "00"
def depositAmount = 18.49
def depositCommunication = 135453
def d = new Date()
def depositDate = d.toTimestamp()
def criterium = 0

/*
sql.query('SELECT NO_AFF, DEN from AFF WHERE no_AFF = 4'){ resultSet ->
    while(resultSet.next()){
        println resultSet.getString('no_aff') + resultSet.getString('den')
    }
}
*/

query.call("CALL PAC_GCA_IMP_AUTO_2.IMP_AUTO(${affiliateNumber}, ${depositIdentifier}, ${depositType}, ${depositAmount}, ${depositCommunication}, ${depositDate}, ${criterium}, ?, ? )", [Sql.INTEGER, Sql.VARCHAR])

当我删除[sql.integer,sql.varchar]（它们是OUT parameters）参数时，脚本将被编译，所以我认为它们有问题······

我找到了一个让我的程序工作的解决方案：

def returnCode = query.call("DECLARE v_sqlcode PLS_INTEGER; v_sqlerrm VARCHAR2(500); BEGIN PAC_GCA_IMP_AUTO_2.IMP_AUTO(?, ?, ?, ?, ?, ?, ?, v_sqlcode, v_sqlerrm); END;",
        [
                affiliateNumber,
                depositIdentifier,
                depositType,
                depositAmount,
                depositCommunication,
                depositDate,
                criterium
        ])

println "Groovy return = ${returnCode}"

但我想有一个更好的方法来传递这些SQL参数...

PS：我正在使用Groovy-2.4.13，并尝试了Oracle JDK9和1.8.0_151

Answer 1

对于带有签名的过程

 procedure proc_x (a number, b number, ret out number )

这对我有用

def a = 100
def b = 50 
def res

db.call("""begin
 proc_x(${a},${b},${Sql.inout(Sql.INTEGER(res))});
end;""") {result -> println "result ${a} + ${b} = ${result}"}


result 100 + 50 = 150

不带inout的替代用法

db.call("""begin
 proc_x(${a},${b},${Sql.INTEGER});
end;""") {result -> println "result out ${a} + ${b} = ${result}"}

.

db.call("CALL proc_x(${a},${b},?)" , [Sql.INTEGER] ) 
    {result -> println "result out ${a} + ${b} = ${result}"}