免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 5751 | 回复: 1

[讨论] 数据库泛型工具 [复制链接]

论坛徽章:
15
射手座
日期:2014-11-29 19:22:4915-16赛季CBA联赛之青岛
日期:2017-11-17 13:20:09黑曼巴
日期:2017-07-13 19:13:4715-16赛季CBA联赛之四川
日期:2017-02-07 21:08:572015年亚冠纪念徽章
日期:2015-11-06 12:31:58每日论坛发贴之星
日期:2015-08-04 06:20:00程序设计版块每日发帖之星
日期:2015-08-04 06:20:00程序设计版块每日发帖之星
日期:2015-07-12 22:20:002015亚冠之浦和红钻
日期:2015-07-08 10:10:132015亚冠之大阪钢巴
日期:2015-06-29 11:21:122015亚冠之广州恒大
日期:2015-05-22 21:55:412015年亚洲杯之伊朗
日期:2015-04-10 16:28:25
发表于 2015-09-29 11:19 |显示全部楼层
本帖最后由 yulihua49 于 2015-09-29 11:22 编辑

先看一下一个程序的执行日志:
5 RC_MID:7F5B69AD4700 09/25 13:09'55 _get_DB_connect tid=7F5B69AD4700,pool[0].1,USEC=3652232995609697
5 RC_MID:7F5B69AD4700 09/25 13:09'55 OAD_mk_update:sth=65536,stmt=UPDATE CMS_PROD.SUMMARY_FINANCIAL SET streaming_session_id=streaming_session_id WHERE device_id=:1 AND udsn=:2 AND txn_date_time=TO_DATE(:3,'YYYY/MM/DD HH24:MI:SS') RETURNING ROWID INTO :4,
5 RC_MID:7F5B69AD4700 09/25 13:09'55 to_summary:OAD_exec 1000 Recs,ret=1000 TM=60931
5 RC_MID:7F5B69AD4700 09/25 13:09'55 OAD_mk_ins sth=65536,INSERT INTO CMS_PROD.CUT_PI_FINANCIAL (streaming_session_id,data_version,txn_class,txn_revision,txn_ssn_a,txn_ssn_b,ols_txn_type,settlement_date,application_validation_flags,host_name,multipart_txn_count,issuer_abort_reason,acquirer_id,destination_participant_id,account_type,portioned_txn_flag,format_version,txn_date_time,source_participant_id,device_id,sam_id,udsn,service_participant_id,device_location,device_ssn,business_date,transaction_status,cd_set_version,reconciliation_date,ud_type,ud_subtype,device_home_depot,mass_installation_id,iss_exception_proc_abrtd,iss_exception,iss_txn_reflection,cch_flags_txn_portion,cch_flags_txn_summarised,cch_flags_txn_forwarded,cch_flags_txn_apportioned,cch_txn_good_for_summaries,cch_no_further_proc,cch_exception,cch_txn_not_to_issuer,cch_txn_approved,exception_list,card_issuer_id,card_serial_number,card_type,card_life_cycle_count,card_action_sequence_number,transaction_value,currency_indicator_fin_details,sales_tax,discount,tax_rate,tax_code,number_of_payments,payment_method,payment_value,partial_transaction_value,partial_sales_tax,product_issuer_id,product_serial_number,product_type,product_action_sequence_number,ptsn,invoice_printed,application_provider_id,application_serial_number,application_personalise_cat,app_action_sequence_number,application_type,application_passenger_type,key_version,purse_remaining_value,lav_sam_id,lav_participant_id,lav_date,lav_txn_value,lav_remaining_value,lav_ptsn,lav_amount_paid,lav_method_of_payment,data_is_valid,processed_date,claim_limit_1_date,claim_limit_2_date,claim_limit_1_currency,claim_limit_1_amount,claim_limit_2_currency,claim_limit_2_amount,claim_limit_1_reason_code,claim_limit_2_reason_code,receipt_number,num_rides,remaining_rides,number_of_entries,portion_to_participant_id,portion_value,lav_remaining_rides,v_start_date_time,v_end_date_time,v_duration,v_distance_origin,v_distance_destination,v_distance_distance,v_journey_period_journeys,v_journey_transfers,v_route_routes,v_location_locations,restrictions_day,restrictions_time,v_period_duration,v_origin,v_destination,aimi_tsn,staff_id,machine_id,full_value,product_purchase_type,number_of_passengers,route_line_id,route_direction,passenger_type,journey_type,current_location,number_of_zones,previous_operator,trip_origin_location,trip_previous_location,cardholder_serial_num,cardholder_issuer_id,company_id,classification_level,cardholder_title,cardholder_name,cardholder_phone_day,address_1,address_2,address_3,address_4,address_5,card_refund_method,refund_reason,payment_requestor_id,payment_request_id,pass_end_date_time,lav_pass_expiry_date_time,ticket_serial_number,shift_number,shift_start_time,toras_code,entitlement_id,invoice_not_printed,split_card_issuer_id,split_card_serial_number,split_card_type,split_card_life_cycle_count,split_app_serial_number,split_product_issuer_id,split_product_serial_number,eft_terminal_id,eft_trace_number,eft_authorisation_status,eft_response_advice,eft_merchant,eft_payment_details,eft_card_type,eft_settlement_date,eft_acquirer_id,eft_reversal_flag,value_write_off_amount,deposit_write_off_amount,currency_indicator,reason_code,value_per_ride,reversal_reason_code,via_actionlist,invalid_card_serial_number,invalid_product_issuer,invalid_life_cycle_count,invalid_product_type,invalid_card_issuer_id,invalid_card_type,invalid_app_issuer_id,invalid_app_serial_number,invalid_product_serial_number,reversed_udsn,recredit_reason,entry_time,reversal_reason,adjustment_amount,surcharge_details,lower_ptsn,upper_ptsn,compensation_reason,should_pay_value,encrypt_flag,purse_serialno,terminate_serialno,crc_version,crc_code,integral_startdate,trans_value_before,trans_value_after,discount_params,discount_cardtype,discount_switch,discount_lowpeak,discount_value_sum,integral_start_value,lowpeak_start,lowpeak_end,trans_value_before_adjust,city_industry_code) VALUES ( :1, :2, :3, :4, :5, :6, :7,TO_DATE(:8,'YYYY/MM/DD'), :9, :10, :11, :12, :13, :14, :15, :16, :17,TO_DATE(:18,'YYYY/MM/DD HH24:MI:SS'), :19, :20, :21, :22, :23, :24, :25,TO_DATE(:26,'YYYY/MM/DD'), :27, :28,TO_DATE(:29,'YYYY/MM/DD'), :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78,TO_DATE(:79,'YYYY/MM/DD'), :80, :81, :82, :83, :84, :85,TO_DATE(:86,'YYYY/MM/DD'),TO_DATE(:87,'YYYY/MM/DD'),TO_DATE(:88,'YYYY/MM/DD'), :89, :90, :91, :92, :93, :94, :95, :96, :97, :98, :99, :100, :101,TO_DATE(:102,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:103,'YYYY-MM-DD HH24:MI:SS'), :104, :105, :106, :107, :108, :109, :110, :111, :112, :113, :114, :115, :116, :117, :118, :119, :120, :121, :122, :123, :124, :125, :126, :127, :128, :129, :130, :131, :132, :133, :134, :135, :136, :137, :138, :139, :140, :141, :142, :143, :144, :145, :146, :147,TO_DATE(:148,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:149,'YYYY-MM-DD HH24:MI:SS'), :150, :151,TO_DATE(:152,'YYYY-MM-DD HH24:MI:SS'), :153, :154, :155, :156, :157, :158, :159, :160, :161, :162, :163, :164, :165, :166, :167, :168, :169,TO_DATE(:170,'YYYY/MM/DD'), :171, :172, :173, :174, :175, :176, :177, :178, :179, :180, :181, :182, :183, :184, :185, :186, :187, :188, :189, :190,TO_DATE(:191,'YYYY/MM/DD HH24:MI:SS'), :192, :193, :194, :195, :196, :197, :198, :199, :200, :201, :202, :203, :204, :205, :206, :207, :208, :209, :210, :211, :212, :213, :214, :215, :216)
5 RC_MID:7F5B69AD4700 09/25 13:09'56 array_ins_DAO:tabname=CUT_PI_FINANCIAL,rec_num=1000,ret=1000,err=0,,TM=754434
5 RC_MID:7F5B69AD4700 09/25 13:09'56 release_DB_connect tid=7F5B69AD4700,pool[0].1,USEC=3652232996431035
3 RC_MID:7F5B69AD4700 09/25 13:09'56 udapp:Version 7.1 process 1000 Rec's INTERVAL=912688

