PLSQL 层层嵌套，自定义函数

匿名块
declare
begin
exception
end;

--exception   异常
┌预定义异常          --系统给的,有名字
├非预定义异常        --系统给的,没有名字
└用户自定义异常      --用户自己定义的

为什么要处理异常?
由于plsql程序块一旦产生异常而没有指出如何处理时,程序会自动终止。
异常情况处理就是用来处理这些正常执行中未预料到的事件。

--预定义异常      (不用声明)
┌违反唯一性限制     dup_val_on_index    
├没有找到数据       no_date_found
├返回多行           too_many_rows
├试图用0作除数      zero_divide
└其他异常           others

--键盘输入一个员工姓名,打印员工编号,输入一个部门编号,打印员工编号
declare
v_ename varchar2(20):=&a;
v_empno number;
v_deptno number:=&b;
begin
  select empno into v_empno
  from emp
  where ename=v_ename;
  
  dbms_output.put_line(v_empno);
  
  select empno into v_empno
  from emp
  where deptno=v_deptno;
  
  dbms_output.put_line(v_empno); 
exception
 when too_many_rows then
    dbms_output.put_line(sqlcode||' '||sqlerrm);
  when no_data_found then
    dbms_output.put_line(sqlcode||' '||sqlerrm);
  when others then 
    dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

错误描述:sqlcode
错误代码:sqlerrm

--非预定义异常

--向emp表插入一条数据

declare
err exception;     --声明异常
err2 exception;
pragma exception_init(err,-2291);     --把-2291这个预定义异常赋给err
pragma exception_init(err2,-00001); 
begin
  insert into emp(deptno,empno) values(50,7788);
exception
  when err then
    dbms_output.put_line(sqlcode||' '||sqlerrm);
  when err2 then 
    dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

--用户自定义异常

--向emp1插入一条员工编号,要求员工编号必须为7开头且四个数字
declare
v1 number:=&a;
err exception;     --声明异常
begin
  
  if v1 not like '7___' then raise err;     --抛出一样
  else insert into emp1(empno) values(v1);
  end if;
exception
  when err then
    dbms_output.put_line('请输入7开头且四位的员工编号');  
    raise_application_error(-20001,'员工编号不规范');    --弹窗报错 
end;

raise_application_error(错误代码,错误描述)
错误代码:-20000到-20999

--emp1表和emp表格式一样.向emp1插入一条empno,sal,job
--要求empno必须是4位数字并且7开头,工资必须大于3000,工作不能为空
--如果不合规定,弹窗报错"...必须..."

declare
err1 exception;
err2 exception;
err3 exception;
v1 number:=&a;
v2 number:=&b;
v3 varchar2(20):=&c;
begin
  if v1 not like '7___' then raise err1;
  elsif v2 <=3000 then raise err2;
  elsif v3 is null then raise err3;
  else insert into emp1(empno,sal,job) values(v1,v2,v3);
  end if;
exception
  when err1 then
    raise_application_error(-20001,'员工编号必须7开头且为4位');
  when err2 then
    raise_application_error(-20002,'工资必须大于3000');
  when err3 then   
   raise_application_error(-20003,'职位必须不能为空');
end;

--层层嵌套和子块并列
程序的执行分为两种情况
串行:本次程序的运行依赖于上一个程序的运行结果,如果上一个程序出错,
     本次程序不会运行,肯定是错的.
并行:两个程序之间没有关系,可能只是为了运行方便或者业务的要求将两个
     程序放在一起执行,一个程序出错,不会影响另一个.

--层层嵌套
declare
v_外 varchar2(20):='外';
begin
  declare
  v_中 varchar2(20):='中';
  v_empno number:=&a;
  v_ename varchar2(20);
  begin
    declare
    v_内 varchar2(20):='内';
    begin
      dbms_output.put_line(v_内);
    exception
    when no_data_found then
      dbms_output.put_line('错了');
    end;
    dbms_output.put_line(v_中);
      select ename into v_ename
      from emp
      where empno=v_empno;    
  end;
  dbms_output.put_line(v_外);

end;

·内层可以用外层的变量，外层不能用内层的变量
·内层可以用外层的异常，外层不能用内层的异常
·如果内层发生异常，从内向外抛出异常

--子块并列
declare
v_外 varchar2(20):='外';
begin
  declare
  v_内1 varchar2(20):='内1';
  begin
    dbms_output.put_line(v_内1);
  end;
  
  declare
  v_内2 varchar2(20):='内2';
  begin
    dbms_output.put_line(v_内2);
  end;
   dbms_output.put_line(v_外);
end;

·子块之间不能用彼此的变量，也不能用彼此的异常
·子块都可以用外层的变量和异常
·层层嵌套和子块并列可以联合使用形成综合结构

--emp1表和emp表格式一样.向emp1插入一条empno,sal,job
--要求empno必须是4位数字并且7开头,如果不符合,将empno处理成7000插入
--工资必须大于3000,如果不符合,将工资处理成3000插入
--工作不能为空,如果不符合,将工作处理成CLERK插入

