oracle insert commit,循环insert多条语句后commit丢失数据

最新推荐文章于 2025-04-30 09:52:29 发布

韭菜实验室

最新推荐文章于 2025-04-30 09:52:29 发布

阅读量1k

点赞数

文章标签： oracle insert commit

本文展示了一种批量插入数据到数据库的方法，通过循环处理并利用SQL语句实现多个表的数据插入，包括recipe_info、recipe_item及recipe表。此外，还涉及了与检验申请单相关的业务逻辑处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

for i=1 to ceiling(li_count/15)

g_n_set.of_get_ops_recipe_no(ls_recipe_no)

lstr_2[i].patient_no = dw_1.object.patient_no[1]

lstr_2[i].serial_no = dw_1.object.serial_no[1]

lstr_2[i].recipe_no = ls_recipe_no

lstr_2[i].recipe_times = dec('0')

lstr_2[i].create_dept = g_dept_id

lstr_2[i].create_dr = g_user_id

lstr_2[i].create_date = ida_create_date

lstr_2[i].create_user = g_user_id //全局用户.

if li_count - i*15>=0 then

li_row2=15

else

li_row2=li_count - (i - 1)*15

end if

for k = 1 to li_row2

li_row= k + (i - 1)*15

lstr_1[k].recipe_no = ls_recipe_no

lstr_1[k].recipe_times = '0'

lstr_1[k].item_id = dw_3.object.code_item_item_id[li_row]

lstr_1[k].item_name = dw_3.object.code_item_item_name[li_row]

lstr_1[k].spec = dw_3.object.code_item_spec[li_row]

lstr_1[k].unit = dw_3.object.code_item_unit[li_row]

lstr_1[k].usage_id = dw_3.object.code_item_usage_id[li_row]

lstr_1[k].oral_id = dw_3.object.code_item_oral_id[li_row]

lstr_1[k].decoct_id = dw_3.object.code_item_decoct_id[li_row]

lstr_1[k].dosage = dw_3.object.code_item_dosage[li_row]

lstr_1[k].dosage_unit = dw_3.object.code_item_dosage_unit[li_row]

lstr_1[k].frq_id = dw_3.object.code_item_frq_id[li_row]

lstr_1[k].days = dw_3.object.compute_0014[li_row]

lstr_1[k].days_unit = dw_3.object.code_item_unit[li_row]

lstr_1[k].test_sign = dw_3.object.code_item_test_sign[li_row]

lstr_1[k].confirm_dept = dw_3.object.check_record_dept_id[li_row]

lstr_1[k].unit_price = dw_3.object.code_item_unit_price[li_row]

lstr_1[k].qty = dw_3.object.compute_0014[li_row]

lstr_1[k].group_no = '1'

//结算内容赋值

lstr_1[k].balance_sign = 'N'

setnull(lstr_1[k].balance_date)

setnull(lstr_1[k].balance_user)

setnull(lstr_1[k].balance_no)

//确认信息

if dw_3.object.check_record_dept_id[li_row] <> g_dept_id then

lstr_1[k].confirm_sign = 'N'

setnull(lstr_1[k].confirm_date)

setnull(lstr_1[k].confirm_user)

else

lstr_1[k].confirm_sign = 'Y'

lstr_1[k].confirm_date = ida_create_date

lstr_1[k].confirm_user = g_user_id

end if

INSERT INTO ops_recipe_info

VALUES(

:lstr_1[k].recipe_no,:lstr_1[k].recipe_times,:lstr_1[k].group_no,:lstr_1[k].item_id,:lstr_1[k].item_name,

:lstr_1[k].spec,:lstr_1[k].unit,:lstr_1[k].usage_id,:lstr_1[k].oral_id,:lstr_1[k].decoct_id,

:lstr_1[k].dosage,:lstr_1[k].dosage_unit,:lstr_1[k].frq_id,:lstr_1[k].days,:lstr_1[k].days_unit,

:lstr_1[k].test_sign,:lstr_1[k].confirm_dept)

USING SQLCA;

if SQLCA.SQLCODE <> 0 then

ROLLBACK USING SQLCA;

return 2

end if

INSERT INTO ops_recipe_item

VALUES(

:lstr_1[k].recipe_no,:lstr_1[k].recipe_times,:lstr_1[k].group_no,:lstr_1[k].item_id,

:lstr_1[k].item_name,:lstr_1[k].spec,:lstr_1[k].unit,

:lstr_1[k].unit_price , :lstr_1[k].qty , :lstr_1[k].unit_price * :lstr_1[k].qty,

:lstr_1[k].balance_sign , :lstr_1[k].balance_date ,:lstr_1[k].balance_user , :lstr_1[k].balance_no ,

:lstr_1[k].confirm_sign , :lstr_1[k].confirm_date ,:lstr_1[k].confirm_user, :lstr_1[k].confirm_dept )

USING SQLCA;

if SQLCA.SQLCODE <> 0 then

ROLLBACK USING SQLCA;

return 2

end if

INSERT INTO ops_recipe

VALUES(

:lstr_2[i].patient_no,:lstr_2[i].serial_no,

:lstr_2[i].recipe_no,:lstr_2[i].recipe_times,

:lstr_2[i].create_dept,:lstr_2[i].create_room,

:lstr_2[i].create_dr,:lstr_2[i].create_date,:lstr_2[i].create_user)

USING SQLCA;

if SQLCA.SQLCODE <> 0 then

ROLLBACK USING SQLCA;

return 2

end if

//2006.03.10 lis_com

long ll_po_req_no

dec ld_qty

string ls_p_reg_no,ls_p_rec_no,ls_p_req_time,ls_dept_id,ls_item_id1

ld_qty=dw_3.object.compute_0014[li_row]

ls_dept_id=dw_3.object.check_record_dept_id[li_row]

ls_item_id1=dw_3.object.code_item_item_id[li_row]

IF g_lis_sign='Y' THEN

int li_return

if ld_qty > 0 then

if ls_dept_id='032' then

ls_p_reg_no=trim(ls_recipe_no)+'-'+'0'

ls_p_rec_no=trim(ls_recipe_no)+'-'+'0'+'-'+trim(ls_item_id1)

ls_p_req_time=string(ida_create_date,'yyyy-mm-dd hh:mm:ss')

ll_po_req_no=0

li_return=g_n_set.of_lis_com(ll_po_req_no,'T','3',str_ops_register_item[1].patient_no,ls_p_reg_no,ls_p_rec_no,ls_item_id1,'0',g_user_id,g_dept_id,ls_p_req_time,'',str_ops_patient.name,'','','','',g_user_id,'')

if li_return=-1 then

messagebox('系统提示','生成检验申请单失败，请与系统员联系！')

// cb_clear.setfocus()

return 2

end if

//2006.03.10 lis_com

END IF

//commit 放在这个位置数据就都会保存

commit;(原程序commit位置)

return 0