Oracle>>通過PL/SQL程式塊判斷，指定用戶的指定數據表和序列是否存在，如果存在則刪除，否則新創建。並且為表添加含有字元串數據，execute immediate執行insert語句中有字元串解決辦法

--變數聲明 declare num0 number; num1 number; num2 number; num3 number; num4 number; num5 number; num6 number; num7 number; num8 number; num9 number; num10 ...

--變數聲明
declare 
      num0   number;
      num1   number;
begin 
      --判斷用戶為：SYSTEM的資料庫中是否存在相關數據表和序列，存在則刪除，不存在則新創建，DBMS_OUTPUT.put_line等同java中的System.out.println輸出語句

      DBMS_OUTPUT.put_line('正在初始化資料庫信息，請稍後......');
      --以下參數說明：
      --所涉及到的參數值必須為大寫
      --sequence_owner：指定哪個資料庫用戶的序列；  sequence_name：指定的序列名稱
      --TABLE_NAME：指定數據表的名稱;     OWNER:指定哪個資料庫用戶的數據表

      --查詢資料庫用戶為SYSTEM並且序列名稱為HIBERNATE_SEQUENCE，查詢到的值進行統計【用count(*)統計】，然後將其賦給變數num0
      select count(*) into num0 from dba_sequences where sequence_owner='SYSTEM' and sequence_name  = 'HIBERNATE_SEQUENCE';
      
      --查詢資料庫用戶為SYSTEM並且數據表名稱為CRM_CUSTCONTACTRECORD，查詢到的值進行統計【用count(*)統計】，然後將其賦給變數num1
      select count(*) into num1 from all_tables where TABLE_NAME = 'CRM_CUSTCONTACTRECORD' and OWNER='SYSTEM';
      
      --進行判斷，如果存在數據表或序列，則通過execute immediate ’[這裡是要執行的語句或者其它]’；
      --這裡執行刪除，“drop table [數據表名] cascade constraints”；該語句將刪除相關主外建，刪除序列語法不贅述；
      if   num0=1   then 
          execute immediate 'drop sequence HIBERNATE_SEQUENCE';
      end if;
      if   num1=1   then 
          execute immediate 'drop table CRM_CUSTCONTACTRECORD cascade constraints';
      end if;
      --創建數據表,不在贅述
      DBMS_OUTPUT.put_line('初始化資料庫信息完畢！正在創建相關數據表，請稍後.....');
     if true then 
           execute immediate 'create sequence HIBERNATE_SEQUENCE
                minvalue 10
                maxvalue 999999999999999999999999
                start with 10
                increment by 1
                cache 20';
           execute immediate 'create table CRM_CUSTCONTACTRECORD (
                ID number(10,0) not null,
                CONTACT_TIME varchar2(255 char),
                CONTACT_ADDRESS varchar2(255 char),
                SUMMARY varchar2(255 char),
                REMARK varchar2(255 char),
                DETAIL varchar2(255 char),
                CUST number(10,0),
                primary key (ID)
            )';
            --添加主鍵與外鍵
             DBMS_OUTPUT.put_line('數據表已創建完畢！正在進一步為數據表設置主鍵和外鍵，請稍後......');
            execute immediate 'alter table CRM_CUSTCONTACTRECORD 
                add constraint FK_kuieobwbi2obpgukx7sa30iyh 
                foreign key (CUST) 
                references CRM_CUSTINFO';
 --以下的操作是添加測試數據
        --添加數據表的數據時，要註意：若通過以下execute immediate來執行，如果insert語句中要插入的值value中含有字元串，那麼必須在字元串外用倆個單引號
        --例如： 
　　　　　　　　

　　　　 --該語句中，admin之前用兩個單引號，admin之後也是如此；
        --原因是execute immediate後面已經跟了字元串，所以加倆個單引號，如同字元串相連
            DBMS_OUTPUT.put_line('數據表的主外建設置完成！正在添加測試數據，請稍後.....');
           --用戶信息
            execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE)
                values (1, ''admin'', ''admin'', 1)';
            execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE)
                values (2, ''Vincent'', ''111'', 2)';
            execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE)
                values (3, ''Milke'', ''111'', 3)';
            execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE)
                values (4, ''Catherine'', ''111'', 4)';
            DBMS_OUTPUT.put_line('測試數據已成功添加');
    end if;
    --提交事務
    commit;
     
end;

