oracle基础_字符_数字_日期_转换-函数

关键字: orcale基础 

一、关系数据库的一些概念 
　　 
　1、主键的值一般不可以改变 
　　 
　2、外键：指向另一个表或本表的主键或唯一键的字段。外键的值一定要和某一主键相同，或者为空。 
　　 
　3、数据库对像：表，视图，序列，索引，同义词，程序（进程，函数，sql和pl/sql数据）4、sql command 类别 
　　 
　　data retrieval数据检索:select 
　　data manipulationlanguage (DML)数据操作语言:insert,update,delete 
　　data definition language (DDL)数据定义语言：create,alter,drop,rename,truncate 
　　transaction control事务控制:commit,rollback,savepoint 
　　data control language（DCL）数据控制语言：grant,revoke 
　　DCL 和DDL命令的执行会导致一次隐式提交，之前未提交的操作（包括DML 命令）都会提交写入日志文件，并在适当地时候写入数据文件。 
　　 
　二、SQL的语法 
　　 
　1) 连接号：|| 
　　 
　2) 把两个字符连接起来 
　　eg:select game_card_type_id||name from game_card_type; 
　　 
　3) select distinct dept_id,title from emp： 对多个字段的唯一 
　　 
　4) order by desc(降序) 
　　order by asc(升序) 
　　 
　5) where column is (not) null 
　　 
　6) like ‘_a%’　 _表示一个字符。％表示多少字符 
　　like ‘%x\_y%’ escape ‘\’:显示包括x_y的字符 
　　 
　7) where table1.column(+)=table2.column 
　　place the operator on the side of the join where there is no value to join to. 
把(+)放在可能没有值的位置. 
　　 
　8) 联接类型: 
　　equijoin:等式查询 
　　non_equijoin:不等式查询 
　　self:自己和自己建立关联 
　　out join:where a.column=b.column(+) 
　　可以用的操作符是：’=’，’and’，不可以用’or’，’in’ 
　　 
　9)　COUNT 函数所用的列包含空值时，空值行被忽略。 
　　 
　10)　where 后的in | any | all 的区别 
　　in : 等于子查询的任何一个数 
　　any : 与子查询的每一个值相比,只要比其中一个大（小）就可以了 
　　all : 与子查询的所有值相比要比所有的的都大（小） 
　　!=ALL作用跟NOT IN 一样 
　　 
　三、SQL*PLUS的环境(可以在glogin.sql中定义初始参数) 
　　 
　　1) START 命令用以执行一个已储存的文件,等同于@ 
　　 
　　2) SAVE命令用以创建一个文件 
　　 
　　3) EDIT命令用以调用编辑器编辑已存文件的内容 
　　 
　　4) CHANGE 是SQL*Plus的编辑命令，用于在当前一行把旧文段转换为新文段 
　　 
　　5) DEL 命令用以删除文本中一行或多行文字 
　　 
　　6) SPOOL命令用以把查询结果储存在一个已有文件中，注意与SAVE区别 
　　 
　　7) GET命令用以一个文件的内容写进一个SQL块中 
　　 
　　8) SPOOL OUT命令用以把文件的结果发送到系统打印机。 
　　 
　　9) set pause off/on:设置页面的滚动。按enter看下一页。 
　　 
　　10) PAGESIZE：指定每页显示的行的数值， 
　　 
　　11) LONG：设置LONG类型数据显示的最大宽度 
　　 
　　12) FEEDBACK：设置查询返回记录的最大值 
　　 
　　13) DESCRIBE :用于显示表和视图的结构，同义词，或指定函数和过程的详述。 
　　 
　　14) Timing：可以看到语句执行的时间 
　　 
　　15) Autotrace：可以看到sql的执行计划，sysdba执行/home/oracle/product/9.2.0/sqlplus/admin/plustrce.sql脚本，而且必须把plustrace角色赋给执行用户。执行用户必须运行/home/oracle/product/9.2.0/rdbms/admin/utlxplan.sql 
　　 
　　16) 在各种数据类型中，只有NUMBER数据类型的默认显示是靠右对齐的，而CHAR, DATE和VARCHAR2是靠左对齐的 
　　 
　　17) QUIT 是SQL*PLUS命令，用以结束一个SQL*PLUS的对话。 
　　 
　　18) ttitle: 
　　ttitle ‘selina’ 设select 的结果的抬头为selina 
　　tti ：显示ttitle的状态 
　　tti off/on 
　　btitle ‘end’ 设结果的尾部 
　　 
　　19) Column： 
　　 
　　a) column name heading ‘名字’format a15 
　　 
　　b) column id justify left format 999999 
　　 
　　c) column start_date format a9 null ‘not hired’//当字段为空的时候则显示成not hired 
　　 
　　d) column :显示所有的column设置 
　　 
　　e) column columnname:显示某一个字段的设置 
　　 
　　f) clear column :清除所有column设置 
　　 
　　g) column columnname clear:清除某一字段的设置 
　　 
　　h) 以上的column可用col代替.clear可以用cle代替 
　　 
　　13）定义变量 
　　 
　　用&：由用户输入变量值，此变量可以存在于where后，做为整个查询语句的变量。也可以在order by 后。做为字段的变量。也可以放在select 后，做为字段或表达式的变量。 
　　 
　　用&&：如果多个地方引用此变量。。只用输入一次 
　　 
　　SQL> SELECT　empno, ename, job, &&column_name 
　　FROM　emp 
　　ORDER BY &column_name; 
　　 
　　accept：由用户输入变量值 
　　accept 变量名 datatype prompt ‘告诉用户需要输入的信息：’ hide 
　　引用的时候：&变名 
　　define(undefined)：一开始就定义变量值 
　　 
　四、函数： 
　　 
　　1、字符函数 
　　 
　　.lower:把字符转成小写 
　　.upper:把字符转成大写 
　　.initcap:把单词的第一个字母变成大写 
　　.concat:连接字符　concat(‘good’,’morning’)=goodmoring 
　　.SUBSTR (column\expression, m[,n]) 用于对字符串进行截取操作，从第m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算。 
　　eg:substr(‘string’,1,3) =str 
　　substr(‘string’-3,3)=ing 
　　.INSTR('String', 'r')=3 返回子字符串所在的位置. 
　　.LPAD(sal,10,'*') =*******sal 函数将左边的字符串填充一些特定的字符 
　　.length: 用于返回表达式中的字符数，注意返回的是NUMBER。 
　　.NVL(expression1, expression2) 
　　 NVL 函数用以把一个空值转换为一个实值,如： 
　　 NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0。 
　　 如果两个字段类型不同必须进行转换。 
　　.Min():返回最小值。。如果是字符。。A　 
　 
　　2、数学函数　　 
　　.round:四舍五入 
　　 round(2.566,2)=2.27 
　　 round(45,-1)=50 
　　.trunc: 截断 
　　 trunc(2.566,2)=2.56 
　　 trunc(45,-1)=40 
　　.mod: 
　　 mod(m,n):m-n*flood(m/n)　　 file://flood是取整数 
　　 
　　3、日期函数:　　 
　　a) months_between(date1,date2)：算date1和date2之间的月的数量,可以是小数可以是负数 
　　months_between(’01-sep-95’,’11-jan-94’)=1.9774194 
　　 
　　b) add_months(date,n)：为date加上N个月，N只可以是整数 
　　 
　　c) next_date(date,’char’)：查找date的下一个星期N 
　　next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95 
　　 
　　d) last_day(date):查找date月的最后一天。 
　　 
　　e) rount(date)：把日期四舍五入　 
　　 rount(25-MAY-95’,’MONTH’)=01-JUN-95　　 
　　 rount(25-MAY-95’,’YEAR’)=01-JAN-95 
　　 
　　f) trunc(date)：把日期截断　　 
　 trunc (25-MAY-95’,’MONTH’)=01-MAY-95　　 
　　 trunc (25-MAY-95’,’YEAR’)=01-JAN-95 
　　 
　　g) 日期中RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作 
　　RR　 YY 
　　1995 27-oct-95 1995 1995 
　　1995 27-oct-17 2017 1917 
　　2001 27-oct-17 2017 2017 
　　2001 27-oct-95 1995 2095 
　　用法：select to_char(sysdate, 'YY') from dual; 
　　select to_char(to_date('95-11-27', 'RR-MM-DD'), 'YYYY-MM-DD') from dual; 
　　select to_char(to_date('95-11-27', 'YY-MM-DD'), 'YYYY-MM-DD') from dual; 
　　 
　　4、转换函数 
　　1)TO_CHAR： 
　　TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0 
　　TO_CHAR(total,’fm$999999’) 
　　 
　　如果想转成$0.25,那就要写成fm$9999990.99 
　　 
　　可以把日期转换成字符 
　　TO_CHAR(log_time,’MM/YY’) 
　　TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’) 
　　 
　　具体格式如下 
　　HH24:MI:SS AM-----------15:24:32 pm 
　　DD’’of’’MONTH-----------12 of MAY 
　　Ddspth------------------------fourteenth 
　　Ddsp--------------------------fourteen 
　　ddth---------------------------4th 
　　YYYY-----------------------1978 
　　MM-----------------------------12 
　　MONTH-------------------------MAY 
　　 
　　5、group 函数avg,count,max,min,stddev,sum,variance 
　　 
　五、数据字典 
　　 
　　用户表：由用户创建，包含用户的内容 
　　 
　　数据字典：由系统建立，包含数据库的信息 
　　 
　　前缀: 
　　USER_ ：由用户创建,显示用户拥有的所有对象。 
　　 
　　ALL_ ：由受权的用户访问, 用户可以访问的对象名。 
　　 
　　DBA_ ：由受了DBA权限的人访问，显示数据库的所有对象。 
　　 
　　V$ ：由受了DBA权限的人访问，显示数据字典数据库服务器性能信息。通常是DBA用于显示系统的统计表和动态性能表。 
　　 
　　数据字典 
　　 
　　DICTIONARY数据字典提供用户可以访问的数据字典表和视图的描述。 
　　USER_OBJECTS显示用户拥有的对象。 
　　USER_VIEWS显示用户拥有的视图。 
　　USER_TABLES显示用户拥有的表。 
　　ALL_TABLES显示用户可以访问的表。 
　　ALL_VIEWS显示用户可以访问的视图。 
　　USER_CATALOG显示用户拥有的所有表，视图，同义词和序列。 
　　USER_CONS_COLUMNS显示带约束的列。 
　　DBA_CONS_COLUMNS显示数据库里的所有表，视图和同义词。 
　　USER_TAB_PRIVS_MADE：本用户赋给别的用户赋予权限的表 
　　USER_TAB_PRIVS_RECD：其他用户给本用户赋予权限的表 
　　USER_COL_PRIVS_MADE：本用户赋给别的用户赋予权限的字段 
　　USER_COL_PRIVS_RECD：其他用户给本用户赋予权限的字段 
　　ROLE_SYS_PRIVS:有什么系统权限赋给role 
　　ROLE_TAB_PRIVS:有什么关于表的权限赋给role 
　　USER_ROLE_PRIVS:role和用户的对应表 
　　 
　　常用的表 
　　 
　　user_objects：用户对象表（存储用户的所有对象）存储以下的类型的数据 
　　Selina Sql>select distinct object_type from user_objects; 
　　INDEX 
　　LOB 
　　PACKAGE 
　　PACKAGE BODY 
　　PROCEDURE 
　　SEQUENCE 
　　SYNONYM 
　　TABLE 
　　TRIGGER 
　　VIEW 
　　User_catalog:用户类表，存储以下的类型的数据 
　　Selina Sql>select distinct table_type from user_catalog; 
　　SEQUENCE 
　　SYNONYM 
　　TABLE 
　　VIEW

