Oracle学习累积

Oracle学习累积

1.oracle的一些基本语句：

    a:实现可修改的查询语句:select t.*,t.rowid from allentest t;

    b:排序实现：按序输出一区间段的值:

         select * from (select rownum,t.* from allentest t order by id) where rownum<8

       取某一段值:

         select *
  from (select rownum rowed, id, name
          from (select rownum, id, name from allentest order by id asc)
         where rownum < 20)
 where rowed between 2 and 8

        注意：在oracle中同样存在两个伪列，rownum,rowid

     c:日期查询

         当前日期： select to_char(sysdate,"YYYY-MM-DD") from dual

         当前日期的前一天：select to_char(sysdate-1,"YYYY-MM-DD") from dual

         精确时间：select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual

     d:Decode函数：

            DECODE的语法：

            DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

      表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else

     e: union操作：需要保证列名相同，可用伪列 as columns 形式保证一致

        String SQL ="select * from (select ara.act_code,ara.name,ara.seq_no,to_char(ara.file_code) as file_code,ara.description,ara.attach_type from act_reviewer_attachment ara " + "where act_code = ?" + "union select a.act_code,p.cname,a.seq_no,'' as file_code,null as description,null as attach_type from act_reviewer a left join person p on a.review_psn_code = p.psn_code " + "where act_code = ?" +" and trim(p.cname) not in (select name from act_reviewer_attachment " + " where act_code = ?)) t order by t.seq_no"; return super.getJt().queryForList(SQL, new Object[]{actCode,actCode,actCode});

     f:  trim操作

 

      TRIM([[位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。 例1: SELECT TRIM(' Sample '); 结果: 'Sample' //jstl中的解释XML文件操作

   g:获取Oracle表中的列的具体信息，包括字段的类型，长度等等

 

 

 

      Select * from user_tab_columns where table_Name = upper(trim('allentest')) and column_name = upper(trim('name'))

 

  h:Oracle表的建立自增字段

      1建立数据表 create table Test_Increase( userid number(10) primary key, /*建立主键*/ username varchar2(20) ); 2创建自动增长序列 CREATE SEQUENCE TestIncrease_Sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 3创建触发器 CREATE TRIGGER Test_Increase BEFORE insert ON Test_Increase FOR EACH ROW begin select TestIncrease_Sequence.nextval into:New.userid from dual; end;

2.springJdbcTemplate读取更新Oracle的blob,clob字段:

           spring定义了一个以统一的方式操作各种数据库的Lob类型数据的LobCreator(保存的时候用),同时提供了一个LobHandler为操作二进制字段和大文本字段提供统一接口访问。 举例，例子里面的t_post表中post_text字段是CLOB类型,而post_attach是BLOG类型： public class PostJdbcDao extends JdbcDaoSupport implements PostDao { private LobHandler lobHandler; private DataFieldMaxValueIncrementer incre; public LobHandler getLobHandler() { return lobHandler; } public void setLobHandler(LobHandler lobHandler) { this.lobHandler = lobHandler; } public void addPost(final Post post) { String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)" + " VALUES(?,?,?,?)"; getJdbcTemplate().execute( sql, new AbstractLobCreatingPreparedStatementCallback( this.lobHandler) { protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException { ps.setInt(1, incre.nextIntValue()); ps.setInt(2, post.getUserId()); lobCreator.setClobAsString(ps, 3, post.getPostText()); lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach()); } }); } } 设置相对应的配置文件(Oracle 9i版本),Oracle的数据库最喜欢搞搞特别的东西啦： <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" lazy-init="true" /> <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true"> <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" /> </bean> <bean id="dao" abstract="true"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean> <bean id="postDao" parent="dao" class="com.baobaotao.dao.jdbc.PostJdbcDao"> <property name="lobHandler" ref="oracleLobHandler" /> </bean> Oracle 10g或其他数据库如下设置： <bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" /> <bean id="dao" abstract="true"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean> <bean id="postDao" parent="dao" class="com.baobaotao.dao.jdbc.PostJdbcDao"> <property name="lobHandler" ref="defaultLobHandler" /> </bean> 读取BLOB/CLOB块,举例： public List getAttachs(final int userId){ String sql = "SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null"; return getJdbcTemplate().query( sql,new Object[] {userId}, new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Post post = new Post(); int postId = rs.getInt(1); byte[] attach = lobHandler.getBlobAsBytes(rs, 2); post.setPostId(postId); post.setPostAttach(attach); return post; } }); }