jzh.sql

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

--创建客户分级规则信息表
create table score_rule  (
   nid                  NUMBER                          not null,
   rulecode             VARCHAR2(30),
   rulename             VARCHAR2(100),
   rulebrand            VARCHAR2(100),
   ruletime             VARCHAR2(30),
   countgene            VARCHAR2(300),
   countmodulus         NUMBER,
   ruledesc             VARCHAR2(1000),
   ruleexpression       VARCHAR2(1000),
   rulepri              VARCHAR2(30),
   checksign            VARCHAR2(2),
   freezesign           VARCHAR2(2),
   numone               NUMBER,
   numtwo               NUMBER,
   cardtype             VARCHAR2(30),
   numthree             NUMBER,
   numfour              NUMBER,
   constraint PK_SCORE_RULE primary key (nid)
);

comment on column score_rule.nid is
'分级ID号';

comment on column score_rule.rulecode is
'分级规则编码';

comment on column score_rule.rulename is
'规则名称';

comment on column score_rule.rulebrand is
'规则品牌';

comment on column score_rule.ruletime is
'规则有效期';

comment on column score_rule.countgene is
'客户分级计算因子';

comment on column score_rule.countmodulus is
'计算系数';

comment on column score_rule.ruledesc is
'规则描述';

comment on column score_rule.ruleexpression is
'规则表达式';

comment on column score_rule.rulepri is
'规则优先级';

comment on column score_rule.checksign is
'审核通过标志';

comment on column score_rule.freezesign is
'冻结标志';

comment on column score_rule.numone is
'数值1';

comment on column score_rule.numtwo is
'数值2';

comment on column score_rule.cardtype is
'卡型';

comment on column score_rule.numthree is
'数值3';

comment on column score_rule.numfour is
'数值4';


create sequence SEQ_SCORERULE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'010','世界风规则1(钻石卡)','世界风','2018-8-1','消费积分',1,
	'连续6个月，3500分≤累积积分:钻石卡',
	'3500≤score:钻石卡',null,'是','否',3500,null,'钻石卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'011','世界风规则2(金卡)','世界风','2018-8-1','消费积分',2,
	'连续6个月，2500分≤累积积分＜3500分:金卡',
	'2500≤score＜3500:金卡',null,'是','否',2500,3500,'金卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'012','世界风规则3(银卡)','世界风','2018-8-1','消费积分',3,
	'连续6个月，1000分≤累积积分＜2500分:银卡',
	'1000≤score＜2500:银卡',null,'是','否',1000,2500,'银卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'013','世界风规则4(钻石卡)','世界风','2018-8-1','话费和ARPU',1,
	'一次性预存话费3000元以上,且连续六个月平均ARPU值达到500元:钻石卡',
	'预存话费>=3000,且平均ARPU值>=500元:钻石卡',null,'是','否',3000,null,'钻石卡',500,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'014','世界风规则5(金卡)','世界风','2018-8-1','话费和ARPU',2,
	'一次性预存话费1800元以上,且连续六个月平均ARPU值达到300元:金卡',
	'预存话费>=1800,且平均ARPU值>=300元:金卡',null,'是','否',1800,3000,'金卡',300,500);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'015','世界风规则6(银卡)','世界风','2018-8-1','话费和ARPU',3,
	'一次性预存话费900元以上,且连续六个月平均ARPU值达到150元:银卡',
	'预存话费>=900元,且平均ARPU值>=150元:银卡',null,'是','否',900,1800,'银卡',150,300);



insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour)  
	values(SEQ_SCORERULE.nextval,'016','新势力规则7(钻石卡)','新势力','2018-8-1','消费积分',1,
	'连续6个月，3500分≤累积积分:钻石卡',
	'3500≤score:钻石卡',null,'是','否',3500,null,'钻石卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour)  
	values(SEQ_SCORERULE.nextval,'017','新势力规则8(金卡)','新势力','2018-8-1','消费积分',2,
	'连续6个月，2500分≤累积积分＜3500分:金卡',
	'2500≤score＜3500:金卡',null,'是','否',2500,3500,'金卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'018','新势力规则9(银卡)','新势力','2018-8-1','消费积分',3,
	'连续6个月，1000分≤累积积分＜2500分:银卡',
	'1000≤score＜2500:银卡',null,'是','否',1000,2500,'银卡',null,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'019','新势力规则10(钻石卡)','新势力','2018-8-1','话费和ARPU',1,
	'一次性预存话费3000元以上,且连续六个月平均ARPU值达到500元:钻石卡',
	'预存话费>=3000,且平均ARPU值>=500元:钻石卡',null,'是','否',3000,null,'钻石卡',500,null);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour) 
	values(SEQ_SCORERULE.nextval,'020','新势力规则11(金卡)','新势力','2018-8-1','话费和ARPU',2,
	'一次性预存话费1800元以上,且连续六个月平均ARPU值达到300元:金卡',
	'预存话费>=1800,且平均ARPU值>=300元:金卡',null,'是','否',1800,3000,'金卡',300,500);

insert into score_rule(nid,rulecode,rulename,rulebrand,ruletime,countgene,countmodulus,ruledesc,ruleexpression,
	rulepri,checksign,freezesign,numone,numtwo,cardtype,numthree,numfour)  
	values(SEQ_SCORERULE.nextval,'021','新势力规则12(银卡)','新势力','2018-8-1','话费和ARPU',3,
	'一次性预存话费900元以上,且连续六个月平均ARPU值达到150元:银卡',
	'预存话费>=900元,且平均ARPU值>=150元:银卡',null,'是','否',900,1800,'银卡',150,300);

