Oracle PL/SQL的数据类型和常用函数

1）PL/SQL数据类型

名称	类型	说明
NUMBER	数字型	能存放整数值和实数值，并且可以定义精度和取值范围
BINARY_INTEGER	数字型	可存储带符号整数，为整数计算优化性能
DEC	数字型	NUMBER的子类型，小数
DOUBLE PRECISION	数字型	NUMBER的子类型，高精度实数
INTEGER	数字型	NUMBER的子类型，整数
INT	数字型	NUMBER的子类型，整数
NUMERIC	数字型	NUMBER的子类型，与NUMBER等价
REAL	数字型	NUMBER的子类型，与NUMBER等价
SMALLINT	数字型	NUMBER的子类型，取值范围比INTEGER小
VARCHAR2	字符型	存放可变长字符串，有最大长度
CHAR	字符型	定长字符串
LONG	字符型	变长字符串，最大长度可达32,767
DATE	日期型	以数据库相同的格式存放日期值
BOOLEAN	布尔型	TRUE OR FALSE
ROWID	ROWID	存放数据库的行号

2.常用函数

　　 1)处理字符的函数
　　 ||
　　 CONCAT ---并置运算符。
　　 格式∶CONCAT(STRING1, STRING2)
　　 例：’ABC’|| ’DE’=’ABCDE’
　　 CONCAT(‘ABC’,’DE’) =’ABCDE’
　　
　　 ASCII---返回字符的ASCII码。
　　 例：ASCII(‘A’) = 65
　　
　　 CHR---返回指定ASCII码的字符。
　　 例：CHR(65) = ‘A’
　　
　　 INSTR---搜索子串位置
　　格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]])
　　 例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
　　
　　 INITCAP---将字符串每个单词首字母均变为大写
　　例: INITCAP(‘this is a test’)=’ This Is A Test’’
　　
　　 LENGTH----计算串长
　　 格式∶ LENGTH(string)
　　
　　 RPAD,LPAD---右填充、左填充。默认为填充空格。
　　 格式: RPAD(字符串 , 字符个数 , 填充字符)
　　例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
　　
　　 LTRIM,RTRIM-----左右截断。默认为删除空格。
　　格式∶ LTRIM(STRING[,’SET’])
　　例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
　　
　　 LOWER----将字符串转换为小写
　　 格式∶LOWER(string)
　　
　　UPPER---将字符串转换为大写
　　 格式∶UPPER(string)
　　
　　SUBSTR----提取子串。START为正数时从左开始 、为负数时从右开始
　　格式∶ SUBSTR(STRING , START [ , COUNT])
　　例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
　　
　　 REPLACE---搜索指定字符串并替换
　　 格式∶REPLACE(string , substring , replace_string)
　　 例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
　　
　　TRIM---删除字符串前缀或尾随字符
　　格式∶TRIM( [LEADING | TRAILING ｜BOTH] [ trimchar FROM ] string)
　　 LEADING---删除前缀字符
　　 TRAILING---删除后缀字符
　　 BOTH---前后缀字符均删除(默认方式)
　　 Trimchar---指定删除的字符
　　
　　注：INSTR,LENGTH,SUBSTR加Ｂ时针对字节．
　　2)处理数字的函数
　　LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
　　格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
　　例∶ LEAST(1,2,5,-10,9)= -10
　　
　　SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
　　格式∶SIGN(value)
　　
　　 CELL---返回大于等于特定值的最小整数
　　 格式∶CELL(value)
　　 例∶ CELL(-10,9)= -10
　　3）处理日期
　　 SYSDATE---系统时间。精确至秒
　　
　　 ADD_MONTH—加减月份。numvalue为负数时减去相应月份
　　 格式: ADD_MONTH (data1,numvalue)
　　
　　MONTHS_BETWEEN---返回两日期之间的月数,当data1
　　 格式: MONTHS_BETWEEN(data1,data2)
　　
　　 LAST_DAY---返回指定日期的最后一天。
　　 格式∶LAST_DAY(date)
　　
　　 TRUNC---将日期按照format格式截短，缺省为DD(不是四舍五入)
　　　　格式：TRUNC（date, format）
　　例∶
　　 TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
　　
　　4）分组函数
　　　　　　　返回基于多个行的单一结果．
　　　　　　　常用函数：
　　 AVG---求平均值
　　 COUNT---返回查询的行数
　　 MAX---返回查询列的最大值
　　 MIN---返回查询列的最小值
　　 SUM---返回查询列的总和
　　 MAX, MIN常与GROUP BY配套使用
　　5）转换函数
　　 TO_DATE---转换字符串为日期型
　　格式∶ TO_DATE(STRING[,’FORMAT’])
　　
　　TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一．其FORMAT格式多种多样
　　格式∶TO_CHAR(DATE [,’FORMAT’])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　P836 日期格式元素
　　
　　 TO_NUMBER---转换字符串为数字
　　 格式∶TO_NUMBER(string [ , format])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　
　　
　　6)其他
　　DECODE---IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
　　格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
　　例∶ DECODE(VALUE,1,100,2,300,500)
　　当VALUE=1时返回100
　　当VALUE=2时返回300
　　否则返回500
　　
　　DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
　　当VALUE<100时返回-10
　　当VALUE>100时返回10
　　当VALUE=100时返回0
　　
　　 SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
　　SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
　　SUM(DECODE(EST_INT_KEY,77773,1,0)) C
　　FROM PMS_BLK
　　
　　 NVL---空值置换
　　 格式: NVL(value,替换值)
　　例: NVL(value,’nullvalue’)当value为NULL值时返回nullvalue否则返回value的值
　　
　　VSIZE---以字节为单位返回数据类型尺寸
　　格式∶VSIZE(数据类型)
　　例∶ VSIZE(SYSDATE)=8
　　
　　USER---得到当前用户名
　　
　　ROWNUM--- oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
　　例∶ select rownum,month,sell from sale where rownum=1
　　 或select rownum,month,sell from sale where rownum<2(返回第一条记录)
　　 返回rownum在4—10之间的记录
　　select rownum,month,sell from sale where rownum<10
　　minus
　　select rownum,month,sell from sale where rownum<5;
　　
　　 以下均错误:
　　select rownum,month,sell from sale where rownum=2(1以上)
　　select rownum,month,sell from sale where rownum>2
　　
　　ROWID---数据行在物理磁盘上的物理地址，Oracle通过ROWID来定位数据的具体位置，这是存取表中数据的最快的方法
　　
　　RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
　　SQLCODE---返回ORACLE错误号。
　　SQLERRM---返回ORACLE错误信息。