set serveroutput on
update 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_admin
before insert on t_admin --当向表中履行插入操作时触发此触发器
for each row --对每行都检讨是不是触发
begin
select 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_user
before insert on t_user --当向表中履行插入操作时触发此触发器
for each row --对每行都检讨是不是触发
begin
select 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_friend
before insert on t_friend --当向表中履行插入操作时触发此触发器
for each row --对每行都检讨是不是触发
begin
select 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_message
before insert on t_message --当向表中履行插入操作时触发此触发器
for each row --对每行都检讨是不是触发
begin
select 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_message
as
select content from t_message where touserid =10006;
select * from f_message;
--创立视图：查询某个用户的所有好友。
create or replace view all_friend
as
select userid,friendid from t_friend where userid=10001;
select * from all_friend;
--包规范（查找用户的进程）
create or replace package search_user
as
type 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_user
as
--普通查找
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);
begin
case
when u_id is not null then
select 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 if
when u_id is null and u_name is not null then
select 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)
as
du_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);
begin
case
when u_sex="女" AND u_age is not null then
select 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 then
select 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 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;
else dbms_output.put_line("输入有误！");
end case;
end senior_search_user;
end search_user;
/
--履行测试
set serveroutput on
exec search_user.sp_basic_search(10002,"徐本锡");
exec search_user.senior_search_user("男",7);
set serveroutput on
--创立查询用户信息视图
create or replace view v_user
as
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_friend
as
--申请添加好友
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_friend
as
--申请添加好友
procedure add_friend(
i_userid t_user.userid%type,
i_friendid t_user.userid%type)
is
i_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 :="没有该用户！";
else
search_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)
is
o_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 sel
when 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)
as
cou number;
begin
select count(*) into cou from t_friend where userid=id1 and friendid=id2 or userid=id2 and friendid=id1;
if cou !=0 then
delete 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 on
exec manage_friend.del_friend(10001,10009);
--聊天信息管理
--包规范
create or replace package manage_message
as
--查找资讯
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_message
as
--查找资讯
procedure sel_message(u_id number,f_id number)
is
c1 number;c2 number;m t_message%rowtype;c3 number;
begin
select 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 then
select 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 then
select * 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)
is
c1 number;c2 number;
begin
select 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 then
insert 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)
is
c1 number;c2 number;m t_message%rowtype;c3 number;
begin
select 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 then
select 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 then
delete 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);
begin
a:="快来上课！老师点名了!";
manage_message.add_message(10003,10008,a);
end;
/
--删除聊天信息测试
exec manage_message.del_message(10003,10008);
set serveroutput on
update t_onlinestate set state
阅读背景：
Oracle聊天系统设计
来源：互联网
分享到：
