Oracle数据库的基本操作（八）—— 存储过程

1、创建存储过程

• 语法

  create or replace 存储过程名
  （参数1 参数的类型 数据类型，参数2 参数的类型 数据类型，...）
  is | as
    声明变量
  begin
    ...
    exception
      ...
  end;
  

• 输入参数 in

• 输出参数 out

• 输入输出参数 in out

例1 创建不带参数的存储过程

create or replace procedure usp_ShowTime
as
begin
  dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));
end;

• 查看编译是否出错
  

• 使用execute命令直接调用存储过程，只能在命令窗口中执行
  

• 使用call命令调用存储过程，可以在命令窗口中也可以在SQL窗口中执行
  、

  call usp_ShowTime();
  

  

• 在PL/SQL匿名块中调用存储过程

  begin
    usp_ShowTime;
  end;
  

  

• 查看创建存储过程的源代码

  select text from user_source where name = 'USP_SHOWTIME' order by line;
  

  

• 创建存储过程的错误代码

     create procedure usp_GetStudent
     as
     begin
       select * from Studen where Sno= '2017001';
     end;
  

  

• 正确代码

  create or replace procedure usp_GetStudent
  as
  vSno Student.Sno % type;
  vSname Student.Sname % type;
  vSsex Student.Ssex % type;
  vSage Student.Sage % type;
  vSdept Student.Sdept % type;
  begin
    select * into vSno,vSname,vSsex,vSage,vSdept from Student where Sno = '2017001';
    dbms_output.put_line('学号：' || vSno || '姓名：' || vSname || '，性别：' || vSsex || '年龄：' || vSage || '专业：' || vSdept);
  end;
  

  

例2 创建带输入参数的存储过程

create or replace procedure usp_AddStudent
(
  vSno Student.Sno % type,
  vSname Student.Sname % type,
  vSsex Student.Ssex % type default '男',
  vSage Student.Sage % type,
  vSdept Student.Sdept % type,
)
as
begin
  insert into Student values(vSno,vSname,vSsex,vSage,vSdept)'
  exception
    when dup_val_on_index then
      dbms_output.put_line('学号不能重复!');
    when others then
      dbms_output.put_line('发生其他错误');
end;

• 位置传递调用存储过程

  call usp_AddStudent('2018001','张四','男','15','CS');
  

• 名称传递

  call usp_AddStudent(vSno => '2019001',vSname => '张武',vSsex => '男',vSage  => '16',vSdept => 'MA');
  

  

• 组合传递调用存储过程

  call usp_AddStudent('2020001','李五','男',vSage  => '16',vSdept => 'MA');
  

  

例3 创建带有输入输出参数的存储过程，输入学号，然后从Student表中删除该生，并输出该生的姓名

• 创建存储过程

  create or replace procedure usp_DelStudent(
    inSno char,
    outSname out varchar2)
  as
    vCount number;
  begin
    select count(*) into vCount from SC where Sno = inSno;
    if vCount != 0 then
      delete from SC where Sno = inSno;
    end if;
    select Sname into outSname from Student where Sno = inSno;
    delete from Student where Sno = inSno;
  end;
  

• 调用存储过程

  declare
    vSname Student.Sname%type;
  begin
    usp_DelStudent('&Sno',vSname);
    dbms_output.put_line('该生的姓名是：' || vSname);
  end;
  

• 运行结果
  

例4 创建带in out 类型的参数的存储过程，计算任意两个数的商和余数

• 创建存储过程

  create or replace procedure usp_TwoDIvide(
    num1 in out number,
    num2 in out number)
  as
    v1 number;
    v2 number;
  begin
    v1 := trunc(num1/num2);
    v2 := mod(num1,num2);
    num1 := v1;
    num2 := v2;
  end;
  

• 调用存储过程

  declare
    n1 number := &n1;
    n2 number := &n2;
  begin
    usp_TwoDivide(n1,n2);
    dbms_output.put_line('商：' || n1 || '，余数：' || n2);
  end;
  

• 运行结果