Oracle之存储过程

一，基本介绍

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。

二.语法结构

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

-- 创建存储过程
create or replace procedure test1 as 
begin
  dbms_output.put_line('hello world');
end;
-- 调用存储过程
call test1();
-- 删除存储过程
drop procedure test1;

2.1变量

Oracle存储过程中的变量大致可以分为普通数据类型和特殊变量类型。普通数据类型就是Oracle中的基本数据类型，如：NUMBER、CHAR 、VARCHAR2 、VARCHAR、NCHAR 、NVARCHAR2 、LONG 、DATE 、TIMESTAMP等等；而特殊变量类型分为引用型变量和记录型变量。引用型变量的数据类型取决表中的数据类型；记录型变量不是接受一个值，是一行值。

普通变量

当我们想使用普通变量的时候，需要先给普通变量命名，制定变量的类型和长度。在赋值的时候可以在声明变量的时候直接赋值:=，也可以在程序中赋值，通过select ... into语句赋值。

create or replace procedure test1 as
-- 定义变量
v_number varchar2(20);       --工号
v_salary int  := 3000;       --声明变量直接赋值
v_depart varchar2(50);       --部门
begin
  -- 通过SELECT语句给变量赋值
  select '5号部门','P00007' into v_depart,v_number from dual;

  -- 打印输出
  dbms_output.put_line('工号：'||v_number); 
  dbms_output.put_line('工资：'||v_salary);
  dbms_output.put_line('部门：'||v_depart);
end ;

引用变量

引用变量变量的类型和长度取决于表中字段的类型和长度，通过表名.列名%TYPE指定变量的类型和长度。

create or replace procedure test2 as
-- 定义变量
v_sid t_student.sid%TYPE;
v_sname t_student.sname%TYPE;
begin
  -- 通过SELECT语句给变量赋值
  select sid,sname into v_sid,v_sname from t_student where sid='01';

  -- 打印输出
  dbms_output.put_line('学号：'||v_sid||',姓名：'||v_sname); 
end;

记录型变量

记录型变量接受表中的一整行记录，语法：变量名称 表名%ROWTYPE

create or replace procedure test3 as
  -- 定义变量，记录型变量，接受表中的一整行记录
  v_stu t_student%ROWTYPE;
begin
  select * into v_stu from t_student where rownum=1;
  dbms_output.put_line('姓名：'||v_stu.sid||',年龄：'||to_char(v_stu.sage,'yyyy-MM-dd'));
end;

2.2参数

in传入参数

IN是参数的默认模式，表示该参数的值必须在调用存储过程时指定。IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回。

CREATE OR REPLACE PROCEDURE test_sid(id varchar2) as
  v_name VARCHAR2(100); 
begin
  select sname into v_name from t_student where sid = id;
  dbms_output.put_line('姓名：'||v_name);
end;

out传出参数

OUT类型参数只能接收赋值，不能给其他变量赋值。输出模式的参数，用于输出值，会忽略传入的值，在子程序内部可以对其进行修改。

-- 创建存储过程
CREATE OR REPLACE PROCEDURE test_out(
       id in varchar2,
       v_sname out t_student.sname%TYPE) as
begin
       select sname into v_sname from t_student where sid = id;
       dbms_output.put_line('姓名：'||v_sname);
end;

-- 调用存储过程，并得到out输出参数值并打印
declare
  sname varchar2(200);
begin
  test_out('01',sname);
  dbms_output.put_line(sname);
end;

inout可变参数

与OUT类型相比不同是默认初始化参数不为NULL，传的是什么就是什么。

-- 创建存储过程
create or replace procedure test_in_out(v_sid in out number)
is
begin
  dbms_output.put_line(v_sid);
  v_sid:=10;
end;

-- 调用存储过程
declare
  sid number:=1;
begin
  test_in_out(sid);
  dbms_output.put_line(sid);
end;

2.3if结构

-- 创建存储过程，根据输入参数性别来判断查询数据的结果
create or replace procedure test_if_sex(v_sex varchar2) as 
  my_name varchar2(200);
begin
  if(v_sex='男') then 
     select sname into my_name from t_student where sid='01';
  else
     select sname into my_name from t_student where sid='02';
  end if;
  dbms_output.put_line('性别：'||v_sex||'，姓名：'||my_name);
end;

-- 调用存储过程
call test_if_sex('男');
call test_if_sex('女');

2.4循环结构

while

create or replace procedure test_while as 
  i int:=0;
begin 
  while i<10 loop
    -- 一定要记得累加值，不然死循环了
    i:=i+1;
    dbms_output.put_line(i);
  end loop;
end;

loop

create or replace procedure test_loop as 
  i number:=0;
begin
  loop
    exit when(i>10);
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

for

create or replace procedure test_for as 
  i number:=0;
begin
  for i in 1..5 loop
    dbms_output.put_line(i);
  end loop;
end;