就业培训学习记录-day005

课堂任务

触发器

触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用。

触发器类型

根据触发器所创建的语句及所影响的对象的不同，将触发器分为以下3类。但今天的内容主要只说第一种。

1. DML触发器
   对数据表进行DML语句操作（如insert、update、delete）时所触发的触发器。
   语法：

create [or replace] trigger trigger_name
{before | after} trigger_event
on table_name
[for each row]
[when trigger_condition]
trigger_body

语法解释：
trigger_name：触发器名称
before | after：指定触发器是在触发事件发生之前触发还事件发生之后触发
trigger_event：触发事件，在DML触发器中主要为insert、update、delete等
table_name：表名，表示发生触发器作用的对象
for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
when trigger_condition：添加的触发条件
trigger_body：触发体，是标准的PL/SQL语句块

2. 替代触发器（instead of触发器）
   对视图进行操作时定义的触发器，替代触发器只能定义在视图上。
   语法：

create [or replace] trigger trigger_name
instead of trigger_event
on view_name
for each row
[when trigger_condition]
trigger_body

3. 系统事件触发器
   对数据库实例或某个用户模式进行操作时定义的触发器，可以分为：数据库系统触发器和用户触发器。

创建触发器

禁止在非工作时间（9点到18点）对表进行修改

create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
  if to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
    raise_application_error(-20003,'not time work');
  end if;
end;

如果想看效果，可以把上面的时间改一下，然后往emp表中插入一条数据即可。

insert into emp values(5050,'JOE',null,null,sysdate,1000,null,10);

在行: 1 上开始执行命令时出错 -
insert into emp values(5050,'JOE',null,null,sysdate,1000,null,10)
错误报告 -
SQL 错误: ORA-20003: not time work
ORA-06512: 在 "SCOTT.TR_EMP_TIME", line 3
ORA-04088: 触发器 'SCOTT.TR_EMP_TIME' 执行过程中出错

在插入新员工后，输出插入成功的提示信息

create trigger tr_emp_insert
after insert
on emp
declare
begin
  dbms_output.put_line('成功插入新员工');
end;

关键字new，old

oracle中，这两个变量只有在使用了关键字FOR EACH ROW时才存在，old代表老数据，new代表新数据，不过二者在使用时是有限制的。
insert时，只有new，没有old 
delete时，只有old，没有new
update时，二者都可用

修改员工工资后，输出新旧工资的值

create or replace trigger checksal
before update
on emp
for each row
begin
if :new.sal!=:old.sal then
  dbms_output.put_line('旧工资：'||:old.sal);
  dbms_output.put_line('新工资：'||:new.sal);
end if;
end checksal;

执行：

update emp set sal=sal+100 where empno=7566 or empno=7521;
update emp set sal=sal+0 where empno=7788;

删除触发器

语法：

drop trigger trigger_name;

存储过程

指存储在数据库中供所有用户程序调用的一组为了完成特定功能的SQL语句集，一次编译后永久有效。

创建存储过程

语法如下：

create [or replace] procedure 过程名(参数列表)
as
begin
  plsql子程序体;
end 过程名;

1. 输出'hello procedure'

create or replace procedure sayhello
as
begin
  dbms_output.put_line('hello procedure');
end;

调用方法1：

set serveroutput on
exec sayhello;

2. 带参数的存储过程，不commit，谁调用谁commit

create or replace procedure raiseSal(eno number)
as
  --定义变量
  psal emp.sal%type;
begin
  --获取涨前的薪水
  select sal into psal from emp where empno=eno;
  --涨工资
  update emp set sal=sal+100 where empno=eno;
  --此处不commit
  dbms_output.put_line('涨前:'||psal||'涨后:'||(psal+100));
end raiseSal;

调用方法2：

set serveroutput on
begin
  raiseSal(7566);
  --提交事务
  commit;
end;

删除存储过程

语法：

DROP PROCEDURE 过程名;

存储函数

函数（function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

创建存储函数

语法:

cerate [or replace] function 函数名(参数列表)
return 函数值类型
as
begin
  plsql子程序体;
end 函数名;

1. 查看指定员工的年收入（参数为员工编号）

create or replace function queryEmpIncome(eno number) return number
as
  --定义变量保存月薪和奖金
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  --返回年收入
  return psal*12+nvl(pcomm,0);
end queryEmpIncome;

调用方法1：

