Oracle数据库入门之函数类型

Oracle主要数据类型

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

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

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

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

date 用来存放日期和时间

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

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

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

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

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

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

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

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

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

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

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

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

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

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

而NLS_NCHAR_CHARACTERSET对应的是nchar或nvarchar 类型所采用的辅助字符集 即AL UTF 字符集

实际上AL UTF 是一种 位定长的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() nvl () nullif() coalesce() case表达式 decode()等

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

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

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

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

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

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

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

日期类型

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

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

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

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

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

NVL()函数

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

格式 NVL(exp exp ) 如果表达式exp 值为null 则返回exp 值 否则返回exp 值

举例 select empno ename sal m sal+nvl(m ) from emp;

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

NVL ()函数

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

格式 NVL (exp exp exp ) 如果表达式exp 值不为null 则返回exp 值 否则返回exp 值

举例 select empno ename sal m nvl (m sal+m sal) 总收入 from emp;

NULLIF()函数

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

格式 nullif(exp exp ) 如果表达式exp 与exp 的值相等 则返回null 否则返回exp 的值

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

COALESCE()函数

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

格式 coalesec(exp exp ) 依次考察各参数表达式 遇到非null值即停止并返回该值

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

举例 select empno ename sal m coalesec(sal+m sal ) 总收入 from emp;

CASE表达式

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

格式 case exp when parison_exp then return_exp

[when parison_exp then return_exp

when parison_expn then return_expn

else else_exp]

end

举例 select empno ename sal

case deptno when then 财务部

when then 研发部

when then 销售部

else 未知部门

end 部门

from emp;

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

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

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

DEDODE()函数

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

格式 decode(col|expression search result

[ search result ]

[ default])

举例 select empno ename sal

decode(deptno 财务部

研发部

销售部

未知部门 )

部门

from emp;

COUNT()函数

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

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

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

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

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

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

分组函数与空值

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

举例 select avg(m) from emp; 等价于sum(m)/count(m)

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

select avg(nvl(m )) from emp; 等价于avg(nvl(m ))/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)> 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( yyyy mm dd )

group by by deptno job

having avg(sal) >

lishixinzhi/Article/program/Oracle/201311/19087

;     

      1、启动oracle数据库：从root切换到oracle用户进入：su-oracle进入sqlplus环境，nolog参数表示不登录：sqlplus/nolog以管理员模式登录：sqlplus。

      2、数据库监听：启动监听服务：lsnrctlstart停止监听服务：lsnrctlstop查看监听状态。

      3、用户权限管理：注：以下命令都需要DBA权限。

