oracle系統包——dbms_random用法及order by 小結

標籤：

 

dbms_random是一個可以產生隨機數值或者字串的程式包。

這個包有initialize()、seed()、terminate()、value()、normal()、random()、string()等幾個函數，但value()是最常用的，下面依次介紹各個函數的詳細用法：

 

1.dbms_random.value方法

 

第一種：FUNCTION value RETURN NUMBER;

說明：

這種用法沒有參數，會返回一個具有38位精度的數值，範圍從0.0到1.0，但不包括1.0。

樣本：

Sql代碼  

1. BEGIN  
2.   FOR i IN 1 .. 10 LOOP  
3.     dbms_output.put_line(round(dbms_random.value * 100));  
4.   END LOOP;  
5. END;  

結果:

 

第二種：FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;

說明：

value帶有兩個參數，第一個指下限，第二個指上限，將會產生下限到上限之間的數字，但不包含上限。

樣本：

Sql代碼  

1. BEGIN  
2.   FOR i IN 1 .. 10 LOOP  
3.     dbms_output.put_line(trunc(dbms_random.value(1,101)));  
4.   END LOOP;  
5. END;  

 

結果:

2. dbms_random.string 方法

 

FUNCTION string (opt CHAR, len NUMBER) RETURN VARCHAR2;

說明：

某些使用者管理程式可能需要為使用者建立隨機的密碼。使用10G下的dbms_random.string 可以實現這樣的功能。

參數：

opt ：

■ ‘u‘, ‘U‘ - returning string in uppercase alpha characters

■ ‘l‘, ‘L‘ - returning string in lowercase alpha characters

■ ‘a‘, ‘A‘ - returning string in mixed case alpha characters

■ ‘x‘, ‘X‘ - returning string in uppercase alpha-numericcharacters

■ ‘p‘, ‘P‘ - returning string in any printable characters.Otherwise the returning string is in uppercase alphacharacters.

len ：

表示返回的字串長度。

樣本：

Sql代碼  

1. SELECT dbms_random.string(‘P‘,8) FROM dual;  

 

結果:

3. dbms_random.random 方法

 

FUNCTIONrandomRETURN BINARY_INTEGER;

說明：

random返回的是BINARY_INTEGER類型值,產生一個任意大小的隨機數。

樣本：

 

Sql代碼  

1. SELECT dbms_random.random FROM dual;  

 

結果:

備忘：dbms_random.value 和 dbms_random.random 兩者之間有什麼區別？

1、Order By dbms_random.value ，為結果集的每一行計算一個隨機數，dbms_random.value 是結果集的一個列（雖然這個列並不在select list 中），然後根據該列排序，得到的順序自然就是隨機的啦。

2、value返回的是number類型，並且返回的值介於1和0之間，而random返回的是BINARY_INTEGER類型（以二進位形式儲存的數字，據說運算的效率高於number但我沒測試過，但取值範圍肯定小於number，具體限制得查資料了）

 

4. dbms_random.normal方法

 

FUNCTIONnormal RETURN NUMBER;

說明：

NORMAL函數返回服從常態分佈的一組數。此常態分佈標準差為1，期望值為0。這個函數返回的數值中有68%是介於-1與+1之間，95%介於-2與+2之間，99%介於-3與+3之間。

樣本：

Sql代碼  

1. BEGIN  
2.   FOR i IN 1 .. 10 LOOP  
3.     dbms_output.put_line(round(dbms_random.normal));  
4.   END LOOP;  
5. END;  

結果:

5. dbms_random.seed方法

 

PROCEDURE seed(val IN BINARY_INTEGER);或PROCEDURE seed(val IN VARCHAR2);

說明：

用於產生一個隨機數種子,設定種子的目的是可以重複產生隨機數，用於調試。否則每次不同，難以調度。

樣本：

Sql代碼  

1. BEGIN  
2.   dbms_random.seed(‘cux‘);  
3.   FOR i IN 1 .. 10 LOOP  
4.     dbms_output.put_line(round(dbms_random.value * 100));  
5.   END LOOP;  
6. END;  

 

結果（不管執行多少次都不變）:

 http://zhangzhongjie.iteye.com/blog/1948930

oracle中隨機數的包的源檔案目錄：{oracle_home}\rdbms\admin\dbmsrand.sql

1.返回0~1間的隨機數(包括0和1)
sql> select dbms_random.value from dual;

2.返回10~20間的隨機數(包括10和20)
sql> select dbms_random.value(10,20) from dual;

3.隨機返回一個number
sql> select dbms_random.normal from dual;
注意：normal函數返回從常態分佈的一組數。此常態分佈標準差為1，期望值為0。這個函數返回的數值中有68%是介於-1與+1之間， 95%介於-2與+2之間，99%介於-3與+3之間。

4.隨機返回字串
文法：dbms_random.string(opt, len)
參數：
opt：取值，由oracle提供，如下
‘u‘或‘U‘–>返回大寫字母
‘l‘或‘L‘–>返回小寫字母
‘a‘或‘A‘–>大小寫字母混合
‘x‘或‘X‘–>大寫字母和數字混合
‘p‘或‘P‘–>任意可顯示字元
當選項為其他字母的時候返回的內容仍將是大寫字母
len：長度
eg：
–返回4位大寫字母
sql> select dbms_random.string(‘U‘,4) FROM DUAL;
–返回4位大小寫字母混合
sql> select dbms_random.string(‘a‘,4) FROM DUAL;

5.產生一個隨機數(正或負)
sql> select dbms_random.random from dual;

6.隨機擷取emp表中3條記錄
sql> select *
from (select * from scott.emp order by dbms_random.random)
where rownum < 4;

http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152614.html

SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY salary DESC;
樣本2：
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;
以上兩個樣本結果相同。
因為ORDER BY salary DESC==ORDER BY 2 DESC
salary是第二個元素，所以可以使用2來代替。
但是數字不可以使用0，也不可以超出查詢的列。
例如：select * from employers
order by x;
如果employers表有九個欄位，那個X的範圍就是1---9
不能是0，也不能是10.

http://blog.163.com/[email protected]/blog/static/16686932420114495216619/

1、ORDER BY 中關於NULL的處理
預設處理，Oracle在Order by 時認為null是最大值，所以如果是ASC升序則排在最後，DESC降序則排在最前。
當然，你也可以使用nulls first 或者nulls last 文法來控制NULL的位置。
Nulls first和nulls last是Oracle Order by支援的文法
如果Order by 中指定了運算式Nulls first則表示null值的記錄將排在最前(不管是asc 還是 desc)
如果Order by 中指定了運算式Nulls last則表示null值的記錄將排在最後 (不管是asc 還是 desc)
使用文法如下：
--將nulls始終放在最前
select * from zl_cbqc order by cb_ld nulls first
--將nulls始終放在最後
select * from zl_cbqc order by cb_ld desc nulls last
2、幾種排序的寫法
單列升序：select<column_name> from <table_name> order by <column_name>; （預設升序，即使不寫ASC）
單列降序：select <column_name> from <table_name> order by <column_name> desc;
多列升序：select <column_one>, <column_two> from <table_name> order by <column_one>, <column_two>;
多列降序：select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> desc;
多列混合排序：select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> asc;
3、今天看到的新寫法
SQL> select * from tb;
BLOGID BLOGCLASS
---------- ------------------------------
1 人生
2 學習
3 工作
5 朋友
SQL> select * from tb order by decode(blogid,3,1,2), blogid;
BLOGID BLOGCLASS
---------- ------------------------------
3 工作
1 人生
2 學習
5 朋友
我所說的就是上面紅色的那句話。實現的功能就是不管怎樣，BLOGID為3的值必須排在第一位，其他的記錄按照BLOGID升序排序。
shiyiwan同學給我的解釋是這樣的：“預設升序排序，blogid = 3時返回1，其他則返回2， 所以blogid = 3的記錄排在最前啊。”
wildwave同學也給了個說法：“你將那個decode理解成前面select後面的列，按照那一列排序就好了。”
我又去試了一條語句，結果如下
SQL> select * from tb order by decode(blogid,3,1,2);
BLOGID BLOGCLASS
---------- ------------------------------
3 工作
5 朋友
1 人生
2 學習
我現在的理解是這樣的，通過decode()函數，BLOGID值為3的那條記錄被轉換為了1，
而其他的記錄都是2，那麼按照這個順序排序，肯定BLOGID為3的那條記錄永遠在最前面了，
不僅如此，注意到後面還加了個按BLOGID預設升序排列，這也就是說對於那些被DECODE轉換為2的記錄來說，
按照它們的BLOGID升序排序

http://blog.csdn.net/zxcvg/article/details/6670895

 

oracle系統包——dbms_random用法及order by 小結（轉）