select queryEmpIncome(7839) from dual;

2. 输入员工名，判断该员工是否存在

create or replace function getCountsByName(name varchar2) return number
as
  --定义变量
  counts number;
begin
  select count(*) into counts from emp where ename=name;
  if counts>0 then
    dbms_output.put_line('用户名已存在');
  else
    dbms_output.put_line('用户名不存在');
  end if;
  return counts;
end getCountsByName;

调用方法2：

declare
  counts number;
begin
  counts:=getCountsByName('King');
  DBMS_OUTPUT.put_line(counts);
  counts:=getCountsByName('KING');
  DBMS_OUTPUT.put_line(counts);
end;

删除存储函数

语法：

DROP FUNCTION 函数名;

存储函数和存储过程的区别

• 存储过程的关键字是procedure，存储函数的关键字是function。
• 存储函数比存储过程多两个return。
• 本质上的区别就是存储过程没有返回值，而存储函数有返回值。
• 我们可以使用存储函数有返回值的特性，来自定义函数。而存储过程不能用来自定义函数。

课后任务

通过编写储存过程和储存函数，来尝试设计一个系统。
（PS：这个是和我一个同学写的练习作业，有需要的话参考学习即可，实际开发并不是这样写）

功能设计

打算模拟一个论坛系统，要实现的功能有：

1. 注册
2. 登录
3. 查看所有的主题
4. 查看某个主题下的帖子
5. 查看某个帖子的主题
6. 查看所有的主题，查看所有的帖子，按最后回复时间排序（分页）
7. 查看某个用户发表的的言论
8. 搜索帖子（模糊查找）
9. 发布帖子（触发器，更新用户表中的发帖数）
10. 回复帖子（触发器，更新帖子表中的回复数）
11. 查看有哪些用户（分页）
12. 隐藏/显示帖子（隐藏/显示某个用户的某个帖子）

表设计

点击查看详细内容

--创建用户表
create table UserT(
	Usid int primary key,
	Uname nvarchar2(100) not null,
	Password nvarchar2(100) not null,
	--帖子数
	Pnum int not null
);

--创建主题表
create table TopicT(
	Tid int primary key,
	Tname nvarchar2(100) not null
);

--创建帖子表
create table PostT(
	Tid int not null,
	Pid int primary key,
	--帖子名称
	Pname nvarchar2(100) not null,
	--帖子内容
	Pcontent nvarchar2(1000) not null,
	--发帖时间
	Pdate timestamp default sysdate,
	Usid int not null,
	--回复数
	Replynum int default 1,
	--最后回复时间
	Lastreplytime timestamp default sysdate,
	--隐藏标识（0显示，1隐藏）
	Screenmark int default 0,
	foreign key(Tid) references TopicT(Tid),
	foreign key(Usid) references UserT(Usid)
);

--创建回复表
create table ReplyT(
	Pid int not null,
	--回复楼层
	Replyfloor int default 2,
	--回复内容
	Replycontent nvarchar2(1000) not null,
	--回复时间
	Replytime timestamp default sysdate,
	Usid int not null,
	foreign key(Pid) references PostT(Pid),
	foreign key(Usid) references UserT(Usid)
);

insert into UserT values(1,'test','test',1);
insert into UserT values(2,'admin','admin',1);
insert into UserT values(3,'root','root',2);
insert into UserT values(4,'rose','rose',2);
insert into UserT values(5,'tom','tom',0);

insert into TopicT values(1,'疫情防控');
insert into TopicT values(2,'智能家居');
insert into TopicT values(3,'节约资源');
insert into TopicT values(4,'就业指导');
insert into TopicT values(5,'爱护环境');

