Oracle 存储过程的创建,调用和删除

创建存储过程的语法:

create or replace  procedure <procedure_name>

[(<parameter list>)]

as|is

 <local variable statements>   --创建过程，可指定运行过程需传递的参数

begin

 <executable statements>       --包括在过程中要执行的语句

 [exception

 <exception handlers>]         --处理异常

end;

 实例:

1.创建无参的存储过程

create or replace procedure first_proc
is
begin
  dbms_output.put_line('我是存储过程');
  dbms_output.put_line('hello everyone!');
end;

无参存储过程的调用

begin
  first_proc;
end;

 或

exec first_proc;

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

create or replace procedure second_proc
(
  v_empno in empnew.empno%type
)
is
begin
  --根据员工号删除指定的员工信息
  delete from empnew where empno = v_empno;
  
  --判断是否删除成功
  if sql%notfound then
    --创建我们自己的异常条件,当Oracle不会抛出它们时 -2000至20999之间
    raise_application_error(-20008,'指定删除的员工不存在!');
  else
    dbms_output.put_line('删除成功!');
  end if;
  
end;

调用带输入参数的存储过程:

begin
  second_proc(131854);
end;

3.创建带有输出参数的存储过程

create or replace procedure third_proc
(
   v_deptno in number,
   v_avgsal out number,
   v_cnt out number
)
is
begin
  select avg(sal),count(1) 
  into v_avgsal,v_cnt
  from emp
  where deptno = v_deptno;
  
  exception
    when no_data_found then
      dbms_output.put_line('没有此部门!');
    when others then
      dbms_output.put_line(sqlerrm);--打印异常信息
    
end;

调用带有输出参数的存储过程:

declare
  v_avgsal number;
  v_cnt number;

begin 
  third_proc(10,v_avgsal,v_cnt);
  --打印结果
  dbms_output.put_line(v_avgsal);
  dbms_output.put_line(v_cnt);
end;

4.创建带有输入输出参数的存储过程

create or replace procedure four_proc
(
    v_num1 in out number,
    v_num2 in out number
)
as
    --定义变量
    v_temp number :=0;
begin
  v_temp := v_num1;
  v_num1 := v_num2;
  v_num2 := v_temp;
end;

调用带有输入输出参数的存储过程:

declare
  v_num1 number:=10;
  v_num2 number:=20;
begin
  four_proc(v_num1,v_num2);
  --打印结果
  dbms_output.put_line(v_num1);
  dbms_output.put_line(v_num2);
end;

 

删除存储过程:

drop procedure 存储过程名称;

 

说明:

raise_application_error 是Oracle内置的函数,用于在plsql使用程序中自定义不正确消息。

raise_application_error的声明语法：

raise_application_error(error_number,error_message,[keep_errors]);

其中

error_number是一个错误号，值在-20000到-20999之间

error_message是与该错误相连的错误消息文本，并且该消息的长度无法超过2048字节；

keep_errors是一个boolean值。该参数是可选的。如果keep_errors为TRUE，则这个新的错误将加在已产生的错误列表之后。如果keep_errors为FALSE，则这个新错误将代替当前的错误列表。