触发器以及序列
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' );