insert into PostT values(1,1,'打卡出校','不开西一门真麻烦',TO_TIMESTAMP('2019-02-01 14:00:00','yyyy-MM-dd hh24:mi:ss'),1,1,TO_TIMESTAMP('2019-02-01 14:00:00','yyyy-MM-dd hh24:mi:ss'),0);
insert into PostT values(2,2,'智能空调','空调能自动开关就好了',TO_TIMESTAMP('2019-02-02 14:00:00','yyyy-MM-dd hh24:mi:ss'),2,1,TO_TIMESTAMP('2019-02-02 14:00:00','yyyy-MM-dd hh24:mi:ss'),0);
insert into PostT values(3,3,'节约用水','随手关水龙头',TO_TIMESTAMP('2019-02-04 14:00:00','yyyy-MM-dd hh24:mi:ss'),3,2,TO_TIMESTAMP('2019-03-09 10:54:50','yyyy-MM-dd hh24:mi:ss'),0);
insert into PostT values(4,4,'刷算法题','每日一题，慢慢积累',TO_TIMESTAMP('2019-02-05 14:00:00','yyyy-MM-dd hh24:mi:ss'),4,1,TO_TIMESTAMP('2019-03-05 14:54:50','yyyy-MM-dd hh24:mi:ss'),0);
insert into PostT values(4,5,'刷面试题','好想进BAT',TO_TIMESTAMP('2019-02-06 14:00:00','yyyy-MM-dd hh24:mi:ss'),4,3,TO_TIMESTAMP('2019-02-06 14:00:00','yyyy-MM-dd hh24:mi:ss'),0);
insert into PostT values(5,6,'爱护花草','周末去浇水',TO_TIMESTAMP('2019-02-07 14:00:00','yyyy-MM-dd hh24:mi:ss'),5,1,TO_TIMESTAMP('2019-02-07 14:00:00','yyyy-MM-dd hh24:mi:ss'),0);

insert into ReplyT values(3,2,'随手关水龙头，好评',TO_TIMESTAMP('2019-03-09 10:54:50','yyyy-MM-dd hh24:mi:ss'),1);
insert into ReplyT values(5,2,'BAT是什么',TO_TIMESTAMP('2019-02-06 12:00:00','yyyy-MM-dd hh24:mi:ss'),1);
insert into ReplyT values(5,2,'百度阿里腾讯',TO_TIMESTAMP('2019-02-06 14:00:00','yyyy-MM-dd hh24:mi:ss'),2);

--DROP TABLE ReplyT;
--DROP TABLE PostT;
--DROP TABLE TopicT;
--DROP TABLE UserT;

PL/SQL实现脚本

点击查看详细内容

--1.注册
create or replace procedure zc(puname nvarchar2,pw nvarchar2)
as
  cursor cemp is select uname from usert;
--  cursor cemp1 is select max(usid) from usert;
  pname nvarchar2(100);
  puid int := 0;
  pmax int:=0;
  i number :=0;
begin
  select max(usid)into pmax from usert;
  open cemp;
  loop
    fetch cemp into pname;
    exit when cemp%notfound;
    if pname=puname then i:=1;
      exit;
    else i:=0;
    end if;
  end loop;
  close cemp;
  
--  open cemp1;
--    fetch cemp1 into pmax;
--  close cemp1;
  puid:=pmax+1;
  if i=1 then dbms_output.put_line('用户名已存在，请输入新的用户');
  else insert into usert values(puid,puname,pw,0);
  commit;
  end if;
end;

--2.登录
create or replace procedure dl(puname nvarchar2,pw nvarchar2)
as
  cursor cemp is select uname,password from usert;
  pname nvarchar2(100);
  ppw nvarchar2(100);
  i number :=0;
begin
  open cemp;
  loop
    fetch cemp into pname,ppw;
    exit when cemp%notfound;
    if pname=puname and ppw=pw then i:=1;
      dbms_output.put_line('登录成功！');
      exit;
    else i:=0;
    end if;
  end loop;
  if i=0 then dbms_output.put_line('请输入正确的用户名或密码！');
  close cemp;
  end if;
end;

--3.查看所有的主题
create or replace procedure ckt
as
  cursor cemp is select tid,tname from topict;
  ptid int;
  ptname nvarchar2(100);
begin
  open cemp;
  loop
    fetch cemp into ptid,ptname;
    exit when cemp%notfound;
    dbms_output.put_line(ptid||'  '||ptname);
  end loop;
  close cemp;
end;

--04.查看某个主题下的帖子
create or replace procedure getPostByTid(ptid in topict.tid%type)
as
  pres postt%rowtype;
