[sql]DATE to Char

time zone

select to_char(sysdate, 'MON', 'NLS_DATE_LANGUAGE=american') from dual;


Reference:

http://sqle.ru/index.php/Oracle_PL/SQL/Date_Timezone/Date_Format#Change_the_original_date_format_in_the_TO_CHAR_function

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/SQL52F46/Default.aspx

http://gogates.blogspot.tw/2011/09/use-of-nlsdatelanguage-parameter-in.html

[SQL]將用分號將字串切割，並切換多行

select regexp_substr(mystring,'[^,]+',1,level) element
        from dual
     connect by level <= length(regexp_replace(mystring,'[^,]+')) + 1

Reference:
http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc

[SQL]反向查詢吃不到INDEX

剛剛在看文件的時候看到
select 的條件若有 <> 時會吃不到Index

另外也儘量不用 not in
可以用not exists or in ( select ... from table1 minus select ... from table2)

以上可以提升效能

http://stackoverflow.com/questions/3545323/why-the-select-from-table-where-id-not-in-list-of-int-ids-query-is-slow-in

[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