使用oracle系统游标处理数据

 

实例代码：

--set serveroutput on;

create or replace procedure split_page_procedure(page_index in number,

page_size in number,

key_word in varchar2) is

　　my_curser SYS_REFCURSOR;

--分页参数

page_count number(7);

min_index number(7); --起始位置

max_index number(7); --结束位置

--打印参数

name_param userlogin.user_name%type;

uid_param userlogin.user_id%type;

begin

if (page_index < 1) or (page_size < 1) then

　　RAISE NO_DATA_FOUND;

end if;

--参数校验

--设置分页参数 分页参数值最少为1，

min_index := (page_index - 1) * page_size - 1;

max_index := page_index * page_size;

--利用分页查询数据;

open my_curser for

select i.user_name, i.user_id name

from (select u.*, rownum rn from userlogin u where rownum < max_index) i

where i.rn > min_index;

loop

FETCH my_curser

INTO name_param, uid_param;

--游标取不到数据则退出

exit when my_curser%NOTFOUND;

dbms_output.put_line('参数：name:' || name_param || ' uid:' || uid_param);

end loop;

close my_curser;

--査取总记录

select count(1) into page_count from userlogin;

dbms_output.put_line('返回的总记录条数：' || page_count);

--异常处理

EXCEPTION

　　WHEN NO_DATA_FOUND THEN

　　　　DBMS_OUTPUT.PUT_LINE('分页参数不合理 page_index：' || page_index ||

　　　　　　' page_size:' || page_size);

　　WHEN OTHERS THEN

　　DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM);

end split_page_procedure;

 

 

oracle块操作

declare

　　v_param number(7,2);

begin

　　v_param :=12;

　　if v_param>10 then

　　dbms_output.put_line('aaa');

　　end if;

end;

方法一, 直接抛

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL>  DECLARE    2     -- 测试异常.    3    e_test_exception EXCEPTION;    4   BEGIN    5    6     -- 直接抛出异常，测试下面的捕获    7    RAISE e_test_exception;    8    9    EXCEPTION   10       WHEN  e_test_exception  THEN   11        dbms_output.put_line( 'Test Error !' );   12       WHEN  OTHERS  THEN   13        dbms_output.put_line( 'OTHERS Error!' );   14   END ;   15  / Test Error !   PL/SQL  procedure  successfully completed.

 

方法二, 定义个错误代码与消息后， 再抛。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL>  BEGIN    2     -- 错误代码允许的范围是 -20,000～20,999    3    RAISE_APPLICATION_ERROR(-20000,  'My Error Happen!' );    4    5    EXCEPTION    6       WHEN  OTHERS  THEN    7        dbms_output.put_line( 'Error Code = '  || TO_CHAR(SQLCODE) );    8        dbms_output.put_line( 'Error Message = '  || SQLERRM );    9   END ;   10  / Error Code = -20000 Error Message = ORA-20000: My Error Happen!   PL/SQL  procedure  successfully completed.

 

mybatis调用oracle存储过程

存储过程在小公司用的不多，但是如果业务比较复杂或者性能要求比较苛刻的时候存储过程就派上用场了，ibatis的前期的一些版本貌似不支持存储过程因此我选择了mybatis来做实验。

1.无输入和输出参数的存储过程

我写了一个比较简单的，需要注意的是Oracle无参存储过程不能写括号

CREATE OR REPLACE Procedure cascadeoperationAsBegin Delete From teacher Where id=1; Update studentdetail Set address='宁波市海曙区' Where studentid=10;End;

这里执行了2个操作，可能用过mybatis的人会迷惑执行的时候到底使用update标签呢还是delete标签，其实都行，我也试过select标签也是OK的，下面是部分的配置文件

    
     	   {call cascadeoperation}	
    

2.带有输入和输出参数的存储过程

,我这里加入了if else的几个判断

CREATE OR REPLACE Procedure queryTeacher(fid In Integer,Type In Varchar,Name Out Varchar)AsBeginIf Type='1' thenSelect Name Into Name From student Where id=fid;Else if Type='2' ThenSelect Name Into Name From teacher Where id=fid;Else  Name:='错误';End If;End If;End;

下面顺便把我在命令行窗口执行的存储过程语句贴出来

DeclareName  Varchar2(50);Beginqueryteacher(3,'2',Name);DBMS_OUTPUT.put_line(Name);End;/

执行过类似语句的时候可能看不到任何的输出，不要着急只需在命令行使用set serveroutput on;