begin
	select * into pres from postt where tid=ptid;
  DBMS_OUTPUT.PUT_LINE('主题ID:'||pres.tid);
  DBMS_OUTPUT.PUT_LINE('帖子ID:'||pres.pid);
  DBMS_OUTPUT.PUT_LINE('帖子标题:'||pres.pname);
  DBMS_OUTPUT.PUT_LINE('帖子内容:'||pres.pcontent);
  DBMS_OUTPUT.PUT_LINE('发帖日期:'||pres.pdate);
  DBMS_OUTPUT.PUT_LINE('作者ID:'||pres.usid);
  DBMS_OUTPUT.PUT_LINE('回复数:'||pres.replynum);
  DBMS_OUTPUT.PUT_LINE('最后回复时间:'||pres.lastreplytime);
  DBMS_OUTPUT.PUT_LINE('屏蔽状态:'||pres.screenmark);
exception
  when no_data_found then RAISE_APPLICATION_ERROR(-20001,'帖子不存在，请确认输入的帖子ID无误');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end getPostByTid;

--05.查看某个帖子的主题
create or replace procedure getTopicByPid(ppid in topict.tid%type)
as
  pres topict%rowtype;
  ptid postt.tid%type;
begin
	select tid into ptid from postt where pid=ppid;
  select * into pres from topict where tid=ptid;
  DBMS_OUTPUT.PUT_LINE('主题ID:'||pres.tid);
  DBMS_OUTPUT.PUT_LINE('主题名称:'||pres.tname);
exception
  when no_data_found then RAISE_APPLICATION_ERROR(-20002,'主题不存在，请确认输入的主题ID无误');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end getTopicByPid;

--06.查看所有的主题，查看所有的帖子，按最后回复时间排序（分页）
create or replace procedure getAllTopicsAndPosts(row number,page number)
as
  --定义游标
  cursor c1 is
  select *
  from (
    select rownum "序号",ttid "主题ID",tname "主题名称",pid "帖子ID",pname "帖子名称",pcontent "帖子内容",
        to_char(pdate,'yyyy-mm-dd hh24:mi:ss') pdate,uusid "发帖人ID",uname "发帖人",
          replynum "回复数",to_char(lastreplytime,'yyyy-mm-dd hh24:mi:ss') "最后回复时间"
    from(
      select t.tid ttid,tname,pid,pname,pcontent,pdate,u.usid uusid,uname,replynum,lastreplytime
      from topict t,postt p,usert u
      where t.tid=p.tid(+) and p.usid=u.usid and screenmark!=1
    ) e1
  where rownum <= row*page
  ) e2
  where e2."序号" > row*(page-1)
  order by e2."主题ID",e2."最后回复时间" desc;
  --定义异常
  page_too_big exception;
  number_error exception;
  --定义变量
  prownum number;
  ptid topict.tid%type;
  ptname topict.tname%type;
  ppid postt.pid%type;
  ppname postt.pname%type;
  ppcontent postt.pcontent%type;
  ppdate varchar(40);
  puusid usert.usid%type;
  puname usert.uname%type;
  preplynum postt.replynum%type;
  plastreplytime varchar(40);
  counts number;
  i number:=0;
begin
  if regexp_like(row,'^[1-9]d*$') and
    regexp_like(page,'^[1-9]d*$') then
    DBMS_OUTPUT.PUT_LINE('输入的数值都为正整数');
  else
    raise number_error;
  end if;
  --获取未隐藏帖子的总数
  select count(tid) into counts from postt where screenmark!=1;
  --判断页码是否过大
  if row*(page-1) >= counts then
    raise page_too_big;
  end if;
	open c1;
  loop
    --退出条件，不写或者写成c1%notfound会出bug
    exit when i=(counts-row*(page-1));
    fetch c1 into prownum,ptid,ptname,ppid,ppname,ppcontent,ppdate,puusid,puname,preplynum,plastreplytime;
    DBMS_OUTPUT.PUT_LINE(
    '序号:'||prownum||'  '||
    '主题ID：'||ptid||'  '||
    '主题名称：'||ptname||'  '||
    '帖子ID：'||ppid||'  '||
    '帖子名称：'||ppname||'  '||
    '帖子内容：'||ppcontent||'  '||
    '发帖日期：'||ppdate||'  '||
    '发帖人ID：'||puusid||'  '||
    '发帖人：'||puname||'  '||
    '回复数：'||preplynum||'  '||
    '最后回复时间：'||plastreplytime
    );
    i:=i+1;
  end loop;
exception
  when page_too_big then RAISE_APPLICATION_ERROR(-20003, '页码过大');
  when number_error then RAISE_APPLICATION_ERROR(-20004, '输入的数值非正整数');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end getAllTopicsAndPosts;

