ORACLE对字母和数字混合的数据排序
主要是通过replace和translate来实现
SQL> create view v_tt
2 as
3 select ename||' '||deptno as data
4 from emp;
View created.
SQL> select * from v_tt;
DATA
---------------------------------------------------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
DATA
---------------------------------------------------
JAMES 30
FORD 20
MILLER 10
14 rows selected.
//by deptno
SQL> select * from v_tt
2 order by replace(data,replace(translate(data,'0123456789','##########'),'#'
,''),'');
DATA
---------------------------------------------------
CLARK 10
KING 10
MILLER 10
JONES 20
FORD 20
ADAMS 20
SMITH 20
SCOTT 20
WARD 30
TURNER 30
ALLEN 30
DATA
---------------------------------------------------
JAMES 30
BLAKE 30
MARTIN 30
14 rows selected.
//by name
SQL> select * from v_tt
2 order by data,replace(translate(data,'0123456789','##########'),'#','');
DATA
---------------------------------------------------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
DATA
---------------------------------------------------
SMITH 20
TURNER 30
WARD 30
14 rows selected.
相关文档：
select r.rollid as rollid from zh1_rool r where r.date_p >=to_date('2009-11-26 23:59:59','yyyy-mm-dd hh24:mi:ss') and rollid not in
(select t.lot_number as rollid
from inv.mtl_onhand_quantities_detail t
left join mtl_system_items_b mi
on t.inventory_item_id = mi.inventory_ ......
Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。
OTHERS处理器应该是异常处理块中的最后的异常处理器，因为它是用来捕获除了别的异常处理器处理以外的所有的Oracle异常，所以在程序的最外层使用一个OTHERS处理器的话， ......
oracle 用户密码和资源管理
oracle中使用profile对用户密码和资源进行管理。
SQL> select * from dba_profiles order by resource_name;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------- ......
SQL> select sal,comm from emp
2 where sal<2000;
SAL COMM
---------- ----------
800
1600 300
1250 500
1250 1400
1500 0
1100
950
1300
8 rows selected.
SQL> select sal as salary,comm as ......