好多的date列,各种不同的格式。还是需要对每个date列分别定义缺省格式,才会比较好用。
现在好多人回避使用date类型,就是嫌其不方便。

这么多列的表,还可能会增加一些列。
写这个语句,绑定那么多变量,人会疯掉的啊。。。。。
幸亏,这不是我写的,是程序自动生成的。变量是自动绑定的。。。OCI包装器。。。类似的表还有好几个,由一个程序处理的,柔性编程,或者说数据库泛型编程,纯C的,谁说C不能泛型?典型的sqlldr,我也能。

5 RC_MID:7F5B69AD4700 09/25 13:09'54 array_ins_DAO:tabname=CUT_PI_EXIT,rec_num=1000,ret=1000,err=0,,TM=146911同一个函数(array_ins_DAO)处理的另一张表。这个泛型技术,不同于STL,它那个是编译时泛型,就是在编译期必须知道数据类型,而这个是运行时泛型,编译时你并不知道具体的数据类型,执行时才知道。

好处,一个函数可以处理多个表。表结构改变时程序无需修改。
语句不会写错。

论坛徽章:
15
射手座
日期:2014-11-29 19:22:4915-16赛季CBA联赛之青岛
日期:2017-11-17 13:20:09黑曼巴
日期:2017-07-13 19:13:4715-16赛季CBA联赛之四川
日期:2017-02-07 21:08:572015年亚冠纪念徽章
日期:2015-11-06 12:31:58每日论坛发贴之星
日期:2015-08-04 06:20:00程序设计版块每日发帖之星
日期:2015-08-04 06:20:00程序设计版块每日发帖之星
日期:2015-07-12 22:20:002015亚冠之浦和红钻
日期:2015-07-08 10:10:132015亚冠之大阪钢巴
日期:2015-06-29 11:21:122015亚冠之广州恒大
日期:2015-05-22 21:55:412015年亚洲杯之伊朗
日期:2015-04-10 16:28:25
发表于 2015-09-29 11:25 |显示全部楼层
本帖最后由 yulihua49 于 2015-09-29 11:36 编辑
yulihua49 发表于 2015-09-29 11:19
先看一下一个程序的执行日志:
5 RC_MID:7F5B69AD4700 09/25 13:09'55 _get_DB_connect tid=7F5B69AD4700, ...