--7.查看某个用户发表的帖子
create or replace procedure lookPostt(uid in int)
as
  cursor cemp is select usid,pcontent from postt;
  cursor cemp1 is select usid,replycontent from replyt;
  content nvarchar2(100);
  rcontent nvarchar2(100);
  sid int;
begin
  open cemp;
  loop
    fetch cemp into sid,content;
    exit when cemp%notfound;
    if uid=sid then dbms_output.put_line(content);
    end if;
  end loop;
  close cemp;
  open cemp1;
  loop
    fetch cemp1 into sid,rcontent;
    exit when cemp1%notfound;
    if uid=sid then dbms_output.put_line(rcontent);
    end if;
  end loop;
  close cemp1;
end;

--08.搜索帖子（模糊查找）
create or replace procedure fuzzySearch(keyword varchar2)
as
  --定义游标
  cursor c1 is
  select *
  from (
    select rownum "序号",ttid "主题ID",tname "主题名称",pid "帖子ID",pname "帖子名称",
      pcontent "帖子内容",to_char(pdate,'yyyy-mm-dd hh24:mi:ss') "发帖日期",pusid "发帖人ID",
        uname "发帖人",replynum "回复数",to_char(lastreplytime,'yyyy-mm-dd hh24:mi:ss') "最后回复时间",
          replyfloor "回复楼层",replycontent "回复内容",rusid "回复人ID",runame "回复人",
            to_char(replytime,'yyyy-mm-dd hh24:mi:ss') "回复时间"
    from(
      select t.tid ttid,tname,p.pid,pname,pcontent,pdate,p.usid pusid,
        (select uname from usert where usert.usid=p.usid) uname,replynum,
          lastreplytime,replyfloor,replycontent,r.usid rusid,
            (select uname from usert where usert.usid=r.usid) runame,replytime
      from topict t,postt p,replyt r
      where t.tid=p.tid(+) and p.pid=r.pid(+) and screenmark!=1
    ) e1
  ) e2
  where e2."主题名称" like '%'||keyword||'%' or e2."帖子名称" like '%'||keyword||'%' or 
    e2."帖子内容" like '%'||keyword||'%' or e2."发帖人" like '%'||keyword||'%' or 
      e2."回复内容" like '%'||keyword||'%' or e2."回复人" like '%'||keyword||'%'
  order by e2."最后回复时间" desc,e2."回复时间" asc nulls last;
  info_not_found exception;
  --定义变量
  prownum number;
  ptid topict.tid%type;
  ptname topict.tname%type;
  ppid postt.pid%type;
  ppname postt.pname%type;
  ppcontent postt.pcontent%type;
  ppdate varchar(40);
  puusid usert.usid%type;
  puname usert.uname%type;
  preplynum postt.replynum%type;
  plastreplytime varchar(40);
  preplyfloor replyt.replyfloor%type;
  preplycontent replyt.replycontent%type;
  prusid usert.usid%type;
  pruname usert.uname%type;
  preplytime varchar(40);
  counts number;
  i number:=0;
