由匿名块引出：函数、存储过程。视图、序列

/**
1.plsql语句是匿名块，并且不能在数据库中永久的保存，因为我们要对匿名块进行封装
  1.1匿名块的缺点：相应的语句不能永久的保存到服务器中，复用性太差
（不可能保存到客户端，都是保存到服务器端）
  1.2为了解决这个问题：引入了函数和存储过程，都是对匿名块的封装
**/
----1.无参函数,比较两个数的大小
 create or replace function maxNum -----注意：无论是函数还是存储过程，返回值和输入输出参数都不能指定大小
 return number
 is
   v_num1 number(3):=100;
   v_num2 number(3):=101;
   v_max number;---接收返回值
 begin
   if v_num1>v_num2 then
     v_max:=v_num1;
   else
     v_max:=v_num2;
  end if ;
  return v_max;
 end;
 ----由于没有输出参数，可以使用select语句调用，也可以使用匿名块调用上面的函数
 select maxNum from dual
 ------------------------------
 --2.带参函数(输出参数in，就是形参)
  create or replace function maxNum(v_num1 in number,v_num2  number)---in是输入参数，可以不写。out是输出参数
 return number
 is
   v_max number;---接收返回值
 begin
   if v_num1>v_num2 then
     v_max:=v_num1;
   else
     v_max:=v_num2;
  end if ;
  return v_max;
 end;
 ----由于没有输出参数，可以使用select语句调用，也可以使用匿名块调用上面的函数
  select maxNum(12,500) from dual
 ---------------------------
 --3.带参函数(out是输出参数),如果有输出函数，就不能用select语句测试了，就得用匿名块测试了
 create or replace function maxNum(v_num1 in number,v_num2 in number,v_r out number)---in是输入参数，可以不写。out是输出参数
 return number
 is
   v_max number;---用来接收返回值
 begin
   if v_num1>v_num2 then
     v_max:=v_num1;
      v_r:=1;
   elsif v_num1<v_num2 then
     v_max:=v_num2;
      v_r:=-1;
  else
     v_r:=0;
   end if ;
  return v_max;
 end;
 --select maxNum(12,500，？) from dual--第三个参数写写什么？不知道，因为输出参数不是变量（select中只能出现变量），因此不能出现在select语句中。因此用select语句无法调用上面的函数了
 -------
---如果有输出参数，就要匿名块调用（因为要定义变量接收这个输出参数），只能使用匿名块调用上面的函数
declare
v_res number;---定义变量，充当输出参数
v_rtu number;-----定义变量接收返回值
begin
  v_rtu:=maxNum(1,2,v_res);
  dbms_output.put_line(v_res||'   '||v_rtu);
  end;
 
 ---------------------------------------------
-------练习：写一个函数，统计参加考试的人和未参加考试的人
-------分析：这里需要输出两个值，因此一个是返回值，另一个是输出参数。因为有输出参数，因此在调用此函数的时候需要使用匿名块调用
   create or replace function countNum(v_join out number) return number ---v_join表示参加考试的学生
   is
   v_num number;----定义变量接收返回值
   begin
     ---得到参加考试的人，并把值赋值给v_join
     select count(distinct sno) into v_join from score;
     ---得到没参加考试的人，结果复制给返回值
     select count(*) into v_num from student where sno not in (select sno from score);
      return v_num;
   end;
  
   declare
     v_result number;---定义变量，充当输出参数
     v_return number;---接收返回值
   begin
     v_return:=countNum(v_result);
     dbms_output.put_line('参加考试的人'||v_result||'    未参加考试的人'||v_return);
   end;
-------------------------------------------
--第二部分：存储过程：不能被select调用，只能用匿名块调用
--自我理解：(因为存储过程没有返回值，因此用输出参数充当返回值，既然使用了输出函数，就要用匿名块调用了)
--表，存储过程，函数，视图等都是数据库对象 
------------------------------------------------
--1.无参存储过程
create or replace procedure
proc_max
is
  v_num1 number(3):=100;
  v_num2 number(3):=200;

  v_max number;
begin
  if  v_num1> v_num2 then
      v_max:=v_num1;
  else
      v_max:=v_num2;
  end if;
  dbms_output.put_line('v_max='||v_max);
end;
------调用存储过程（只能使用匿名块）
begin----由于没有输出参数，因此不用定义变量，因此不用写declare,可以写，写了也没用
  proc_max;
end;
-----------------------------------------
---2.带参存储过程
create or replace procedure
proc_max1(v_num1 number,v_num2 number,
v_res out number)
is

