oracle存储过程

基本结构
　

　create OR REPLACE PROCEDURE 存储过程名字

　　(

　　参数1 IN NUMBER,

　　参数2 IN NUMBER

　　) IS

　　变量1 INTEGER :=0;

　　变量2 DATE;

　　BEGIN


　　END 存储过程名字

通俗定义：

CREATE [OR REPLACE] PROCEDURRE PROCEDURE_NAME
[(PARAMETER [{IN |OUT | IN OUT}] DATATYPE [{:=|DEFAULT} EXPRESSION]
[,(PARAMETER [{IN |OUT | IN OUT}] DATATYPE [{:=|DEFAULT} EXPRESSION]...)]

[AUTHID{CURRENT_USER|DESIGNER}] 

{IS | AS}
[<DECLARATIONS>]

BEGIN
        [EXCEPTION  <EXCEPTION_HANDLERS>]
END PROCEDURE_NAME;

以下几点为存储过程实现常用的一些功能：
1.select INTO STATEMENT

　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

　　例子：

　

BEGIN
　　select col1,col2 into 变量1,变量2 FROM typestruct where xxx;
　　EXCEPTION
　　WHEN NO_DATA_FOUND THEN
　 　　 xxxx;

　　...

2.IF 判断

　　

IF V_TEST=1 THEN
　 　 BEGIN
　　　　 do something
　 　 END;
　　END IF;

create or replace procedure test(x in number) is

begin

    if x >0 then

         begin

            x := 0 - x;

         end; 

    end if;

    if x = 0 then 

        begin

         x: = 1;

        end; 

    end if; 

end test;

3.while 循环

WHILE V_TEST=1 LOOP
　　   BEGIN
　        XXXX
　　   END;
　  END LOOP;

create or replace procedure test(i in number) as

begin

   while i < 10 LOOP 

       begin    

          i:= i + 1;

       end; 

   end LOOP;

end test;

4.变量赋值
　　

V_TEST := 123;

create or replace procedure test(workDate in Date) is

x number(4,2);

 begin

 x := 1; 

end test;

5.用for in 使用cursor

　 ...
　 IS
　　CURSOR cur IS select * FROM xxx;
　 BEGIN
　FOR cur_result in cur LOOP
　    BEGIN
　　     V_SUM :=cur_result.列名1+cur_result.列名2
　    END;
　  END LOOP;
　END;

create or replace procedure test() as

    Cursor cursor is select name from student; name varchar(20);

begin

    for name in cursor LOOP 
        begin

          dbms_output.putline(name); 

        end; 
    end LOOP;
end test;

6.带参数的cursor

　 CURSOR C_USER(C_ID NUMBER) IS select NAME FROM USER where TYPEID=C_ID;
　 OPEN C_USER(变量值);
　　LOOP
　　FETCH C_USER INTO V_NAME;
　　EXIT FETCH C_USER%NOTFOUND;
　　　do something
　 END LOOP;
CLOSE C_USER;

对游标在oracle procedure中的应用做如下解释：
(1)Cursor型游标(不能用于参数传递)

create or replace procedure test() is   

cusor_1 Cursor is select std_name from student where  ...;/*Cursor的使用方式1*/  
cursor_2 Cursor;

begin

select class_name into cursor_2 from class where ...;  /*Cursor的使用方式2*/

	/*可使用For x in cursor LOOP .... end LOOP; 循环来实现对Cursor的遍历*/

end test;

(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor_name SYS_REFCURSOR; 
name   varhcar(20);

begin

OPEN cursor_name FOR select name from student where ... /*SYS_REFCURSOR只能通过OPEN方法来打开和赋值*/

 LOOP
   fetch cursor into name   /*SYS_REFCURSOR只能通过fetch into来打开和遍历 */
   exit when cursor%NOTFOUND; 
  /*SYS_REFCURSOR中可使用三个状态属性：                                         
       %NOTFOUND(未找到记录信息) 
       %FOUND(找到记录信息)                                         
       %ROWCOUNT(然后当前游标所指向的行位置)*/
 dbms_output.putline(name); 

 end LOOP;
 rsCursor := cursor_name;
end test;

游标详细使用方法请参考：
http://tomotoboy.iteye.com/admin/blogs/437646[url]

参考文献：
http://tech.ddvip.com/2008-09/122049998460199.html
http://blog.csdn.net/squirrelRao/archive/2008/07/11/2639571.aspx