Oracle中的随机函数与自定义函数

目录

一、随机函数

1.语法格式：

2.使用DBMS_RANDOM包中的函数生成随机数：

二、自定义函数

1.语法格式：

2.自定义函数运用实例

3.自定义函数--有out输出情况

---

一、随机函数

指可以产生随机数的函数，它是可以产生随机的数字或者字符串；

1.语法格式：

①RAND()

可以产生0到1之间的随机数；

②dbms_random.value(数1(小),数2(大))

生成数1-数2之间的随机数,默认生成小数；

③dbms_random.string('格式',数)

u	大写字母
l	小写字母
a	字母
x	大写字母和数字
p	可打印字符

生成随机字符串；

注意：

Ⅰ、Oracle 11g中没有RAND函数，可以使用DBMS_RANDOM包中的函数来生成随机数。

Ⅱ、DBMS_RANDOM包提供了多种随机数生成函数，比如：

• DBMS_RANDOM.VALUE：返回一个0到1之间的随机数。
• DBMS_RANDOM.RANDOM：返回一个0到N之间的整数随机数，其中N需要作为参数传递。
• DBMS_RANDOM.STRING：返回一个由指定字符集中的字符组成的随机字符串，可以指定字符串的长度和字符集。

2.使用DBMS_RANDOM包中的函数生成随机数：

①生成一个0到1之间的随机数：

SELECT DBMS_RANDOM.VALUE AS random_number FROM DUAL;

②生成一个0到100之间的整数随机数：

SELECT FLOOR(DBMS_RANDOM.VALUE(0, 101)) AS random_number FROM DUAL;

③生成一个由10个字符组成的随机字符串，字符集为大写字母和数字：

SELECT DBMS_RANDOM.STRING('U', 10) AS random_string FROM DUAL;

④随机打印5个（大小写）字母/字符/数字 

SELECT dbms_random.value(100,0) a,
       dbms_random.string('u',5) b,  --随机打印5个大写字母
       dbms_random.string('l',5) c,  --随机打印5个小写字母
       dbms_random.string('a',5) d,  --随机打印5个字母
       dbms_random.string('x',5) e,  --随机打印5个大写字母和数字
       dbms_random.string('p',5) f   --随机打印5个可打印字符
FROM dual;

⑥生成一个1-100的随机数

如果这个数大于95,返回1等奖；如果这个数大于85,返回2等奖；如果这个数大于70,返回3等奖；否则返回谢谢惠顾。

declare
v1 number:=dbms_random.value(1,100);
begin
  if v1>95 then dbms_output.put_line('1等奖');
  elsif v1>85 then dbms_output.put_line('2等奖');
  elsif v1>70 then dbms_output.put_line('3等奖');
 else dbms_output.put_line('谢谢惠顾');
end if;
end;

 ⑦使用RAND函数

可以运用在需要产生随机数的场合下，例如在测试、模拟等场合下使用。比如可以使用RAND()函数产生一个0到100之间的随机整数：

SELECT FLOOR(RAND() * 100) AS random_number;

总结：

Ⅰ、DBMS_RANDOM包中的随机数生成函数是基于伪随机数生成算法实现的，因此生成的随机数并不是真正的随机数，而是在一定程度上的伪随机数。

Ⅱ、rand函数是MySQL和PHP中的函数，用于生成随机数。在MySQL中，rand函数可以在任何版本中使用。在PHP中，rand函数可以在PHP 4和PHP 5中使用。其他数据库系统（如Oracle、SQL Server等）中没有rand函数，它们使用不同的方式来生成随机数。

二、自定义函数

指用户自己定义的函数，可以根据自己的需求来编写函数。

1.语法格式：

CREATE FUNCTION function_name (parameters) --function_name为函数名；parameters为函数参数
RETURNS return_type                        --return_type为返回值类型
AS 
BEGIN 
function_body;                             --function_body为函数体
END;

--详细描述理解：
create or replace function             --or replace 覆盖
函数名(参数 [in]|out|in out 类型...)    --不写默认是in，后面加类型不写长度
return  返回类型
is|as 
[声明变量;]
begin
  要执行的语句; 
  return 值|变量;
[exception]
  [要执行的语句;] 
end;

2.自定义函数运用实例

自定义函数可以运用在需要根据用户自己的需求编写函数的场合下使用。

例如：

①制作一个函数mysign

输入一个数，如果大于0返回1；如果小于0返回-1；如果等于0返回0；

create or replace function mysign(v  number) --参数v是要外界输入的，不用赋值   
return number 
is
begin
  if v>0 then return 1;
  elsif v<0 then return -1;
  elsif v=0 then return 0;
  end if;
end;

调用函数：

和系统函数的调用方法一样;

select sign(100),mysign('100'),mysign(-10)
from dual;

②再制作一个函数，输入一个数，要求返回他的相反数：

create or replace function xfs(v number) 
return number 
is
begin
  return -v;
end;

调用函数：

select xfs(-0),xfs(0)
from dual;

③再制作一个函数表示π(没有参数)：

create or replace function π 
return number is
begin
  return acos(-1);          --acos(-1) 在数据库里代表π
end;

调用函数：

select π,acos(-1)
from dual;

--比如求半径是3的圆的周长和面积
select 2*π*3 周长,π*power(3,2) 面积
from dual;

④要求创建一个函数，当输入一个员工编号时能够返回员工工资：

create or replace function rs1 (v number) 
return number is
v_sal number;     --声明变量
begin
  select sal into v_sal from emp where empno=v;
  return v_sal;
end;

--调用函数：
select rs1(7788) from dual;

3.自定义函数--有out输出情况

比如：

①要求当输入员工编号时输出员工工资，并返回职位：

create or replace function rs2 (v_empno number,v_sal out number)
return varchar2 
is
v_job varchar2(20);
begin
  select sal,job into v_sal,v_job from emp where empno=v_empno;
return v_job;
end;

调用函数：

因为函数创建时有out输出，函数调用就不能和系统函数调用方法一样，此时需要采用pl/sql调用；

declare
v1 number;
v2 varchar2(20);
begin
  v2:=rs2(7788,v1);                  --返回值:=函数名（参数1,参数2）
  dbms_output.put_line(v1||' '||v2);
end;

②要求自定义一个函数，输入员工姓名，输出员工工资，最后返回员工的部门编号：

create or replace function  rs3(v_name varchar2,v_sal out number)
return number 
is
v_deptno number;
begin
  select sal,deptno into v_sal,v_deptno 
  from emp 
  where ename=v_name;
return v_deptno;
end;

函数调用：

declare
v_deptno number;
v_sal number;
begin
  v_deptno:=rs3('KING',v_sal);
  dbms_output.put_line(v_sal||' '||v_deptno);
end;

自定义函数总结：

• ·有out一般不用函数
• ·函数一般不包含DML
• ·函数不能包含DDL