【数据库】Oralce存储过程 [个人笔记]

1、定义

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。可以简单理解为：声明一个方法，该方法中有个多sql 语句。

2、存储过程的创建

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
无参存储过程：

create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;

有参存储过程：

create or replace procedure runbyparmeters    
    (isal in emp.sal%type,   
     sname out varchar,  
     sjob in out varchar)  
 as   
    icount number;  
 begin  
      select count(*) into icount 
      from emp where sal>isal and job=sjob;  
      
      if icount=1 then  
        .......sname  = "你好";
      else  
        ....  sname  = "大家好"
     end if;  
exception  
     when too_many_rows then  
     DBMS_OUTPUT.PUT_LINE('返回值多于1行'); //相当于java 中的system.out.print
     when others then  
     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end;

注：其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

3、游标的定义（集合、数组）

个人理解： 游标相当于数组、集合，一般用于查询返回数据为多条时，数据的遍历；

--显示cursor的处理
declare  							
cursor cursor_name is 				 ---声明cursor,创建和命名一个sql工作区
    select real_name from t_test;
    v_realname varchar2(20);
begin 
    open cursor_name;					---打开cursor,执行sql语句产生的结果集
    fetch cursor_name into v_realname;	--提取cursor,提取结果集中的记录
    dbms_output.put_line(v_realname);
    close cursor_name;					--关闭cursor
end;

4、存储过程中游标定义使用

as 				//定义(游标一个可以遍历的结果集)   
CURSOR cur_1 IS   
  SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,  
         SUM(usd_amt)/10000 usd_amt_sn   
  FROM BGD_AREA_CM_M_BASE_T   
  WHERE ym >= vs_ym_sn_beg   
       AND ym <= vs_ym_sn_end   
  GROUP BY area_code,CMCODE;   
      
begin //执行（常用For语句遍历游标）       
FOR rec IN cur_1 LOOP   
  UPDATE xxxxxxxxxxx_T   
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn   
   WHERE area_code = rec.area_code   
   AND CMCODE = rec.CMCODE   
   AND ym = is_ym;   
END LOOP;

注：
AS 等同于declare 都是声明的作用。
cur_1 就是集合名（数组名)
FOR 变量 IN 数组 LOOP

5、 存储过程常用的调用方式

SQL> exec  proc_emp('参数1','参数2');//无返回值过程调用  
SQL> var vsal number  
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  
或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用 

6、Java的调用存储过程【示例代码】

import java.sql.*;
public class ProcedureTest 
{
	public static void main(String args[]) throws Exception
	{
	   //加载驱动
	   DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
	   //获得连接
	   Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");
	   CallableStatement c=conn.prepareCall("{call test_pro(?)}");
	   
	   //设置入参
	   c.setInt(1,1);
	   //设置出参类型，否则可能会无效索引异常
	   c.registerOutParameter(1,java.sql.Types.VARCHAR);
	   c.execute();//执行调用
	   
	   System.out.println (c.getString(1));
	   conn.close(); //记得关闭连接
	}
}