SQL> desc content;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
CONTENTID NOT NULL NUMBER
SUBJECT NOT NULL VARCHAR2(50)
WORDS VARCHAR2(1000)
USERNAME VARCHAR2(50)
CREATETIME VARCHAR2(30)
LASTANSWERTIME VARCHAR2(30)
HITCOUNT NUMBER
SQL> create or replace procedure update_hitcount --查看帖子详细信息时,该帖子人气指数更新
2 (parm_subject varchar2,
3 parm_createtime varchar2)
4 as
5 v_contentid number;
6 begin
7 select contentid into v_contentid from content
8 where subject=parm_subject and createtime=parm_createtime;
9 update content
10 set hitcount=hitcount+1
11 where contentid=v_contentid;
12 commit;
13 end;
14 /
过程已创建。
SQL> CREATE OR REPLACE PROCEDURE DELETE_CONTENT --删除帖子
2 (parm_username varchar2,
3 parm_createtime varchar2,
4 parm_return out number) --返回值 1:管理员,删除成功 0:普通用户,权限不足, 不允许删除
5 as
6 begin
7 if parm_username='Tsoft' then
8 delete content
9 where username=parm_username and createtime=parm_createtime;
10 commit;
11 parm_return:=1;
12 else
13 parm_return:=0;
14 end if;
15 end;
16 /
过程已创建。
SQL>
SQL> create table hfinfo --回帖信息
2 (hfid number not null constraint pk_hfid primary key, --回复id
3 contentid number not null, --帖子id
4 hfsubject varchar(50), --回复主题
5 hfnr varchar2(1000), --回复内容
6 hfname varchar2(50), --回复用户名
7 hftime varchar2(30) --回复时间
8 );
表已创建。
SQL> alter table hfinfo
2 add
3 constraint fk_contentid foreign key(contentid) references content(contentid);
表已更改。
SQL> alter table hfinfo
2 add
3 constraint fk_hfname foreign key(hfname) references userinfo(username);
表已更改。
SQL> desc userinfo;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
USERID NOT NULL NUMBER
USERNAME NOT NULL VARCHAR2(50)
USERPWD VARCHAR2(50)
ENAME VARCHAR2(50)
EMAIL VARCHAR2(50)
LOGO VARCHAR2(30)
SQL> create or replace package pkg_usercontent
2 as
3 type cur_usercontent is ref cursor;
4 procedure select_usercontent
5 (parm_subject varchar2,
6 parm_username varchar2,
7 parm_createtime varchar2,
8 parm_hitcount number,
9 parm_rec out cur_usercontent);
10 end pkg_usercontent;
11 /
程序包已创建。
SQL> create or replace package body pkg_usercontent
2 as
3 procedure select_usercontent
4 (parm_subject varchar2,
5 parm_username varchar2,
6 parm_createtime varchar2,
7 parm_hitcount number,
8 parm_rec out cur_usercontent)
9 is
10 begin
11 open parm_rec for
12 select content.username,logo,email,contentid,subject,words,createtime,hitcount
13 from content,userinfo
14 where content.username=userinfo.username and subject=parm_subject and
15 username=parm_username and createtime=parm_createtime and hitcount=parm_hitcount;
16 end;
17 end pkg_usercountent;
18 /
警告: 创建的包体带有编译错误。
SQL> show error
PACKAGE BODY PKG_USERCONTENT 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/5 PLS-00113: END 标识符 'PKG_USERCOUNTENT' 必须同
'PKG_USERCONTENT' 匹配 (在第 1 行,第 14 列)
SQL> l17
17* end pkg_usercountent;
SQL> c/usercountent/usercontent/
17* end pkg_usercontent;
SQL> run
1 create or replace package body pkg_usercontent
2 as
3 procedure select_usercontent
4 (parm_subject varchar2,
5 parm_username varchar2,
6 parm_createtime varchar2,
7 parm_hitcount number,
8 parm_rec out cur_usercontent)
9 is
10 begin
11 open parm_rec for
12 select content.username,logo,email,contentid,subject,words,createtime,hitcount
13 from content,userinfo
14 where content.username=userinfo.username and subject=parm_subject and
15 username=parm_username and createtime=parm_createtime and hitcount=parm_hitcount;
16 end;
17* end pkg_usercontent;
警告: 创建的包体带有编译错误。
SQL> show error
PACKAGE BODY PKG_USERCONTENT 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/7 PL/SQL: SQL Statement ignored
15/9 PL/SQL: ORA-00918: 未明确定义列
SQL> l15
15* username=parm_username and createtime=parm_createtime and hitcount=parm_hitcount;
SQL> c/username=/content.username=/
15* content.username=parm_username and createtime=parm_createtime and hitcount=parm_hitcount;
SQL> run
1 create or replace package body pkg_usercontent
2 as
3 procedure select_usercontent
4 (parm_subject varchar2,
5 parm_username varchar2,
6 parm_createtime varchar2,
7 parm_hitcount number,
8 parm_rec out cur_usercontent)
9 is
10 begin
11 open parm_rec for
12 select content.username,logo,email,contentid,subject,words,createtime,hitcount
13 from content,userinfo
14 where content.username=userinfo.username and subject=parm_subject and
15 content.username=parm_username and createtime=parm_createtime and hitcount=parm_hitcount;
16 end;
17* end pkg_usercontent;
程序包主体已创建。
SQL>