Oracle 字符函数

lpad 
在Oracle/PLSQL中,lpad函数将左边的字符串填充一些特定的字符, 
其语法格式如下： 
lpad( string1, padded_length, [ pad_string ] ) 
其中string1是需要粘贴字符的字符串 
padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length; 

pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。 
例如： 
lpad('tech', 7); 将返回' tech' 
lpad('tech', 2); 将返回'te' 
lpad('tech', 8, '0'); 将返回'0000tech' 
lpad('tech on the net', 15, 'z'); 将返回 'tech on the net' 
lpad('tech on the net', 16, 'z'); 将返回 'ztech on the net' 






nvl 
通过查询获得某个字段的合计值，如果这个值为null将给出一个预设的默认值 

例如： 

select nvl(sum(t.dwxhl),1) 

from tb_jhde t 

就表示如果sum(t.dwxhl) = NULL 就返回 1 


另一个有关的有用方法 

declare i integer 

select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1这样就可以把获得的合计值存储到变量 
i中，如果查询的值为null就把它的值设置为默认的1 





通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值 
select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1 
这里关心的nvl的用法,nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value 

另一个有关的有用方法 
declare 
i integer 
select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1 
这样就可以把获得的合计值存储到变量i中，如果查询的值为null就把它的值设置为默认的1 