begin
  select count(*) into counts
  from (
    select rownum "序号",ttid "主题ID",tname "主题名称",pid "帖子ID",pname "帖子名称",
      pcontent "帖子内容",to_char(pdate,'yyyy-mm-dd hh24:mi:ss') "发帖日期",pusid "发帖人ID",
        uname "发帖人",replynum "回复数",to_char(lastreplytime,'yyyy-mm-dd hh24:mi:ss') "最后回复时间",
          replyfloor "回复楼层",replycontent "回复内容",rusid "回复人ID",runame "回复人",
            to_char(replytime,'yyyy-mm-dd hh24:mi:ss') "回复时间"
    from(
      select t.tid ttid,tname,p.pid,pname,pcontent,pdate,p.usid pusid,
        (select uname from usert where usert.usid=p.usid) uname,replynum,
          lastreplytime,replyfloor,replycontent,r.usid rusid,
            (select uname from usert where usert.usid=r.usid) runame,replytime
      from topict t,postt p,replyt r
      where t.tid=p.tid(+) and p.pid=r.pid(+) and screenmark!=1
    ) e1
  ) e2
  where e2."主题名称" like '%'||keyword||'%' or e2."帖子名称" like '%'||keyword||'%' or 
    e2."帖子内容" like '%'||keyword||'%' or e2."发帖人" like '%'||keyword||'%' or 
      e2."回复内容" like '%'||keyword||'%' or e2."回复人" like '%'||keyword||'%'
  order by e2."最后回复时间" desc,e2."回复时间" asc nulls last;
  --判断是否有结果
  if counts=0 then
    raise info_not_found;
  end if;
	open c1;
  loop
    --退出条件，不写或者写成c1%notfound会出bug
    exit when i=counts;
    fetch c1 into prownum,ptid,ptname,ppid,ppname,ppcontent,ppdate,puusid,puname,
      preplynum,plastreplytime,preplyfloor,preplycontent,prusid,pruname,preplytime;
    DBMS_OUTPUT.PUT_LINE(
    '序号:'||prownum||'  '||
    '主题ID：'||ptid||'  '||
    '主题名称：'||ptname||'  '||
    '帖子ID：'||ppid||'  '||
    '帖子名称：'||ppname||'  '||
    '帖子内容：'||ppcontent||'  '||
    '发帖日期：'||ppdate||'  '||
    '发帖人ID：'||puusid||'  '||
    '发帖人：'||puname||'  '||
    '回复数：'||preplynum||'  '||
    '最后回复时间：'||plastreplytime||'  '||
    '回复楼层：'||preplyfloor||'  '||
    '回复内容：'||preplycontent||'  '||
    '回复人ID：'||prusid||'  '||
    '回复人：'||pruname||'  '||
    '回复时间：'||preplytime||'  '
    );
    i:=i+1;
  end loop;
exception
  when info_not_found then DBMS_OUTPUT.PUT_LINE('没有相关结果');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end fuzzySearch;

--9.发布帖子（触发器，更新用户表中的发帖数）
create or replace procedure postMessage(tnamee nvarchar2,pnamee nvarchar2,pcontentt nvarchar2,uid int)
--主题，内容，用户id
as 
  cursor cemp is select tname,tid from topict;
  tidmax int;
  pidmax int;
  pdate TIMESTAMP(6);
  date1 TIMESTAMP(6);
  name nvarchar2(100);
  id int;
  topictId int;
  i int;
begin
  i:=0;
  open cemp;
  loop
    fetch cemp into name,id;
    exit when cemp%notfound;
    if name=tnamee then i:=1;
      dbms_output.put_line('主题表中有此主题！');
      exit;
    end if;
  end loop;
  close cemp;
--  select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')into pdate from dual;
  select max(pid) into pidmax from postt;
--  帖子编号
  date1:=sysdate;
  if i=1 then insert into PostT values(id,pidmax+1,pnamee,pcontentt,date1,uid,1,date1,0);
  elsif i=0 then select max(tid) into topictId from topict;
    insert into topict values(topictId+1,tnamee);
    select max(tid) into tidmax from postt;
--    主题编号
    insert into PostT values(tidmax+1,pidmax+1,pnamee,pcontentt,date1,uid,1,date1,0);
  end if;
  commit;
end;

--触发器
create or replace trigger update1
after insert
on PostT
for each row
begin
  update usert set pnum=pnum+1 where usid=:new.usid;
  dbms_output.put_line('数据已更新！');
end update1;

--10.回复帖子（触发器，更新帖子表中的回复数）
create or replace trigger tr_replyt_insert
after insert
on replyt
for each row
begin
  update postt set replynum=replynum+1 where pid=:new.pid;
  dbms_output.put_line('回复数已自动更新');
end;

create or replace procedure reply(in_pid number,in_floor number,in_content nvarchar2,in_usid number)
as
  counts_pid number:=0;
  counts_usid number:=0;
  post_not_exist exception;
  user_not_exist exception;
  length_illegal exception;
  floor_illegal exception;
begin
  select count(pid) into counts_pid from postt where pid=in_pid and screenmark!=1;
  select count(usid) into counts_usid from usert where usid=in_usid;
  if counts_pid=0 or in_pid is null then
    raise post_not_exist;
  end if;
  if counts_usid=0 or in_usid is null then
    raise user_not_exist;
  end if;
  if length(in_content)=0 or in_content is null then
    raise length_illegal;
  end if;
  if in_floor<=1 or in_floor is null then
    raise floor_illegal;
  end if;
  insert into replyt values(in_pid,in_floor,in_content,sysdate,in_usid);
  DBMS_OUTPUT.PUT_LINE('已回复');
