• db2存储过程小结


    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" onclick="showCheckMsg('''||trim(char(cust_code))||''','||trim(char(a.if_check))||')">驳                   回&rsaquo;&rsaquo;</font>';

    6.存储过程输出参数在java Action类中使用

    简写一个存储过程

     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 @
    View Code

    在 action中获取值

    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");
        }
    View Code
    一切美好的都是值得等待的。
  • 相关阅读:
    HTML5存储
    HTML5 地理位置定位(Geolocation)原理及应用
    HTML5多媒体组件的使用
    HTML5拖拽
    HTML5一些总结
    js高级程序设计--数据类型
    JS高级程序设计基本概念
    浏览器内核、渲染引擎、js引擎
    JS高级程序设计笔记之script标签
    CenOS7下安装 nginx
  • 原文地址:https://www.cnblogs.com/minghua-b/p/8184175.html
Copyright © 2020-2023  润新知