cs.setString(1, checkUnit);// 设置输入参数的值
cs.setString(2, idCard);
cs.registerOutParameter(3,OracleTypes.VARCHAR);// 注册输出参数的类型 看看
cs.setString(2, idCard);
cs.registerOutParameter(3,OracleTypes.VARCHAR);// 注册输出参数的类型 看看
如果你非要用call那你得改成{?=call fn_fjxx_saftyreport (?,?)}这样才有三个参数,第一个参数就是用来接收你函数返回值的也是你的输出参数
CREATE OR REPLACE FUNCTION Fn_fjxx_saftyReport(checkUnit IN Varchar2,id_card IN Varchar2) return varchar2 is
--checkUnit 被检查单位,查询t_fjzz_safty_item的条件
--order 查询t_fjzz_safty_item的排序条件
--idcard 创建人的身份证号v_id Varchar2(500):='';--返回值
v_public number(4,1):=0;
v_special number(4,1):=0;
v_item_id t_fjzz_safty_itemscore.itemscr_sys_id%type;
v_grade_id t_fjzz_safty_grade.grade_sys_id%type;
v_uname Varchar2(20);
v_uname2 Varchar2(20);
item_record t_fjzz_safty_item%rowtype;
u_record t_fjzz_user_base%rowtype;
Cursor item_cursor Is
select * from t_fjzz_safty_item t where isuse='yes' and (use_unit is null or use_unit like '%'||checkUnit||'%') ;
Cursor user_cursor Is
select * from t_fjzz_user_base t where user_code=id_card;begin
select 'Sgrade'||SAFTYGRADE_ID.Nextval into v_grade_id from dual ;
for item_record in item_cursor loop select 'ITEMScr'||ITEMSCORE_ID.Nextval into v_item_id from dual;
insert into t_fjzz_safty_itemscore (itemscr_sys_id,total_type,type,item,standars,item_point,frequency,limit_time,day,item_orderno,GRADE_ID)
values (v_item_id,item_record.total_type,item_record.type,item_record.item,item_record.standars,item_record.point,item_record.frequency,item_record.limit_time,item_record.day,item_record.orderno,v_grade_id);
if v_id is null then v_id:=v_item_id;
else v_id:=v_id||','|| v_item_id;
end if;
if item_record.total_type ='公共部分' then v_public:=v_public+item_record.point;
end if;
if item_record.total_type ='行业特殊要求' then v_special:=v_special+item_record.point;
end if;
end loop;
select unit_name into v_uname from t_fjzz_unit_base where unit_code=checkUnit;
open user_cursor;
fetch user_cursor into u_record;
select unit_name into v_uname2 from t_fjzz_unit_base where unit_code=u_record.unit_code;
insert into t_fjzz_safty_grade t (GRADE_SYS_ID,unit_name,UNIT_CODE,CREATOR,ZONE_CODE,ZONE_NAME,CREATE_TIME,PUL_POINT,SPEC_POINT) values
(v_grade_id,v_uname,checkUnit,u_record.user_name,u_record.unit_code,v_uname2,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),v_public,v_special);
close user_cursor;
COMMIT;
return v_id; Exception
When OTHERS Then
Return substr(sqlerrm,1,100);end Fn_fjxx_saftyReport ;能否写清楚点,我是新手,不太懂
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select fn_fjxx_saftyreport (?,?)]; nested exception is java.sql.SQLException: ORA-00923: 未找到要求的 FROM 关键字
忘记了你是oracle,那你得这么用
String param2Value =getSimpleJdbcTemplate().getJdbcOperations().query("select fn_fjxx_saftyreport (?,?) from dual", new Object[]{checkUnit,idCard}, new ResultSetExtractor<String>(){
public String extractData(ResultSet rs) {
if(rs.next()) return rs.getString(1);
else return null;
}
}) ;
忘记了你是oracle,那你得这么用
String param2Value =getSimpleJdbcTemplate().getJdbcOperations().query("select fn_fjxx_saftyreport (?,?) from dual", new Object[]{checkUnit,idCard}, new ResultSetExtractor<String>(){
public String extractData(ResultSet rs) {
if(rs.next()) return rs.getString(1);
else return null;
}
}) ;
我那是函数,可以用select from dual吗
忘记了你是oracle,那你得这么用
String param2Value =getSimpleJdbcTemplate().getJdbcOperations().query("select fn_fjxx_saftyreport (?,?) from dual", new Object[]{checkUnit,idCard}, new ResultSetExtractor<String>(){
public String extractData(ResultSet rs) {
if(rs.next()) return rs.getString(1);
else return null;
}
}) ;
我那是函数,可以用select from dual吗
就因为你是oracle的函数才加from dual,这是oracle函数调用的sql写法,这个楼主不该问google就能知道了