commit;


--创建客户分级规则日志表
create table rule_log  (
   nid                NUMBER                          not null,
   ruleid             NUMBER,
   rulecreater        VARCHAR2(30),
   createtime         VARCHAR2(30),
   ruleupdater        VARCHAR2(30),
   updatetime         VARCHAR2(30),
   checkman           VARCHAR2(30),
   checktime          VARCHAR2(30),
   constraint PK_RULE_LOG primary key (nid)
);

comment on table rule_log is
'客户分级规则日志信息表';

comment on column rule_log.nid is
'日志编号';

comment on column rule_log.ruleid is
'规则编号';

comment on column rule_log.rulecreater is
'规则创建人';

comment on column rule_log.createtime is
'创建时间';

comment on column rule_log.ruleupdater is
'规则修改人';

comment on column rule_log.updatetime is
'修改时间';

comment on column rule_log.checkman is
'规则审核人';

comment on column rule_log.checktime is
'审核时间';

create sequence SEQ_RULELOG
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

commit;

--创建联盟商家质量级别表
create table masslevel  (
   nid                  NUMBER                          not null,
   sname                VARCHAR2(30),
   constraint PK_MASSLEVEL primary key (nid)
);

comment on table masslevel is
'联盟商家质量级别表';

comment on column masslevel.nid is
'级别编号';

comment on column masslevel.sname is
'级别名称';

insert into masslevel values(1,'很好');
insert into masslevel values(2,'比较好');
insert into masslevel values(3,'中等');
insert into masslevel values(4,'差');
insert into masslevel values(5,'非常差');

commit;

--创建联盟商家评分表
create table bussinessgrade  (
   nid                  NUMBER                          not null,
   busnid               NUMBER,
   grade                NUMBER,
   constraint PK_BUSSINESSGRADE primary key (nid)
);

comment on table bussinessgrade is
'联盟商家评分表';

comment on column bussinessgrade.nid is
'评分编号';

comment on column bussinessgrade.busnid is
'商家编号';

comment on column bussinessgrade.grade is
'评分';


--创建俱乐部活动表

create table clubaction  (
   nid                  NUMBER                          not null,
   clubname             VARCHAR2(50),
   actiontime           VARCHAR2(30),
   saddress             VARCHAR2(300),
   content              VARCHAR2(1000),
   budget               NUMBER,
   organiger            VARCHAR2(100),
   intendnum            NUMBER,
   factnum              NUMBER,
   factcharge           NUMBER,
   ischecked            VARCHAR2(2),
   ispass               VARCHAR2(2),
   opinion              VARCHAR2(300),
   constraint PK_CLUBACTION primary key (nid)
);

comment on table clubaction is
'俱乐部活动表';

comment on column clubaction.nid is
'活动编号';

comment on column clubaction.clubname is
'俱乐部名称';

comment on column clubaction.actiontime is
'活动时间';

comment on column clubaction.saddress is
'活动地点';

comment on column clubaction.content is
'活动内容';

comment on column clubaction.budget is
'活动预算';

comment on column clubaction.organiger is
'活动组织者';

comment on column clubaction.intendnum is
'预计参加活动人数';

comment on column clubaction.factnum is
'实际参加人数';

comment on column clubaction.factcharge is
'实际费用';

comment on column clubaction.ischecked is
'是否审核';

comment on column clubaction.ispass is
'是否通过审核';

comment on column clubaction.opinion is
'审核意见';


create sequence seq_clubaction
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'东方俱乐部','2008-08-26 15:30','活动室1','联谊',
	300,'陈东',100,null,null,'已','是','通过');

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'天天俱乐部','2008-08-16 08:20','活动室2','游园',
	500,'陈东',200,null,null,'已','是','通过');

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'工人俱乐部','2008-08-06 09:08','活动室1','展览',
	1300,'张三',300,null,null,'已','否','费用太高');

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'东方俱乐部','2008-09-26 10:45','活动室3','观摩',300,
	'李四',40,null,null,'未',null,null);

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'工人俱乐部','2008-08-26 11:08','活动室1','讲座',300,
	'陈东',100,null,null,'已','否','时间冲突');

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'天天俱乐部','2008-08-26 16:40','活动室2','联谊',800,
	'王五',200,null,null,'未',null,null);

insert into clubaction(nid,clubname,actiontime,saddress,content,budget,organiger,
	intendnum,factnum,factcharge,ischecked,ispass,opinion)
	values(seq_clubaction.nextval,'东方俱乐部','2008-10-02 13:40','活动室3','考察',600,
	'赵明',100,null,null,'未',null,null);

commit;

--PL/SQL

--包头
create or replace package package_intergral is

  -- 定义动态游标
  type cs_table is ref cursor;
  
  -- 函数声明
  function detailsearch(str_sql varchar2) return cs_table;

end package_intergral;
/

--包体
create or replace package body package_intergral is

  -- 函数的实现
  function detailsearch(str_sql varchar2) return cs_table 
    is  mycursor cs_table;
  begin
    open mycursor for str_sql;
    return mycursor;
  end detailsearch;

end package_intergral;
/

commit;