--变量声明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;