begin
     if v_num1>v_num2 then
        v_res:=v_num1;
     elsif v_num1<v_num2 then
        v_res:=v_num2;
     else
        v_res:=0;
     end if;
end;
-------必须用匿名块调用存储过程
declare
   v_result number;
begin
  proc_max1(34,65,v_result);
  dbms_output.put_line(v_result);
end;

-------------------------------------------
---练习：使用存储过程求出两个数中的较大值
create or replace procedure proc_max(v_num1 number,v_num2 number,v_max out number)
 is
 ----需要变量就写,如果什么也不需要就什么不写
  begin
    if v_num1>v_num2 then
      v_max:=v_num1;
      dbms_output.put_line(v_max);
    elsif  v_num1<v_num2  then
      v_max:=v_num2;
      dbms_output.put_line(v_max);
     else
       v_max:=0;
      dbms_output.put_line(v_max);
     end if;
     end;
 ----只能使用匿名块调用存储过程  
 declare
 -- v_a number(2):=1;
  --v_b number(2):=2;
  v_c number(2);
 begin
   proc_max(1,2,v_c);
 end;    
 ------------
 --函数和存储过程的区别：
 ---相同点：
    ---1.封装匿名块，提高代码复用性。
    ---2.都可以有输入输出参数
 -----不同：
     ----1.函数必须有返回值，存储过程没有返回值
     ----1.函数可以通过select语句或者匿名块调用。存储过程只能用匿名块调用
 ------------------------------------------------------------------------
 ----练习：实现学生表student中  ,写一个函数将电话号码的中间四为用*表示（意思是在查询的时候电话号码的显示中间四位的时候是*）
 ----思考：首先考虑是使用函数做还是使用存储过程做，由于最后要写一个查询语句显示电话号码和学生表的其他信息，因此只能用函数写（只有函数能用select查询语句）
 select * from student
 create or replace function substel(v_tel in varchar2) return varchar2
  is
    v_res varchar2(11);----用于接收返回值（return）
    v_si varchar2(4);---前四位
    v_san varchar2(3);---后三位
  begin
   v_si:=substr(v_tel,1,4);
    v_san:=substr(v_tel,9,3);
   v_res:=v_si || '****' || v_san ;
   return v_res;
  end;
  select sno,sname,substel(stel) from student;----这里这个函数就相当于max
  --------------------
  ---第三部分.视图：是一张包含特定数据的虚拟表（不存在的数据）（内容和table表一样，但是效果不一样）
  ---例题1：查询补考过学生的信息
  select student.* from student
  where sno in(select sno from score group by cid,sno having count(*)>1)
  ---现在想找到那个学号的那个学生的那个科目补考过（这也不能永久的保存，这时候就用虚拟表保存）
   ---视图名字as select语句。as是作为，是把复杂是select语句起个名字保存下来
  ---视图一般关联多张表。就是给复杂的select起个别名永久的保存，以后就直接用别名调用了。
  ---因此现在把上面的select语句创建一个试图
  create or replace  view view_exam_two---保存参加过补考的学生
    as
     select student.* from
    student
    where sno in(select sno from score
    group by sno,cid
    having count(*)>1)
  select * from  view_exam_two---查询试图
  --------------------------------------------
  ---例题2：查询参加过考试的学生的学号，姓名，科目
     create or replace view view_join_exam
     as
     select student.sno,sname,cname from
     student inner join score
     on student.sno=score.sno
     inner join course
     on score.cid=course.cid
     -----查询试图
     select * from view_join_exam
  ---视图的好处
     --1.简化对复杂select的处理
     --2.隐藏表结构，增加数据的安全性
  ---视图的分类：单表视图，多表视图，视图的视图
  ----单表视图：就是为了给列起个别名（隐藏表和表结构，这样外人就不知道表的名字和这表的数据代表什么意思了）。单表视图可以update
  ----多表跟单表一样
  ---视图的视图：在视图上在创建视图。一级视图，二级视图。。。不能看到物理表
  -----------------------------------------------------------
  --第四部分：序列：数据库来维护，一组能够自动增长的值：要不要循环（例如：一次产生5个数，用完在给）。要不要排序
  ---有两个属相，nextval:下一个值 currval：当前值
  ---用处：用序列的值充当主键 例如：insert into student(seq_sno.nextval,,,,)
  create sequence SEQ_SNO-----在左侧找到sequences，右键新建，添加值，这就创建了一个完整的序列
    minvalue 100000
    maxvalue 999999
    start with 111200
    increment by 1
    cache 5
    order;  
select seq_sno.currval from dual