Oracle聊天系统设计

set serveroutput onupdate t_onlinestate set statedesc='隐身' where stateid=3;--查询状态表：select * from t_onlinestate;--查询交友策略表:select * from t_friendshippolicy;--查询管理员表：select * from t_admin;--查询用户表：select * from t_user;--查询好友表：select * from t_friend;--查询聊天信息表：select * from t_message;---交友策略表create table t_friendshippolicy( polid number(1) not null primary key, policy nvarchar2(200) not null unique);insert into t_friendshippolicy（polid,policy）values(1,'允许任何人加我为好友');insert into t_friendshippolicy（polid,policy）values(2,'不允许任何人加我为好友');insert into t_friendshippolicy（polid,policy）values(3,'经验证才允许别人加我为好友');---用户状态表create table t_onlinestate( stateid number(1) not null primary key, statedesc nvarchar2(10) not null unique);insert into t_onlinestate(stateid,statedesc)values(1，'在线');insert into t_onlinestate(stateid,statedesc)values(2，'离线');insert into t_onlinestate(stateid,statedesc)values(3，'隐身');insert into t_onlinestate(stateid,statedesc)values(4，'忙碌');---管理员表create table t_admin(  adminid number(10) not null primary key,  adminname nvarchar2(20) not null unique,  adminpwd nvarchar2(100) not null);--创建序列 create sequence seq_t_admin start with 1 increment by 1 nominvalue maxvalue 10000 nocycle nocache/--创建触发器create trigger trigger_t_adminbefore insert on t_admin --当向表中执行插入操作时触发此触发器for each row  --对每一行都检查是否触发beginselect seq_t_admin.nextval  into:new.adminid from dual;end;/--添加管理员数据insert into t_admin(adminname,adminpwd)values('唯一','weiyi');insert into t_admin(adminname,adminpwd)values('小少','xiaoshao');---用户表create table t_user( userid  number(38)  not null primary key, pwd nvarchar2(100) not null, nikename nvarchar2(10) not null,  sex number(1) check(sex=1 or sex=0), birthday date, currstate number(1) default 0, friendshippolicy  number(1), foreign key (currstate) references t_onlinestate(stateid) , foreign key (friendshippolicy) references  t_friendshippolicy(polid));--创建表时没默认，修改成默认数值：alter table t_user modify currstate  number(1) default 0;--创建序列 create sequence seq_t_user start with 10001 increment by 1 nomaxvalue nocycle nocache/--创建触发器create or replace trigger trigger_t_userbefore insert on t_user  --当向表中执行插入操作时触发此触发器for each row  --对每一行都检查是否触发beginselect seq_t_user.nextval  into:new.userid from dual;end;/--在用户表中插入值**insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('000000','石晓涛','1',to_date('2008-08-05','yyyy-mm-dd'),1,1);--插入数据显示无效未通过重新验证insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('111111','徐本锡','1',to_date('2008-08-06','yyyy-mm-dd'),2,2);insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('222222','孙培培','0',to_date('2008-08-07','yyyy-mm-dd'),3,1);insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('333333','李巧丽','0',to_date('2008-08-08','yyyy-mm-dd'),4,1);insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('444444','李慧慧','0',to_date('2008-08-09','yyyy-mm-dd'),3,1);insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values('555555','刘杰','0',to_date('2008-08-10','yyyy-mm-dd'),2,1);--创建添加用户的存储过程：create procedure sp_add_user(pwd1 varchar2,nikename1 varchar2,sex1 number,birthday1 date,currstate1 number,friendshippolicy1 number)   as begin     insert into t_user(pwd,nikename,sex,birthday,currstate,friendshippolicy)values(pwd1,nikename1,sex1,birthday1,currstate1,friendshippolicy1);commit;     exception when others then       dbms_output.put_line('添加失败！');rollback;   end sp_add_user;/--执行添加用户的存储过程：运行黑框exec sp_add_user('888888','马云','0',to_date('2008-08-13','yyyy-mm-dd'),1,3);PL/SQL里begin   sp_add_user('888888','马云','0',to_date('2008-08-13','yyyy-mm-dd'),1,1);end;--创建好友表create table t_friend( ufid number(38)  not null primary key, userid number(38) not null, friendid  number(38) not null, foreign key (userid) references t_user(userid), foreign key (friendid) references t_user(userid));--创建序列 create sequence seq_t_friend start with 1 increment by 1 nominvalue maxvalue 10000 nocycle nocache/--创建触发器create or replace trigger trigger_t_friendbefore insert on t_friend  --当向表中执行插入操作时触发此触发器for each row  --对每一行都检查是否触发beginselect seq_t_friend.nextval  into:new.ufid from dual;end;/--插入值*insert into t_friend(userid,friendid)values(10001,10006);insert into t_friend(userid,friendid)values(10002,10007);insert into t_friend(userid,friendid)values(10003,10008);--聊天信息表***倒数三个属性检查约束及默认值设置create table t_message( messageid number(38) not null primary key, fromuserid  number(38) not null, touserid number(38) not null, foreign key(fromuserid) references t_user(userid), foreign key(touserid) references t_user(userid), content nvarchar2(500), messagetype number(1) check(messagetype=1 or messagetype=0), state number(1) check(state=1 or state=0), sendtime date );--创建序列 create sequence seq_t_message start with 1 increment by 1 nominvalue maxvalue 10000 nocycle nocache/--创建触发器create trigger trigger_t_messagebefore insert on t_message  --当向表中执行插入操作时触发此触发器for each row  --对每一行都检查是否触发beginselect seq_t_message.nextval  into:new.messageid from dual;end;/insert into t_message(fromuserid,touserid,content,messagetype,state,sendtime)values(10001,10006,'在吗？',1,1,to_date('2015-08-05','yyyy-mm-dd'));insert into t_message(fromuserid,touserid,content,messagetype,state,sendtime)values(10002,10007,'干什么呢啊？',1,1,to_date('2005-07-30','yyyy-mm-dd'));--创建视图：查询某个好友的聊天记录create view f_messageasselect content from t_message where touserid =10006;select * from f_message;--创建视图：查询某个用户的所有好友。create or replace view all_friendasselect userid,friendid from t_friend where userid=10001; select * from  all_friend;--包规范（查找用户的过程）create or replace package search_userastype usercursor is ref cursor;procedure sp_basic_search(u_id number,u_name nvarchar2);--普通查找procedure senior_search_user(u_sex nvarchar2,u_age number);--高级查找end search_user;/--包主体（查找用户的过程）create or replace package body search_useras --普通查找  procedure sp_basic_search(u_id number,u_name nvarchar2)--普通查找as s_u_all t_user % rowtype;u_count number;s_u_all t_user % rowtype;s_u_id number;s_u_nik nvarchar2(20);s_u_sex nvarchar2(5);begincase when u_id is not null thenselect count(*) into u_count from t_user where userid=u_id;if u_count=!0 then select userid,nikename,sex into s_u_id,s_u_nik,s_u_sex from t_user where userid=u_id;dbms_output.put_line('ID编号：'||s_u_id||'  昵称：'||s_u_nik||'  性别'||s_u_sex);else dbms_output.put_line('用户不存在！');end ifwhen u_id is null and u_name is not null thenselect count(*) into u_count from t_user where nikename=u_name;if u_count=!0 then select userid,nikename,sex into s_u_id,s_u_nik,s_u_sex from t_user where nikename=u_name;dbms_output.put_line('ID编号：'||s_u_id||'  昵称：'||s_u_nik||'  性别'||s_u_sex);else dbms_output.put_line('用户不存在！');end if;else dbms_output.put_line('号码和昵称不能同时为空！');end case;end sp_basic_search;--高级查找procedure senior_search_user(u_sex nvarchar2,u_age number)asdu_cursor usercursor;u_count number;u_id1 number;u_age1 number;s_u_all t_user % rowtype;s_u_id number;s_u_nik nvarchar2(20);s_u_sex nvarchar2(5);begincasewhen u_sex='女' AND u_age is not null thenselect count(*) into u_count from t_user where sex=0 and (extract(year from sysdate)-extract(year from birthday))=u_age;open du_cursor for select userid from t_user where sex=0 and (extract(year from sysdate)-extract(year from birthday))=u_age;if u_count<>0 then  loop fetch du_cursor into u_id1;   exit when du_cursor%notfound;select userid,nikename,decode(sex,0,'女',1,'男'),(extract(year from sysdate)-extract(year from birthday))into s_u_id,s_u_nik,s_u_sex,u_age1 from t_user where userid=u_id1; dbms_output.put_line('ID：'||s_u_id||'  昵称：'||s_u_nik||'  性别：'||s_u_sex||'  年龄：'||u_age1);end loop;close du_cursor;else dbms_output.put_line('用户不存在！');end if;when u_sex='男' AND u_age is not null  thenselect count(*) into u_count from t_user where sex=1 and (extract(year from sysdate)-extract(year from birthday))=u_age;open du_cursor for select userid from t_user where sex=1 and (extract(year from sysdate)-extract(year from birthday))=u_age;if u_count<>0 thenloop fetch du_cursor into u_id1;   exit when du_cursor%notfound;select userid,nikename,decode(sex,0,'女',1,'男'),(extract(year from sysdate)-extract(year from birthday))into s_u_id,s_u_nik,s_u_sex,u_age1 from t_user where userid=u_id1; dbms_output.put_line('ID：'||s_u_id||'  昵称：'||s_u_nik||'  性别：'||s_u_sex||'  年龄：'||u_age1);end loop;close du_cursor;else dbms_output.put_line('用户不存在！');end if;else dbms_output.put_line('输入有误！');end case;end senior_search_user;end search_user;/--执行测试set serveroutput onexec search_user.sp_basic_search(10002,'徐本锡');exec search_user.senior_search_user('男',7);set serveroutput on --创建查询用户信息视图create or replace view v_useras select userid, pwd , nikename,  decode(sex,0,'女',1,'男')性别, birthday, statedesc 状态, policy as 交友策略 from t_user join t_onlinestate on currstate=stateid join t_friendshippolicy on friendshippolicy=polid;--执行查询用户信息视图select* from v_user;--好友管理包规范create or replace package manage_friendas    --申请添加好友procedure add_friend(i_userid t_user.userid%type,i_friendid t_user.userid%type);  --处理添加好友信息procedure deal_friend(i_userid t_user.userid%type ,i_friendid t_user.userid%type,sel number);procedure del_friend(id1 number,id2 number);--删除好友的过程end manage_friend;/--好友管理包主体create or replace package body manage_friendas--申请添加好友procedure add_friend(i_userid t_user.userid%type,i_friendid t_user.userid%type)isi_friendshippolicy number;mycount number;o_message varchar2(50);begin  select count(*) into mycount from t_user where t_user.userid=i_friendid;  if mycount=0 then    o_message :='没有该用户！';    elsesearch_user.sp_basic_search(i_friendid,null);select friendshippolicy into i_friendshippolicy from t_user where userid=i_friendid;   case  i_friendshippolicy    when 1 then   insert into t_friend(userid,friendid)values(i_userid,i_friendid);   o_message:='添加成功！';   when 2 then   o_message:='不能添加！';   when 3 then   o_message:='发送验证信息！';   insert into t_message(fromuserid,touserid,content,messagetype,state,sendtime) values(i_userid,i_friendid,'我想加你为好友',0,0,sysdate);   end case;  end if;dbms_output.put_line(o_message);  end add_friend;    --处理添加好友信息procedure deal_friend(i_userid t_user.userid%type ,i_friendid t_user.userid%type,sel number)  iso_message varchar2(50);  begin  update t_message set  t_message.state=1 where t_message.content='我想加你为好友' and t_message.state=0 and t_message.fromuserid=i_friendid;case selwhen 1 then  insert into t_friend(userid,friendid)values(i_userid,i_friendid);   o_message :='处理成功，已经添加好友';when 2 then  o_message :='拒绝添加好友！';end case;dbms_output.put_line(o_message);end deal_friend;--删除好友的过程procedure del_friend(id1 number,id2 number)ascou number;beginselect count(*) into cou from t_friend where userid=id1 and friendid=id2 or userid=id2 and friendid=id1;if cou !=0 thendelete t_friend where userid=id1 and friendid=id2 or userid=id2 and friendid=id1;dbms_output.put_line('删除成功！');else dbms_output.put_line('好友不存在！');end if;end del_friend;end manage_friend;/--申请添加好友测试exec manage_friend.add_friend(10001,10009);--处理添加好友信息exec manage_friend.deal_friend(10001,10009,1);exec manage_friend.deal_friend(10001,10009,2);--删除好友测试select * from t_friend;insert into t_friend(userid,friendid)values(10004,10005);insert into t_friend(userid,friendid)values(10005,10004);set serveroutput onexec manage_friend.del_friend(10001,10009);--聊天信息管理--包规范create or replace package manage_messageas--查找消息procedure sel_message(u_id number,f_id number);--添加消息procedure add_message(u_id number,f_id number,a nvarchar2);--删除消息procedure del_message(u_id number,f_id number);end manage_message;/-包主体create or replace package body manage_messageas--查找消息procedure sel_message(u_id number,f_id number)isc1 number;c2 number;m t_message%rowtype;c3 number;beginselect count(*) into c1 from t_user where userid=u_id or userid=f_id;if c1=2 then select count(*) into c2 from t_friend where userid=u_id and friendid=f_id or userid=f_id and friendid=u_id;if c2!=0 thenselect count(*) into c3 from t_message where fromuserid=u_id and touserid=f_id or fromuserid=f_id and touserid=u_id;if c3 !=0 thenselect * into m from t_message where fromuserid=u_id and touserid=f_id or fromuserid=f_id and touserid=u_id;dbms_output.put_line('发送者：'||m.fromuserid||'  接收者：'||m.touserid||'  消息内容：'||m.content||'  发送时间'||m.sendtime);else dbms_output.put_line('聊天信息为空！');end if;else dbms_output.put_line('不是好友关系！');end if;else dbms_output.put_line('用户不存在！');end if;end sel_message;--添加消息procedure add_message(u_id number,f_id number,a nvarchar2)isc1 number;c2 number;beginselect count(*) into c1 from t_user where userid=u_id or userid=f_id;if c1=2 then select count(*) into c2 from t_friend where userid=u_id and friendid=f_id or userid=f_id and friendid=u_id;if c2!=0 theninsert into t_message(fromuserid,touserid,content,messagetype,state,sendtime)values(u_id,f_id,a,1,1,sysdate); dbms_output.put_line('添加消息成功！');else dbms_output.put_line('不是好友关系！');end if;else dbms_output.put_line('用户不存在！');end if;end add_message;--删除消息procedure del_message(u_id number,f_id number)isc1 number;c2 number;m t_message%rowtype;c3 number;beginselect count(*) into c1 from t_user where userid=u_id or userid=f_id;if c1=2 then select count(*) into c2 from t_friend where userid=u_id and friendid=f_id or userid=f_id and friendid=u_id;if c2!=0 thenselect count(*) into c3 from t_message where fromuserid=u_id and touserid=f_id or fromuserid=f_id and touserid=u_id;if c3 !=0 thendelete from t_message where fromuserid=u_id and touserid=f_id or fromuserid=f_id and touserid=u_id; dbms_output.put_line('删除消息成功！');else dbms_output.put_line('聊天信息为空！');end if;else dbms_output.put_line('不是好友关系！');end if;else dbms_output.put_line('用户不存在！');end if;end del_message;end manage_message;/--查询聊天信息测试exec manage_message.sel_message(10003,10008);--添加聊天信息测试declare a nvarchar2(500);begina:='快来上课！老师点名了!';manage_message.add_message(10003,10008,a);end;/--删除聊天信息测试exec manage_message.del_message(10003,10008);