[oracle]存储过程入门

什么是存储过程

官方解释是这样的：存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。
我在初学存储过程的时候给我的感觉就很像是java、C#里的方法，能够传参以及反复调用，效率较高。

演示

第一个存储过程：无参数，无返回值。

无参的存储过程带不带小括号都可以。

create or replace procedure procDemo1--不带小括号
as
begin
  dbms_output.put_line('哈喽，第一个存储过程');
end;
--或者
create or replace procedure procDemo1()--带有小括号
as
begin
  dbms_output.put_line('哈喽，第一个存储过程');
end;

这就创建好了，可以在目录Procedures里找到这个存储过程。但是创建后仅仅是编译好了，我们需要使用的话还得调用。

存储过程有两种调用方式
第一种：无参的时候带不带小括号都可以

begin
  procDemo1;
end;
--或者
begin
  procDemo1();
end;

第二种：即便是无参也不能省略小括号

call procDemo1();

output输出结果为

哈喽，第一个存储过程

一般在PL/SQL窗口用第一种方式，在应用程序里用第二种方式，比如
hibernateDao.excuteSqlUpdate("{call procDemo1()}");

第二个存储过程：声明变量和赋值

在存储过程中声明的变量属于局部变量，只能在存储过程内部使用，声明变量写在关键字as和begin之间。

create or replace procedure procDemo2
as
  myName varchar2(20);
  myPetName varchar2(20):='笨笨';--可以在声明的时候就赋初始值
begin
  myName:='李大傻';
  dbms_output.put_line('我的名字叫'||myName||'，我的宠物叫'||myPetName);
end;

call procDemo2();

output输出结果为

我的名字叫李大傻，我的宠物叫笨笨

第三个存储过程：带参数

create or replace procedure procDemo3(myName varchar2,myPetName varchar2)--注意：参数不能像声明变量一样指定长度，否则会编译出错。
as
begin
  dbms_output.put_line('我的名字叫'||myName||'，我的宠物叫'||myPetName);
end;

call procDemo3('王二丫','毛毛');
--传实参还可以用另一种方式，即形参名=>实参变量名，但似乎不支持call调用。
begin
  procDemo3(myName=>'王二丫',myPetName=>'毛毛');
end;
--甚至可以不按照形参顺序来，只需要名字一一对应即可。
begin
  procDemo3(myPetName=>'毛毛',myName=>'王二丫');
end;

以上3种调用效果相同，output输出结果为

我的名字叫王二丫，我的宠物叫毛毛

第四个存储过程：入参和出参

在第三个存储过程中，并没有指定是入参还是出参，那么它既是入参又是出参，比如形参写法myName varchar2实际上就是myName in out varchar2的简写。
而在第四个存储过程中，则明确定义了是出参还是入参。
入参要使用关键字in，表明该参数只能拿来使用，但不能改变值；
出参要使用关键字out，需要在存储过程内部给它重新赋值。

这个存储过程用于计算两个数相加，并返回相加后的结果

create or replace procedure procDemo4(a_num1 in number,a_num2 in number,a_result out number)
as
begin
  a_result:=a_num1+a_num2;
end;

declare
  v_num1 number(5,2);
  v_num2 number(5,2);
  v_result number(6,2);
begin
  v_num1:=101.76;
  v_num2:=298.24;
  v_result:=0;
  dbms_output.put_line('调用存储过程前，v_num1='||v_num1||'，v_num2='||v_num2||'，v_result='||v_result);
  procDemo4(v_num1,v_num2,v_result);
  dbms_output.put_line('调用存储过程后，v_num1='||v_num1||'，v_num2='||v_num2||'，v_result='||v_result);
end;

output输出结果为

调用存储过程前，v_num1=101.76，v_num2=298.24，v_result=0
调用存储过程后，v_num1=101.76，v_num2=298.24，v_result=400

关于出参的研究

经过试验发现，如果不在存储过程内部给出参赋值的话，并不会导致编译错误，但是变量从传进存储过程那一刻起，就变成了null。

create or replace procedure procDemo4_1(a_result out number)
as
begin
  dbms_output.put_line('不进行任何操作');
end;

declare
  a_result number(6,2);
begin
  a_result:=99.85;
  if a_result is null then
    dbms_output.put_line('调用存储过程前：a_result is null');
  else
    dbms_output.put_line('调用存储过程前：a_result='||a_result);
  end if;
  procDemo4_2(a_result);
  if a_result is null then
    dbms_output.put_line('调用存储过程后：a_result is null');
  else
    dbms_output.put_line('调用存储过程后：a_result='||a_result);
  end if;
