十二種常用的英文標點符號

常用的英文標點符號有十二種

1.Apostrophe 上標點(')  
2.Comma 逗號(,)  
3.Colon 冒號(:)  
4.Dash 破折號(--)  
5.Exclamation Point 驚嘆號(!)  
6.Hyphen 連字號(-) 
7.Parentheses 括號( )  
8. Period 句號(．)  
9. Question Mark 問號 (?) 
lO.Quotation Marks 引號("…")   
11.Semicolon 分號(;)  
l2.Triple Dots 刪節號(…)

MySQL dump (import and export)

C:\mysqldump --user=root -p OLD_DBNAME > c:\test.sql
C:\mysqldump --user=root -p NEW_DBNAME < c:\test.sql

判斷Excel的值並複製到新的分頁

Sub Macro1()
 
   '在Sheet1執行巨集
   Sheet1.Activate
 
   '定義參數
   Dim k As Long, Km As Long

   '從1開始
   Km = 1

   '複製抬頭
   Sheet2.Rows(1).Value = Rows(1).Value
 
   For k = 2 To Range("E65536").End(xlUp).Row
 
      'E欄位
      With Range("E" & k)
   
         If .Value = 3 And .Value <> "" Then
     
            Km = Km + 1
         
            '將k列的資料複製到 Sheet2的Km列
            Sheet2.Rows(Km).Value = Rows(k).Value
         
         End If
      End With
   Next k
 
End Sub

Update sql with join table

update TABLE_A
set TABLE_A.COLUMN_A = TABLE_B.COLUMN_A
from TABLE_A
join TABLE_B on TABLE_A.COLUMN_PK = TABLE_B.COLUMN_PK

Use '&' in oracle Database

oracle 裡面
[ & ]後面的字串會當做變數處理(變數首碼符號)

set define off;

insert ......

set define on

oracle 自動新增欄位 procedure

SET SERVEROUTPUT ON; --ENABLE

EXEC DBMS_OUTPUT.ENABLE(1000000);

DECLARE

B NUMBER;

BEGIN

SELECT COUNT(*)

INTO B

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TABLE_A' AND COLUMN_NAME = 'COLUMN_A';

IF B = 0

THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLE_A ADD(COLUMN_A NVARCHAR2(100) DEFAULT ''IB'' )';

DBMS_OUTPUT.PUT_LINE('ALTER TABLE TABLE_A ADD(COLUMN_A NVARCHAR2(100) DEFAULT ''IB'' )');

END IF;

END;

oracle drop database

shutdown abort;
startup mount exclusive restrict;
drop database_name;
exit;

Oracle imp/exp file

exp userid/password@SID file=d:\file_name.dmp full=y;

imp userid/password@SID file=d:\file_name.dmp ignore=y tables=(table1);
tables=(table1) is option

MS SQL rename table

ALTER TABLE tablename RENAME TO footable

EXEC sp_rename 'OldTableName', 'NewTableName'

EXEC sp_rename
   @objname = 'TableName.OldColumnName',
   @newname = 'NewColumnName',
   @objtype = 'COLUMN'

Oracle marge into

若符合條件則 update，
否則 insert

MERGE INTO Table1 target
USING Table2 source
ON (target.COLUMN1 = source.COLUMN1)
WHEN MATCHED THEN UPDATE SET taeged.COLUMN2 = source.COLUMN2
WHEN NOT MATCHED THEN
INSERT /** (target.COLUMN1, target.COLUMN2, target.COLUMN3) **/
VALUES (source.COLUMN1, source.COLUMN2, source.COLUMN3)
;

MSN 檔案傳輸掃毒設定

MSN 檔案傳輸掃毒設定
Antivir

MSN > 工具 > 選項 > 檔案傳輸 > 勾選「使用下列軟體進行病毒掃瞄(V)」
[瀏覽] AntiVir 安裝資料夾裡的avscan.exe
並在後面加上參數" /GUIMODE=3 /PATH=%FILE%" (包含空格，不包含引號)

完成後應該像這樣
"C:\Program Files\AntiVir PersonalEdition Classic\avscan.exe" /GUIMODE=3 /PATH=%FILE%

GUIMODE=3 完全不會跳出視窗
GUIMODE=2 跳出簡易檢視視窗
GUIMODE=1 跳出完整檢視視窗

Kaspersky

MSN > 工具 > 選項 > 檔案傳輸 > 勾選「使用下列軟體進行病毒掃瞄(V)」
[瀏覽] 找到安裝 KIS/KAV 的目錄
- KAV 6 "C:\Program Files\Kaspersky Lab\Kaspersky Anti-Virus 6.0\avp.exe"
- KIS 6 "C:\Program Files\Kaspersky Lab\Kaspersky Internet Security 6.0\avp.exe"
- KAV 7 "C:\Program Files\Kaspersky Lab\Kaspersky Anti-Virus 7.0\avp.exe"
- KIS 7 "C:\Program Files\Kaspersky Lab\Kaspersky Internet Security 7.0\avp.exe"
並在後面加上參數" scan %1" (包含空格，不包含引號)

完成後應該像這樣
"C:\Program Files\Kaspersky Lab\Kaspersky Internet Security 6.0\avp.exe" scan %1
Kaspersky 7.0則路徑為 Kaspersky Anti-Virus 7.0或Kaspersky Internet Security 7.0

NOD32　　(自動關閉視窗)

MSN > 工具 > 選項 > 檔案傳輸 > 勾選「使用下列軟體進行病毒掃瞄(V)」
[瀏覽] NOD32 安裝資料夾裡的nod32.exe
並在後面加上參數" /quit" (包含空格，不包含引號)

完成後應該像這樣
C:\Program Files\ESET\nod32.exe /quit

Norton AntiVirus

MSN > 工具 > 選項 > 檔案傳輸 > 勾選「使用下列軟體進行病毒掃瞄(V)」
[瀏覽] Norton AntiVirus 安裝資料夾裡的ccIMScn.exe

完成後應該像這樣
C:\Program Files\Norton SystemWorks\Norton AntiVirus\ccIMScn.exe

Norton AntiVirus 2003/2004/2005 需先進行 LiveUpdate 。

Avast

MSN > 工具 > 選項 > 檔案傳輸 > 勾選「使用下列軟體進行病毒掃瞄(V)」
[瀏覽] Avast 安裝資料夾裡的ashQuick.exe

完成後應該像這樣
C:\Program Files\Alwil Software\Avast4\ashQuick.exe

Key Code Reference Table

Key Code Reference Table

Key Pressed	Javascript Key Code
backspace	8
tab	9
enter	13
shift	16
ctrl	17
alt	18
pause/break	19
caps lock	20
escape	27
page up	33
page down	34
end	35
home	36
left arrow	37
up arrow	38
right arrow	39
down arrow	40
insert	45
delete	46
0	48
1	49
2	50
3	51
4	52
5	53
6	54
7	55
8	56
9	57
a	65
b	66
c	67
d	68
e	69
f	70
g	71
h	72
i	73
j	74
k	75
l	76
m	77
n	78
o	79
p	80
q	81
r	82
s	83
t	84
u	85
v	86
w	87
x	88
y	89
z	90
left window key	91
right window key	92
select key	93
numpad 0	96
numpad 1	97
numpad 2	98
numpad 3	99
numpad 4	100
numpad 5	101
numpad 6	102
numpad 7	103
numpad 8	104
numpad 9	105
multiply	106
add	107
subtract	109
decimal point	110
divide	111
f1	112
f2	113
f3	114
f4	115
f5	116
f6	117
f7	118
f8	119
f9	120
f10	121
f11	122
f12	123
num lock	144
scroll lock	145
semi-colon	186
equal sign	187
comma	188
dash	189
period	190
forward slash	191
grave accent	192
open bracket	219
back slash	220
close braket	221
single quote	222

Oracle 取消系統備份

alter system set db_recovery_file_dest= '' scope=spfile;

[轉] Oracle 課堂筆記分享: synonyms 與 privileges

1.login user: system
(1) --建立一個user名稱test01(CREATE USER test01)密碼預設為test01(語法:IDENTIFIED BY test01)
CREATE USER TEST01 IDENTIFIED BY test01 ;
(2) --賦予test01可以連接資料庫的權限
GRANT CREATE SESSION TO test01;
(3) --建立一個user test02,且賦予連線權限
CREATE USER TEST02 IDENTIFIED BY test02 ;
GRANT CREATE SESSION TO test02;

2.login user: hr (練習建立public synonyms與權限)
(1) --將hr下面的table:departments建立成為公用synonyms:dep_p
create public synonym dep_p for hr.departments;
(2) --賦予user:test01可以讀取(select)hr裡面的table:departments的權限
grant select on departments to test01;

3.login user: test01 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(以下語法執行若不成功表示上述範例1與2的設定有問題)
select * from dep_p ;

4.login user: test02 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(以下語法執行會出現錯誤ORA-00942: 表格或視觀表不存在
--,因為雖然dep_p為public,但是在範例2-(2)中沒有設定test02的讀取權限)
select * from dep_p ;

5.login user: hr (練習建立權限)
(1) --賦予user:test02可以讀取(select)hr裡面的table:departments的權限
grant select on departments to test02;

6.login user: test02 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(以下語法執行若不成功表示上述範例5的設定有問題)
select * from dep_p ;

7.login user: hr (練習with grant option)
(1) --先將之前建立的權限移除
revoke select on departments from test02;
revoke select on departments from test01;
(2) --賦予test01有with grant option的權限
grant select on departments to test01
with grant option;

(2) --賦予user:test01可以讀取(select)hr裡面的table:departments的權限
grant select on departments to test01;

8.login user: test01 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(以下語法執行若不成功表示上述範例7的設定有問題)
select * from dep_p ;

9.login user: test02 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(應該會無法讀取才對)
select * from dep_p ;

10.login user: test01 (練習with grant option)
(1) --練習由user:test01去指定賦予user:test02讀取user:hr的table:departments的權限
grant select on hr.departments to test02;

11.login user: test02 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(應該會可以讀取才對)
select * from dep_p ;

12.login user: hr (練習移除with grant option)
(1) --直接由hr撤銷test01對department的select權限
revoke select on departments from test01;

13.login user: test01 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(應該會無法讀取才對,因範例12已經撤銷權限)
select * from dep_p ;

14.login user: test02 (測試是否能讀取public synonyms)
(1) --測試是否能讀取公用synonyms(應該會無法讀取才對,因為範例12已經撤銷父層權限)
select * from dep_p ;

15.login user: hr (練習建立private synonyms與權限)
(1) --將hr下面的table:departments建立成為user:test02私用synonyms:dept
create synonym test02.dept for hr.departments;
(2) --賦予user:test02可以讀取(select)hr裡面的table:departments的權限
grant select on departments to test02;

16.login user: test02 (測試是否能讀取private synonyms)
(1) --測試是否能讀取私用synonyms(應該會可以讀取才對)
select * from dept ;

java.lang.OutOfMemoryError: Java heap space

JVM Tuning

1.Xms建議等於Xmx
2.Xmn要小於Xmx
3.Heap size 不能決定JVM真正使用記憶體的大小，實際上會更大
Memory usage=Stack Size+Heap Size+Code Size
4.調整Stack size的參數是Xss

Java Virtual Machine Statistics Monitoring Tool

Using the gcutil option

This example attaches to lvmid 21891 and takes 7 samples at 250 millisecond intervals and displays the output as specified by the -gcutil option.

jstat -gcutil 21891 250 7
S0 S1 E O P YGC YGCT FGC FGCT GCT
12.44 0.00 27.20 9.49 96.70 78 0.176 5 0.495 0.672
12.44 0.00 62.16 9.49 96.70 78 0.176 5 0.495 0.672
12.44 0.00 83.97 9.49 96.70 78 0.176 5 0.495 0.672
0.00 7.74 0.00 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 23.37 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 43.82 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 58.11 9.51 96.71 79 0.177 5 0.495 0.673

Java內存洩露

temp

update OSCOUNTRYKIT l
set L.OCKEXPIRE_DATE =(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = l.OCKEFFECTIVE_DATE
)
where exists(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = L.OCKEFFECTIVE_DATE
)

oracle 更新截止日

update PRICELIST l
set L.PLEXPIRE_DATE =(
SELECT MAX(P.PLEFFECTIVE_DATE) - 1

FROM PRICELIST P
WHERE NVL(P.PLRO,'PLRO') = NVL(L.PLRO,'PLRO') AND
NVL(P.PLNS,'PLNS') = NVL(L.PLNS,'PLNS') AND
NVL(P.PLODM,'PLODM') = NVL(L.PLODM,'PLODM') AND
NVL(P.PLBRAND,'NULL') = NVL(L.PLBRAND,'NULL') AND
NVL(P.PLMRK_NAME,'NULL') = NVL(L.PLMRK_NAME,'NULL') AND
NVL(P.PLBOM_NAME,'NULL') = NVL(L.PLBOM_NAME,'NULL') AND
NVL(P.PLODD,'PLODD') = NVL(L.PLODD,'PLODD') AND
NVL(P.PLOS,'PLOS') = NVL(L.PLOS,'PLOS') AND
NVL(P.PLCPU,'PLCPU') = NVL(L.PLCPU,'PLCPU') AND
NVL(P.PLLCD,'PLLCD') = NVL(L.PLLCD,'PLLCD') AND
NVL(P.PLBEZEL,'PLBEZEL') = NVL(L.PLBEZEL,'PLBEZEL') AND
NVL(P.PLCHASSIS,'PLCHASSIS') = NVL(L.PLCHASSIS,'PLCHASSIS') AND
NVL(P.PLMS_SPECIAL,'PLMS_SPECIAL') = NVL(L.PLMS_SPECIAL,'PLMS_SPECIAL') AND
NVL(P.PLITEMNAME,'PLITEMNAME') = NVL(L.PLITEMNAME,'PLITEMNAME') AND
p.PLEXPIRE_DATE = l.PLEXPIRE_DATE
--and P.PLEFFECTIVE_DATE = l.PLEFFECTIVE_DATE
GROUP BY p.PLRO, p.PLNS, p.PLODM, p.PLBRAND, p.PLMRK_NAME, p.PLBOM_NAME, p.PLOS, p.PLCPU, p.PLLCD, p.PLODD,
p.PLBEZEL, p.PLCHASSIS, p.PLMS_SPECIAL, p.PLITEMNAME, p.PLEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.PLEFFECTIVE_DATE) = l.PLEFFECTIVE_DATE

)
where exists(
SELECT MAX(P.PLEFFECTIVE_DATE) - 1

FROM PRICELIST P
WHERE NVL(P.PLRO,'PLRO') = NVL(L.PLRO,'PLRO') AND
NVL(P.PLNS,'PLNS') = NVL(L.PLNS,'PLNS') AND
NVL(P.PLODM,'PLODM') = NVL(L.PLODM,'PLODM') AND
NVL(P.PLBRAND,'NULL') = NVL(L.PLBRAND,'NULL') AND
NVL(P.PLMRK_NAME,'NULL') = NVL(L.PLMRK_NAME,'NULL') AND
NVL(P.PLBOM_NAME,'NULL') = NVL(L.PLBOM_NAME,'NULL') AND
NVL(P.PLODD,'PLODD') = NVL(L.PLODD,'PLODD') AND
NVL(P.PLOS,'PLOS') = NVL(L.PLOS,'PLOS') AND
NVL(P.PLCPU,'PLCPU') = NVL(L.PLCPU,'PLCPU') AND
NVL(P.PLLCD,'PLLCD') = NVL(L.PLLCD,'PLLCD') AND
NVL(P.PLBEZEL,'PLBEZEL') = NVL(L.PLBEZEL,'PLBEZEL') AND
NVL(P.PLCHASSIS,'PLCHASSIS') = NVL(L.PLCHASSIS,'PLCHASSIS') AND
NVL(P.PLMS_SPECIAL,'PLMS_SPECIAL') = NVL(L.PLMS_SPECIAL,'PLMS_SPECIAL') AND
NVL(P.PLITEMNAME,'PLITEMNAME') = NVL(L.PLITEMNAME,'PLITEMNAME') AND
p.PLEXPIRE_DATE = l.PLEXPIRE_DATE
--and P.PLEFFECTIVE_DATE = l.PLEFFECTIVE_DATE
GROUP BY p.PLRO, p.PLNS, p.PLODM, p.PLBRAND, p.PLMRK_NAME, p.PLBOM_NAME, p.PLOS, p.PLCPU, p.PLLCD, p.PLODD,
p.PLBEZEL, p.PLCHASSIS, p.PLMS_SPECIAL, p.PLITEMNAME, p.PLEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.PLEFFECTIVE_DATE) = l.PLEFFECTIVE_DATE

)