--變數聲明 declare num0 number; num1 number; num2 number; num3 number; num4 number; num5 number; num6 number; num7 number; num8 number; num9 number; num10 number; num11 number; num12 number; num13 number; num14 number; num15 number; begin --判斷用戶為：SYSTEM的資料庫中是否存在相關數據表和序列，存在則刪除，不存在則新創建 DBMS_OUTPUT.put_line('正在初始化資料庫信息，請稍後......'); select count(*) into num0 from dba_sequences where sequence_owner='SYSTEM' and sequence_name = 'HIBERNATE_SEQUENCE'; select count(*) into num1 from all_tables where TABLE_NAME = 'CRM_CUSTCONTACTRECORD' and OWNER='SYSTEM'; select count(*) into num2 from all_tables where TABLE_NAME = 'CRM_CUSTCONTACTS' and OWNER='SYSTEM'; select count(*) into num3 from all_tables where TABLE_NAME = 'CRM_CUSTDEVPLAN' and OWNER='SYSTEM'; select count(*) into num4 from all_tables where TABLE_NAME = 'CRM_CUSTINFO' and OWNER='SYSTEM'; select count(*) into num5 from all_tables where TABLE_NAME = 'CRM_CUSTLOST' and OWNER='SYSTEM'; select count(*) into num6 from all_tables where TABLE_NAME = 'CRM_CUSTRANK' and OWNER='SYSTEM'; select count(*) into num7 from all_tables where TABLE_NAME = 'CRM_CUSTREGION' and OWNER='SYSTEM'; select count(*) into num8 from all_tables where TABLE_NAME = 'CRM_CUSTSERVICE' and OWNER='SYSTEM'; select count(*) into num9 from all_tables where TABLE_NAME = 'CRM_ROLE' and OWNER='SYSTEM'; select count(*) into num10 from all_tables where TABLE_NAME = 'CRM_SALEOPPORTUNITY' and OWNER='SYSTEM'; select count(*) into num11 from all_tables where TABLE_NAME = 'CRM_SERVICETYPE' and OWNER='SYSTEM'; select count(*) into num12 from all_tables where TABLE_NAME = 'CRM_USERINFO' and OWNER='SYSTEM'; select count(*) into num13 from all_tables where TABLE_NAME = 'SR_HISTORYORDER' and OWNER='SYSTEM'; select count(*) into num14 from all_tables where TABLE_NAME = 'SR_PRODUCTINFO' and OWNER='SYSTEM'; select count(*) into num15 from all_tables where TABLE_NAME = 'SR_PRODUCTSTOCK' and OWNER='SYSTEM'; if num0=1 then execute immediate 'drop sequence HIBERNATE_SEQUENCE'; end if; if num1=1 then execute immediate 'drop table CRM_CUSTCONTACTRECORD cascade constraints'; end if; if num2=1 then execute immediate 'drop table CRM_CUSTCONTACTS cascade constraints'; end if; if num3=1 then execute immediate 'drop table CRM_CUSTDEVPLAN cascade constraints'; end if; if num4=1 then execute immediate 'drop table CRM_CUSTINFO cascade constraints'; end if; if num5=1 then execute immediate 'drop table CRM_CUSTLOST cascade constraints'; end if; if num6=1 then execute immediate 'drop table CRM_CUSTRANK cascade constraints'; end if; if num7=1 then execute immediate 'drop table CRM_CUSTREGION cascade constraints'; end if; if num8=1 then execute immediate 'drop table CRM_CUSTSERVICE cascade constraints'; end if; if num9=1 then execute immediate 'drop table CRM_ROLE cascade constraints'; end if; if num10=1 then execute immediate 'drop table CRM_SALEOPPORTUNITY cascade constraints'; end if; if num11=1 then execute immediate 'drop table CRM_SERVICETYPE cascade constraints'; end if; if num12=1 then execute immediate 'drop table CRM_USERINFO cascade constraints'; end if; if num13=1 then execute immediate 'drop table SR_HISTORYORDER cascade constraints'; end if; if num14=1 then execute immediate 'drop table SR_PRODUCTINFO cascade constraints'; end if; if num15=1 then execute immediate 'drop table SR_PRODUCTSTOCK cascade constraints'; end if; --創建數據表 DBMS_OUTPUT.put_line('初始化資料庫信息完畢！正在創建相關數據表，請稍後.....'); if true then execute immediate 'create sequence HIBERNATE_SEQUENCE minvalue 10 maxvalue 999999999999999999999999 start with 10 increment by 1 cache 20'; execute immediate 'create table CRM_CUSTCONTACTRECORD ( ID number(10,0) not null, CONTACT_TIME varchar2(255 char), CONTACT_ADDRESS varchar2(255 char), SUMMARY varchar2(255 char), REMARK varchar2(255 char), DETAIL varchar2(255 char), CUST number(10,0), primary key (ID) )'; execute immediate 'create table CRM_CUSTCONTACTS ( ID number(10,0) not null, CUST_CONTACTS_NAME varchar2(255 char), CUSTCONTACTSSEX number(10,0), CUST_CONTACTS_POSITION varchar2(255 char), CUST_CONTACTS_OFFICE_PHONE varchar2(255 char), CUST_CONTACTS_PHONE varchar2(255 char), REMARK varchar2(255 char), CUST number(10,0), primary key (ID) )'; execute immediate 'create table CRM_CUSTDEVPLAN ( ID number(10,0) not null, DEVDATE varchar2(255 char), DEVCONT varchar2(255 char), DEVEFFECT varchar2(255 char), ISDEVSUCCESS number(10,0), SALEOPPOR number(10,0), primary key (ID) )'; execute immediate 'create table CRM_CUSTINFO ( ID number(10,0) not null, CUST_NAME varchar2(255 char), CUST_NUMBER varchar2(255 char), CUST_REGION number(10,0), CUST_PRINCIPAL varchar2(255 char), CUST_RANK number(10,0), CUST_REPUTATION number(10,0), CUST_SATISFACTION number(10,0), ADDRESS varchar2(255 char), POST_CODE varchar2(255 char), PHONE varchar2(255 char), FAX varchar2(255 char), NET varchar2(255 char), BUSINESS_LICENSE varchar2(255 char), LEGAL_REPRESENTATIVE varchar2(255 char), REGISTER_CAPITAL float, ANNUAL_SALE number(10,0), DISPOSIT_BANK varchar2(255 char), BANK_ACCOUNT varchar2(255 char), LOCAL_TAX_NUM varchar2(255 char), NATIONAL_TAX_NUM varchar2(255 char), primary key (ID) )'; execute immediate 'create table CRM_CUSTLOST ( ID number(10,0) not null, CUST_NAME varchar2(255 char), CUST_MANAGER varchar2(255 char), LOST_STATE number(10,0), LAST_PLACE_ORDER varchar2(255 char), RESPIT_MEASURE varchar2(255 char), CUST_LOST_REASON varchar2(255 char), CONFIRM_CUST_LOST_DATE varchar2(255 char), custId number(10,0), primary key (ID) )'; execute immediate 'create table CRM_CUSTRANK ( ID number(10,0) not null, VALUE varchar2(255 char), primary key (ID) )'; execute immediate 'create table CRM_CUSTREGION ( ID number(10,0) not null, VALUE varchar2(255 char), primary key (ID) )'; execute immediate 'create table CRM_CUSTSERVICE ( ID number(10,0) not null, CUSTNAME varchar2(255 char), TYPE number(10,0), SUMMARY varchar2(255 char), CREATE_MAN varchar2(255 char), SERVICE_STATE number(10,0), SERVICE_REQUEST_CONTENT varchar2(255 char), SERVICE_DISPTCH varchar2(255 char), SERVICE_DISPTCH_DATE varchar2(255 char), SERVI_CECREATE_DATE varchar2(255 char), SERVICE_DEAL_MAN varchar2(255 char), SERVICE_DEAL_DATE varchar2(255 char), SERVICE_DEAL_CONTENT varchar2(255 char), SERVICE_DEAL_FEEDBACK_MAN varchar2(255 char), SERVICE_DEAL_FEEDBACK_DATE varchar2(255 char), SERVICE_DEAL_FEEDBACK_RESULT varchar2(255 char), SERVICE_DEAL_SATISFACTION number(10,0), primary key (ID) )'; execute immediate 'create table CRM_ROLE ( ID number(10,0) not null, ROLENAME varchar2(255 char), SALEOPPORM number(10,0), DEVPLAN number(10,0), CUSTINFOM number(10,0), CUSTLOSTM number(10,0), SERVICECREATE number(10,0), SERVICEDISPATCH number(10,0), SERVICEDEAL number(10,0), SERVICEFEEDBACK number(10,0), SERVICEARCH number(10,0), CUSTCONTRA number(10,0), CUSTCONSA number(10,0), CUSTLOSTA number(10,0), CUSTSERVICEA number(10,0), CUSTRANK number(10,0), CUSTTYPE number(10,0), CUSTREGION number(10,0), QUERYPRODUCE number(10,0), QUERYSTORY number(10,0), USERINFOM number(10,0), ROLE number(10,0), primary key (ID) )'; execute immediate 'create table CRM_SALEOPPORTUNITY ( ID number(10,0) not null, CUSTNAME varchar2(255 char), PRINCIPAL varchar2(255 char), OPPORSOURCE varchar2(255 char), SUCCESSPRO varchar2(255 char), COMPANYPHONE varchar2(255 char), SUMMARY varchar2(255 char), OPPORDESC varchar2(255 char), CUSTRANK number(10,0), CUSTREGION number(10,0), DISPTCH number(10,0), DISPTCHTIME varchar2(255 char), DEVSTATE number(10,0), CREATE_PERSON varchar2(255 char), CREATE_DATE varchar2(255 char), primary key (ID) )'; execute immediate 'create table CRM_SERVICETYPE ( ID number(10,0) not null, VALUE varchar2(255 char), primary key (ID) )'; execute immediate 'create table CRM_USERINFO ( ID number(10,0) not null, USERNAME varchar2(255 char), PASSWORD varchar2(255 char), ROLE number(10,0), primary key (ID) )'; execute immediate 'create table SR_HISTORYORDER ( ID number(10,0) not null, PLACE_ORDER_DATE varchar2(255 char), DELIVERY_ADDRESS varchar2(255 char), ORDER_STATE number(10,0), PRODUCTINFO number(10,0), ORDERNUM number(10,0), CUSTINFO number(10,0), TOTAL_AMOUNT number(10,0), primary key (ID) )'; execute immediate 'create table SR_PRODUCTINFO ( ID number(10,0) not null, P_NAME varchar2(255 char), TYPE varchar2(255 char), BATCH_NUM varchar2(255 char), UNIT varchar2(255 char), PRICE float, REMARK varchar2(255 char), primary key (ID) )'; execute immediate 'create table SR_PRODUCTSTOCK ( ID number(10,0) not null, PRO_NAME varchar2(255 char), STORAGE_NUM varchar2(255 char), GOODS_ALLOCATION varchar2(255 char), STOCK number(10,0), REMARK varchar2(255 char), primary key (ID) )'; --添加主鍵與外鍵 DBMS_OUTPUT.put_line('數據表已創建完畢！正在進一步為數據表設置主鍵和外鍵，請稍後......'); execute immediate 'alter table CRM_CUSTCONTACTRECORD add constraint FK_kuieobwbi2obpgukx7sa30iyh foreign key (CUST) references CRM_CUSTINFO'; execute immediate 'alter table CRM_CUSTCONTACTS add constraint FK_1hdvlgrepiu3ry2y19wkj1xre foreign key (CUST) references CRM_CUSTINFO'; execute immediate 'alter table CRM_CUSTDEVPLAN add constraint FK_1s4utcam3pph4jxd0heg308c7 foreign key (SALEOPPOR) references CRM_SALEOPPORTUNITY'; execute immediate 'alter table CRM_CUSTINFO add constraint FK_hq941ppwv52xwtcl7e9x56xxj foreign key (CUST_REGION) references CRM_CUSTREGION'; execute immediate 'alter table CRM_CUSTINFO add constraint FK_qubnlw9rxwgidf6ynm2rgji3g foreign key (CUST_RANK) references CRM_CUSTRANK'; execute immediate 'alter table CRM_CUSTSERVICE add constraint FK_q0drd0i5uiir6yjpamkj6yumg foreign key (TYPE) references CRM_SERVICETYPE'; execute immediate 'alter table CRM_SALEOPPORTUNITY add constraint FK_akca116rf4yd760rhf0u8oe6c foreign key (CUSTRANK) references CRM_CUSTRANK'; execute immediate 'alter table CRM_SALEOPPORTUNITY add constraint FK_ejym7spmfmsgi1d3phk3n2o27 foreign key (CUSTREGION) references CRM_CUSTREGION'; execute immediate 'alter table CRM_SALEOPPORTUNITY add constraint FK_p6jh4w3na4l21c1mybrcba7ij foreign key (DISPTCH) references CRM_USERINFO'; execute immediate 'alter table CRM_USERINFO add constraint FK_n0hrt4nv89mdlf6lbyma8n5kv foreign key (ROLE) references CRM_ROLE'; execute immediate 'alter table SR_HISTORYORDER add constraint FK_oa4qfri39p5frwsfe1u8klx5m foreign key (PRODUCTINFO) references SR_PRODUCTINFO'; execute immediate 'alter table SR_HISTORYORDER add constraint FK_3v1b0tqi1yq3yacbmmd3ccs3s foreign key (CUSTINFO) references CRM_CUSTINFO'; --以下的操作是添加測試數據 DBMS_OUTPUT.put_line('數據表的主外建設置完成！正在添加測試數據，請稍後.....'); --角色 execute immediate 'insert into CRM_ROLE (ID, ROLENAME, SALEOPPORM, DEVPLAN, CUSTINFOM, CUSTLOSTM, SERVICECREATE, SERVICEDISPATCH, SERVICEDEAL, SERVICEFEEDBACK, SERVICEARCH, CUSTCONTRA, CUSTCONSA, CUSTLOSTA, CUSTSERVICEA, CUSTRANK, CUSTTYPE, CUSTREGION, QUERYPRODUCE, QUERYSTORY, USERINFOM, ROLE) values (1, ''管理員'', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)'; execute immediate 'insert into CRM_ROLE (ID, ROLENAME, SALEOPPORM, DEVPLAN, CUSTINFOM, CUSTLOSTM, SERVICECREATE, SERVICEDISPATCH, SERVICEDEAL, SERVICEFEEDBACK, SERVICEARCH, CUSTCONTRA, CUSTCONSA, CUSTLOSTA, CUSTSERVICEA, CUSTRANK, CUSTTYPE, CUSTREGION, QUERYPRODUCE, QUERYSTORY, USERINFOM, ROLE) values (2, ''董事長'', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)'; execute immediate 'insert into CRM_ROLE (ID, ROLENAME, SALEOPPORM, DEVPLAN, CUSTINFOM, CUSTLOSTM, SERVICECREATE, SERVICEDISPATCH, SERVICEDEAL, SERVICEFEEDBACK, SERVICEARCH, CUSTCONTRA, CUSTCONSA, CUSTLOSTA, CUSTSERVICEA, CUSTRANK, CUSTTYPE, CUSTREGION, QUERYPRODUCE, QUERYSTORY, USERINFOM, ROLE) values (3, ''經理'', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)'; execute immediate 'insert into CRM_ROLE (ID, ROLENAME, SALEOPPORM, DEVPLAN, CUSTINFOM, CUSTLOSTM, SERVICECREATE, SERVICEDISPATCH, SERVICEDEAL, SERVICEFEEDBACK, SERVICEARCH, CUSTCONTRA, CUSTCONSA, CUSTLOSTA, CUSTSERVICEA, CUSTRANK, CUSTTYPE, CUSTREGION, QUERYPRODUCE, QUERYSTORY, USERINFOM, ROLE) values (4, ''主管'', 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 0)'; --用戶信息 execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE) values (1, ''admin'', ''admin'', 1)'; execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE) values (2, ''Vincent'', ''111'', 2)'; execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE) values (3, ''Milke'', ''111'', 3)'; execute immediate 'insert into CRM_USERINFO (ID, USERNAME, PASSWORD, ROLE) values (4, ''Catherine'', ''111'', 4)'; --客戶等級 execute immediate 'insert into CRM_CUSTRANK (ID, VALUE) values (1, ''戰略合作伙伴'')'; execute immediate 'insert into CRM_CUSTRANK (ID, VALUE) values (2, ''重點開發客戶'')'; execute immediate 'insert into CRM_CUSTRANK (ID, VALUE) values (3, ''大客戶'')'; execute immediate 'insert into CRM_CUSTRANK (ID, VALUE) values (4, ''合作伙伴'')'; execute immediate 'insert into CRM_CUSTRANK (ID, VALUE) values (5, ''普通客戶'')'; --客戶地區 execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (1, ''北京'')'; execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (2, ''廣州'')'; execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (3, ''深圳'')'; execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (4, ''上海'')'; execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (5, ''山東'')'; execute immediate 'insert into CRM_CUSTREGION (ID, VALUE) values (6, ''大貴州'')'; --服務類型 execute immediate 'insert into CRM_SERVICETYPE (ID, VALUE) values (1, ''咨詢'')'; execute immediate 'insert into CRM_SERVICETYPE (ID, VALUE) values (2, ''投訴'')'; execute immediate 'insert into CRM_SERVICETYPE (ID, VALUE) values (3, ''建議'')'; --產品信息 execute immediate 'insert into SR_PRODUCTINFO (ID, P_NAME, TYPE, BATCH_NUM, UNIT, PRICE, REMARK) values (1, ''蘋果6plus'', ''mobile'', ''EX5552'', ''部'', 6523, ''實用，流行'')'; execute immediate 'insert into SR_PRODUCTINFO (ID, P_NAME, TYPE, BATCH_NUM, UNIT, PRICE, REMARK) values (2, ''蘋果5s'',''mobile'', ''EX5554'', ''部'', 5234, ''實用，流行'')'; execute immediate 'insert into SR_PRODUCTINFO (ID, P_NAME, TYPE, BATCH_NUM, UNIT, PRICE, REMARK) values (3, ''蘋果5'', ''mobile'', ''EX5222'', ''部'', 3921, ''實用，流行'')'; execute immediate 'insert into SR_PRODUCTINFO (ID, P_NAME, TYPE, BATCH_NUM, UNIT, PRICE, REMARK) values (4, ''蘋果4s'', ''mobile'', ''EX5553'', ''部'', 3225, ''實用，流行'')'; --產品庫存 execute immediate 'insert into SR_PRODUCTSTOCK (ID, PRO_NAME, STORAGE_NUM, GOODS_ALLOCATION, STOCK, REMARK) values (1, ''iphone6s'', ''心想一號001號'', ''獅城大廈A001'', 1000, ''充足'')'; execute immediate 'insert into SR_PRODUCTSTOCK (ID, PRO_NAME, STORAGE_NUM, GOODS_ALLOCATION, STOCK, REMARK) values (2, ''iphone5'', ''心想一號002號'', ''獅城大廈A002'', 2000, ''充足'')'; execute immediate 'insert into SR_PRODUCTSTOCK (ID, PRO_NAME, STORAGE_NUM, GOODS_ALLOCATION, STOCK, REMARK) values (3, ''iphone5s'', ''心想一號003號'', ''獅城大廈A003'', 3000,''充足'')'; execute immediate 'insert into SR_PRODUCTSTOCK (ID, PRO_NAME, STORAGE_NUM, GOODS_ALLOCATION, STOCK, REMARK) values (4, ''iphone4s'', ''心想一號004號'', ''獅城大廈A004'', 4000, ''充足'')'; --客戶信息 execute immediate 'insert into CRM_CUSTINFO (ID, CUST_NAME, CUST_NUMBER, CUST_REGION, CUST_PRINCIPAL, CUST_RANK, CUST_REPUTATION, CUST_SATISFACTION, ADDRESS, POST_CODE, PHONE, FAX, NET, BUSINESS_LICENSE, LEGAL_REPRESENTATIVE, REGISTER_CAPITAL, ANNUAL_SALE, DISPOSIT_BANK, BANK_ACCOUNT, LOCAL_TAX_NUM, NATIONAL_TAX_NUM) values (1, ''方正集團'', ''A11001'', 1, ''李總'', 1, 2, 4, ''中國北京'', ''552321'', ''12575423254'', ''0451-5232658'', ''www.fangzheng.com'', ''AS1023415'', ''法人代表'', 8622, 10000, ''中國人民銀行'', ''6212265423021226594'', ''AR16546156'', ''AE11561561'')'; execute immediate 'insert into CRM_CUSTINFO (ID, CUST_NAME, CUST_NUMBER, CUST_REGION, CUST_PRINCIPAL, CUST_RANK, CUST_REPUTATION, CUST_SATISFACTION, ADDRESS, POST_CODE, PHONE, FAX, NET, BUSINESS_LICENSE, LEGAL_REPRESENTATIVE, REGISTER_CAPITAL, ANNUAL_SALE, DISPOSIT_BANK, BANK_ACCOUNT, LOCAL_TAX_NUM, NATIONAL_TAX_NUM) values (2, ''百事可樂'', ''A1102'', 2, ''魏總'', 2, 5, 3, ''中國貴州'', ''552321'', ''12632564123'', ''0521-2365489'', ''www.baishikele.com'', ''FS6545641'', ''法人代表'', 2100, 200, ''中國工商銀行'', ''6212289654021226325'', ''BD16541654'', ''BG21654615'')'; execute immediate 'insert into CRM_CUSTINFO (ID, CUST_NAME, CUST_NUMBER, CUST_REGION, CUST_PRINCIPAL, CUST_RANK, CUST_REPUTATION, CUST_SATISFACTION, ADDRESS, POST_CODE, PHONE, FAX, NET, BUSINESS_LICENSE, LEGAL_REPRESENTATIVE, REGISTER_CAPITAL, ANNUAL_SALE, DISPOSIT_BANK, BANK_ACCOUNT, LOCAL_TAX_NUM, NATIONAL_TAX_NUM) values (3, ''東方科技'', ''A11006'', 1, ''趙總'', 1, 2, 4, ''中國北京'', ''552321'', ''12575423254'', ''0451-5232658'', ''www.dongfangkeji.com'', ''AS1023415'', ''法人代表'', 5454, 1065, ''中國人民銀行'', ''6212265423021226594'', ''AR16546156'', ''AE11561561'')'; --客戶聯繫人 execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (1, ''李麗'', 0, ''前臺'', ''0562-636656323'', ''15423215232'', ''前臺服務'', 1)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (2, ''Jon'', 1, ''主管'', ''26521-226464664'', ''15236251432'', ''可找經理'', 1)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (3, ''Milke'', 1, ''經理'', ''26521-226464664'', ''15236251425'', ''可找童市長'', 1)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (4, ''李帆'', 0, ''前臺'', ''0562-636656323'', ''15423215232'', ''前臺服務'', 2)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (5, ''Vincent'', 1, ''主管'', ''26521-226464664'', ''15236251432'', ''可找經理'', 2)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (6, ''Bob'', 1, ''經理'', ''26521-226464664'', ''15236251425'', ''可找童市長'', 2)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (7, ''李菲菲'', 0, ''前臺'', ''0562-636656323'', ''15423215232'', ''前臺服務'', 3)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (8, ''Reborn'', 1, ''主管'', ''26521-226464664'', ''15236251432'', ''可找經理'', 3)'; execute immediate 'insert into CRM_CUSTCONTACTS (ID, CUST_CONTACTS_NAME, CUSTCONTACTSSEX, CUST_CONTACTS_POSITION, CUST_CONTACTS_OFFICE_PHONE, CUST_CONTACTS_PHONE, REMARK, CUST) values (9, ''kleted'', 1, ''經理'', ''26521-226464664'', ''15236251425'', ''可找童市長'', 3)'; --客戶交往記錄 execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (1, ''2015-6-30 15:30:56'', ''北京'', ''結賬'', ''success'', ''經理與主管'', 1)'; execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (2, ''2015-9-11 15:30:56'', ''貴州'', ''見面會'', ''very success'', ''人很多'', 1)'; execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (3, ''2015-6-30 15:30:56'', ''北京'', ''結賬'', ''success'', ''經理與主管'', 2)'; execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (4, ''2015-9-11 15:30:56'', ''貴州'', ''見面會'', ''very success'', ''人很多'', 2)'; execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (5, ''2015-6-30 15:30:56'', ''北京'', ''結賬'', ''success'', ''經理與主管'', 3)'; execute immediate 'insert into CRM_CUSTCONTACTRECORD (ID, CONTACT_TIME, CONTACT_ADDRESS, SUMMARY, REMARK, DETAIL, CUST) values (6, ''2015-9-11 15:30:56'', ''貴州'', ''見面會'', ''very success'', ''人很多'', 3)'; --銷售機會 execute immediate 'insert into CRM_SALEOPPORTUNITY (ID, CUSTNAME, PRINCIPAL, OPPORSOURCE, SUCCESSPRO, COMPANYPHONE, SUMMARY, OPPORDESC, CUSTRANK, CUSTREGION, DISPTCH, DISPTCHTIME, DEVSTATE, CREATE_PERSON, CREATE_DATE) values (1, ''方正集團'', ''李總'', ''主動聯繫'', ''85'', ''5632-1425262'', ''大型國有企業'', ''機具發展潛力'', 2, 2, 4, ''2016-04-02 14:56:55'', 2, ''Vincent'', ''2016-04-02 14:56:55'')'; execute immediate 'insert into CRM_SALEOPPORTUNITY (ID, CUSTNAME, PRINCIPAL, OPPORSOURCE, SUCCESSPRO, COMPANYPHONE, SUMMARY, OPPORDESC, CUSTRANK, CUSTREGION, DISPTCH, DISPTCHTIME, DEVSTATE, CREATE_PERSON, CREATE_DATE) values (3, ''東方科技'', ''趙總'', ''主動聯繫'', ''87'', ''5632-1425245'', ''國有企業'', ''非常具有發展潛力'', 3, 6, 4, ''2013-6-10 15:30:56'', 0, ''admin'', ''2016-04-02 14:56:55'')'; execute immediate 'insert into CRM_SALEOPPORTUNITY (ID, CUSTNAME, PRINCIPAL, OPPORSOURCE, SUCCESSPRO, COMPANYPHONE, SUMMARY, OPPORDESC, CUSTRANK, CUSTREGION, DISPTCH, DISPTCHTIME, DEVSTATE, CREATE_PERSON, CREATE_DATE) values (4, ''方正集團'', ''李總1'', ''主動聯繫'', ''85'', ''5632-1425262'',''大型國有企業'', ''機具發展潛力'', 2, 2, 4, ''2016-04-02 14:56:55'', 0, ''admin'', ''2016-04-02 14:56:55'')'; execute immediate 'insert into CRM_SALEOPPORTUNITY (ID, CUSTNAME, PRINCIPAL, OPPORSOURCE, SUCCESSPRO, COMPANYPHONE, SUMMARY, OPPORDESC, CUSTRANK, CUSTREGION, DISPTCH, DISPTCHTIME, DEVSTATE, CREATE_PERSON, CREATE_DATE) values (5, ''百事可樂'', ''魏總1'', ''主動聯繫'', ''90'', ''2362-5654856'', ''小型企業'', ''有潛力'', 2, 2, 3, ''2015-2-30 15:30:56'', 1, ''admin'', ''2016-04-02 14:56:55'')'; --客戶開發計劃 execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (1, ''2015-6-8 15:30:56'', ''商討'', ''滿意'', 1, 1)'; execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (5, ''2015-6-8 15:30:56'', ''商討'', ''滿意'', 1, 1)'; execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (3, ''2016-2-23 15:30:56'', ''計劃'', null, 0, 3)'; execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (4, ''2016-9-23 15:30:56'', ''計劃'', null, 1, 4)'; execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (7, ''2016-2-23 15:30:56'', ''計劃'', null, 0, 3)'; execute immediate 'insert into CRM_CUSTDEVPLAN (ID, DEVDATE, DEVCONT, DEVEFFECT, ISDEVSUCCESS, SALEOPPOR) values (8, ''2016-9-23 15:30:56'', ''計劃'', null, 1, 4)'; --歷史訂單 execute immediate 'insert into SR_HISTORYORDER (ID, PLACE_ORDER_DATE, DELIVERY_ADDRESS, ORDER_STATE, PRODUCTINFO, ORDERNUM, CUSTINFO, TOTAL_AMOUNT) values (1, ''2014-8-5 15:30:56'', ''北京'', 0, 1, 20, 1, 6699)'; execute immediate 'insert into SR_HISTORYORDER (ID, PLACE_ORDER_DATE, DELIVERY_ADDRESS, ORDER_STATE, PRODUCTINFO, ORDERNUM, CUSTINFO, TOTAL_AMOUNT) values (2, ''2016-6-10 15:30:56'', ''貴州'', 1, 2, 10, 2, 2302)'; execute immediate 'insert into SR_HISTORYORDER (ID, PLACE_ORDER_DATE, DELIVERY_ADDRESS, ORDER_STATE, PRODUCTINFO, ORDERNUM, CUSTINFO, TOTAL_AMOUNT) values (3, ''2013-6-1 15:30:56'', ''深圳'', 1, 3, 46, 1, 9872)'; execute immediate 'insert into SR_HISTORYORDER (ID, PLACE_ORDER_DATE, DELIVERY_ADDRESS, ORDER_STATE, PRODUCTINFO, ORDERNUM, CUSTINFO, TOTAL_AMOUNT) values (4, ''2015-2-21 15:30:56'', ''上海'', 0, 4, 83, 2, 10231)'; --服務管理 execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (1, ''方正集團—測試'', 1, ''昨天開始，今天處理'', ''admin'', 0, ''咨詢項目'', null, null, ''2015-6-21 15:30:56'', null, null, null, null, null, null, null)'; execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (2, ''方正集團—測試'', 2, ''昨天開始，今天處理'', ''admin'', 0, ''建議改善'', null, null, ''2016-7-21 15:30:56'', null, null, null, null, null, null, null)'; execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (3, ''方正集團—測試'', 3, ''昨天開始，今天處理'', ''admin'', 0, ''投訴項目'', null, null, ''2015-6-21 15:30:56'', null, null, null, null, null, null, null)'; execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (4, ''東方科技'', 1, ''昨天開始，今天處理'', ''admin'', 0, ''咨詢項目'', null, null, ''2015-6-21 15:30:56'', null, null, null, null, null, null, null)'; execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (5, ''東方科技'', 2, ''昨天開始，今天處理'', ''admin'', 0, ''建議改善'', null, null, ''2016-7-21 15:30:56'', null, null, null, null, null, null, null)'; execute immediate 'insert into CRM_CUSTSERVICE (ID, CUSTNAME, TYPE, SUMMARY, CREATE_MAN, SERVICE_STATE, SERVICE_REQUEST_CONTENT, SERVICE_DISPTCH, SERVICE_DISPTCH_DATE, SERVI_CECREATE_DATE, SERVICE_DEAL_MAN, SERVICE_DEAL_DATE, SERVICE_DEAL_CONTENT, SERVICE_DEAL_FEEDBACK_MAN, SERVICE_DEAL_FEEDBACK_DATE, SERVICE_DEAL_FEEDBACK_RESULT, SERVICE_DEAL_SATISFACTION) values (6, ''百事可樂'', 3, ''昨天開始，今天處理'', ''admin'', 0, ''投訴項目'', null, null, ''2015-6-21 15:30:56'', null, null, null, null, null, null, null)'; DBMS_OUTPUT.put_line('測試數據已成功添加'); end if; --提交事務 commit; end;