Oracle某些功能实现语句处理方法

触发器以及序列
CREATE OR REPLACE TRIGGER t_MSTB_BDS_DATA BEFORE INSERT ON  MSTB_BDS_DATA  FOR EACH ROW
DECLARE
  INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN

 SELECT SEQ_MSTB_BDS_DATA.NEXTVAL INTO :NEW.data_id FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);

END ;


create sequence SEQ_MSTB_BDS_DATA
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 21;

 

插入HTML格式语句实现方法

某字段存数据为以下格式：<input type='radio'  name='ids'  value='$0'/>
insert into MSTB_BDS_DATA (DATA_ID, NAME_TC ) values('16', '<input type='||'''radio''  name='||'''ids''  value='||'''$0''/>')

格式为这样的<img src='afw/common/images/table/basic_blue/filter.gif'/>

实现如下：
insert into MSTB_BDS_DATA ( NAME_TC) values('<img src='||'''afw/common/images/table/basic_blue/filter.gif''/>');
insert into MSTB_BDS_DATA ( NAME_TC) values('<textarea cols='||''''' rows='||'''''>);

 

MySQL的级联更新：
UPDATE BLC_DrawRcd tt1
            INNER JOIN BLC_DrawCash tt2  ON tt1.TradeSysWater = tt2.TradeSysWater
            SET tt1.YsBackToDjAcc = '12' ,tt1.YsBackDebit = p_tmp_water_str_hz 
            WHERE tt2.BalanceStatus = '09' 
                                AND tt2.TradeStatus = 2
                                AND tt1.DrawBanckAccountType <> '01'  
                                AND tt1.YsResult = '9'
                                AND    to_char(tt2.TradeTime,'yyyyMMdd') < p_tmp_now_day 
                                AND tt2.Memo='01' AND  tt1.YsBackToDjAcc = '11'

Oracle的级联更新：
 UPDATE BLC_DrawRcd tt1  SET tt1.YsBackToDjAcc = '12' ,tt1.YsBackDebit = p_tmp_water_str_hz 
                where exists(select * from BLC_DrawCash tt2  where  tt1.TradeSysWater = tt2.TradeSysWater
                 AND tt2.TradeStatus = 2
                                AND tt1.DrawBanckAccountType <> '01'  
                                AND tt1.YsResult = '9'
                                AND    to_char(tt2.TradeTime,'yyyyMMdd') < p_tmp_now_day 
                                AND tt2.Memo='01' AND  tt1.YsBackToDjAcc = '11'  -- 待退款
                );

 

MySQL case 用法

UPDATE  Meb_UserLogOff  tt1  
							INNER JOIN BLC_DrawRcd tt2
							ON tt1.UserLogOffId = tt2.QmLogOffID
							AND  tt2.DrawToAccRspFile = pin_fName_Ret  AND tt2.DrawBanckAccountType ='01'  
			SET LogOffStatus = CASE tt2.YsResult WHEN '0' THEN '10'

　　

oracle   case when 用法
UPDATE  Meb_UserLogOff  tt1  
        SET LogOffStatus =( select CASE WHEN tt2.YsResult ='0' THEN '10'   END  from BLC_DrawRcd tt2
        where tt1.UserLogOffId = tt2.QmLogOffID
                            AND  tt2.DrawToAccRspFile = pin_fName_Ret  AND tt2.DrawBanckAccountType ='01')
         where exists(select 1 from BLC_DrawRcd tt2  where  tt1.UserLogOffId = tt2.QmLogOffID
         AND  tt2.DrawToAccRspFile = pin_fName_Ret  AND tt2.DrawBanckAccountType ='01' );