oracle数据库函数定义,Oracle数据库PL/SQL学习笔记——函数定义

小技巧：

如果在sql window窗口下创建函数，但是创建的函数一直是无效函数(有个小红叉)，

那么可以再新建 -》program window-》 function  窗口中重写创建函数，这里面有相信的错误信息

--事务作用域

/*是执行线程 ——进程，当连接至Oracle 11g数据库时，

建立一个会话，在会话中做的任何事只有自己可见，直到提交操作完成，

其他会话才知道所作的改变。*/

调用子例程时，参数的传递：

1.位置表示法：为形参列表中的每个变量提供值，这些值必须是有顺序的，且必须匹配数据类型

2.命名表示法：可通过使用形参名，联合运算符(=>)和值传递实参。无序

create or replace function add_three_numbers

(a number:=0,b number :=0,c number :=0)

return number is

begin

return a+b-c;

end;

--位置表示法

begin

dbms_output.put_line(add_three_numbers(3,4,5));

end;

--命名表示法

begin

dbms_output.put_line(add_three_numbers(c =>5,b=>4,a=>3));

end;

--混合表达法(第一个参数使用位置表达法直接传值，后两个参数使用命名表达法赋值)

begin

dbms_output.put_line(add_three_numbers(3,c=>5,b=>4));

end;

--排除表达法(没有提供的第二个参数b用null代替)

begin

dbms_output.put_line(add_three_numbers(3,c=>5));

end;

/*sql中调用函数时也能使用调用表示法*/

select add_three_numbers(3,c=>5,b=>4) from dual;

函数

有值传递和引用传递。

注意：不能在sql查询中调用包含DML操作的存储函数，会出现不能再查询中执行DML的错误。

但是，可以再插入，更新和删除的sql语句中调用执行DML操作的函数。

--管道函数的使用(速度更快)

create or replace function Pipelined_numbers

return numbers

pipelined is

list numbers := numbers(0,1,2,3,4,5,6,7,8,9);

begin

for i in 1..list.last loop

pipe row(list(i)); --管道函数的使用

end loop;

return;

end;

包的使用：

--例：在包规范中定义了记录结构体、记录结构体的集合以及返回集合类型的函数：

--定义包pipelined

create or replace package pipelined is

type user_record is record --在包中定义记录record

(name varchar2(30),

age number

);

type user_collection is table of user_record;

function pf return user_collection pipelined; --函数声明,返回的类型要指明所在的包pipelined

end pipelined;

--在包主体实现pf函数(当函数返回集合时，pipelined子句提供了改进的性能)

create or replace package body pipelined is --注意包的主体声明中是 package body

function pf

return user_collection pipelined is

counter number := 1;

userlist user_collection := user_collection(); --初始化为空的集合

cursor c is

select t.name,t.age from tb_user t;

begin

for i in c loop

userlist.extend; --extend为一个元素分配空间，然后值被指派给被索引元素的组件

userlist(counter).name := i.name;

userlist(counter).age := i.age;

pipe row(userlist(counter));--在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。

counter := counter +1;

end loop;

return;

end pf;

end pipelined;

--在包体中的函数调用 包名.函数名

select pipelined.pf from dual; --返回记录结构体的行为聚集表 ， collection集合

--使用relies_on子句实现跨会话结果缓存：

--注意事项：

1.不能是管道表函数

2.不能有引用传递，如in out或out

3.不能使用blob，clob，nclob，ref cursor，集合，对象，或记录数据类型的形参

create or replace type strings as table of varchar2(30); --创建用户级别的type

create or replace function getnames

(par_name varchar2) return strings

result_cache relies_on(tb_user) is -- result_cache 声明缓存结果，relies_on(tb_user)确保了对底层表的任何改变都会使缓存无效，确保了缓存是动态的

counter number := 1; --oracle数据库中集合从1开始遍历

return_value strings := strings();

cursor name_cur (v_name varchar2) is

select name from tb_user where upper(name) like '%'||upper(v_name)||'%';

begin

for i in name_cur(par_name) loop

return_value.extend;

return_value(counter):=i.name;

counter := counter + 1;

end loop;

return return_value;

end getnames;

create or replace function getnames

(par_name varchar2) return strings

result_cache is -- 可省略relies_on(tb_user)提高查询性能，

counter number := 1; --oracle数据库中集合从1开始遍历

return_value strings := strings();

cursor name_cur (v_name varchar2) is

select name from tb_user where upper(name) like '%'||upper(v_name)||'%';

begin

for i in name_cur(par_name) loop

return_value.extend;

return_value(counter):=i.name;

counter := counter + 1;

end loop;

return return_value;

end getnames;

--测试(第二次执行，耗时0s，说明缓存成功)

declare

list strings;

begin

list := getnames('a');

for i in 1..list.last loop

dbms_output.put_line('list('||i||'):['||list(i)||']');

end loop;

end;

--值传递函数

--接受输入列表(形参)，返回单个输出变量，  所有形参必须使用in模式定义

--ref游标

隐式游标和显示游标都是静态定义的，他们在编译的时候结果集就已经确定。

如果想在运行的时候动态确定结果集，就要使用ref游标和游标变量

--系统引用游标 --例子：使用函数返回游标(这里只能是系统游标，也只有系统游标能作为参数和返回值) --因为：sys_refcursor 是oracle数据库预定义的类型

--利用此函数能给另外一个系统游标赋值

create or replace function get_full_names

return sys_refcursor is

names sys_refcursor; --变量声明

begin

open names for

select name,age from tb_user;

return names;

end;

--递归函数

--例：返回任意数的阶乘值

create or replace function factorial

(n binary_double) return binary_double is

begin

if n<=1 then

return 1;

else

return n * factorial(n-1);

end if;

end factorial;

select factorial(5) from dual;--120

--引用传递函数

传递引用，被调用时接受值的副本，能够返回实参的新值给调用者使用至少有一个形参通过使用out或in out模式定义。

总结：共有两种引用传递参数，一种在进入和退出时都有值：in out 模式变量；

另一种退出时有一个值：out模式参数

create or replace function counting

(number_in in out number) return varchar2 is --传入的参数经过函数处理后返回给调用者

type numbers is table of varchar2(5);

ordinal numbers := numbers('one','two','three','four','five');

retval varchar2(9) := 'not found';

begin

if number_in is null then

number_in := 1;

end if;

if number_in < 4 then

retval := ordinal(number_in);

number_in := number_in +1;

else

retval := ordinal(number_in);

end if;

return retval;

end;

--测试

declare

counter number :=1;

begin

for i in 1..5 loop

dbms_output.put_line('counter['||counter||']');

dbms_output.put_line('counter['||counting(counter)||']');

end loop;

end;

--out模式下的引用传递

create or replace function counting2

(

number_out out number

) return varchar2 is

type numbers is table of varchar2(5);

ordinal numbers := numbers('one','two','three','four','five');

retval varchar2(9):= 'not found';

begin

if number_out is null then --不管输入的number_out的值是多少，由于是out类型的，所以始终是null

number_out := 1;

end if;

if number_out < 4 then

retval := ordinal(number_out);

number_out:=number_out+1;

else

retval := ordinal(number_out);

end if;

return retval;

end;

--测试

declare

counter number :=1;

begin

for i in 1..5 loop

dbms_output.put('counter['||counter||']');

dbms_output.put_line('['||counting2(counter)||']');

end loop;

end;