( )SQLPLUS编程用户的规范·存储结构的规范考虑到用SQLPLUS编程可实现各行各业 各公司 各部门多种多样的应用需求 我们的SQLPLUS编程用户也应该朝这个方向规范:不同种类的应用必须有不同的用户;不同种类的应用必须有不同的存储位置 包括物理文件 缺省表空间 临时表空间的创建和规划:当准备编写某一较大规模(从ORACLE数据量和面向用户量考虑)应用程序时 首先应该创建一个逻辑的存储位置 表空间 同时定义物理文件的存放路径和所占硬盘的大小 ① 物理文件缺省的存放路径在/oracle_home/dbs下 在命令行下用UNIX指令df k 可查看硬盘资源分区的使用情况 如果oracle_home使用率达 ‰以上 而且有一个或多个较为空闲的硬盘资源分区可以利用 我们最好把物理文件缺省的存放路径改到较为空闲的硬盘资源分区路径下 在此路径下我们可以这样规划资源物理文件的存储:xxx表空间xxx行业/ xxx公司/ xxx 部门/ xxx 服务 dbfDEMO表空间default_datafile_home /col /elec/sys /demo dbfdefault_datafile_home /col /elec/sys /demo dbf公司系统四部摹拟演示系统物理文件HUMAN表空间default_datafile_home /col/elec/human/human dbf公司人事部人事管理系统物理文件BOOK表空间default_datafile_home /col/elec/book/book dbf公司资料室图书管理系统物理文件QUESTION表空间default_datafile_home /col/elec/client/question dbf公司客户服务部问题库系统物理文件PC表空间default_datafile_home /col/chaoxun/client/pc dbf公司PC机售后服务系统物理文件……表空间default_datafile_home /……………………………等等说明:其中default_datafile_home 指oracle_home/dbs;default_datafile_home 指较为空闲的硬盘资源分区路径 ② 物理文件的大小根据应用系统的数据量 数据对象 程序包的多少来定 一般用于摹拟演示的小系统 表空间初始的物理文件为 M即能满足要求 如果信息量满 还可以增加物理文件 扩充表空间(每次扩充大小也可暂定为 M);一般实际运行的应用系统可适当增加表空间初始的物理文件大小 但也不要一次分配太大(因为不易回收空间 却易扩充空间) 这也需要根据具体情况具体分析:信息量大 需长时间保存的应用在条件允许情况下 表空间可以大到几百M甚至上G;信息量小 短期经常刷新的应用 表空间可以控制在 M以下 ③ 表空间的名称应该采用同系统应用相似的英文字符或字符缩写 表空间所对应的一个或多个物理文件名也应有相关性 不同用户所处的缺省表空间不同 存储的信息就不能互相访问 这比把所有用户信息都储存在系统表空间 安全性大大提高了 如果用ORACLE WEB SERVER管理端口创建的用户 其缺省和临时表空间一定是系统表空间 DBA切记要改变用户的缺省表空间 临时表空间存放临时数据段 处理一些排序 合并等中间 *** 作 根据实际应用的需求可以把它们放在专门创建的表空间里;如果系统表空间大 也可以把它们放在系统表空间 用户创建的数据索引最好和数据文件分开存放在不同表空间 以减少数据争用和提高响应速度 ·密码和用户名的规范有相当数量的ORACLE用户名和密码一致 这是个很不安全的因素 我们建议ORACLE用户名和密码一定不要一样 密码最好在五 六位字符以上 不同用户间不应该使用相同的密码 用户名的定义可根据实际应用的英文名来设 而依据编程人员的姓名定义的用户名实际上不规范 可在日后的工作中结合上述有关存储结构规范的说明逐步改进 ( )特殊要求用户的规范在ORACLE数据库使用过程中 还会遇到一些有特殊要求的用户:非编程人员需要对某个表有查询 增加 删除 修改的权利 DBA应创建一个这样的用户 先确定用户名和密码 再规定相关应用所在缺省表空间(包含某个表)和临时表空间 最后TABLE属主给其授权:赋予CONNECT角色SELECT INSERT DELETE UPDATE ON THE TABLE的对象级权限 这可根据实际需求自由取舍 举例:●给新用户授于对象级权限(命令行方式):假设新用户NEW 需要有查询 删除 修改DCD用户的表EMP %svrmgrlSVRMGR>connect internal; 以系统管理员登录SVRMGR>create user new identified by new default tablespace app;SVRMGR>connect dcd/dcdpwd; 以dcd用户登录SVRMGR>grant connect to new ;SVRMGR>grant select on emp to new ;SVRMGR>grant delete on emp to new ;SVRMGR>grant update on emp to new ;说了这么多关于用户的问题 那么接下来我们就详细得说一下关于密码文件的使用以及维护 在Oracle数据库系统中 用户如果要以特权用户身份(INTERNAL/SYSDBA/SYSOPER)登录Oracle数据库可以有两种身份验证的方法:即使用与 *** 作系统集成的身份验证或使用Oracle数据库的密码文件进行身份验证 因此 管理好密码文件 对于控制授权用户从远端或本机登录Oracle数据库系统 执行数据库管理工作 具有重要的意义 Oracle数据库的密码文件存放有超级用户INTERNAL/SYS的口令及其他特权用户的用户名/口令 它一般存放在ORACLE_HOME\DATABASE目录下 ·密码文件的创建:在使用Oracle Instance Manager创建一数据库实例的时侯 在ORACLE_HOME\DATABASE目录下还自动创建了一个与之对应的密码文件 文件名为PWDSID ORA 其中SID代表相应的Oracle数据库系统标识符 此密码文件是进行初始数据库管理工作的基础 在此之后 管理员也可以根据需要 使用工具ORAPWD EXE手工创建密码文件 命令格式如下:C:\ >ORAPWD　FILE=< FILENAME >　PASSWORD =< PASSWORD > ENTRIES=< MAX_USERS >各命令参数的含义为:FILENAME:密码文件名;PASSWORD:设置INTERNAL/SYS帐号的口令;MAX_USERS:密码文件中可以存放的最大用户数 对应于允许以SYSDBA/SYSOPER权限登录数据库的最大用户数 由于在以后的维护中 若用户数超出了此限制 则需要重建密码文件 所以此参数可以根据需要设置得大一些 有了密码文件之后 需要设置初始化参数REMOTE_LOGIN_PASSWORDFILE来控制密码文件的使用状态 ·设置初始化参数REMOTE_LOGIN_PASSWORDFILE:在Oracle数据库实例的初始化参数文件中 此参数控制着密码文件的使用及其状态 它可以有以下几个选项:NONE:指示Oracle系统不使用密码文件 特权用户的登录通过 *** 作系统进行身份验证;EXCLUSIVE:指示只有一个数据库实例可以使用此密码文件 只有在此设置下的密码文件可以包含有除INTERNAL/SYS以外的用户信息 即允许将系统权限SYSOPER/SYSDBA授予除INTERNAL/SYS以外的其他用户 SHARED:指示可有多个数据库实例可以使用此密码文件 在此设置下只有INTERNAL/SYS帐号能被密码文件识别 即使文件中存有其他用户的信息 也不允许他们以SYSOPER/SYSDBA的权限登录 此设置为缺省值 在REMOTE_LOGIN_PASSWORDFILE参数设置为EXCLUSIVE SHARED情况下 Oracle系统搜索密码文件的次序为:在系统注册库中查找ORA_SID_PWFILE参数值(它为密码文件的全路径名);若未找到 则查找ORA_PWFILE参数值;若仍未找到 则使用缺省值ORACLE_HOME\DATABASE\PWDSID ORA;其中的SID代表相应的Oracle数据库系统标识符 ·向密码文件中增加 删除用户:当初始化参数REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE时 系统允许除INTERNAL/SYS以外的其他用户以管理员身份从远端或本机登录到Oracle数据库系统 执行数据库管理工作;这些用户名必须存在于密码文件中 系统才能识别他们 由于不管是在创建数据库实例时自动创建的密码文件 还是使用工具ORAPWD EXE手工创建的密码文件 都只包含INTERNAL/SYS用户的信息;为此 在实际 *** 作中 可能需要向密码文件添加或删除其他用户帐号 由于仅被授予SYSOPER/SYSDBA系统权限的用户才存在于密码文件中 所以当向某一用户授予或收回SYSOPER/SYSDBA系统权限时 他们的帐号也将相应地被加入到密码文件或从密码文件中删除 由此 向密码文件中增加或删除某一用户 实际上也就是对某一用户授予或收回SYSOPER/SYSDBA系统权限 要进行此项授权 *** 作 需使用SYSDBA权限(或INTERNAL帐号)连入数据库 且初始化参数REMOTE_LOGIN_PASSWORDFILE的设置必须为EXCLUSIVE 具体 *** 作步骤如下:创建相应的密码文件;设置初始化参数REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;使用SYSDBA权限登录: CONNECT　SYS/internal_user_passsword　AS　SYSDBA;启动数据库实例并打开数据库;创建相应用户帐号 对其授权(包括SYSOPER和SYSDBA): 授予权限:GRANT　SYSDBA　TO　user_name;收回权限:REVOKE　SYSDBA　FROM　user_name;现在这些用户可以以管理员身份登录数据库系统了;·使用密码文件登录:有了密码文件后 用户就可以使用密码文件以SYSOPER/SY lishixinzhi/Article/program/Oracle/201311/17833

以上就是关于Oracle数据库入门之函数/类型全部的内容，包括:Oracle数据库入门之函数/类型、oracle登录数据库的命令、Oracle安全全程接触（完整版）二等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！