Oracle数据库中的存储过程与函数练习

Oracle数据库存储过程

以下函数或存储过程创建了之后都需要测试：
先建表：

//部门表
create table dept(
  2  d_id number primary key,
  3  d_name varchar2(32)
  4  )
  5  /
  insert into dept values(1,'开发部');
  insert into dept values(2,'财务部');
  insert into dept values(3,'人事部');
  
//员工表
create table staff(
  2  s_id number primary key,
  3  s_name varchar2(32),
  4  s_sal number,
  5  s_deptId number references dept(d_id)
  6  )
  7  /
  insert into staff values(1,'张三',5500,1);
  insert into staff values(2,'李四',6500,1);
  insert into staff values(3,'王五',4000,2);
  insert into staff values(4,'马六',4500,2);
  insert into staff values(5,'小明',3500,3);
  insert into staff values(6,'小亮',4200,3);

1.定义一个存储过程，用来查询指定部门的员工的人数和平均工资，如果部门不存在，就引发一个自定义异常，显示“部门编号不存在！”(部门编号作为存储过程的输入参数，在调用存储过程前由用户输入，该部门员工的人数和平均工资需要返回)

//sdeptId 部门id
//scount 人数
//ssal 平均工资
create or replace procedure s1(sdeptId in staff.s_deptId%type,scount out number,ssal out number)
as
begin
   select count(*) into scount from staff where s_deptId = sdeptId;
  if scount >0 then
     select avg(s_sal) into ssal from staff group by s_deptId having s_deptId = sdeptId; 
  else
     raise_application_error(-20002,'部门编号不存在！');
  end if;
end;

//sdeptId 部门id
//scount 人数
//ssal 平均工资
declare
  sdeptId staff.s_deptId%type;
  scount number;
  ssal number;
  begin
    sdeptId:='&部门编号';
    s1(sdeptId,scount,ssal);
    dbms_output.put_line('部门编号：'||sdeptId||'人数：'||scount||'平均工资：'||ssal);
end;

2. 定义一个函数，用来求一个数的N次幂的结果并返回，底数和幂数在调用函数的时候作为参数传递过来

//a 输入的数
//b 几次幂
//c 结果
create or replace function s2(a in number,b in number,c out number) return number
as
begin
    if b < 0 then
       raise_application_error(-20002,'输入的次幂要大于0！');
    elsif b = 0 then
       c:=1;
    else
       c:=1;
       for i in 1..b loop
       c:=c*a;
       end loop;
    end if;
return c;
end;

//a 输入的数
//b 几次幂
//c 结果
declare
 a number;
 b number;
 c number;
 begin
   a:=&请输入一个数;
   b:=&请输入几次幂;
   c:=s2(a,b,c);
   dbms_output.put_line(a||'的'||b||'次幂为：'||c);
 end;

3. 创建一个带参数的存储过程，根据制定的参数删除一个员工

create or replace procedure s3(sid in staff.s_id%type)
as
scount number;
begin
   select count(*) into scount from staff where s_id = sid;
   if scount > 0 then
      delete from staff  where s_id = sid;
      dbms_output.put_line('删除成功');
   else
      dbms_output.put_line('没有这个员工编号，删除失败！！！');
   end if;
end;

declare
  sid staff.s_id%type;
begin
  sid:=&请输入你要删除的数据id;
  s3(sid);
end;

4. 创建一个带输出参数的存储过程，在这个过程中首先调用上面的那个存储过程，然后用输出参数返回剩下的员工人数

create or replace procedure s4(sid in staff.s_id%type,scount out number)
as
begin
   select count(*) into scount from staff where s_id = sid;
   if scount > 0 then
      delete from staff  where s_id = sid;
      dbms_output.put_line('删除成功');
      select count(*) into scount from staff;
   else
      dbms_output.put_line('没有这个员工编号，删除失败！！！');
      select count(*) into scount from staff;
   end if;
end;

declare
  sid staff.s_id%type;
  scount number;
begin
  sid:=&请输入你要删除的数据id;
  s4(sid,scount);
  dbms_output.put_line('剩下的员工人数为:'||scount);
end;

5. 创建一个函数，包含二个参数a，b，求从a到b之间的数的总和，如果a大于b则引发一个自定义异常。最后测试该函数的功能

create or replace function s5(a in number,b in number)return number
as
begin
  if a > b then
     raise_application_error(-20002,'a大于b发生异常！');
  else
     return a+b;
  end if;
end; 

declare
  a number;
  b number;
  c number;
begin
  a:=&请输入第一个数a的值;
  b:=&请输入第二个数b的值;
  c:=s5(a,b);
  dbms_output.put_line('a和b的总和为：'||c);
end;