今天第一次写了一个存储过程费了好大劲,标记下来了
create or replace procedure updateCasUserInfo(repair_loginname in varchar2,
repair_password in varchar2,
repair_phone in varchar2,
repair_email in varchar2,
repair_unionid in varchar2,
resultNum out integer) is
s_sql varchar(5000) := '';
begin
s_sql := 'update base_user t set t.password =''' || repair_password ||
''', t.email =''' || repair_email || ''', t.phone =''' || repair_phone ||
''',t.open_id=''' || repair_unionid || ''' where t.loginname =''' ||
repair_loginname || '''';
execute immediate s_sql;
resultNum := sql%rowcount;
dbms_output.put_line(resultNum);
commit;
end;
///Java 代码调用
public int updateCasUser(User user, HttpServletRequest request) {
DbUtil dbUtil = new DbUtil();
ArrayList<String> strArray = new ArrayList<String>();
int returnVal = 0;
try {
Connection conn = dbUtil.getConnection("oracle", BaseData.SYSPARAMS.get("cas_db_url"), BaseData.SYSPARAMS.get("cas_db_name"), BaseData.SYSPARAMS.get("cas_db_psd"));
if (conn != null) {
Map<String, Object> map = new HashMap<>();
map.put("loginName", user.getLoginName());
User user2 = ckEmail(map);//
strArray.add(user.getLoginName());// 用户帐号
strArray.add((user.getPassword() == null || user.getPassword() == "") ? user2.getPassword() : user.getPassword());// 密码
strArray.add(user.getPhone());// 手机号
strArray.add(user.getEmail());// 邮箱
strArray.add(user.getUnionId());// 微信id
CallableStatement cstmt;
try {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < strArray.size(); i++) {// 根据list长度决定传入多少个参数
sb.append("?,");
}
cstmt = conn.prepareCall("{call updateCasUserInfo(" + sb + "?)}");// 最后一个新增的“?”是返回值
for (int i = 0; i < strArray.size(); i++) {
cstmt.setObject(i + 1, strArray.get(i));
}
cstmt.registerOutParameter(strArray.size() + 1, Types.INTEGER);//注册out参数
cstmt.execute();
returnVal = cstmt.getInt(strArray.size() + 1);// 得到返回值,1表示成功, 0表示失败
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
dbUtil.closeDB();
}
版权声明:本文为u010261944原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。