ORACLE 存储过程实例

环境：

• oracle数据库
• PL/SQL Developer
• 测试库使用的是 学习分析函数删除冗余数据那个[tablename:test1]

1.创建个最简单的存储过程

SQL窗口执行：

create or replace procedure p_test --创建一个名字为p_test的存储过程
(
  p1 in number,--in表示该参数为输入参数，输出参数用out
  p2 in number --最后一个参数声明最后无逗号
)
IS --这里用AS也可以,详见底部注释@1
  c1 varchar2(25); --声明存储过程逻辑中用到的临时变量
  c2 varchar2(25); --注意最后一个也有分号
begin 
NULL; --如无内容必须写个NULL;
end p_test; --注意写上存储过程名

打开测试窗口测试：

 call p_test(1,1) --测试脚本，后边无分号

2.增加简单的逻辑，和输出语句

create or replace procedure p_test --创建一个名字为p_test的存储过程
(
  p1 in number,--in表示该参数为输入参数，输出参数用out
  p2 in number --最后一个参数声明最后无逗号
)
AS
  c1 varchar2(25); --声明存储过程逻辑中用到的临时变量
  c2 varchar2(25); --注意最后一个也有分号
begin 
  if p1 = p2 then --如果p1=p2则输出p1=p2
    dbms_output.put_line('p1 = p2 !');
  end if;
  if p1 <> p2 then--如果p1=p2则输出p1!=p2
    dbms_output.put_line('p1 != p2 !');
  end if;
end p_test; --注意写上存储过程名

在测试窗口中进行测试：

 call p_test(1,1) --输入参数相同时，DBMS输出p1=p2!
--call p_test(1,2)--输入参数不同时，DBMS输出p1!=p2!

3.查询一条记录，并把字段值保存进变量c1,c2

create or replace procedure p_test(p1 in number, p2 in number)
AS
  c1 varchar2(25);
  c2 varchar2(25);
begin 
 SELECT code1,code2 into c1,c2 FROM test1 where id = 1;--将code1,code2的值分别保存进变量c1,c2
  dbms_output.put_line('code1 = ' || c1);--输出变量c1的值
  dbms_output.put_line('code2 = ' || c2);--输出变量c2的值
 EXCEPTION --异常处理异常处理
  WHEN NO_DATA_FOUND --查无结果的处
  THEN dbms_output.put_line('NO DATA FOUND!'); --查无结果输出 NO DATA FOUND! 
end p_test;

在测试窗口中进行测试：

 call p_test(1,1) --输出结果为 code1 = 1
                           --code2 = a

4.多记录查询，操作游标
4.1 FOR循环游标

create or replace procedure p_test
(
  p_in_code1 in varchar2
 ) 
AS
 vs_msg   varchar2(4000);   --记录异常信息 
 cursor cur_test 
 is--这里不能用AS,只能用IS，详细描述见注释@1
 select * from test1 where code1 = p_in_code1;
begin
  for cur in cur_test --游标变量在for循环游标中可以直接使用，不用另外声明
  loop
     dbms_output.put_line(cur.id ||'  '||cur.code1||'  '||cur.code2||'   '||cur.code3);
  end loop;
  exception
      when others then ---错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。
        vs_msg := 'ERROR IN P_TEST('||p_in_code1||'):'||SUBSTR(SQLERRM,1,500);
        Dbms_Output.put_line('异常警告！！！');
        Dbms_Output.put_line('异常信息：' || vs_msg);
      ROLLBACK;--发生异常，回滚事务
    --将当前错误，记录到日志中
     --insert into XXX(proc_name,error_info,op_date) values('p_test',vs_msg,SYSDATE);
     --commit;
     --return;
end p_test;

4.2 Fetch 游标

create or replace procedure p_test
(
  p_in_code1 in varchar2
 ) 
AS
 vs_msg   varchar2(4000);   --记录异常信息  
cursor cur_test IS
 select * from test1 where code1 = p_in_code1;
 p_cur_test cur_test%rowtype;--定义一个游标变量，%rowtype说明见 注释@2
