ORACLE常用函数汇总（持续更新中....）

在使用ORACLE过程中，把一些常用的函数的相关用法，注意事项进行简单的汇总，便于自己查询参考。

• DBMS_RANDOM包

dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数

1. value() 用于返回两个数值之间的随机数，value（low，high）
2. random() 返回的值介于2的31次方和-2的31次方之间的整形数值
3. dbms_random.send方法

   用于生成一个随机数种子,设置种子的目的是可以重复生成随机数，用于调试。否则每次不同，难以调度。

4. dbms_random.normal方法

   NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。

 

• EXECUTE IMMEDIATE

解析并马上执行动态语句 ,或非运行时创建的pl/sql块  

  

1. 不提交dml事务,要显式提交;  

execute immediate处理ddl,会提交所以以前改变的数据;  

  

2.不支持多行查询,可以临时表 或者ref cursors  

  

3.执行sql不需语句,执行pl/sql 要加分号;  

  

--0.传入  

declare  

    i_aac001 number(6):=111;  

begin  

    execute immediate 'insert into a2(aac001) values(:1)'  

    using i_aac001;   

end;  

  

  

insert into a2(aac001) values(1);  

insert into a2(aac001) values(2);  

  

  

--1.传入/传出  

declare  

    cnt number(6);  

begin  

    execute immediate 'select 1 from dual where 1=:1' into cnt  

    using cnt;  

    dbms_output.put_line(cnt);  

end;  

  

--2.调用存储过程  

declare  

    s1 varchar2(10);  

    s2 varchar2(10);  

begin  

    execute immediate 'begin test1(:1,:2); end;'  

    using s1,s2;  

end;  

  

--3.传值到记录  

declare  

--类型  

--声明  

    type type_a is record(str varchar2(10));  

    v_a type_a;  

    v_b a2%rowtype;  

begin  

    execute immediate 'select * from a2 where aac001=1742178'   

    into v_b;  

end;  

  

--4.多行查询 用临时表 或ref cursors  

declare   

   l_sal    pls_integer := 2000;   

begin   

   execute immediate 'insert into temp(empno, ename) ' ||   

                    '           select empno, ename from emp ' ||   

                    '           where   sal > :1'   

     using l_sal;   

   commit;   

end;   

  

http://baiyaoming.iteye.com/blog/1255016  

  

  

for in  变量声明类型  

-------------------------------  

declare  

cursor cur is select * from a2;  

begin  

    for c in (select * from a2) loop  

        dbms_output.put_line(c.aac002);  

    end loop;  

end;  

  

insert into a2(aac001) values(1);  

  

declare  

    type t_a is table of a2%rowtype;  

    type t_b is table of number(10) index by pls_integer;  

    v_a t_a;  

    v_b t_b;  

begin  

    select aac001 bulk collect into v_b from a2;  

    for i in v_b.first..v_b.last loop  

        dbms_output.put_line(v_b(i));  

    end loop;  

end;

• REPLACE函数

replace(x,y,z)返回值为将串X中的Y串用Z串替换后的结果字符串。若省略Z参数，则将串X中为Y串的地方删除  

• TRUNC函数

TRUNC函数返回以指定元素格式截去一部分的日期值。

其具体的语法格式如下：

TRUNC（date,[fmt]）

其中：

date 为必要参数，是输入的一个日期值

fmt 参数可忽略，是日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去

下面是该函数的使用情况：

trunc(sysdate,'yyyy') --返回当年第一天.

trunc(sysdate,'mm') --返回当月第一天.

trunc(sysdate,'d') --返回当前星期的第一天.

fmt值参考如下:

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI