SQL 归来
1. PL/SQL 转义
- select order#, ……… from ****
- select col1 from A where col2 like '%\_keywors%' escape '\';
- update A set url = 'homepage.aspx?code=32' || '&' || 'active=0' --&
update A set url = 'homepage.aspx?code=32' || chr(38) || 'active=0' --&
2. EXTRACT
| 日付の要素 | element 引数 | |
| 年 | YEAR | |
| 月 | MONTH | |
| 日 | DAY | |
| 時 | HOUR | |
| 分 | MINUTE | |
| 秒 | SECOND | |
| 時 | タイムゾーン 情報あり |
TIMEZONE_HOUR |
| 分 | TIMEZONE_MINUTE | |
| タイムゾーン名称 | TIMEZONE_REGION | |
| タイムゾーン略称 | TIMEZONE_ABBR | |
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL
3. TRUNC
SELECT SYSDATE S1,
TRUNC(SYSDATE) S2, --返回当前日期,无时分秒
TRUNC(SYSDATE,'YEAR') YEAR, --返回当前年的1月1日,无时分秒
TRUNC(SYSDATE,'MONTH') MONTH , --返回当前月的1日,无时分秒
TRUNC(SYSDATE,'DAY') DAY --返回当前星期的星期天,无时分秒
FROM DUAL
| S1 | S2 | YEAR | MONTH | DAY |
| 2015/3/11 11:30 | 2015/3/11 | 2015/1/1 | 2015/3/1 | 2015/3/8 |
4. 调用存储过程
DECLARE
MY_STR_DATE VARCHAR2(100);
BEGIN
MY_STR_DATE := 'ASDFASDFASDF';
DBMS_OUTPUT.put_line(MY_STR_DATE);
END;
5. Exists -- 替换 minus 和 intersect
select * from (select 1 col1, 'a' col2 from dual union all select 2 col1, 'b' col2 from dual) t where not exists (select 'x' from (select 1 col1, 'a' col2 from dual union all select 2 col1, 'b' col2 from dual) w where t.col1 = w.col1 and t.col2 = w.col2)
另外： 如果select的内容都出自一个表的时候，比如上面的t和w有不同的列，而最终结果只选择t表中的列，最好用Exists
6. 全角/半角转换（link）
SELECT
TO_MULTI_BYTE(SYS.UTL_I18N.TRANSLITERATE('ｱAあ1１','hwkatakana_fwkatakana')) as full_,
TO_single_BYTE(SYS.UTL_I18N.TRANSLITERATE('アＡあ１１','kana_hwkatakana')) as half
FROM DUAL
| FULL_ | HALF |
| アＡあ１１ | ｱAｱ11 |
7. 用A表的数据更新B表的数据
方法1：
update a_code s set s.name = (select e.name from a_data e where e.code = s.code and rownum < 2) where s.code in (select code from a_data);
方法2：
merge into a_code t using a_data d on (t.code = d.code) -- 带括号 when matched then update set t.name = d.name;
| CODE | NAME |
| 1 | 111111 |
| 2 | 222222 |
| 3 | 333333 |
| 4 | mmmm |
| 5 | wwwww |
| 6 | ttttt |
8. (NOT) IN / EXISTS
select code from t where code IN (1,2) => select code from t where code = 1 or code = 2
select code from t where code NOT IN (1,2,null) => select code from t where code <> 1 and code <> 2 and code <> null
code <> null 的值为unknown，所以NOT IN (1,2,null)的返回的结果集为空，如果用not in就需要把含有null的过滤掉
或则改用Exists
select code from t where NOT EXISTS (select 1 from x where t.code = x.code)
9. coalesce / NVL
coalesce (val1, val2, val3) :
coalesce 可以有多个参数，当val1的值为null的时候，返回val2，如果val2也为空，则返回val3，最后一个参数不能为null即可；如果最后一个参数为null，则返回错误。
NVL(val1, val2) :
NVL只有2个参数，当val1为null的时候，直接返回val2，即使val2为null也不出错；如果val1和val2的数据类型不同，oracle会进行隐式转换，如果转换失败，则返回ERROR；
NVL会同时计算val1和val2的值，对于coalesce只是在val1为null的时候，才会去计算val2的值
另：NVL2(val1, 'completed', 'n/a')
当val1的值为null的时候，返回'n/a'，否则返回'completed'
10. 游标更新 where current of
cursor c_f is select a,b from f where length(b) = 5 for update; open c_f; loop fetch c_f into v_a, v_b; exit when c_f%notfound; update f set a=v_a*v_a where current of c_f; end loop;
11. Join 写法
select id_num, txt_num, id_lang, txt_lang, txt_trans from numbers_en join translations using (id_num) left join lang using (id_lang);
join translations using id_num 等同于 join translations on numbers_en.id_num = translation.id_num
12. Function VS Procedure: from LINK
13. 赋权限
GRANT SELECT, UPDATE ON "schema1"."table1" TO "schema2" ;
14. Pipelined Table Functions: LINK
Data is said to be pipelined if it is consumed by a consumer (transformation) as soon as the producer (transformation) produces it, without being staged in tables or a cache before being input to the next transformation.
Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.
create function gen_numbers(n in number default null) return array PIPELINED as begin for i in 1 .. nvl(n,999999999) loop pipe row(i); end loop; return; end; / ----------------------------------------------------------- /* select * from TABLE(gen_numbers(3)); COLUMN_VALUE ------------ 1 2 3 OR select * from ( select * from (select * from table(gen_numbers(49))) order by dbms_random.random ) where rownum <= 6 / COLUMN_VALUE ------------ 47 42 40 15 48 23 */
15. Foreign Key: On delete -> No Action / Cascade / Set null
No Action: prevents deleting a parent when there are children
Cascade: when a referenced parent table row is removed all the child are removed automatically
Set null: set col to null in child when parent table row is removed
16. unicode TO string
'基準日: ' => UNISTR('\57fa\6e96\65e5\003a\3000')
http://unicodelookup.com/ unicode 编码查询
string TO unicode ？？？
17. 频繁插入删除操作，可能导致高水位线，重新分析下表，可能使对表引用的查询更快一点
analyze table XXX compute statistics
18. LEFT JOIN :)
1. select count from left join B on (A.id = B.id and B.col = 'xxx')
where 1 =1
2. select count(*) from left join B on (A.id = B.id)
where 1 =1 AND B.col = 'xxx'
1,2 的结果可能会不同，
19. running total
Table XXX:
select tag, insdate, amount, nvl(lag(amount) over(partition by tag order by insdate), 0) lag_total, nvl(lead(amount) over(partition by tag order by insdate), 0)
from xxx
20. check English/digital characters only
[1]
select 1 from dual where REGEXP_LIKE('ｓｄ巣', '[^ -~]', 'i') union all
select 1 from dual where REGEXP_LIKE('abc', '[^ -~]', 'i')
[2]
select translate( 'ｓｄ巣abcd', chr(0) || 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789.,- ', chr(0) ) from dual
21. NOCOPY
TYPE connection IS RECORD ( host VARCHAR2(255), -- Host name of SMTP server port PLS_INTEGER, -- Port number of SMTP server tx_timeout PLS_INTEGER, -- Transfer time-out (in seconds) private_tcp_con utl_tcp.connection, -- For internal use only private_state PLS_INTEGER -- For internal use only ); FUNCTION helo(c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN reply;
NOCOPY is a IN OUT COPY Which is used avoid the overhad of copying IN OUT Parameter Values.
If IN OUT parameter returns huge records we can specify the NOCOPY to copy the values.
The PLSQL Engine first makes a copy of the record and then during program executing makes a changes to that copy.
22. bitwise operator:BITAND
oracle中只有BITAND函数;
位或：
BITOR(x,y) = (x + y) - BITAND(x, y);
异或：
BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;
应用：赋权，权限判断的时候可以用这个（判断一个角色是否有多个权限）
23. LISTAGG
select listagg(code, ';') within group (order by code) from table_codes
结果：001; 002; 003
24.
