[SQL]optimal not in performance

若使用not in 的效能很差
可以改用minus可以提升效能

http://dbaforums.org/oracle/index.php?showtopic=21583

http://www.dba-oracle.com/oracle_news/2005_3_31_tips_using_not_exists_minus_sql.htm

http://www.dba-oracle.com/t_cbo_existence_queries.htm

[JavaScrip]判斷字串是否為空白或包含某字元

判斷是否為空白字元
$('[name*=polFormatid]').val().trim() == ""
http://stackoverflow.com/questions/1151032/javascript-blank-space-validation


判斷是否包含某字元
$('[name*=polFormatid]').val().indexOf("%") != -1
http://stackoverflow.com/questions/1789945/method-like-string-contains-in-javascript http://miisoo.blogspot.tw/2008/01/javascript-string-operations.html

[SQL] new line sign

new line in plsql is  CHR(13) || CHR(10)

reference:http://www.orafaq.com/forum/t/59679/2/

[SQL]ROW LOCK的發生情況

1.UPDATE的時候會造成ROW LOCK

最好在UPDATE後COMMIT
開頭也要加 PRAGMA AUTONOMOUS_TRANSACTION

2.同時INSERT時也會LOCK

參考：
http://www.dbasky.com/oracle/update_index_row_lock_waits.html

[SQL]數值處理

ROUND(n, [m]) --四捨五入(m代表取到第幾位)

SELECT ROUND(5/3, 2) FROM DUAL;
=>1.67


SELECT ROUND(5/3, 0) FROM DUAL;
=>2



TRUNC(n, [m]) --無條件捨去(m代表取到第幾位)

SELECT TRUNC(5/3, 2) FROM DUAL;
=>1.66


SELECT TRUNC(5/3, 0) FROM DUAL;
=>1

[JAVA]POI Example

http://poi.apache.org/spreadsheet/quick-guide.html

http://www.blogjava.net/ltc603/archive/2006/08/16/63804.html

http://hypgr.iteye.com/blog/439820

SQL找出字串出現次數

1.用CONNECT BY LEVEL
   把字串切割，然後再group by
   再去看你要的字串會出現幾次

select count(*), substr('10', '20', '30', level, 1) from dual connect by level <= length('10', '20', '30')
group by substr('10', '20', '30', level, 1)
having substr('10', '20', '30', level, 1 = ',';

https://forums.oracle.com/forums/thread.jspa?threadID=2358534

2.用replace，然後再去減掉原本的字串

select length(replace('10', '20', '30'), ',', '--')) - length('10', '20', '30') from dual;

http://dbaspot.com/oracle-server/405323-counting-strings.html

第二個方法很厲害，都沒想過可以先replace 再去減掉長度
讚!