工作笔记 2010-04-21之 sql

--新建一张表PROD_SUPPORT_MATERIAL_20110421备份表PROD_SUPPORT_MATERIAL里面的数据
create table PROD_SUPPORT_MATERIAL_20110421 as select * from PROD_SUPPORT_MATERIAL;

--在PROD_SUPPORT_MATERIAL表中增加申请时间APPLY_DATE字段
ALTER TABLE PROD_SUPPORT_MATERIAL ADD APPLY_DATE Date;
COMMENT ON COLUMN PROD_SUPPORT_MATERIAL.APPLY_DATE  IS '申请时间';


--把表PROD_SUPPORT_MATERIAL里的UNIT字段改为VARCHAR2()类型
ALTER TABLE PROD_SUPPORT_MATERIAL RENAME COLUMN UNIT TO UNIT_NEW;
ALTER TABLE PROD_SUPPORT_MATERIAL ADD UNIT VARCHAR2(20);
UPDATE PROD_SUPPORT_MATERIAL SET UNIT = CAST(UNIT_NEW AS VARCHAR2(20));
ALTER TABLE PROD_SUPPORT_MATERIAL DROP COLUMN UNIT_NEW;

查询并且可以修改数据
select * from prod_support_material for update

SimpleORM的sql
<sql id="dynamicSelect">
<dynamic prepend="WHERE">
    <isNotEmpty prepend="AND" property="ID">
   INSTR(#ID#,','||ID||',')>0
   </isNotEmpty>
<isNotEmpty prepend="AND" property="NAME">
NAME LIKE '%'||#NAME#||'%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="TYPE">
TYPE LIKE '%'||#TYPE#||'%'
</isNotEmpty>
</dynamic>
</sql>

id是字符串形式，相当于id in()
select * from FROM PROD_SUPPORT_MATERIAL  WHERE
instr(',C4ABF499-B7E0-0001-132E-DF7612F02170,9cb8653c-5ec4-47fb-adfa-39eb89c053b9,',','||id||',') >0