Upper() 
功能将字符串中的小写字母转换为大写字母。 
语法Upper( string ) 
参数string：要将其中的小写字母转换为大写字母的字符串返回值String。函数执行成功时返回将小写字母转换为大写字母后的字符串，发生错误时返回空字符串（""）。如果string参数的值为NULL，Upper()函数返回NULL。 
eg:select upper('AaCdD') from dual; 
UPPER('AACDD') 




Replace函数 
描述 

返回一个字符串，该字符串中指定的子字符串已被替换成另一子字符串，并且替换发生的次数也是指定的。 

语法 

Replace(expression, find, replacewith[, start[, count[, compare]]]) 

Replace函数语法有如下几部分： 

部分 描述 
expression 必需的。字符串表达式，包含要替换的子字符串。 
find 必需的。要搜索到的子字符串。 
replacewith 必需的。用来替换的子字符串。 
start 可选的。在表达式中子字符串搜索的开始位置。如果忽略，假定从1开始。 
count 可选的。子字符串进行替换的次数。如果忽略，缺省值是 -1，它表明进行所有可能的替换。 
compare 可选的。数字值，表示判别子字符串时所用的比较方式。关于其值，请参阅“设置值”部分。 


设置值 

compare参数的设置值如下： 

常数 值 描述 
vbUseCompareOption -1 使用Option Compare语句的设置值来执行比较。 
vbBinaryCompare 0 执行二进制比较。 
vbTextCompare 1 执行文字比较。 
vbDatabaseCompare 2 仅用于Microsoft Access。基于您的数据库的信息执行比较。 


