GHGL项目总结-Oracle

项目当时用的是Oracle数据库，遇到的问题的一些总结。

1、中文乱码问题：

Oracle和pl/sql字符集一致即可。

http://blog.csdn.net/aovenus/article/details/12648751

2、循环添加数据

DECLARE

x number;

BEGIN

x :=1000000;

WHILE x < 5000000 LOOP

x := x + 1;

end loop;

END;

 

用游标循环添加数据

declare

  cursor curisSELECT QYBM FROM GH_QYXXWHERE ROWNUM <5;

begin

  for rc in cur loop

     insertinto GH_QYHF(ywlsdm, qybm, qysqzt, qybqzt, czyydm, czrydm, czsj)

    values(Seq_ywls.Nextval, rc.qybm,'0','1', '符合条件','1', to_date('01-03-2017','dd-mm-yyyy'));

    end loop;

end;    

3、Oracle中的表结构导出到word  

http://blog.itpub.net/25444422/viewspace-2091023/

4、查出每组中时间最近的一条记录

http://bbs.csdn.net/topics/320234900/

a、

select * from GH_QYGSGZ t

where skssn=(selectmax(skssn)from GH_QYGSGZ where qybm=t.qybm)

 and skssy = (select max(skssy)from GH_QYGSGZwhere qybm=t.qybm)

b、rownum:

select   * from ( select qybm,skssn,skssy,zgrs,gzze ,row_number() over(partition by qybm order by skssndesc, skssy desc) rn from GH_QYGSGZ)t1 where rn=1 and qybm ='00510148'

5、增加sequece序列

create sequence

 minvalue 1

 maxvalue 99999999

  startwith 1

 increment by 1

  CYCLE

 nocache;

 

http://blog.csdn.net/indexman/article/details/37653911

6、列转行、行转列

http://www.2cto.com/database/201501/367164.html

7、oracle sqlORA-01840:输入值对于日期格式够长

查一下输入的数据，例如to_date('2012-12','yyyy-mm-dd')因为你要转换的值不满足你提供的格式长度，就会报这个错

8、查看数据库sql语句执行性能

select * from (

select parsing_user_id,executions,sorts

command_type,disk_reads, SQL_FULLTEXT,sql_textfrom v$sqlareaorderby disk_readsdesc

)whererownum<10

 

9、oracle函数

wmsys.wm_concat 统计某个字段相同，另一个字段不同

select * from (

select zzjgdm, wmsys.wm_concat(qymc) nsrsbhfrom gh_qyxx

 where length(zzjgdm)='10'groupby zzjgdm ) where nsrsbh isnotnull

 and instr(nsrsbh,',')>0and zzjgdmnot in

 (select zzjgdmfrom gh_qyxxgroupby zzjgdm having count(zzjgdm)>1);

 

相似度函数：用来比较两个字符串的相似度- SYS.UTL_MATCH.edit_distance_similarity(“”“”)

select* from (

  selectscjx.*,SYS.UTL_MATCH.edit_distance_similarity(#{jydz,jdbcType=VARCHAR},scjxmc)semblance from gh_zd_scjx scjx

    order by semblance desc ) where rownum =1

10、从以下得出子查询中in最大是1000

<deleteid="batchDelete">

        delete from t_user where id in (

        <foreachcollection="list"item="item"separator=",">

            #{item.departmentGuid}

        </foreach>

        ) 

 </delete>

可以用exists关键字