Oracle中的数据类型和数据类型之间的转换

Oracle中的数据类型

/*
   ORACLE 中的数据类型；
   
   char     长度固定 范围：1-2000

   VARCHAR2 长度可变 范围：1-4000
   
   LONG    长度可变   最大的范围2gb  长字符类型
   
   number  数字  number(p,s)
   
   Date  日期类型,精确到秒
   
   TIMESTAMP 存储日期，时间，时区，妙值，精确到小数
   
   CLOB 字符数据
   
   BLOB 存放二进制数据，视频图片等
   
   BFILE :用于将二进制数据存储在数据库外部的操作系统文件中
   
   所谓固定长度：

   所谓固定长度：是指虽然输入的字段值小于该字段的限制长度，
   但是实际存储数据时，会先自动向右补足空格后，
   才将字段值的内容存储到数据块中。
   这种方式虽然比较浪费空间，
   但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。
   
   
   所谓可变长度：是指当输入的字段值小于该字段的限制长度时，
   直接将字段值的内容存储到数据块中，
   而不会补上空白，这样可以节省数据块空间。
   
*/



--储字节或字符？

 CREATE TABLE T1(
   NAME CHAR(4) --默认的是字节
 );
 
  INSERT INTO T1 VALUES('AB');
  INSERT INTO T1 VALUES('ABCD');
  INSERT INTO T1 VALUES('我爱中国');   --这个就会报错
  INSERT INTO T1 VALUES('我爱');       --这样就是正确滴呀
  
  SELECT * FROM T1;
  SELECT LENGTH(ltrim(rtrim(NAME))) FROM T1 WHERE NAME='AB' --2
  SELECT LENGTH(NAME) FROM T1 WHERE NAME='AB' --4
  
   SELECT NAME FROM T1 WHERE NAME=N'AB'; --没有值滴呀；
   
    SELECT NAME FROM T1 WHERE NAME='AB'; 
  
  
  
  --同样的比较;
   CREATE TABLE T2(
   NAME CHAR(4 CHAR) --默认的是字节
 );
 
  INSERT INTO T2 VALUES('ABCD');
  INSERT INTO T2 VALUES('ABCDABCD'); --报错
  INSERT INTO T2 VALUES('我爱中国'); ----正常插入
  
  SELECT * FROM T2;
 
  --一个汉字占几个字符，具体的还要看 编码方式
  /*
    数据库的NLS_CHARACTERSET 为AL32UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占用两个字节。
  */


--NCHAR   --注意N 开头的都是unicode 字符类型的数据滴呀 
/*
这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时，如果字段是NCHAR类型，则需要如下书写

SELECT translated_description FROM product_descriptions

WHERE translated_name = N'LCD Monitor 11/PM';

*/


 CREATE TABLE N1(
   NAME NCHAR(4) --默认的是字节
 );
 
  INSERT INTO N1 VALUES('AB');
  INSERT INTO N1 VALUES('ABCD');
  INSERT INTO N1 VALUES('我爱中国');   --插入成功
  INSERT INTO N1 VALUES('我爱');       --插入成功
  
  SELECT * FROM N1;

/*
  N 在这里表示 Unicode，就是双字节字符。对于西文字符，用一个字节来存储过足够了，对于东方文字字符，
  就需要两个字节来存储。
  Unicode 为了统一、规范、方便、兼容，就规定西文字符也用两个字节来存储。
*/
-- N 不能乱加滴呀
  SELECT * FROM N1
  WHERE NAME =N'我爱  ';  --这样查询的话，你必须加上两个空格；才能查到值滴呀；
  
  SELECT * FROM N1
  WHERE NAME ='我爱';
  
  SELECT * FROM N1
  WHERE NAME ='AB';
  
  
  SELECT * FROM N1
  WHERE NAME =N'AB  ';
  
  
  SELECT LENGTH('AB') FROM DUAL;  --2
  
  SELECT LENGTH('AB  ') FROM DUAL;  --4
  
  SELECT LENGTH(NAME) FROM N1 WHERE NAME ='AB'; --长度为4


/*
 不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词，
 VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比，具有不同的比较语义。

1.4： VARCHAR2类型

变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

1.5： NVARCHAR2类型

这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。

*/


