oracle psql 相關

1查看分區大小

SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 USED_MB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME IN ('tableone',
                        'tabletwo')

2 比較兩表資料差異

 (select * from table1)

  minus

 (select * from table2)

3 '123,456,789'需要作為in('123','456','789')來查詢，但是正常的replace後得到的還是一個字串，所以才去下面的多行的辦法，

  不過限於sql長度，反倒不如代碼處理後直接得到'123','456','789'合算

  select replace(regexp_substr('123,456,789','[^,]+',1,level),',',' ') c1
               from dual
               connect by level<=length('123,456,789')-length(replace('123,456,789',',',''))+1

4 oracle 10g trim不起作用

  REGEXP_REPLACE(param, '\s*', '')

5 按照座標計算兩點間距離

    earth_padius:=6378137.0;
  radLat1:=3.141592625*lat1/180.0;
  radLat2:=3.141592625*lat2/180.0;
  a:=radLat1 - radLat2;
  b:=3.141592625*lng1/180.0 - 3.141592625*lng2/180.0; 
  s:=2*Asin(Sqrt(power(sin(a / 2), 2) +cos(radLat1) * cos(radLat2) * power(sin(b / 2), 2)));
  s := s * earth_padius;
  s := Round(s * 10000) / 10000.0;
  return(s);