declare
v_empno number:=&a;
v_sal number:=&b;
v_job varchar2(20):=&c;
err exception;
begin
  begin
    if v_empno not like '7___' then raise err;
    else insert into emp1(empno)values(v_empno);
    end if;
  exception
    when err then
    insert into emp1(empno)values(7000);
  end;
  
  begin
    if v_sal<=3000 then raise err;
    else insert into emp1(sal)values(v_sal);
    end if;
  exception
    when err then 
      insert into emp1(sal)values(3000);
  end;
  
  begin
    if v_job is null then raise err;
    else insert into emp1(job)values(v_job);
    end if;
  exception
    when err then 
      insert into emp1(job)values('CLERK');
  end;
end;

--自定义函数     function      有名块
create [or replace] function 函数名(参数 [in]|out 类型,参数2..) return 返回值类型
is
声明;
begin
  执行部分;
[exception]
异常部分
end;

--制作一个函数my_sign
create or replace function my_sign(v1 in number) return number
is
begin
  if v1>0 then return 1;
  elsif v1<0 then return -1;
  elsif v1=0 then return 0;
  end if;
end;

--制作一个函数my_xfs,输入一个数字返回他的相反数
create or replace function my_xfs(v1 number) return number
is
begin
   return -v1;
end;

--制作一个函数my_jdz,输入一个数字返回他的绝对值
create or replace function my_jdz(v1 number) return number
is
begin
  if v1>0 then return v1;
  elsif v1<0 then return -v1;
  else return 0;
  end if;
end;

select my_jdz(-5)
from dual

--制作一个函数,表示π 
create or replace function π return number
is
begin
  return 3.14;
end;

select π
from dual

--查询半径是2的圆的面积
select π*power(2,2)
from dual

--查询半径是2的球的体积
select 4/3*π*power(2,3)
from dual

--制作一个函数,输入三个数字,反回最大的那个数字
create or replace function f1(v1 number,v2 number,v3 number) return number
is
begin
  if v1>=v2 and v1>=v3 then return v1;
  elsif v2>=v1 and v2>=v3 then return v2;
  elsif v3>=v1 and v3>=v2 then return v3;
  end if;
end;

create or replace function f1_1(v1 number,v2 number,v3 number) return number
is
v_m number;
begin
  select max(A) into v_m
  from (select v1 A
        from dual 
        union 
        select v2
        from dual
        union
        select v3
        from dual)
  return v_m;
end;

create or replace function fun_1(v1 number,v2 number,v3 number)
return number is
v_r number;
begin 
  if v1>v2 then
    v_r:=v1;
  else
    v_r:=v2;
  end if;
  if v_r>v3 then
    return v_r;
  else 
    return v3;
  end if;
end;

--制作一个函数,输入一个员工姓名,返回他的工资
create or replace function f2(v_ename varchar2) return number
is
v_sal number;
begin
  select sal into v_sal
  from emp
  where ename=v_ename;
  
  return v_sal;
end;

--制作一个函数,输入员工姓名,返回比他工资高的人数
create or replace function f3(v_ename varchar2) return number
is
v1 number;
begin
  select count(1) into v1
  from emp
  where sal>(select sal
             from emp
             where ename=v_ename);
   return v1;
end;

select f3('SMITH')
from dual

--制作一个函数,输入一个部门编号和一个职位,返回这个部门这个职位的人数
create or replace function f4(v_deptno number,v_job varchar2) return number
is
v1 number;
begin
  select count(1) into v1
  from emp
  where deptno=v_deptno and job=v_job;
   
  return v1;
end;

select f4(30,'SALESMAN')
from dual

--制作一个函数,输入一个部门编号和一个年份,
--返回这个部门这个年份入职的人名
--用逗号隔开
create or replace function f5(v_deptno number,v_year number) return varchar2
is
v_name varchar2(200);
begin
  select wm_concat(ename) into v_name
  from emp
  where deptno=v_deptno and to_char(hiredate,'yyyy')=v_year;
  return v_name;
end;

select f5(20,1981)
from dual

--返回动态游标             --不常用
--制作一个函数,输入一个部门编号,返回这个部门的人名

create or replace function f6(v_deptno number) return sys_refcursor
is
cur_1 sys_refcursor;
v_ename varchar2(20);
begin
  open cur_1 for select ename from emp where deptno=v_deptno;
  loop
    fetch cur_1 into v_ename;
    exit when cur_1%notfound;
    dbms_output.put_line(v_ename);
    end loop;
    
    return cur_1;
    close cur_1;
end;

select f6(10)
from dual

--有out

--输入一个员工编号,输出他的工资,返回他的部门编号
create or replace function f7(v_empno number,v_sal out number) return number
is
v_deptno number;
begin
  select sal,deptno into v_sal,v_deptno
  from emp
  where empno=v_empno;
  
  return v_deptno;
end;

调用     --需要用到匿名块
declare
v1 number;
v2 number;
begin
  v2:=f7(7788,v1);
  dbms_output.put_line(v1||' '||v2);
end;

--有out一般不用函数用存储过程
--自定义函数一般不包含DML
--自定义函数不能包含DDl

--函数小结
                 调用
┌没有参数     SQL调用
├只有in       sql调用
└有out        pl/SQL调用(返回值:=函数名(参..))

--小结
异常┌预定义异常
    ├非预定义异常
    └自定义异常

层层嵌套和子块并列┌变量
                  └异常
自定义函数┌没有参数
          ├只有in
          └有out