exception
  when post_not_exist then RAISE_APPLICATION_ERROR(-20005, '回复的帖子不存在');
  when user_not_exist then RAISE_APPLICATION_ERROR(-20006, '回复的用户不存在');
  when length_illegal then RAISE_APPLICATION_ERROR(-20007, '回复内容非法');
  when floor_illegal then RAISE_APPLICATION_ERROR(-20008, '回复楼层非法');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end reply;

--11.查看有哪些用户（分页）
create or replace procedure paging(row in int,page in int)
as
  cursor cemp is select t.uname,t.usid
  FROM (SELECT ROWNUM AS rowno,usert.*
          FROM usert
           where ROWNUM <= row*page)t
 WHERE t.rowno >row*(page-1);
 uame nvarchar2(100);
 uid int;
begin
  open cemp;
  loop
    fetch cemp into uame,uid;
    exit when cemp%notfound;
    dbms_output.put_line(uame||' '||uid);
  end loop;
  close cemp;
end;

--12.隐藏/显示帖子（隐藏/显示某个用户的某个帖子）
create or replace procedure changePostStatus(in_usid number,in_pid number,in_status number)
as
  counts_pid number:=0;
  counts_usid number:=0;
  post_not_exist exception;
  user_not_exist exception;
  status_illegal exception;
begin
  select count(pid) into counts_pid from postt where pid=in_pid;
  select count(usid) into counts_usid from usert where usid=in_usid;
  if counts_pid=0 or in_pid is null then
    raise post_not_exist;
  end if;
  if counts_usid=0 or in_usid is null then
    raise user_not_exist;
  end if;
  if in_status not in (0,1) then
    raise status_illegal;
  end if;
  update postt set screenmark=in_status where pid=in_pid;
  if in_status=1 then
    DBMS_OUTPUT.PUT_LINE('帖子已隐藏');
  else
    DBMS_OUTPUT.PUT_LINE('帖子已显示');
  end if;
exception
  when post_not_exist then RAISE_APPLICATION_ERROR(-20009, '帖子不存在');
  when user_not_exist then RAISE_APPLICATION_ERROR(-20010, '用户不存在');
  when status_illegal then RAISE_APPLICATION_ERROR(-20011, '状态设置非法');
  when others then RAISE_APPLICATION_ERROR(-20000,'其他异常');
end changePostStatus;

/*
01.注册
02.登录
03.查看所有的主题
04.查看某个主题下的帖子
05.查看某个帖子的主题
06.查看所有的主题，查看所有的帖子，按最后回复时间排序（分页）
07.查看某个用户发表的的言论
08.搜索帖子（模糊查找）
09.发布帖子（触发器，更新用户表中的发帖数）
10.回复帖子（触发器，更新帖子表中的回复数）
11.查看有哪些用户（分页）
12.隐藏/显示帖子（隐藏/显示某个用户的某个帖子）
*/
--预处理
set serveroutput on
--程序入口
declare 
begin
--  zc('abc','123456');  --01.注册，参数：用户名，密码
--  dl('Abc','123456');  --02.登录，参数：用户名，密码
--  ckt();  --03.查看所有的主题，参数：无
--  getPostByTid(1);  --04.查看某个主题下的帖子，参数：主题ID
--  getTopicByPid(4);  --05.查看某个帖子的主题，参数：帖子ID
--  getAllTopicsAndPosts(3,2);  --06.查看所有的主题，查看所有的帖子，按最后回复时间排序（分页），参数：每页行数，查看第几页
--  lookPostt(2);  --07.查看某个用户发表的的言论，参数：用户ID
--  fuzzySearch('BATT');  --08.搜索帖子（模糊查找），参数：关键词
--  postMessage('疫情防控','健康饮食','多吃蔬菜少吃油炸食品',3);  --09.发布帖子（触发器，更新用户表中的发帖数），参数：主题名称，帖子名称，帖子内容，用户ID
--  reply(5,2,'冲冲冲！',1);  --10.回复帖子（触发器，更新帖子表中的回复数），参数：帖子ID，回复楼层，回复内容，用户ID
--  paging(5,2);  --11.查看有哪些用户（分页），参数：每页行数，查看第几页
--  changePostStatus(1,1,1);  ---12.隐藏/显示帖子（隐藏/显示某个用户的某个帖子），参数：用户ID，帖子ID，隐藏状态（0显示，1隐藏）
end;