+ibatis oracle +case when 使用,IBATIS2.0映射文件 oracle/mysql 版实现示例，功能全面且丰富...

HUGS_ID, HUGS_NAME, HUGS_BRAND, HUGS_VERSION, HUGS_PRODUCT_DATE, HUGS_ASSET_CODE,

HUGS_PRICE, HUGS_DEPT_ID, HUGS_DEPT_NAME, HUGS_USER_ID, HUGS_USER_NAME, HUGS_USE_RESON,

HUGS_USE_STATUS, HUGS_DESCR, HUGS_STATUS, HUGS_CREATOR, HUGS_CREATE_TIME, HUGS_UPDATE,

HUGS_UPDATE_TIME,HUGS_COMPANY

AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'

AND HUGS_NAME LIKE '%$HUGS_NAME$%'

AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'

AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'

AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'

AND HUGS_COMPANY = #HUGS_COMPANY#

SELECT COUNT(*) FROM HU_GOODS

SELECT * FROM

(

SELECT

A .*, ROWNUM r

FROM

(

SELECT

FROM HU_GOODS

ORDER BY $sortField$ $sortOrder$

) A

WHERE

ROWNUM #end#

) B

WHERE r ]]> #start#

SELECT

(

SELECT

WM_CONCAT (

EM.HUGI_DESCR || ' ' ||'变更时间：'||TO_CHAR(EM.HUGI_CHANGE_DATE,'YYYY-MM-DD')

)

FROM

HU_GOODS_ITEM EM

WHERE

EM.HUGI_GOODS_ID = GOODS.HUGS_ID

) AS ITEMS,

GOODS.HUGS_NAME,

GOODS.HUGS_BRAND,

GOODS.HUGS_VERSION,

GOODS.HUGS_PRODUCT_DATE,

GOODS.HUGS_ASSET_CODE,

GOODS.HUGS_PRICE,

GOODS.HUGS_DEPT_NAME,

GOODS.HUGS_USER_NAME,

GOODS.HUGS_USE_RESON,

GOODS.HUGS_DESCR,

GOODS.HUGS_COMPANY,

CASE GOODS.HUGS_USE_STATUS

WHEN 1 THEN

'使用中'

WHEN 2 THEN

'在库'

ELSE

'作废'

END HUGS_USE_STATUS

,CASE GOODS.HUGS_CHECK_STATUS

WHEN 0 THEN

'尚未盘点'

WHEN -1 THEN

'问题物资'

ELSE

'盘点无误'

END HUGS_CHECK_STATUS

FROM

HU_GOODS GOODS

ORDER BY

GOODS.HUGS_ID ASC

SELECT OG.HUGS_COMPANY FROM HU_GOODS OG

WHERE OG.HUGS_ID IN(

SELECT MAX(GOOD.HUGS_ID) FROM HU_GOODS GOOD

GROUP BY GOOD.HUGS_COMPANY

)

ORDER BY OG.HUGS_ID ASC

SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL

INSERT INTO HU_GOODS (

HUGS_ID,

HUGS_NAME,

HUGS_BRAND,

HUGS_VERSION,

HUGS_PRODUCT_DATE,

HUGS_ASSET_CODE,

HUGS_PRICE,

HUGS_DEPT_ID,

HUGS_DEPT_NAME,

HUGS_USER_ID,

HUGS_USER_NAME,

HUGS_USE_RESON,

HUGS_USE_STATUS,

HUGS_DESCR,

HUGS_STATUS,

HUGS_CREATOR,

HUGS_CREATE_TIME,

HUGS_UPDATE,

HUGS_UPDATE_TIME,

HUGS_COMPANY

)

VALUES

(

#HUGS_ID#,

#HUGS_NAME#, #HUGS_BRAND#,

#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE,

#HUGS_UPDATE#,

SYSDATE,

#HUGS_COMPANY#

)

UPDATE HU_GOODS SET

HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,

HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE

where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#

SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE

HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#

SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE

HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#

HUGI_ID, HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME, HUGI_USER_ID, HUGI_USER_NAME,

HUGI_USE_RESON, HUGI_USE_STATUS, HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,

HUGI_UPDATE, HUGI_UPDATE_TIME

AND HUGI_GOODS_ID = #HUGI_GOODS_ID#

AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'

AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'

AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'

SELECT COUNT(*) FROM HU_GOODS_ITEM

SELECT * FROM

(

SELECT

A .*, ROWNUM r

FROM

(

SELECT

FROM HU_GOODS_ITEM

ORDER BY $sortField$ $sortOrder$

) A

WHERE

ROWNUM #end#

) B

WHERE r ]]> #start#

SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL

INSERT INTO HU_GOODS_ITEM (HUGI_ID,HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,

HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,

HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,

HUGI_UPDATE, HUGI_UPDATE_TIME)

VALUES (#HUGI_ID#,#HUGI_GOODS_ID#, #HUGI_DEPT_ID#, #HUGI_DEPT_NAME#,

#HUGI_USER_ID#, #HUGI_USER_NAME#, #HUGI_USE_RESON#, #HUGI_USE_STATUS#,

#HUGI_DESCR#, #HUGI_STATUS#, #HUGI_CREATOR#, SYSDATE,

#HUGI_UPDATE#, SYSDATE)

DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#