begin
 open cur_test;
    loop
        fetch cur_test into p_cur_test;--提取一行数据到游标变量中
        exit when cur_test%notfound;--取不到值跳出本次循环 ，%notfound 返回值说明见注释@3
        dbms_output.put_line(p_cur_test.id ||'  '||p_cur_test.code1||'  '||p_cur_test.code2||'   '||p_cur_test.code3);
    end loop;
  close cur_test;
  exception
      when others then
        vs_msg := 'ERROR IN P_TEST('||p_in_code1||'):'||SUBSTR(SQLERRM,1,500);
        Dbms_Output.put_line('异常警告！！！');
        Dbms_Output.put_line('异常信息：' || vs_msg);
      ROLLBACK;--发生异常，回滚事务
    --将当前错误，记录到日志中
     --insert into XXX(proc_name,error_info,op_date) values('p_test',vs_msg,SYSDATE);
     --commit;
     --return;
end p_test;

4.3带参数的游标

create or replace procedure p_test
(
  p_in_code1 in varchar2
 ) 
AS
 vs_msg   varchar2(4000);   --记录异常信息  
cursor cur_test(p_cur test1.code1%type) --test1为数据库表名，code1是test1的一个字段,p_cur是定义的一个游标参数
IS
 select * from test1 where code1 = p_cur;
 p_cur_test cur_test%rowtype;--定义一个游标变量，%rowtype说明见 注释@2
begin
 open cur_test(p_in_code1);--打开游标
    loop
        fetch cur_test into p_cur_test;--提取一行数据到游标变量中
        exit when cur_test%notfound;--取不到值跳出本次循环 ，%notfound 返回值说明见注释@3
        dbms_output.put_line(p_cur_test.id ||'  '||p_cur_test.code1||'  '||p_cur_test.code2||'   '||p_cur_test.code3);
    end loop;
  close cur_test;--关闭游标
  exception
      when others then
        vs_msg := 'ERROR IN P_TEST('||p_in_code1||'):'||SUBSTR(SQLERRM,1,500);
        Dbms_Output.put_line('异常警告！！！');
        Dbms_Output.put_line('异常信息：' || vs_msg);
      ROLLBACK;--发生异常，回滚事务
    --将当前错误，记录到日志中
     --insert into XXX(proc_name,error_info,op_date) values('p_test',vs_msg,SYSDATE);
     --commit;
     --return;
end p_test;

5.增删改操作

create or replace procedure p_test
(
  p_in_id in integer,
  p_in_code1 in varchar2,
  p_in_code2 in varchar2,
  p_in_code3 in number
 ) 
AS
 vs_msg   varchar2(4000);   --记录异常信息
begin
  insert into test1(id,code1,code2,code3)values(p_in_id,p_in_code1,p_in_code2,p_in_code3); -- 新增记录 根据输入参数
  Dbms_Output.put_line('新增数据' || sql%rowcount||'条。');
  update test1 set id = p_in_id + 1,code1=p_in_code1||'zzz',code2=p_in_code2||'zz',code3=p_in_code3+1 where id =p_in_id;--修改记录
  Dbms_Output.put_line('更新数据' || sql%rowcount||'条。');
  delete from test1 where id = p_in_id + 1;--删除记录 根据ID
  Dbms_Output.put_line('删除数据' || sql%rowcount||'条。');
  commit;
  exception
      when others then ---错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前s错误的详细信息。
        vs_msg := 'ERROR IN P_TEST('||p_in_id||'):'||SUBSTR(SQLERRM,1,500);
        Dbms_Output.put_line('异常警告！！！');
        Dbms_Output.put_line('异常信息：' || vs_msg);
      ROLLBACK;--发生异常，回滚事务
    --将当前错误，记录到日志中
     --insert into XXX(proc_name,error_info,op_date) values('p_test',vs_msg,SYSDATE);
     --commit;
     --return;
end p_test;

注释：
@1. 存储过程中的IS，AS

--在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在游标(CURSOR)中只能用IS不能用AS。

@2. %rowtype

--定义一个游标变量p_cur_test cur_test%ROWTYPE ，变量p_cur_test的类型为游标cur_test中的一行数据类型。

@3. %notfound

--判读是否提取到值           
--取到值cur_test%notfound 是false            
--取不到值cur_test%notfound 是true
--或者写为： not cur_test%found

--四个常用属性：%found、%notfound、%isopen和%rowcount。