sql En Zong

create table TBL_MERCHANT_TEMP
(
  merch_sn               VARCHAR2(512) not null,
  merch_id               VARCHAR2(512),
  merch_name             VARCHAR2(512) not null,
  license_reg_no         VARCHAR2(512) not null,
  address                VARCHAR2(512) not null,
  registered_capital     VARCHAR2(512) not null,
  biz_scope              VARCHAR2(512),
  biz_period_from        VARCHAR2(512),
  biz_period_to          VARCHAR2(512) not null,
  legal_person_name      VARCHAR2(512) not null,
  legal_person_idc_no    VARCHAR2(512) not null,
  tax_cert_reg_no        VARCHAR2(512),
  tax_cert_validity      VARCHAR2(512),
  protocol_no            VARCHAR2(512),
  attachment_image       VARCHAR2(512),
  linkname_name          VARCHAR2(512),
  linkname_phone         VARCHAR2(512),
  merch_type             VARCHAR2(512) not null,
  accept_way             VARCHAR2(512) not null,
  trans_flag             VARCHAR2(512)not null,
  audit_flag             VARCHAR2(512) not null,
  stamp_datetime         VARCHAR2(512),
  settle_cycle           VARCHAR2(512) not null,
  discount_cycle         VARCHAR2(512),
  in_acc_no              VARCHAR2(512),
  in_acc_name            VARCHAR2(512),
  bank_no                VARCHAR2(512),
  bank_name              VARCHAR2(512),
  create_datetime        VARCHAR2(512) not null,
  update_datetime        VARCHAR2(512),
  audit_datetime         VARCHAR2(512),
  delete_datetime        VARCHAR2(512),
  create_operator_id     VARCHAR2(512),
  update_operator_id     VARCHAR2(512),
  audit_operator_id      VARCHAR2(512),
  delete_operator_id     VARCHAR2(512),
  area_code              VARCHAR2(512),
  mcc_code               VARCHAR2(512),
  organ_id               VARCHAR2(512),
  employee_sn            VARCHAR2(512),
  parent_merch_id        VARCHAR2(512),
  license_image          VARCHAR2(512),
  legal_person_idc_image VARCHAR2(512),
  legal_person_image     VARCHAR2(512),
  tax_cert_image         VARCHAR2(512),
  protocol_image         VARCHAR2(512),
  finance_list           VARCHAR2(512),
  resp_code              VARCHAR2(512),
  resp_inf               VARCHAR2(512),
  syn_count              VARCHAR2(512),
  syn_flag               VARCHAR2(512),
  syn_datetime           VARCHAR2(512),
  syn_operator_id        VARCHAR2(512),
  employee_name          VARCHAR2(512),
  primary_acc_no         VARCHAR2(512),
  primary_acc_name       VARCHAR2(512),
  acc_type               VARCHAR2(512),
  acc_flag               VARCHAR2(512),
  error_message          VARCHAR2(3000)
)

create table TBL_MERCHANT
(
  merch_sn               VARCHAR2(512) not null,
  merch_id               VARCHAR2(512),
  merch_name             VARCHAR2(512) not null,
  license_reg_no         VARCHAR2(512) not null,
  address                VARCHAR2(512) not null,
  registered_capital     VARCHAR2(512) not null,
  biz_scope              VARCHAR2(512),
  biz_period_from        VARCHAR2(512),
  biz_period_to          VARCHAR2(512) not null,
  legal_person_name      VARCHAR2(512) not null,
  legal_person_idc_no    VARCHAR2(512) not null,
  tax_cert_reg_no        VARCHAR2(512),
  tax_cert_validity      VARCHAR2(512),
  protocol_no            VARCHAR2(512),
  attachment_image       VARCHAR2(512),
  linkname_name          VARCHAR2(512),
  linkname_phone         VARCHAR2(512),
  merch_type             VARCHAR2(512) not null,
  accept_way             VARCHAR2(512) not null,
  trans_flag             VARCHAR2(512)not null,
  audit_flag             VARCHAR2(512) not null,
  stamp_datetime         VARCHAR2(512),
  settle_cycle           VARCHAR2(512) not null,
  discount_cycle         VARCHAR2(512),
  in_acc_no              VARCHAR2(512),
  in_acc_name            VARCHAR2(512),
  bank_no                VARCHAR2(512),
  bank_name              VARCHAR2(512),
  create_datetime        VARCHAR2(512) not null,
  update_datetime        VARCHAR2(512),
  audit_datetime         VARCHAR2(512),
  delete_datetime        VARCHAR2(512),
  create_operator_id     VARCHAR2(512),
  update_operator_id     VARCHAR2(512),
  audit_operator_id      VARCHAR2(512),
  delete_operator_id     VARCHAR2(512),
  area_code              VARCHAR2(512),
  mcc_code               VARCHAR2(512),
  organ_id               VARCHAR2(512),
  employee_sn            VARCHAR2(512),
  parent_merch_id        VARCHAR2(512),
  license_image          VARCHAR2(512),
  legal_person_idc_image VARCHAR2(512),
  legal_person_image     VARCHAR2(512),
  tax_cert_image         VARCHAR2(512),
  protocol_image         VARCHAR2(512),
  finance_list           VARCHAR2(512),
  resp_code              VARCHAR2(512),
  resp_inf               VARCHAR2(512),
  syn_count              VARCHAR2(512),
  syn_flag               VARCHAR2(512),
  syn_datetime           VARCHAR2(512),
  syn_operator_id        VARCHAR2(512),
  employee_name          VARCHAR2(512),
  primary_acc_no         VARCHAR2(512),
  primary_acc_name       VARCHAR2(512),
  acc_type               VARCHAR2(512),
  acc_flag               VARCHAR2(512)
)



 
insert into TBL_MERCHANT (merch_sn, merch_id, merch_name, license_reg_no, address, registered_capital, biz_scope, biz_period_from, biz_period_to, legal_person_name, legal_person_idc_no, tax_cert_reg_no, tax_cert_validity, protocol_no, attachment_image, linkname_name, linkname_phone, merch_type, accept_way, trans_flag, audit_flag, stamp_datetime, settle_cycle, discount_cycle, in_acc_no, in_acc_name, bank_no, bank_name, create_datetime, update_datetime, audit_datetime, delete_datetime, create_operator_id, update_operator_id, audit_operator_id, delete_operator_id, area_code, mcc_code, organ_id, employee_sn, parent_merch_id, license_image, legal_person_idc_image, legal_person_image, tax_cert_image, protocol_image, finance_list, resp_code, resp_inf, syn_count, syn_flag, syn_datetime, syn_operator_id, employee_name, primary_acc_no, primary_acc_name, acc_type, acc_flag)
values ('1409170000006343', '443701058110139', '加会尽快', '778', '1', 1, null, '2014-09-02', '2014-09-11', '1', '1', null, null, null, null, null, null, 1, 1, 1, 2, '2014-09-17 14:26:55', 2, 1, null, null, '1', '1', '2014-09-17 14:26:55', null, '2014-09-17 14:27:28', null, 'sysadmin', null, 'sysadmin', null, '7010', '5811', '0443', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 1, 1);
commit;
  

create or replace procedure get_value
AS
  merchId         VARCHAR2(512);
  countFlag       number(10);
  errorMessage    VARCHAR2(2000);
  type ref_cursor is ref cursor;
  cur_str         ref_cursor;
BEGIN           
      begin
          OPEN cur_str for select TBL_MERCHANT_TEMP.Merch_Id  from TBL_MERCHANT_TEMP;
             loop
                 fetch cur_str  into merchId;
                 exit when cur_str%notfound;
                    begin
                      select count(1) into countFlag from Tbl_Merchant where Merch_Id=merchId
                        DBMS_OUTPUT.PUT_LINE(1);
                        if(countFlag>0)
                               then
                                        errorMessage:=errorMessage|| '商户编号存在';
                                        update TBL_MERCHANT_TEMP set TBL_MERCHANT_TEMP.Error_Message=:errorMessage where  Merch_Id=merchId;   
                               else 
                                        insert into Tbl_Merchant select * from TBL_MERCHANT_TEMP where  Merch_Id=merchId;
                        end if;
                    end 
            end loop;
          close cur_str;         
end;

BEGIN
  get_value();
END;