end;

output输出结果为

调用存储过程前：a_result=99.85
不进行任何操作
调用存储过程后：a_result is null

可以看到，存储过程内部不给出参赋值，导致外部变量的值变成了null。

关于入参的研究

入参不能赋值，否则造成编译错误。

create or replace procedure procDemo4_2(a_num in number)
as
begin
  a_num:=101.33;
end;

这个存储过程是不能成功创建的，此时编译报错信息如下。

第五个存储过程：查询表信息

准备数据

create table myFriends(
       id number(5) primary key,
       name varchar2(50) not null,
       age number(3) not null,
       hobby varchar2(100)
);
insert into myfriends values(1,'大明',28,'打炮');
insert into myfriends values(2,'黑狗',22,'看AV');
insert into myfriends values(3,'小黄',25,'健身');
insert into myfriends values(4,'老王',31,'拳击');
insert into myfriends values(5,'小花',18,'刷电视剧');
commit;
select * from myfriends;

创建存储过程并调用

create or replace procedure procDemo5(v_id in number)
as
  v_name myfriends.name%type;
  v_age myfriends.age%type;
  v_hobby myfriends.hobby%type;
begin
  select t.name,t.age,t.hobby into v_name,v_age,v_hobby from myfriends t where t.id=v_id;
  dbms_output.put_line('我的朋友名字='||v_name||'，年龄='||v_age||'，爱好='||v_hobby);
  exception when no_data_found then
    dbms_output.put_line('no_data_found异常');
end;

call procDemo5(2);

output输出结果为

我的朋友名字=黑狗，年龄=22，爱好=看AV

当select into查询不到数据时，会触发no_data_found异常，如下。

call procDemo5(6);

output输出结果为

no_data_found异常

还可以使用for循环进行遍历

create or replace procedure procDemo5_1
as
begin
  for friend in (select * from myfriends) loop
    dbms_output.put_line('我的朋友id='||friend.id||'，名字='||friend.name||'，年龄='||friend.age||'，爱好='||friend.hobby);
  end loop;
end;

call procDemo5_1();

output输出结果为

我的朋友id=1，名字=大明，年龄=28，爱好=打炮
我的朋友id=2，名字=黑狗，年龄=22，爱好=看AV
我的朋友id=3，名字=小黄，年龄=25，爱好=健身
我的朋友id=4，名字=老王，年龄=31，爱好=拳击
我的朋友id=5，名字=小花，年龄=18，爱好=刷电视剧

第六个存储过程：增删改操作

注意：一般不在存储过程内部写commit或rollback，而是外部调用完后才写，这样可以比较好统一处理事务。

新增操作

sql%found为隐式游标属性，关于隐式游标详情见我另一篇博客https://blog.youkuaiyun.com/qq_20481125/article/details/101675668

create or replace procedure procDemo6(v_id myfriends.id%type,v_name myfriends.name%type,v_age myfriends.age%type,v_hobby myfriends.hobby%type)
as
begin
  insert into myfriends values(v_id,v_name,v_age,v_hobby);
  if sql%found then
    dbms_output.put_line('新增成功');
  else
    dbms_output.put_line('新增失败');
  end if;
  exception when dup_val_on_index then
    dbms_output.put_line('插入的id重复');
  when others then
    dbms_output.put_line('其他异常');
end;

begin
  procDemo6(6,'老铁',33,'踢足球');
  commit;
end;

output输出结果为

插入成功

此时表数据如下，发现确实成功新增了id=6这一条信息。

修改：根据id修改其他信息

create or replace procedure procDemo6_update(v_id myfriends.id%type,new_name myfriends.name%type,new_age myfriends.age%type,new_hobby myfriends.hobby%type)
as
begin
  update myfriends set name=new_name,age=new_age,hobby=new_hobby where id=v_id;
  if sql%found then
    dbms_output.put_line('修改成功');
  else
    dbms_output.put_line('修改失败');
  end if;
end;

begin
  procDemo6_update(6,'老李',40,'打篮球');
  commit;
end;

output输出结果为

修改成功

此时表数据如下，发现id=6的信息确实更新成功了。

删除：根据id删除

create or replace procedure procDemo6_delete(v_id myfriends.id%type)
as
begin
  delete from myfriends where id=v_id;
  if sql%found then
    dbms_output.put_line('删除成功');
  else
    dbms_output.put_line('删除失败');
  end if;
end;

begin
  procDemo6_delete(6);
  commit;
end;

output输出结果为

删除成功

此时表数据如下，发现id=6的信息不存在了。

存储过程入门的东西就那么多了。