Oracle FlashBack

alter table CM_CODE enable row movement;
flashback table CM_CODE to timestamp to_timestamp('2009-05-18 23:10:00','yyyy-mm-dd hh24:mi:ss');

Oracle 英文大小寫function

upper() 轉大寫

lower() 轉小寫

INITCAP() 首字大寫
PS:以底線分隔的字，首字也是大寫

example

SELECT INITCAP ('JAMES_jan') FROM dual;
---------
James_Jan
1 row selected.

java的四捨五入

import java.math.BigDecimal;

public class jamesMath {

private static final int DEF_DIV_SCALE = 10;

public static double round(double v,int scale){

if(scale<0){

throw new IllegalArgumentException(

"The scale must be a positive integer or zero");

}

BigDecimal b = new BigDecimal(Double.toString(v));

BigDecimal one = new BigDecimal("1");

return b.divide(one,scale,BigDecimal.ROUND_HALF_UP).doubleValue();

}

/**

* 兩個Double數相加

* @param v1

* @param v2

* @return double

*/

public static double add(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.add(b2).doubleValue();

}

/**

* 兩個Double數相減

* @param v1

* @param v2

* @return double

*/

public static double sub(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.subtract(b2).doubleValue();

}

/**

* 兩個Double數相乘

* @param v1

* @param v2

* @return double

*/

public static double mul(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.multiply(b2).doubleValue();

}

/**

* 兩個Double數相除

* @param v1

* @param v2

* @return double

*/

public static double div(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.divide(b2,DEF_DIV_SCALE,BigDecimal.ROUND_HALF_UP).doubleValue();

}

/**

* 兩個Double數相除，並保留scale位小數

* @param v1

* @param v2

* @param scale

* @return double

*/

public static double div(Double v1,Double v2,int scale){

if(scale<0){

throw new IllegalArgumentException(

"The scale must be a positive integer or zero");

}

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.divide(b2,scale,BigDecimal.ROUND_HALF_UP).doubleValue();

}

}