Shulou(Shulou.com)05/31 Report--

This article shows you how to use the CAST function in oracle, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The CAST () function can convert data types.

The parameters of the CAST () function have two parts, the source value and the target data type, separated by the AS keyword.

The following examples have passed my test.

I. convert columns or values

Syntax: cast (column name / value as data type)

Use case:

1), conversion column

-converts the type of empno (number) to the type of varchar2.

Select cast (empno as varchar2 (10)) as empno from emp

EMPNO

-

7369

7499

7521

...

2), conversion value

Converts a string to an integer.

SELECT CAST ('123' AS int) as result from dual

RESULT

-

one hundred and twenty three

The return value is an integer value of 123.

What happens if you try to convert a string representing a decimal to an integer value?

SELECT CAST ('123.4' AS int) as result from dual

RESULT

-

one hundred and twenty three

SELECT CAST ('123.6' AS int) as result from dual

RESULT

-

one hundred and twenty four

As you can see from the above, the CAST () function performs rounding operations.

-truncated decimal

SELECT CAST ('123.447654' AS decimal (5pm 2)) as result from dual

RESULT

-

123.45

Decimal (5 ~ 2) indicates that the total number of digits of the value is 5, accurate to 2 places after the decimal point.

SELECT CAST ('123.4' AS decimal) as result from dual

The result is an integer value:

one hundred and twenty three

2. Convert a set

Syntax: cast (multiset (query statement) as data type)

1) convert to table

Example:

-- Student score sheet

Create table stu_score

(stu_no varchar2 (50),-- student number

Total score of score number--

);

Insert into stu_score values ('201301 June 67)

Insert into stu_score values ('201302 June 63)

Insert into stu_score values ('201303 June 77)

Insert into stu_score values ('201304 June 68)

Insert into stu_score values ('201305 June 97)

Insert into stu_score values ('201306 June 62nd)

Insert into stu_score values ('201307 June 87)

Commit

-

Select * from stu_score

Student number score

--

201301 67

201302 63

201303 77

201304 68

201305 97

201306 62

201307 87

-- A form of scholarship.

The scholarship table specifies the ranking, the number of people in each ranking and the bonus.

Create table scholarship

(

Stu_rank varchar (10)-- ranking

Stu_num int,-- limits the number of people

Money number-- bonus

);

Insert into scholarship values ('1pm, 1pm, 1000')

Insert into scholarship values ('2million, 2million, 500')

Insert into scholarship values ('3pm, 3pm, 100')

Commit

Select * from scholarship

Ranking and number of bonuses

1 1 1000

2 2 500

3 3 100

Now it is necessary to arrange them in descending order according to their grades, and determine the rankings and bonuses according to the number of places in the scholarship table. The same score is not considered in the ranking.

The results of the ranking should be as follows:

Student number, performance, ranking, bonus

201305 97 1 1000

201307 87 2 500

201303 77 2 500

201304 68 3 100

201301 67 3 100

201302 63 3 100

SELECT c.stu_no,c.score,b.stu_rank,b.money

FROM (SELECT c.) OVER (ORDER BY score DESC) rn FROM stu_score c) c

, (SELECT b.stuttingrankingb.moneyreachorrownNUMBER () OVER (ORDER BY b.stu_rank) rn

FROM scholarship b

, TABLE (CAST (MULTISET (SELECT NULL)

FROM DUAL

CONNECT BY LEVEL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.