簡單實用的sql小技巧(第二篇)

ASCII和CHR
在平時的工作中，可能會在sql或者pl/sql塊中嵌入特殊字元，比如空格，回車之類。這個時候可以使用ascii和chr來做字元和ascii碼的轉換。
比如我們想得到字母a對應的ascii碼,可以使用ascii來實現。
SQL> select  ascii('a') from dual;
ASCII('A')
----------
        97
如果反推，需要根據ascii碼值來得到對應的字元，就可以使用chr
SQL> select chr(97) from dual;
CHR
---
a
關於ascii碼對應的字元，總結的列表如下。對於前32個ascii碼來說，是不可見字元。所以使用描述代替。這部分特殊字元在平時的工作中使用的地方還是比較多的。像回車符，換行符，空格都很常用。
用到的時候可以參考一下。

ascii碼	對應的字元	ascii碼	對應的字元
0	null	64	@
1	start of heading	65	A
2	start of text	66	B
3	end of text	67	C
4	end of transmission	68	D
5	enquiry	69	E
6	acknowledge	70	F
7	bell	71	G
8	backspace	72	H
9	horizontal tab	73	I
10	new line	74	J
11	vertical tab	75	K
12	new page	76	L
13	carriage return	77	M
14	shift out	78	N
15	shift in	79	O
16	data link escape	80	P
17	device control 1	81	Q
18	device control 2	82	R
19	device control 3	83	S
20	device control 4	84	T
21	negative acknowledge	85	U
22	synchronous idle	86	V
23	end of trans. block	87	W
24	cancel	88	X
25	end of medium	89	Y
26	substitute	90	Z
27	escape	91	[
28	file separator	92	\
29	group separator	93	]
30	record separator	94	^
31	unit separator	95	_
32	space	96	`
33	!	97	a
34	"	98	b
35	#	99	c
36	$	100	d
37	%	101	e
38	&	102	f
39	'	103	g
40	(	104	h
41	)	105	i
42	*	106	j
43	+	107	k
44	,	108	l
45	-	109	m
46	.	110	n
47	/	111	o
48	0	112	p
49	1	113	q
50	2	114	r
51	3	115	s
52	4	116	t
53	5	117	u
54	6	118	v
55	7	119	w
56	8	120	x
57	9	121	y
58	:	122	z
59	;	123	{
60	<	124	|
61	=	125	}
62	>	126	~
63	?	127	DEL

DUMP
對於一些特殊字元，有些是不可見字元，如果在客戶端檢視根本判斷不出來。這個時候一旦出現什麼問題，可以根據dump來檢視是否存在特殊字元。舉個簡單的例子。
create table test(id number,name varchar2(100));
insert into test values(1,'this is a test');
insert into test values(2,'this is a test'||chr(10));
commit;
SQL> col dump_name format a100
SQL> col name format a30
SQL> select *from test;  --透過客戶端檢視，根本看不出區別來。一旦出現問題是很難查的。
        ID NAME
---------- ------------------------------
         1 this is a test
         2 this is a test
SQL> select id,dump(name)dump_name from test;  -使用dump來解析，最後的"10“就是不可見字元，是一個換行符。
        ID DUMP_NAME
---------- ----------------------------------------------------------------------------------------------------
         1 Typ=1 Len=14: 116,104,105,115,32,105,115,32,97,32,116,101,115,116
         2 Typ=1 Len=15: 116,104,105,115,32,105,115,32,97,32,116,101,115,116,10

TO_CHAR (DATE)
關於to_char的使用，功能還是很強大的。看似簡單的一個轉換竟然能夠實現很複雜的邏輯。
比如我們先得到當前的時間戳。

SQL> select to_char(sysdate,'yyyy--mm-dd hh24:mi:ss') now_date from dual;
NOW_DATE
------------------------------------------------------------
2014--12-16 17:46:39

得到這個月最後一天的資訊，如果是在複雜的業務邏輯中判斷能省去不少事。
SQL>  select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
------------------
31-DEC-14
檢視今天是這周的第幾天。
SQL> select to_char(sysdate,'d') from dual;
TO_
---
3
檢視今天是星期幾。
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDATE,'DAY')
---------------------------
tuesday
SQL> select to_char(sysdate,'dy') from dual;
TO_CHAR(S
---------
tue
檢視今天是一年中的第幾天
SQL> select to_char(sysdate,'ddd') from dual;
TO_CHAR(S
---------
350
檢視今天是這個月的第幾天
select to_char(sysdate,'dd') from dual;
TO_CHA
------
16
檢視今天是一年中的第幾周
SQL> select to_char(sysdate,'ww') from dual;
TO_CHA
------
50
檢視月份
SQL> select to_char(sysdate,'MM') from dual;
TO_CHA
------
12
select to_char(sysdate,'mm') from dual;
TO_CHA
------
12
今天屬於當月的第幾周。
SQL> select to_char(sysdate,'W') from dual;
TO_
---
3