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 curis SELECT QYBM FROM GH_QYXX WHERE 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_QYGSGZ where 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://www.jb51.cc/article/p-qywjbyav-um.html

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$sqlarea orderby disk_reads desc

)whererownum<10

9、oracle函数

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

select * from (

select zzjgdm, wmsys.wm_concat(qymc) nsrsbhfrom gh_qyxx

where length(zzjgdm)='10'group by zzjgdm ) where nsrsbh isnot null

and instr(nsrsbh,',')>0and zzjgdm not in

(select zzjgdmfrom gh_qyxx groupby 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

delete from t_user where id in (

#{item.departmentGuid}

</foreach>

)

</delete>

可以用exists关键字