创建函数的的语法： 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
329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