看到结果了吧，下面使用mybatis来执行这个存储过程，下面是映射文件的写法

    
     	   {call queryTeacher(#{fid,mode=IN,jdbcType=INTEGER},#{type,mode=IN,jdbcType=VARCHAR},#{name,mode=OUT,jdbcType=VARCHAR})}	
    

那怎么取得返回的内容呢，其实只要存储过程执行后map里就有值了，java代码大致如下

Map
    
      mm=new HashMap
     
      (); mm.put("fid", 3); mm.put("type", 2); m.queryTeacher(mm); System.out.println(mm.get("name"));
     
    

 

 

3.返回游标的存储过程

还有一种存储过程，它可以返回一个游标就类似一个集合这种

CREATE OR REPLACE Procedure getTeacher(cur_arg out Sys_Refcursor)Asbegin    open cur_arg for Select * From teacher;End;

这种情况，在mybatis里就稍微有些不同了，此时jdbcType就是CURSOR，javaType则是ResultSet了，这里还可以把结果转成resultMap了，如下所示

 

    
     	   {call GETTEACHER(#{result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet, resultMap=resultMap3})}	
    

这里的话Java代码就稍微复杂一些

Map
    
      map = new HashMap
     
      ();		m.getAllTeacher(map);		Set
      
       
        > set = map.entrySet();		for (Iterator
        
         
          > it = set.iterator(); it .hasNext();) { Map.Entry
          
            entry = (Map.Entry
           
            ) it .next(); // System.out.println(entry.getKey() + "--->" + // (Teacher)entry.getValue()); List
            
              t = (List
             
              ) entry.getValue(); Iterator
              
                itera = t.iterator(); while (itera.hasNext()) { Teacher tt = itera.next(); System.out.println(tt.getName() + "," + tt.getAddress()); } }
              
             
            
           
          
         
        
       
      
     
    

 

到这里存储过程已经差不多了，研究了好久才弄出来，其他的用jdbc执行存储过程我随后会把文章添上来。

 

 

---

 

 

返回游标  可以直接用下面的方法  上面原作者的写法 太麻烦了

 

1 2 3 4 5 6

Map map =  new  HashMap();          map.put( "jid" , jid);          userInfoMapper.getFriendList(map);          //result 为在mybatis xml文件时 写的返回结果名          List<UserInfo> list = (List<UserInfo>)map.get( "result" );          return  list;

 

 

MyBatis 通过包含的jdbcType类型

BIT         FLOAT      CHAR           TIMESTAMP       OTHER       UNDEFINED

TINYINT     REAL       VARCHAR        BINARY          BLOB        NVARCHAR

SMALLINT    DOUBLE     LONGVARCHAR    VARBINARY       CLOB        NCHAR

INTEGER     NUMERIC    DATE           LONGVARBINARY   BOOLEAN     NCLOB

BIGINT      DECIMAL    TIME           NULL            CURSOR

 

Mybatis中javaType和jdbcType对应和CRUD例子

<resultMap type="java.util.Map" id="resultjcm">
  <result property="FLD_NUMBER" column="FLD_NUMBER"  javaType="double" jdbcType="NUMERIC"/>
  <result property="FLD_VARCHAR" column="FLD_VARCHAR" javaType="string" jdbcType="VARCHAR"/>
  <result property="FLD_DATE" column="FLD_DATE" javaType="java.sql.Date" jdbcType="DATE"/>
  <result property="FLD_INTEGER" column="FLD_INTEGER"  javaType="int" jdbcType="INTEGER"/>
  <result property="FLD_DOUBLE" column="FLD_DOUBLE"  javaType="double" jdbcType="DOUBLE"/>
  <result property="FLD_LONG" column="FLD_LONG"  javaType="long" jdbcType="INTEGER"/>
  <result property="FLD_CHAR" column="FLD_CHAR"  javaType="string" jdbcType="CHAR"/>
  <result property="FLD_BLOB" column="FLD_BLOB"  javaType="[B" jdbcType="BLOB" />
  <result property="FLD_CLOB" column="FLD_CLOB"  javaType="string" jdbcType="CLOB"/>
  <result property="FLD_FLOAT" column="FLD_FLOAT"  javaType="float" jdbcType="FLOAT"/>
  <result property="FLD_TIMESTAMP" column="FLD_TIMESTAMP"  javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"/>
 </resultMap>

 

 

Mybatis中javaType和jdbcType对应关系

 

                   JDBC Type           Java Type            CHAR                String            VARCHAR             String            LONGVARCHAR         String            NUMERIC             java.math.BigDecimal            DECIMAL             java.math.BigDecimal            BIT             boolean            BOOLEAN             boolean            TINYINT             byte            SMALLINT            short            INTEGER             int            BIGINT              long            REAL                float            FLOAT               double            DOUBLE              double            BINARY              byte[]            VARBINARY           byte[]            LONGVARBINARY               byte[]            DATE                java.sql.Date            TIME                java.sql.Time            TIMESTAMP           java.sql.Timestamp            CLOB                Clob            BLOB                Blob            ARRAY               Array            DISTINCT            mapping of underlying type            STRUCT              Struct            REF                         Ref            DATALINK            java.net.URL[color=red][/color]
JDBC Type           Java Type
CHAR                String
VARCHAR             String
LONGVARCHAR         String
NUMERIC             java.math.BigDecimal
DECIMAL             java.math.BigDecimal
BIT             boolean
BOOLEAN             boolean
TINYINT             byte
SMALLINT            short
INTEGER             int
BIGINT              long
REAL                float
FLOAT               double
DOUBLE              double
BINARY              byte[]
VARBINARY           byte[]
LONGVARBINARY               byte[]
DATE                java.sql.Date
TIME                java.sql.Time
TIMESTAMP           java.sql.Timestamp
CLOB                Clob
BLOB                Blob
ARRAY               Array
DISTINCT            mapping of underlying type
STRUCT              Struct
REF                         Ref
DATALINK            java.net.URL[color=red][/color]