见识一下这个泛型函数:需要一个特殊的数据结构DAU,内定义了表及表结构模板,是先前从数据库字典读出的

  1. //返回插入成功的条数,<0出错
  2. static int array_ins_DAO(DAU *dp,char *recs,int rec_num,char *stmt)
  3. {
  4. int ret,cc;
  5. OAD oad;//Oracle Array Describe
  6. INT64 now=now_usec();
  7.         OAD_init(&oad,dp,recs,rec_num);//分配空间
  8.         *stmt=0;
  9.         ret=OAD_mk_ins(&oad,stmt);//生成语句和绑定集
  10.         if(!ret) {
  11.                 ret=OAD_exec(&oad,0,rec_num);//绑定变量,执行批量插入
  12.                 if(ret!=rec_num) {
  13.                         cc=single_ins(dp,recs,ret,rec_num,stmt);
  14.                         ret=ret>=0?ret+cc:cc;
  15.                 }
  16.         }
  17.         OAD_free(&oad);//析构
  18.         ShowLog(5,"%s:tabname=%s,rec_num=%d,ret=%d,err=%d,%s,TM=%u",__FUNCTION__,
  19.                         dp->srm.tabname,rec_num,ret,
  20.                         dp->SQL_Connect->Errno,dp->SQL_Connect->ErrMsg,
  21.                         INTERVAL(now));
  22.         return ret;
  23. }
复制代码
一个复杂的泛型批量插入操作,只需4步就完成了。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
  

北京盛拓优讯信息技术有限公司. 版权所有 16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122
中国互联网协会会员  联系我们:huangweiwei@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP