Oracle System Package--dbms_random usage and ORDER by summary

Dbms_random is a package that can generate random numbers or strings.

This package has several functions such as initialize (), seed (), terminate (), value (), Normal (), random (), string (), but value () is the most common, followed by the detailed usage of each function:

1.dbms_random.value method

First type: FUNCTION value RETURN number;

Description

This usage has no parameters and returns a numeric value with 38 digits, ranging from 0.0 to 1.0, but excluding 1.0.

Example:

SQL code

1. BEGIN
2. For I in 1: Ten loops
3. Dbms_output.put_line (Round (dbms_random.value * 100));
4. END LOOP;
5. END;

Results:

The second type: FUNCTION value (low in number, high in number) RETURN number;

Description

Value has two parameters, the first is the lower limit, the second is the upper limit, and a number between the lower bound and the upper bound is generated, but not the upper bound.

Example:

SQL code

1. BEGIN
2. For I in 1: Ten loops
3. Dbms_output.put_line (Trunc (Dbms_random.value (1,101)));
4. END LOOP;
5. END;

Results:

2. Dbms_random.string method

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

Description

Some user management programs may need to create a random password for the user. This can be done using the dbms_random.string under 10G.

Parameters:

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:

Represents the length of the string returned.

Example:

SQL code

1. SELECT dbms_random.string (' P ', 8) from dual;

Results:

3. Dbms_random.random method

Functionrandomreturn Binary_integer;

Description

Random returns a value of type Binary_integer that produces a random number of any size.

Example:

SQL code

1. SELECT dbms_random.random from dual;

Results:

Note: What is the difference between Dbms_random.value and dbms_random.random?

1, order by Dbms_random.value, calculates a random number for each row of the result set, Dbms_random.value is a column of the result set (although the column is not in the select list), and then sorts it according to that column. The order of the obtained is naturally random.

2, Value returns the number type, and the returned value is between 1 and 0, and the random returns the Binary_integer type (numbers stored in binary form, which is said to be more efficient than the digit, but I haven't tested it. But the value range is certainly less than number, the specific limit to check information.

4. Dbms_random.normal method

Functionnormal RETURN number;

Description

The normal function returns a set of numbers that obey a normal distribution. This normal distribution has a standard deviation of 1 and a expected value of 0. 68% of the values returned by this function are between 1 and +1, 95% between 2 and +2, and 99% between 3 and +3.

Example:

SQL code

1. BEGIN
2. For I in 1: Ten loops
3. Dbms_output.put_line (Round (dbms_random.normal));
4. END LOOP;
5. END;

Results:

5. Dbms_random.seed method

PROCEDURE seed (Val in Binary_integer), or PROCEDURE seed (Val in VARCHAR2);

Description

Used to generate a random number seed, the purpose of seeding is to repeat the generation of random numbers for debugging. Otherwise, it is difficult to dispatch each time.

Example:

SQL code

1. BEGIN
2. Dbms_random.seed (' Cux ');
3. For I in 1: Ten loops
4. Dbms_output.put_line (Round (dbms_random.value * 100));
5. END LOOP;
6. END;

Results (no matter how many times they are executed):

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

Source file directory for random number packages in Oracle: {oracle_home}\rdbms\admin\dbmsrand.sql

1. Return random numbers between 0~1 (including 0 and 1)
Sql> select Dbms_random.value from dual;

2. Return random numbers between 10~20 (including 10 and 20)
Sql> Select Dbms_random.value (10,20) from dual;

3. Randomly returns a number
Sql> select Dbms_random.normal from dual;
Note: The normal function returns a set number from a normal distribution. This normal distribution has a standard deviation of 1 and a expected value of 0. 68% of the values returned by this function are between 1 and +1, 95% between 2 and +2, and 99% between 3 and +3.

4. Random return string
Syntax: dbms_random.string (OPT, Len)
Parameters:
Opt: value, provided by Oracle, as follows
' U ' or ' u ' –> return capital letters
' L ' or ' l ' –> return lowercase letters
' A ' or ' a ' –> uppercase and lowercase letters mixed
' x ' or ' x ' –> capital letters and numbers mixed
' P ' or ' P ' –> any character that can be displayed
The returned content will still be uppercase when the item is selected as a different letter
Len: Length
eg
– Returns 4-bit uppercase letters
Sql> Select dbms_random.string (' U ', 4) from DUAL;
– Returns a 4-bit mixed case letter
Sql> Select Dbms_random.string (' A ', 4) from DUAL;

5. Generate a random number (positive or negative)
Sql> select Dbms_random.random from dual;

6. Randomly obtain 3 records in the EMP table
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;
Example 2:
SELECT last_name, salary, hire_date
From EMPLOYEES
ORDER by 2 DESC;
The above two example results are the same.
Because ORDER by salary Desc==order by 2 DESC
Salary is the second element, so you can use two instead.
However, the number cannot be used for 0, nor is it possible to exceed the query's columns.
Example: SELECT * FROM Employers
Order by X;
If the employers table has nine fields, the X range is 1---9
It can't be 0, and it can't be 10.

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

1. Handling of NULL in ORDER by
Default processing, Oracle considers NULL to be the maximum value at order BY, so if ASC ascending is the last, desc descending is the first.
Of course, you can also use Nulls first or nulls last syntax to control the position of NULL.
Nulls first and Nulls last are the Oracle Order by supported syntax
If the expression specified in order by nulls first indicates a null value, the record will be in the top (either ASC or DESC)
If the expression specified in order by is nulls last, the record that represents the null value will be at the end (either ASC or DESC)
Use the following syntax:
--always put Nulls in the front
SELECT * from ZL_CBQC ORDER by Cb_ld nulls first
--always put nulls at the end
SELECT * from ZL_CBQC ORDER BY cb_ld Desc nulls Last
2, several sorts of writing
Single row ascending:select<column_name> from <table_name> order by <column_name>; (Default ascending, even if ASC is not written)
Single row descending: Select <column_name> from <table_name> order by <column_name> Desc;
Multiple columns Ascending: select <column_one>, <column_two> from <table_name> order by <column_one>, <column_two >;
Multiple column descending: select <column_one>, <column_two> from <table_name> order by <column_one> Desc, <column _two> desc;
Multi-column Mixed sort: select <column_one>, <column_two> from <table_name> order BY <column_one> Desc, < column_two> ASC;
3. New wording to see today
Sql> SELECT * from TB;
BLOGID Blogclass
---------- ------------------------------
1 Life
2 Learn
3 work
5 Friends
Sql> SELECT * from TB ORDER by decode (blogid,3,1,2), blogID;
BLOGID Blogclass
---------- ------------------------------
3 work
1 Life
2 Learn
5 Friends
All I'm saying is the red one above. The function that is implemented is that the value of blogID 3 must be ranked first, and the other records sorted in ascending order of blogID.
Shiyiwan to me the explanation is this: "The default ascending sort, blogID = 3 o'clock returns 1, others return 2, so blogID = 3 of the record in the first AH." ”
Wildwave classmate also gave a saying: "You will that decode to understand the column behind the front select, according to that column sort." ”
I tried another statement, and the results were as follows
Sql> SELECT * from TB ORDER by decode (blogid,3,1,2);
BLOGID Blogclass
---------- ------------------------------
3 work
5 Friends
1 Life
2 Learn
I now understand that, through the decode () function, the record with the blogID value of 3 is converted to 1,
And the other records are 2, then in this order, it is sure that the record of blogID 3 is always at the front,
Not only that, but also added a blogid by default in ascending order, which means that for those records converted to 2 by Decode,
Sort in ascending order of their blogid

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

Oracle System Package--dbms_random usage and ORDER by summary (GO)