grails调用存储过程(调用的时候call不能少了那个一对大括号{}，

Sql?mySql?=?new?Sql(dataSource_lookup)

def?=mySql.call("{call?test2(?)}",['650203199106272111'])

存储过程:

CREATE?OR?REPLACE?

PROCEDURE?"test2"(sfid?VARCHAR2)?AS

v_sf_id?T_BASE.SF_ID%type;

BEGIN

SELECT?sf_id?into?v_sf_id?from?T_BASE?where?SF_ID=sfid;

--?routine?body?goes?here,?e.g.

DBMS_OUTPUT.PUT_LINE('Navicat?for?Oracleaaaa'||v_sf_id);

END;

有一个问题最好把那个过程名的双引号去掉不然有时候会报错

?

?

带输入的存储过程：

Sql?mySql?=?new?Sql(dataSource_lookup)

def?ret

???????????mySql.call("{call?test2(?,?)}",['650203199106272111',mySql.VARCHAR]){ret=it}

println?"ret:>"+ret

?

CREATE?OR?REPLACE?

PROCEDURE?test2(sfid?VARCHAR2,names?OUT?VARCHAR2)?AS

v_sf_id?T_BASE.SF_ID%type;

?

BEGIN

SELECT?sf_id?into?names?from?T_BASE?where?SF_ID=sfid;

--?routine?body?goes?here,?e.g.

--DBMS_OUTPUT.PUT_LINE('Navicat?for?Oracleaaaa'||v_sf_id);

END;

?

?

?

?

?

?

?

带输入输出的游标存储过程:

返回集合的存储过程要用游标，而且要用带包的存储过程

包:

CREATE?OR?REPLACE?

PACKAGE?grails_package?AS?

type?grailsReture?is?ref?cursor;

/*?TODO?enter?package?declarations?(types,?exceptions,?methods?etc)?here?*/?

end?grails_package;

?

过程:

CREATE?OR?REPLACE?

PROCEDURE?grails_pak?(sex?IN?VARCHAR2,?grails_cursor?OUT?grails_package.grailsReture)

AS

BEGIN

open?grails_cursor?for?select?*?from?T_BASE?where?sex=sex;

--?routine?body?goes?here,?e.g.

--?DBMS_OUTPUT.PUT_LINE('Navicat?for?Oracle');

END;

?

Grails?调用:

Def?dataSource

Sql?mySql?=?new?Sql(dataSource_lookup)

def?ret

???????????mySql.call("{call?grails_pak(?,['男',mySql.resultSet(OracleTypes.CURSOR)]){carModel?->

//这里不能打印?carModel?会报错(Message:?org.apache.commons.dbcp.DelegatingCallableStatement?with?//address:?"oracle.jdbc.driver.OracleCallableStatementWrapper@1a1280c"?is?closed.)

carModel.eachRow(){x?->

println"car?style:?${x.sf_id},car?series:?${x.name},car?type:?${x.sj}"

???????????}?}