Oracle-mapper文件 And PL/SQL中的-批量-增 删 改 查

增：Add

 

PL/SQL客户端：

//批量添加

--字段一样多，命名可以不一样

--PA_TEMP_MATERIAL_DEAL表是PA_MATERIAL_DEAL表的临时表

insert intoPA_TEMP_MATERIAL_DEAL select * from PA_MATERIAL_DEAL;

 

--字段不一样多

insert intoPA_TEMP_MATERIAL_DEAL

(material_number,provider_code,provider_name,promise_date,num_segpoints,unit_price,currency_code,work_code)

selectimlitm,provider_code,provider_name,promise_date,num_segpoints,unit_price,currency_code,''from PA_MATERIAL_DEAL;

 

mapper文件：

//批量添加

//Mapper文件中的code

<insertid="addEstPriceInfo" parameterType="java.util.List">

insert intoPA_NOCALAPPLYORDER_ESTPRICE

(

order_number,

order_type,

order_company,

order_line,

provider_code,

provider_name,

work_code,

expected_date,

estimate_price,

create_estimate_time

)

<foreachcollection="noCalApplyOrderPOList" item="item"index="index" separator="union" open="("close=")">

(

select

#{item.order_number},

#{item.order_type},

#{item.order_company},

#{item.order_line},

#{item.provider_code},

#{item.provider_name},

#{item.work_code},

#{item.expected_date},

#{item.estimate_price},

to_char(sysdate,'yyyy-MM-ddHH24:mi:ss')

from dual

)

</foreach>

</insert>

 

//mapper代码转化为PL/SQL代码

insertintoPA_TEMP_MATERIAL_DEAL

( material_number,provider_code, currency_code,work_code )

(

 ( select 'SPCL','900000','USD','222' fromdual)

 union

 ( select 'SPCL','900000','CNY','222' fromdual)

)

 

 

删：Delete

 

PL/SQL客户端：

//批量删除

(同mapper代码转化为</