select cast(empno asvarchar2(10)) as empno from emp;
EMPNO ———- 7369 7499 7521 …
1 2
2)、转换值 --将字符串转换为整型。 SELECT CAST('123' AS int) as result from dual;
RESULT — 123 返回值是整型值123。
1
--如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？ SELECT CAST('123.4' AS int) as result from dual;
RESULT ——– 123
1 2
SELECT CAST('123.6' AS int) as result from dual; 124 从上面可以看出，CAST()函数能执行四舍五入操作。
–截断小数
1 2 3 4
SELECT CAST('123.447654' AS decimal(5,2)) as result from dual; RESULT ----------- 123.45 decimal(5,2)表示值总位数为5，精确到小数点后2位。 SELECT CAST('123.4' AS decimal) as result from dual; 结果是一个整数值： 123 二、转换一个集合 语法：cast( multiset(查询语句) as 数据类型 ) 1)转换成table
例子： –学生成绩表
1
create table stu_score (stu_no varchar2(50),--学号 score number--总分 ); 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 c.stu_no,c.score,b.stu_rank,b.money FROM (SELECT c.*,ROW_NUMBER() OVER(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;
