创建函数的的语法：
create [or replace] function
function_name [(param1,param2)] --函数名、参数
return param_type is|as --返回值类型
[local declarations] --变量声明
begin
executable statements;
return result; --返回值
exception
exception handlers;
end function_name
9.1.TO_CHAR
将一个数值转换成与之等价的字符串.如果不指定格式,将转换成最简单的字符串形式.如果为负数就在前面加一个减号.
9.1.1.TO_CHAR(number[,format])
Oracle为数值提供了很多格式,下表列出了部分可接受的格式:
元素 描述 示例 值 结果
9 返回指定位数的数值,前面为0,显示为空格 99999 784 ‘ 784’
9 返回指定位数的数值,前面为0,显示为空格 99999 -578 ‘ -578’
9 返回指定位数的数值,前面为0,显示为空格 99999 1234567 ‘######’
9 返回指定位数的数值,前面为0,显示为空格 99999 45.895 ‘ 46’
9 插入小数点 9999.99 784 ‘784.00’
9 插入小数点 9999.99 45.3482 ’45.35’
9 在结果串的指定位置插入逗号 9,999,999 784 ‘ 784’
9 在结果串的指定位置插入逗号 9,999,999 1234567 ‘1,234,567’
9 在结果串的指定位置插入逗号 9,999,999 0.44 ‘ 0’
$ 返回值前面加一个元符号 $99,999 784 ‘$784’
$ 返回值前面加一个元符号 $99,999 -578 ‘-$578’
B 结果的整数部分如果是0,就显示成空格 B9999.9 784 ‘ 784.0’
B 结果的整数部分如果是0,就显示成空格 B9999.9 0.44 ‘ .4’
S 返回 有正负号的数值 S9999 784 ‘+784’
EEEE 以科 计数法表示数值 9.9EEEE 45 ‘ 4.5E+01’
EEEE 以科 计数法表示数值 9.9EEEE 0.0666 ‘ 6.7E-02’
9.1.2.TO_CHAR(date[,format])
用途:
将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,
用户可以用它们的组合来表示最终的输出格式.唯一限制就是最终的掩码不能超过22个字符.
格式 意义
D 用数字表示星期几(1,2,3,4,5,6,7)
DY 用三个字符表示星期几的缩写形式(MON)
DAY 星期几的完整表示(MONDAY)
DD 用数字表示一月中的几号
DDD 用数字表示年份中的天数
W 一月中的星期数
WW 一年中的星期数
MM 用数字表示月数
MON 用三个字母表示月的缩写(APR)
MONTH 月的完整英文表示(FEBRUARY)
Y 年份中的最后一个数字(9)
YY 年份中的最后二个数字(99)
YYY 年份中的最后三个数字(999)
YYYY 年份用四个数字表示(1999)
YEAR 年份的字母全部拼写(NINETEEN-NINETY-NINE)
AM或PM 午前或午后表示符
HH 用小时表示日期
MI 分钟表示小时
SS 秒钟表示分钟
SSSS 自午夜以来的秒数(这个数字经常在0-86399之间)
9.1.3. TO_DATE(string,format)
用途:
根据给定的格式将一个字符串转换成Oracle的日期值.
该函数的主要用途是用来验证输入的日期值.在应用程序中,
用户必须验证输入日期是否有效,如月份是否在1~12之间和日期中的天数是否在指定月份的天数内.
9.2. TO_NUMBER(string[,format])
用途:
该函数将一个字符串转换成相应的数值.对于简单的字符串转换数值(例如几位数字加上小数点).格式是可选的.
9.3. ADD_MONTHS(date,number)
用途:
在日期date上加指定的月数,返回一个新日期.如果给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,如果是小数,
正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.
--例如：
SELECT TO_CHAR(ADD_MONTHS(sysdate,1),'DD-MON-YYYY') "Next month";
FROM dual
Next month
-----------
19-FEB-2000
9.4. LAST_DAY(date)
用途: 返回日期date所在月份的最后一天的日期.
--例如:
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL
SYSDATE Last Days Left
--------- --------- ----------
19-JAN-00 31-JAN-00 12
9.5. MONTHS_BETWEEN(date1,date2)
用途:返回两个日期之间的月份.如果两个日期月份内的天数相同(或者都是某个月的最后一天),返回值是整数.否则,返回值是小数,
每于1/31月来计算月中剩余天数.如果第二个日期比第一个日期还早,则返回值是负数.
--例如:
SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'), TO_DATE('01-01-1992', 'MM-DD-YYYY')) "Months"
FROM DUAL
Months
----------
1.03225806
--例如：
SELECT MONTHS_BETWEEN(TO_DATE('02-29-1992', 'MM-DD-YYYY'), TO_DATE('01-31-1992', 'MM-DD-YYYY')) "Months" FROM DUAL
Months
----------
1
9.6.NEXT_DAY(date,day)
用途:该函数返回日期date指定若天后的日期.注意:参数day必须为星期,可以星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分别表示星期日到星期六.例如,查询返回本月最后一个星期五的日期.
--例如:
SELECT NEXT_DAY((last_day(sysdate)-7),'FRIDAY')
FROM dual
NEXT_DAY(
---------
28-JAN-00
9.7. NEXT_DAY(date[,format])
用途:该函数把一个日期四舍五入到最接近格式元素指定的形式.如果省略format,只返回date的日期部分.
--例如,如果想把时间(24/01/00 14:58:41)四舍五入到最近的小时.下表显示了所有可用格式元素对日期的影响.
Format Element Result
SS 24/01/00 14:58:41
MI 24/01/00 14:59:00
HH 24/01/00 15:00:00
DD 25/01/00 00:00:00
MM 01/02/01 00:00:00
YY 01/01/00 00:00:00
CC 01/01/00 00:00:00
--例如:
SELECT to_char(ROUND(sysdate,'HH'),'DD-MON-YY HH24:MI:SS')
FROM dual
TO_CHAR(ROUND(SYSDATE,'HH'),'DD-MON-YYHH24:MI:SS')
-----------------------------------------------------------------
24-JAN-00 15:00:00
9.8. TRUNC(date[,format])
用途:TRUNC函数与ROUND很相似,它根据指定的格式掩码元素,只返回输入日期用户所关心的那部分,与ROUND有所不同,它删除更精确的时间部分,而不是将其四舍五入.
--例如:
SELECT TRUNC(sysdate)
FROM dual
TRUNC(SYS
---------
24-JAN-00
9.9. FLOOR 求两个日期之间的天数用；
select floor(sysdate - to_date('20080805','yyyymmdd')) from dual;
9.10. ASCII(character)
用途:返回指定字符的ASCII码值.如果为字符串时,返回第一个字符的ASCII码值.
--例如:
SELECT ASCII('Z')
FROM dual
ASCII('Z')
----------
90
9.11. CHR(number)
用途:该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符.
--例如:
SELECT CHR(65)||'BCDEF'
FROM dual
CHR(65
------
ABCDEF
9.12. CONCAT(string1,string2)
用途:该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符(||).
--例如:
SELECT CONCAT(‘This is a’,’ computer’)
FROM dual
CONCAT('THISISA',
------------------
This is a computer
它也可以写成这样:
SELECT ‘This is a’||’computer’
FROM dual
'THISISA'||'COMPUT'
------------------
This is a computer
这两个语句的结果是完全相同的,但应尽可能地使用||操作符.
9.13. INITCAP(string)
用途:该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母.
--例如:
SELECT INITCAP(first_name||'.'||last_name)
FROM employee
WHERE department_id=12
INITCAP(FIRST_NAME||'.'||LAST_N
-------------------------------
Chris.Alberts Matthew.Fisher Grace.Roberts Michael.Douglas
9.14. INSTR(input_string,search_string[,n[,m]])
用途:该函数是从字符串input_string的第n个字符开始查找搜索字符串的第m次出现,如果没有找到搜索的字符串,函数将返回0.如果找到,函数将返回位置.
--例如:
SELECT INSTR('the quick sly fox jumped over the lazy brown dog','the',2,1) FROM dual
INSTR('THEQUICKSLYFOXJUMPEDOVERTHELAZYBROWNDOG','THE',2,1)
----------------------------------------------------------
9.15. INSTRB(input_string,search_string[,n[,m]])
用途:该函数类似于INSTR函数,不同之处在于INSTRB函数返回搜索字符串出现的字节数,而不是字符数.在NLS字符集中仅包含单字符时,
INSTRB函数和INSTR函数是完全相同的.
9.16. LENGTH(string)
用途:该函数用于返回输入字符串的字符数.返回的长度并非字段所定义的长度,而只是字段中占满字符的部分.
以列实例中,字段first_name定义为varchar2(15).
--语法:
SELECT first_name,LENGTH(first_name)
FROM employee
FIRST_NAME LENGTH(FIRST_NAME)
--------------- ------------------
JOHN 4
KEVIN 5
9.17. LENGTHB(string)
用途:该函数用于返回输入字符串的字节数.对于只包含单字节字符的字符集来说LENGTHB函数和LENGTH函数完全一样.
9.18. LOWER(string)
用途:该函数将字符串string全部转换为小写字母,对于数字和其它非字母字符,不执行任何转换.
9.19. UPPER(string)
用途: 该函数将字符串string全部转换为大写字母,对于数字和其它非字母字符,不执行任何转换.
9.20. LPAD(string,length[,’set’])
用途:在字符串string的左边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.
--例如:
SELECT first_name,LPAD(first_name,20,' ')
FROM employee
FIRST_NAME LPAD(FIRST_NAME,20,'')
--------------- -----------------------------------------
JOHN JOHN
KEVIN KEVIN
9.21. RPAD(string,length[,’set’])
用途:在字符串string的右边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.
--例如:
SELECT first_name,rpad(first_name,20,'-')
FROM employee
FIRST_NAME RPAD(FIRST_NAME,20,'-')
--------------- -----------------------------------------
JOHN JOHN----------------
KEVIN KEVIN---------------
9.22. LTRIM(string[,’set’])
用途:该函数从字符串的左边开始,去掉字符串set中的字符,直到看到第一个不在字符串set中的字符为止.
--例如:
SELECT first_name,ltrim(first_name,'BA')
FROM employee
WHERE first_name='BARBARA'
FIRST_NAME LTRIM(FIRST_NAM
--------------- ---------------
BARBARA RBARA
9.23. RTRIM(string[,’set’])
用途:该函数从字符串的右边开始,去掉字符串set中的字符,直到看到第一个不在字符串set中的字符为止.具有NULL值的字段不能与具有空白字符的字段相比较.
这是因为空白字符与NULL字符是完全不同的两种字符.该函数的另外一个用途是当进行字段连接时去掉不需要的字符.
9.24. SUBSTR(string,start[,length])
用途:该函数从输入字符串中取出一个子串,从start字符处开始取指定长度的字符串,如果不指定长度,返回从start字符处开始至字符串的末尾.
9.25. REPLACE(string,search_set[,replace_set])
用途:该函数将字符串中所有出现的search_set都替换成replace_set字符串.可以使用该函将字符串中所有出现的符号都替换成某个有效的名字.如果不指定replace_set,则将从字符串string中删除所有的搜索字符串search_set.
--例如:
SELECT REPLACE('abcdefbdcdabc,dsssdcdrd','abc','ABC') FROM dual
REPLACE('ABCDEFBDCDABC',
-----------------------
ABCdefbdcdABC,dsssdcdrd
9.26. TRANSLATE(string,search_set,replace_set)
用途:该函数用于将所有出现在搜索字符集search_set中的字符转换成替换字符集replace_set中的相应字符.注意:如果字符串string中的某个字符没有出现在搜索字符集中.则它将原封不动地返回.如果替换字符集replace_set比搜索字符集search_set小,那么搜索字符集search_set中后面的字符串将从字符串string中删除.
--例如:
SELECT TRANSLATE('GYK-87M','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',9999999999xxxxxxxxxxxxxx)
FROM dual
TRANSL
------
xx-99x
9.27. ABS(number)
用途:该函数返回数值number的绝对值.绝对值就是一个数去掉符号的那部分.
9.28. SQRT(number)
用途:该函数返回数值number的帄方根,输入值必须大于等于0,否则返回错误.
9.29. CEIL(number)
用途:该函数返回大于等于输入值的下一个整数.
9.30. FLOOR(number)
用途:该函数返回小于等于number的最大整数.
9.31. MOD(n,m)
用途:该函数返回n除m的模,结果是n除m的剩余部分.m,n可以是小数,负数.
9.32. POWER(x,y)
用途:该函数执行LOG函数的反操作,返回x的y次方.
9.33. ROUND(number,decimal_digits)
用途:该函数将数值number四舍五入到指定的小数位.如果decimal_digits为0,则返回整数.decimal_digits可以为负数.
9.34. TRUNC(number[,decimal_pluces])
用途:该函数在指定的小数字上把一个数值截掉.如果不指定精度,函数预设精度为0. decimal_pluces可以为负数.
9.35. SIGN(number)
用途:该函数返回number的符号,如果number为正数则返回1,为负数则返回-1,为0则返回0.
9.36. SIN(number)
用途:该函数返回弧度number的正弦值.
9.37. SINH(number)
用途:该函数返回number的返正弦值.
9.38. COS(number)
用途:该函数返回弧度number的三角余弦值.要用角度计算余弦,可以将输入值乘以0.01745转换成弧度后再计算.
9.39. COSH(number)
用途:该函数返回输入值的反余弦值.
9.40. TAN(number)
用途:该函数返回弧度number的正切值.
9.41. TANH(number)
用途:该函数返回数值number的反正切值.
9.42. LN(number)
用途:该函数返回number自然对数.
9.43. EXP(number)
用途:该函数返回e(2.71828183)的number次方.该函数执行自然对数的反过程.
9.44. LOG(base,number)
用途:该函数返回base为底,输入值number的对数. 单行函数:
单行函数中可以对任何数据类型的数据进行操作.
9.45. DUMP(expression[,format[,start[,length]]])
用途:该函数按指定的格式显示输入数据的内部表示.下表列出了有效的格式.
格式代码 描述
8 八进制
10 十进制
16 十六进制
17 单字符
--例如:
SELECT DUMP('FARRELL',16)
FROM dual
DUMP('FARRELL',16)
----------------------------------
Typ=96 Len=7: 46,41,52,52,45,4c,4c
9.46. GREATEST(list of values)
用途:该函数返回列表中项的最大值.对数值或日期来说,返回值是最大值或最晚日期,如果列表中包含字符串,返回值是按字母顺序列表中的最后一项.
--例如:
SELECT GREATEST(123,234,432,112)
FROM dual
GREATEST(123,234,432,112)
-------------------------
9.47. LEAST(list of values)
用途:该函数返回列表中项的最小值.对数值或日期来说,返回值是最小值或最早日期,如果列表中包含字符串,返回值是按字母顺序列表中的第一项.
--例如:
SELECT LEAST(sysdate,sysdate-10)
FROM dual
LEAST(SYS
---------
10-JAN-00
9.48 NVL(expression,replacement_value)
用途:如果表达式不为空值,函数返回该表达式的值,如果是空值,就返回用来替换的值.
--例如:
SELECT last_name,
NVL(TO_CHAR(COMMISSION),'NOT APPLICABLE')
FROM employee
WHERE department_id=30
LAST_NAME NVL(TO_CHAR(COMMISSION),'NOTAPPLICABLE')
--------------- ----------------------------------------
ALLEN 300
WARD 500
MARTIN 1400
BLAKE NOT APPLICABLE
多行函数
组函数可以对表达式的所有值操作,也可以只对其中不同值进行操作,组函数的语法如下所示:
9.49. function[DISTINCT|ALL expression]
如果既不指定DISTINCT,也不指定ALL,函数将对查询返回的所有数据行进行操作.不能在同一个SELECT语句的选择列中同时使用组函数和单行函数.
9.50. AVG([DISTINCT|ALL] expression)
用途:对查询返回的数据行求帄均值.
--例如:
SELECT AVG(sal) "Average"
FROM emp
Average
----------
2073.21429
9.51. COUNT([DISTINCT|ALL] expression)
用途:计算表达式的个数.要计算EMP表中雇员的个数.
--例如:
SELECT COUNT(deptno)
FROM emp
COUNT(DEPTNO)
-------------
14
SELECT COUNT(distinct deptno)
FROM emp
COUNT(DISTINCTDEPTNO)
---------------------
9.52. MAX([DISTINCT|ALL] expression)
用途:对查询返回的行集求最大值.如果有多个最大值,将所有均返回.要检索公司中最高工资的雇员.
语法:
SELECT ename,sal
FROM emp
WHERE sal=(select max(sal) FROM emp)
ENAME SAL
---------- ---------
KING 5000
9.53. MIN([DISTINCT|ALL] expression)
用途:对查询返回的行集求最小值.如果有多个最小值,将所有均返回.
--例如:
SELECT MIN(last_name)
FROM employee
MIN(LAST_NAME)
---------------
ADAMS
9.54. SUM([DISTINCT|ALL] expression)
用途:计算查询返回的所有非空数值的总和.如果返回的数据都是空值,则该函数也返回空值.
--例如:
SELECT SUM(salary)"Total"
FROM employee
WHERE department_id=10
Total
---------
8750
9.55. VARIANCE([DISTINCT|ALL] expression)
用途:该函数计算返回所有行的统计方差.
--例如:
SELECT VARIANCE(salary)
FROM employee
VARIANCE(SALARY)
----------------
973659.27
字符函数：
数据库字符集与X等价数值 ：Chr(x) 相反函数：ASCII
select Chr('65') from dual;
select ASCII('A') from dual;
连接字符串：concat(string 1,string2) 常用||替代
select concat('123','456') from dual;
select '123'||'456'||'789' from dual
去空格： trim(string)\LTRIM\RTRIM
select length(trim(' 12 123 ')) from dual;
select length(LTRIM(' 12 123 ')) from dual;
select LTRIM(' 12 123 ') from dual;
select RTRIM(' 12 123 ') from dual;
大小写转换 UPPER 和 LOWER
select UPPER('aaa') from dual;
select LOWER('AAA') from dual;
首字母大写：INITCAP(enam)\NLS_INICAP 只有首字母大写
select INITCAP('NLS_INICAP') from dual;
字符串截取：substr()
--
select 'ABCDEFG',substr('ABCDEFG',2,3) from dual
--
SELECT 'ABCDEFG',SUBSTR('ABCDEFG',-3,3) FROM DUAL;
字符串长度：length()
select length('1234567') from dual;
内容替换：replace()
select replace('ASDFF','ASD','F')from dual;
内容比较替换：TRANSLATE(string ,string1,string2)
select TRANSLATE('ASDFGHJKL','ASDFGH','q') from dual;
-------------
分析函数 over max(personincome) over(partition by earnmonth,area) 最高值
Min(personincome) over(partition by earnmonth,area) 最低值avg(personincome)
over(partition by earnmonth,area) 平均值sum(personincome)
over(partition by earnmonth,area) 总额
aLL表示对所有，distinct表示对不同的值求最大值,相同的只取一次
常用的聚合函数 AVG,MAX,MIN,COUNT,SUM
select from dual;
select a.empsalary,mod(empsalary,5) from emp a;
select * from emp ;
select ('13500.00'+'20060.00')/2 from dual;
--16780
SUM(DISTINCT|ALL column) 求和
AVG(DISTINCT|ALL column) 平均
MAX(DISTINCT|ALL column) 最大
MIN(DISTINCT|ALL column) 最小
COUNT(DISTINCT|ALL column) 统计 count(*)和count(1)
MEDIAN 求中位数
VARIANCE(DISTINCT|ALL column) 方差
STDDEV(distinct|all column) 标准差
-------------
四舍五入：ROUND()
2、截断小数位：TRUNC()
3、取余(取模)：MOD
SELECT ROUND(789.536) FROM dual;
【1】保留2位小数,(如果是-2则对整数进行四舍五入，变为800了)
SELECT ROUND(783.569,2) FROM dual;
【2】使用MOD()函数进行取余操作
SELECT MOD(10,2) FROM dual;
转换函数：
1、TO_CHAR()： 将日期或数值转换成字符串
2、TO_NUMBER()：将字符串转换成数字
3、TO_DATE()： 将字符串转换成日期
select TO_DATE('20170208','YYYYMMDD') from DUAL;
SELECT SYSDATE FROM dual;
Oracle提供了以下的日期函数支持：
MONTHS_BETWEEN():求出给定日期范围的月数
ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期
NEXT_DAY():下一个的今天的日期
LAST_DAY():求出给定日期的最后一天日期
select MONTHS_BETWEEN(SYSDATE,TO_DATE('20170208','YYYYMMDD')) from dual;
select ADD_MONTHS(SYSDATE,1) from dual;
select NEXT_DAY(SYSDATE,2) from dual;
select LAST_DAY(SYSDATE) from dual;
select sysdate from dual;
--返回字符串首字母变成大写
select initcap(t.prod_code) from subs.inf_group t;
--在一个字符串中搜索指定字符。
--instr(被搜索字符串,指定字符串,开始搜索的位置,搜索第几个指定字符)
select instr('oraclerasdf','ra',1,2) from dual;
--返回字符串的长度
select length('aaaa') from dual;
--返回字符串并且将所有的字符小写
select lower('ASDFGHJ') FROM DUAL;
--返回字符串并且将所有的字符大写
select upper('asdfghj') FROM DUAL;
--在字符右边添加字符rpad(被添加添加的字符,添加字符后的总位数,添加的字符)
--在字符左边添加字符lpad(被添加添加的字符,添加字符后的总位数,添加的字符)
select lpad(rpad('aaa',10,'*'),17,'*') from dual;
--删除右边的字符串rtrim(字符串,删除的部分)
select rtrim('gao qian jing','ing') from dual;
--删除左边的字符串rtrim(字符串,删除的部分)
select ltrim('gao qian jing','gao') from dual;
--截取字符串substr('字符串',开始的位置,截取字符串的长度)开始的位置坐标从1开始
select substr('abcdefghijklmnopqistuvwxyz',3,4) from dual;
--替换字符串replace(字符串,被替换的字符串,要替换的字符串)
select replace('he love you','he','i') from dual;
--返回指定值的绝对值
select abs(-100) from dual;
--返回一个大于或等于的整数
select ceil(3.0001) from dual;
--对给定的数据取整
select floor(2.67) from dual;
--mod(n1,n2)返回一个n1除以n2的余数
select mod(13,4) from dual;
--power(n1,n2)返回n1的n2次方根
select power(3,3) from dual;
--按照指定的精度进行舍入
select round(1.5),round(1.4),trunc(1.5),trunc(1.4) from dual;
--比较数字，大于0返回1，小于0返回-1，等于0返回0
select sign(123),sign(-123),sign(0) from dual;
--返回数字的根
select sqrt(9),sqrt(4) from dual;
--按照指定的精度截取一个数
select trunc(123.1666,-1),trunc(123.1666,2) from dual;
--增加或减去月份
select to_char(add_months(to_date('1999-12-07','yyyy-mm-dd'),-3),'yyyy-mm-dd') from dual;
--返回日期的最后一天
select last_day(to_date('1999-12-07','yyyy-mm-dd')) from dual;
--返回两个日期相差的月数
select months_between(to_date('1999-1-08','yyyy-mm-dd'),
to_date('1999-3-07','yyyy-mm-dd')) from dual;
--desc对查询结果进行从大到小的排序
select * from subs.inf_group c order by c.group_id asc;
--nvl(n1,n2) n1为null返回n2 n1不为null返回n1
--nvl2(n1,n2,n3) n1为null返回n2，n2为null返回n3，n2和n3类型不同，n3会转换成n2类型
--nullif(n1,n2) 相等返回null，不相等返回n1
在Oracle中的DBMS_RANDOM程序包中封装了一些生成随机数和随机字符串的函数，其中常用的有以下两个：
DBMS_RANDOM.VALUE函数
该函数用来产生一个随机数，有两种用法：
1. 产生一个介于0和1之间（不包含0和1）的38位精度的随机数，这种用法不包含参数,语法为：
DBMS_RANDOM.VALUE RETURN NUMBER;
2. 产生一个介于指定范围之内的38位精度的随机数，语法为：
DBMS_RANDOM.VALUE(low IN NUMBER,high IN NUMBER) RETURN NUMBER;
这种用法包含两参数，参数low用来指定要生成的随机数的下限，参数high指定上限，生成的随机。
请注意生成的随机数有可能等于下限，但绝对小于上限，即“low<=随机数<high”。
例如: 要产生一个1到100之间的数可以这样写:
DBMS_RANDOM.VALUE(1,100)
DBMS_RANDOM.STRING函数
该函数产生一个随机字符串，语法为：
DBMS_RANDOM.STRING (opt IN CHAR,len IN NUMBER) RETURN VARCHAR2;
len ：指定生成的字符串的长度。
opt：指定生成的字符串的样式，
允许的取值及其表示的含义如下表所示：
‘u’或’U’ ： 返回一个由大写字母组成的字符串
‘l’或’L’ ： 返回一个由小写字母组成的字符串
‘a’或’A’ ： 返回一个由大写字母和小写字母组成的字符串
‘x’或’X’ ： 返回一个由大写字母和数字组成的字符串
‘p’或’P’ ： 返回一个由任意的可打印字符组成的字符串
例如: 要产生一个长度为6且只包括大写字母的字符串,可以这样写:
DBMS—RANDOM.STRING('U',6)
复制代码
1、从表中随机取记录
select * from (select * from staff order by dbms_random.random)
where rownum < 4
表示从STAFF表中随机取3条记录
2、产生随机数
SELECT DBMS_RANDOM.RANDOM FROM DUAL;
产生一个任意大小的随机数
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;
产生一个100以内的随机数
SELECT TRUNC(100+900*dbms_random.value) FROM dual;
产生一个100～1000之间的随机数
SELECT dbms_random.value FROM dual;
产生一个0～1之间的随机数
SELECT dbms_random.value(10,20) FROM dual;
产生一个10～20之间的随机数
SELECT dbms_random.normal FROM dual;
NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。
3、产生随机字符串
select dbms_random.string('P',20) from dual;
第一个参数 P 表示 printable，即字符串由任意可打印字符构成
第二个参数表示返回字符串长度
4、ceil( n )函数是返回大于或等于n的最小整数。
DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
要产生两位的随机数，可以DBMS_RANDOM.VALUE()*100，这样产生( 0,100 )
的随机数，当产生( 0,10)之间的数时，只要加上10就可以保证产生的数都是两位了。
ORACLE的PL/SQL提供了生成随机数和随机字符串的多种方式，罗列如下:
1、小数( 0 ~ 1)
select dbms_random.value from dual
2、指定范围内的小数 ( 0 ~ 100 )
select dbms_random.value(0,100) from dual
3、指定范围内的整数 ( 0 ~ 100 )
select trunc(dbms_random.value(0,100)) from dual
4、长度为20的随机数字串
select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual
5、正态分布的随机数
select dbms_random.normal from dual
6、随机字符串
select dbms_random.string(opt, length) from dual
opt可取值如下：
'u','U' : 大写字母
'l','L' : 小写字母
'a','A' : 大、小写字母
'x','X' : 数字、大写字母
'p','P' : 可打印字符
7、随机日期
select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual
通过下面的语句获得指定日期的基数
select to_char(sysdate,'J') from dual
8、生成GUID
select sys_guid() from dual
--生成带分隔符(-)的GUID的自定义函数
create or replace function my_guid
return varchar2
is
guid varchar(36);
temp varchar(32);
begin
temp:=sys_guid();
guid:= substr(temp,1,8) || '-'
||substr(temp,9,4) || '-'
||substr(temp,13,4)|| '-'
||substr(temp,17,4)|| '-'
||substr(temp,21,12);
return guid;
end;
/
oracle中substr函数的用法
1、substr(string string, int a, int b)
参数1:string 要处理的字符串
参数2：a 截取字符串的开始位置（起始位置是0）
参数3：b 截取的字符串的长度(而不是字符串的结束位置)
例如：
substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符
substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
substr("ABCDEFG", -3, 3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。
2、substr(string string, int a)
参数1:string 要处理的字符串
参数2：a 可以理解为从索引a（注意：起始索引是0）处开始截取字符串，也可以理解为从第 （a+1）个字符开始截取字符串。
例如：
substr("ABCDEFG", 0); //返回：ABCDEFG, 截取所有字符
substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符
INSTR
(源字符串, 目标字符串, 起始位置, 匹配序号)
在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，就是说从字符的开始
到字符的结尾就结束。
语法如下：
instr( string1, string2 [, start_position [, nth_appearance ] ] )
参数分析：
string1
源字符串，要在此字符串中查找。
string2
要在string1中查找的字符串.
start_position
代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance
代表要查找第几次出现的string2的位置. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
注意：
如果String2在String1中没有找到，instr函数返回0.
示例：
select instr('121221222122221','1',1,1) from dual;
返回：1
select instr('121221222122221','1',1,2) from dual;
返回：3
select instr('121221222122221','1',1,3) from dual;
返回：6
select instr('121221222122221','1',1,4) from dual;
返回：10
select instr('121221222122221','1',1,5) from dual;
返回：15
select instr('121221222122221','1',1,6) from dual;
返回：0
select instr('121221222122221','12',1,1) from dual;
返回：1
select instr('121221222122221','12',1,2) from dual;
返回：3
select instr('121221222122221','12',1,3) from dual;
返回：6
select instr('121221222122221','12',1,4) from dual;
返回：10
select instr('121221222122221','12',1,5) from dual;
返回：0
select instr('121221222122221','12',1,6) from dual;
返回：0
SELECT instr('syranmo','s') FROM dual; -- 返回 1
SELECT instr('syranmo','ra') FROM dual; -- 返回 3
1 SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0
（根据条件，由于a只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！）
SELECT instr('syranmo','an',-1,1) FROM dual; -- 返回 4
（就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4）
SELECT instr('abc','d') FROM dual; -- 返回 0
注：也可利用此函数来检查String1中是否包含String2，如果返回0表示不包含，否则表示包含。
对于上面说到的，我们可以这样运用instr函数。请看下面示例：
如果我有一份资料，上面都是一些员工的工号（字段：CODE），可是我现在要查询出他们的所有员工情况，例如名字，部门，职业等等，这里举例是两个员工，工号分别是’A10001′，’A10002′，其中假设staff是员工表，那正常的做法就如下：
1 2 SELECT code , name , dept, occupation FROM staff WHERE code IN ('A10001','A10002');
或者：
SELECT code , name , dept, occupation FROM staff WHERE code = 'A10001' OR code = 'A10002';
有时候员工比较多，我们对于那个’觉得比较麻烦，于是就想，可以一次性导出来么？这时候你就可以用instr函数，如下：
SELECT code , name , dept, occupation FROM staff WHERE instr('A10001,A10002',code)>0;
查询出来结果一样，这样前后只用到两次单引号，相对方便点。
还有一个用法，如下：
SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;
等同于
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;
--生成随即字符dbms_random.string(选项，长度);
--选项：u表示大写字母，L表示小写字母，x表示数字和字母混合，p表示任意字符
--随机生成数字：语法1:dbms_random.value； 生成0-1之间随机数
--dbms_random.value*num; 生成0到num之间的随机数
--dbms_random.value(min,max）；生成min到max之间的随机数
--随机数取整：round(dbms_random.value(min,max）); 生成min到max之间的随机整数
--随机数取整：trunc(dbms_random.value(min,max）); 生成min到max之间的随机整数
--随机获取13100000000~18999999999之间的数字（也就是131到189之间的手机号码）
select trunc(dbms_random.value(13100000000,18999999999)) from dual;
自定义函数：
create or replace function fun_kiki_emp_avg_sal(v_department emp.department%type)return number is
/*
*（3）创建一个函数，以部门号为参数，返回该部门的平均工资。
*/
v_avg_sal emp.sal%type;
begin
select avg(sal) into v_avg_sal from emp where department=v_department;
return v_avg_sal;
end;
--测试1
declare
v_no number:=0;
begin
v_no:=fun_kiki_emp_avg_sal(10);
dbms_output.put_line(v_no);
end;
---------------------------
18579.9218
--测试2
select fun_kiki_emp_avg_sal(10) from dual;
-----------------------------------
18579.9218
浙公网安备 33010602011771号