Oracle存储过程简单使用及示例代码

公司新项目启动需要将旧系统进行数据的迁移，初步决定使用存储过程进行数据迁移，所以将近期学习存储过程的相关知识进行汇总，在此感谢网上前辈提供的学习资料！

世界正在奖励那些偷偷学习的人。

一、什么是存储过程：

简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。

存储过程优点：
1、存储过程增强了SQL语言灵活性。存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
2、减少网络流量，降低了网络负载。存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行；
3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
4、系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

二、存储过程与函数的区别：

    在我们学习一项新技术的时候，一定要对比已经掌握的东西进行探索，这样不仅可以温习旧的东西，还可以更深入了解新技术的原理，对于掌握新的知识更有帮助。

　　相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。

　　　　    2.都是一次编译，多次执行。

　　不同点：1.存储过程定义关键字用procedure，函数定义用function。

　　　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。

　　　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。

总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

 

三、存储过程的分类：

1.无参数存储过程
2.含输入参数存储过程
3.含输出参数存储过程
4.含输入输出参数存储过程

四、存储过程的变量解释

default value：为参数设置默认值,只适用于in参数 
or replace:替换已经存在的存储过程 
procedure_name：存储过程名称 
parameter ：参数名 
in ：传递参数 
out:返回一个参数 
int out：传递和返回一个参数 
data_type：参数的数据类型，用于过程体重 
is | as:用于声明变量 
declaration_section：变量名称
begin:sql体开始
end：sql体结束

五、存储过程的创建

1、创建无参数存储过程：

 

------------------------创建存储过程---------------------------------------
create or replace procedure test_procedure
as
v_total number(1);
begin
  select count(*) into v_total from Aikes_Test;
  DBMS_OUTPUT.put_line('总人数：'||v_total);
end;

alter user scott account unlock; 
alter user scott identified by tiger;

------------执行存储过程
begin
  -- Call the procedure
  test_procedure;
end;

2、创建游标存储过程：

 

 

--------------------------创建带游标的存储过程-----------------------------
create or replace procedure test_cursor
    AS 
      ---使用游标
        CURSOR test_cursor IS select t.aid,t.name from Aikes_Test t;
    Begin
        for Test_record IN test_cursor loop---遍历游标，在打印出来
           DBMS_OUTPUT.put_line(Test_record.aid||Test_record.name);
        END LOOP;
        test_procedure;--同时执行另外一个存储过程（TEST_LIST中包含存储过程test_count）
    end;

 -----执行存储过程TEST_LIST
begin 
    test_cursor;
END;

3、创建带参数存储过程：
 

---------------------------------------------------------------------------------
---存储过程的参数
---IN  定义一个输入参数变量，用于传递参数给存储过程   
---OUT 定义一个输出参数变量，用于从存储过程获取数据   
---IN OUT  定义一个输入、输出参数变量，兼有以上两者的功能  
---这三种参数只能说明类型，不需要说明具体长度 比如 varchar2(12)
---defaul 可以不写，但是作为一个程序员最好还是写上。

--------------------------创建带参数的存储过程------------IN---------------------------
 create or replace procedure test_param_in(p_id1 in VARCHAR2  default '6')
        as v_name varchar2(32);
        begin
          select t.name into v_name from Aikes_Test t where t.aid=p_id1;
          DBMS_OUTPUT.put_line('name：'||v_name);
        end;
         
         
----执行存储过程----若不添加参数会使用存储过程中的默认参数
begin
   test_param_in;
end;

------------------------------创建有参数的存储过程-------------OUT---------------------
create or replace procedure test_param_out(v_name OUT VARCHAR2 )
as  
begin
  select name into v_name from Aikes_Test where aid='1';
  DBMS_OUTPUT.put_line('name：'||v_name);
End;
      
----执行存储过程
DECLARE  
  v_name VARCHAR2(32);   
  BEGIN  
  test_param_out(v_name);
END;  

------------------------------创建有参数的存储过程------IN-------OUT------------------
create or replace procedure test_param_in_out(myno in out varchar2)
as  
begin 
  idno:='1424'||myno;
end;
    
----执行存储过程
DECLARE  
  idno VARCHAR2(32);  
BEGIN  
  idno:='26731092';  
  test_param_in_out(idno);  
  DBMS_OUTPUT.PUT_LINE('证件号：'||idno);  
END;  

4、查看有哪些存储过程：

 

 

select * from user_source;

六、存储过程中异常和事务的处理：

 

create or replace Procedure demo_procedure(no in VARCHAR2)
    AS 
        CURSOR demo_cursor IS (SQL语句)
    Begin
      for demo_record IN demo_cursor Loop
          --deal data;
          Commit;              
     End Loop;
       Exception
         When Others Then
	    Rollback;
            Raise;--将异常抛出，交给上层处理
    End demo_procedure;

总结：本文记录的只是最基本的语法，在实际应用中还会有各式各样的问题，这就需要大家针对不同的问题各自分析，大部分是因为语法问题导致失败，还有一小部分就是权限问题导致失败。最后存储过程的内部逻辑需要根据不同的业务制定最佳算法，针对存储过程中涉及到的事务提交，以及异常的处理也要重点留意。