Oracle数据库入门之函数/类型

Oracle主要数据类型 概述：各种数据库所支持的数据类型大同小异，与标准SQL语言中的数据类型可能略有出入 类型：varchar2：可以在声明字段时设置它的长度上限。而且使用它之后，就不必再考虑空格的存在 若插入的字符串的长度低于长度上限，系统就会自动将其

　　Oracle主要数据类型

　　概述：各种数据库所支持的数据类型大同小异，与标准SQL语言中的数据类型可能略有出入

　　类型：varchar2：可以在声明字段时设置它的长度上限。而且使用它之后，就不必再考虑空格的存在

　　若插入的字符串的长度低于长度上限，系统就会自动将其缩减为字符串的真实长度

　　number(m,n)：既可以用来表示整型，也可以表示浮点型。但m不可以超过38。如果n为0或者省略n，就代表它是整数

　　date：用来存放日期和时间

　　blob：通常是在应用程序中使用到它，而不是在数据库中利用SQL指令直接使用

　　比如通过JDBC技术访问数据库，读写blob或clob类型的字段，即读写长的字符串信息等等

　　char：它是一种定长的字符类型。在Oracle数据库不区分字符和字符串，它们被统称为字符型或文本型

　　所谓定长的字符型是指，插入的字符串若没有达到约定的字段长度，系统就会在字符串尾部自动补空格

　　同样，读取时的字段长度永远是声明时的字段长度。而且在比较字符串内容的时候，也需要考虑到空格的过滤

　　nchar：它也是定长的字符串类型，它是SQL语言标准中规定的，通常采用Unicode编码来保存不同国家或不同语言的字符

　　varchar：SQL标准在定义varchar时并没有保证能够向前和向后兼容，即有可能随着语言标准的修改而产生不兼容的问题

　　所以Oracle定义了同varchar型类似的varchar2型。就是为了在Oracle以后的版本中，，都永远支持varchar2类型

　　Oracle这么做就是为了确保此类型向前后兼容，以达到能够在Oracle系列数据库中进行数据的导入和导出的目的

　　long：它和varchar2的差别在于，它不支持对字符串内容进行检索，即查询时不可以对它的内容进行条件查询

　　而varchar2和char、nchar型等等都可以在查询的时候直接检索字符串的内容

　　补充：select * from v$nls_parameters;数据库的配置信息以数据表的形式存在，通常称其为关于数据的数据或数据字典

　　实际上它查询的是数据字典中的一个视图。其中NLS_CHARACTERSET对应的是当前的数据库字符集

　　缺省均为使用数据库字符集。教程中使用的是安装时默认的ZHS16GBK字符集，即汉字占2个字节，英文占1个字节

　　而NLS_NCHAR_CHARACTERSET对应的是nchar或nvarchar2类型所采用的辅助字符集，即AL16UTF16字符集

　　实际上AL16UTF16是一种16位定长的Unicode编码的字符集。而数据库字符集以及这种国家字符集都可以修改

　　但数据库字符集修改后可能会面临很严重的后果，除非是数据库管理员，普通用户不必对这方面进行深究

　　数据库中的数据导入导出的时候，如果源数据库和目标数据库所采用的字符集不同，也很容易出问题

　　函数

　　概述：函数可以认为是能够完成相对独立的功能的一段代码的集合。Oracle函数相当于其它语言中的方法或过程

　　Oracle函数可以分为单行函数和多行函数两大类。Oracle函数都是有返回值的

　　所谓的单行函数是针对查询结果中的每一行都起作用，都会返回一个结果

　　多行函数也就是所谓分组函数，是针对一组查询的记录，或者说多行，返回一个结果

　　单行：操作数据项，接受参数并返回处理结果，对每一返回行均起作用，可修改数据类型，可嵌套使用

　　单行函数分为字符函数，数值函数，日期函数，转换函数，通用函数

　　多行：也称分组函数，即对一组数据进行运算，针对一组数据(多行记录)只能返回一个结果

　　多行函数包括avg()、count()、max()、min()、sum()等

　　比如select avg(sal), max(sal), min(sal), sum(sal), max(hiredate), min(hiredate) from emp;

　　续一：使用Oracle的系统函数中的单行函数可实现诸多功能，如对数据进行计算，控制数据的输出格式

　　设置和改变日期的显示格式，进行数据类型转换，使用NVL等函数处理空值，实现IF-THEN-ELSE多路分支逻辑等等

　　续二：转换函数不会改变表中数据的字段类型和值。它就相当于将数据复制了一份，所转换的是复制之后的数据

　　数据类型转换包括隐含转换和显式转换两种方式，建议使用显式的数据类型转换，确保SQL语句的可靠性

　　续三：通用函数适用于包括空值在内的任何类型数据。通常用来实现空值的处理，空值的过滤或设置缺省值等

　　通用函数包括nvl()、nvl2()、nullif()、coalesce()、case表达式、decode()等

　　嵌套：单行函数可以嵌套使用，嵌套层次无限制。分组函数最多可嵌套两层。嵌套函数的执行顺序是由内到外

　　单行比如select empno,lpad(initcap(trim(ename)),10,' ') 姓名, job, sal from emp;

　　多行比如select max(avg(sal)) from emp group by deptno;--其实这里再使用分组函数就没有意义了

　　说明：通常数据库层面提供的函数，只是进行数据的简单的处理，或者说是只能实现极为常规的功能

　　所以就不应该，或者说是不要指望在数据库查询的层面来实现特别复杂的业务逻辑

　　如果应用程序的逻辑跟数据库混在一起的话，会不利于代码的维护和更新

　　而且也不利于数据库的管理，包括数据移植，数据库导入导出等等

　　日期类型