返回值 

Replace的返回值如下： 

如果 Replace返回值 
expression长度为零 零长度字符串("")。 
expression为Null 一个错误。 
find长度为零 expression的复本。 
replacewith长度为零 expression的复本，其中删除了所有出现的find 的字符串。 
start > Len(expression) 长度为零的字符串。 
count is 0 expression.的复本。 


说明 

Replace函数的返回值是一个字符串，但是，其中从start所指定的位置开始，到expression字符串的结尾处的一段子字符串已经发生过替换动作。 
并不是原字符串从头到尾的一个复制。 

Oracle 数学函数

oracle trunc()函数的用法. 
1.TRUNC函数为指定元素而截去的日期值。 
其具体的语法格式如下：TRUNC（date[,fmt]） 

其中： 
date 一个日期值 
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去 

下面是该函数的使用情况： 
select trunc(sysdate,'mm')from dual --2007-1-1:当月第一天 
select trunc(sysdate,'yy')from dual --2007-1-1:当年第一天 
select trunc(sysdate,'dd')from dual --2007-1-14:当天 
select trunc(sysdate,'day')from dual --2007-1-15:当周第一天 
select trunc(sysdate)from dual --2007-1-15:当天 
说明:trunc(sysdate)输出的格式为YYYY-MM-DD,但它是把时间四舍五入到了当日的零点, 
即:to_char(trunc(sysdate),'YYYY-MM-DD HH24:mi:ss pm|am') = YYYY-MM-DD 00:00:00 上午 

比较下面三个的不同: 
select last_day(sysdate)+2 from dual; --2007-2-2 19:07:52 
select (last_day(sysdate)+2)+10/24 from dual; --2007-2-3 5:07:18 
select trunc(last_day(sysdate)+2)+10/24 from dual; --2007-2-2 10:00:00 

TRUNC（TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)）=’24-Nov-1999 12:00:00 am’ 
TRUNC（TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)） =’24-Nov-1999 08:00:00 am’ 

2.TRUNC(for number) 
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。 
其具体的语法格式如下TRUNC（number[,decimals]） 

其中： 
number 待做截取处理的数值 
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分 
下面是该函数的使用情况： 
TRUNC（89.985，2）=89.98 
TRUNC（89.985）=89 
TRUNC（89.985，-1）=80 
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。 



select nvl(alloffinum,offinum) 网元名称, 
sum(decode(checkflag,'A',1,0)) 未制作, sum(decode(checkflag,'B',1,0)) 制作中, 
sum(decode(checkflag,'C',1,0)) 成功, sum(decode(checkflag,'E',1,0)) 人工成功, 
sum(decode(checkflag,'G',1,0)) 成功回笼,sum(decode(checkflag,'I',1,0)) 人工回笼, 
sum(decode(checkflag,'F',1,0)) 退单回笼,sum(decode(checkflag,'H',1,0)) 失败, 
sum(decode(checkflag,'D',1,0)) 退单, sum(decode(checkflag,'K',1,0)) 保护, 
sum(decode(checkflag,'J',1,0)) 禁止 from sheet_control 
where areano='0378' and stationno like '%' 
and intime >=trunc(sysdate) 
group by nvl(alloffinum,offinum) 




