Introduction to the use of cast functions in Oracle

The cast () function can be used to convert data types.

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

The following examples are tested by me.

First, convert a column or value

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

Case:

1), Convert columns

--Converts the type of empno (number) to the VARCHAR2 type.

Select CAST (empno as VARCHAR2) as empno from EMP;

EMPNO
----------
7369
7499
7521
...

2), conversion value

--Converts a string to an integer type.
SELECT CAST (' 123 ' as int) as result from dual;

RESULT
---

123
The return value is an integer value of 123.

--What happens if you try to convert a string that represents a decimal to an integer value?
SELECT CAST (' 123.4 ' as int) as result from dual;

RESULT
--------

123

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

RESULT
--------

124
As can be seen from the above, the CAST () function can perform rounding operations.

--Truncate decimals

SELECT CAST (' 123.447654 ' as Decimal (5,2)) as result from dual;

RESULT
-----------
123.45
Decimal (5,2) indicates that the total number of values is 5, which is exactly 2 digits after the decimal point.
SELECT CAST (' 123.4 ' as decimal) as result from dual;
The result is an integer value:
123
Second, convert a collection

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

1) Convert to table

Example:

--Student score Table

CREATE TABLE Stu_score
(Stu_no varchar2 (),--Study No.
Score number--Total Score
);
INSERT into Stu_score values (' 201301 ', 67);
INSERT into Stu_score values (' 201302 ', 63);
INSERT into Stu_score values (' 201303 ', 77);
INSERT into Stu_score values (' 201304 ', 68);
INSERT into Stu_score values (' 201305 ', 97);
INSERT into Stu_score values (' 201306 ', 62);
INSERT into Stu_score values (' 201307 ', 87);
Commit

------------------------------------------

SELECT * from Stu_score;

Study number Score

--------  ----------
201301 67
201302 63
201303 77
201304 68
201305 97
201306 62
201307 87

--Scholarship form.

-The scholarship table specifies the rank, the number of each rank and the bonus.

CREATE TABLE Scholarship
(
Stu_rank varchar,--Rank
Stu_num int,--Limited number of persons
Money number--Bonuses
);
INSERT INTO scholarship values (' 1 ', 1, ' 1000 ');
INSERT INTO scholarship values (' 2 ', 2, ' 500 ');
INSERT INTO scholarship values (' 3 ', 3, ' 100 ');
Commit

-----------------------------------------------

SELECT * from scholarship;

Rank number Bonus
---------- --------------------------------------- ----------
1 1 1000
2 2 500
3 3 100

The rankings and bonuses are to be determined by the number of scholarships available, based on the grades in descending order. The same score is not considered in the rankings.
the results of the rankings should be as follows:
School Number score prize
201305 1
201307 2
201303 2
201304 3
201301 3
201302 3

SELECT C.stu_no,c.score,b.stu_rank,b.money
From (SELECT C.*,row_number () up (ORDER by score DESC) RN from Stu_score c) c
, (SELECT B.stu_rank,b.money,row_number () over (ORDER by B.stu_rank) RN
From Scholarship B
, TABLE (CAST (MULTISET (SELECT NULL
From DUAL
CONNECT by Level <= B.stu_num
)
As SYS. Odcivarchar2list)
)
) b
WHERE C.rn=b.rn;

The results of the implementation are as follows:

Stu_no Score Stu_rank Money
-------------------------------------------------- ----------        ----------         ----------
201305 97 1 1000
201307 87 2 500
201303 77 2 500
201304 68 3 100
201301 67 3 100
201302 63 3 100

By comparison, it is true that the goal is achieved.

In addition, cast can also be converted into Collection,varray, at this time all need to multiset set function together to use.

Introduction to the use of cast functions in Oracle