　　概述：在计算机操作系统或者各种高级编程语言中，日期通常会被保存成一个长整数，通常记录的是毫秒

　　Oracle内部以数字格式存储日期和时间信息：世纪,年,月,日,小时,分钟,秒

　　缺省的日期格式是DD—MON—YY。可使用sysdata函数获取当前系统日期和时间

　　运算：日期型数据可以直接加或减一个数值，结果认为日期。约定的该数值代表的是相加减的天数

　　两个日期型数据可以相减，结果为二者相差多少天。二者不能，因为日期相加是没有意义的

　　NVL()函数

　　概述：它用于将空值null替换为指定的缺省值，适用于字符、数字、日期等类型数据

　　格式：NVL(exp1,exp2)--如果表达式exp1值为null，则返回exp2值，否则返回exp1值

　　举例：select empno, ename, sal, comm, sal+nvl(comm,0) from emp;

　　select empno, ename, job, nvl(job, 'No job yet') from emp;

　　NVL2()函数

　　概述：它用于实现条件表达式功能

　　格式：NVL2(exp1,exp2,exp3)--如果表达式exp1值不为null，则返回exp2值，否则返回exp3值

　　举例：select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) 总收入 from emp;

　　NULLIF()函数

　　概述：它用于数据等价性比较并根据比较结果返回null或其中一个被比较的数值。实际开发中应用并不是很多

　　格式：nullif(exp1,exp2)--如果表达式exp1与exp2的值相等，则返回null，否则返回exp1的值

　　举例：select name 原名, nullif(pen_name, name) 化名 from author;

　　COALESCE()函数

　　概述：它用于实现数据“接合”功能

　　格式：coalesec(exp1,exp2,...)--依次考察各参数表达式，遇到非null值即停止并返回该值

　　若表达式均为null值，则返回null。通常最后一个表达式都是能确保不是空值的字段

　　举例：select empno, ename, sal, comm, coalesec(sal+comm,sal,0) 总收入 from emp;

　　CASE表达式

　　概述：它用于实现多路分支结构

　　格式：case exp when comparison_exp1 then return_exp1

　　[when comparison_exp2 then return_exp2

　　when comparison_expn then return_expn

　　else else_exp]

　　end

　　举例：select empno, ename, sal,

　　case deptno when 10 then '财务部'

　　when 20 then '研发部'

　　when 30 then '销售部'

　　else '未知部门'

　　end 部门

　　from emp;

　　说明：CASE中的每一个表达式(如deptno，10，财务部等)都可以是复合而成的

　　这种对齐方式的书写是为了增加可读性，当然也可以把代码写在同一行上

　　其中case到end之间的整体就相当于普通查询中的一个字段，end后面的“部门”是别名

　　DEDODE()函数

　　概述：和case表达式类似，它也用于实现多路分支结构

　　格式：decode(col|expression, search1, result1

　　[, search2, result2,...,]

　　[, default])

　　举例：select empno, ename, sal,

　　decode(deptno, 10, '财务部',

　　20, '研发部',

　　30, '销售部',

　　'未知部门')

　　部门

　　from emp;

　　COUNT()函数

　　格式：count(*)返回组中总记录数目

　　count(exp)返回表达式exp值非空的记录

　　count(distinct(exp))返回表达式exp值不重复的、非空的记录数目

　　举例：select count(*) from emp;--缺省的情况下，整个表就是一组

　　select count(comm) from emp;--返回emp表中comm字段不为空的记录(行)数目

　　select count(distinct(deptno)) from emp;--查找deptno值为非空且不重复的记录数目

　　分组函数与空值

　　概述：分组函数省略列中的空值。可使用NVL()函数强制分组函数处理空值

　　举例：select avg(comm) from emp;--等价于sum(comm)/count(comm)

　　select sum(comm) from emp;--计算表中非空的comm值的总和

　　select avg(nvl(comm,0)) from emp;--等价于avg(nvl(comm,0))/count(*)

　　GROUP BY子句

　　概述：它用于将表中数据分成若干小组

　　格式：select column, group_function(column)

　　from table

　　[where condition]

　　[group by group_by_expression]

　　[order by column];

　　举例：select deptno, avg(sal) from emp group by deptno;

　　说明：出现在SELECT列表中的字段，如果不是包含在组函数中，那么该字段必须同时在GROUP BY子句中出现

　　包含在GROPY BY子句中的字段则不必须出现在SELECT列表中。子句执行顺序是where→group by→order by缺省按升序排列

　　补充：select deptno, job, avg(sal) from emp group by deptno, job order by deptno desc;--基于多个字段的分组

　　select deptno, avg(sal) from emp;--非法

　　注意：如果没有GROUP BY子句，SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况

　　WHERE中不允许使用分组函数，如select deptno, avg(sal) from emp where avg(sal)>2000 group by deptno;--非法

　　这跟子句执行的顺序有关。where子句最先执行，在执行where子句的时候还没有执行过group by子句

　　于是程序不知道这是在分组，也不曾计算过avg(sal)的组内平均工资，所以在where子句中不允许使用分组函数

　　由于还没有执行过group by子句，所以此时就不确定如何怎么分组以及分多少个组

　　所以where子句中只能进行初级过滤。此时可以使用HAVING子句实现对平均工资的过滤

　　HAVING子句

　　概述：它用于过滤分组

　　格式：select column, group_function(column)

　　from table

　　[where condition]

　　[group by group_by_expression]

　　[having group_condition]

　　[order by column];

　　举例：select deptno, job, avg(sal)

　　from emp

　　where hiredate >= to_date('1981-05-01','yyyy-mm-dd')

　　group by by deptno, job

　　having avg(sal) > 1200

　　order by deptno, job;