round函数 
round函数用于求出并返回最接近给定参数value值的整数值。如果两个整数都和value值等距离，那么round函数将返回较大的那个整数值。 
如果value是一个整数，则返回的就是value本身。round函数的语法格式为： 

float.round(value) 

如果参数value的值不是有效的数，那么round函数就会返回无效值invalid。 

使用round函数的例子如下： 



var a = float.round(3.5); // 计算结果为a = 4；4和3与3.5等距离，取较大的数4。 

var b = float.round(-3.5); // 计算结果为b = -3 

var c = float.round(0.5); // 计算结果为c = 1；1和0与0.5等距离，取较大的数1。 

var d = float.round(-0.5); // 计算结果为d = 0 






在oracle中，用法：mod（number1，number2） 
描述 
两个数值相除并返回其余数。 
语法 
result = number1 Mod number2 
Mod 运算符的语法有以下部分： 

部分 描述 
result 任意数值变量。 
number1 任意数值表达式。 
number2 任意数值表达式。 


说明 
模数或余数。运算符执行 number1 除以 number2 操作（浮点数四舍五入为整数）并只返回余数作为 result。例如，在下面的表达式中， A（为 result）等于 5。 

A = 19 Mod 6.7 

如果任一表达式为 Null，则 result 也为 Null。任一表达式为 Empty 时按 0 来处理。 
-------------------------------------------------------------------------------- 
0.5 mod 8 =0 
1.5 mod 8 =2 
0.6 mod 8=1 
1.6 mod 8=2 

4.5 mod 8=4 
6.5 mod 8=6 
6.6 mod 8=7 
7.4 mod 8=7 
7.5 mod 8=0 

8.4 mod 8=0 
8.5 mod 8=0 
8.6 mod 8=1 
被除数四舍五入之后再取模 
Round 函数都是采用 Banker's rounding（银行家舍入）算法，即四舍六入五取偶 
四舍六入五取偶 

round(3.5) mod round(8) 等于 4 mod 8 
round(4.5) mod round(8) 等于 4 mod 8 
round(5.5) mod round(8) 等于 6 mod 8 

Oracle 日期函数

a) months_between(date1,date2)：算date1和date2之间的月的数量,可以是小数可以是负数 
　　months_between(’01-sep-95’,’11-jan-94’)=1.9774194 

b) add_months(date,n)：为date加上N个月，N只可以是整数 
　　 
c) next_date(date,’char’)：查找date的下一个星期N 
　　next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95 
　　 
d) last_day(date):查找date月的最后一天。 
　　 
e) rount(date)：把日期四舍五入 
　　 
f) rount(25-MAY-95’,’MONTH’)=01-JUN-95 
　　 
g) rount(25-MAY-95’,’YEAR’)=01-JAN-95 
　　 
h) trunc(date)：把日期截断 
　　 
i) trunc (25-MAY-95’,’MONTH’)=01-MAY-95 
　　 
j) trunc (25-MAY-95’,’YEAR’)=01-JAN-95 

k) 日期中RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作 
　　RR　 YY 
　　1995 27-oct-95 1995 1995 
　　1995 27-oct-17 2017 1917 
　　2001 27-oct-17 2017 2017 
　　2001 27-oct-95 1995 2095 
　　用法：select to_char(sysdate, 'YY') from dual; 
　　select to_char(to_date('95-11-27', 'RR-MM-DD'), 'YYYY-MM-DD') from dual; 
　　select to_char(to_date('95-11-27', 'YY-MM-DD'), 'YYYY-MM-DD') from dual; 

Oracle 转换函数

TO_CHAR： 
　　TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0 
　　TO_CHAR(total,’fm$999999’) 
　　 
　　如果想转成$0.25,那就要写成fm$9999990.99 
　　 
　　可以把日期转换成字符 
　　TO_CHAR(log_time,’MM/YY’) 
　　TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’) 
　　 
　　具体格式如下 
　　HH24:MI:SS AM-----------15:24:32 pm 
　　DD’’of’’MONTH-----------12 of MAY 
　　Ddspth------------------------fourteenth 
　　Ddsp--------------------------fourteen 
　　ddth---------------------------4th 
　　YYYY-----------------------1978 
　　MM-----------------------------12 
　　MONTH-------------------------MAY