1.distinct 去掉重复的值,保证值唯一
2. 临时表字段默认字体颜色 if_check_cn VARCHAR(500) DEFAULT '<font color="blue">可以替换</font>' --字体颜色为蓝色
3.如果a表中phone_number为空,则取b表的 phone_number;用coalesce()函数
coalesce(nullif(a.phone_number,''),b.phone_number)
4.向临时表插入数据
insert into session.result(临时表字段 )
select .(字段)...from .(表a)..left join .(表b)..on(a.字段=b.字段)(也可添加条件 and a.字段=xx) left join ..(可join 多个表)
where (条件) and ,order by xx;
5.向临时表中merge数据
(1) merge into session.result a
using(select code from c_wx where..)b
on a.code=b.code
when matched then update
set a.price=1, a.price_cn='是',
a.check_cn ='<font color="red" title="点击查看驳回意见" class="more-info" οnclick="showCheckMsg('''||trim(char(cust_code))||''','||trim(char(a.if_check))||')">驳 回››</font>';
6.存储过程输出参数在java Action类中使用
简写一个存储过程
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 DROP PROCEDURE GetInfo 2 @ 3 CREATE PROCEDURE GetInfo 4 ( 5 IN p_code INT, 6 OUT p_rejectInfo VARCHAR(100) 7 ) 8 LANGUAGE SQL 9 DYNAMIC RESULT SETS 0 10 11 BEGIN 12 13 DECLARE SQLCODE INT; 14 DECLARE SQLSTATE CHAR(5); 15 DECLARE v_errCode INT DEFAULT 0; 16 DECLARE v_errState CHAR(5) DEFAULT '00000'; 17 DECLARE v_save_state INT DEFAULT 0; 18 DECLARE v_rejectComm VARCHAR(100); 19 20 -------------------------------------------------------- 21 -- DECLARE ERROR HANDLERS 22 -------------------------------------------------------- 23 24 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 25 BEGIN 26 SET v_errCode = SQLCODE; 27 SET v_errState = SQLSTATE; 28 END; 29 30 31 SELECT reject_comm INTO v_rejectComm 32 FROM g_info b 33 LEFT JOIN DATA_LOG a 34 ON a.check_person_id = b.person_id 35 WHERE a.code=p_code; 36 37 SET p_rejectInfo = v_rejectComm; 38 IF v_errCode <> 0 AND v_errCode <> 100 39 THEN 40 SET v_save_state = 1; 41 GOTO QuitProc; 42 END IF; 43 GOTO EndProc; 44 45 QuitProc: 46 ROLLBACK; 47 48 EndProc: 49 RETURN v_errCode; 50 END 51 @
在 action中获取值
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public ActionForward checkMsgShow(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { CallHelper helper = new CallHelper("GetInfo"); helper.setParam("code", request.getParameter("code")); helper.execute(); request.setAttribute("rejectInfo", (String) helper.getOutput("rejectInfo")); return mapping.findForward("checkMsgShow"); }