Oracle存储过程学习笔记

存储过程

存储过程是一种命名pl/sql程序块,它可以被赋予参数,存储在数据库中,可以被用户调用.由于存储过程是已编译好的代码,所以在调用的时候不必再次编译代码，从而提高程序的运行效率。另外存储过程可以实现程序的模块化设计.

1、创建存储过程

语法：

 Create [or replace] procedure procedure_name  
 [ (parameter[{in|in out}]) data_type，  
 (parameter[{in|in out}]) data_type，  
 ……  
 ]  
{ is|as}  
 Decoration section  
Begin  
   Executable section;  
Exception  
   Exception handlers;  
End;  

Procedure_name存储过程的名称
Parameter 参数
In 向存储过程传递参数
Out：从存储过程返回参数
In out:传递和返回参数
Data_type:参数的类型 不能够指明长度
As|is后声明的变量主要过程体,且不能加declare语句。

//创建一个插入emp中记录的存储过程 
SQL> create procedure insert_emp as  
     begin  
     insert into emp(empno,ename,job,mgr,sal,comm,deptno)  
values('7777','redarmy','teacher','7369',9000,1000,20);  
     commit;  
     end insert_emp;  
     / 

2、调用存储过程

SQL> set serveroutput on;  
SQL> begin  
     insert_emp;  
     end;  
     /

3、修改存储过程

SQL> create or replace procedure insert_emp as 
     //修改时只需加 or replace就可以了 里边的存储过程就可以修改了  
     begin  
     insert into emp(empno,ename,job,mgr,sal,comm,deptno)  
values('7777','redarmy','teacher','7369',9000,1000,20);  
     commit;  
     end insert_emp;
     /  

4、参数

Oracle中有三种参数模型 in、out、in out

(1).in参数

该类型的参数值有调用者传入,并且只能被存储过程读取,也是默认格式.

案例：

SQL> create or replace procedure insert_emp(  
     cempno in number,  
     cename in varchar2,  
     cjob in varchar2,  
     cmgr in number,  
     chiredate in date,  
     csal in number,  
     ccomm in number,  
     cdeptno in number  
     ) as  
     begin  
     insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)  
      values(cempno,cename,cjob,cmgr,chiredate,csal,ccomm,cdeptno);  
     end insert_emp;  
     /

     Procedure created 

上面创建的存储过程需要出入参数,在oralce有如下三种方式传入参数

• 名称表示法

语法如下：

参数名称=>参数值;多个之间用逗号隔开

SQL> set serveroutput on;  
SQL> begin
insert_emp(cempno=>7377,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);  
  end;  
  /  

• 位置表示法

当参数比较多时,名称表示法可能会比较长,为克服名称表示法的弊端,可以采用位置表示法,注意参数一定要对应。

SQL> set serveroutput on;  
SQL> begin  
      insert_emp(3333,'mjjj','stu',7777,to_date('2011-01-01','YYYY-MM-dd'),5000,1000,20);  
  end;  
  /  

• 混合表示法

SQL> set serveroutput on;  
SQL> begin       insert_emp(9999,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);  
 end;  
 /

注意：当用户使用的混合表示法时,分界线之前必须一致,分界线之后必须一致,并且不能穿插。

(2).out参数

该类型的参数值是有存储过程写入.out类型的参数适用于存储过程向调用者返回多条信息的情况。

//创建一个根据员工编号查询员工名称及薪资存储过程  
SQL> create or replace procedure emp_select(cempno in       number,cename out emp.ename%type,csal out emp.sal%type  
  ) is  
begin  
     select ename,sal into cename,csal from emp where     empno=cempno;  
     exception  
     when NO_DATA_FOUND then  
     cename:='NULL';  
     csal:=0;  
     end emp_select;  
     / 

Procedure created

调用存储过程：

out输出的参数是返回值,也就说在调用存储过程的时候必须有提供能够接受返回值的变量。
在这里我们需要使用variable命令绑定参数

SQL> variable ename varchar2(20); //绑定参数的声明  
SQL> variable sal number;  
SQL> begin  
     emp_select('7777',:ename,:sal); //执行存储过程  
     end;  
     /  

PL/SQL procedure successfully completed  
ename  
---------  
redarmy  
sal  
---------  
9000  

SQL> print ename; //打印相应的参数  
ename  
---------  
redarmy  

SQL> print sal; //打印相应的参数  
sal  
---------  
9000  

(3).in out参数

in参数可以接收一个值,但是不能在存储过程中修改这个值,而对于out参数,它在调用过程时为空,在过程执行中将为为这个参数指定一个值，并在执行后返回.
而in out参数同时具有了in参数和out参数的特性,在过程中可以读取和写入该类型的参数。

//作业实现 案例交换两个数  
create or replace procedure test_pro(num1 in out number,num2 in out number) as
num3 number;
begin
dbms_output.put_line('调换前:'||num1||' '||num2);
num3:=num1;
num1:=num2;
num2:=num3;
dbms_output.put_line('调换后:'||num1||' '||num2);
end test_pro;   

//执行结果
SQL> set serveroutput on;
SQL> declare
    num1 number;
    num2 number;
    begin 
    num1:=1;
    num2:=2;
    test_pro(num1,num2);
    end;
    /

调换前:1 2
调换后:2 1

PL/SQL procedure successfully completed

参考文献：redarmy_chen，http://redarmychen.iteye.com/blog/1023160