DECLARE 
   I INT :=1;
   J VARCHAR2(20) :='1';  
   BEGIN
       DBMS_OUTPUT.put_line(I || J);  --结果为 11
   END;
   
   
   DECLARE 
   I INT :=1;
   J VARCHAR2(20) :='1';  
   BEGIN
       DBMS_OUTPUT.put_line(I + J);  --结果：2 ，存在隐私转换和显示转换的问题滴呀
   END;
   

   
   DECLARE 
   I VARCHAR2(20) :='1';
   J VARCHAR2(20) :='1';  
   BEGIN
       DBMS_OUTPUT.put_line(I + J);  --结果还是我们的2 关键看我们使用符号滴呀
   END;


    DECLARE 
   I INT :=1;
   J INT :=1;  
   BEGIN
       DBMS_OUTPUT.put_line(I ||  J);  --结果是11 ，+ 是用于数值类型之间加减，||是用于字符串之间的加减；
                                       --这个才是问题的关键滴呀
   END;



  --时间格式的转换滴呀

SELECT SYSDATE FROM DUAL;  --默认的格式是这样的：   2016/6/22 16:12:07

--然后使用我们的TO_CHAR 来进行我们时间格式的转化滴:  2016-06-22 04:06:02
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM DUAL;



--将数值类型转换成字符串类型；

--强制显示为当地的货币类型
SELECT TO_CHAR(1200,'L99,999.99') FROM DUAL;

SELECT TO_CHAR(SAL,'$00,000,00') FROM EMP;

这里再补充一些；

--这里还有我们的number 数据类型的值；
--INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。
--FLOAT类型也是NUMBER的子类型

/*

它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节， 而不是2千兆字符），与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下：

1.一个表中只有一列可以为LONG型。(Why?有些不明白)

2.LONG列不能定义为主键或唯一约束，

3.不能建立索引

4.LONG数据不能指定正则表达式。

5.函数或存储过程不能接受LONG数据类型的参数。

6.LONG列不能出现在WHERE子句或完整性约束（除了可能会出现NULL和NOT NULL约束）


*/

 ORACLE 之间类型的转换

--trunc

--数值类型函数

  SELECT ABS(-5) FROM DUAL;  --5
  
  SELECT CEIL(2.1) FROM DUAL; --3
  
  SELECT FLOOR(2.9) FROM DUAL; --2
  
  --ROUND 按指定的精度 对十进制进行四舍五入；
  
  SELECT ROUND(12.111,1) FROM DUAL; --12.1
  
  SELECT ROUND(12.999,1) FROM DUAL; --13 
  
  
  --TRNC 按照指定的精度 十进制，进行截断 
  
  SELECT TRUNC(12.99,1) FROM DUAL; -- 12.9
  
  SELECT TRUNC(12.99) FROM DUAL;   --12
  
  SELECT TRUNC(45.923,1) FROM DUAL; --45.9
  


--字符类型函数

  SELECT ASCII('A')  FROM DUAL; --65
  
  SELECT CHR(65) FROM DUAL;  --A
  
  SELECT LOWER('ABC') FROM DUAL; --abc
  
  SELECT UPPER('abc') FROM DUAL; --ABC
  
  --将字符串转换成每个单词以大写开头
  --每个字母的开头都是大写的
  SELECT INITCAP('i am student') FROM DUAL; --I Am Student
  
  SELECT CONCAT('HELLO--','67') FROM DUAL;  --HELLO--67

  SELECT SUBSTR('HELLO',1,2) FROM DUAL; --HE ,2 是包含了自己的
  
  SELECT LENGTH('FUCK') FROM DUAL; --4
  
  
  --字符串的操作函数是比较有用滴呀；
  
  --第一次出现的位置
  
    SELECT INSTR('ABA','A',1,1) FROM DUAL;  --1
  
  --第二次出线的位置
  
    SELECT INSTR('ABA','A',1,2) FROM DUAL;  --3
    
    --用指定字符串填充左侧 指定的长度
    
    SELECT LPAD('A',3,'-') FROM DUAL;  --   --A
    --右侧
    SELECT RPAD('A',3,'-') FROM DUAL;    --  A--
 
  --先是去除空格;
  
  SELECT LENGTH(TRIM('   A    '))  FROM DUAL;  --1
  
  -- 去除 ssmith 中的s 
  SELECT trim('S' FROM 'SSMISTHS')  FROM DUAL;  --MISTH 
  
  --只是去除开头的
  
  SELECT TRIM(LEADING 'S' FROM 'SSMISTHS') FROM DUAL; --MISTHS
  
  --只是去除结尾的
  
  SELECT TRIM(trailing 'S' FROM 'SSMISTHS') FROM DUAL;  --SSMISTH
 

  -- 还有一个both 参数  默认的就是这个函数滴呀
   SELECT TRIM(BOTH 'S' FROM 'SSMISTHS') FROM DUAL;  --MISTH 
   
   SELECT REPLACE('ABCB','B','X') FROM DUAL;     --AXCX 这个就是基本的额转换滴呀



