Oracle之存储过程的创建

存储过程是以命名的数据库对象形式存储于数据库当中。

存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

存储过程可由数据库提供安全保证，需要有存储过程的所有者的授权。

存储过程的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程。

创建过程

方式一：plsql创建

 “确认”后生成如下脚本，接下来就可写业务脚本代码（方式二就是直接在SQL窗口写以下代码）

create or replace procedure producers_test is
begin
  /*业务脚本*/
end producers_test;

实例：

create or replace procedure put_report is
/*注意：定义变量必须在begin之前申明*/
  start_date char(24);
  end_date   char(24);
  error_mes  varchar2(200);
  use_time   varchar2(100);
begin
/*注：变量赋值的方式是通过 ':=' 进行的*/
  start_date := to_char(systimestamp, 'yyyy-MM-dd HH24:mi:ss.ff3');
  
/*打印信息：DBMS_OUTPUT.PUT_LINE()*/
  DBMS_OUTPUT.PUT_LINE('执行开始：' || start_date);
  /***********start 业务逻辑***********/
  
  /***********end 业务逻辑***********/
  end_date := to_char(systimestamp, 'yyyy-MM-dd HH24:mi:ss.ff3');
  use_time := to_timestamp(end_date, 'yyyy:MM-dd HH24:mi:ss.ff3') -
              to_timestamp(start_date, 'yyyy:MM-dd HH24:mi:ss.ff3') || '';
  DBMS_OUTPUT.PUT_LINE('执行结束：' || end_date);
  DBMS_OUTPUT.PUT_LINE('耗时：' || use_time);
  
/*异常处理*/
EXCEPTION
  WHEN OTHERS THEN
    error_mes := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
    DBMS_OUTPUT.PUT_LINE(error_mes);
end put_report;

条件格式语法

-- 单if件格式：
if codition then
   代码块
end if;
if-else条件格式：
if codition then
  代码块1
else
  代码块2
end if;

-- 多条件块：
if codition then
  代码块1
elsif codition2 then
  代码块2
else
  代码块2
end if;

附带条件的存储过程

create or replace procedure p_test is
  mess varchar2(30);
begin
  select user_code into mess from sm_user where user_code = 'system';
  if mess <> 'sys' then
    goto end_;/*跳转到标记为 end_ 的地方*/
  else
    dbms_output.put_line('业务正常进行！');
  end if;

  <<end_>> /*由 goto 定义的标记，当走goto语句时，即跳转到此处*/
  dbms_output.put_line('非SYS用户不能执行 p_test 存储过程');
exception
  when others then
  /*异常代码：SQLCODE->异常编码；SQLERRM->异常信息*/
    dbms_output.put_line('异常:' || CHR(13) || SQLCODE || SQLERRM);
end p_test;

实例：创建一个带输入和输出的存储过程，执行查询结果输出，包括测试

/*创建存储过程p_test(code in varchar2, res out varchar2)*/
create or replace procedure p_test(code in varchar2, res out varchar2) is
begin
  /*判断输入参数*/
  /*当code为空时直接跳转至 end2_*/
  if code is null then
    goto end2_;
  end if;

  /*将查询结果赋值给参数res*/
  select user_code || ',' || user_name || ',' || user_password
    into res
    from s_user
   where user_code = code;

  /*当查询结果为空时直接跳转至 end_*/
  if res is null then
    goto end_;
  end if;

  <<end2_>>
  if code is null then
    dbms_output.put_line('用户编码不能为空');
  end if;

  <<end_>>
  if code is not null and res is null then
    dbms_output.put_line('查无此人');
  end if;

  /*异常处理*/
exception
  when others then
    dbms_output.put_line('异常:' || CHR(13) || SQLCODE || SQLERRM);
end p_test;

存储过程执行，编写语句并执行

/*创建一个测试窗口，对p_test进行测试*/
/*申明2个变量，一个输入参数，一个输出参数。*/
/*输入参数为用户编码；输出参数为接收查询结果*/
declare 
    code varchar2(20) default null;
    res varchar2(100) default null;
begin
  code := 'test';
  p_test(code,res);
  dbms_output.put_line(msg);
end;