而且arraysize也要设置恰当：
SQL> show arraysize
arraysize 15
这个参数是指定一次网络传输的数据行数，如果设置较大，则数据只要传输少数次就会完成事务；相反设置过大，也会加大
带宽的争用；
如果想使用一个列的别名来执行条件或者其它查询条件的话：
SQL> select sal as salary from emp where salary<4000;
select sal as salary from emp where salary<4000
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
而只能使用内嵌视图：
SQL> select * from (select sal as 工资 from emp) where 工资<3000;
工资
----------
2850
2450
2975
1250
我们来看看这个语句的处理过程：
首先我们知道select语句的处理过程是：from -->where-->select
所以前一条语句不能处理就很好的得到了解释： from 从emp表中，where的salary条件比较；正是这里出了问题,from处理后的emp中并没有
salary列；而后一条语句就不一样了，from处理了内嵌的select视图，这个临时视图列名就是salary，然后where就可以进行基于salary列的操作了。
case语句的使用：
select ename,sal,case sal
when sal<=1000 then 'low'
when sal>1000 and sal<=3000 then 'mid'
when sal>3000 then 'high'
else 'others'
end [as status] ### 可以给case列取别名；
from emp;
回忆rownum机制：
oracle再处理返回行限制时，有别于其它数据库：
比如：select * from emp where rownum>5;
我们都知道这个条件是行不通的，oracle内部是这样处理rownum伪列的：
查询返回“第一行”，这个行的rownum伪列值是1，1>5=false；
接着返回“第二行”，这个行的rownum伪列值再次从1开始，1>5=false；
.
.
.
就这样oracle直到返回完所有表中行记录，也不会找到满足条件的；
SQL> select * from scott.emp where rownum>3;
no rows selected
如果我们要对一个表进行“抽样”，那么oracle中我们应该怎么做呢：
select * from (select ename,sal,deptno from emp order by dbms_random.value())
where rownum<=5;
比较：
select * from emp where rownum<=5 order by dbms_random.value();
比较说明：前一种是正确的使用方法，而后者则不能对全表进行随机抽样，前者的执行机制是from 生成临时随机排序表，再执行where判断；
后者则不是了，它是from从emp中，where抽出其中的五条，再对这固定的五条进行随机排序；
从上面的两条语句中我们得出结论：在编写sql语句时，一定要清楚的知道oracle sql语句的执行顺序，这和C语言java等编程语言的算术
运算符一样，是有运算优先级的，比如：单目运算符总是优先于双目运算符。
下面给出常用运算符运算顺序：
from->where->group by->having->select->order by
以上过程：from定位查询目标-->where过滤结果集-->对结果集进行分组-->对分组后结果进行过滤-->返回结果集-->排序后显示出;
扩展学习： dbms_random.value(n,m)，就是返回n到m
之间随机的一个数，缺省是0到1之间的；
dbms_random.string('A',10)
'A'是大小写混合的
'L'是小写
'U'是大写
10代表字符串长度；
还有随机产生日期：
select to_char(sysdate,'J') from dual;
TO_CHAR
-------
2455282
select to_char(to_date('2010-5-21','yyyy-mm-dd'),'J') from dual;
TO_CHAR
-------
2455338
select to_date(trunc(dbms_random.value(2455282,2455338)),'J') from dual;
这样就产生了从当前时间到2010年5月21日之间的日期随机值。
DBMS_RANDOM包：
PACKAGE dbms_random AS
------------
-- OVERVIEW
--
-- This package should be installed as SYS. It generates a sequence of
-- random 38-digit Oracle numbers. The expected length of the sequence
-- is about power(10,28), which is hopefully long enough.
--
--------
-- USAGE
--
-- This is a random number generator. Do not use for cryptography.
-- For more options the cryptographic toolkit should be used.
--
-- By default, the package is initialized with the current user
-- name, current time down to the second, and the current session.
--
-- If this package is seeded twice with the same seed, then accessed
-- in the same way, it will produce the same results in both cases.
--
--------
-- EXAMPLES
--
-- To initialize or reset the generator, call the seed procedure as in:
-- execute dbms_random.seed(12345678);
-- or
-- execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
-- To get the random number, simply call the function, e.g.
-- my_random_number BINARY_INTEGER;
-- my_random_number := dbms_random.random;
-- or
-- my_random_real NUMBER;
-- my_random_real := dbms_random.value;
-- To use in SQL statements:
-- select dbms_random.value from dual;
-- insert into a values (dbms_random.value);
-- variable x NUMBER;
-- execute :x := dbms_random.value;
-- update a set a2=a2+1 where a1 < :x;
-- Seed with a binary integer
PROCEDURE seed(val IN BINARY_INTEGER);
PRAGMA restrict_references (seed, WNDS);
-- Seed with a string (up to length 2000)
PROCEDURE seed(val IN VARCHAR2);
PRAGMA restrict_references (seed, WNDS);
-- Get a random 38-digit precision number, 0.0 <= value < 1.0
FUNCTION value RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random Oracle number x, low <= x < high
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random number from a normal distribution
FUNCTION normal RETURN NUMBER;
PRAGMA restrict_references (normal, WNDS);
-- get a random string
FUNCTION string (opt char, len NUMBER)
/* "opt" specifies that the returned string may contain:
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
*/
RETURN VARCHAR2; -- string of characters
PRAGMA restrict_references (string, WNDS);
-- Obsolete, just calls seed(val)
PROCEDURE initialize(val IN BINARY_INTEGER);
PRAGMA restrict_references (initialize, WNDS);
-- Obsolete, get integer in ( -power(2,31) <= random < power(2,31) )
FUNCTION random RETURN BINARY_INTEGER;
PRAGMA restrict_references (random, WNDS);
-- Obsolete, does nothing
PROCEDURE terminate;
TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
END dbms_random;
PACKAGE BODY dbms_random AS
mem num_array; -- big internal state hidden from the user
counter BINARY_INTEGER := 55;-- counter through the results
saved_norm NUMBER := NULL; -- unused random normally distributed value
need_init BOOLEAN := TRUE; -- do we still need to initialize
-- Seed the random number generator with a binary_integer
PROCEDURE seed(val IN BINARY_INTEGER) IS
BEGIN
seed(TO_CHAR(val));
END seed;
-- Seed the random number generator with a string.
PROCEDURE seed(val IN VARCHAR2) IS
junk VARCHAR2(2000);
piece VARCHAR2(20);
randval NUMBER;
mytemp NUMBER;
j BINARY_INTEGER;
BEGIN
need_init := FALSE;
saved_norm := NULL;
counter := 0;
junk := TO_SINGLE_BYTE(val);
FOR i IN 0..54 LOOP
piece := SUBSTR(junk,1,19);
randval := 0;
j := 1;
-- convert 19 characters to a 38-digit number
FOR j IN 1..19 LOOP
randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
END LOOP;
-- try to avoid lots of zeros
randval := randval*1e-38+i*.01020304050607080910111213141516171819;
mem(i) := randval - TRUNC(randval);
-- we've handled these first 19 characters already; move on
junk := SUBSTR(junk,20);
END LOOP;
randval := mem(54);
FOR j IN 0..10 LOOP
FOR i IN 0..54 LOOP
-- barrelshift mem(i-1) by 24 digits
randval := randval * 1e24;
mytemp := TRUNC(randval);
randval := (randval - mytemp) + (mytemp * 1e-38);
-- add it to mem(i)
randval := mem(i)+randval;
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
-- record the result
mem(i) := randval;
END LOOP;
END LOOP;
END seed;
-- give values to the user
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION value RETURN NUMBER IS
randval NUMBER;
BEGIN
counter := counter + 1;
IF counter >= 55 THEN
-- initialize if needed
IF (need_init = TRUE) THEN
seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
USER || USERENV('SESSIONID'));
ELSE
-- need to generate 55 more results
FOR i IN 0..30 LOOP
randval := mem(i+24) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
FOR i IN 31..54 LOOP
randval := mem(i-31) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
END IF;
counter := 0;
END IF;
RETURN mem(counter);
END value;
-- Random 38-digit number between LOW and HIGH.
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
BEGIN
RETURN (value*(high-low))+low;
END value;
-- Random numbers in a normal distribution.
-- Pilfered from Knuth volume 2.
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
v1 NUMBER;
v2 NUMBER;
r2 NUMBER;
fac NUMBER;
BEGIN
IF saved_norm is not NULL THEN -- saved from last time
v1 := saved_norm; -- to be returned this time
saved_norm := NULL;
ELSE
r2 := 2;
-- Find two independent uniform. variables
WHILE r2 > 1 OR r2 = 0 LOOP
v1 := value();
v1 := v1 + v1 - 1;
v2 := value();
v2 := v2 + v2 - 1;
r2 := v1*v1 + v2*v2; -- r2 is radius
END LOOP; -- 0 < r2 <= 1: in unit circle
/* Now derive two independent normally-distributed variables */
fac := sqrt(-2*ln(r2)/r2);
v1 := v1*fac; -- to be returned this time
saved_norm := v2*fac; -- to be saved for next time
END IF;
RETURN v1;
END normal;
-- Random string. Pilfered from Chris Ellis.
FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2 is -- string of characters
optx char (1) := lower(opt);
rng NUMBER;
n BINARY_INTEGER;
ccs VARCHAR2 (128); -- candidate character subset
xstr VARCHAR2 (4000) := NULL;
BEGIN
IF ptx = 'u' THEN -- upper case alpha characters only
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26;
ELSIF ptx = 'l' THEN -- lower case alpha characters only
ccs := 'abcdefghijklmnopqrstuvwxyz';
rng := 26;
ELSIF ptx = 'a' THEN -- alpha characters only (mixed case)
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'abcdefghijklmnopqrstuvwxyz';
rng := 52;
ELSIF ptx = 'x' THEN -- any alpha-numeric characters (upper)
ccs := '0123456789' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 36;
ELSIF ptx = 'p' THEN -- any printable char (ASCII subset)
ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' ||
'abcdefghijklmnopqrstuvwxyz' || '{|}~' ;
rng := 95;
ELSE
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,4000) LOOP
/* Get random integer within specified range */
n := TRUNC(rng * value) + 1;
/* Append character to string */
xstr := xstr || SUBSTR(ccs,n,1);
END LOOP;
RETURN xstr;
END string;
-- For compatibility with 8.1
PROCEDURE initialize(val IN BINARY_INTEGER) IS
BEGIN
seed(to_char(val));
END initialize;
-- For compatibility with 8.1
-- Random binary_integer, -power(2,31) <= Random < power(2,31)
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION random RETURN BINARY_INTEGER IS
BEGIN
RETURN TRUNC(Value*4294967296)-2147483648;
END random;
-- For compatibility with 8.1
PROCEDURE terminate IS
BEGIN
NULL;
END terminate;
END dbms_random;
order by:
select ename,job,sal from emp where deptno=10 order by sal desc/asc;
解析过程： from-->where过滤-->select返回结果集-->order by 排序结果集并显示出;
多列排序：
select ename,job,sal from emp order by sal desc,ename asc;
order by 函数中优先顺序是从左到右，上面语句就是先按sal降序排列，如果sal相同再按ename降序排序；
子串排序：
select * from emp order by substr(ename,length(ename)-3,2);
中文排序特性：
SQL> select parameter,value from v$nls_valid_values where ISDEPRECATED='FALSE' and parameter='SORT';
SORT SCHINESE_PINYIN_M
SORT SCHINESE_RADICAL_M
SORT SCHINESE_STROKE_M
找到这几行，schinese(simplified chinese)简体中文的，第一个是拼音（很明显），radical是按照偏旁部首来的（例如：“他”和“信”是“亻”的，所以排序在一起），stroke是按照笔画来的；
SQL> show parameter sort
NAME TYPE VALUE
------------------------------------ ----------- ----------
nls_sort string
SQL> create table t(x nvarchar2(1));
Table created.
SQL> alter session set nls_sort=SCHINESE_PINYIN_M;
Session altered.
SQL> select * from t order by x;
X
--
啊
的
李
哦
SQL> alter session set nls_sort=SCHINESE_RADICAL_M
2 ;
Session altered.
SQL> select * from t order by x;
X
--
哦
啊
李
的
SQL> alter session set nls_sort=SCHINESE_STROKE_M;
Session altered.
SQL> select * from t order by x;
X
--
李
的
哦
啊
对字母数字混合排序：
create view test
as
select ename||' '||sal as data from emp;
那么当我们在查询test视图时怎样基于ename和sal列进行排序呢，还好ename全为字母，而sal全为数字，所以我们可以通过函数进行过滤：
select data from test order by replace(data,replace(translate(data,'0123456789','##########'),'#',''),'');
translate函数用法：translate(c1,c2,c3),先将c2中出现的所有字符在c1中标出，然后用c3中对应的字符去替换c1中已经被标记的字符；
SQL> select translate('123001aabc21dd','aab2','####') from dual;
TRANSLATE('123
--------------
1#3001###c#1dd
如果c1,c2,c3中任何有一个为null，则返回null：
SQL> select translate('123001aabc21dd','aab2','###') from dual;
TRANSLATE('1
------------
13001###c1dd
如果c3的小于c2长度，则将c1中对应标记的删除；
排序空值：
select * from emp order by comm nulls last/first;
这样就能让null列排序在前面还是后面；
select * from emp order by comm desc nulls last;
order by 中使用case 表达式，完成条件排序；
select ename,sal,job,comm from emp order by case when job='SALESMAN' then comm else sal end;
上面的排序方式是：如果工作是售货员的话就按照comm排序，否则按照工资排序；
异曲同工的还有：
select ename,sal,job,comm,case
when job='SALESMAN' then comm
else sal
end
from emp order by 5;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23524877/viewspace-631588/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23524877/viewspace-631588/
扫一扫
2102
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