--日期类型函数

 --取出月份差值；
 
  select add_months(sysdate,-1) from dual; 
 
  SELECT MONTHS_BETWEEN('04-11月-05','11-4月-05') FROM DUAL;

  --至少你要知道有这些函数，用的时候，才方便查询滴呀
  
  SELECT add_months(sysdate,3)  FROM DUAL;
  
  SELECT to_char(add_months(sysdate,1),'yyyy-mm-dd')  FROM DUAL; --就变成了我们的 
  
  SELECT NEXT_DAY(SYSDATE,'星期一') FROM DUAL;  --下个星期一的时间； 如：下周星期一是几号;
  
  SELECT LAST_DAY(SYSDATE) FROM DUAL;  --该月的最后一天
  
  SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-4)) FROM DUAL;
  
  --TO_CHAR 中的时间格式滴呀；
  
  --按指定的格式，对时间进行四舍五入滴 
  SELECT ROUND(SYSDATE) FROM DUAL;
  
  SELECT TO_CHAR('13-2月-03') FROM DUAL; --尼玛 原来这个时间，是尼玛从右边往左边读取滴呀,我曹你麻痹的
  
  SELECT TO_DATE('13-2月-03') FROM DUAL; ---2003/2/13
   
  --按照指定的格式，进行时间上的四射五日滴呀
  --比如：不到半个月，算一个月
  --比如： 满8个月，算一年的 
  --各种算法滴呀;
  
  
  SELECT ROUND(TO_DATE('13-2月-03'),'YEAR') FROM DUAL;
  
  SELECT ROUND(TO_DATE('17-5月-03'),'MONTH') FROM DUAL;
    
  SELECT ROUND(TO_DATE('13-2月-03'),'DAY') FROM DUAL;
   
  --按指定的方式进行截断滴呀
    
  SELECT TRUNC(TO_DATE('13-2月-03'),'YEAR') FROM DUAL;
  
  SELECT TRUNC(TO_DATE('28-5月-03'),'MONTH') FROM DUAL;
    
  --这个就是按照周进行截断
  SELECT TRUNC(TO_DATE('6-6月-16'),'DAY') FROM DUAL;



 --转换函数
 
 --转换成字符串类型;
 SELECT TO_CHAR(1234.5,'$9999.9') FROM DUAL;
 
 SELECT TO_DATE('1990-01-10','yyyy-mm-dd') FROM DUAL;
 
 SELECT TO_NUMBER('123.5') FROM DUAL;

 -- SELECT 1 || 1 FROM DUAL; 就算是数字类型，也会给你转换成字符类型的值；



--自动类型转换 

/*

 自动类型转换 
Oracle可以自动根据具体情况进行如下的转换： 
* 字符串到数值。 
* 字符串到日期。 
* 数值到字符串。 
* 日期到字符串。  

*/


--日期类型转换

--2016-06-23 17:02:39 下午 星期四
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM DY') FROM DUAL;

/*
 语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。
 “YYYY”为4位的年份，“MM”为两位的月份，“DD”为两位的日期，
 “HH24”表示显示24小时制的小时,“MI”表示显示分钟，“SS”表示显示秒，
 “AM”表示显示上午或下午(本例中为下午)，“DY”表示显示星期。“-”、
 “:”和空格原样显示，用于分割日期和时间

*/


--其他常用函数

SELECT NVL(NULL,0) FROM DUAL;

SELECT DECODE(1,1,'男',2,'女','中间') from DUAL;

SELECT DECODE(7,1,'男',2,'女','中间') from DUAL;

--或则可以这么使用滴呀

SELECT DECODE((1-1),0,'男','女')  FROM DUAL;

--多个语句之间的 判断；
SELECT DECODE((1-1),0,'男','女',(1-1),1,'男','女')  FROM DUAL;

--返回环境信息；

SELECT USERENV('LANGUAGE') FROM DUAL;

--返回最大值

SELECT GREATEST(1,2,23,6,3,2,222) FROM DUAL;

--返回最小值
SELECT LEAST(1,2,23,6,3,2,222) FROM DUAL;